R Solution for Excel Puzzles
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
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.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.