R Solution for Excel Puzzles

Puzzles no. 449–453

Puzzles

Author: ExcelBI

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

Puzzle #449

We are starting this week with rotating words. Wait what? No, we are not gonna swirl them and twist them. We need to find out if one word is second one’s rotated version, which means that it has to be shifted some places and letters from the end are coming to the beginning. But it is little bit tricky. Rotated version should be rotated by X spaces, where X is not equal to lenght of word. They have to be equal in lenghts as well, because it should come to word back after shifting it with rest of characters. Let’s rotate them.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/449 Rotated Strings.xlsx", range = "A1:B10") %>% arrange(String1)
test  = read_excel("Excel/449 Rotated Strings.xlsx", range = "C1:D6") %>% arrange(`Answer Expected`)
colnames(test) = colnames(input)

Transformation Approach 1

is_rotated = function(string1, string2) {
  is_0_rot = string1 == string2
  is_rot = str_detect(paste0(string1, string1), string2)
  is_length_equal = nchar(string1) == nchar(string2)
  return(is_rot & !is_0_rot & is_length_equal)
}

result = input %>%
  mutate(is_rotated = map2_lgl(String1, String2, is_rotated)) %>%
  filter(is_rotated) %>%
  select(-is_rotated)

Transformation Approach 2

result2 = input %>% 
  filter(map2_lgl(String1, String2, ~str_detect(paste0(.x, .x), .y) & .x != .y & nchar(.x) == nchar(.y)))

Validation

identical(result, test)
# [1] TRUE

identical(result2, test)
# [1] TRUE  

Puzzle #450

Today we need to rank sales person in different companies without sorting structure of table itself. Fortunatelly in case of R it is pretty easy.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/450 Ranking.xlsx", range = "A1:C20")
test  = read_excel("Excel/450 Ranking.xlsx", range = "D1:D20")

Transformation

result = input %>%
  mutate(rank = dense_rank(desc(Sales)), .by = Company)

Validation

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

Puzzle #451

We have long sequence of numbers, and we need to find which of them negative and positive separately, will form longer consecutive chains. Let’s try doing it.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/451 Consecutive Numbers.xlsx", range = "A1:A20")
test  = read_excel("Excel/451 Consecutive Numbers.xlsx", range = "D1:E3")

Transformation

result = input %>%
  mutate(group = cumsum(Numbers - lag(Numbers, default = 0) != 0),
         pos = ifelse(Numbers > 0, "P", "N")) %>%
  summarise(count = n() %>% as.numeric(), .by = c(group, Numbers, pos)) %>%
  filter(count == max(count), .by = pos) %>%
  summarise(Number = paste(unique(Numbers), collapse = ", "), Count = unique(count), .by = pos) %>%
  arrange(desc(Count)) %>%
  select(-pos)

Validation

identical(result, test)
# [1] TRUE

Puzzle #452

Why there is mosquito in ilustration? Because we have parasitic numbers today? What are they? Those are numbers that if multiplied by single digit integer number, will form number with almost the same shape but rotated (last digit comes to beginning). And today we need to find numbers that are parasitic, are lower than 1M and we need their multipliers as well. Get to work.

Loading libraries and data

library(tidyverse)
library(readxl)

test = read_excel("Excel/452 Parasitic Numbers.xlsx", range = "A1:B8")

Transformation

a = tibble(Number = as.character(1:1000000)) %>%
  mutate(cycled = str_c(str_sub(Number, -1), str_sub(Number, 1, -2)) %>% 
           as.numeric() %>% 
           as.character()) %>%
  filter(nchar(Number) == nchar(cycled),
         as.integer(cycled) %% as.integer(Number) == 0,
         as.integer(cycled) != as.integer(Number)) %>%
  mutate(across(everything(), as.numeric)) %>%
  mutate(Multiplier = cycled / Number) %>%
  select(-cycled)

Validation

identical(a, test)
# [1] TRUE

Puzzle #453

We need to find out from how many fruits from two lists we need to make exact pairs, so if there is 3 apples in one list, but 2 in second, there are only 2 pairs. Lets find the rest.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/453 Common in Columns.xlsx", range = "A1:B12")
test  = read_excel("Excel/453 Common in Columns.xlsx", range = "D2:E6")

Transformation Approach 1

result = input %>%
  mutate(nr_l1 = row_number(), .by = List1) %>%
  mutate(nr_l2 = row_number(), .by = List2) %>%
  unite("List1", List1, nr_l1, sep = "_") %>%
  unite("List2", List2, nr_l2, sep = "_") 

l1 = result$List1
l2 = result$List2

common = intersect(l1, l2)

result2 = as_tibble(common) %>%
  separate(value, c("Match", "Count"), sep = "_") %>%
  mutate(Count = as.numeric(Count)) %>%
  slice_max(Count, by = Match)

Transformation Approach 2

result = input %>%
  pivot_longer(cols = everything()) %>%
  count(value, by = name) %>%
  mutate(nr = n_distinct(by),
         min_n = min(n) %>% as.numeric(),
         .by = value) %>%
  filter(nr == 2) %>%
  select(Match = value, Count = min_n) %>%
  distinct()

Validation

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

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.