R Solution for Excel Puzzles

Puzzles no. 564–568

Puzzles

Author: ExcelBI

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

Puzzle #564

Have you ever thought about sorting only part of given data, not even changing position where that data live… Like conductor showing one part of orchestra to change way they playing without changing the others. So we have something like this today. We need to sequence of characters and numbers and only thing we need to do is, sort numbers descending, leaving letters on their position, and then place numeers back in original posiution but sorted.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/564 Sort Numbers only.xlsx"
input = read_excel(path, range = "A2:D11") 
test  = read_excel(path, range = "F2:I11")

Transformation

process_column = function(col) {
  letters = grep("[A-Za-z]", col)
  num_positions = grep("[0-9]", col)
  numbers = as.numeric(col[num_positions])
  numbers = sort(numbers)
  col[num_positions] = numbers
  return(col)
}

input = input %>% map_df(process_column)

Validation

all.equal(input, test)
#> [1] TRUE

Puzzle #565

Roman god Janus had two faces looking in two oposite directions, so I think that he can be nice illustration of reversed numbers. Because we are doing it again, reversing, manipulating etc. numbers. Today we have to find 50 numbers for which both square roots of number and reverse are even numbers and are perfect squares. So we need to make some conditions to check. Let’s do it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/565 Even Number and Reversal Perfect Square.xlsx"
test  = read_excel(path, range = "A1:A51")

Transformation

is_even = function(x) {
  x %% 2 == 0
}

is_perfect_square = function(x) {
  sqrt_x = sqrt(x)
  sqrt_x == floor(sqrt_x)
}

reverse_number = function(x) {
  as.numeric(paste(rev(strsplit(as.character(x), NULL)[[1]]), collapse = ""))
}

find_even_reverse_perfect_squares = function(n_required) {
  results = vector("list", n_required)
  count = 1
  n = 10
  
  while (count <= n_required) {
    square = n^2
    reverse_square = reverse_number(square)
    if (is_even(square) && is_even(reverse_square) &&
        is_perfect_square(reverse_square)) {
      results[[count]] = list(original = square, reverse = reverse_square)
      count = count + 1
    }
    n = n + 1
  }
  return(results)
}

result = find_even_reverse_perfect_squares(50) %>%
  map_df(~ .x)

Validation

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

Puzzle #566

This time it looks like we get some fruits in 3 different boxes. And we need to find out in which box there were what amount of which fruit. Many “Wh” words in one sentence, right. Now do some pivoting to find out the solution.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/566 Count in Columns.xlsx"
input = read_excel(path, range = "A2:C14")
test  = read_excel(path, range = "E2:J6")

Transformation

result = input %>%
  pivot_longer(everything(), names_to = "basket", values_to = "fruit") %>%
  summarise(Count = n(), .by = c(fruit, basket)) %>%
  na.omit() %>%
  pivot_wider(names_from = Count,
              values_from = basket, 
              values_fn = list(basket = ~ str_c(sort(.x), collapse = ", "))) %>%
  arrange(fruit) %>%
  select(Count = fruit,`1`, `2`, `3`, `4`, `5`) 

Validation

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

Puzzle #567

You know that I am enjoying ASCII drawing. So I was happy to reuse some of previous code. Do you remember triangle we did few weeks ago? I modified it a little and used here. Let matrix populate now…

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/567  ASCII House.xlsx"
test  = read_excel(path, range = "C2:Q18", col_names = F) %>%
  replace(is.na(.), "") %>% as.matrix()

Transformation

M = matrix("", nrow = 17, ncol = 15)

for (i in 1:7) {
  M[i, (8 - i + 1):(8 + i - 1)] = "#"
}
M[17, ] = "#"
for (i in 8:16) {
  M[i, c(2, 14)] = "#"
}
M[16, -c(1, 15)] = "#"
M[9:11, c(4,6)] = "#"
M[c(9, 11), 5] = "#"
M[9:16, 9] = "#"
M[9, 10:11] = "#"
M[9:16, 12] = "#"

as.data.frame(M)

Validation

all.equal(M, test, check.attributes = F) # TRUE

Puzzle #568

Lets think about alphabet little bit another way today. You probably treat it as set of characters, they are just brought all together. But what if we should treat alphabet as sequence, as a chain? What if place in this ordered structure is one of the most important properties of letter? We have today puzzle that force us to complete space between letters in word with all sequence that comes between. Sequence can be read from left to right or right to left, depending of letter place in order. I needs few manouvers but it is pretty easy at the same time.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/568 Fill in the Alphabets.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:B10")

Transformation

fill_words = function(string) {
  lets = strsplit(string, "")[[1]]
  pairs = map(1:(length(lets) - 1), ~paste(lets[.x:(.x + 1)], collapse = ""))

  df = tibble(
    first = map_chr(pairs, ~str_sub(.x, 1, 1)),
    second = "",
    third = map_chr(pairs, ~str_sub(.x, 2, 2))
  )
  
  df = df %>%
    mutate(
      second = map2_chr(first, third, ~{
        first_num = as.numeric(charToRaw(.x))
        third_num = as.numeric(charToRaw(.y))
        letters = map_chr((first_num):(third_num), ~rawToChar(as.raw(.x)))
        paste(letters, collapse = "") %>% str_sub(2, -2)
      }),
      third = if_else(row_number() == n(), "", third)
    ) %>%
    unite("word", c("first", "second", "third"), sep = "") %>%
    pull(word) %>%
    paste(collapse = "")
  
  return(df)
}

result = input %>%
  mutate(`Answer Expected` = map_chr(Words, fill_words)) %>%
  select(-Words)

Validation

all.equal(result$`Answer Expected`, test$`Answer Expected`, check.attributes = FALSE)
#> [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.
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.