R Solution for Excel Puzzles

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.

Load libraries and data

library(tidyverse)
library(readxl)

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") %>%
  mutate(`Answer Expected` = str_remove_all(as.character(`Answer Expected`), "\\s"))

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.

Load data and libraries

library(tidyverse)
library(readxl)

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 %>%
  mutate(`Answer Expected` = map_chr(Strings, reverse_alpha))

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.

Load libraries and data

library(tidyverse)
library(readxl)

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.

Load libraries and data

library(tidyverse)
library(readxl)

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.

Load libraries and data

library(tidyverse)
library(readxl)

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
  str_pad(incremented_number, original_length, pad = "0")
}

process_word = function(word) {
  if (str_detect(word, "\\d+$")) {  
    parts = str_match(word, "(.*?)(\\d+)$")  
    return(paste0(parts[2], add_one(parts[3])))  
  } 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 %>%
  mutate(`Answer Expected` = map_chr(Sentences, process_text))

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.