“R Solution for Excel Puzzles: Enhancing Data Manipulation and Problem-Solving Skills”

by jsendak | Jan 15, 2024 | DS Articles

Puzzles no. 364–368

Puzzles

Author: ExcelBI

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

Puzzle #364

In this puzzle we have several numbers to check. But what is the condition those numbers should met? Number should be like this:
1st digit -> 2nd digit = 1st digit +1 or 1st digit-1 -> 3rd digit = 2nd digit +2 or 2nd digit-2 and so on. So absolute value of difference between consecutive digits should increase by 1. Let’s check it.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/364 Difference Consecutive Digits.xlsx", range = "A1:A10")
test  = read_excel("Excel/364 Difference Consecutive Digits.xlsx", range = "B1:B6")

Transformation

check_seq_diff = function(x) {
  digits = as.character(strsplit(as.character(x), "")[[1]])
  diffs = map2_dbl(digits[-length(digits)],
                   digits[-1],
                   ~abs(as.numeric(.x) - as.numeric(.y)))
  all(diffs == 1:length(diffs))
}

result = input %>%
  mutate(test = map_lgl(Number, check_seq_diff)) %>%
  filter(test) %>%
  select(-test)

Validation

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

Puzzle #365

Second puzzle was not really hard, but certainly interesting. We need to check if there is any possibility that one of digits in number is a sum of all the others. Logically it should be the largest digits. Lets find out.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/365 One digit is Equal to Sum of other Digits.xlsx", range = "A1:A10")
test  = read_excel("Excel/365 One digit is Equal to Sum of other Digits.xlsx", range = "B1:B5")

Transformation

evaluate = function(number) {
  digits = as.numeric(unlist(strsplit(as.character(number), "")))
  check  = purrr::map_lgl(digits, ~ .x == sum(digits[-which(digits == .x)]))
  return(any(check))
}

result = input %>%
  mutate(eval = map_lgl(Number, evaluate)) %>%
  filter(eval) %>%
  select(`Answer Expected` = Number)

Validation

identical(result, test)
# [1] TRUE

Puzzle #366

Today we get some weirdly mixed string of letters and digits. We need to make little bit more in order. To do it we need to break it into digits and letters and then treat those vectors as zip fastener: take one element from first, then second, and first again and so on. Lets zip it up!

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/366 Exchange Alphabets and Numbers.xlsx", range = "A1:A10")
test  = read_excel("Excel/366 Exchange Alphabets and Numbers.xlsx", range = "B1:B10")

Transformation

zip_string = function(string) {
  letters = str_extract_all(string, "[a-zA-Z]")[[1]]
  digits = str_extract_all(string, "[0-9]")[[1]]

  vec_diff = abs(length(letters) - length(digits))
  if (vec_diff > 0) {
    if (length(letters) > length(digits)) {
      digits = c(digits, rep("", vec_diff))
    } else {
      letters = c(letters, rep("", vec_diff))
    }
  }
  result = map2_chr(letters, digits, function(x, y) paste0(x, y)) %>% paste0(collapse = "")

  return(result)
}

result = input %>%
  mutate(`Answer Expected` = map_chr(Words, zip_string)) %>%
  select(-Words)

Validation

identical(result, test)
# [1] TRUE

Puzzle #367

This puzzle reminded me “Sea Battle” game. We get table with sentence and index. We have to check if index is pointing to first letter of any word in sentence. If this can be confirm, just take word, like we shot vessels in “Sea Battle”.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/367 Extract the Word Starting at an Index.xlsx", range = "A1:B10")
test  = read_excel("Excel/367 Extract the Word Starting at an Index.xlsx", range = "C1:C10")

Transform

extract_word_by_index = function(sentence, index) {
  word_pos = str_locate_all(sentence, "w+") %>% as.data.frame()
  word = word_pos %>% filter(start == index)

  if (nrow(word) == 0) {
    word = NA_character_
  } else {
    word = sentence %>% str_sub(start = word$start, end = word$end)
  }

  return(word)
}

result = input %>%
  mutate(`Answer Expected` = map2_chr(Sentence, Index, extract_word_by_index))

Validation

identical(result$`Answer Expected`, test$`Answer Expected`)
# [1] FALSE
# Mistake in the puzzle

input %>% bind_cols(test) %>% bind_cols(result$`Answer Expected`)

Puzzle #368

This puzzle brought many memories. When I was teenager and young adult, all “comunicational” revolution happened. Firstly those old-style mobiles, then more inteligent models, and finally smartphones come up in less then 10 years (at least in Poland). And we were texting a lot on those. If somebody get fluent in texting using multitapping, then it is possible in many different ways (below table, in pocket, behind back).
But get back to puzzle. We need to encode given words into multitap cypher.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/368 Multi Tap Cipher.xlsx", range = "A1:A10")
test  = read_excel("Excel/368 Multi Tap Cipher.xlsx", range = "B1:B10")

Transformation

encode = function(word) {
  chars = str_split(word, "")[[1]]
  pos = match(chars, letters)
  tibble = tibble(
    Letter = chars,
    Position = pos,
    Button = calculate_button(pos),
    Taps = calculate_taps(pos),
    repetitions = (map2_chr(Button, Taps, ~ rep(.x, .y) %>% paste0(collapse = "")))
  ) %>%
    pull(repetitions) %>%
    str_c(collapse = "")
}

calculate_button <- function(letter_pos) {
  case_when(
    letter_pos <= 15 ~ ((letter_pos - 1) %/% 3) + 2,
    letter_pos <= 19 ~ 7,
    letter_pos <= 22 ~ 8,
    TRUE ~ 9
  )
}

calculate_taps <- function(letter_pos) {
  case_when(
    letter_pos <= 15 ~ ((letter_pos - 1) %% 3) + 1,
    letter_pos <= 19 ~ ((letter_pos - 16) %% 4) + 1,
    letter_pos <= 22 ~ ((letter_pos - 20) %% 3) + 1,
    TRUE ~ ((letter_pos - 23) %% 4) + 1
  )
}

result = input %>%
  mutate(`Answer Expected` = map_chr(Words, encode))

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. Contact me on Linkedin if you wish as well.

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.

Continue reading: R Solution for Excel Puzzles

Key Points

The key focus of the text is a series of puzzles that involve various mathematical and algorithmic tasks in the programming language R. The five puzzles tackled in the text involve different types of data manipulation, including sequential number check, single digit sum check, string reordering, word extraction, and encoding.

Comprehensive Follow-up

Each of these puzzles represents a unique problem-solving scenario, which enhances both numerical and programming proficiency. While they are presented as entertaining brain exercises, they illustrate important concepts in data manipulation and provide an interesting approach to teaching coding techniques in R.

Long-term implications

Such hands-on puzzle-solving exercises have a significant impact in the long term as they can improve the users’ understanding of the programming language, enhance their logical thinking abilities and deductive reasoning in problem-solving scenarios, and heighten their adaptability by enabling them to solve various data manipulation problems.

Possible Future Developments

Presenting more complex problems in the form of puzzles could be a promising method for learning advanced concepts and techniques in R and other programming languages. This approach could be particularly effective for developing machine learning models or learning complex mathematical formulas that are commonly used in managing large datasets. Inclusion of automation techniques or AI-related challenges could add a new level of complexity and make learning process more engaging.

Actionable Advice

R programmers – from beginners to intermediate coders – can benefit immensely from these puzzle-solving exercises. Faced with unique sets of challenges, they will be compelled to explore different aspects of the language and its syntax, understand logic flow, and come up with efficient solutions in real-world scenarios. Aspiring programmers should regularly engage with such tasks to improve their logical reasoning and coding skills. Experienced professionals could also try these out as a form of refreshers and even create their own puzzles to test their expertise.

Read the original article