CODEWITHSUNDEEP
rdplyr
chrisjacques
chrisjacques

Reputation: 646

From sessions to minute by minute summary

I have a dataset which has viewing sessions with start and end timestamps, and i would like to create a minute-by-minute summary showing the number of people in each minute. A simplified version of my data looks like this: 

db<-data.frame(id=1:4, start=c(1,1,2,4), end=c(3,4,5,6))

And i would like something like this:

min1to2 = 2,
min2to3 = 3,
min3to4 = 2,
min4to5 = 2,
min5to6 = 1

In reality my dataset is very large (about 6Gb) and the level of granularity is the second, so a timestamp would look like this: "2017-04-01 09:01:26". Ideally I'd like to use dplyr.

Any ideas?

Upvotes: 2

Views: 110

Answers (1)

Jaap
Jaap

Reputation: 83215

A possible solution with the -package based on my comment:

# create the data as in the question
library(data.table)
db <- data.table(id=1:4, start=c(1,1,2,4), end=c(3,4,5,6))

# convert the 'start' & 'end' columns into real minute data
set.seed(1)
db[, (2:3) := lapply(.SD, function(x) as.POSIXct(paste0('2017-05-31 15:',x,':',sample(15:45,4)), format = '%Y-%m-%d %H:%M:%S') ), .SDcols = 2:3]

This results in the following data.table:

   id               start                 end
1:  1 2017-05-31 15:01:23 2017-05-31 15:03:21
2:  2 2017-05-31 15:01:26 2017-05-31 15:04:41
3:  3 2017-05-31 15:02:31 2017-05-31 15:05:42
4:  4 2017-05-31 15:04:40 2017-05-31 15:06:33

Now, the following code:

db[, .(times = seq(as.POSIXct(floor(as.numeric(start)/60)*60, origin = '1970-01-01'),
                   as.POSIXct(floor(as.numeric(end)/60)*60, origin = '1970-01-01'), 'min')), id
   ][, .N, times][]

gives:

                 times N
1: 2017-05-31 15:01:00 2
2: 2017-05-31 15:02:00 3
3: 2017-05-31 15:03:00 3
4: 2017-05-31 15:04:00 3
5: 2017-05-31 15:05:00 2
6: 2017-05-31 15:06:00 1

Upvotes: 2

Related Questions