CODEWITHSUNDEEP
rstatisticsusage-statistics
user1653150
user1653150

Reputation: 353

combine similar consecutive observations into one observation in R

I have a data set like this

date ID key value
05   1   3   2
05   1   3   5
05   1   3   1
05   1   5   2
05   1   7   3
05   1   7   3
05   1   3   4
05   2   9   8

I need the output to look like this

date ID key value
05   1   3   8
05   1   5   2
05   1   7   6
05   1   3   4
05   2   9   8

so as you see if consecutive date, ID, and key are the same , I want to know how to combine these observation and add their value. I need this to happen only if the events where consecutive. is it possible to do it r? if yes, can anyone please tell me how to do it? thanks

Upvotes: 1

Views: 252

Answers (1)

user20650
user20650

Reputation: 25854

Use rle to look for consecutive sequences

# your data
df <- read.table(text="date ID key value
 05   1   3   2
 05   1   3   5
 05   1   3   1
 05   1   5   2
 05   1   7   3
 05   1   7   3
 05   1   3   4
 05   2   9   8", header=T)

# get consecutive values - add a grouping variables
r <- with(df, rle(paste(date, ID, key)))
df$grps <- rep(seq(r$lengths), r$lengths)

# aggregate values
a <- aggregate(value ~ date + ID + key + grps, data = df , sum)

# remove the grouping variable
a$grps <- NULL

Upvotes: 1

Related Questions