R Solution for Excel Puzzles

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.