R Solution for Excel Puzzles

Puzzles no. 419–423

Puzzles

Author: ExcelBI

All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Puzzle #419

What do you think hearing reversely divisible? My first thougth was… hey, palindromes are perfectly divisible backwords. But challenge was not that simple, we needed to find numbers that are reversely divisible and are not palindromes.

When I found out that we need to have first 12 of numbers with this property, I said “Ok that should be easy”. Just reverse, divide, confirm that they are not palindromes. But when I spotted that 12th number has 8 digits, I realised that it would last centuries to iterate 10⁹ numbers. So I did what I should do at the beginning — research. Few weeks ago I found out that there is that site out of mathematical cosmos — Online Encyclopedia of Integer Sequences (OEIS). There we can find many very interesting sequences, and guess what. There is also sequence of non-trivial (means do not include palindromes) reverse divisible numbers, and their properties.

So I used one of them. It is interesting that those numbers have two base numbers 1089 and 2178, and next such numbers are just “stuffed” with zeroes and nines. Nines are placed in middle of number (10989, 109989), while zeroes between them (217802178). So all iterations can be replaced with single string manipulation. I made 8 numbers for each combination of zero or nine and two bases, and took 12 smallest of them. Look at this.

Load libraries and data

library(tidyverse)
library(readxl)

test = read_excel("Excel/419 Reverse Divisible & Not Palindromes.xlsx", range = "A1:A12")

Transformation

bases = c("1089", "2178")

insert9 = function(num_char,  max_length = 8) {
   
  fp = str_sub(num_char, 1,2)
  sp = str_sub(num_char, 3,4)
  
  sequence_of_nines <- map_chr(0:max_length, ~strrep("9", .x))

  generated_numbers <- map_chr(sequence_of_nines, ~paste0(fp, .x, sp))
    
  return(generated_numbers)
}

i9_1 =  insert9(bases[1], 8)
i9_2 =  insert9(bases[2], 8) 

insert0 = function(num_char,  max_length = 8) {
  sequence_of_zeroes <- map_chr(0:max_length, ~strrep("0", .x))
  generated_numbers <- map_chr(sequence_of_zeroes, ~paste0(num_char, .x, num_char))
  return(generated_numbers)
}

i0_1 =  insert0(bases[1], 8)
i0_2 =  insert0(bases[2], 8)

generated_numbers = c(i9_1, i9_2, i0_1, i0_2) %>%
  as.numeric() %>%
  sort() %>%
  head(11)

Validation

identical(generated_numbers, test$`Expected Answer`)
# [1] TRUE

Puzzle #420

I am not into electronics so I didn’t know what is it all about. I didn’t know that resistors can have colour bands to code their properties. But it is great puzzle to solve, so I needed to update my knowledge. Look how we solve resistor puzzle without any intelectual resistance.

Load libraries and data

library(tidyverse)
library(readxl)

input1 = read_excel("Excel/420 Resistor Value.xlsx", range = "A1:B11")
input2 = read_excel("Excel/420 Resistor Value.xlsx", range = "D1:D10")
test   = read_excel("Excel/420 Resistor Value.xlsx", range = "E1:E10")

Transformation

find_resistance = function(bands, input) {
    
    codes = input %>%
      mutate(code = 0:9)  
  
    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^code) %>%
      pull(res)
    
    pairs_wol = pairs[-nrow(pairs),] %>%
      mutate(res = code*10^(nr-2)) %>%
      pull(res)
    
    final_res = sum(pairs_wol) * last
    
  return(final_res)
}

result = input2 %>%
  mutate(`Answer Expected` = map_dbl(`Color Bands`, find_resistance, input1) %>% 
           as.character())

Validation

identical(result$`Answer Expected`, test$`Answer Expected`)
# [1] TRUE

Puzzle #421

How many diagonals square or square matrix has? Two, right? But in case of matrices we can also read another lines of numbers diagonally, just like lines on those boxes in ilustration. Let’s try to do it.

Load libraries and data

library(tidyverse)
library(readxl)

test1 = read_excel("Excel/421 Stack Diagonals.xlsx", range = "G2:H4", col_names = F)
test2 = read_excel("Excel/421 Stack Diagonals.xlsx", range = "G6:I10", col_names = F)
test3 = read_excel("Excel/421 Stack Diagonals.xlsx", range = "G12:J18", col_names = F)
test4 = read_excel("Excel/421 Stack Diagonals.xlsx", range = "G20:K28", col_names = F)

Transformation

extract_antidiagonals = function(matrix_size) {
  dim = sqrt(matrix_size)
  
  M = matrix(1:matrix_size, nrow=dim, ncol=dim)
  d = row(M)+col(M)
  x = split(M, d) 
  x = lapply(x, rev) %>%
    lapply(function(x) c(x, rep(NA, nrow(M) - length(x))) )
  
  N = matrix(nrow = length(x), ncol = ncol(M)) %>%
    as.data.frame()
  
  for (i in 1:length(x)) {
    N[i,] = x[[i]]
  }
  
  N = as_tibble(N)
  
  return(N)
}

Validation

extract_antidiagonals(4) == test1
extract_antidiagonals(9) == test2
extract_antidiagonals(16) == test3
extract_antidiagonals(25) == test4 

Puzzle #422

Time passes mercilessly. Just few weeks ago we had 29th of February, which itself happens only once every 4 years, because of leap year. Leap year is a construct that allows us to adjust our time measuring tools to exact time of Earth’s revolution around the Sun. But in different calendar systems there are slightly different rules. Of course most of the leap years are identical in gregorian and julian calendar. But our task today is to find all years till year 9999 that are leap in only one of these calendars.

Load libraries and data

library(tidyverse)
library(readxl)

test = read_excel("Excel/422 Leap Years in Julian and Gregorian.xlsx", range = "A1:A27")

Transformation

years = tibble(range = 1901:9999) 

is_greg_leap = function(year){
  if (year %% 4 == 0 && !year %% 100 == 0) {
    return(TRUE)
  } else if (year %% 100 == 0 && year %% 400 == 0) {
    return(TRUE)
  } else {
    return(FALSE)
  }
}

is_revjul_leap = function(year){
  if (year %% 4 == 0 && !year %% 100 == 0) {
    return(TRUE)
  } else if (year %% 100 == 0 && year %% 900 %in% c(200, 600)) {
    return(TRUE)
  } else {
    return(FALSE)
  }
}

leap_years = years %>%
  mutate(greg_leap = map_lgl(range, is_greg_leap),
         revjul_leap = map_lgl(range, is_revjul_leap)) %>%
  filter(greg_leap != revjul_leap) %>%
  select(range)

Validation

all.equal(test$`Expected Answer`, leap_years$range)
# [1] TRUE

Puzzle #423

Sometimes there are cases when our host is giving us the same dataset and almost the same task. And key difficulty is usually in word “almost”. This time we had puzzle similar to one from last week (#417), where we had to split by letters and digits. Twist today is to split to digits, majuscules and minuscules (or uppercase letters and lowercase letters :-)). In case of my R script from #417, we just needed tiny modification. But I will provide you with full script changed for this purpose.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/423 Split Case Sensitive Alphabets and Numbers.xlsx", range = "A1:A10")
test  = read_excel("Excel/423 Split Case Sensitive Alphabets and Numbers.xlsx", range = "B1:B10")

Transformation

pattern = ("[A-Z]+|[a-z]+|[0-9]+")

result = input %>%
  mutate(splitted = map_chr(Data, ~str_extract_all(., pattern) %>% unlist() %>% 
                              str_c(collapse = ", "))) 

# Change is in the pattern. Last time it was [A-Za-z]+ that get all letters,
# either lowercase and upper case. 
# This time we needed to separate them to shape you see above.

Validation

identical(result$splitted, test$`Expected Answer`)
# [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.