I want to aggregate group2 based on NAs in group1:
Datetime group1 group2
2011-08-08 21:00:00 1 1
2011-08-08 21:10:00 NA 2
2011-08-08 21:20:00 NA 3
2011-08-08 21:30:00 2 4
2011-08-08 21:40:00 NA 5
2011-08-08 21:50:00 NA 6
2011-08-08 22:00:00 3 7
This is my desired output:
Datetime group1 group2
2011-08-08 21:00:00 1 1
2011-08-08 21:30:00 2 9
2011-08-08 22:00:00 3 18
Edit: 9=2+3+4 and 18=5+6+7.
aggregate(group2~group1, data=Data, subset(Data,group1==NA),sum)
Any suggestion is appreciated. Can I do it with aggregate? or should I use different package?
It looks like na.locf from package zoo would be quite useful here.
Assuming dat is your original data, we can take the dates for the non-NA group1 levels and use cbind to bring them together with the aggregated group2 data.
> library(zoo)
> Datetime <- dat$Datetime[!is.na(dat$group1)]
> cbind(Datetime, aggregate(group2~group1, na.locf(dat, fromLast = TRUE), sum))
# Datetime group1 group2
# 1 2011-08-08 21:00:00 1 1
# 2 2011-08-08 21:30:00 2 9
# 3 2011-08-08 22:00:00 3 18
PS: Thanks for updating/editing your question (+1).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments