Excel Puzzles Solved with R

by jsendak | Apr 29, 2024 | DS Articles

Puzzles no. 439–443

Puzzles

Author: ExcelBI

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

Puzzle #439

From time to time in ExcelBI challenges, hard puzzles come to us. And this time it was one of such situations. Input we were given was matrix with values, and coordinate values as rows and columns names. Didn’t look hard unless I read the task. Then we were given pairs of coordinates to find value for them, but there was one small problem, some of coordinates were not equal with our table. Some of values pointed in “space” between each coordinate. Like on map with meridians and parallels, we have some lines but also vast area just in between them. There comes technique called bilinear interpolation. We need to check what is the difference between lines and find value that is corresponding this fraction. Lets check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/439 - Bilinear Interpolation.xlsx", range = "A1:B5")
lookup = read_excel("Excel/439 - Bilinear Interpolation.xlsx", range = "E1:M7")
test = read_excel("Excel/439 - Bilinear Interpolation.xlsx", range = "C1:C5")

Transformation

lookup = lookup %>%
  column_to_rownames("a/b")

bilinear_interpolation = function(a, b, lookup_table) {
  a_low = floor(a * 10) / 10
  a_high = ceiling(a * 10) / 10
  b_low = floor(b)
  b_high = ceiling(b)

  dist_a = a_high - a_low
  dist_b = b_high - b_low

  vlook_1 = lookup_table[as.character(b_low), as.character(a_low)]
  vlook_2 = lookup_table[as.character(b_high), as.character(a_low)]
  vlook_3 = lookup_table[as.character(b_low), as.character(a_high)]

  x_1 = if_else(dist_b == 0, 0, (vlook_2 - vlook_1) * (b - b_low) / dist_b)
  x_2 = if_else(dist_a == 0, 0, (vlook_3 - vlook_1) * (a - a_low) / dist_a)

  value = vlook_1 + x_1 + x_2
  return(round(value, 3))
}

result = input %>%
  mutate(`Answer Expected` = map2_dbl(a, b, ~bilinear_interpolation(.x, .y, lookup)))

Validation

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

Puzzle #440

Today slogan is: “Find numbers by properties”, and we are looking for numbers between 1 and 100 that can be expressed as a sum of two squared numbers that are not equal to each others. Like those snowmen above. It looks like it is really funny and suprisingly easy solution.

Loading libraries and data

library(tidyverse)
library(readxl)

test = read_excel("Excel/440 List of Numbers Expressed as Sum of Two Squares.xlsx", range = "A1:A30")

Transformation

is_sum_of_squares = function(x) {
  squares = (1:floor(sqrt(x)))^2
  any(map_lgl(squares,  ~ any(x == .x + squares[squares != .x])))
}

result = data.frame(numbers = 1:100 %>% as.numeric()) %>%
  filter(map_lgl(numbers, is_sum_of_squares))

Validation

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

Puzzle #441

Sometimes, something are too long to read, too long to write, and we are developing concepts as abbreviations, acronyms, number ranges and so on. We are skipping some characters like the frog from illustration. But our task is to do exactly opposite today. We do not need to skip any elements, but rather to step on each and every waterlily leaf on the pond. If string contains written range of numbers we need to get all numbers from this range. Find out how I did it.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/441 Integer Intervals.xlsx", range = "A1:A7")
test  = read_excel("Excel/441 Integer Intervals.xlsx", range = "B1:B7")

Transformation

result = input %>%
  mutate(rn = row_number()) %>%
  separate_rows(Problem, sep = ", ") %>%
  mutate(Problem = map(Problem, ~{
    if(str_detect(., "-")){
      range = str_split(., "-")[[1]]
      seq(as.numeric(range[1]), as.numeric(range[2]))
    } else {
      as.numeric(.)
    }
  })) %>%
  unnest(Problem) %>%
  summarise(`Answer Expected` = str_c(sort(unique(Problem)), collapse = ", "), .by = rn) %>%
  select(-rn)

Validation

identical(result, test)
# [1] TRUE

Puzzle #442

Ciphers, ciphers, ciphers… I didn’t know that people has so many ideas to hide messages from public visibility. Today we have Columnar Transposition Cipher. We use keyword as kind of template and then we can code even the longest messages. First we need to check our keyword, then rank its letters alphabetically. For example MARS would be ranked as 2134. Then we take text for encoding and wrap it after number of characters equal to length of keyword. In our example every 4 characters should be in separate rows. Then we have to read columns that appeared after folding in order like in ranked keyword, so first we need to read second column, then first, then third and fourth. But this example is easy. Check the solution.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/442 Columnar Transposition Cipher.xlsx", range = "A1:B10")
test  = read_excel("Excel/442 Columnar Transposition Cipher.xlsx", range = "C1:C10")

Transfromation

encode = function(text, keyword){
  keyword = strsplit(keyword, "")[[1]] %>%
    rank(ties.method = "first")
  l_key = length(keyword)

  text = str_extract_all(text, "[a-z]")[[1]]
  text_filled = c(text, rep("", l_key - length(text) %% l_key))

  matrix_text = matrix(text_filled, ncol = l_key, byrow = TRUE)
  matrix_text = matrix_text[, order(keyword)] %>% t()
  matrix_text = matrix_text %>%
    apply(1, paste, collapse = "") %>%
    paste(collapse = " ")

  return(matrix_text)
}

result = input %>%
  mutate(`Answer Expected` = map2_chr(`Plain Text`, Keyword, encode))

Validation

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

Puzzle #443

Do you remember from your childhood those word search riddles. So ExcelBI decided to throw us into those times again. We need to find some names in word search. Fortunately, they are all vertical, all left-to-right, so we have probably the easiest case. But when we were kids, when somebody find word usually crossed it with line or encircle them. What we have to do? Find words and replace all the other positions with “x”. So lets find a bird then.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/443 Birds Search.xlsx", range = "B2:K11", col_names = FALSE)
list  = read_excel("Excel/443 Birds Search.xlsx", range = "M1:M12")
test  = read_excel("Excel/443 Birds Search.xlsx", range = "O2:X11", col_names = FALSE)
colnames(test) = c(1:10)

Transformation

find_bird = function(grid, bird_name) {
  grid = unite(grid, col = "all", everything(), sep = "") %>%
    mutate(nrow = row_number()) %>%
    mutate(coords = str_locate(all, bird_name)) %>%
    na.omit() %>%
    select(-all)
  return(grid)
}

coords = map_dfr(list$Birds, ~find_bird(input, .x)) %>%
  mutate(start = coords[,1], end = coords[,2]) %>%
  select(-coords) %>%
  rowwise() %>%
  mutate(cols = list(seq(start, end))) %>%
  select(-start, -end) %>%
  unnest(cols) %>%
  mutate(check = T)

input2 = input %>%
  mutate(nrow = row_number()) %>%
  pivot_longer(cols = -nrow, names_to = "col", values_to = "value") %>%
  mutate(col = str_extract(col, "d+") %>% as.numeric()) %>%
  left_join(coords, by = c("nrow" = "nrow", "col" = "cols")) %>%
  mutate(check = ifelse(is.na(check), F, T),
         value = ifelse(check, value, 'x')) %>%
  select(-check) %>%
  pivot_wider(names_from = col, values_from = value) %>%
  select(-nrow)

Validation

identical(input2, test)
# [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

Insights from ExcelBI Puzzle Solving Using R

The provided content describes the process of using R to solve five ExcelBI puzzles. The puzzles emphasize different aspects namely dealing with matrix interpolation, finding numbers that can be expressed as the sum of two squared numbers, parsing strings with numerical ranges, creating a Columnar Transposition Cipher, and performing a word search on given texts. The solutions rely on various R functionalities including loading libraries and data, performing transformations, and validating output. This article explores the long-term implications and future developments of each puzzle.

Bilinear Interpolation

The first puzzle was about solving a bilinear interpolation problem, an interpolation technique used for two-dimensional scenarios. Its long-term implications are manifold. Firstly, mastering bilinear interpolation introduces problem solvers to algorithms that are essential for graphics programming. Secondly, the use of the tidyverse and readxl libraries in R for data processing demonstrates the possibilities of using R in data analysis. Potential future developments include refining the function to handle missing data or working with more complex datasets and improving the efficiency of the function.

Finding Numbers by Properties

This task required finding numbers that can be expressed as a sum of two unequal squares. Long-term, this puzzle urges problem-solvers to understand that brute force methods can still be efficient for smaller ranges. Future enhancements could include extending the range or adding more conditions or restrictions to the numbers that are being found. Additionally, this task illuminates the power of the R programming language for mathematical computations.

Intervals and Numbers

The third task required all numbers from a written range to be parsed and returned. The ability to break down and handle string representations of numerical ranges could be useful in various domains, including data visualization, data cleaning, and machine learning. Future advancements may include dealing with more complex arrangements of intervals, handling different formats of number ranges, or automating the process for larger datasets.

Columnar Transposition Cipher

The fourth task introduced a Columnar Transposition Cipher, exhibiting the potential of R for text manipulation and related tasks, including data encryption and decryption. This technique has extensive implications for cybersecurity or any task requiring secure text transportation. Given the ever-evolving field of data security, future developments might focus on implementing more complex encryption techniques or building more secure channels of communication.

Word Search and Substitution

The final task involved finding specific words within a given text grid and replacing the remaining letters with “x”. This could be very useful in areas such as text analytics and natural language processing. Future enhancements include handling various orientations of words besides vertical and left-to-right, working with multiple word grids at once, or dealing with non-English languages.

Actionable Advice

• While solving puzzles is beneficial for practice, consider applying these techniques to real-world problems to increase their utility.
• Maximize the use of the wide range of libraries offered by R to improve efficiency and functionality.
• Consistently refine and optimize written code to handle bigger datasets and more complex problems effectively.
• Learn and implement more sophisticated techniques for tackling more advanced issues such as data security and natural language processing.
• Join online R communities to get up to date information on the latest R packages and methods to tackle different data issues.

Read the original article