PowerQuery Puzzle solved with R
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
#217–218
Puzzles
Author: ExcelBI
All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.
Puzzle #217
Turn around, transpose, pivot it all means almost the same. Like table asked us to spin itself around. Let get back to math maybe. We have table containing rate (maybe daily rate) for each person and units to be paid for. We need to reduce table but putting final values inside value cells, and then spin the table to have names from rows to columns, and months vice-versa. Final touch need to add both row and column totals. Get to the dancefloor now.
Loading libraries and data
library(tidyverse) library(readxl) library(janitor) path = "Power Query/PQ_Challenge_217.xlsx" input = read_excel(path, range = "A1:H5") test = read_excel(path, range = "J1:O8")
Trransformation
result = input %>% mutate(across(3:8, ~ . * Amt)) %>% select(-Amt) %>% t() %>% as.data.frame() %>% row_to_names(1) %>% rownames_to_column(var = "Month") %>% mutate(across(-Month, ~ as.numeric(.))) %>% adorn_totals(c("row", "col"))
Validation
all.equal(result, test, check.attributes = FALSE) # [1] TRUE
Puzzle #218
We have bunch of projects that consists mainly of similar parts so we are tracking it in one table. Someone realise that we have completion date only for some of processes, so we need to qualify them into completed and not completed in finer visual version. Hurry up, management need it.
Loading libraries and data
library(tidyverse) library(readxl) path = "Power Query/PQ_Challenge_218.xlsx" input = read_excel(path, range = "A1:C17") test = read_excel(path, range = "E1:G5") %>% replace_na(list(`Completed Tasks` = "", `Not Completed Tasks` = ""))
Transformation
result = input %>% mutate(has_date = ifelse(is.na(`Completion Date`), "Not Completed Tasks", "Completed Tasks")) %>% select(-`Completion Date`) %>% pivot_wider(names_from = has_date, values_from = Tasks, values_fn = list) %>% mutate(`Completed Tasks` = map_chr(`Completed Tasks`, ~paste(.x, collapse = ", ")), `Not Completed Tasks` = map_chr(`Not Completed Tasks`, ~paste(.x, collapse = ", ")))
Validation
identical(result, test) #> [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.
PowerQuery Puzzle solved with R 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.