Using dplyr for dynamic group_by

Question

Trying to get my head around this dplyr thingy. I have a sorted data frame that I want to group based on a variable. However, the groups need to be constructed so that each of them have a minimum sum of 30 on the grouping variable.

Consider this small example data frame:

df1 <- matrix(data = c(05,0.9,95,12,0.8,31,
    16,0.8,28,17,0.7,10,
        23,0.8,11,55,0.6,9,
    56,0.5,12,57,0.2,1,
    59,0.4,1),
  ncol = 3,
  byrow = TRUE,
  dimnames = list(c(1:9), 
    c('freq', 'mean', 'count')
  )
)

Now, I want to group so that count have a sum of at least 30. freq and mean should then be collapsed into a weighted.mean where the weights is the count values. Note that the last "bin" reaches a sum of 32 by row 7, but since row 8:9 only sums to 2, I add them to the last "bin".

Like so:

freq   mean   count
 5.00  0.90   95
12.00  0.80   31
16.26  0.77   38
45.18  0.61   34

The simple summarizing with dplyr is not a problem, but this I can't figure out. I do think the the solution is hidden somewhere here:

Dynamic Grouping in R | Grouping based on condition on applied function

But how to apply it to my situation escapes me.

Moody_Mudskipper Moody_Mudskipper · Accepted Answer · 2017-11-16T11:02:44

I wish I had a shorter solution but here is what I came up with.

First we define a custom cumsum function :

cumsum2 <- function(x){
  Reduce(function(.x,.y){
    if(tail(.x,1)>30) x1 <- 0 else x1 <- tail(.x,1) ;c(.x,x1+.y)},x,0)[-1]
}
# cumsum2(1:10)
# [1]  1  3  6 10 15 21 28 36  9 19

Then we can have fun with the dplyr chain :

library(dplyr)
library(tidyr)

df1 %>%
  as.data.frame %>%                        # as you started with a matrix
  mutate(id = row_number(),                # we'll need this to sort in the end
         cumcount = cumsum2(count))    %>% # adding nex cumulate count
  `[<-`(.$cumcount < 30,"cumcount",NA) %>% # setting as NA values less than 30 ...
  fill(cumcount,.direction = "up")     %>% # ... in order to fill them with cumcount
  fill(cumcount,.direction = "down")   %>% # the last NAs belong to the last group so we fill down too
  group_by(cumcount)                   %>% # these are our new groups to aggregate freq and mean
  summarize(id = min(id),
            freq = sum(freq*count)/sum(count),
            mean = sum(mean*count)/sum(count)) %>%
  arrange(id)                          %>% # sort
  select(freq,mean,count=cumcount)         # and lay out as expected output

# # A tibble: 4 x 3
#       freq      mean count
#      <dbl>     <dbl> <dbl>
# 1  5.00000 0.9000000    95
# 2 12.00000 0.8000000    31
# 3 16.26316 0.7736842    38
# 4 45.17647 0.6117647    32

Using dplyr for dynamic group_by

1 Answers