Excel BI - Excel Challenge 705

🔰 Words Answer Expected b7 7b Z1e6 1Z6e 123abc abc123 2gL71Q g27LQ1

Illustration for Excel BI - Excel Challenge 705

Challenge Description

🔰 Words Answer Expected b7 7b Z1e6 1Z6e 123abc abc123 2gL71Q g27LQ1

Solutions

R Solution

library(tidyverse)
library(readxl)

path = "Excel/705 Swap Alphabets and Numbers.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")

result = input %>%
  mutate(rn = row_number()) %>%
  separate_rows(Words, sep = "") %>%
  filter(Words != "") %>%
  mutate(dig_alpha = ifelse(str_detect(Words, "[0-9]"), -1, 1)) %>%
  mutate(char_index = row_number(), .by = c("rn", "dig_alpha")) %>%
  mutate(
    rematch_index = char_index * dig_alpha,
    rematch_index2 = char_index * dig_alpha * -1
  )

r1 = result %>%
  left_join(
    result,
    by = c(
      "rematch_index" = "rematch_index2",
      "char_index" = "char_index",
      "rn" = "rn"
    )
  ) %>%
  summarise(
    Words = paste(Words.x, collapse = ""),
    Words2 = paste(Words.y, collapse = ""),
    .by = c("rn")
  )

all.equal(r1$Words2, test$`Answer Expected`)
#> True

R Analysis

• Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
• Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
• 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 elegant part is how little code is needed once the correct intermediate representation is chosen.

Python Solution

import pandas as pd
import re

path = "705 Swap Alphabets and Numbers.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10, names=["Words"])
test = pd.read_excel(path, usecols="B", nrows=10, names=["Answer Expected"])

result = (
    input.assign(rn=range(1, len(input) + 1))
    .assign(Words=input["Words"].str.split(""))
    .explode("Words")
    .query("Words != ''")
    .assign(
        dig_alpha=lambda df: df["Words"].apply(lambda x: -1 if re.match(r"[0-9]", x) else 1)
    )
    .assign(char_index=lambda df: df.groupby(["rn", "dig_alpha"]).cumcount() + 1)
    .assign(
        rematch_index=lambda df: df["char_index"] * df["dig_alpha"],
        rematch_index2=lambda df: df["char_index"] * df["dig_alpha"] * -1
    )
)

r1 = (
    result.merge(
        result,
        left_on=["rematch_index", "char_index", "rn"],
        right_on=["rematch_index2", "char_index", "rn"],
        suffixes=(".x", ".y")
    )
    .groupby("rn")
    .agg(
        Words=("Words.x", lambda x: "".join(x)),
        Words2=("Words.y", lambda x: "".join(x))
    )
    .reset_index()
)

print(r1['Words2'].equals(test['Answer Expected'])) # True

Python

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.

Related Challenges

• Excel Challenge 706
• Excel Challenge 707
• Excel Challenge 708