PowerQuery Puzzle solved with R

#149–150

Puzzles

Author: ExcelBI

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

Puzzle #149

This weekend riddles are little bit easier than last week, but it doesn’t mean that they are not challenging. In input data we have table including data about how long somebody works in company and what is her/his daily pay amount. But accountants cannot get it as it is because employees were working in two or even three consecutive months and we have only first and last day of their contracts. So what we need to do? Slice and cut those periods into months and calculate how much should company pay for each of months.
What is my Chain of Thoughts? Make a sequence of dates, extract month from them, count days and multiply by cash amount. Lets check how to code it.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_149.xlsx", range = "A1:D6") %>%
  janitor::clean_names()
test  = read_excel("Power Query/PQ_Challenge_149.xlsx", range = "F1:I12") %>%
  janitor::clean_names() %>% 
  arrange(employee, start_date)

Transformation

result = input %>%
  mutate(days = map2(start_date, end_date, ~ seq(.x, .y, by = "day"))) %>%
  unnest(days) %>%
  mutate(month = floor_date(days, "month")) %>%
  select(-start_date, -end_date) %>%
  group_by(employee, per_diem, month) %>%
  summarise(n_days = n(),
            start_date = min(days),
            end_date = max(days)) %>%
  ungroup() %>%
  mutate(total = n_days * per_diem) %>%
  select(employee, start_date, end_date, per_diem = total) %>%
  arrange(employee, start_date)

Validation

identical(result, test)
#> [1] TRUE

Puzzle #150

This time we have something which looks like it is queue service time at hospital or in any kind of office. Lets think about it as a queue for some document, and column Item would mean customer in this metaphor. We have log of Item, Time In, Time Out and Duration of this appointment. But sometimes there was a chaos while customers where changing and we only have info about duration, and we are told that they get into when previous person get out. But there is one twist. First person with blanks are after first non-blank, second after second and so on. It doesnt mean that table is perfectly prepared for calculations.
Lets try to make it.
I used hms package, because in some cases (like this one) we need only time parts, not datetime, and hms is really good for this purpose.

Load libraries and data

library(tidyverse)
library(readxl)
library(lubridate)
library(hms)

input = read_excel("Power Query/PQ_Challenge_150.xlsx", range = "A1:D11") %>%
  janitor::clean_names() 
test  = read_excel("Power Query/PQ_Challenge_150.xlsx", range = "F1:I11") %>%
  janitor::clean_names() %>% 
  mutate(across(c(time_in, time_out), ~as_hms(.x)))

Transformation

result = input %>%
  mutate(across(c(time_in, time_out), ~as_hms(.x))) %>%
  group_by(empty = is.na(time_in)) %>%
  mutate(nr = row_number()) %>%
  ungroup() %>%
  group_by(nr) %>%
  mutate(time_in = if_else(empty, first(time_out), time_in),
         time_out = if_else(empty, time_in + dminutes(round(duration * 60,0)), time_out)) %>%
  ungroup() %>%
  select(-c(empty, nr))

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.