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. 404–408
Puzzles
Author: ExcelBI
All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.
Puzzle #404
Can analyst make something that looks good? Of course… Can analyst draw with numbers? Once more yeah. But today, like some times in past already, we have another way. I usually name making charts and dashboards — drawing or painting with numbers. Not today. We just recreate one specific graphic filling fields of spreadsheet (or in our case, make this graphic in console). And as you see above it is… Star-Spangled Banner aka flag of the USA.
Load libraries and data
library(tidyverse) library(readxl) test = read_excel("Excel/404 Generate US ASCII Flag.xlsx", range = "A1:AL15", col_names = FALSE, .name_repair = "unique") %>% as.matrix() # remove attribute "names" from matrix attr(test, "dimnames") = NULL result = matrix(NA, nrow = 15, ncol = 38)r
Transformation
# border of flag result[1,] = "-" result[15,] = "-" result[2:14,1] = "|" result[2:14,38] = "|" # stripe section for (i in 2:14){ for (j in 2:37){ if (i %% 2 == 0){ result[i,j] = 0 } else { result[i,j] = "1" } } } # star section for (i in 2:10){ for (j in 2:12){ if (i %% 2 == 0){ if (j %% 2 == 0){ result[i,j] = "*" } else { result[i,j] = NA } } else { if (j %% 2 == 0){ result[i,j] = NA } else { result[i,j] = "*" } } } }
Validation
identical(result, test) # [1] TRUE
Puzzle #405
Did you know sandwich numbers? That is that unique kind of numbers that as both neighbours has prime numbers, so they are like between two slices of toast bread. And our task is to find first 100 of sandwich numbers together with their “breads” aka neighbouring primes.
Load libraries and data
library(tidyverse) library(readxl) test = read_excel("Excel/405 Sandwich Numbers.xlsx", range = "A1:C101") %>% janitor::clean_names()
Transformation
is_prime <- function(x) { if (x <= 1) return (FALSE) if (x == 2 || x == 3) return (TRUE) if (x %% 2 == 0) return (FALSE) for (i in 3:sqrt(x)) { if (x %% i == 0) return (FALSE) } TRUE } # of course I could use primes package, but I decided otherwise :D is_sandwich <- function(x) { is_prime(x-1) && is_prime(x+1) } find_first_n_sandwich_numbers <- function(no) { keep(1:10000, is_sandwich) %>% unlist() %>% head(no) } a = find_first_n_sandwich_numbers(100) check = tibble(sandwich_number = a) %>% mutate(before_number = sandwich_number - 1, after_number = sandwich_number + 1) %>% select(2,1,3)
Validation
all.equal(test, check) # [1] TRUE
Puzzle #406
I suppose that in every educational system at least once Pythagorean Theorem is mentioned. In this puzzle given area and length of hypotenuse we have to find length of other two sides of right angled triangle. Of course there probably is some formula to do it at once, but I wanted to show you step by step way to do it. We are gonna use library numbers to use very useful function divisors. Otherwise we would have to check every combination of numbers to find numbers behind area of triangle.
Load libraries and data
library(tidyverse) library(readxl) library(numbers) input = read_excel("Excel/406 Right Angled Triangle Sides.xlsx", range = "A2:B10") %>% janitor::clean_names() test = read_excel("Excel/406 Ri
Transformation
process_triangle = function(area, hypotenuse) { ab = 2 * area ab_divisors = divisors(ab) grid = expand_grid(a = ab_divisors, b = ab_divisors) %>% mutate(r = a * b, hyp = hypotenuse, hyp_sq = hyp**2, sides_sq = a**2+b**2, check = hyp_sq == sides_sq, base_shorter = a < b) %>% filter(check, base_shorter) %>% select(base = a, perpendicular = b) return(grid) } result = input %>% mutate(res = map2(area, hypotenuse, process_triangle)) %>% unnest(res) %>% select(3:4)
Validation
identical(result, test) # [1] TRUE
Puzzle #407
I like cyphering puzzles and I am really happy that we have one again. Today we merge 2 types of cyphers: Ceasar and Mirror, so we have reverse and shift coded text to succeed. Let’s check how it went.
Load libraries and data
library(tidyverse) library(readxl) input = read_excel("Excel/407 Mirror Cipher.xlsx", range = "A1:B10") %>% janitor::clean_names() test = read_excel("Excel/407 Mirror Cipher.xlsx", range = "C1:C10") %>% janitor::clean_names()
Transformation
code = function(text, shift) { if (shift == 0) { keycode = letters } else { keycode = c(letters[(26-shift+1):26],letters[1:(26-shift)]) } keytable = tibble(letters = letters, code = keycode) chars = str_split(text, "")[[1]] %>% rev() tab = tibble(text = chars) %>% left_join(keytable, by = c("text" = "code")) %>% mutate(letters = if_else(is.na(letters), " ", letters)) %>% select(letters) %>% pull() %>% str_c(collapse = "") return(tab) } result = input %>% mutate(answer_expected = map2_chr(plain_text, shift, code))
Validation
identical(result$answer_expected, test$answer_expected) # [1] TRUE
Puzzle #408
Time: physics, math, eternity… but does time have any geometry? Stephen Hawking probably would say something about it, but we have much easier issue. We only need to check geometry of clock face. There are two or three hands on it. As long as we present time as cycles, we use circle presenting this cycle and positions of hands on the face of round, circular face of clock are enabling us to read time measurements. So lets check what angle hands presents at specific times of a day.
Load libraries and data
library(tidyverse) library(readxl) input = read_excel("Excel/408 Angle Between Hour and Minute Hands.xlsx", range = "A1:A10") test = read_excel("Excel/408 Angle Between Hour and Minute Hands.xlsx", range = "B1:B10")
Transformation
angle_per_min_hh = 360/(60*12) angle_per_min_mh = 360/60 result = input %>% mutate(time = as.character(Time), Time = str_extract(time, "\\s\\d{2}:\\d{2}")) %>% separate(Time, into = c("hour","mins"), sep = ":") %>% mutate(hour = as.numeric(hour), mins = as.numeric(mins), hour12 = hour %% 12, period_hh = hour12*60 + mins, period_mh = mins, angle_hh = period_hh * angle_per_min_hh, angle_mh = period_mh * angle_per_min_mh, angle_hh_to_mh = if_else(angle_hh > angle_mh, 360 - (angle_hh - angle_mh), angle_mh - angle_hh)) %>% select(answer_expected = angle_hh_to_mh) # there is probably single formula for this, # but I wanted to show you this step by step.
Validation
identical(result$answer_expected, test$`Answer Expected`) # [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.
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.