Omid - Challenge 268

data-challenges
advanced-exercises
🔰 Challenge 268: Custom Grouping!
Published

March 24, 2026

Illustration for Omid - Challenge 268

Challenge Description

🔰 Challenge 268: Custom Grouping!

Solutions

library(tidyverse)
library(readxl)

excel = "files/200-299/268/CH-268 Custom Grouping.xlsx"
dat = read_excel(excel, range = "B2:C16")
test = read_excel(excel, range = "F2:H16")

conf = list(c("A101", "A105"), c("B01", "B03"))

grp = function(ids, pairs) {
  g = 1
  seen = character()
  out = integer(length(ids))
  for (i in seq_along(ids)) {
    id = ids[i]
    hit = any(map_lgl(pairs, ~all(.x %in% c(seen, id))))
    if (hit) {
      g = g + 1
      seen = id
    } else {
      seen = union(seen, id)
    }
    out[i] = g
  }
  out
}

res = dat %>% mutate(Group = grp(ID, conf))

all.equal(res$Group, test$Group) # TRUE

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Builds the intermediate columns that drive the final result

    • Applies the rule iteratively until the output stabilizes

  • 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

path = "200-299/268/CH-268 Custom Grouping.xlsx"
df = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=15)
df_test = pd.read_excel(path, usecols="F:H", skiprows=1, nrows=15)

conf = [["A101", "A105"], ["B01", "B03"]]

def mut_grp(x, conf):
    grp, seen, out = 1, set(), []
    for id in x:
        if any(set(c) <= seen | {id} for c in conf):
            grp += 1
            seen = {id}
        else:
            seen.add(id)
        out.append(grp)
    return out

df['Group'] = mut_grp(df['ID'], conf)
print(df['Group'].equals(df_test['Group'])) # True

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • 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 business rule is readable, but the workbook still requires careful implementation to reach the expected layout.