Want to share your content on R-bloggers? click here if you have a blog, or here if you don’t.
Puzzles no. 434–438
Puzzles
Author: ExcelBI
All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.
Puzzle #434
Sometimes challenges we are facing have not really much with real world problems. And no. 434 is one of them. Our task this time was to make matrix 20×5 with names of columns of Excel spreadsheet. But if they were in normal order it would be to easy. We had to make consecutively longer jumps, firstly by one column, then skip one and jump two, and then increase skipped columns each time. Wow, that was a thing. Look yourself.
Loading libraries and data
library(tidyverse)
library(stringi)
library(readxl)
test = read_excel("Excel/434 Generate the Column Headers Matrix.xlsx",
range = "A2:E21", col_names = FALSE) %>%
as.matrix()
Transformation
col_names = c(LETTERS, do.call(paste0, expand.grid(LETTERS, LETTERS)),
do.call(paste0, expand.grid(LETTERS, LETTERS, LETTERS))) %>%
map_chr(~stri_reverse(.))
columns = data.frame(cols = col_names) %>%
mutate(indices = 1:nrow(.))
index <- accumulate(1:99, ~ .x + .y, .init = 1)
result_df = columns %>%
filter(indices %in% index) %>%
pull(cols)
result = matrix(result_df, nrow = 20, ncol = 5, byrow = FALSE)
Validation
all.equal(result, test, check.attributes = FALSE) # [1] TRUE
Puzzle #435
And again… Task for showing off skill, not something really useful in analytics. But of course we love showing off. So let draw boat with characters on a matrix.
Transformation
M = matrix(NA, nrow = 12, ncol = 23)
for (i in 1:7) {
M[i, ] = c(rep(NA, (23 - 2*i + 1)/2), rep('+', 2*i - 1), rep(NA, (23 - 2*i + 1)/2))
}
for (i in 8) {
M[i, ] = c(rep(NA, (23 - 2*i + 1)/2), rep('=', 2*i - 1), rep(NA, (23 - 2*i + 1)/2))
}
for (i in 9:12) {
M[i, ] = c(rep(NA, i - 9), rep('x', 23 - 2*(i - 9)), rep(NA, i - 9))
}
as.data.frame(M)
Puzzle #436
Finally some numbers to play with. Are you familiar with pandigital numbers? I’ve met them for the first time too. They are numbers that if they had 3 digits, consists of only 1, 2 and 3 (in various orders). So for n digits there would always go numbers from 1 to n without repetitions. But we have one more twist here. We need to generate sequence of first 100 pandigitals that are also primes. So little bit bruteforcely — I generated all pandigitals up to seven digits and then filtered only primes. Check it out.
Loading libraries and data
library(tidyverse)
library(readxl)
library(primes)
library(gtools)
test = read_excel("Excel/436 Pandigital Primes.xlsx", range = "A1:A101")
Transformation
generate_pandigital = function(n) {
digits = 1:n
digits = permutations(n,n)
digits = apply(digits, 1, function(x) as.numeric(paste(x, collapse = "")))
return(digits)
}
df = data.frame(numbers = NA)
for (i in 1:7) {
pandigitals = generate_pandigital(i)
df = rbind(df, data.frame(numbers = pandigitals))
}
result = df %>%
mutate(is_prime = map_lgl(numbers, is_prime)) %>%
filter(is_prime) %>%
head(100)
Validation
identical(result$numbers, test$`Answer Expected`) # [1] TRUE
Puzzle #437
Bifid ciphering is back, but with twist. This time our encoding process needs to include keyword which letters are shifting coding square. But it was not really hard and was mainly about adjusting code from puzzle #432 from last episode.
Loading libraries and data
library(tidyverse)
library(readxl)
input = read_excel("Excel/437 Bifid Cipher_Part 2.xlsx", range = "A1:B10")
test = read_excel("Excel/437 Bifid Cipher_Part 2.xlsx", range = "C1:C10")
Transformation
create_coding_square <- function(keyword) {
p1 = str_split(keyword %>% str_replace(pattern = "j", replacement = "i"), "")[[1]] %>%
unique()
p2 = setdiff(letters, c("j", p1))
Letters = c(p1, p2)
df = as.data.frame(matrix(Letters, nrow = 5, byrow = TRUE)) %>%
pivot_longer(cols = everything()) %>%
mutate(column = as.numeric(str_extract(name, "[0-9]+")),
row = rep(1:5,each = 5)) %>%
select(-name)
return(df)
}
bifid_encode = function(text, keyword) {
coding_square = create_coding_square(keyword)
text = str_replace_all(text, "J", "I")
chars = str_split(text, "")[[1]]
coords = map_dfr(chars, function(char) {
coords = coding_square %>%
filter(value == char) %>%
select(row, column)
return(coords)
})
coords = paste0(coords$row, coords$column) %>%
str_split("", simplify = TRUE) %>%
as.numeric() %>%
matrix(ncol = 2, byrow = TRUE) %>%
as.data.frame()
encoded = coords %>%
left_join(coding_square, by = c("V1" = "row", "V2" = "column")) %>%
pull(value) %>%
paste0(collapse = "")
return(encoded)
}
result = input %>%
mutate(`Answer Expected` = map2_chr(`Plain Text`,Keywords, bifid_encode)) %>%
select(`Answer Expected`)
Validation
identical(result, test) # [1] TRUE
Puzzle #438
And another part of electrical riddle. And again we are basing on one of previous tasks (this time #420). Basing on colourful bands on resistors we have to calculate their resistance. But it was previous task. Today’s addition is to change notation of numbers (do not worry, still in decimals), for them to have Kilo Ohms, Mega Ohms and Giga Ohms. We needed some adjustments and one new function. Check all of this code.
Loading libraries and data
library(tidyverse)
library(readxl)
input1 = read_excel("Excel/438 Resistor Value_v2.xlsx", range = "A1:C11")
input2 = read_excel("Excel/438 Resistor Value_v2.xlsx", range = "E1:E10")
test = read_excel("Excel/438 Resistor Value_v2.xlsx", range = "F1:F10")
Transformation
find_resistance = function(bands, input) {
codes = input
pairs = strsplit(bands, "")[[1]]
pairs = matrix(pairs, ncol = 2, byrow = TRUE) %>%
as.data.frame() %>%
unite("pair", V1, V2, sep = "") %>%
left_join(codes, by = c("pair" = "Code")) %>%
mutate(nr = rev(row_number()))
last = pairs[nrow(pairs),] %>%
mutate(res = 10^Value) %>%
pull(res)
pairs_wol = pairs[-nrow(pairs),] %>%
mutate(res = Value*10^(nr-2)) %>%
pull(res)
final_res = sum(pairs_wol) * last
return(final_res)
}
convert_to_notation = function(x) {
case_when(
x >= 1e9 ~ paste0(x/1e9, " G Ohm"),
x >= 1e6 ~ paste0(x/1e6, " M Ohm"),
x >= 1e3 ~ paste0(x/1e3, " K Ohm"),
TRUE ~ paste0(x, " Ohm"))
}
result = input2 %>%
mutate(`Answer Expected` = map_dbl(`Color Bands`, find_resistance, input1)) %>%
mutate(`Answer Expected` = map_chr(`Answer Expected`, convert_to_notation))
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
Analysis of R Solutions for Excel Puzzles
The text introduces a collection of five puzzles (numbered 434 through 438) and their solutions using R programming language. Author “ExcelBI” from “Numbers around us” on Medium shares these insights with readers, encouraging them to consider how R can be used to resolve unusual or abstract tasks. The key points, long-term implications, and possible future developments for each puzzle are considered in turn.
Puzzle #434: Manipulating Excel Spreadsheet Columns
The first puzzle involves creating a unique matrix formation with Excel spreadsheet column names. These formed a 20×5 matrix with challenging constraints on their arrangement. The R script subsequently used libraries like ‘tidyverse’, ‘stringi’, and ‘readxl’ to solve the problem effectively.
Implications & Future Directions
The exercise highlights the versatility of R in manipulating data structures and can lead to future developments in more complex data transformation tasks. The key lies in understanding how applying consecutive operations ‘map_chr’ and ‘accumulate’ can simplify problem-solving.
Actionable Advice
R programmers should focus on libraries like ‘tidyverse’, as they offer many easy-to-use functions that can simplify complex data manipulation tasks.
Puzzle #435: Drawing Images with a Matrix
The second puzzle involves creating an image, more specifically a boat, using characters arranged in a matrix, which was done using the rep function in R.
Implications & Future Directions
Drawing images with a matrix could have implications in creating simple text-based designs or potentially be integrated into data visualization functions. Mastery of matrix operations in R is undoubtedly a valuable skill.
Actionable Advice
Users should continue advancing in techniques and operations that involve matrices and other complex data structures, as they are key in advanced data representation.
Puzzle #436: Generating Pandigital Primes
Puzzle #436 marks a departure into number theory, focusing on pandigital numbers and prime numbers. The challenge in this task was to generate the first 100 prime pandigital numbers. A function ‘generate_pandigital’ was defined, and then used inside a ‘for’ loop to generate and append pandigitals for each number from 1 to 7. Additional R libraries used in this task include ‘gtools’ and ‘primes’.
Implications & Future Directions
The discussed process shows the potential of R in solving complex mathematical problems, which could serve as a foundation for more advanced numerical problem solving and algorithm development in the future.
Actionable Advice
Programmers should look at integrating R with mathematical or scientific programming for more sophisticated number manipulations.
Puzzle #437: Bifid Ciphering with a Twist
The next puzzle is based on the concept of ‘Bifid’ ciphering, which is a form of monoalphabetic substitution cipher. The challenge was to encode a given text using a specific keyword. The R script used libraries like ‘tidyverse’ and ‘readxl’ to create a ‘coding_square’ and ‘bifid_encode’ function to solve the problem.
Implications & Future Directions
The exercise could serve as a foundation for developing more advanced cryptography algorithms, and opens up possibilities for using R in fields like cyber security and data privacy.
Actionable Advice
The intersecting capabilities of R in linguistic processing and cryptography can be explored further, especially by developers working on encryption techniques and cybersecurity applications.
Puzzle #438: Decoding Electrical Resistance
The last of the five puzzles relates to electrical resistance. Based on the colourful bands on resistors, the resistance value was calculated and appropriately symbolized in Ohms, Kilo-Ohms or Mega-Ohms etc. The R script used libraries like ‘tidyverse’ and ‘readxl’ to create ‘find_resistance’ and ‘convert_to_notation’ functions to solve the problem.
Implications & Future Directions
The ability of R to solve this problem highlights its potential usability in even more diverse fields, like electrical engineering. Future developments may involve using R in solving complex engineering problems, design and simulation tasks.
Actionable Advice
R programmers working on solving complex practical problems should explore cross-domain application of R, considering its versatility as demonstrated by these puzzles.
These puzzles give us a glimpse into the depth and breadth of what we can accomplish with R. From day-to-day tasks to advanced algorithmic challenges, R continues to prove its usefulness to both pros and novices alike.