: “Solving ExcelBI Puzzles with R: A Data Analytics Adventure”

by jsendak | Mar 12, 2024 | DS Articles

Puzzles no. 404–408

Puzzles

Author: ExcelBI

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

Puzzle #404

Can analyst make something that looks good? Of course… Can analyst draw with numbers? Once more yeah. But today, like some times in past already, we have another way. I usually name making charts and dashboards — drawing or painting with numbers. Not today. We just recreate one specific graphic filling fields of spreadsheet (or in our case, make this graphic in console). And as you see above it is… Star-Spangled Banner aka flag of the USA.

Load libraries and data

library(tidyverse)
library(readxl)

test = read_excel("Excel/404 Generate US ASCII Flag.xlsx", range = "A1:AL15",
                  col_names = FALSE, .name_repair = "unique") %>% as.matrix()
# remove attribute "names" from matrix
attr(test, "dimnames") = NULL

result = matrix(NA, nrow = 15, ncol = 38)r

Transformation

# border of flag
result[1,] = "-"
result[15,] = "-"
result[2:14,1] = "|"
result[2:14,38] = "|"

# stripe section
for (i in 2:14){
  for (j in 2:37){
    if (i %% 2 == 0){
      result[i,j] = 0
    } else {
      result[i,j] = "1"
    }
  }
}

# star section
for (i in 2:10){
  for (j in 2:12){
    if (i %% 2 == 0){
      if (j %% 2 == 0){
        result[i,j] = "*"
      } else {
        result[i,j] = NA
      }
    } else {
      if (j %% 2 == 0){
        result[i,j] = NA
      } else {
        result[i,j] = "*"
      }
    }
  }
}

Validation

identical(result, test)
# [1] TRUE

Puzzle #405

Did you know sandwich numbers? That is that unique kind of numbers that as both neighbours has prime numbers, so they are like between two slices of toast bread. And our task is to find first 100 of sandwich numbers together with their “breads” aka neighbouring primes.

Load libraries and data

library(tidyverse)
library(readxl)

test = read_excel("Excel/405  Sandwich Numbers.xlsx", range = "A1:C101") %>% janitor::clean_names()

Transformation

is_prime <- function(x) {
  if (x <= 1) return (FALSE)
  if (x == 2 || x == 3) return (TRUE)
  if (x %% 2 == 0) return (FALSE)
  for (i in 3:sqrt(x)) {
    if (x %% i == 0) return (FALSE)
  }
  TRUE
} # of course I could use primes package, but I decided otherwise :D

is_sandwich <- function(x) {
  is_prime(x-1) && is_prime(x+1)
}

find_first_n_sandwich_numbers <- function(no) {
  keep(1:10000, is_sandwich) %>%
    unlist() %>%
    head(no)
}

a = find_first_n_sandwich_numbers(100)

check = tibble(sandwich_number = a) %>%
  mutate(before_number = sandwich_number - 1,
         after_number = sandwich_number + 1) %>%
  select(2,1,3)

Validation

all.equal(test, check)
# [1] TRUE

Puzzle #406

I suppose that in every educational system at least once Pythagorean Theorem is mentioned. In this puzzle given area and length of hypotenuse we have to find length of other two sides of right angled triangle. Of course there probably is some formula to do it at once, but I wanted to show you step by step way to do it. We are gonna use library numbers to use very useful function divisors. Otherwise we would have to check every combination of numbers to find numbers behind area of triangle.

Load libraries and data

library(tidyverse)
library(readxl)
library(numbers)

input = read_excel("Excel/406 Right Angled Triangle Sides.xlsx", range = "A2:B10") %>%
  janitor::clean_names()
test  = read_excel("Excel/406 Ri

Transformation

process_triangle = function(area, hypotenuse) {
  ab = 2 * area
  ab_divisors = divisors(ab)
  grid = expand_grid(a = ab_divisors, b = ab_divisors) %>%
    mutate(r = a * b,
           hyp = hypotenuse,
           hyp_sq = hyp**2,
           sides_sq = a**2+b**2,
           check = hyp_sq == sides_sq,
           base_shorter = a < b) %>%
    filter(check, base_shorter) %>%
    select(base = a, perpendicular = b)
  return(grid)
}

result = input %>%
  mutate(res = map2(area, hypotenuse, process_triangle)) %>%
  unnest(res) %>%
  select(3:4)

Validation

identical(result, test)
# [1] TRUE

Puzzle #407

I like cyphering puzzles and I am really happy that we have one again. Today we merge 2 types of cyphers: Ceasar and Mirror, so we have reverse and shift coded text to succeed. Let’s check how it went.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/407 Mirror Cipher.xlsx", range = "A1:B10") %>%
  janitor::clean_names()
test  = read_excel("Excel/407 Mirror Cipher.xlsx", range = "C1:C10") %>%
  janitor::clean_names()

Transformation

code = function(text, shift) {
  if (shift == 0) {
    keycode = letters
  }
  else {
    keycode = c(letters[(26-shift+1):26],letters[1:(26-shift)])
  }
  keytable = tibble(letters = letters, code = keycode)

  chars = str_split(text, "")[[1]] %>%
    rev()
  tab = tibble(text = chars) %>%
    left_join(keytable, by = c("text" = "code")) %>%
    mutate(letters = if_else(is.na(letters), " ", letters)) %>%
    select(letters) %>%
    pull() %>%
    str_c(collapse = "")
  return(tab)
}

result = input %>%
  mutate(answer_expected = map2_chr(plain_text, shift, code))

Validation

identical(result$answer_expected, test$answer_expected)
# [1] TRUE

Puzzle #408

Time: physics, math, eternity… but does time have any geometry? Stephen Hawking probably would say something about it, but we have much easier issue. We only need to check geometry of clock face. There are two or three hands on it. As long as we present time as cycles, we use circle presenting this cycle and positions of hands on the face of round, circular face of clock are enabling us to read time measurements. So lets check what angle hands presents at specific times of a day.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/408 Angle Between Hour and Minute Hands.xlsx", range = "A1:A10")
test  = read_excel("Excel/408 Angle Between Hour and Minute Hands.xlsx", range = "B1:B10")

Transformation

angle_per_min_hh = 360/(60*12)
angle_per_min_mh = 360/60

result = input %>%
  mutate(time = as.character(Time),
         Time = str_extract(time, "sd{2}:d{2}")) %>%
  separate(Time, into = c("hour","mins"), sep = ":") %>%
  mutate(hour = as.numeric(hour),
         mins = as.numeric(mins),
         hour12 = hour %% 12,
         period_hh = hour12*60 + mins,
         period_mh = mins,
         angle_hh = period_hh * angle_per_min_hh,
         angle_mh = period_mh * angle_per_min_mh,
         angle_hh_to_mh = if_else(angle_hh > angle_mh,
                                  360 - (angle_hh - angle_mh),
                                  angle_mh - angle_hh)) %>%
  select(answer_expected = angle_hh_to_mh)

# there is probably single formula for this,
# but I wanted to show you this step by step.

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

Analyzing ExcelBI Puzzles and Their R Package Solutions

The article focuses on an interesting series of puzzles presented by ExcelBI and their solutions using the R programming language. Each puzzle presents a programming challenge and gives insights into how data analytics and programming skills can be used to solve real-world problems. While the author mostly uses R’s base packages, there’s also usage of external libraries like tidyverse, readxl, and numbers.

Key Points from the Puzzles

Puzzle #404

In the first puzzle, the task is to reproduce the flag of USA using matrix transformations in R based on a given pattern. This task shows the versatility of R programming in graphical work besides typical numerical analysis.

Puzzle #405

Puzzle #405 talks about finding a ‘sandwich numbers’, which are numbers sandwiched between two prime numbers. This interesting task reflects the power of mathematical functions in R, such as identifying prime numbers.

Puzzle #406

Using the Pythagorean theorem, the puzzle aims to find the length of two sides in a right-angled triangle given the area and hypotenuse. Comprising mathematical functions like area calculations and hypotenuse value derivation, this puzzle demonstrates the use of R programming in geometrical problems.

Puzzle #407

Puzzle #407 presents a text deciphering task involving the use of Caesar and mirror cyphers. This shows how R can be leveraged for decryption and encoding tasks, especially useful in cybersecurity.

Puzzle #408

Finally, the last puzzle has a task to find the angle between hour and minute hands at a specific time. The problem uses mathematical transformations and notion of time to solve a real-world problem.

Future Implications and Developments

These puzzles showcase the power, versatility, and breadth of the R programming language. Not restricted to just statistical analyses, users of R can leverage its features to solve a wide range of problems, from graphical reproductions and geometrical calculations to coding cyphers.

It is expected that the role of R will continue to expand, including into non-traditional areas, given the language’s open-source nature and active community of contributors.

Actionable Advice

1. Enhance R Skills: The ability to handle diverse problems using R will likely become an increasingly valuable skill in the future. Therefore, learning R and improving programming skills can open up new opportunities.
2. Look Beyond Analytics: R isn’t just a tool for data analytics. These puzzles show that R can be used in a variety of tasks. Focus on understanding the principles and functionality of R to unlock its full potential.
3. Engage with the Community: The R community is a rich resource for learning and problem-solving. Don’t hesitate to engage, ask questions, and contribute when you can.

Read the original article