Omid - Challenge 23

data-challenges
advanced-exercises
🔰 Advanced Weighted Average Calculation Based on these values, calculate the average of weight for monthly productions as below.
Published

March 24, 2026

Illustration for Omid - Challenge 23

Challenge Description

🔰 Advanced Weighted Average Calculation Based on these values, calculate the average of weight for monthly productions as below.

Solutions

library(tidyverse)
library(readxl)

input1 = read_excel("files/CH-023 Advance Weighted AVG.xlsx", range = "B2:E18")
input2 = read_excel("files/CH-023 Advance Weighted AVG.xlsx", range = "G2:J5")
test   = read_excel("files/CH-023 Advance Weighted AVG.xlsx", range = "L2:M5")

prod = input2 %>%
  pivot_longer(cols = -Month, names_to = "Machine Code", values_to = "value") 

result = input1 %>%
  group_by(Month, `Machine Code`) %>%
  summarise(Avg = mean(`Weight (KG/Meter)`)) %>%
  left_join(prod, by = c("Machine Code", "Month")) %>%
  ungroup() %>%
  group_by(Month) %>%
  summarise(`AVG weight (Kg/Meter)` = sum(Avg * value) / sum(value)) %>%
  mutate(`AVG weight (Kg/Meter)` = round(`AVG weight (Kg/Meter)`, 2)) %>%
  ungroup()

identical(result, test)
# [1] TRUE

  • 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

    • 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

input1 = pd.read_excel("CH-023 Advance Weighted AVG.xlsx", usecols="B:E", skiprows=1, nrows=17)
input2 = pd.read_excel("CH-023 Advance Weighted AVG.xlsx", usecols="G:J", skiprows=1, nrows=4)
test = pd.read_excel("CH-023 Advance Weighted AVG.xlsx", usecols="L:M", skiprows=1, nrows=4)

prod = input2.melt(id_vars="Month", var_name="Machine Code", value_name="value")
result = (
    input1.groupby(["Month", "Machine Code"], as_index=False)["Weight (KG/Meter)"]
    .mean()
    .rename(columns={"Weight (KG/Meter)": "Avg"})
    .merge(prod, on=["Machine Code", "Month"], how="left")
    .groupby("Month", as_index=False)
    .apply(lambda g: pd.Series({"AVG weight (Kg/Meter)": round((g["Avg"] * g["value"]).sum() / g["value"].sum(), 2)}))
    .reset_index(drop=True)
)

print(result.equals(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 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.