Omid - Challenge 31

data-challenges
advanced-exercises
🔰 In the question table, the distances between various cities are provided.
Published

March 24, 2026

Illustration for Omid - Challenge 31

Challenge Description

🔰 In the question table, the distances between various cities are provided.

Solutions

library(tidyverse)
library(readxl)

input = read_excel("files/CH-031 Creat From-To matrix.xlsx", range = "B2:D12")
test  = read_excel("files/CH-031 Creat From-To matrix.xlsx", range = "F2:K7") %>% 
  column_to_rownames(var = "...1") %>%
  as.matrix()

t1 = input %>%
  pivot_wider(names_from = "TO", values_from = "Distance") %>%
  select(From, A, B, C, D, E) %>% 
  column_to_rownames(var = "From") %>%
  as.matrix() %>%
  replace(is.na(.), 0)
  
t2 = t1 %>%
  t()  %>%
  replace(is.na(.), 0)

t = t1 + t2

identical(t, test)
# [1] TRUE

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Reshapes the data into the grain required by the task

  • 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

input = pd.read_excel("CH-031 Creat From-To matrix.xlsx", usecols="B:D", skiprows=1, nrows=11)
test = pd.read_excel("CH-031 Creat From-To matrix.xlsx", usecols="F:K", skiprows=1, nrows=5)
test.columns = ["From"] + test.columns[1:].tolist()
test.set_index("From", inplace=True)
test_matrix = test.to_numpy()

t1 = input.pivot(index="From", columns="TO", values="Distance").fillna(0).to_numpy()
t2 = t1.T
t = t1 + t2

print(t==test_matrix) # True for all elements

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Reshapes the data into the grain required by the task

    • Applies the rule iteratively until the output stabilizes

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