PowerQuery Puzzle solved with R

#163–164

Puzzles

Author: ExcelBI

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

Puzzle #163

Some puzzle we receive are based on recognizing and checking certain patterns. I remember checking IPv6 addresses and validity of South African IDs, and today we have to check if license plates from India that are placed in longer string are valid. It has certain pattern: code of province (two letters), two digits except “00”, two letters and four digits except “0000”. Let’s find them.

Load libraries and data

library(tidyverse)
library(readxl)

input1 = read_excel("Power Query/PQ_Challenge_163.xlsx", range = "A1:B29")
input2 = read_excel("Power Query/PQ_Challenge_163.xlsx", range = "D1:D10")
test   = read_excel("Power Query/PQ_Challenge_163.xlsx", range = "F1:G10")

Transformation

pattern = "([A-Z]{2})(\\d{2})([A-Z]{2})(\\d{4})"

res = input2 %>%
  mutate(Data = str_remove_all(Data, " ")) %>%
  mutate(a = str_match_all(Data, pattern), nr = row_number()) %>%
  unnest_longer(a, keep_empty = TRUE) %>%
  mutate(p1_valid = a[,2] %in% input1$`Vehicle code`,
         p2_valid = a[,3] != "00",
         p4_valid = a[,5] != "0000",
         `Vehicle Numbers` = ifelse(p1_valid & p2_valid & p4_valid, a[,1], NA_character_)) %>%
  select(Data, `Vehicle Numbers`, nr) %>%
  group_by(nr) %>%
  mutate(r = row_number()) %>%
  pivot_wider(names_from = r, values_from = `Vehicle Numbers`) %>%
  ungroup() %>%
  unite("Vehicle Numbers", `1`, `2`, na.rm = TRUE, sep = ", ") %>%
  mutate(`Vehicle Numbers` = ifelse(`Vehicle Numbers` == "", NA, `Vehicle Numbers`))

Validation

identical(res$`Vehicle Numbers`, test$`Vehicle Numbers`)
# [1] TRUE

Puzzle #164

Sometimes we have to transform one type of table to another (and sometimes back too…). And this time we had strange looking wide country, but want to have it long. For example we have results of match, and it is reasonable to have it as a pair of data, but sometimes we only need to count all goals separately. These are moments where tidyr comes for help.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_164.xlsx", range = "A1:E7")
test  = read_excel("Power Query/PQ_Challenge_164.xlsx", range = "G1:J13")

Transformation

input = read_excel("Power Query/PQ_Challenge_164.xlsx", range = "A1:E7")
test  = read_excel("Power Query/PQ_Challenge_164.xlsx", range = "G1:J13")

result = input %>%
  pivot_longer(cols = -c(1), 
               names_to = c(".value", "suffix"), 
               names_pattern = "(\\D+)(\\d+)") %>%
  mutate(Type = str_extract_all(Number, "[A-Z]+") %>% map_chr(~paste(., collapse = "")),
         Code = str_extract_all(Number, "\\d+") %>% map_chr(~paste(., collapse = ""))) %>%
  select(Group, Type, Code, Value) 

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.