R Solution for Excel Puzzles

Puzzles no. 549–553

Puzzles

Author: ExcelBI

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

Puzzle #549

We do not discriminate any people and technology in solving those tasks, but today is day of discrimination. We woke up this morning and said: we don’t like O letter, letter O is BANNED. But being serious I love all numbers, but Vijay is telling us today to find only those number which text representation in English doesn’t contains letter O both capital and lower case. Fortunatelly in R there is nice package that can help us do it quickly.

Loading libraries and data

library(tidyverse)
library(readxl)
library(english)

path = "Excel/549 Oban Numbers.xlsx"
test  = read_excel(path, range = "A1:A455")

Transformation

input = data.frame(number = 1:1000) %>%
  filter(!str_detect(english(number), "o"))

Validation

all.equal(input$number, test$`Answer Expected`)
#> [1] TRUE         

Puzzle #550

We did similar job just several puzzles ago, but today we have additional twist. Last time we scanned only rows and columns searching for even numbers, but today we have to more dimensions to check: rows and columns from the other end. We can make complicated code to point it or another complicated code to spin matrix according to our needs. I choose second version

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/550 Pick the Odd Numbers in a Grid.xlsx"
input1 = read_excel(path, range = "A2:B3", col_names = FALSE) %>% as.matrix()
test1  = read_excel(path, range = "G2:G2", col_names = FALSE) %>% pull()

input2 = read_excel(path, range = "A5:C7", col_names = FALSE) %>% as.matrix()
test2  = read_excel(path, range = "G5:G5", col_names = FALSE) %>% pull()

input3 = read_excel(path, range = "A9:C11", col_names = FALSE) %>% as.matrix()
test3  = read_excel(path, range = "G9:G9", col_names = FALSE) %>% pull()

input4 = read_excel(path, range = "A13:D16", col_names = FALSE) %>% as.matrix()
test4  = read_excel(path, range = "G13:G13", col_names = FALSE) %>% pull()

input5 = read_excel(path, range = "A18:E22", col_names = FALSE) %>% as.matrix()
test5  = read_excel(path, range = "G18:G18", col_names = FALSE) %>% pull()

Transformation

pick_odds <- function(M) {
  all <- as.numeric(apply(rbind(M, t(M), M[,nrow(M):1],t(M)[,nrow(t(M)):1]) , 1, paste0, collapse = ""))
  paste(all[all %% 2 == 1], collapse = ", ")
}

Validation

all.equal(pick_odds(input1), test1) # 54 shouldn't be here
all.equal(pick_odds(input2), test2) # TRUE
all.equal(pick_odds(input3), test3) # only discrepancy is 011 vs 11
all.equal(pick_odds(input4), test4) # TRUE
all.equal(pick_odds(input5), test5) # TRUE

Puzzle #551

Math, geometry is everywhere even in chemistry of our bodies. In DNA for example we have two pairs of nucleobases: adenine, cytosine, guanine and thymine. They are compemantary in pairs. Adenine always pairs with thymine on other part of helix, cytosine always with guanine. In puzzle today we have small part of DNA with one element masked with X. We need to find out what should be under X, considering that after completing reverse sequence would be complementary to original. So I realized that the most efficient and smartest way to find it would be to fold sequence in half and check corresponding position, or mathematically, check on which position from start we have X, and find that position from end, and only reverse one nucleobases, not all of them.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/551 Watson-Crick Palindromes.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:B10")

Transformation

compliment = function(x) {
  recode(x, "A" = "T", "T" = "A", "C" = "G", "G" = "C", .default = "N")
}

result = input %>%
  mutate(nchar = nchar(.$String),
         pos = str_locate(.$String, "X"),
         char = str_sub(.$String, nchar - pos[,1] + 1, nchar - pos[,1] + 1),
         compliment = compliment(char)) %>%
  select(`Answer Expected` = compliment)

Validation

identical(result, test)
#> [1] TRUE

Puzzle #542

And we have something I like. Sometimes using RegEx showing to much power, like cannon against the fly, sometimes it is exactly what we need. And today we don’t really need to extract something and that’s it. We have also something to manipulate with them. And this is the place where capturing groups shows their capabilities.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/552 Regex Challenges.xlsx"
input = read_excel(path, range = "A1:B4")
test  = read_excel(path, range = "C1:C4")

Transformation

q1 = input %>%
  filter(row_number() == 1) %>%
  mutate(Answer = str_replace(String, 
                              "(\\d{2})(\\d{2})-(\\d{2})-(\\d{2})",
                              "\\3-\\4-\\2"))

q2 = input %>%
  filter(row_number() == 2) %>%
  mutate(Answer = str_replace(String, 
                              "^(\\w+) \\w+ (\\w+)$",
                              "\\2, \\1"))

q3 = input %>%
  filter(row_number() == 3) %>%
  mutate(Answer = gsub("\\b(\\w)(\\w*?)(\\w)\\b", 
                       "\\U\\1\\E\\2\\U\\3", 
                       String, perl = TRUE))

answers = bind_rows(q1, q2, q3) %>%
  select(Answer)

Validation

all.equal(answers, test, check.attributes = FALSE)
#> [1] TRUE

Puzzle #553

And one of my favourite types of Vijay’s riddles — ASCII Drawing in matrices. And now we have something like old photo from our computer’s old family album — great-great-…-greatdad Abacus. Let’s draw it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/553 ASCII Abacus.xlsx"
test  = read_excel(path, range = "B2:T13", col_names = FALSE) %>% as.matrix()

Transformation

M = matrix(NA_character_, nrow = 12, ncol = 19)

for (i in 1:12) {
  for (j in 1:19) {
    if (i %in% c(1, 5, 12)) {
      M[i, j] = "---"
    } else if (i %in% c(2, 6)) {
      M[i, j] = "|"
    } else {
      M[i, j] = "O"
    }
  }
}

for (i in 2:11) {
  M[i, 1] = "|"
  M[i, 19] = "|"
}

Validation

all(M == test) %>% print()
as.data.frame(M)

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.
On my Github repo there are also solutions for the same puzzles in Python. Check it out!

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.