Puzzles no. 534–538

Puzzles

Author: ExcelBI

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

Puzzle #534

Palindromes and other symetric numbers are pretty common them of our challenges, but this twist we have today, is pretty unique and hard in the same moment. First time we have to find next 3 palindromes, not just next but next which has any digit repeated N times. It is calculation heavyy one and pretty long script. Check it.

Loading libraries and data

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

path = "Excel/534 Palindrome Numbers with Digit Repeations.xlsx"
input = read_excel(path, range = "A1:B9")
test  = read_excel(path, range = "C1:E9")

Transformation

has_digit_repeated_n_times <- function(num, N) {
  any(table(strsplit(as.character(num), "")[[1]]) == N)
}

is_palindrome <- function(num) {
  as.character(num) == stri_reverse(as.character(num))
}

result <- input %>%
  rowwise() %>%
  mutate(
    res = list(
      seq(.data$`No. of Digits`, .data$`No. of Digits` + 1000000) %>% 
        keep(~ is_palindrome(.x)) %>%                                 
        keep(~ has_digit_repeated_n_times(.x, .data$Repeats)) %>%   
        head(3)                                                      
    )
  ) %>%
  unnest(res) %>%
  ungroup()

res <- result %>%
  mutate(nr = row_number(), .by = `No. of Digits`) %>%
  select(-c(Repeats)) %>%
  pivot_wider(names_from = nr, names_glue = "P_{nr}", values_from = res) %>%
  select(-`No. of Digits`)

Validation

all.equal(test, res, check.attributes = FALSE)
#> [1] TRUE

Puzzle #535

Our world is like tapestry of many threads and ribbons. And today we have drawing with numbers again. This time we need to populate matrix of given dimensions with numbers, but each next row have to start with numbers taken from the end of sequence. Do you remember how we checked rotation of numbers and strings? I realized that we can do it here as well. And it works fast, and need small amount of code.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/535 Generate Number Grid.xlsx"
height = read_excel(path, range = "B1", col_names = F) %>% pull()
width = read_excel(path, range = "B2", col_names = F) %>% pull()

test = read_excel(path, range = "D2:K7", col_names = F) %>% as.matrix()

Transformation

matrix = matrix(0, nrow = height, ncol = width)
for (i in 1:height) {
  matrix[i, ] <- c(tail(1:width, i - 1), head(1:width, width - (i - 1)))
}

Validation

all.equal(matrix, test, check.attributes = F)
# [1] TRUE

Puzzle #536

We get table of coordinates… It remind me giving coordinates for artilery (or maybe just in “Ship Battle” game). We have to put specific values in cells of given coords. And I found two ways of doing it: by transforming input table and by populating matrix based on input table. Both are pretty easy to do, but we need to take care of concatenation of two symbols in couple of cells.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/536 Populate Grid for Rows and Columns.xlsx"
input = read_excel(path, range = "A1:C10")
test = read_excel(path, range = "E2:J7")
testM = test %>% as.matrix() %>%
  .[, -1] 

Transformation — Tidyverse approach

result = input %>%
  pivot_wider(names_from = Column, 
              values_from = Value, 
              values_fn = list(Value = function(x) paste(x, collapse = ", "))) %>%
  mutate(`5` = NA) %>%
  select(Row, `1`, `2`, `3`, `4`, `5`) %>%
  arrange(Row)

Transformation — Matrix approach

M = matrix(NA_character_, nrow = 5, ncol = 5)
for (i in 1:nrow(input)) {
  M[input$Row[i], input$Column[i]] = ifelse(is.na(M[input$Row[i], input$Column[i]]), 
                                            as.character(input$Value[i]), 
                                            paste(M[input$Row[i], input$Column[i]], as.character(input$Value[i]), sep = ", "))
}

Validation

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

all.equal(M, testM, check.attributes = FALSE)
# [1] TRUE

Puzzle #537

Our task today is to find minimal (not with lowest absolute value, but lowest nominal value) triplet from 6 columns. In each we need to find combination that gives us the lowest value and write down this value. Do you realize that this introduction is much longer than code I will use for transformation today? Check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/537 Minimum Product for Triplet.xlsx"
input = read_excel(path, range = "A1:F10")
test  = read_excel(path, range = "G1:G10")

Transformation

output = input %>%
  mutate(min_product = pmap_dbl(., ~ min(combn(c(...), 3, prod))))

Validation

identical(test$`Answer Expected`, output$min_product) 
#> [1] TRUE

Puzzle #538

We all know what is it palindromic number, and what is it a prime number. But have you heard of emirp number. Yeah, of course it is prime backward. But simple reversed would be to easy. We need to find all numbers from 10 to 10 millions that are not palindromic, that are prime and after reversing order of digits they are prime as well. Code is not complicated today, but I wonder why we need such numbers at all.

Loading libraries and data

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

path = "Excel/538 Emirps.xlsx"
test = read_excel(path)

Transformation

is_palindrome = function(x) {
  x = as.character(x)
  x == stri_reverse(x)
}

max_val = 10000000
primes = generate_n_primes(max_val)
primes = primes[primes > 10]
non_pal_primes = primes[!is_palindrome(primes)]

nums = data.frame(number = 1:10000000) %>%
  filter(number %in% non_pal_primes & stri_reverse(as.character(number)) %in% non_pal_primes)

Validation

all.equal(nums$number, test$`Expected Answer`) # 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.
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.