R Solution for Excel Puzzles

Puzzles no. 524–528

Puzzles

Author: ExcelBI

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

Puzzle #524

Today we get bunch of numbers with masked digits. Our task is to check if from those masked sequences we can form number that is perfect square. In some cases it was not possible, in some in many different ways. Be aware that multiple X’s in one number could be replaced with different digits. Lets check it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/524 Fill in Digits to make Perfect Square.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:B10")

Transformation

find_square  = function(x) {
 result =  x %>% 
    strsplit("") %>% 
    .[[1]] %>% 
    map(~ if (.x == "X") 0:9 else as.numeric(.x)) %>% 
    expand.grid() %>%
    unite(num, everything(), sep = "") %>%
    mutate(num = as.numeric(num)) %>%
    filter(sqrt(num) == floor(sqrt(num))) %>%
    pull(num)
  
    if (length(result) == 0) return("NP")
    else if (length(result) == 1) return(as.character(result))
    else return(paste(result, collapse = ", "))
    }

output = input %>%
  mutate(`Answer Expected` = map_chr(Numbers, find_square)) %>%
  select(-Numbers) %>%
  bind_cols(test)

print(output)

Puzzle #525

We have task I usually call graphical, that is related with some visual construct that has mathematical base under. Current task is about finding out if given matrix has structure like proper chess board. So we have to CHECK it, MATE.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/525 Valid Chessboard.xlsx"
input1 = read_excel(path, sheet = 1, col_names = FALSE, range = "A2:B3") %>% as.matrix()
test1 = read_excel(path, sheet = 1, col_names = FALSE, range = "J2") %>% pull()
input2 = read_excel(path, sheet = 1, col_names = FALSE, range = "A5:B6") %>% as.matrix()
test2 = read_excel(path, sheet = 1, col_names = FALSE, range = "J5") %>% pull()
input3 = read_excel(path, sheet = 1, col_names = FALSE, range = "A8:C10") %>% as.matrix()
test3 = read_excel(path, sheet = 1, col_names = FALSE, range = "J8") %>% pull()
input4 = read_excel(path, sheet = 1, col_names = FALSE, range = "A12:C14") %>% as.matrix()
test4 = read_excel(path, sheet = 1, col_names = FALSE, range = "J12") %>% pull()
input5 = read_excel(path, sheet = 1, col_names = FALSE, range = "A16:D19") %>% as.matrix()
test5 = read_excel(path, sheet = 1, col_names = FALSE, range = "J16") %>% pull()
input6 = read_excel(path, sheet = 1, col_names = FALSE, range = "A21:F26") %>% as.matrix()
test6 = read_excel(path, sheet = 1, col_names = FALSE, range = "J21") %>% pull()
input7 = read_excel(path, sheet = 1, col_names = FALSE, range = "A28:H35") %>% as.matrix()
test7 = read_excel(path, sheet = 1, col_names = FALSE, range = "J28") %>% pull()
input8 = read_excel(path, sheet = 1, col_names = FALSE, range = "A37:H44") %>% as.matrix()
test8 = read_excel(path, sheet = 1, col_names = FALSE, range = "J37") %>% pull()

Transformation

is_proper_chessboard <- function(board) {
  board_numeric <- ifelse(board == 'B', -1, 1)
  n <- nrow(board)
  
  sum_check <- function(x) {
    if (n %% 2 == 0) 
      return(all(rowSums(x) == 0) && all(colSums(x) == 0))
    else 
      return(all(abs(rowSums(x)) == 1) && all(abs(colSums(x)) == 1))
  }
  
  result = ifelse(sum_check(board_numeric), "Valid", "Invalid")
  return(result)
}

Validation

is_proper_chessboard(input1) == test1 # TRUE
is_proper_chessboard(input2) == test2 # TRUE
is_proper_chessboard(input3) == test3 # TRUE
is_proper_chessboard(input4) == test4 # TRUE
is_proper_chessboard(input5) == test5 # TRUE
is_proper_chessboard(input6) == test6 # TRUE
is_proper_chessboard(input7) == test7 # TRUE
is_proper_chessboard(input8) == test8 # TRUE

Puzzle #526

Palindromes appears quite often in our challenges, and here we have them. We are getting series of numbers, and we need to find three palindromic numbers after given number. Most of solution by other competitors needed loops, but I took another approach, I did it looking for structure of number itself. It was much faster in coding and executing. Let’s do it my way.

Loading libraries and data

library(tidyverse)
library(readxl)
library(stringi)

path = "Excel/526 Next 3 Palindromes.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:D10")

Transformation

get_next_palindromes = function(num, cnt) {
  nc = nchar(num)
  
  fh = str_sub(num, 1, nc / 2)
  mid = str_sub(num, nc / 2 + 1, nc / 2 + 1)
  ld = str_sub(num, nc / 2, nc / 2)
  fd = str_sub(num, nc / 2 + 2, nc / 2 + 2)
  
  if (nc %% 2 == 0) {
    next_fh = (as.numeric(fh) + seq_len(cnt) - (mid < ld)) %>% as.character()
    palindromes = paste0(next_fh, stri_reverse(next_fh))
  } else {
    next_fh =  (as.numeric(paste0(fh, mid)) + seq_len(cnt) - (fd < ld)) %>% as.character()
    palindromes = paste0(next_fh, str_sub(stri_reverse(next_fh), 2))
  }
  return(palindromes)
}

result = input %>%
  mutate(res = map(Number, ~get_next_palindromes(.x, 3))) %>%
  unnest_wider(res, names_sep = "_") %>%
  select(-Number) %>%
  mutate(across(everything(), as.numeric))

colnames(test) = colnames(result)

Validation

all.equal(result, test, check.attributes = FALSE) # TRUE

Puzzle #527

Lets play with numbers. In this task we have to convert each digit to its representation in specific order (last digit — base 2, penultimate- base3 and so on to the first digit). Then we need to add them together and sort original numbers according to result of our previous operations. Oddly satisfying.

PS. I found package called Gmisc, that helped me with this task, so I think I need to learn more about this package.

Loading libraries and data

library(tidyverse)
library(readxl)
library(Gmisc)

path = "Excel/527 Sum of Digits in Different Bases.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")

Transformation

convert_to_sum <- function(number) {
  digits <- as.numeric(strsplit(as.character(number), "")[[1]]) %>%
    tibble(num = .) %>%
  mutate(row = nrow(.) + 2 -  row_number()) %>%
  rowwise() %>%
  mutate(converted = Gmisc::baseConvert(num,  target = row, base = 10) %>% as.numeric()) %>%
  ungroup() %>%
  summarise(sum = sum(converted)) %>%
  pull()
  return(digits)
}

result = input %>%
  mutate(conv = map_dbl(Number, convert_to_sum)) %>%
  arrange(conv) %>%
  select(`Answer Expected` = Number)

Validation

identical(result, test)
#> [1] TRUE 

Puzzle #528

Today we are jewelers making beautiful gems of letters with A always in the center. I don’t know diamonds are the girls best friends, but I am sure that such challenges are hidden gems. And great chance to improve skills or show off already improved. As usual, graphical tasks are most comfortable to do using matrix, at least for me.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/528 Diamonds of Alphabets.xlsx"

Transformation

draw_diamond = function(size) {
  M = matrix(NA, nrow = 2 * size - 1, ncol = 2 * size - 1)

  for (i in 1:(2 * size - 1)) {
    for (j in 1:(2 * size - 1)) {
      M[i, j] = abs(abs(i - size) + abs(j - size)) + 1
    }
  }
  M = M %>% as.data.frame() %>% mutate_all(~ifelse(. <= size, LETTERS[.], ""))
  return(M)
}

draw_diamond(3)

  V1 V2 V3 V4 V5
1        C      
2     C  B  C   
3  C  B  A  B  C
4     C  B  C   
5        C      

draw_diamond(5)

  V1 V2 V3 V4 V5 V6 V7 V8 V9
1              E            
2           E  D  E         
3        E  D  C  D  E      
4     E  D  C  B  C  D  E   
5  E  D  C  B  A  B  C  D  E
6     E  D  C  B  C  D  E   
7        E  D  C  D  E      
8           E  D  E         
9              E            

draw_diamond(8)

   V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12 V13 V14 V15
1                        H                           
2                     H  G  H                        
3                  H  G  F  G   H                    
4               H  G  F  E  F   G   H                
5            H  G  F  E  D  E   F   G   H            
6         H  G  F  E  D  C  D   E   F   G   H        
7      H  G  F  E  D  C  B  C   D   E   F   G   H    
8   H  G  F  E  D  C  B  A  B   C   D   E   F   G   H
9      H  G  F  E  D  C  B  C   D   E   F   G   H    
10        H  G  F  E  D  C  D   E   F   G   H        
11           H  G  F  E  D  E   F   G   H            
12              H  G  F  E  F   G   H                
13                 H  G  F  G   H                    
14                    H  G  H                        
15                       H                           

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.
On my Github repo there are also solutions for the same puzzles in Python. Check it out!

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.