Omid - Challenge 259

data-challenges
advanced-exercises
🔰 : Extract Extract all product IDs from the texts that match the following pattern:
Published

March 24, 2026

Illustration for Omid - Challenge 259

Challenge Description

🔰 : Extract Extract all product IDs from the texts that match the following pattern:

Solutions

library(tidyverse)
library(readxl)
library(slider)

path = "files/200-299/259/CH-259 Extract from Text.xlsx"
input = read_excel(path, range = "B2:B3") %>% pull()
test  = read_excel(path, range = "D2:D3") %>% pull()

pattern = "[A-Z][a-z][0-9]-[0-9]{2}[A-Z][a-z]"
chars = str_split(input, "", simplify = TRUE)

windows = slide_chr(
  .x = seq_len(length(chars) - 7),
  .f = ~ str_c(chars[.x:(.x + 7)], collapse = "")
)
matches = windows[str_detect(windows, pattern)] %>%
  str_c(collapse = ", ")

all.equal(matches, test) 
#> [1] TRUE

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Parses the text patterns directly instead of relying on manual cleanup

  • Strengths:

    • The R solution stays close to the workbook rule and keeps the transformation compact.

  • Areas for Improvement:

    • The code assumes the sheet structure and source ranges remain stable.

  • Gem:

    • The strongest part of the solution is choosing the right intermediate representation before shaping the final output.
import pandas as pd
import re
from itertools import islice

path = "200-299/259/CH-259 Extract from Text.xlsx"
input = pd.read_excel(path, usecols="B", nrows=2, skiprows=1).iloc[0, 0]
test = pd.read_excel(path, usecols="D", nrows=2, skiprows=1).iloc[0, 0]

pattern = r"[A-Z][a-z][0-9]-[0-9]{2}[A-Z][a-z]"
windows = [
    ''.join(islice(input, i, i + 8))
    for i in range(len(input) - 7)
]
matches = ', '.join([window for window in windows if re.search(pattern, window)])

print(matches == test) # True

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Parses the text patterns directly instead of relying on manual cleanup

    • Applies the rule iteratively until the output stabilizes

  • Strengths:

    • The Python version follows the same rule in a direct dataframe-oriented implementation.

  • Areas for Improvement:

    • The code assumes the workbook layout remains stable, so any sheet redesign would require small adjustments.

  • Gem:

    • The implementation stays close to the original workbook rule instead of adding unnecessary abstraction.

Difficulty Level

This task is moderate:

  • The core logic is clear, but the correct transformation pattern is not obvious from the raw input.

  • The challenge combines multiple reshaping, grouping, or parsing steps.