how to group by nearby values using data.tables

Question

My data looks like this:

    ID1     ID2     Time            diff
1:  1958616 P209576 4/15/2016 7:46  NA mins
2:  1958493 P209580 3/23/2016 9:41  -33005.16793 mins
3:  1958493 P209580 3/25/2016 15:41 3240.09742 mins
4:  1958493 P209580 3/30/2016 10:22 6880.65360 mins
5:  1958492 P209580 3/30/2016 11:31 69.00078 mins
6:  1958493 P209580 4/11/2016 10:07 17196.62313 mins

What I'd like to do is group all IDs that occur within an 8 hour time window of each other and also return the number of distinct ID1s after grouping. In the above example, rows 4 & 5 would be grouped since abs(diff) < 60*8.

I used data[, diff := TIME - shift(TIME)] to generate the diff column.

My ideal output would look something like this

    num_of_unique_id1   ID2     Initial_time
1:  1                   P209576 4/15/2016 7:46
2:  1                   P209580 3/23/2016 9:41
3:  1                   P209580 3/25/2016 15:41
4:  2                   P209580 3/30/2016 10:22
5:  1                   P209580 4/11/2016 10:07

I'm thinking that num_of_unique_id1 could be created using .SD and length(unique(ID1)) but not sure how to create column for by = parameter.

I know theres also going to be boundary issues that arise (A is within 8 hours of B and B is within 8 hours of C but A and C are more than 8 hours apart) and in these cases I think I would like to group it all into one row.

output of dput(data)

structure(list(ID1 = c("1958616", "1958493", "1958493", "1958493",
"1958492", "1958493"), ID2 = c("P209576", "P209580", "P209580",
"P209580", "P209580", "P209580"), Time = structure(c(1460706387.438,
1458726077.362, 1458920483.207, 1459333322.423, 1459337462.47,
1460369259.858), class = c("POSIXct", "POSIXt"), tzone = "GMT"),
    diff = structure(c(NA, -33005.1679333329, 3240.09741666714,
    6880.65360000133, 69.0007833321889, 17196.6231333335), units = "mins", class = "difftime")), .Names = c("ID1",
"ID2", "Time", "diff"), class = c("data.table", "data.frame"), row.names = c(NA,
-6L), .internal.selfref = <pointer: 0x1ce9a28>)

Can you use `dput` on the `head` of your data to provide reproducible data? See [here](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — alexperrone, Jun 17 '16 at 19:14
"What I'd like to do is group all IDs that occur within an 8 hour time window of each other"... you want within 8-hour time window of the *previous* (in time) entry? Why don't you sort the data by time first? — alexperrone, Jun 17 '16 at 19:27
I sorted by ID2 first then Time. Yes group by previous time entry assuming that ID2 is the same. Data does not need to stay in the order that it comes. — yingw, Jun 17 '16 at 19:42

alexperrone · Accepted Answer · 2016-06-17T19:51:48.573

If the data are ordered in time, we can compute the diff and assign unique groups using cumsum.

data <- data[order(Time)]
data[ , diff := NULL]  # we will re-compute diff in hours
data[ , diff_hours := as.numeric(c(0, diff(Time)))]
##        ID1     ID2                Time diff_hours
## 1: 1958493 P209580 2016-03-23 09:41:17   0.000000
## 2: 1958493 P209580 2016-03-25 15:41:23  54.001624
## 3: 1958493 P209580 2016-03-30 10:22:02 114.677560
## 4: 1958492 P209580 2016-03-30 11:31:02   1.150013
## 5: 1958493 P209580 2016-04-11 10:07:39 286.610386
## 6: 1958616 P209576 2016-04-15 07:46:27  93.646550

window <- 8  # the time window in hours
data[ , group := cumsum(diff_hours > window) + 1]
data[ , num_of_unique_id1 := uniqueN(ID1), by = group]
##        ID1     ID2                Time diff_hours group num_of_unique_id1
## 1: 1958493 P209580 2016-03-23 09:41:17   0.000000     1                 1
## 2: 1958493 P209580 2016-03-25 15:41:23  54.001624     2                 1
## 3: 1958493 P209580 2016-03-30 10:22:02 114.677560     3                 2
## 4: 1958492 P209580 2016-03-30 11:31:02   1.150013     3                 2
## 5: 1958493 P209580 2016-04-11 10:07:39 286.610386     4                 1
## 6: 1958616 P209576 2016-04-15 07:46:27  93.646550     5                 1

Note that the two data points on 2016-03-30 within about an hour of each other are assigned the same group and the num_of_unique_id1 (per group) is 2, whereas all the other data points are in their own group.

how to group by nearby values using data.tables

1 Answers1