Omid - Challenge 76

data-challenges
advanced-exercises
🔰 In challenge 58, we aimed to find an efficient way to calculate the stepped tax based on the tax rates presented in the question table.
Published

March 24, 2026

Illustration for Omid - Challenge 76

Challenge Description

🔰 In challenge 58, we aimed to find an efficient way to calculate the stepped tax based on the tax rates presented in the question table.

Solutions

library(tidyverse)
library(readxl)

path = "files/CH-076 Reverse Stepped Tax.xlsx"

input1 = read_xlsx(path, range = "B2:D7")
input2 = read_xlsx(path, range = "F2:G7")
test   = read_xlsx(path, range = "F2:H7")

in1 = input1 %>%
  mutate(To = ifelse(To == "Over", Inf, as.numeric(To))) %>%
  mutate(max_tax = round(cumsum((To - From) * `Tax Rate`),0))

in2 = input2 %>%
  crossing(in1) %>%
  mutate(tax_in_max_thr = Tax - lag(max_tax), .by = `Person ID`) %>%
  filter(tax_in_max_thr > 0) %>%
  filter(tax_in_max_thr == min(tax_in_max_thr), .by = `Person ID`) %>%
  mutate(income = From + tax_in_max_thr / `Tax Rate`)

diff = test$Income - in2$income
# [1] -1.000000 -1.421053  1.076923 -1.052632 -2.567568
# discrepancies caused by rounding errors

  • Logic:

    • 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

path = "CH-076 Reverse Stepped Tax.xlsx"

input1 = pd.read_excel(path, usecols="B:D", skiprows=1)
input2 = pd.read_excel(path,  usecols="F:G", skiprows=1)
test = pd.read_excel(path, usecols="F:H", skiprows=1)

in1 = input1.copy()
in1["To"] = in1["To"].apply(lambda x: float("inf") if x == "Over" else float(x))
in1["max_tax"] = round((in1["To"] - in1["From"]) * in1["Tax Rate"]).cumsum()

in2 = pd.merge(input2, in1, how="cross")
in2["tax_in_max_thr"] = in2["Tax"] - in2.groupby("Person ID")["max_tax"].shift(1)
in2 = in2[in2["tax_in_max_thr"] > 0]
in2 = in2.groupby("Person ID").apply(lambda x: x[x["tax_in_max_thr"] == x["tax_in_max_thr"].min()])
in2["Income"] = in2["From"] + in2["tax_in_max_thr"] / in2["Tax Rate"]
in2 = in2.reset_index(drop=True)

print(in2["Income"] - test["Income"])

# 0   -1.222222
# 1    1.421053
# 2   -1.076923
# 3    1.052632
# 4   -0.135135
# Discrepancies because of rounding errors

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

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