Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
#229–230
Puzzles
Author: ExcelBI
All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.
Puzzle #229
When somebody is working on a project for more than a month it doesn’t really matter for him individually. But accountants wouldn’t be happy if we throw cost into interval which is not month. So we need to asssign amount to each month of its duration respectively and proportionally. Find out how to do it.
Loading libraries and data
library(tidyverse) library(readxl) path = "Power Query/PQ_Challenge_229.xlsx" input = read_excel(path, range = "A1:D6") test = read_excel(path, range = "F1:H16")
Transformation
result = input %>% mutate(days = as.numeric(as.Date(`To Date`) - as.Date(`From Date`)) + 1, daily = Amount / days) %>% rowwise() %>% mutate(date = list(seq(`From Date`, `To Date`, by = "day"))) %>% unnest(date) %>% mutate(`Month - Year` = paste0(str_pad(month(date), width = 2, "0", side = "left"), "-", str_sub(year(date), 3, 4))) %>% summarise(Amount = round(sum(daily),0), .by = c(Transaction, `Month - Year`))
Validation
Ssall.equal(result, test, check.attributes = FALSE) # [1] TRUE
Puzzle #230
We have summary of sales amounts from whole world, but it looks like calendar chart with square for each month. We need to summarise it “math way”. So first tidy up this table, then stack months into one two-column structure, again tidy and finally summarise them all together into months. Little mess, I know, but it is very fast job.
Loading libraries and data
library(tidylibrary(tidyverse) library(readxl) path = "Power Query/PQ_Challenge_229.xlsx" input = read_excel(path, range = "A1:D6") test = read_excel(path, range = "F1:H16")
Transformation
result = input %>% mutate(days = as.numeric(as.Date(`To Date`) - as.Date(`From Date`)) + 1, daily = Amount / days) %>% rowwise() %>% mutate(date = list(seq(`From Date`, `To Date`, by = "day"))) %>%cscscsssssssssssssssssssssssssssssSCsc unnest(date) %>% mutate(`Month - Year` = paste0(str_pad(month(date), width = 2, "0", side = "left"), "-", str_sub(year(date), 3, 4))) %>% summarise(Amount = round(sum(daily),0), .by = c(Transaction, `Month - Year`))
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.
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.