R Solution for Excel Puzzles
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
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.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.