Omid - Challenge 3

data-challenges
advanced-exercises
🔰 Extract all possible combinations of ID and result in the right-side table.
Published

March 24, 2026

Illustration for Omid - Challenge 3

Challenge Description

🔰 Extract all possible combinations of ID and result in the right-side table.

Solutions

library(tidyverse)
library(readxl)

path = "files/CH-003.xlsx"
input = read_excel(path, range = "B2:C7")
test  = read_excel(path, range = "H2:I33")

# map on sequence 1:5 combn on input$ID

result = map(1:5, ~combn(input$ID, .x, simplify = FALSE)) %>%
  flatten() %>%
  imap(function(sublist, parent_idx) {
  data.frame(parent_index = parent_idx,ID = sublist) %>%
    pivot_longer(-parent_index)
}) %>%
  bind_rows() %>%
  left_join(input, by = c("value" = "ID")) %>%
  summarise(`ID Combination` = paste(value, collapse = ","), 
            `Total value (cost)` = sum(`value (cost)`),
            .by = parent_index) %>%
  select(-parent_index)

identical(result, test)

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Reshapes the data into the grain required by the task

    • Aggregates or ranks values at the relevant grouping level

  • 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 itertools
import pandas as pd

path = "CH-003.xlsx"
input_data = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=6)
test = pd.read_excel(path, usecols="H:I", skiprows=1, nrows=32)

rows = []
ids = input_data["ID"].tolist()
costs = dict(zip(input_data["ID"], input_data["value (cost)"]))
for r in range(1, len(ids) + 1):
    for combo in itertools.combinations(ids, r):
        rows.append({
            "ID Combination": ",".join(combo),
            "Total value (cost)": sum(costs[i] for i in combo),
        })

result = pd.DataFrame(rows)
print(result.equals(test))

  • 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 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.