Puzzle #183
This Saturday we had quite interesting case to solve. We had table with rental agreements, which need to be transformed to kind of payment schedule. We have lenght of contract, interest rate increase after first year, and so on. It is one of cases where we can use formula for compound percent. Check it out.
Loading libraries and data
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_183.xlsx", range = "A1:F5")
test = read_excel("Power Query/PQ_Challenge_183.xlsx", range = "H1:K24") %>%
mutate(Rental = as.integer(Rental))
Transformation
result = input %>%
unite("OYQ", Year, Quarter, sep = " ") %>%
mutate(OYQ = yq(OYQ)) %>%
rowwise() %>%
mutate(quarters = list(seq.Date(from = as.Date(OYQ), by = "quarter", length.out = `Total Periods`))) %>%
ungroup() %>%
unnest(quarters) %>%
mutate(Year = year(quarters),
Quarter = paste0("Q",quarter(quarters)),
rn = row_number(),
roll_year = (rn - 1) %/% 4 ,
.by = Vendor) %>%
mutate(Rental = round(Rental * (1 + `% Hike Yearly`/100)^roll_year) %>% as.integer()) %>%
select(Vendor, Year, Quarter, Rental)
Validation
identical(result, test)
# [1] TRUE
Puzzle #184
Sunday with Regex… good mind workout. Today we have some strings. And inside them suppose to be sequence as follow: Letters followed by digits. Sometimes there are more then one of such sequences, sometimes there are not even one. So we have to take last possible sequence from given string and concatenate them together inside the group. Conditional structures need to be used as well. Lets do it.
Loading libraries and data
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_184.xlsx", range = "A1:B10")
test = read_excel("Power Query/PQ_Challenge_184.xlsx", range = "D1:G4")
Transformation
result = input %>%
mutate(group = str_extract_all(Text,"[A-Za-z]+\\d+")) %>%
mutate(group = map_chr(group, ~if(length(.x) > 1) tail(.x, 1) else if(length(.x) == 0) NA_character_ else .x)) %>%
summarise(
Text = paste(group[!is.na(group)], collapse = "-"),
`Original Count` = n() %>% as.numeric(),
`New Count` = sum(!is.na(group)) %>% as.numeric(),
.by = Set
)
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.