Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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.

```library(tidyverse)

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.

```library(tidyverse)

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.

```library(tidyverse)

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.

```library(tidyverse)

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.

```library(tidyverse)

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.