[This article was first published on 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. 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.

```library(tidyverse)

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.

```library(tidyverse)

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.

```library(tidyverse)

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.

```library(tidyverse)

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.

```library(tidyverse)

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.

To 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.

# Never miss an update! Subscribe to R-bloggers to receive e-mails with the latest R posts.(You will not see this message again.)

Click here to close (This popup will not appear again)