Puzzles no. 459–463

Puzzles

Author: ExcelBI

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

Puzzle #459

This episode first puzzle is based on pure math. We need to find next perfect square number to one given. Given number doesn’t have to be square itself. But it is really easy if you think about it. We need to find out what is square root of given number, then round down to closest integer, add 1 and square it back. That is so easy, that function can be written as oneliner. Check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/459 Next Perfect Square.xlsx", range = "A1:A10")
test  = read_excel("Excel/459 Next Perfect Square.xlsx", range = "B1:B10")

Transformation

find_next_perf_square = function(n) (floor(sqrt(n)) + 1) ** 2

result = input %>%
  mutate(`Answer Expected` = map_dbl(Number, find_next_perf_square)) %>%
  select(-Number)

Validation

identical(result, test)
# [1] TRUE

Puzzle #460

Yes, R can be used as scissors for words, sentences and so on. Today we have to cut given string every N places, but somehow counting from the end. Because if there is not enough characters shorter group need to be at the beginning, not at the end. And groups need to be separated with dash (vel hyphen). Looking at the difficulty, not the easiest, but nice to have tasks like this from time to time. Look how I did it.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/460 Insert Dash Splitter.xlsx", range = "A1:B10")
test  = read_excel("Excel/460 Insert Dash Splitter.xlsx", range = "C1:C10")

Transformation

split_by_dash = function(word, n) {
  str_split(word, "", simplify = TRUE) %>%
    rev() %>%
    split(rep(1:ceiling(length(.) / n), each = n, length.out = length(.))) %>%
    map(~paste0(rev(.), collapse = "")) %>%
    rev() %>%
    paste0(collapse = "-")
}

result = input %>%
  mutate(`Answer Expected` = map2_chr(String, N, split_by_dash)) %>%
  select(3)

Validation

identical(result, test)
# [1] TRUE

Puzzle #461

Looks like someone mixed chapters for new book and we need to order them for publisher. If we sort it right away, it will not work, because it is string not a number, and will try to put 12 at the beginning because 1 is earlier in alphabetical order. So what we are gonna do? Cut, sort, bring back. Nothing easier.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/461 Sort the Numbers.xlsx", range = "A1:A10")
test  = read_excel("Excel/461 Sort the Numbers.xlsx", range = "B1:B10")

Transformation

result = input %>%
  separate(String, into = c("A", "B", "C", "D"), sep = "\\.", remove = FALSE) %>%
  mutate(across(A:D, as.numeric)) %>%
  arrange(A, B, C, D) %>%
  select(String)

Validation

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

Puzzle #462

Usually I use purrr package for any tasks that are repeatable, iterable, but this time I realised that maybe not the shortest, but most readable option will be to use old-fashioned loops. We need to find empty cells in matrix, then fill it with maximum value from neighbouring cells. Let’s get looping.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/462 Fill in the Grid.xlsx", range = "A2:J11", col_names = F) %>%
  as.matrix()
test  = read_excel("Excel/462 Fill in the Grid.xlsx", range = "A14:J23", col_names = F) %>%
  as.matrix()

Transformation

na_coords = which(is.na(input), arr.ind = T) 

get_surrounding_values = function(x, y, matrix){
  values = c()
  for (i in -1:1) {
    for (j in -1:1) {
      if (x + i > 0 & x + i <= nrow(matrix) & y + j > 0 & y + j <= ncol(matrix)) {
        values = c(values, matrix[x + i, y + j])
      }
    }
  }
  return(max(values, na.rm = T))
}


for (i in 1:nrow(na_coords)) {
  input[na_coords[i, 1], na_coords[i, 2]] = get_surrounding_values(na_coords[i, 1], na_coords[i, 2], input)
}

Validation

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

Puzzle #463

Last task this week was refering to inventory management. Having only partial information, based only on changes of inventory level, we need to prepare levels for each month, even if there were no changes. Tricky, but I hired purrr::accumulate2() for this job. Check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/463 Inventory Calculation.xlsx", range = "A1:C6") %>% janitor::clean_names()
test  = read_excel("Excel/463 Inventory Calculation.xlsx", range = "E2:F14") %>% janitor::clean_names()

Transformation

months = tibble(abbs = month.abb, month = 1:12)

result = months %>%
  left_join(input, by = c("abbs" = "month")) %>%
  replace_na(list(incoming_qty = 0, outgoing_qty = 0)) %>%
  mutate(inventory = accumulate2(incoming_qty, outgoing_qty, .init = 0, 
                                .f = ~ ..1 + ..2 - ..3)[-1]) %>%
  select(month = abbs, inventory)

Validation

identical(result, test)
# [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.
PS. Couple weeks ago, I started uploading on Github not only R, but also in Python. Come and check it.

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.