Omid - Challenge 234

data-challenges
advanced-exercises
🔰 Question Result ID MN-123-98-24 XMN-0-0-23-2 I-XX-MN1 MN1 MN-123
Published

March 24, 2026

Illustration for Omid - Challenge 234

Challenge Description

🔰 Question Result ID MN-123-98-24 XMN-0-0-23-2 I-XX-MN1 MN1 MN-123

Solutions

library(tidyverse)
library(readxl)

path = "files/200-299/234/CH-234 Column Splitting.xlsx"
input = read_excel(path, range = "B2:B7")
test = read_excel(path, range = "D2:G7")

result = input %>%
  separate(
    ID,
    into = c("ID.1", "ID.1.5", "ID.2", "ID.2.5", "ID.3", "ID.4"),
    sep = "-",
    extra = "merge"
  ) %>%
  unite("ID.1", ID.1, ID.1.5, sep = "-", remove = TRUE) %>%
  unite("ID.2", ID.2, ID.2.5, sep = "-", remove = TRUE)

  • Logic:

    • Reads the workbook ranges needed for the challenge

  • 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 = "200-299/234/CH-234 Column Splitting.xlsx"

input = pd.read_excel(path, usecols="B", skiprows=1, nrows=6)
test = pd.read_excel(path, usecols="D:G", skiprows=1, nrows=6)

split_cols = input.iloc[:, 0].str.split("-", n=5, expand=True)
split_cols.columns = ["ID.1", "ID.1.5", "ID.2", "ID.2.5", "ID.3", "ID.4"]
result = pd.DataFrame({
    "ID.1": split_cols["ID.1"] + "-" + split_cols["ID.1.5"],
    "ID.2": split_cols["ID.2"] + split_cols["ID.2.5"].apply(lambda x: f"-{x}" if pd.notna(x) and x != "" else ""),
    "ID.3": split_cols["ID.3"],
    "ID.4": split_cols["ID.4"]
})

print(result)

  • Logic:

    • Reads the workbook ranges needed for the challenge

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