R: aggregate every n rows with variable n depending on sum(n) of second column

user_7229574 0
Viewed 127

I'm trying to aggregate values over a flexible pooling variable, e.g. calculate the mean of my value x for every n rows when the sum of consecutive d is equal a predetermined value. I think it comes down to finding the indices of my summations and using those to create a grouping variable, but I don't know how to do this.

> head(dat)
           x        d
1 0.10000112 22.24835
2 0.11074217 22.24835
3 0.03002743 22.24835
4 0.05756194 22.24836
5 0.10906047 22.24836
6 0.05954912 25.12431

I want to calculate the mean/sum/length of x every n rows for which the sum of d e.g. is ~100.

sample data:

structure(list(x = c(0.10000112377193, 0.110742170350877, 0.0300274304561404, 
0.0575619395964912, 0.109060465438596, 0.0595491225614035, 0.0539270264912281, 
0.0812452063859649, 0.0341699389122807, 0.0391744879122807, 0.0411787485614035, 
0.0996091644385965, 0.0970479474912281, 0.0595715843684211, 0.0483489989122807, 
0.0549631194561404, 0.0705080555964912, 0.080437472631579, 0.105883664631579, 
0.0872411613684211, 0.103236660631579, 0.0381296894912281, 0.0465064491578947, 
0.0936565184561403, 0.0410095752631579, 0.0311180032105263, 0.0257758157894737, 
0.0354721928947368, 0.0584999394736842, 0.0241286060175439, 0.112053376666667, 
0.0769823868596491, 0.0558137530526316, 0.0374491000701754, 0.0419279142631579, 
0.0260257506842105, 0.0544360374561404, 0.107411071842105, 0.103873468, 
0.0419322114035088, 0.0483912961052632, 0.0328373653157895, 0.0866868717719298, 
0.063990467245614, 0.0799280314035088, 0.123490407070175, 0.145676836280702, 
0.0292878782807018, 0.0432093036666667, 0.0203547443684211), 
    d = c(22.2483512600033, 22.2483529247042, 22.2483545865809, 
    22.2483562542823, 22.24835791863, 25.1243105415557, 25.1243148759953, 
    25.1243192107884, 25.1243235416981, 25.1243278750792, 27.2240858553058, 
    27.2240943134697, 27.2241027638674, 27.224111222031, 27.2241196741942, 
    24.5623431981188, 24.5623453409221, 24.5623474809012, 24.562349626705, 
    24.5623517696847, 28.1458125837154, 28.1458157376341, 28.1458188889053, 
    28.1458220452951, 28.1458251983314, 27.8293318542146, 27.8293366652115, 
    27.8293414829159, 27.829346292148, 27.8293511094993, 27.5271773325046, 
    27.5271834011289, 27.5271894694002, 27.5271955369655, 27.5272016048837, 
    28.0376097925214, 28.0376146410729, 28.0376194959786, 28.0376243427651, 
    28.0376291969647, 26.8766095768196, 26.8766122563318, 26.8766149309023, 
    26.8766176123562, 26.8766202925746, 27.8736950101666, 27.8736960528853, 
    27.8736971017815, 27.8736981446767, 27.8736991932199)), row.names = c(NA, 
50L), class = "data.frame")
aggregate r
1 Answers

Maybe this helps

library(dplyr)
dat %>% 
    mutate(rn = row_number()) %>%
    group_by(grp = (cumsum(d)-1)%/% 100 + 1) %>%
    summarise(x = mean(x, na.rm = TRUE), start = first(rn), end = last(rn))
user_37322710
Related