Puzzles no. 559–563
Author: ExcelBI
All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.
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.
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")
output = input %>% mutate(across(everything(), ~cummax(.)))
all.equal(output, test, check.attributes = FALSE) #> [1] TRUE
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.
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` = ""))
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))
all.equal(result$result, test$`Answer Expected`, check.attributes = FALSE) #> [1] TRUE
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.
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))))
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)))
all.equal(result, test, check.attributes = FALSE) # [1] TRUE
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.
library(tidyverse) library(readxl) path = "Excel/562 One Child Palindromes.xlsx" test = read_excel(path, range = "A1:A1001")
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)
all.equal(test$`Answer Expected`, result$palindromes, check.attributes = FALSE) # [1] TRUE
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.
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")
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)
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.