Assign an identifier column in data table based on date range and serial number in another data table

Question

My first data table contains gps telemetry locations from multiple animals. Each row is a different location point collected and there columns with longitude, latitude, date, and serial number of the collar. Some collars have been worn by multiple animals over the course of my study.

> table1
                Datetime Latitude Longitude Serial
1  2018-03-07 12:0:0 EST 33.21580 -81.52677  40987
2  2018-03-07 15:0:0 EST 33.21608 -81.52699  40987
3  2018-03-07 18:0:0 EST 33.21592 -81.52706  40987
4  2018-03-09 21:0:0 EST 33.21521 -81.53458  40987
5  2018-03-10 21:0:0 EST 33.21737 -81.53036  40987
6  2018-03-11 21:0:0 EST 33.21171 -81.53041  40987
7   2018-03-12 0:0:0 EST 33.21233 -81.53210  40987
8   2018-03-12 3:0:0 EST 33.21531 -81.53459  40987
9  2018-10-27 20:0:0 EST 33.37290 -81.62092  41718
10 2018-10-27 22:0:0 EST 33.37161 -81.62070  41718
11 2018-10-27 22:0:0 EST 33.37161 -81.62070  41718
12  2018-10-28 4:0:0 EST 33.37291 -81.62025  41718
13  2018-10-28 4:0:0 EST 33.37291 -81.62024  41718
14 2018-10-28 20:0:0 EST 33.37317 -81.62013  41718
15 2018-10-28 20:0:0 EST 33.37317 -81.62013  41718
16  2018-05-18 3:0:0 EST 33.35660 -81.48555  40989
17  2018-05-18 6:0:0 EST 33.35467 -81.48659  40989
18  2018-05-18 9:0:0 EST 33.35462 -81.48659  40989
19 2018-05-18 15:0:0 EST 33.35478 -81.48666  40989
20 2018-05-18 18:0:0 EST 33.35448 -81.48651  40989
21 2018-05-18 21:0:0 EST 33.35487 -81.48395  40989
22  2018-05-19 0:0:0 EST 33.35470 -81.48040  40989
23  2018-05-19 3:0:0 EST 33.35466 -81.48641  40989
24  2018-05-19 6:0:0 EST 33.35450 -81.48651  40989
25  2018-05-19 9:0:0 EST 33.35445 -81.48637  40989
26 2018-05-19 12:0:0 EST 33.35452 -81.48654  40989
27 2018-08-12 20:0:0 EST 33.33132 -81.74094  41726
28 2018-08-12 22:0:0 EST 33.33230 -81.74474  41726
29  2018-08-13 0:0:0 EST 33.33251 -81.74249  41726
30  2018-08-13 0:0:0 EST 33.33252 -81.74249  41726
31  2018-08-13 2:0:0 EST 33.32780 -81.74340  41726
32  2018-08-13 2:0:0 EST 33.32780 -81.74339  41726
33  2018-08-13 4:0:0 EST 33.32887 -81.74516  41726

My second table has an animal ID column, a serial number, a start date, and an end date.

> table2
  ID Serial     Start        End
1 41  40987  3/7/2018   8/8/2018
2 57  40985  4/3/2018  8/21/2018
3 59  40989  4/5/2018  5/22/2018
4 75  41726 7/17/2018 10/16/2018

How do I add an ID column to the first data table using all the criteria from the second data table?

Possible duplicate of [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right). Though you haven't provided the expected output, from your description, it seems like a *join* operation. — yusuzech, Oct 21 '19 at 23:19

score 0 · Accepted Answer · answered Oct 22 '19 at 00:47

As mentioned, it would help if you could provide expected output. From what you described, you want to add ID to table1 matching by the Serial number, and include only records that fall between the Start and End dates for that number. This would suggest a left (outer) join.

One approach:

table3 <- merge(table1, table2, by = "Serial")
with(table3, table3[Datetime >= Start & Datetime <= End, ])

An alternative with dplyr:

library(dplyr)

table1 %>%
  left_join(table2, by = "Serial") %>%
  filter(Datetime >= Start, Datetime <= End)

Assign an identifier column in data table based on date range and serial number in another data table

1 Answers1