Omid - Challenge 384

data-challenges
advanced-exercises
🔰 Table Transformation!
Published

March 24, 2026

Illustration for Omid - Challenge 384

Challenge Description

🔰 Table Transformation!

Solutions

library(tidyverse)
library(readxl)

path <- "300-399/384/CH-384 Table Transformation.xlsx"
df <- read_excel(path, range = "B3:E11", col_names = TRUE)
test <- read_excel(path, range = "G3:J9", col_names = TRUE)

name <- df[seq(1, nrow(df), 2), ]
sale <- df[seq(2, nrow(df), 2), ]
name <- name |>
  mutate(Date = zoo::na.locf(Date))
result <- bind_rows(
  name |>
    transmute(
      Date,
      Customer,
      Product = `product 1`,
      Sale = sale$`product 1`
    ),
  name |>
    transmute(Date, Customer, Product = `product 2`, Sale = sale$`product 2`)
) |>
  mutate(.row = rep(seq_len(nrow(name)), 2)) |>
  drop_na() |>
  arrange(.row) |>
  select(-.row) |>
  mutate(Sale = as.integer(Sale))

colnames(test) <- colnames(result)

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

  • 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

path = "300-399/384/CH-384 Table Transformation.xlsx"
df = pd.read_excel(path, usecols="B:E", skiprows=2, nrows=8)
test = pd.read_excel(path, usecols="G:J", skiprows=2, nrows=6)

name = df.iloc[::2].reset_index(drop=True)
sale = df.iloc[1::2].reset_index(drop=True)

name["Date"] = name["Date"].ffill()

result = (
    pd.concat([
        name[["Date", "Customer"]].assign(Product=name["product 1"], Sale=sale["product 1"]),
        name[["Date", "Customer"]].assign(Product=name["product 2"], Sale=sale["product 2"]),
    ])
    .dropna()
    .sort_index(kind="stable")
    .reset_index(drop=True)
)

result["Sale"] = result["Sale"].astype(int)

test.columns = result.columns
print(result.equals(test))

  • Logic:

    • Reads the workbook ranges needed for the challenge

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