Omid - Challenge 222

data-challenges
advanced-exercises
🔰 Table Transformation!
Published

March 24, 2026

Illustration for Omid - Challenge 222

Challenge Description

🔰 Table Transformation!

Solutions

library(tidyverse)
library(readxl)

path = "files/CH-222 Table Transformation.xlsx"
input = read_excel(path, range = "B2:B17")
test = read_excel(path, range = "D2:F7")

result = input %>%
  mutate(
    type = case_when(
      str_detect(`Column 1`, "[a-zA-Z]") ~ "Product",
      as.numeric(`Column 1`) > 100 ~ "Date",
      as.numeric(`Column 1`) < 100 ~ "Quantity"
    )
  ) %>%
  mutate(rn = row_number(), .by = type) %>%
  pivot_wider(
    names_from = type,
    values_from = `Column 1`,
    values_fill = list(`Column 1` = NA)
  ) %>%
  select(-rn) %>%
  mutate(
    Date = as.Date(as.numeric(Date), origin = "1899-12-30") %>% as.POSIXct(),
    Quantity = as.numeric(Quantity)
  )

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

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Reshapes the data into the grain required by the task

    • Builds the intermediate columns that drive the final result

    • Parses the text patterns directly instead of relying on manual cleanup

  • 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-222 Table Transformation.xlsx"
input_data = pd.read_excel(path, usecols="B", skiprows=1, nrows=16)
test_data = pd.read_excel(path, usecols="D:F", skiprows=1, nrows=5)

input_data['Type'] = input_data.iloc[:, 0].apply(lambda v: "Product" if isinstance(v, str) and v.isalpha() else "Quantity" if isinstance(v, (int, float)) else "Date")
input_data['RowNumber'] = input_data.groupby('Type').cumcount() + 1

pivoted_data = input_data.pivot(index='RowNumber', columns='Type', values=input_data.columns[0]).reset_index(drop=True)
pivoted_data['Date'] = pd.to_datetime(pivoted_data['Date'], errors='coerce')
pivoted_data['Quantity'] = pivoted_data['Quantity'].astype('int64', errors='ignore')
pivoted_data.columns.name = None

print(pivoted_data.equals(test_data))

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