Omid - Challenge 380

data-challenges
advanced-exercises
🔰 The question table shows the preferences of factors over each other (used in MADM model like Elctre).
Published

March 24, 2026

Illustration for Omid - Challenge 380

Challenge Description

🔰 The question table shows the preferences of factors over each other (used in MADM model like Elctre).

Solutions

library(tidyverse)
library(readxl)

path <- "300-399/380/CH-380 Matrix Calculation.xlsx"
input <- read_excel(path, range = "B3:G8")
test <- read_excel(path, range = "J3:K8")

result = input %>%
  mutate(weight = 2^(rev(row_number()) - 1)) %>%
  pivot_longer(A:E, names_to = "Factor", values_to = "bit") %>%
  summarise(score = sum(bit * weight), .by = Factor) %>%
  arrange(score) %>%
  mutate(Rank = row_number()) %>%
  select(Rank, Factor)

all.equal(result, test)
#> [1] TRUE

  • 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

    • 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 numpy as np
import pandas as pd

path = "300-399/380/CH-380 Matrix Calculation.xlsx"
input_df = pd.read_excel(path, usecols="B:G", skiprows=2, nrows=5, index_col=0)
test = pd.read_excel(path, usecols="J:K", skiprows=2, nrows=5)

weights = 2 ** np.arange(len(input_df))[::-1]

result = (
    (input_df * weights[:, None])
    .sum()
    .sort_values()
    .reset_index(name="score")
    .rename(columns={"index": "Factor"})
    .assign(Rank=lambda df: df.index + 1)
    [["Rank", "Factor"]]
)

print(result.equals(test))
# True

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Builds the intermediate columns that drive the final result

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