R Solution for Excel Puzzles

Puzzles no. 554–558

Puzzles

Author: ExcelBI

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

Puzzle #554

Another interesting number called after Indian mathematician. We need to find first 50 Kaprekar numbers of any order. What does it mean? We need to find numbers which square cut in any point (after first, second or any other digit) give us two numbers that sums up to original number. So we need to cut numbers (not divide, not subtract, but cut). It is terribly slow if we iterate over long numbers treating it as characters. But today I found out how to do it using numbers only. Check it out.

PS. And I threw all hands on board, which mean that I used parallel computing.

Loading libraries and data

library(tidyverse)
library(readxl)
library(parallel)

path = "Excel/554 Kaprekar Numbers.xlsx"
test = read_excel(path, range = "A1:A51")

Transformation

check_kaprekar_fast = function(n) {
  nsqr = n^2
  digits = floor(log10(nsqr)) + 1
  for (split_pos in 1:(digits - 1)) {
    right_part = nsqr %% 10^split_pos
    left_part = nsqr %/% 10^split_pos
    if (right_part > 0 && left_part + right_part == n) {
      return(TRUE)
    }
  }
  return(FALSE)
}

parallel_kaprekar_check = function(n_values) {
  num_cores = detectCores() - 1
  cl = makeCluster(num_cores)
  clusterExport(cl, "check_kaprekar_fast")
  result = parLapply(cl, n_values, check_kaprekar_fast)
  stopCluster(cl)
  return(unlist(result))
}

n_values = 4:1000000
kaprekar_flags = parallel_kaprekar_check(n_values)

df = data.frame(n = n_values, is_kaprekar = kaprekar_flags) %>%
  filter(is_kaprekar) %>%
  head(50) %>%
  select(n)

Validation

all.equal(df, test, check.attributes = FALSE) 
# TRUE

Puzzle #555

It looks like somebody wrote down cities to visit, but it would be to easy just to sort them. We are given special way of sorting first row should be sorted descending, second — ascending, and so on even and odd rows. This is quite tricky, but let me show you how I did it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/555 Order Cities.xlsx"
input = read_excel(path, range = "A1:E19")
test  = read_excel(path, range = "G2:K19", col_names = FALSE)
names(test)  = c("1", "2", "3", "4", "5")

Transformation

result = input %>%
  mutate(rn = row_number()) %>%
  select(rn, everything()) %>%
  pivot_longer(-rn, names_to = "key", values_to = "value") %>%
  group_by(rn) %>%
  arrange(
    rn,
    desc(if_else(rn %% 2 == 0, value, NA_character_)),
    if_else(rn %% 2 != 0, value, NA_character_)
  ) %>% 
  mutate(rn2 = row_number(),
         key = if_else(is.na(value), NA, key)) %>%
  ungroup() %>%
  select(-value) %>%
  pivot_wider(names_from = rn2, values_from = key) %>%
  select(-rn)

Validation

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

Puzzle #556

We built many structures so far, and pyramids along them were numerous as well. Today we have triangle made of triangular numbers. Each step up is made of next element of sequence of triangular numbers. We already discussed this kind of numbers many time. So we could say many things are the same as many time before. And as before I will do it using matrix.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/556 Generate Triangle Cumsum.xlsx"
input = read_excel(path, range = "A1:A1", col_names = FALSE) %>% pull()
test  = read_excel(path, range = "B2:T11:", col_names = FALSE) %>% as.matrix()

Transformation

M = matrix(NA_real_, nrow = input, ncol = 2 * input - 1)
p = 1:input %>% cumsum()

for (i in 1:10) {
  M[i, (input - i + 1):(input + i - 1)] = rev(p)[i]  
}

Validation

all.equal(M, test, check.attributes = FALSE) 
# TRUE

Puzzle #557

We have three main tasks today all based on fact that RegEx is pretty freshly available in Excel. So let get heavy weapon called regular expressions ready for our puzzle today.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/557 Regex Challenges 2.xlsx"
input = read_excel(path, range = "A1:A6")
test  = read_excel(path, range = "C1:C6") %>%
  mutate(`Answer Expected` = as.numeric(`Answer Expected`))

Transformation

q1 = input %>%
  filter(row_number() == 1) %>%
  mutate(Answer = str_extract(String, "\\d+(?!.*\\d)") %>% as.numeric())

q2 = input %>%
  filter(row_number() %in% c(2, 3)) %>% 
  mutate(Answer = str_detect(String,  "(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)") %>% as.numeric())

q3 = input %>%
  filter(row_number() %in% c(4, 5)) %>%
   mutate(Answer = str_detect(String,  "^(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9])(?=.*[^A-Za-z0-9])(?=\\S+$).{8,}$") %>% as.numeric())

answer = bind_rows(q1, q2, q3)

Validation

all.equal(answer$Answer, test$`Answer Expected`, check.attributes = FALSE)
#> [1] TRUE

Puzzle #558

I like the way how structures similar to Python dictionaries can be packed and unpacked to series of keys and values. And we have it today as our puzzle. We need to split dictionary type string to strings made of keys and values respectivelly.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/558 Unpack Dictionary.xlsx"
input = read_excel(path, range = "A2:A7")
test  = read_excel(path, range = "B2:C7")

Transformation

result = input %>%
  mutate(rn = row_number()) %>%
  separate_rows(Dictionary, sep = ", ") %>%
  separate(Dictionary, c("Key", "Value"), sep = ":|;", extra = "merge") %>%
  summarise(Key = str_c(Key, collapse = ", "),
            Value = str_c(Value, collapse = ", "), .by = rn) %>%
  select(Key, Value)

Validation

all.equal(test, result)
#> [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.
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.