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

Puzzles no. 454–458

### Puzzles

Author: ExcelBI

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

### Puzzle #454

Sombody was counting something, and as usually we have to check it and find the most insigthful info. We have to find the length of each sequence, but in some of the, there are ranges with text. That mean that we have to use some R magic. Let’s play.

```library(tidyverse)

input = read_excel("Excel/454 Extraction of number of nodes.xlsx", range = "A1:A9")
test  = read_excel("Excel/454 Extraction of number of nodes.xlsx", range = "B1:B9")```

#### Transformation

```replace_notation_with_range <- function(text_vector) {
str_replace_all(text_vector, "\\d+ to \\d+", function(match) {
numbers <- str_split(match, " to ") %>%
unlist() %>%
as.numeric()

range <- seq(from = numbers[1], to = numbers[2])
paste(range, collapse = ", ")
})
}

count_numbers <- function(text_vector) {
str_count(text_vector, "\\d+") %>%
as.numeric()
}

result = input %>%
mutate(Pronlem = str_to_lower(Pronlem)) %>%
mutate(Pronlem = map_chr(Pronlem, replace_notation_with_range)) %>%
mutate(Count = count_numbers(Pronlem)) %>%
select(Count)```

#### Validation

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

### Puzzle #455

Have you heard of antiperfect numbers? They are in some weird way perfect to me… perfect to play with. We need to find out which of given numbsers are antiperfect. But what does it mean? That if you take all divisors except number itself, change order of letters and add them up, they will be equal to original number. Let’s do it.

```library(tidyverse)
library(numbers)

input = read_excel("Excel/455 Anti perfect numbers.xlsx", range = "A1:A10")
test  = read_excel("Excel/455 Anti perfect numbers.xlsx", range = "B1:B5")```

#### Transformation

```is_antiperfect = function(number) {
divisors = divisors(number)
divisors = divisors[-length(divisors)]
reversed_divisors = map(divisors, ~str_c(rev(str_split(.x, "")[[1]]), collapse = "")) %>%
as.numeric()
sum_rev_div = sum(reversed_divisors)
return(sum_rev_div == number)
}

result = input %>%
mutate(is_antiperfect = map_lgl(Numbers, is_antiperfect)) %>%
filter(is_antiperfect) %>%

#### Validation

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

### Puzzle #456

Today’s challenge is pretty easy. And that is why I will give you two ways to do it.

```library(tidyverse)

input = read_excel("Excel/456 Extract special Characters.xlsx", range = "A1:A10")
test  = read_excel("Excel/456 Extract special Characters.xlsx", range = "B1:B10")```

#### Transformation — approach 1

```# approach 1 - remove alphanumerics

result = input %>%
mutate(String = str_replace_all(String, "[[:alnum:]]", "")) %>%
mutate(String = ifelse(String == "", NA, String))```

#### Transformation — approach 2

```# approach 2 - extract special characters

result2 = input %>%
mutate(String = str_extract_all(String, "[^[:alnum:]]") %>%
map_chr(~paste(.x, collapse = ""))) %>%
mutate(String = ifelse(String == "", NA, String))```

#### Validation

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

#> [1] TRUE```

### Puzzle #457

Today we have task similar, but we have more complicated case. I used Regex capacities to find all numbers that are “hugged”with any kind of parenthesis. I can say that except Regex itself pretty easy case. Regex needs to use lookbehind and lookahead in cases.

```library(tidyverse)

input = read_excel("Excel/457 Extract Numbers in Parenthesises.xlsx", range = "A1:A10")
test  = read_excel("Excel/457 Extract Numbers in Parenthesises.xlsx", range = "B1:B10")```

#### Transformation

```library(tidyverse)

input = read_excel("Excel/457 Extract Numbers in Parenthesises.xlsx", range = "A1:A10")
test  = read_excel("Excel/457 Extract Numbers in Parenthesises.xlsx", range = "B1:B10")```

#### Validation

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

### Puzzle #458

Capital letters stands out in text as too tall soldier in a row. And our host gave us chance to make special meeting for the tallest soldiers. We need to find longest sequence of capital letters in this words. If there are more than one possible, we need to concatenate them.

```library(tidyverse)

input = read_excel("Excel/458 Maximum Consecutive Uppercase Alphabets.xlsx", range = "A1:A11")
test  = read_excel("Excel/458 Maximum Consecutive Uppercase Alphabets.xlsx", range = "B1:B11")```

#### Transformation

```get_longest_capital = function(string) {
caps = str_extract_all(string, "[A-Z]+") %>% unlist()
caps_len = ifelse(length(caps) == 0, NA, max(nchar(caps)))
caps = caps[nchar(caps) == caps_len] %>% paste0(collapse = ", ")
return(caps)
}

result = input %>%
mutate(ans = map_chr(Words, get_longest_capital)) %>%
mutate(ans = ifelse(ans == "", NA_character_, ans))
```

#### Validation

```all.equal(result\$ans, test\$`Expected Answer`)
# [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.