CODEWITHSUNDEEP
rdataframegroup-summaries
drastega
drastega

Reputation: 1773

Group by relative difference in a column (accounting for how data is ordered)

I have a data frame. The snippet is:

df1 <- data.frame(x = c(1, 2, 1, 3, 5, 1, 4, 1), y = c(1, 1, 2, 2, 1, 1, 1, 3))
  x y
1 1 1
2 2 1
3 1 2
4 3 2
5 5 1
6 1 1
7 4 1
8 1 3

I need to group df1 by y and sum over x but accounting for order of y. I.e I need to create new group after each new y and sum over corresponding x. The desired output is

   x y
1  3 1
2  4 2
3 10 1
4  1 3

How to do this in R?

Upvotes: 1

Views: 80

Answers (2)

M--
M--

Reputation: 29202

Using data.table:

library(data.table)

setDT(df1)[, .(x=sum(x), y=y[1]), .(rleid(y))][,rleid:=NULL][]

#>     x y
#> 1:  3 1
#> 2:  4 2
#> 3: 10 1
#> 4:  1 3

Or another dplyr solution using base::rle:

library(dplyr)

df1 %>% 
  group_by(y_grp = with(rle(y), rep(seq_along(lengths), lengths))) %>% 
  summarise(x = sum(x), y = y[1]) %>% 
  ungroup %>% select(-y_grp)

#> # A tibble: 4 x 2
#>       x     y
#>   <dbl> <dbl>
#> 1     3     1
#> 2     4     2
#> 3    10     1
#> 4     1     3

Upvotes: 1

akrun
akrun

Reputation: 887851

We can use rleid (from data.table) to get the run-length-id for grouping adjacent similar elements and get the sum of 'x'

library(dplyr)
library(data.table)
df1 %>%
   group_by(grp= rleid(y), y) %>%
   summarise(x = sum(x)) %>%
   ungroup %>%
   select(names(df1))
# A tibble: 4 x 2
#      x     y
#  <dbl> <dbl>
#1     3     1
#2     4     2
#3    10     1
#4     1     3

Or with only dplyr, create a logical expression with the lag values of 'y', convert to numeric index with cumsum in group_by and get the sum of 'x'

df1 %>%
   group_by(grp = cumsum(y != lag(y, default = first(y)))) %>% 
   summarise(x = sum(x), y = first(y)) %>%
   select(-grp)

Upvotes: 2

Related Questions