# R Solution for Excel Puzzles

**Numbers around us - Medium**, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

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

Puzzles no. 369–373

### Puzzles

Author: ExcelBI

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

### Puzzle #369

Today we get series of comma separated numbers and instruction “In” or “Out”. We have to shuffle this numbers like playing cards and in/out means which part of pile goes first. So… we are dividing those numbers into two halves and according to instruction start shuffling. Lets do it with code.

#### Load libraries and data

library(tidyverse) library(readxl) input = read_excel("Excel/369 Faro Shuffle.xlsx", range = "A1:B12") test = read_excel("Excel/369 Faro Shuffle.xlsx", range = "C1:C12")

#### Transformation and validation

shuffl e = function(input, type) { numbers = str_extract_all(input, "\\d+")[[1]] len = length(numbers) p1 = numbers[1:(len/2)] p2 = numbers[(len/2 + 1):len] if (type == "In") { shuffle_deck = map2_chr(p2, p1, ~ paste0(.x, ", ", .y)) %>% paste0(collapse = ", ") } else { shuffle_deck = map2_chr(p1, p2, ~ paste0(.x, ", ", .y)) %>% paste0(collapse = ", ") } return(shuffle_deck) } result = input %>% mutate(Result = map2_chr(Numbers, Type, shuffle)) %>% bind_cols(test) %>% mutate(Correct = ifelse(Result == `Expected Answer`, "Yes", "No"))

### Puzzle #370

Today’s form is different than usual, because there was no input data, but only test data. But there is very nice task here. We have to find or generate first 1000 of cyclopic palindromes. What does it mean? We need to find or generate number that has EYE (0) in middle and symetrical numbers around it. There should be no zeroes beside central one. I choose generating instead of brute force checking.

#### Loading libraries and data

library(tidyverse) library(stringi) library(readxl) test = read_excel("Excel/370 Palindromic Cyclops Number.xlsx", range = "A1:A1001") %>% mutate(`Expected Answer` = as.integer(`Expected Answer`))

#### Transformation

generate_cyclopic_palindromes <- function(n) { half_parts <- seq(1, 10^n - 1) %>% keep(~ !str_detect(.x, "0")) %>% map_chr(~ paste0(.x, "0", stri_reverse(.x))) %>% as.integer() half_parts } palindromic_cyclopic_numbers <- generate_cyclopic_palindromes(4) %>% head(1000)

#### Validation

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

### Puzzle #371

In this task we have wide table with dates when certain fruits were ordered. But we only want to make order list per date between given dates.

#### Load libraries and data

library(tidyverse) library(readxl) input1 = read_excel("Excel/371 Find data between dates.xlsx", range = "A1:F8") input2 = read_excel("Excel/371 Find data between dates.xlsx", range = "H1:I2") %>% janitor::clean_names()

#### Transformation

test = read_excel("Excel/371 Find data between dates.xlsx", range = "H5:I8") result = input1 %>% pivot_longer(cols = -c("Products"), names_to = "index", values_to = "Dates") %>% filter(Dates >= input2$from_date & Dates <= input2$to_date) %>% group_by(Dates) %>% arrange(Products) %>% summarise(Product = paste(Products, collapse = ", "))

#### Validation

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

### Puzzle #372

Today matrices came back. We have to fill only left top triangle in certain order (start from narrow tip at the bottom and finish in wide part at top). We have 4 ready triangles to check. Lets do it.

#### Load libraries and data

library(tidyverse) library(readxl) test2 = read_excel("Excel/372 Draw Inverted Right Angled Triangle.xlsx", range = "B2:C3", col_names = FALSE) %>% set_names(paste0("V", 1:ncol(.))) test3 = read_excel("Excel/372 Draw Inverted Right Angled Triangle.xlsx", range = "B5:D7", col_names = FALSE) %>% set_names(paste0("V", 1:ncol(.))) test4 = read_excel("Excel/372 Draw Inverted Right Angled Triangle.xlsx", range = "B9:E12", col_names = FALSE) %>% set_names(paste0("V", 1:ncol(.))) test7 = read_excel("Excel/372 Draw Inverted Right Angled Triangle.xlsx", range = "B14:H20", col_names = FALSE) %>% set_names(paste0("V", 1:ncol(.)))

#### Function

draw_inverted_triangle <- function(size) { numbers <- seq(size * (size + 1) / 2, 1) mat <- matrix(NA, nrow = size, ncol = size) mat[lower.tri(mat, diag = TRUE)] <- numbers mat <- apply(mat, 2, rev) %>% t() as_tibble(mat) %>% print(n = Inf) }

#### Validation

all.equal(draw_inverted_triangle(2), test2) #> [1] TRUE all.equal(draw_inverted_triangle(3), test3) #> [1] TRUE all.equal(draw_inverted_triangle(4), test4) #> [1] TRUE all.equal(draw_inverted_triangle(7), test7) #> [1] TRUE

Why I used all.equal() instead of identical()? Because NA is not identical to NA, but is equal. So if we have NA in structure to confirm, better use this function.

### Puzzle #373

Really nice puzzle to solve with one twist I didn’t expect, but somehow I was only one person which has error in solution. Fortunatelly, I managed to find reason. And it was not about the code. But what is the task…

We need to count every occurence of given digit in sequence consisting of squares of another sequence with given start and end. For example if we would have 1 to 5, then second sequence would be 1, 4, 9, 16 and 25, and for example we need to count ones, so answer is 2. Lets dive into puzzle.

#### Load libraries and data

library(tidyverse) library(readxl) input = read_excel("Excel/373 Count Digits in Squares.xlsx", range = "A1:C10") test = read_excel("Excel/373 Count Digits in Squares.xlsx", range = "D1:D10") count_digits = function(x, y, digit) { s = seq(x, y) sq = s^2 u = unlist(strsplit(as.character(sq), "")) n = sum(u == digit) return(n) }

#### Transformation and validation

result = input %>% mutate(count = pmap_int(list(N1, N2, D), count_digits)) %>% bind_cols(test) %>% mutate(check = count == `Answer Expected`)

And what was this twist I mentioned? In one row of our input we have following values: 699 as start of first sequence, 1078 as end of first sequence and 0 as digit to count. So what is the story here? Correct answer was 313 zeroes in those numbers and I get 308. Where are 5 missing zeroes? Devil in details, I can say… By default R use scientific notation for numbers, so square of 1000 was “1e+06” which has one zero, instead of “1000000” which has six of them, and that are exactly those five zeroes I missed. So code is working perfectly, but we have to type something on console to stop using scientific notation.

options(scipen=999)

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.

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.

**leave a comment**for the author, please follow the link and comment on their blog:

**Numbers around us - Medium**.

R-bloggers.com offers

**daily e-mail updates**about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

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