I am trying to identify values within an interval that also match a column specifying a unique identifier.
I have two data frames one that has the values to be matched (called contacts):
File Time Behavior Status
1 MG_20220412_FE2_48 83.451 Tentacle_head POINT
2 MG_20220412_FE2_48 108.939 Tentacle_proximalOT POINT
3 MG_20220412_FE2_48 424.675 Tentacle_snut POINT
4 MG_20220412_FE2_49 90.453 Tentacle_proximalOT POINT
5 MG_20220412_FE2_50 668.519 Tentacle_proximalOT POINT
6 MG_20220412_FE2_52 105.655 Tentacle_proximalOT POINT
And the other has the intervals (called point_behaviors):
File Time Behavior Status Start_Interval
25 MG_20220412_FE2_48 324.455 Pause POINT 323.705
26 MG_20220412_FE2_48 403.236 Rhinal Bristle POINT 402.486
27 MG_20220412_FE2_48 425.168 Curl POINT 424.418
28 MG_20220412_FE2_48 465.896 Protrude START 465.146
29 MG_20220412_FE2_48 513.755 Lateral Bristle POINT 513.005
30 MG_20220412_FE2_49 12.169 Pause POINT 11.419
31 MG_20220412_FE2_49 56.183 J-Turn POINT 55.433
32 MG_20220412_FE2_49 62.009 Lateral Bristle POINT 61.259
33 MG_20220412_FE2_49 77.708 J-Turn POINT 76.958
34 MG_20220412_FE2_49 83.714 Oral Tentacle Tap POINT 82.964
35 MG_20220412_FE2_49 90.500 Protrude START 90.400
36 MG_20220412_FE2_49 90.426 Cross Oral Tentacles POINT 89.676
37 MG_20220412_FE2_49 118.759 Body Column Scrunch POINT 118.009
38 MG_20220412_FE2_49 186.009 Body Column Scrunch POINT 185.259
39 MG_20220412_FE2_49 458.317 Rhinal Bristle POINT 457.567
40 MG_20220412_FE2_50 294.004 Curl POINT 293.254
What I am trying to do is identify Behaviors in contacts that occur between the Start_Interval and Time in point_behaviors. I figured out how to use this using sapply:
contacts$response = sapply(contacts$Time, function (x){
i = which(point_behaviors$Start_Interval<=x & point_behavior$Time>x)
point_behaviors$Behavior[i] })
But the problem is that I cannot figure out how to make it so it only searches rows with a matching File identifier. For example row 1 of contacts should only be an interval containing 83.451 from point_behaviors where the File is MG_20220412_FE2_48. I was starting to try a for loop that would subset the point_behaviors to be only the correct file name and then use the function I wrote, but felt like there would be an easier way.
Here is the for loop I started, which is not functional yet:
for (i in 1:length(contacts_test)){
temp <- point_behaviors[point_behaviors$File == contacts_test$File[i],]
contacts_test$responsetest[i] = function (x){
i = which(temp$Start_Interval<=x & temp$Time>x)
temp$Behavior[i]
}
}
Edit: I edited the example so that some were actually in the time interval. In the dataset, many may not have a match and some will have multiple matches. The output should look like:
File Time Behavior Status Response
1 MG_20220412_FE2_48 83.451 Tentacle_head POINT NA
2 MG_20220412_FE2_48 108.939 Tentacle_proximalOT POINT NA
3 MG_20220412_FE2_48 424.675 Tentacle_snut POINT Curl
4 MG_20220412_FE2_49 90.453 Tentacle_proximalOT POINT Protrude, Cross Oral Tentacles
5 MG_20220412_FE2_50 668.519 Tentacle_proximalOT POINT NA
6 MG_20220412_FE2_52 105.655 Tentacle_proximalOT POINT NA