Aggregate rows with specific shared value

Question

I want to aggregate my data as follows:

Aggregate only for successive rows where status = 0
Keep age and sum up points

Example data:

da <- data.frame(userid = c(1,1,1,1,2,2,2,2), status = c(0,0,0,1,1,1,0,0), age = c(10,10,10,11,15,16,16,16), points = c(2,2,2,6,3,5,5,5))

da
  userid status age points
1      1      0  10      2
2      1      0  10      2
3      1      0  10      2
4      1      1  11      6
5      2      1  15      3
6      2      1  16      5
7      2      0  16      5
8      2      0  16      5

I would like to have:


da2
  userid status age points
1      1      0  10      6
2      1      1  11      6
3      2      1  15      3
4      2      1  16      5
5      2      0  16     10

d.b · Accepted Answer · 2019-09-12T15:59:56.080

da %>%
    mutate(grp = with(rle(status),
                      rep(seq_along(values), lengths)) + cumsum(status != 0)) %>%
    group_by_at(vars(-points)) %>%
    summarise(points = sum(points)) %>%
    ungroup() %>%
    select(-grp)
## A tibble: 5 x 4
#  userid status   age points
#   <dbl>  <dbl> <dbl>  <dbl>
#1      1      0    10      6
#2      1      1    11      6
#3      2      0    16     10
#4      2      1    15      3
#5      2      1    16      5

score 1 · Answer 2 · answered Sep 12 '19 at 15:06

You can use group_by from dplyr:

da %>% group_by(da$userid, cumsum(da$status), da$status) 
   %>% summarise(age=max(age), points=sum(points))

Output:

  `da$userid` `cumsum(da$status)` `da$status`   age points
        <dbl>               <dbl>       <dbl> <dbl>  <dbl>
1           1                   0           0    10      6
2           1                   1           1    11      6
3           2                   2           1    15      3
4           2                   3           0    16     10
5           2                   3           1    16      5

Rémi Coulaud · Answer 3 · 2019-09-12T15:30:08.117

Exactly the same idea as above :

library(dplyr)

data1 <- data %>% group_by(userid, age, status) %>%
  filter(status == 0) %>%
  summarise(points = sum(points))

data2 <- data %>%
  group_by(userid, age, status) %>%
  filter(status != 0) %>%
  summarise(points = sum(points))

data <- rbind(data1,
              data2)

We need to be more carreful with your specification of status equal to 0. I think the code of Quang Hoang works only for your specific example.

I hope it will help.

Aggregate rows with specific shared value

3 Answers3