Omid - Challenge 229

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

March 24, 2026

Illustration for Omid - Challenge 229

Challenge Description

🔰 Challenge 229: Custom Grouping!

Solutions

library(tidyverse)
library(readxl)

path = "files/200-299/229/CH-229 Custom Grouping.xlsx"
input = read_excel(path, range = "B2:B102")
mean = 463

give_range = function(df, mean, perc) {
  d = df %>%
    mutate(dist = abs(. - mean)) %>%
    arrange(dist) %>%
    slice(ceiling(n() * perc)) %>%
    pull()
  return(paste0(mean - d, "-", mean + d))
}

result = tibble(pc = seq(0.1, 1, 0.1)) %>%
  mutate(Range = map_chr(pc, ~ give_range(input, mean, .)))

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Builds the intermediate columns that drive the final result

  • 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 numpy as np

path = "200-299/229/CH-229 Custom Grouping.xlsx"
mean = 463

input_data = pd.read_excel(path, usecols="B", skiprows=1, nrows=101)

percentages = np.arange(0.1, 1.1, 0.1)
ranges = []

for perc in percentages:
    input_data['dist'] = abs(input_data.iloc[:, 0] - mean)
    d = input_data.nsmallest(int(np.ceil(len(input_data) * perc)), 'dist').iloc[-1, 1]
    ranges.append(f"{mean - d}-{mean + d}")

result = pd.DataFrame({
    'pc': percentages,
    'Range': ranges
})

print(result)

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