Omid - Challenge 200

data-challenges
advanced-exercises
🔰 In the question table, direct connections between people are provided.
Published

March 24, 2026

Illustration for Omid - Challenge 200

Challenge Description

🔰 In the question table, direct connections between people are provided.

Solutions

library(tidyverse)
library(readxl)
library(igraph)

path = "files/CH-200People Connection.xlsx"
input = read_excel(path, range = "B2:C11")
test  = read_excel(path, range = "E2:M10")

g = graph_from_data_frame(input, directed = FALSE)
all_people = sort(unique(c(input$`Person 1`, input$`Person 2`))) 
all_com = expand.grid(a1 = all_people, a2 = all_people, stringsAsFactors = F) 

shortest_path = function(a1, a2, g){
  if (a1 == a2) {
    return("-")
  }
  path = all_shortest_paths(g, from = a1, to = a2)
  if (length(path$res) == 0 || length(path$res[[1]]$name) <= 2) {
    return("1")
  }
  nodes_between = path$res[[1]]$name[-c(1, length(path$res[[1]]$name))]
  return(paste(nodes_between, collapse = "-"))
}

result = all_com %>%
  rowwise() %>%
  mutate(path = shortest_path(a1, a2, g)) %>%
  pivot_wider(names_from = a1, values_from = path)

print(result)
print(test)

# Discrepancies because of incosistent node sorting, but the paths are correct

  • 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

  • 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
import networkx as nx

path = "CH-200People Connection.xlsx"
input = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=9)
test = pd.read_excel(path, usecols="E:M", skiprows=1, nrows=9)

G = nx.from_pandas_edgelist(input, source='Person 1', target='Person 2', create_using=nx.Graph())

all_people = sorted(set(input['Person 1']).union(set(input['Person 2'])))
all_com = pd.DataFrame([(a1, a2) for a1 in all_people for a2 in all_people], columns=['a1', 'a2'])

def shortest_path(a1, a2, G):
    if a1 == a2:
        return "-"
    try:
        path = nx.shortest_path(G, source=a1, target=a2)
        if len(path) <= 2:
            return "1"
        nodes_between = path[1:-1]
        return "-".join(nodes_between)
    except nx.NetworkXNoPath:
        return "1"

all_com['path'] = all_com.apply(lambda row: shortest_path(row['a1'], row['a2'], G), axis=1)
result = all_com.pivot(index='a2', columns='a1', values='path')
print(result)
print(test)

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