News
Entertainment
Science & Technology
Life
Culture & Art
Hobbies
News
Entertainment
Science & Technology
Culture & Art
Hobbies
8 | Follower
Puzzles no. 434–438PuzzlesAuthor: ExcelBIAll files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.Puzzle #434Sometimes 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 20x5 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 datalibrary(tidyverse)library(stringi)library(readxl)test = read_excel("Excel/434 Generate the Column Headers Matrix.xlsx", range = "A2:E21", col_names = FALSE) %>% as.matrix()Transformationcol_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 % filter(indices %in% index) %>% pull(cols)result = matrix(result_df, nrow = 20, ncol = 5, byrow = FALSE)Validationall.equal(result, test, check.attributes = FALSE)# [1] TRUEPuzzle #435And 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.TransformationM = 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 #436Finally 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 datalibrary(tidyverse)library(readxl)library(primes)library(gtools)test = read_excel("Excel/436 Pandigital Primes.xlsx", range = "A1:A101")Transformationgenerate_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)Validationidentical(result$numbers, test$`Answer Expected`)# [1] TRUEPuzzle #437Bifid 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 datalibrary(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")Transformationcreate_coding_square % 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`)Validationidentical(result, test)# [1] TRUEPuzzle #438And 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 datalibrary(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")Transformationfind_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))Validationidentical(result$`Answer Expected`, test$`Answer Expected`)# [1] TRUEFeel 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.
Join our workshop on Conducting Simulation Studies in R, which is a part of our workshops for Ukraine series! Here’s some more info: Title: Conducting Simulation Studies in R Date: Thursday, May 23rd, 18:00 – 20:00 CEST (Rome, Berlin, Paris timezone) Speaker: Greg Faletto is a statistician and data scientist at VideoAmp, where he works … Continue reading Conducting Simulation Studies in R workshopConducting Simulation Studies in R workshop was first posted on April 22, 2024 at 3:00 pm.
Previously, I took advantage of a dataset that linked preprints to their published counterparts to look at the fraction of papers in a journal that are preprinted. This linkage can be used to answer other interesting questions. Such as: when do authors preprint their papers relative to submission? And does this differ by journal? There’s […]