I'm trying to figure out the best way to achieve my goal. I have one large master data.table (>110,000 observations) that contains data from photos taken by several camera stations. I have a separate data.table that holds information about these cameras, like when photos were uploaded from them (photos were uploaded from each camera multiple times). For each camera, I need to subset the photos taken by it into "bins" that are defined by when the photos were uploaded. I assume that I will need to use a for loop to run through each camera but from there I get stuck. I feel like I am looking for a version of the cut function that can return what "bin" the photo belongs in as a separate column in the data.table.
Example data.table of the photos from one camera:
> station_photos
year_unit_station Photo_Number Creation_Datetime bin_name
1: 2016_275_02 275_02_0017.JPG 2016-09-23 11:51:03
2: 2016_275_02 275_02_0035.JPG 2016-09-27 15:58:21
3: 2016_275_02 275_02_0036.JPG 2016-09-27 15:58:49
4: 2016_275_02 275_02_0037.JPG 2016-09-27 16:00:04
5: 2016_275_02 275_02_0038.JPG 2016-09-27 16:00:59
6: 2016_275_02 275_02_0039.JPG 2016-09-27 16:01:27
7: 2016_275_02 275_02_0062.JPG 2016-10-02 12:22:35
>
Example of the table that shows when the photos were uploaded:
> station_bins
year_unit_station service_end_dttm bin_name
1: 2016_275_02 2016-09-23 11:21:00 2016_275_02_1
2: 2016_275_02 2016-09-30 10:45:00 2016_275_02_2
3: 2016_275_02 2016-10-07 08:31:00 2016_275_02_3
End goal table for each camera that I am hoping to get from my code:
> station_photos
year_unit_station Photo_Number Creation_Datetime bin_name
1: 2016_275_02 275_02_0017.JPG 2016-09-23 11:51:03 2016_275_02_1
2: 2016_275_02 275_02_0035.JPG 2016-09-27 15:58:21 2016_275_02_1
3: 2016_275_02 275_02_0036.JPG 2016-09-27 15:58:49 2016_275_02_1
4: 2016_275_02 275_02_0037.JPG 2016-09-27 16:00:04 2016_275_02_1
5: 2016_275_02 275_02_0038.JPG 2016-09-27 16:00:59 2016_275_02_1
6: 2016_275_02 275_02_0039.JPG 2016-09-27 16:01:27 2016_275_02_1
7: 2016_275_02 275_02_0062.JPG 2016-10-02 12:22:35 2016_275_02_2
8: 2016_275_02 275_02_0075.JPG 2016-10-31 03:09:43 2016_275_02_3
>
I've considered using cut() or subset but I am not sure how to get either to fill in that last variable of the "bin_name" for me rather than just returning a list or data.frame. My other concern is that not every camera will have 3 bins, some will have 2 some will have 4. And to add one more twist to this how could I use a similar or the same method to create bins that are a set length rather than from a date range. The end goal is to count how many photos were taken by the camera between uploads as well as to count the number of photos taken by each camera in 10 minute intervals. It would be very helpful to still have that bin_name column for future analysis.
I'm not really sure if my explanation makes sense and it is quite possible that I am making the solution way more complicated than I need to. Thank you in advance for any help or insight you can give!
