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

Puzzles no. 359–363

### Puzzles

Author: ExcelBI

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

### Puzzle #359

Just like in this dragon sometimes numbers are a sum of many consecutive elements. And todays task is to discover which sequence of numbers are valid to given number.

```library(tidyverse)

input = read_excel("Excel/359 Express as Sum of Consecutive Digits.xlsx", range = "A1:A10")
test  = read_excel("Excel/359 Express as Sum of Consecutive Digits.xlsx", range = "B1:B10") %>%

I did this additiona action, because probably unintentionally there are some double spaces, so I removed all spaces and adjusted my further code to it as well.

#### Transformation

```find_consecutive_sums <- function(target) {
results <- tibble()
for (start_num in 1:(target/2 + 1)) {
sum <- start_num
next_num <- start_num
while (sum < target) {
next_num <- next_num + 1
sum <- sum + next_num
if (sum == target) {
results <- bind_rows(results, tibble(start = start_num, end = next_num))
}
}
}
if (nrow(results) == 0) {
return(tibble(Numbers = target, seq = NA_character_))
# this take care of numbers that doesn't met condition
} else {
sqs <- results %>%
mutate(
Numbers = target,
seq = map2_chr(start, end, ~paste(.x:.y, collapse = "+"))
) %>%
select(Numbers, seq)
return(sqs)
}
}

result = map(input\$Numbers, find_consecutive_sums) %>%
bind_rows() %>%
group_by(Numbers) %>%
slice(1)
# If you omit last line you'll get all sequences for each number. There are some :)
# Final solutions are the longest sequences, so we only keep the first one.```

#### Validation

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

### Puzzle #360

From Tuesday to Friday we have puzzles focused on text transformation and sometimes we use regular expressions to go through. First of those was about extracting letters from given string and then put them back in the same places but with exactly reversed order. For example: f@x -> x@f.

```library(tidyverse)

input = read_excel("Excel/360 Reverse alphabets only.xlsx", range = "A1:A13")
test  = read_excel("Excel/360 Reverse alphabets only.xlsx", range = "B1:B13")```

#### Transformation

```reverse_alpha = function(word) {
chars = strsplit(word, "")[[1]]
pos = which(chars %in% c(letters, LETTERS))
alphas = chars[pos]
rev_alphas = rev(alphas)
chars[pos] = rev_alphas
processed = paste(chars, collapse = "")
return(processed)
}

output = input %>%

#### Validation

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

### Puzzle #361

Here are strings again. Now we have some pretty long strings containing letters, digits, special characters, punctuations and white spaces. And like this miner in picture above we need to find gold nuggets which in this case are longest sequence of letters and digits distinctively. If there are more then one sequence for the max length, they have to be both shown concatenated.

```library(tidyverse)

input = read_excel("Excel/361 Longest Sequence of Alphabets and Numbers.xlsx", range ="A1:A10")
test  = read_excel("Excel/361 Longest Sequence of Alphabets and Numbers.xlsx", range ="B1:C10")```

#### Transformation

```extract_longest_typeseq = function(string, pattern) {
seqs = data.frame(string = string) %>%
mutate(seq = str_extract_all(string, pattern)) %>%
unnest_longer(seq) %>%
mutate(str_len = str_length(seq)) %>%
filter(str_len == max(str_len, na.rm = TRUE)) %>%
pull(seq) %>%
paste0(collapse = ", ")

if (is.na(seqs) | seqs == "") {
return(NA)
} else {
return(seqs)
}

}

result = input %>%
mutate(Alphabets = map_chr(String, ~extract_longest_typeseq(.x, "[A-Za-z]+")),
Numbers = map_chr(String, ~extract_longest_typeseq(.x, "[0-9]+")))```

#### Validation

```identical(result\$Alphabets, test\$Alphabets)
#> [1] TRUE
identical(result\$Numbers, test\$Numbers)
#> [1] TRUE```

### Puzzle #362

Next two puzzles are basing on exactly the same dataset but with different outcome. In #362 letters that are next to digits (no matter if in front or behind) in string have to capitalize itself. Like some bodybuilder flexing himself in front of beautiful girl. So lets buff some letters.

```library(tidyverse)

input = read_excel("Excel/362 Uppercase Conversion Around Numbers.xlsx", range = "A1:A10")
test  = read_excel("Excel/362 Uppercase Conversion Around Numbers.xlsx", range = "B1:B10")```

#### Transformation

```convert = function(sentence) {
pos_foll = str_locate_all(sentence, pattern = "[a-z](?=[0-9])") %>% unlist()
# pattern is regular expression for letter followed by digit
pos_pre  = str_locate_all(sentence, pattern = "(?<=[0-9])[a-z]") %>% unlist()
# pattern is regular expression for letter preceded by digit
pos = c(pos_foll, pos_pre) %>% unique()

chars = str_split(sentence, pattern = "")[[1]]
chars[pos] = str_to_upper(chars[pos])
sentence = paste(chars, collapse = "")

return(sentence)
}

result = input %>%
mutate(`Answer Expected` = map_chr(Sentences, convert)) ```

#### Validation

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

### Puzzle #363

As I said this riddle has the same data set, but different task. Now we have to find all “words” ending with number. Be careful we are working on numbers, not digits this time. And we have to increase those numbers by one. Tricky, but not so hard.

```library(tidyverse)

input = read_excel("Excel/363 Increment last number by 1.xlsx", range = "A1:A10")
test  = read_excel("Excel/363 Increment last number by 1.xlsx", range = "B1:B10")```

#### Transformation

```add_one = function(x) {
original_length <- nchar(x)
incremented_number <- as.numeric(x) + 1
}

process_word = function(word) {
if (str_detect(word, "\\d+\$")) {
parts = str_match(word, "(.*?)(\\d+)\$")
} else {
return(word)
}
}

process_text = function(text) {
words = unlist(str_split(text, " "))
processed_words = map_chr(words, process_word)
text_concat = str_c(processed_words, collapse = " ")
return(text_concat)
}

result = input %>%

#### Validation

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

Feel free to comment, share and contact me with advices, questions and your ideas how to improve anything.

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.

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.