How to sum values by groups in sequence

user_8039978 0
Viewed 189

I have a dataframe with duration values in column duration and group values in column gaze_focus.

df1
   duration gaze_focus
29    1.011  periphery
31    1.590     center
33    1.582     center
35    0.571  periphery
37    0.561     center
39    2.136     center
41    0.181  periphery
43    1.475     center
45    0.177  periphery
47    0.940  periphery
49    2.102     center

I'd like to compute the sums of immediately adjacent identical group values to obtain this result:

df2
  duration gaze_focus
1    1.011  periphery
2    3.172     center
3    0.571  periphery
4    2.697     center
5    0.181  periphery
6    1.475     center
7    1.117  periphery
8    2.102     center

I know that mathematical operations such as summing by group can be done using e.g. aggregateor tapply but I don't know how to sum values by groups in little chunks. Help is appreciated!

Reproducible data:

df1 <- structure(list(duration = c(1.011, 1.59, 1.582, 0.571, 0.561, 
2.136, 0.181, 1.475, 0.177, 0.94, 2.102), gaze_focus = c("periphery", 
"center", "center", "periphery", "center", "center", "periphery", 
"center", "periphery", "periphery", "center")), row.names = c(29L, 
31L, 33L, 35L, 37L, 39L, 41L, 43L, 45L, 47L, 49L), class = "data.frame")
grouping r sum
4 Answers

One dplyr option could be:

df1 %>%
 group_by(gaze_focus, rleid = with(rle(gaze_focus), rep(seq_along(lengths), lengths))) %>%
 summarise_all(sum) %>%
 arrange(rleid)

  gaze_focus rleid duration
  <chr>      <int>    <dbl>
1 periphery      1    1.01 
2 center         2    3.17 
3 periphery      3    0.571
4 center         4    2.70 
5 periphery      5    0.181
6 center         6    1.48 
7 periphery      7    1.12 
8 center         8    2.10
user_59645570

Does this work:

> for(i in 1:(nrow(df1)-1)){
+           if(df1$gaze_focus[i] == df1$gaze_focus[i+1]) { 
+             df1$duration[i+1] = df1$duration[i] + df1$duration[i+1] 
+             df1[i,] = NA
+             }
+   else{ df1$duration[i] = df1$duration[i] }
+ }
> df1
   duration gaze_focus
29    1.011  periphery
31       NA       <NA>
33    3.172     center
35    0.571  periphery
37       NA       <NA>
39    2.697     center
41    0.181  periphery
43    1.475     center
45       NA       <NA>
47    1.117  periphery
49    2.102     center
> df2 <- df1 %>% na.omit()
> df2
   duration gaze_focus
29    1.011  periphery
33    3.172     center
35    0.571  periphery
39    2.697     center
41    0.181  periphery
43    1.475     center
47    1.117  periphery
49    2.102     center
> rownames(df2) <- NULL
user_107227520

An option with dplyr and rleid

library(dplyr)
library(data.table)
df1 %>% 
   group_by(grp = rleid(gaze_focus), gaze_focus) %>% 
  summarise(duration = sum(duration, na.rm = TRUE),
         .groups = 'drop') %>% 
  select(-grp)

-output

# A tibble: 8 x 2
#  gaze_focus duration
#  <chr>         <dbl>
#1 periphery     1.01 
#2 center        3.17 
#3 periphery     0.571
#4 center        2.70 
#5 periphery     0.181
#6 center        1.48 
#7 periphery     1.12 
#8 center        2.10
user_37322710

We can use rleid from data.table to create every consecutive values as separate group.

library(data.table)

setDT(df1)[, .(gaze_focus = first(gaze_focus), 
               duration = sum(duration)), rleid(gaze_focus)]

#   rleid gaze_focus duration
#1:     1  periphery    1.011
#2:     2     center    3.172
#3:     3  periphery    0.571
#4:     4     center    2.697
#5:     5  periphery    0.181
#6:     6     center    1.475
#7:     7  periphery    1.117
#8:     8     center    2.102
user_39629140
Related