R Solution for Excel Puzzles

Puzzles no. 574–578

Puzzles

Author: ExcelBI

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

Puzzle #574

We have some weird military drill on numbers today. Like some seargent shouting “Every first out, step forward” and then we need to sort them in order of height. But we are doing it on strings made of digits. Digits from odd position have to be sorted separately from even ones. Is it possible? Of course.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/574 Sort Numbers in Odd Positions Only.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:B10")

Transformation

process_numbers = function(number) {
  number = strsplit(as.character(number), "")[[1]]
  odd = seq(1, length(number), by = 2)
  number[odd] = sort(as.numeric(number[odd]))
  paste(number, collapse = "")
}

result = input %>%
  mutate(`Answer Expected` = map_chr(Numbers, process_numbers))

Validation

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

Puzzle #575

Today we have sorting out who earn below or above department average. Unfortunatelly department managers give us data on some scrap of paper, as one long string of names and amounts. So we need to separate it to granular data before we can do any calculations.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/575 List Above and Below Average Salary.xlsx"
input = read_excel(path, range = "A2:B9")
test  = read_excel(path, range = "D2:E13")

Transformation

result = input %>%
  separate_rows(Names, sep = ", ") %>%
  separate(Names, into = c("Name", "Salary"), sep = "-") %>%
  mutate(AvgSalary = mean(as.numeric(Salary), na.rm = T),
         AboveAvg = ifelse(Salary >= AvgSalary, ">= Average", "< Average")) %>%
  mutate(nr = row_number(), .by = AboveAvg,
         Names  = paste0(Dept,"-",Name)) %>%
  select(Names, AboveAvg, nr) %>%
  pivot_wider(names_from = AboveAvg, values_from = Names) %>%
  select(`< Average`, `>= Average`) 

Validation

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

Puzzle #576

Now we have similar thing like in first challenge but concerning letters not digits. In following sentences we need to get only consonants and sort them alphabetically. Vowels and whitespaces shoul stay in the same places. And you can probably guess, we are gonna do it similar way as before.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/576 Sort only Consonants.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:B10")

Transformation

process_column <- function(word) {
  col <- strsplit(word, "")[[1]]
  consonant_pos <- grep("[b-df-hj-np-tv-z]", col)
  sorted_consonants <- sort(col[consonant_pos])
  col[consonant_pos] <- sorted_consonants
  paste(col, collapse = "") 
}

result = input %>%
  mutate(result = map_chr(Sentences, process_column))

Validation

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

Puzzle #577

Today is drawing day again. And we have a candle to draw. I thought that it will be good idea to illustrate it with two types of candles that are lighten those days around the world. Indian culture celebrates Divali, Festival of Light, and in Western countries there is a time of All Saints Day, when people are leaving candles on graves to commemorate their descendants. Lets do our candle.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/577 Make ASCII Lamp.xlsx"
test  = read_excel(path, range = "B2:X11", col_names = F)  %>% as.matrix()
test[is.na(test)] <- ""

Transformation

centered = function(matrix, row, how_many) {
  pad <- (ncol(matrix) - how_many) %/% 2
  matrix[row, ] <- c(rep("", pad), rep("x", how_many), rep("", ncol(matrix) - how_many - pad))
  matrix
}

M = matrix("", nrow = 10, ncol = 23)

M <- reduce(2:3, ~centered(.x, .y, 1), .init = M)
M <- reduce(4:6, ~centered(.x, .y, 3), .init = M)
M <- reduce(7:10, ~centered(.x, .y, 21 - (.y - 7) * 2), .init = M)

Validation

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

Puzzle #578

We get 3 sets of numbers and need to make some combinatorics today. We need to find which combination (one number from each column) gives us the highest number, or rather top 3 of them as product. Using expand.grid function it is not really big deal. Check it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Excel/578 Find Maximum Product.xlsx"
input = read_excel(path, range = "A2:C11")
test  = read_excel(path, range = "E2:H5")

Transformation

result = expand.grid(Number1 = input$Number1, Number2 = input$Number2, Number3 = input$Number3) %>%
  mutate(Product = Number1 * Number2 * Number3) %>%
  arrange(desc(Product)) %>%
  slice(1:3) %>%
  select(Product, everything())

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.