Omid - Challenge 300

data-challenges
advanced-exercises
🔰 Group : Custom Grouping!
Published

March 24, 2026

Illustration for Omid - Challenge 300

Challenge Description

🔰 Group : Custom Grouping!

Solutions

library(tidyverse)
library(readxl)

path = "files/300-399/CH-300 Custom Grouping.xlsx"
input = read_excel(path, range = "B2:C19")
test  = read_excel(path, range = "G2:H7")

custom_grouping = function(sales) {
  if (!length(sales)) return(data.frame(Group = integer(), `Total Sales` = numeric()))
  res = list(data.frame(Group = 1, `Total Sales` = sales[1]))
  grp = 2
  sum = 0
  prev = sales[1]
  for (i in sales[-1]) {
    sum = sum + i
    if (sum >= 2 * prev) {
      res[[grp]] = data.frame(Group = grp, `Total Sales` = sum)
      grp = grp + 1
      prev = sum 
      sum = 0
    }
  }
  if (sum) res[[grp]] = data.frame(Group = grp, `Total Sales` = sum)
  bind_rows(res)
}


result = custom_grouping(input$Sales)

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • 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 = "300-399/CH-300 Custom Grouping.xlsx"
input = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=18)
test = pd.read_excel(path, usecols="G:H", skiprows=1, nrows=5)

def custom_grouping(sales):
    res = []
    if sales:
        res.append({'Group': 1, 'Total Sales': sales[0]})
        grp, total, prev = 2, 0, sales[0]
        for i in sales[1:]:
            total += i
            if total >= 2 * prev:
                res.append({'Group': grp, 'Total Sales': total})
                grp += 1
                prev, total = total, 0
        if total:
            res.append({'Group': grp, 'Total Sales': total})
    return pd.DataFrame(res)

result = custom_grouping(input['Sales'].tolist())
print(result.equals(test)) # 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.