Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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

```library(tidyverse)

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.

```library(tidyverse)

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.

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.