Excel Puzzle Solutions: 559-563

Puzzles no. 559–563

Puzzles

Author: ExcelBI

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

Puzzle #559

Sometimes we need to make work on columns. It technically just like working on lists, and look what we need to do with the lists today. It is called running maximum. We usually have some contact with running sum or average, but running maximum is little bit less popular. It is not returning single digit, but similarly like in cumsum, all sequence of numbers in which for certain element we need to aggregate in a way all preceeding elements (in sum — we need to sum them up, in average — mean, and in maximum always choose max from all preceeding). It is pretty easy in R so check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/559 Max of first N elements.xlsx"
input = read_excel(path, range = "A2:D13")
test  = read_excel(path, range = "F2:I13")

Transformation

output = input %>%
  mutate(across(everything(), ~cummax(.))) 

Validation

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

Puzzle #560

Lets think about words as sandwiches… today we need to find all sandwiches on our table. Oh, no, get back to Excel… do not think about pastrami sandwiches. We need to find every combination of one or more vowels in between of consonants. I found one obstacle in this task, but also the solution how to extract also those combinations that are overlapping with another valid combinations like rot and tor in rotor.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/560 Vowels between Consonants.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:B10") %>% replace_na(list(`Answer Expected` = ""))

Transformation

extract_cvc_overlap <- function(input_string) {
  pattern <- "(?=([^aeiou][aeiou]+[^aeiou]))"
  str_match_all(input_string, pattern) %>%
    map_chr(~ paste(.[, 2], collapse = ", ")) %>%
    str_trim()
}

result = input %>%
  mutate(result = map_chr(Words, extract_cvc_overlap))

Validation

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

Puzzle #561

Today we are going to play stock trading in small scale. We have 9 assets and prices from 10 days, and all we need to do is to find most profitable scenario when to buy and sell. Of course we would do it probably faster manually then writing code, but we are here to write code. And if it is hard to solve it is even more satisfying. Check it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/561 Maximum Profit.xlsx"

input = read_excel(path, range = "A2:J11")
test  = read_excel(path, range = "K2:M11") %>%
  mutate(across(everything(), ~if_else(.x == "NP", NA_real_, as.numeric(.x))))

Transformation

process_row <- function(...){
  row <- c_across(everything())
  cell_list <- map(1:length(row), ~row[.x:length(row)])
  df_pairs <- map_dfr(1:length(cell_list), function(i) {
    tibble(
      from = rep(row[i], length(cell_list[[i]]) - 1),
      to = cell_list[[i]][-1]
    )
  })
  df_pairs <- df_pairs %>%
    mutate(diff = to - from)
  max_pair <- df_pairs %>%
    slice_max(diff, with_ties = FALSE)
  return(list(
    max_diff = max_pair$diff,
    from_value = max_pair$from,
    to_value = max_pair$to
  ))
}

result <- input %>%
  rowwise() %>%
  mutate(result = list(process_row(across(everything())))) %>%
  mutate(
    Buy = result$from_value,
    Sell = result$to_value,
    Profit = result$max_diff
  ) %>%
  ungroup() %>%
  select(Buy, Sell, Profit) %>%
  mutate(across(everything(), ~if_else(Profit <= 0, NA_real_, .x)))

Validation

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

Puzzle #562

Palindromes, palindromes… I think Vijay A. Verma wouldn’t find the end of realm of palindromes anytime soon. Today our topic is One child palindrome. What does it mean that number has child? It means that substring of number is divisible by length of such original number. So if we want number with one child we need to find one that among all substrings has only one that is divisible by length of original number. But we have to mix it also with properties of palindrome and find 1000 first numbers with all those properties together.

It is pretty slow solution, but working fine.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/562 One Child Palindromes.xlsx"
test = read_excel(path, range = "A1:A1001")

Transformation

has_one_child <- function(n) {
  nchar = nchar(n)
  if (nchar == 1) {
    return(FALSE)
  }
  grid_coord = expand.grid(1:nchar, 1:nchar)
  substrings = apply(grid_coord, 1, function(x) {
    substr(n, x[1], x[2])
  }) %>%
  as.numeric() %>%
  .[!is.na(.) & . != 0] %>%
  unique()

  substrings = substrings[substrings %% nchar == 0]
  return(length(substrings) == 1)
}

generate_all_palindromes <- function(num_digits) {
  if (num_digits < 1) {
    stop("Number of digits must be at least 1")
  }
  if (num_digits == 1) {
    return(0:9)
  }
  half_digits <- ceiling(num_digits / 2)
  start_num <- 10^(half_digits - 1)
  end_num <- 10^half_digits - 1
  palindromes <- vector("integer", length = 0)

  for (i in start_num:end_num) {
    num_str <- as.character(i)
    rev_str <- paste0(rev(strsplit(num_str, "")[[1]]), collapse = "")
    if (num_digits %% 2 == 0) {
      palindrome_str <- paste0(num_str, rev_str)
    } else {
      palindrome_str <- paste0(num_str, substring(rev_str, 2))
    }
    palindromes <- c(palindromes, as.integer(palindrome_str))
  }
  return(palindromes)
}


palindrome_df <- tibble(num_digits = 1:9) %>%
  mutate(palindromes = map(num_digits, generate_all_palindromes))
res = palindrome_df %>%
  unnest(cols = c(palindromes)) %>%
  mutate(palindromes = as.integer(palindromes),
         has_one_child = map_lgl(palindromes, has_one_child))
result = res %>%
  filter(has_one_child == TRUE, palindromes > 10) %>%
  head(1000) %>%
  select(palindromes)

Validation

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

Puzzle #563

Some time ago we did similar thing but other way. When we have some range notations, we needed to split it to numbers within range, but this time we need to do another way. If we find consecutive numbers in groups, we just collapse them into range. Find out how I did it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/563 Bands of Numbers.xlsx"
input = read_excel(path, range = "A2:B17")
test  = read_excel(path, range = "D2:F6")

Transformation

result = input %>%
  mutate(Group = cumsum(c(1, diff(Numbers)) != 1), .by = Product) %>%
  mutate(Band = ifelse(n() == 1, paste0(Numbers), paste0(Numbers[1], "-", Numbers[n()])),
         .by = c(Product, Group)) %>%
  summarise(Bands = paste0(unique(Band), collapse = ", "),
            Count = n_distinct(Band),
            .by = Product)

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.

Continue reading: R Solution for Excel Puzzles

Understanding and Solving Excel Puzzles Using R Coding

The text discusses a series of problem-solving exercises (referred to as puzzles) that deal with various tasks using the R programming language. The tasks, which involve operations on Excel datasets, include running maximum, finding vowel combinations between consonants, stock trading simulation, palindrome generation, and finding consecutive numbers in groups.

Key Takeaways and Future Direction

The Power of R Language for Problem Solving

The variety and complexity of the puzzles presented in this article underscore R’s versatility and power as a tool for data handling and problem-solving. The intricate problems mentioned serve as proof of how this programming language can address a variety of real-world scenarios. From string manipulation to statistical calculations to pattern recognition, R’s capabilities prove to be a handy solution for diverse problem sets.

Endless Possibilities for Future Applications

Given the demonstrated ability to handle unique problem sets, there is a strong potential for the future expansion of similar coding exercises. It is evident that such precision and capabilities of R can be fine-tuned and scaled to address related real-world problems in various industries like finance (as shown in the stock trading exercise), natural language processing, and even game development (palindrome creation exercise).

Actionable Points for Beginners and Experienced R Users

• Expand learning through practice: As demonstrated in the puzzles, consistent practice with R’s functionalities can improve the ability to navigate different problem sets. Beginners should consistently engage in hands-on coding exercises.
• Diversify problem sets: Experts can enhance their skills by delving into unique problems like the ones offered in these puzzles. By diversifying the challenges addressed, coding and problem-solving skills can be expanded.
• Collaborate and learn: Engage with other R users through various forums, engaging in discussions, sharing ideas, and solving puzzles together. Platforms like Github and R-blogger can foster exchanges that enhance learning and skill development.

Long-term Implications

The widespread use of R in various industries and its proven problem-solving capacity make it immensely valuable. In the long term, the ability to solve complex puzzles with this language can lead to advancements in data handling, analytics, automation, and augment problem-solving in various sectors from healthcare to finance to academia.

Read the original article