Menu
I am struggling with a problem related to merging two data.tables (although it could be data.frames as well) based on a timestamp (POSIXct) that is unequal.
Based on a certain timestamp in table A I'd like R to return me the entry in table B that occurs prior to the time in A.
An example:
I have table A that contains data about activities at a certain point in time.
EDIT: This is different data to the original post that better reflects the problem: I need to 'lookup' based on timestamp and a grouping variable which I call Station ID. Apologies for not being clear in the first place..
Start.Time Start.Station.ID
1: 2014-04-06 18:24:32 238
2: 2014-04-06 18:20:30 238
3: 2014-04-06 01:04:13 373
4: 2014-04-06 01:03:36 373
5: 2014-04-06 01:03:37 373
6: 2014-04-06 01:03:01 373
7: 2014-04-06 01:02:42 373
8: 2014-04-06 01:02:31 373
I want to add a column to that table A that indicates what the status for that station was at a certain point in time in terms of 'availability'. These status can be found in table B.
status_dt station_id availability
1: 2014-04-06 00:29:02 238 0.9354839
2: 2014-04-06 00:29:02 373 1.0000000
3: 2014-04-06 01:29:03 238 1.0000000
4: 2014-04-06 01:29:03 373 0.6111111
5: 2014-04-06 02:59:03 238 0.9354839
6: 2014-04-06 02:59:03 373 0.6666667
...
41: 2014-04-06 17:59:03 238 0.8387097
42: 2014-04-06 17:59:03 373 0.4444444
43: 2014-04-06 18:59:03 238 0.9032258
44: 2014-04-06 18:59:03 373 0.5000000
45: 2014-04-06 20:29:03 238 0.7741935
status_dt station_id availability
The timestamps do not match, therefore I'd like to add to table A the status from table B at the observation prior to timestamp in table A.
The expected result would be for example column 'availability':
status_dt station_id availability
1: 2014-04-06 18:24:32 238 0.8387097
2: 2014-04-06 18:20:30 238 0.8387097
3: 2014-04-06 01:04:13 373 1.0000000
4: 2014-04-06 01:03:36 373 1.0000000
5: 2014-04-06 01:03:37 373 1.0000000
6: 2014-04-06 01:03:01 373 1.0000000
7: 2014-04-06 01:02:42 373 1.0000000
8: 2014-04-06 01:02:31 373 1.0000000
BodieG's proposal works if the entries in Start.Station.ID/station_id are unique, but applying his suggestion to this data gives
status_dt station_id availability Start.Station.ID
1: 2014-04-06 18:24:32 373 0.4444444 238
2: 2014-04-06 18:20:30 373 0.4444444 238
3: 2014-04-06 01:04:13 373 1.0000000 373
4: 2014-04-06 01:03:36 373 1.0000000 373
5: 2014-04-06 01:03:37 373 1.0000000 373
6: 2014-04-06 01:03:01 373 1.0000000 373
7: 2014-04-06 01:02:42 373 1.0000000 373
8: 2014-04-06 01:02:31 373 1.0000000 373
Where the entries in the first two rows are not what I would have expected (or rather hoped for): they refer to the 'availability' in station 373 instead of 238.
I guess the code just has to be adapted to reflect the timestamp AND the station ID, but I'm banging my head against the wall here.... Also I could not figure out whether using the suggested xts-package would help, because clearly I have duplicated timesteps here...
Again, any hint is very appreciated. Thanks in advance!
For reproducibility:
Table A:
structure(list(Start.Time = structure(c(1396808672, 1396808430,
1396746253, 1396746216, 1396746217, 1396746181, 1396746162, 1396746151
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), Start.Station.ID = c(238,
238, 373, 373, 373, 373, 373, 373)), .Names = c("Start.Time",
"Start.Station.ID"), class = c("data.table", "data.frame"), row.names = c(NA,
-8L))
Table B:
structure(list(status_dt = structure(c(1396744142, 1396744142,
1396747743, 1396747743, 1396753143, 1396753143, 1396754942, 1396754942,
1396756743, 1396756743, 1396758542, 1396758542, 1396760343, 1396760343,
1396765743, 1396765743, 1396767542, 1396767542, 1396772943, 1396772943,
1396778402, 1396778402, 1396781943, 1396781943, 1396785542, 1396785542,
1396787342, 1396787342, 1396790942, 1396790942, 1396794543, 1396794543,
1396798143, 1396798143, 1396799943, 1396799943, 1396801743, 1396801743,
1396805343, 1396805343, 1396807143, 1396807143, 1396810743, 1396810743,
1396816143, 1396816143, 1396817942, 1396817942, 1396821542, 1396821542,
1396826942, 1396826942), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
station_id = c(238, 373, 238, 373, 238, 373, 238, 373, 238,
373, 238, 373, 238, 373, 238, 373, 238, 373, 238, 373, 238,
373, 238, 373, 238, 373, 238, 373, 238, 373, 238, 373, 238,
373, 238, 373, 238, 373, 238, 373, 238, 373, 238, 373, 238,
373, 238, 373, 238, 373, 238, 373), availability = c(0.935483870967742,
1, 1, 0.611111111111111, 0.935483870967742, 0.666666666666667,
0.967741935483871, 0.666666666666667, 0.967741935483871,
0.666666666666667, 0.935483870967742, 0.666666666666667,
0.967741935483871, 0.666666666666667, 0.967741935483871,
0.611111111111111, 0.967741935483871, 0.611111111111111,
1, 0.444444444444444, 0.870967741935484, 0.5, 0.806451612903226,
0.5, 0.774193548387097, 0.388888888888889, 0.709677419354839,
0.388888888888889, 0.67741935483871, 0.333333333333333, 1,
0.5, 0.903225806451613, 0.444444444444444, 0.935483870967742,
0.444444444444444, 0.903225806451613, 0.444444444444444,
0.870967741935484, 0.444444444444444, 0.838709677419355,
0.444444444444444, 0.903225806451613, 0.5, 0.774193548387097,
0.611111111111111, 0.766666666666667, 0.611111111111111,
0.774193548387097, 0.555555555555556, 0.870967741935484,
0.666666666666667)), .Names = c("status_dt", "station_id",
"availability"), class = c("data.table", "data.frame"), row.names = c(NA,
-52L), sorted = "status_dt")
228
You can use the roll parameter:
setkey(B, status_dt)
B[A, roll=TRUE]
Produces:
status_dt station_id availability Start.Station.ID
1: 2014-04-06 21:07:42 225 0.4864865 225
2: 2014-04-06 21:06:50 225 0.4864865 225
3: 2014-04-06 21:06:49 225 0.4864865 225
4: 2014-04-06 21:06:15 225 0.4864865 225
5: 2014-04-06 21:04:35 225 0.4864865 225
6: 2014-04-06 21:05:33 225 0.4864865 225
7: 2014-04-06 21:04:45 225 0.4864865 225
8: 2014-04-06 21:04:37 225 0.4864865 225
9: 2014-04-06 21:04:35 225 0.4864865 225
10: 2014-04-06 21:01:45 225 0.4864865 225
11: 2014-04-06 21:00:57 225 0.4864865 225
12: 2014-04-06 20:59:04 225 0.4864865 225
13: 2014-04-06 20:58:04 225 0.8648649 225
14: 2014-04-06 20:57:22 225 0.8648649 225
15: 2014-04-06 20:57:24 225 0.8648649 225
16: 2014-04-06 20:56:40 225 0.8648649 225
17: 2014-04-06 20:55:52 225 0.8648649 225
18: 2014-04-06 20:55:25 225 0.8648649 225
19: 2014-04-06 20:55:24 225 0.8648649 225
20: 2014-04-06 20:55:00 225 0.8648649 225
21: 2014-04-06 18:25:30 225 0.9729730 225
22: 2014-04-06 18:25:28 225 0.9729730 225
status_dt station_id availability Start.Station.ID
This matches closely to your expected output, except it has some extra rows that as far as I can tell are legitimate per your description of the problem.
84
I mostly use the zoo or xts packages which were essentially written for this.
R> dfA <- as.data.frame(A)
R> a <- xts(dfA[,2], order.by=dfA[,1])
R> dfB <- as.data.frame(B)
R> b <- xts(dfB[,-1], order.by=dfB[,1])
Now that we have two xts object, we can just merge() and run na.locf() over the result to fill NA with prior values:
R> na.locf(merge(a, b))
a station_id availability
2014-04-06 17:59:03 NA 225 0.972973
2014-04-06 18:25:28 225 225 0.972973
2014-04-06 18:25:30 225 225 0.972973
2014-04-06 18:59:03 225 225 0.621622
2014-04-06 20:29:03 225 225 0.864865
2014-04-06 20:55:00 225 225 0.864865
2014-04-06 20:55:24 225 225 0.864865
2014-04-06 20:55:25 225 225 0.864865
2014-04-06 20:55:52 225 225 0.864865
2014-04-06 20:56:40 225 225 0.864865
2014-04-06 20:57:22 225 225 0.864865
2014-04-06 20:57:24 225 225 0.864865
2014-04-06 20:58:04 225 225 0.864865
2014-04-06 20:59:02 225 225 0.486486
2014-04-06 20:59:04 225 225 0.486486
2014-04-06 21:00:57 225 225 0.486486
2014-04-06 21:01:45 225 225 0.486486
2014-04-06 21:04:35 225 225 0.486486
2014-04-06 21:04:35 225 225 0.486486
2014-04-06 21:04:37 225 225 0.486486
2014-04-06 21:04:45 225 225 0.486486
2014-04-06 21:05:33 225 225 0.486486
2014-04-06 21:06:15 225 225 0.486486
2014-04-06 21:06:49 225 225 0.486486
2014-04-06 21:06:50 225 225 0.486486
2014-04-06 21:07:42 225 225 0.486486
2014-04-06 21:59:02 225 225 0.162162
2014-04-06 23:29:02 225 225 0.162162
R>
But there ought to be a data.table answer in this too...
Edit: Per the comment, here is merge with just a timestamps:
R> na.locf(merge(a, b))[index(a), -1]
station_id availability
2014-04-06 18:25:28 225 0.972973
2014-04-06 18:25:30 225 0.972973
2014-04-06 20:55:00 225 0.864865
2014-04-06 20:55:24 225 0.864865
2014-04-06 20:55:25 225 0.864865
2014-04-06 20:55:52 225 0.864865
2014-04-06 20:56:40 225 0.864865
2014-04-06 20:57:22 225 0.864865
2014-04-06 20:57:24 225 0.864865
2014-04-06 20:58:04 225 0.864865
2014-04-06 20:59:04 225 0.486486
2014-04-06 21:00:57 225 0.486486
2014-04-06 21:01:45 225 0.486486
2014-04-06 21:04:35 225 0.486486
2014-04-06 21:04:35 225 0.486486
2014-04-06 21:04:37 225 0.486486
2014-04-06 21:04:45 225 0.486486
2014-04-06 21:05:33 225 0.486486
2014-04-06 21:06:15 225 0.486486
2014-04-06 21:06:49 225 0.486486
2014-04-06 21:06:50 225 0.486486
2014-04-06 21:07:42 225 0.486486
R>
In this particular case I also removed the redundant station id column.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
