Site icon R-bloggers

PowerQuery Puzzle solved with R

[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.

#143–144

Puzzles

Author: ExcelBI

From this week all files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Suprisingly this week we have no time-related puzzles, they are rather transformations of tables.

Puzzle #143

In this puzzle we have some kind of log. Employees are entering values of observation, but some observation has the same value. To check if it is right or wrong we have to find duplicated values. But only duplicated, so that means do not note first occurence but only second (first duplicated) and last occurence. I know maybe it is little bit chaotic, but lets do it…

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_143.xlsx", range = "A1:C21")
test  = read_excel("Power Query/PQ_Challenge_143.xlsx", range = "F1:H7")

Transformation

result = input %>%
  group_by(Emp, Value) %>%
  mutate(rn = row_number()) %>%
  filter(rn == 2 | (rn == max(rn) & rn > 2)) %>%
  select(-rn) %>%
  ungroup()

# Pretty easy, isn't it?

Validation

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

Puzzle #144

Again we have some employee’s log. They forget to note which of two days they log information about, so as work around manager told analyst to break log of each person in the middle (if there are odd number of logs, “first half” should be bigger). So let correct somebody’s mistakes.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_144.xlsx", range = "A1:B16")
test  = read_excel("Power Query/PQ_Challenge_144.xlsx", range = "E1:G16")

Transformation

result = input %>%
  group_by(Group) %>%
  mutate(Half = ifelse(row_number() <= ceiling(n()/2), "First", "Second")) %>%
  ungroup() %>%
  group_by(Group, Half) %>%
  mutate(`Running Total` = cumsum(Value)) %>%
  ungroup() %>%
  select(-Half)

Validation

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

Thanks for your engagement, and let me know if you have any comments.


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.
Exit mobile version