R Solution for Excel Puzzles

Puzzles no. 354–358

Puzzles

Author: ExcelBI

From this week all files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Puzzle #354

This puzzle comes up on Christmas (Dec, 25th), so it was not really suprising that topic to solve will be somekind linked to Christmas. This time we do not have to count, calculate or process anything… Our goal was to make Christmas Tree Generator. After changing input variable value, we were generating Christmas tree with different width.

Load libraries

library(tidyverse)
library(crayon)

Function

print_christmas_tree <- function(n) {
  for(i in 1:n) {
    spaces <- strrep(" ", n - i)
    asterisks <- strrep("*", 2 * i - 1)
    cat(spaces, green(asterisks), spaces, "\n")
  }
  
  trunk_spaces <- strrep(" ", n - 1)
  for(i in 1:2) {
    cat(trunk_spaces, red("*"), trunk_spaces, "\n")
  }
  cat(strrep(" ", n - 2), red("***"), strrep(" ", n - 2), "\n")
}

Christmas trees 🙂

Puzzle #355

From cosy atmosphere of Xmas we are going into vast spaces of the Internet. What we have exactly to do is to check if given IP address (from IPv6 range) is correct. We are given some clues how to recognize proper IP and what can an excluding signal.

1. IPv6 address is represented as x:x:x:x:x:x:x:x (total 8 x) where x consists of 1 to 4 Hexadecimal digits.
2. Leading 0s can be omitted. Hence, 00A6 can be written as A6
3. Double colons (::) can be used in place of a series of zeros. For example, IPv6 address CD34:0:0:0:0:0:0:A4 can be written as CD34::A4.
4. Double colons can be used only once in an IP address.

So lets check those addresses.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/355 Valid IPv6 Addresses.xlsx", range = "A1:B10") %>%
  janitor::clean_names()
test  = read_excel("Excel/355 Valid IPv6 Addresses.xlsx", range = "C1:C5") %>%
  janitor::clean_names()

Transformation

is_hexadecimal <- function(x) {
  str_detect(x, "^[0-9a-fA-F]+$")
}

is_ipv6 <- function(ip) {
  if (str_detect(ip, ":::") || str_detect(ip, ":") > 7) {
    return(FALSE)
  }
  
  parts = str_split(ip, ":", simplify = TRUE)
  
  if (str_detect(ip, "::")) {
    missing_parts = 8 - length(parts[parts != ""])
    parts = parts[parts != ""]
    parts = c(parts, rep("0", missing_parts))
  }
  
  length(parts) == 8 
  && all(map_lgl(parts, is_hexadecimal)) 
  && all(map_lgl(parts, ~ nchar(.) <= 4))
}

result = input %>%
  mutate(check = map_lgl(i_pv6_address, is_ipv6)) %>%
  filter(check == TRUE) %>%
  select(i_pv6_address)

Validation

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

Puzzle #356

Today we are counting, sorting and comparing. But case is not really hard. We have to check big numbers for following condition:

• check what is frequency of biggest digit
• list all digits that are more frequent in this number than the biggest one.

So lets go.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/356 Count Digit Frequencies.xlsx", range = "A1:A10")
test  = read_excel("Excel/356 Count Digit Frequencies.xlsx", range = "B1:B10")

Transformation

evaluate = function(number) {
  result = str_split(number, "")[[1]] %>%
    table() %>%
    as.data.frame() %>%
    select(digit = 1, freq = 2) %>%
    mutate(digit = as.numeric(as.character(digit)),
           freq_of_max = freq[which.max(digit)]) %>%
    filter(freq > freq_of_max) %>%
    pull(digit) %>%
    paste0(collapse = ", ") %>%
    ifelse(nchar(.) == 0, NA, .)
  return(result)  
}

result = input %>%
  mutate(Digits = map_chr(Numbers, evaluate))

Validation

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

Puzzle #357

This time we have problem linked with dictionary (which is not very popular in R). Object of this kind are very popular and commonly used in Python. And what we exactly have to do is to construct dictionary (pair or pairs of Keys and Values) from two separate lists (Keys in one and Values in second). In Python there is function called zip which similarly like real life zip get alternally one value from Keys, one from Values, bind them and then go to next. But we can do it differently as well.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/357 Prepare Dictionary.xlsx", range = "A1:B7")
test  = read_excel("Excel/357 Prepare Dictionary.xlsx", range = "C1:C7")

Transformation

process = function(key, value) {
  keys = strsplit(key, ", ")[[1]]
  values = strsplit(value, ", ")[[1]]
  tib = tibble(key = keys, value = values) %>%
    filter(!key %in% c("a","e","i","o","u")) %>%
    unite("dict", key, value, sep = ":") %>% 
    pull(dict) %>%
    paste0(., collapse = ", ") %>%
    ifelse(. == "", NA, .)
  return(tib)
}

result = input %>%
  mutate(dict = map2_chr(Key, Value, process))

Validation

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

Puzzle #358

Not really long ago we had similar task, but then we were checking what the sum of digits in diagonals is. Today we “only” have to get both diagonals and “straight” it up to be columns. Lets do it.

Load libraries and data

library(tidyverse)
library(readxl)

input_1 = read_excel("Excel/358 Stack Diagonal Values.xlsx", 
                     range = "A2:C4", col_names = F) %>%  as.matrix()
input_2 = read_excel("Excel/358 Stack Diagonal Values.xlsx", 
                     range = "A6:D9",  col_names = F) %>% as.matrix()
input_3 = read_excel("Excel/358 Stack Diagonal Values.xlsx", 
                     range = "A11:E15", col_names = F) %>% as.matrix()

test_1 = read_excel("Excel/358 Stack Diagonal Values.xlsx", 
                    range = "G2:H4", col_names = c("A", "B"))
test_2 = read_excel("Excel/358 Stack Diagonal Values.xlsx", 
                    range = "G6:H9", col_names = c("A", "B"))
test_3 = read_excel("Excel/358 Stack Diagonal Values.xlsx", 
                    range = "G11:H15", col_names = c("A", "B"))

Transformation and validation

get_diagonals = function(M) {
  result = tibble(
    A = diag(M),
    B = diag(M[, ncol(M):1])
  )
  return(result)
}

identical(test_1, get_diagonals(input_1))
#> [1] TRUE
identical(test_2, get_diagonals(input_2))
#> [1] TRUE
identical(test_3, get_diagonals(input_3))
#> [1] TRUE

Feel free to comment, share and contact me with advices, questions and your ideas how to improve anything.

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.