How to use sapply to identify values within a time interval that have a matching identifier?

Viewed 24

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
1 Answers

You can use mapply for this:

beh <- mapply(function(file, time) {
  b <- with(point_behaviors, 
       Behavior[File==file & Time > time & Start_Interval <= time])
  paste(b, collapse=', ')
}, contacts$File, contacts$Time)

contacts$Response <- ifelse(beh=='', NA, beh)

Data:

contacts <- structure(list(File = c("MG_20220412_FE2_48", "MG_20220412_FE2_48", 
"MG_20220412_FE2_48", "MG_20220412_FE2_49", "MG_20220412_FE2_50", 
"MG_20220412_FE2_52"), Time = c(83.451, 108.939, 424.675, 90.453, 
668.519, 105.655), Behavior = c("Tentacle_head", "Tentacle_proximalOT", 
"Tentacle_snut", "Tentacle_proximalOT", "Tentacle_proximalOT", 
"Tentacle_proximalOT"), Status = c("POINT", "POINT", "POINT", 
"POINT", "POINT", "POINT"), Response = c(NA, NA, "Curl", "Protrude, Cross_Oral_Tentacles", 
NA, NA)), row.names = c("1", "2", "3", "4", "5", "6"), class = "data.frame")

point_behaviors <- structure(list(File = c("MG_20220412_FE2_48", "MG_20220412_FE2_48", 
"MG_20220412_FE2_48", "MG_20220412_FE2_48", "MG_20220412_FE2_48", 
"MG_20220412_FE2_49", "MG_20220412_FE2_49", "MG_20220412_FE2_49", 
"MG_20220412_FE2_49", "MG_20220412_FE2_49", "MG_20220412_FE2_49", 
"MG_20220412_FE2_49", "MG_20220412_FE2_49", "MG_20220412_FE2_49", 
"MG_20220412_FE2_49", "MG_20220412_FE2_50"), Time = c(324.455, 
403.236, 425.168, 465.896, 513.755, 12.169, 56.183, 62.009, 77.708, 
83.714, 90.5, 90.526, 118.759, 186.009, 458.317, 294.004), Behavior = c("Pause", 
"Rhinal_Bristle", "Curl", "Protrude", "Lateral_Bristle", "Pause", 
"J-Turn", "Lateral_Bristle", "J-Turn", "Oral_Tentacle_Tap", "Protrude", 
"Cross_Oral_Tentacles", "Body_Column_Scrunch", "Body_Column_Scrunch", 
"Rhinal_Bristle", "Curl"), Status = c("POINT", "POINT", "POINT", 
"START", "POINT", "POINT", "POINT", "POINT", "POINT", "POINT", 
"START", "POINT", "POINT", "POINT", "POINT", "POINT"), Start_Interval = c(323.705, 
402.486, 424.418, 465.146, 513.005, 11.419, 55.433, 61.259, 76.958, 
82.964, 90.4, 89.676, 118.009, 185.259, 457.567, 293.254)), class = "data.frame", row.names = c("25", 
"26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", 
"37", "38", "39", "40"))

I had to change point_behaviors slightly so that there really were two matches for the fourth contact.

Related