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.

Loading libraries and data

library(tidyverse)
library(readxl)

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.

Loading libraries and data

library(tidyverse)
library(readxl)
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) %>%
  select(`Expected Answer` = Numbers)

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.

Loading libraries and data

library(tidyverse)
library(readxl)

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

identical(result2$String, test$`Expected Answer`)
#> [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.

Loading libraries and data

library(tidyverse)
library(readxl)

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)
library(readxl)

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.

Loading libraries and data

library(tidyverse)
library(readxl)

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.