Puzzles no. 569–573

Puzzles

Author: ExcelBI

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

Puzzle #569

Today we have some random strings thate need to process in certain way. We have to count every letter that don’t have pair. That is my shortway to say this. Officially first we need to count all non-common letters, then from common we need to calculate absolute difference between strings. Then sort alphabetically and give in order letter-count-letter-count and so on. Check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/569 Diff of Common Counts.xlsx"
input = read_excel(path, range = "A1:B10") %>% replace_na(list(String1 = "", String2 = ""))
test  = read_excel(path, range = "C1:C10")

Transformation

process_strings = function(str1, str2) {
  count_letters = function(str) {
    lets = str_split(str_to_lower(str), "")[[1]]
    df = letters %>%
      tibble(letter = .) %>%
      mutate(count = map_int(letter, ~ sum(.x == lets)))
  }
  s1 = count_letters(str1)
  s2 = count_letters(str2)
  
  s = s1 %>%
    left_join(s2, by = "letter") %>%
    mutate(diff = abs(count.x - count.y)) %>%
    filter(diff != 0) %>%
    select(letter, diff) %>%
    unite("letter_diff", letter, diff, sep = "") %>%
    pull() %>%
    paste0(collapse = "")
  return(s)
}

result = input %>%
  mutate(`Answer Expected` = map2_chr(String1, String2, process_strings))

Validation

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

Puzzle #570

Today again we are playing with words. Imagine it like group dance with changing partners or maybe soccer match when referee is announcing players change. But this time they are not going on the bench, but only changing positions. And second column of our problem table is exactly list of those changes. Each consecutive pair says “Player X is replacing Y and viceversa”. So find out what string would we have after all instructions.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/570 Position Swapping.xlsx"
input = read_excel(path, range = "A1:B10")
test  = read_excel(path, range = "C1:C10")

Transformation

swap_string = function(string, numbers) {
  numbers = as.numeric(str_split(numbers, ",\\s*")[[1]])
  str_vec = str_split(string, "", simplify = TRUE)
  for (i in seq(1, length(numbers), by = 2)) {
    str_vec[c(numbers[i], numbers[i + 1])] = str_vec[c(numbers[i + 1], numbers[i])]
  }
  str_c(str_vec, collapse = "")
}

result = input %>%
  mutate(Swapped = map2_chr(String, Numbers, swap_string))

Validation

all.equal(result$Swapped, test$`Answer Expected`)
# [1] TRUE

Puzzle #571

This time we are playing with numbers. We have to find first 500 numbers that when multiplied by its own reverse, still have only those digits like in original. It is pretty slow algorithm, but not extremal as well. We need to check each number and find list of unique digits in multiplied and original numbers and find out if there are any difference between them.

Loading libraries and data

library(tidyverse)
library(readxl)
path = "Excel/571 Product of Number and its revese.xlsx"
test = read_excel(path) %>% unlist() %>% as.integer()

Transformation

check_number = function(num) {
  get_digits = function(n) {
    digits = integer()
    while (n > 0) {
      digits = c(digits, n %% 10)
      n = n %/% 10
    }
    return(sort(unique(digits)))
  }
  dig_num = get_digits(num)
  rev_num = as.numeric(paste(rev(as.integer(strsplit(as.character(num), "")[[1]])), collapse = ""))
  prod_num = num * rev_num
  dig_prod = get_digits(prod_num)
  return(identical(dig_num, dig_prod))
}

find_numbers = function(limit) {
  results = integer(limit)
  count = 0
  num = 10
  while (count < limit) {
    if (check_number(num)) {
      count = count + 1
      results[count] = num
    }
    num = num + 1
  }
  return(results)
}

result = find_numbers(500)

Validation

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

Puzzle #572

I like pivoting and I am sure that you like it to. So what answer we need today? We need to check how many times certain values were achieved per week. So let say: In first week there was one day where there was 4 units achieved. Maybe it is little bit not real, but we need to transform it, not to think about it. At least if we are only solving challenges. 😀

Loading libraries and data

library(tidyverse)
library(readxl)
library(lubridate)

path = "Excel/572 Pivot Problem.xlsx"
input = read_excel(path, range = "A1:B14")
test  = read_excel(path, range = "D2:I7")

Transformation

result = input %>%
  mutate(Date = as.Date(Date),
         Week = ceiling((day(Date) - 1) %/% 7 + 1)) %>%
  pivot_wider(names_from = Value, values_from = Date, values_fn = length, values_fill = 0) %>%
  select(Week, `1`,`2`,`3`,`4`,`5`) 

Validation

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

Puzzle #573

Usually those kind of puzzles we are having on weekend in PQ Challenges, but why not do it today. We need to check how long every person was on their shift. Unfortunatelly table is not really “tidy”, which we like most, but we can make them tidy really fast. Then we need to merge times and dates and process calculations on each person. Let’s find out how we can do it.

Loading libraries and data

library(tidyverse)
library(readxl)
library(hms)
library(janitor)
library(lubridate)

path = "Excel/573 Durations.xlsx"
input = read_excel(path, range = "A2:B14")
test  = read_excel(path, range = "D2:E6")

Transformation

result = input %>%
  mutate(name = ifelse(str_detect(`Name & Date`, "[a-zA-Z]"), `Name & Date`, NA)) %>%
  fill(name) %>%
  filter(!is.na(Time)) %>% 
  mutate(date =  excel_numeric_to_date(as.numeric(`Name & Date`)) %>% as.character(),
         time = as_hms(Time) %>% as.character()) %>%
  unite("datetime", c("date", "time"), sep = " ") %>%
  mutate(datetime = ymd_hms(datetime)) %>%
  select(name, datetime) %>%
  mutate(rn = row_number(), .by = name) %>%
  pivot_wider(names_from = rn, values_from = datetime) %>%
  mutate(duration = as.numeric(`2` - `1`)) %>%
  select(Name = name, Duration = duration)

Validation

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