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.

# 135–136


PQ_135: content file
PQ_136: content file


Lets imagine that we have binning machines in our sport centre. We can set how many balls need to be grouped in one bin/bucket/chest/whatever. We have 10 balls and machine is placing it in maximally 4 containers.
Now basing on bin size set on machine we have to find what range of balls is in each container.

Load libraries and data:


sequence = data.frame(elements = str_c("B", 1:10))

input = read_excel("PQ_Challenge_135.xlsx", range = "A1:B6")
test = read_excel("PQ_Challenge_135.xlsx", range = "L1:Q6")

Transform data and validation:

slice_df_to_range = function(df, x) {
  df_sliced = df %>%
    mutate(section = str_c("Group ",((row_number()-1) %/% x)+1)) %>%
    group_by(section) %>%
    mutate(range = str_c(first(elements),"-", last(elements))) %>%
    select(-elements) %>%

result = input %>%
  mutate(sections = map(`Bin size`, slice_df_to_range, df = sequence)) %>%
  unnest(cols = sections) %>%
  pivot_wider(names_from = section, values_from = range)

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


In this puzzle we have groups that (as I imagine take parts in different projects — IDs). And we need to find which group has common project with another groups. But we can point relationship only on first occurence. For example if we have A and B in project of group A, even if we have them in project of group B, it can not be count. Little bit tricky, but let try.

Load libraries and data:


input <- read_excel("PQ_Challenge_136.xlsx", range = "A1:B15")
test <- read_excel("PQ_Challenge_136.xlsx", range = "D1:E5")

Transform data and validation:

groups <- input %>%
  group_by(Group) %>%
  summarise(all = list(ID), .groups = 'drop')

row_indices <- seq_len(nrow(groups)) - 1

acum <- map(row_indices, ~{
  current_ids <- groups$all[[.x + 1]]
  subsequent_rows <- row_indices[.x + 2:length(row_indices)]
  map(subsequent_rows, ~{
    if (any(groups$all[[.x + 1]] %in% current_ids)) {
      groups$Group[.x + 1]
    } else {
  }) %>% unlist() %>% na.omit()
}) %>% map_chr(., ~str_c(.x, collapse = ", ")) 

result <- tibble(Group = groups$Group, Common = acum) %>%
  mutate(Common = if_else(Common == "", NA_character_, Common))

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

We’ve seen some nice puzzles considering time changes and it shows little bit out of the box thinking. Feel free to ask, like and share.
Lets be in touch for next article about the functions.

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.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)