Omid - Challenge 120

data-challenges
advanced-exercises
🔰 In challenge 60, we attempted to calculate the source of payment for each receipt.
Published

March 24, 2026

Illustration for Omid - Challenge 120

Challenge Description

🔰 In challenge 60, we attempted to calculate the source of payment for each receipt.

Solutions

library(tidyverse)
library(readxl)

path = "files/CH-120 payments Durations.xlsx"
input1 = read_excel(path, range = "B2:D14")
input2 = read_excel(path, range = "F2:H7")
test  = read_excel(path, range = "K2:L14")
test = test %>%
  mutate(Duration = ifelse(is.na(as.numeric(Duration)), Duration, as.character(round(as.numeric(Duration), 0))))


rec = input1 %>%
  mutate(ID = factor(ID, levels = paste0("C", 1:12), ordered = TRUE)) %>%
  uncount(Cost, .remove = FALSE) %>%
  mutate(Cost = 1, rn = row_number())

pay = input2 %>%
  mutate(ID = factor(ID, levels = paste0("P", 1:5), ordered = TRUE)) %>%
  uncount(Payment, .remove = FALSE) %>%
  mutate(Payment = 1, rn = row_number())

all = full_join(rec, pay, by = "rn") %>%
  mutate(pay_time = Date.y - Date.x) %>%
  summarise(amount = sum(Cost), .by = c(ID.x, ID.y, pay_time)) %>%
  summarise(mean_time = as.character(round(sum(pay_time * amount) / sum(amount)),0), .by = ID.x) %>%
  replace_na(list(mean_time = "NP")) %>%
  mutate(ID.x = as.character(ID.x))

all.equal(test, all, check.attributes = FALSE)
#> [1] TRUE

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

    • 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-120 payments Durations.xlsx"
input1 = pd.read_excel(path, usecols="B:D", skiprows=1, nrows= 12)
input2 = pd.read_excel(path, usecols="F:H", skiprows=1, nrows=5).rename(columns=lambda x: x.replace('.1', ''))
test  = pd.read_excel(path, usecols="K:L", skiprows=1, nrows= 12)
test['Duration'] = test['Duration'].replace('NP', '0').astype(int).astype(str)

input1["ID"] = pd.Categorical(input1["ID"], categories=input1["ID"].unique(), ordered=True)
input1 = input1.loc[input1.index.repeat(input1["Cost"])].assign(Value=1).reset_index(drop=True)
input1['rownumber'] = input1["Value"].cumsum()
input1 = input1.drop(columns=['Cost'])

input2["ID"] = pd.Categorical(input2["ID"], categories=input2["ID"].unique(), ordered=True)
input2 = input2.loc[input2.index.repeat(input2["Payment"])].assign(Value=1).reset_index(drop=True)
input2['rownumber'] = input2["Value"].cumsum()
input2 = input2.drop(columns=['Payment'])

all_data = pd.merge(input1, input2, on='rownumber', how='outer').sort_values(by='rownumber').reset_index(drop=True)
all_data['pay_time'] = (all_data['Date_y'] - all_data['Date_x']).dt.days.fillna(0).astype(int)
all_data = all_data.groupby(['ID_x', 'ID_y']).agg({'Value_x': 'sum', 'pay_time': 'first'}).reset_index()
all_data['multiplication'] = all_data['Value_x'] * all_data['pay_time']
all_data = all_data.groupby('ID_x').agg({'multiplication': 'sum', 'Value_x': 'sum'}).reset_index()
all_data['average'] = (all_data['multiplication'] / all_data['Value_x']).fillna(0).astype(int).astype(str)
all_data['ID_x'] = all_data['ID_x'].astype(str)
all_data = all_data.drop(columns=['multiplication', 'Value_x'])

all_data.columns = test.columns

print(all_data.equals(test)) # True

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

    • Builds the intermediate columns that drive the final result

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