Puzzles no. 554–558
Author: ExcelBI
All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.
Another interesting number called after Indian mathematician. We need to find first 50 Kaprekar numbers of any order. What does it mean? We need to find numbers which square cut in any point (after first, second or any other digit) give us two numbers that sums up to original number. So we need to cut numbers (not divide, not subtract, but cut). It is terribly slow if we iterate over long numbers treating it as characters. But today I found out how to do it using numbers only. Check it out.
PS. And I threw all hands on board, which mean that I used parallel computing.
library(tidyverse) library(readxl) library(parallel) path = "Excel/554 Kaprekar Numbers.xlsx" test = read_excel(path, range = "A1:A51")
check_kaprekar_fast = function(n) { nsqr = n^2 digits = floor(log10(nsqr)) + 1 for (split_pos in 1:(digits - 1)) { right_part = nsqr %% 10^split_pos left_part = nsqr %/% 10^split_pos if (right_part > 0 && left_part + right_part == n) { return(TRUE) } } return(FALSE) } parallel_kaprekar_check = function(n_values) { num_cores = detectCores() - 1 cl = makeCluster(num_cores) clusterExport(cl, "check_kaprekar_fast") result = parLapply(cl, n_values, check_kaprekar_fast) stopCluster(cl) return(unlist(result)) } n_values = 4:1000000 kaprekar_flags = parallel_kaprekar_check(n_values) df = data.frame(n = n_values, is_kaprekar = kaprekar_flags) %>% filter(is_kaprekar) %>% head(50) %>% select(n)
all.equal(df, test, check.attributes = FALSE) # TRUE
It looks like somebody wrote down cities to visit, but it would be to easy just to sort them. We are given special way of sorting first row should be sorted descending, second — ascending, and so on even and odd rows. This is quite tricky, but let me show you how I did it.
library(tidyverse) library(readxl) path = "Excel/555 Order Cities.xlsx" input = read_excel(path, range = "A1:E19") test = read_excel(path, range = "G2:K19", col_names = FALSE) names(test) = c("1", "2", "3", "4", "5")
result = input %>% mutate(rn = row_number()) %>% select(rn, everything()) %>% pivot_longer(-rn, names_to = "key", values_to = "value") %>% group_by(rn) %>% arrange( rn, desc(if_else(rn %% 2 == 0, value, NA_character_)), if_else(rn %% 2 != 0, value, NA_character_) ) %>% mutate(rn2 = row_number(), key = if_else(is.na(value), NA, key)) %>% ungroup() %>% select(-value) %>% pivot_wider(names_from = rn2, values_from = key) %>% select(-rn)
all.equal(result, test) # [1] TRUE
We built many structures so far, and pyramids along them were numerous as well. Today we have triangle made of triangular numbers. Each step up is made of next element of sequence of triangular numbers. We already discussed this kind of numbers many time. So we could say many things are the same as many time before. And as before I will do it using matrix.
library(tidyverse) library(readxl) path = "Excel/556 Generate Triangle Cumsum.xlsx" input = read_excel(path, range = "A1:A1", col_names = FALSE) %>% pull() test = read_excel(path, range = "B2:T11:", col_names = FALSE) %>% as.matrix()
M = matrix(NA_real_, nrow = input, ncol = 2 * input - 1) p = 1:input %>% cumsum() for (i in 1:10) { M[i, (input - i + 1):(input + i - 1)] = rev(p)[i] }
all.equal(M, test, check.attributes = FALSE) # TRUE
We have three main tasks today all based on fact that RegEx is pretty freshly available in Excel. So let get heavy weapon called regular expressions ready for our puzzle today.
library(tidyverse) library(readxl) path = "Excel/557 Regex Challenges 2.xlsx" input = read_excel(path, range = "A1:A6") test = read_excel(path, range = "C1:C6") %>% mutate(`Answer Expected` = as.numeric(`Answer Expected`))
q1 = input %>% filter(row_number() == 1) %>% mutate(Answer = str_extract(String, "\\d+(?!.*\\d)") %>% as.numeric()) q2 = input %>% filter(row_number() %in% c(2, 3)) %>% mutate(Answer = str_detect(String, "(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)") %>% as.numeric()) q3 = input %>% filter(row_number() %in% c(4, 5)) %>% mutate(Answer = str_detect(String, "^(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9])(?=.*[^A-Za-z0-9])(?=\\S+$).{8,}$") %>% as.numeric()) answer = bind_rows(q1, q2, q3)
all.equal(answer$Answer, test$`Answer Expected`, check.attributes = FALSE) #> [1] TRUE
I like the way how structures similar to Python dictionaries can be packed and unpacked to series of keys and values. And we have it today as our puzzle. We need to split dictionary type string to strings made of keys and values respectivelly.
library(tidyverse) library(readxl) path = "Excel/558 Unpack Dictionary.xlsx" input = read_excel(path, range = "A2:A7") test = read_excel(path, range = "B2:C7")
result = input %>% mutate(rn = row_number()) %>% separate_rows(Dictionary, sep = ", ") %>% separate(Dictionary, c("Key", "Value"), sep = ":|;", extra = "merge") %>% summarise(Key = str_c(Key, collapse = ", "), Value = str_c(Value, collapse = ", "), .by = rn) %>% select(Key, Value)
all.equal(test, result) #> [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.