library(tidyverse)
library(readxl)
input <- read_excel("Excel/800-899/803/803 Max & Min.xlsx", range = "A2:I7")
test <- read_excel("Excel/800-899/803/803 Max & Min.xlsx", range = "K2:M9")
result <- input %>%
pivot_longer(-Name, names_to = "Year", values_to = "Value") %>%
mutate(Value = as.integer(Value), Year = as.integer(Year)) %>%
filter(Value %in% range(Value)) %>%
mutate(Category = ifelse(Value == min(Value), "Min", "Max")) %>%
arrange(Category, Name) %>%
select(Category, Name, Year) %>%
mutate(Category = ifelse(row_number() == 1, Category, NA_character_), .by = Category)
all.equal(result, test, check.attributes = FALSE)Excel BI - Excel Challenge 803
excel-challenges
excel-formulas
🔰 List the Name
Challenge Description
🔰 List the Name
Solutions
- Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level; Reshape the result into the workbook output format.
- Strengths: The reshaping step mirrors the workbook output closely instead of forcing extra post-processing.
- Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
- Gem: The last reshape turns a raw transformation into something that already looks like a report.
import pandas as pd
file_path = "800-899/803/803 Max & Min.xlsx"
input = pd.read_excel(file_path, usecols="A:I", skiprows=1, nrows=6)
test = pd.read_excel(file_path, usecols="K:M", skiprows=1, nrows=8).rename(columns=lambda c: c.replace('.1', ''))
input_long = input.melt(id_vars='Name', var_name='Year', value_name='Value')
input_long = input_long.dropna(subset=['Value', 'Year'])
input_long = input_long.astype({'Value': int, 'Year': int})
value_range = [input_long['Value'].min(), input_long['Value'].max()]
filtered = input_long[input_long['Value'].isin(value_range)].copy()
filtered['Category'] = filtered['Value'].apply(lambda x: 'Min' if x == input_long['Value'].min() else 'Max')
filtered = filtered.sort_values(['Category', 'Name']).reset_index(drop=True)[['Category', 'Name', 'Year']]
filtered['Category'] = filtered.groupby('Category', group_keys=False).apply(
lambda g: [g['Category'].iloc[0]] + [None]*(len(g)-1)
).explode().values
print(test.equals(filtered)) #The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.
Difficulty Level
Medium
The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.