CODEWITHSUNDEEP
r
CARTman
CARTman

Reputation: 747

Count number of rows between two timestamps

I have data in a dataframe where rows are organized by time-stamp. I need to take each row and look back by 1 hour from that row's time-stamp and calculate number of rows are there within that 1 hour. I tried zoo package with window but looks like it works with Date type not time-stamp

My data looks like:

    Timestamp   X
1/9/2015 0:00   1
1/9/2015 0:30   2
1/9/2015 1:10   3
1/9/2015 1:30   4
1/9/2015 2:20   5
1/9/2015 2:30   6
1/9/2015 3:15   7
1/9/2015 3:30   8
1/9/2015 4:00   9
1/9/2015 4:30   10
1/9/2015 5:01   11

The result should look like:

    Timestamp   X   row_count
1/9/2015 0:00   1   0
1/9/2015 0:30   2   1
1/9/2015 1:10   3   1
1/9/2015 1:30   4   2
1/9/2015 2:20   5   1
1/9/2015 2:30   6   2
1/9/2015 3:15   7   2
1/9/2015 3:30   8   2
1/9/2015 4:00   9   2
1/9/2015 4:30   10  2
1/9/2015 5:01   11  2

Thank you in advance for any suggestion.

EDIT: I've asked a follow-up question here.

Upvotes: 2

Views: 244

Answers (1)

eddi
eddi

Reputation: 49448

Assuming you have a data.frame:

library(data.table)
setDT(dt)

dt[.(Timestamp = Timestamp - 1*60*60, X = X), on = 'Timestamp', roll = -Inf,
   i.X - X]
# [1] 0 1 1 2 1 2 2 2 2 2 1

Upvotes: 4

Related Questions