[This article was first published on Numbers around us - Medium, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.#191–192
PuzzlesAuthor: ExcelBI
All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.
Puzzle #191Usually on Power Query Challenges we are getting some table transformations, but not this time. We have some pretty hardcore text manipulation here. What we have to do? Get from text only “words” that consists of:
In both cases special characters are optional. But can be mixed as densely as author would only imagine, even after every letter or digit.
But it is not the end, after finding proper strings we need to clear all special characters, and concatenate in order letters > digits, separated with underscore.
I can confess that hardest thing here was to compose regular expression to find those “words”. It took me about 90% of all time consumed for this task. Check this out.
Loading libraries and data
library(tidyverse)library(readxl)library(rebus)path = "Power Query/PQ\_Challenge\_191.xlsx"input = read\_excel(path, range = "A1:A11")test = read\_excel(path, range = "A1:B11")
Transformation
pattern1 = "\\b[A-Z]+(?:[!@#$%^&*\_+=]*[A-Z]*)*[!@#$%^&*\_+=]*[0-9]+(?:[!@#$%^&*\_+=]*[0-9]*)*\\b"pattern2 = "\\b[0-9]+(?:[!@#$%^&*\_+=]*[0-9]*)*[!@#$%^&*\_+=]*[A-Z]+(?:[!@#$%^&*\_+=]*[A-Z]*)*\\b"order\_chars = function(text) { text = str\_replace\_all(text, "[^[:alnum:]]", "") letters = str\_extract\_all(text, "[A-Z]")[[1]] %>% paste0(collapse = "") numbers = str\_extract\_all(text, "[0-9]")[[1]] %>% paste0(collapse = "") result = paste0(letters, "\_", numbers) return(result)}result = input %>% mutate(pat1 = str\_extract\_all(Text, pattern1), pat2 = str\_extract\_all(Text, pattern2)) %>% mutate(ext = map2(pat1, pat2, ~c(.x, .y))) %>% select(-c(pat1, pat2)) %>% unnest(ext, keep\_empty = T) %>% mutate(result = map\_chr(ext, order\_chars)) %>% group\_by(Text) %>% summarise(`Answer Expected` = paste0(result, collapse = ", ")) %>% mutate(`Answer Expected` = if\_else(`Answer Expected` == "NA\_NA", NA\_character\_, `Answer Expected`))
Validation — “by eye”
res = left\_join(test, result, by = c("Text" = "Text"))# A tibble: 10 × 3 Text `Answer Expected.x` `Answer Expected.y` <chr> <chr> <chr> 1 Life is beautiful LA$340 LA\_340 LA\_340 2 Q#AR\_8 Dream big, work hard QAR\_8 QAR\_8 3 Actions 55 speak 83\_LDR louder than words LDR\_83 LDR\_83 4 Every Q9#02 MOMENT 89abc matters Q\_902 Q\_902 5 Kindness costs 45A6Q nothing 83 ABC NA NA 6 Believe88 45 you 2\_3*ABC can, and you're halfway there ABC\_23 ABC\_23 7 Happiness Q@56, TY#787 is a choice Q\_56, TY\_787 Q\_56, TY\_787 8 Time 99+RT heals all wounds GHOPQ*45 RT\_99, GHOPQ\_45 GHOPQ\_45, RT\_99 9 Knowledge is ABc\_5726 power 23#PQR PQR\_23 PQR\_23 10 EMBRACE THE A$B$C$2$3$8$8$0 JOURNEY ABC\_23880 ABC\_23880
ExtraI need to explain Regex for those who are not really familiar with it.
\\b[A-Z]+(?:[!@#$%^&*\_+=]*[A-Z]*)*[!@#$%^&*\_+=]*[0-9]+(?:[!@#$%^&*\_+=]*[0-9]*)*\\b- \\b: at the beginning and end are responsible for applying code to "word" which means fragment of string separated by whitespaces.- [A-Z]+: fragment consisting of one or more capital letters- (?:....): is called non-capturing group, I am using it to made group for purpose of checking its multi-occurence and that is why there is * after, which means zero or more occurences.- [!@#$%^&*\_+=]*[A-Z]*: inside non-capturing group means that we are looking for group in which there can be special sign or capital letter. I use it to check if after first segment of letters there are more mixed with special characters.-[!@#$%^&*\_+=]*: we have optional special characters in middle of expression as well- [0-9]+: fragment consisting of one or more digit- (?:[!@#$%^&*\_+=]*[0-9]*)*: and again we have non-capturing group for mixing digits and special characters
I hope I don’t need to explain second one, because it is only reversed: digits first than capital letters.
Puzzle #192And we have some table manipulation as well. Today we have project management issue to solve. We have pretty nice data about planned and actual performance of some projects. We have dates for each scenario, and we need to calculate if projects are late, on time or are done faster than planned, and of course if they consume planned time, or maybe more or less. It is not very hard task, but needs a lot of transformations and conditional expressions. Find out yourself.
Loading libraries and data
library(tidyverse)library(readxl)path = 'Power Query/PQ\_Challenge\_192.xlsx'input = read\_excel(path, range = "A1:E14")test = read\_excel(path, range = "G1:J6")
Transformation
count\_workdays <- function(from, to) { map2(from, to, seq, by = "days") %>% map(~ tibble(timeperiod = .x)) %>% map(~ mutate(.x, weekday = wday(timeperiod, week\_start = 1))) %>% map(~ filter(.x, weekday %in% 1:5)) %>% map\_int(~ nrow(.x))}result = input %>% filter\_all(any\_vars(!is.na(.))) %>% fill(everything(), .direction = "down") %>% rename("scenario" = 3) %>% pivot\_wider(names\_from = scenario, values\_from = c(4, 5)) %>% mutate(`Schedule Performance` = case\_when( `To Date\_Actual` > `To Date\_Plan` ~ "Overrun", `To Date\_Actual` < `To Date\_Plan` ~ "Underrun", TRUE ~ "On Time" ), `Actual Dates` = map2\_int(`From Date\_Actual`, `To Date\_Actual`, count\_workdays) , `Plan Dates` = map2\_int(`From Date\_Plan`, `To Date\_Plan`, count\_workdays), `Cost Performance` = case\_when( `Actual Dates` > `Plan Dates` ~ "Overrun", `Actual Dates` < `Plan Dates` ~ "Underrun", TRUE ~ "At Cost" )) %>% mutate(nr = row\_number(), .by = Project) %>% select(Project, Phase, nr, `Schedule Performance`, `Cost Performance`) %>% mutate(Project = if\_else(nr == 1, Project, NA\_character\_)) %>% select(-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.
To leave a comment for the author, please follow the link and comment on their blog: Numbers around us - Medium.
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.Continue reading: PowerQuery Puzzle solved with R