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, "sd{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.
Continue reading: R Solution for Excel Puzzles
Analyzing ExcelBI Puzzles and Their R Package Solutions
The article focuses on an interesting series of puzzles presented by ExcelBI and their solutions using the R programming language. Each puzzle presents a programming challenge and gives insights into how data analytics and programming skills can be used to solve real-world problems. While the author mostly uses R’s base packages, there’s also usage of external libraries like tidyverse, readxl, and numbers.
Key Points from the Puzzles
Puzzle #404
In the first puzzle, the task is to reproduce the flag of USA using matrix transformations in R based on a given pattern. This task shows the versatility of R programming in graphical work besides typical numerical analysis.
Puzzle #405
Puzzle #405 talks about finding a ‘sandwich numbers’, which are numbers sandwiched between two prime numbers. This interesting task reflects the power of mathematical functions in R, such as identifying prime numbers.
Puzzle #406
Using the Pythagorean theorem, the puzzle aims to find the length of two sides in a right-angled triangle given the area and hypotenuse. Comprising mathematical functions like area calculations and hypotenuse value derivation, this puzzle demonstrates the use of R programming in geometrical problems.
Puzzle #407
Puzzle #407 presents a text deciphering task involving the use of Caesar and mirror cyphers. This shows how R can be leveraged for decryption and encoding tasks, especially useful in cybersecurity.
Puzzle #408
Finally, the last puzzle has a task to find the angle between hour and minute hands at a specific time. The problem uses mathematical transformations and notion of time to solve a real-world problem.
Future Implications and Developments
These puzzles showcase the power, versatility, and breadth of the R programming language. Not restricted to just statistical analyses, users of R can leverage its features to solve a wide range of problems, from graphical reproductions and geometrical calculations to coding cyphers.
It is expected that the role of R will continue to expand, including into non-traditional areas, given the language’s open-source nature and active community of contributors.
Actionable Advice
- Enhance R Skills: The ability to handle diverse problems using R will likely become an increasingly valuable skill in the future. Therefore, learning R and improving programming skills can open up new opportunities.
- Look Beyond Analytics: R isn’t just a tool for data analytics. These puzzles show that R can be used in a variety of tasks. Focus on understanding the principles and functionality of R to unlock its full potential.
- Engage with the Community: The R community is a rich resource for learning and problem-solving. Don’t hesitate to engage, ask questions, and contribute when you can.