R Solution for Excel Puzzles

Puzzles no. 559–563

Puzzles

Author: ExcelBI

All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Puzzle #559

Sometimes we need to make work on columns. It technically just like working on lists, and look what we need to do with the lists today. It is called running maximum. We usually have some contact with running sum or average, but running maximum is little bit less popular. It is not returning single digit, but similarly like in cumsum, all sequence of numbers in which for certain element we need to aggregate in a way all preceeding elements (in sum — we need to sum them up, in average — mean, and in maximum always choose max from all preceeding). It is pretty easy in R so check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/559 Max of first N elements.xlsx"
input = read_excel(path, range = "A2:D13")
test  = read_excel(path, range = "F2:I13")

Transformation

output = input %>% 
  mutate(across(everything(), ~cummax(.))) 

Validation

all.equal(output, test, check.attributes = FALSE)
#> [1] TRUE

Puzzle #560

Lets think about words as sandwiches… today we need to find all sandwiches on our table. Oh, no, get back to Excel… do not think about pastrami sandwiches. We need to find every combination of one or more vowels in between of consonants. I found one obstacle in this task, but also the solution how to extract also those combinations that are overlapping with another valid combinations like rot and tor in rotor.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/560 Vowels between Consonants.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:B10") %>% replace_na(list(`Answer Expected` = ""))

Transformation

extract_cvc_overlap <- function(input_string) {
  pattern <- "(?=([^aeiou][aeiou]+[^aeiou]))"
  str_match_all(input_string, pattern) %>%
    map_chr(~ paste(.[, 2], collapse = ", ")) %>%
    str_trim()
}

result = input %>%
  mutate(result = map_chr(Words, extract_cvc_overlap))

Validation

all.equal(result$result, test$`Answer Expected`, check.attributes = FALSE)
#> [1] TRUE

Puzzle #561

Today we are going to play stock trading in small scale. We have 9 assets and prices from 10 days, and all we need to do is to find most profitable scenario when to buy and sell. Of course we would do it probably faster manually then writing code, but we are here to write code. And if it is hard to solve it is even more satisfying. Check it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/561 Maximum Profit.xlsx"

input = read_excel(path, range = "A2:J11")
test  = read_excel(path, range = "K2:M11") %>%
  mutate(across(everything(), ~if_else(.x == "NP", NA_real_, as.numeric(.x))))

Transformation

process_row <- function(...){
  row <- c_across(everything())
  cell_list <- map(1:length(row), ~row[.x:length(row)])
  df_pairs <- map_dfr(1:length(cell_list), function(i) {
    tibble(
      from = rep(row[i], length(cell_list[[i]]) - 1),
      to = cell_list[[i]][-1]
    )
  })
  df_pairs <- df_pairs %>%
    mutate(diff = to - from)
  max_pair <- df_pairs %>%
    slice_max(diff, with_ties = FALSE)
  return(list(
    max_diff = max_pair$diff,
    from_value = max_pair$from,
    to_value = max_pair$to
  ))
}

result <- input %>%
  rowwise() %>%
  mutate(result = list(process_row(across(everything())))) %>%
  mutate(
    Buy = result$from_value,
    Sell = result$to_value,
    Profit = result$max_diff
  ) %>%
  ungroup() %>%
  select(Buy, Sell, Profit) %>%
  mutate(across(everything(), ~if_else(Profit <= 0, NA_real_, .x)))

Validation

all.equal(result, test, check.attributes = FALSE)
# [1] TRUE

Puzzle #562

Palindromes, palindromes… I think Vijay A. Verma wouldn’t find the end of realm of palindromes anytime soon. Today our topic is One child palindrome. What does it mean that number has child? It means that substring of number is divisible by length of such original number. So if we want number with one child we need to find one that among all substrings has only one that is divisible by length of original number. But we have to mix it also with properties of palindrome and find 1000 first numbers with all those properties together.

It is pretty slow solution, but working fine.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/562 One Child Palindromes.xlsx"
test = read_excel(path, range = "A1:A1001")

Transformation

has_one_child <- function(n) {
  nchar = nchar(n)
  if (nchar == 1) {
    return(FALSE)
  }
  grid_coord = expand.grid(1:nchar, 1:nchar)
  substrings = apply(grid_coord, 1, function(x) {
    substr(n, x[1], x[2])
  }) %>%
  as.numeric() %>%
  .[!is.na(.) & . != 0] %>%
  unique()
  
  substrings = substrings[substrings %% nchar == 0]
  return(length(substrings) == 1)
}

generate_all_palindromes <- function(num_digits) {
  if (num_digits < 1) {
    stop("Number of digits must be at least 1")
  }
  if (num_digits == 1) {
    return(0:9)
  }
  half_digits <- ceiling(num_digits / 2)
  start_num <- 10^(half_digits - 1)
  end_num <- 10^half_digits - 1
  palindromes <- vector("integer", length = 0)
  
  for (i in start_num:end_num) {
    num_str <- as.character(i)
    rev_str <- paste0(rev(strsplit(num_str, "")[[1]]), collapse = "")
    if (num_digits %% 2 == 0) {
      palindrome_str <- paste0(num_str, rev_str)
    } else {
      palindrome_str <- paste0(num_str, substring(rev_str, 2))
    }
    palindromes <- c(palindromes, as.integer(palindrome_str))
  }
  return(palindromes)
}


palindrome_df <- tibble(num_digits = 1:9) %>%
  mutate(palindromes = map(num_digits, generate_all_palindromes))
res = palindrome_df %>%
  unnest(cols = c(palindromes)) %>%
  mutate(palindromes = as.integer(palindromes),
         has_one_child = map_lgl(palindromes, has_one_child))
result = res %>%
  filter(has_one_child == TRUE, palindromes > 10) %>%
  head(1000) %>%
  select(palindromes)

Validation

all.equal(test$`Answer Expected`, result$palindromes, check.attributes = FALSE)
# [1] TRUE

Puzzle #563

Some time ago we did similar thing but other way. When we have some range notations, we needed to split it to numbers within range, but this time we need to do another way. If we find consecutive numbers in groups, we just collapse them into range. Find out how I did it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/563 Bands of Numbers.xlsx"
input = read_excel(path, range = "A2:B17")
test  = read_excel(path, range = "D2:F6")

Transformation

result = input %>%
  mutate(Group = cumsum(c(1, diff(Numbers)) != 1), .by = Product) %>%
  mutate(Band = ifelse(n() == 1, paste0(Numbers), paste0(Numbers[1], "-", Numbers[n()])), 
         .by = c(Product, Group)) %>%
  summarise(Bands = paste0(unique(Band), collapse = ", "), 
            Count = n_distinct(Band), 
            .by = Product)

Validation

all.equal(result, test, check.attributes = FALSE)
# [1] TRUE

Feel free to comment, share and contact me with advices, questions and your ideas how to improve anything. Contact me on Linkedin if you wish as well.
On my Github repo there are also solutions for the same puzzles in Python. Check it out!

R Solution for Excel Puzzles was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.