PowerQuery Puzzle solved with R

# 135–136

Puzzles:

PQ_135: content file
PQ_136: content file

PQ_135

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:

library(tidyverse)
library(readxl)

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) %>%
    distinct()
  
  return(df_sliced)
}

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

PQ_136

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:

library(tidyverse)
library(readxl)

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 {
      NA_character_
    }
  }) %>% 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.