Excel BI - Excel Challenge 913

🔰 913 Vowel Replacement.xlsx says: > Calculate the frequency of all vowels appearing in a string.

Illustration for Excel BI - Excel Challenge 913

Challenge Description

🔰 The prompt in 913 Vowel Replacement.xlsx says: Calculate the frequency of all vowels appearing in a string. Replace the last occurrence of a vowel with the frequency of that vowel. Earlier occurrences of that vowel must be removed. > > Example: Actuate: A:2, u:1, e:1 => ct12t1 The goal is to process each string so that:

Solutions

R Solution

library(tidyverse)
library(readxl)

path <- "Excel/900-999/913/913 Vowel Replacement.xlsx"
input <- read_excel(path, range = "A1:A10")
test <- read_excel(path, range = "B1:B10")

process <- function(x) {
  vowels <- c("a", "e", "i", "o", "u")

  data.frame(col1 = x) %>%
    separate_rows(col1, sep = "") %>%
    mutate(
      low = tolower(col1),
      is_v = low %in% vowels,
      grp = if_else(is_v, low, paste0("c_", row_number()))
    ) %>%
    group_by(grp) %>%
    mutate(
      freq = if_else(is_v, row_number(), 0L),
      keep = !is_v | freq == max(freq)
    ) %>%
    ungroup() %>%
    mutate(
      col1 = case_when(
        !keep ~ "",
        is_v ~ as.character(freq),
        TRUE ~ col1
      )
    ) %>%
    summarise(col1 = paste0(col1, collapse = "")) %>%
    pull(col1)
}

result <- input %>%
  mutate(`Answer Expected` = map_chr(Names, process))

all.equal(result$`Answer Expected`, test$`Answer Expected`)

R Analysis

• Logic: Split the word into individual characters.; Mark which characters are vowels.; Group vowel characters by vowel identity..
• Strengths: The clever part is that the transformation depends on both grouping and order.
• Areas for Improvement: The approach assumes the workbook structure and naming conventions stay stable, so any changed input shape would need minor adjustments.
• Gem: For the word:

Python Solution

import pandas as pd

path = "Excel/900-999/913/913 Vowel Replacement.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10)

def process(x):
    vowels = {"a", "e", "i", "o", "u"}
    df = pd.DataFrame({'col1': list(x)})
    df['low'] = df['col1'].str.lower()
    df['is_v'] = df['low'].isin(vowels)
    df['grp'] = df.apply(lambda row: row['low'] if row['is_v'] else f"c_{row.name + 1}", axis=1)
    grouped = df.groupby('grp', group_keys=False)
    df['freq'] = grouped.cumcount() + 1
    df['keep'] = df['is_v'] & (df['freq'] == grouped['freq'].transform('max')) | ~df['is_v']
    df['col1'] = df.apply(
        lambda row: "" if not row['keep'] else str(row['freq']) if row['is_v'] else row['col1'], axis=1
    )
    return "".join(df['col1'])

input['Answer Expected'] = input['Names'].apply(process)

result = input['Answer Expected'].tolist() == test['Answer Expected'].tolist()
print(result)

Python

The Python version follows the same structure: break the string into characters.; label vowels and consonants..

Difficulty Level

Easy

Once the core pattern is recognized, the implementation is short and direct.

Related Challenges

• Excel Challenge 922
• Excel Challenge 928
• Excel Challenge 906