replacing nested loop in r (with multiple criteria)

Question

I am very new in r and the forum - I have searched the forum but cannot find a very simple description of how to do this.

I am trying to combine two datasets, A and B by overwriting records in A where certain criteria match with B. Here's an example of the data:

A:

date farmID ip lm
146  F10    10 0.5
147  F10    11 0.6
148  F10    6  0.6
149  F10    8  0.1
146  F11    10 0.4
147  F11    12 0.3
148  F11    4  0.5
149  F11    5  0.3

B:

farmID start end ip lm
F10    140   145 3  0.4
F10    140   147 15 0.8
F11    145   146 13 0.9
F11    146   149 20 0.7

I need to do the following: if date lies between start and end (not inclusive on end), and farmID are the same, then overwrite columns ip and lm in B

the result would be:

date farmID ip  lm
146  F10    15  0.8   <-- this one changed
147  F10    15  0.8   <-- this one changed
148  F10    6   0.6
149  F10    8   0.1
146  F11    20  0.7   <-- this one changed
147  F11    20  0.7   <-- this one changed
148  F11    20  0.7   <-- this one changed
149  F11    20  0.7   <-- this one changed

I first tried nested loops to go through the two tables, but I have around 800,000 records in A, so this is way too slow.

for (i in seq(1:nrow(A))){
  for (j in seq(1:nrow(B))){
    if (B$farmID[j] == A$farmID[i]){
      if (B$start[j] <= A$date[i] | A$date[i] > B$end[j]){
     
        A$lm[i] <- B$lm[j]
        A$ip[i] <- B$ip[j]
  
      }
    }
  }

I know that this is not the right approach for r and I should be using a vectorised function.

I found this quite similar post without the second criteria for farmID, so thought I could do this maybe with data.table - Replacing nested loop in R

Can someone please tell me a sensible way to perform this?

EDIT

This took a while to cut down to a useful size. I tried to select representative data.

dput(B)


structure(list(SiteID = c("FS0013", "FS0013", "FS0056", "FS0068", 
"FS0068", "FS0206", "FS0206", "FS0407", "FS0408", "FS0408", "FS0694", 
"FS0695", "FS0695", "FS0695", "FS1033", "FS1036", "FS1039", "FS1039", 
"FS1335", "FS1335"), FromDate = c(42401L, 42758L, 42443L, 42969L, 
43166L, 43782L, 43860L, 43119L, 42642L, 43228L, 42852L, 42527L, 
42895L, 43918L, 43848L, 42711L, 42569L, 43255L, 43954L, 43968L
), ToDate = c(42766L, 43123L, 42808L, 43334L, 43531L, 44147L, 
44225L, 43484L, 43007L, 43593L, 43217L, 42892L, 43260L, 44283L, 
44213L, 43076L, 42934L, 43620L, 44319L, 44333L), LFF_On = c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 0L, 
1L, 1L, 1L), ip = c(4L, 4L, 1L, 3L, 3L, 5L, 5L, NA, NA, 6L, 9L, 
NA, NA, 1L, 5L, NA, NA, 6L, 9L, 9L), lm = c(NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 2L)), class = "data.frame", row.names = c(NA, 
-20L))

dput(A)

structure(list(date = c(42395L, 42395L, 42395L, 42395L, 42395L, 
42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42396L, 
42396L, 42396L, 42396L, 42396L, 42396L, 42396L, 42396L, 42396L, 
42396L, 42396L, 42396L, 42397L, 42397L, 42397L, 42397L, 42397L, 
42397L, 42397L, 42397L, 42397L, 42397L, 42397L, 42397L, 42398L, 
42398L, 42398L, 42398L, 42398L, 42398L, 42398L, 42398L, 42398L, 
42398L, 42398L, 42398L, 42399L, 42399L, 42399L, 42399L, 42399L, 
42399L, 42399L, 42399L, 42399L, 42399L, 42399L, 42399L, 42400L, 
42400L, 42400L, 42400L, 42400L, 42400L, 42400L, 42400L, 42400L, 
42400L, 42400L, 42400L, 42401L, 42401L, 42401L, 42401L, 42401L, 
42401L, 42401L, 42401L, 42401L, 42401L, 42401L, 42401L, 42402L, 
42402L, 42402L, 42402L, 42402L, 42402L, 42402L, 42402L, 42402L, 
42402L, 42402L, 42402L, 42403L, 42403L, 42403L, 42403L, 42403L, 
42403L, 42403L, 42403L, 42403L, 42403L, 42403L, 42403L, 42404L, 
42404L, 42404L, 42404L, 42404L, 42404L, 42404L, 42404L, 42404L, 
42404L, 42404L, 42404L, 42405L, 42405L, 42405L, 42405L, 42405L, 
42405L, 42405L, 42405L, 42405L, 42405L, 42405L, 42405L, 42406L, 
42406L, 42406L, 42406L, 42406L, 42406L, 42406L, 42406L, 42406L, 
42406L, 42406L, 42406L, 42407L, 42407L, 42407L, 42407L, 42407L, 
42407L, 42407L, 42407L, 42407L, 42407L, 42407L, 42407L, 42395L, 
42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 
42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 
42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 
42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 
42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 
42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 
42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 42395L, 
42395L, 42395L, 42395L, 42643L, 42643L, 42643L, 42643L, 42643L, 
42643L, 42643L, 42643L, 42643L, 42643L, 42643L, 42643L, 42800L, 
42800L, 42800L, 42800L, 42800L, 42800L, 42800L, 42800L, 42800L, 
42800L, 42800L, 42800L, 42801L, 42801L, 42801L, 42801L, 42801L, 
42801L, 42801L, 42801L, 42801L, 42801L, 42801L, 42801L, 42802L, 
42802L, 42802L, 42802L, 42802L, 42802L, 42802L, 42802L, 42802L, 
42802L, 42802L, 42802L, 42803L, 42803L, 42803L, 42803L, 42803L, 
42803L, 42803L, 42803L, 42803L, 42803L, 42803L, 42803L, 42804L, 
42804L, 42804L, 42804L, 42804L, 42804L, 42804L, 42804L, 42804L, 
42804L, 42804L, 42804L, 42805L, 42805L, 42805L, 42805L, 42805L, 
42805L, 42805L, 42805L, 42805L, 42805L, 42805L, 42805L, 42806L, 
42806L, 42806L, 42806L, 42806L, 42806L, 42806L, 42806L, 42806L, 
42806L, 42806L, 42806L, 42807L, 42807L, 42807L, 42807L, 42807L, 
42807L, 42807L, 42807L, 42807L, 42807L, 42807L, 42807L, 42808L, 
42808L, 42808L, 42808L, 42808L, 42808L, 42808L, 42808L, 42808L, 
42808L, 42808L, 42808L, 42809L, 42809L, 42809L, 42809L, 42809L, 
42809L, 42809L, 42809L, 42809L, 42809L, 42809L, 42809L, 42810L, 
42810L, 42810L, 42810L, 42810L, 42810L, 42810L, 42810L, 42810L, 
42810L, 42810L, 42810L, 42811L, 42811L, 42811L, 42811L, 42811L, 
42811L, 42811L, 42811L, 42811L, 42811L, 42811L, 42811L, 42894L, 
42894L, 42894L, 42894L, 42894L, 42894L, 42894L, 42894L, 42894L, 
42894L, 42894L, 42894L, 42895L, 42895L, 42895L, 42895L, 42895L, 
42895L, 42895L, 42895L, 42895L, 42895L, 42895L, 42895L, 43160L, 
43160L, 43160L, 43160L, 43160L, 43160L, 43160L, 43160L, 43160L, 
43160L, 43160L, 43160L, 43161L, 43161L, 43161L, 43161L, 43161L, 
43161L, 43161L, 43161L, 43161L, 43161L, 43161L, 43161L, 43162L, 
43162L, 43162L, 43162L, 43162L, 43162L, 43162L, 43162L, 43162L, 
43162L, 43162L, 43162L, 43163L, 43163L, 43163L, 43163L, 43163L, 
43163L, 43163L, 43163L, 43163L, 43163L, 43163L, 43163L, 43164L, 
43164L, 43164L, 43164L, 43164L, 43164L, 43164L, 43164L, 43164L, 
43164L, 43164L, 43164L, 43165L, 43165L, 43165L, 43165L, 43165L, 
43165L, 43165L, 43165L, 43165L, 43165L, 43165L, 43165L, 43166L, 
43166L, 43166L, 43166L, 43166L, 43166L, 43166L, 43166L, 43166L, 
43166L, 43166L, 43166L, 43167L, 43167L, 43167L, 43167L, 43167L, 
43167L, 43167L, 43167L, 43167L, 43167L, 43167L, 43167L, 43168L, 
43168L, 43168L, 43168L, 43168L, 43168L, 43168L, 43168L, 43168L, 
43168L, 43168L, 43168L, 43169L, 43169L, 43169L, 43169L, 43169L, 
43169L, 43169L, 43169L, 43169L, 43169L, 43169L, 43169L, 43618L, 
43618L, 43618L, 43618L, 43618L, 43618L, 43618L, 43618L, 43618L, 
43618L, 43618L, 43618L, 43619L, 43619L, 43619L, 43619L, 43619L, 
43619L, 43619L, 43619L, 43619L, 43619L, 43619L, 43619L, 43620L, 
43620L, 43620L, 43620L, 43620L, 43620L, 43620L, 43620L, 43620L, 
43620L, 43620L, 43620L, 43851L, 43851L, 43851L, 43851L, 43851L, 
43851L, 43851L, 43851L, 43851L, 43851L, 43851L, 43851L, 43852L, 
43852L, 43852L, 43852L, 43852L, 43852L, 43852L, 43852L, 43852L, 
43852L, 43852L, 43852L, 43853L, 43853L, 43853L, 43853L, 43853L, 
43853L, 43853L, 43853L, 43853L, 43853L, 43853L, 43853L, 43854L, 
43854L, 43854L, 43854L, 43854L, 43854L, 43854L, 43854L, 43854L, 
43854L, 43854L, 43854L, 43855L, 43855L, 43855L, 43855L, 43855L, 
43855L, 43855L, 43855L, 43855L, 43855L, 43855L, 43855L, 43856L, 
43856L, 43856L, 43856L, 43856L, 43856L, 43856L, 43856L, 43856L, 
43856L, 43856L, 43856L, 43857L, 43857L, 43857L, 43857L, 43857L, 
43857L, 43857L, 43857L, 43857L, 43857L, 43857L, 43857L, 43858L, 
43858L, 43858L, 43858L, 43858L, 43858L, 43858L, 43858L, 43858L, 
43858L, 43858L, 43858L, 43859L, 43859L, 43859L, 43859L, 43859L, 
43859L, 43859L, 43859L, 43859L, 43859L, 43859L, 43859L, 43860L, 
43860L, 43860L, 43860L, 43860L, 43860L, 43860L, 43860L, 43860L, 
43860L, 43860L, 43860L, 43861L, 43861L, 43861L, 43861L, 43861L, 
43861L, 43861L, 43861L, 43861L, 43861L, 43861L, 43861L, 43862L, 
43862L, 43862L, 43862L, 43862L, 43862L, 43862L, 43862L, 43862L, 
43862L, 43862L, 43862L, 43863L, 43863L, 43863L, 43863L, 43863L, 
43863L, 43863L, 43863L, 43863L, 43863L, 43863L, 43863L, 43864L, 
43864L, 43864L, 43864L, 43864L, 43864L, 43864L, 43864L, 43864L, 
43864L, 43864L, 43864L, 43865L, 43865L, 43865L, 43865L, 43865L, 
43865L, 43865L, 43865L, 43865L, 43865L, 43865L, 43865L, 43866L, 
43866L, 43866L, 43866L, 43866L, 43866L, 43866L, 43866L, 43866L, 
43866L, 43866L, 43866L, 43867L, 43867L, 43867L, 43867L, 43867L, 
43867L, 43867L, 43867L, 43867L, 43867L, 43867L, 43867L, 43868L, 
43868L, 43868L, 43868L, 43868L, 43868L, 43868L, 43868L, 43868L, 
43868L, 43868L, 43868L, 43869L, 43869L, 43869L, 43869L, 43869L, 
43869L, 43869L, 43869L, 43869L, 43869L, 43869L, 43869L), SiteID = c("FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", 
"FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", 
"FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", 
"FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", 
"FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", 
"FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", 
"FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", 
"FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", 
"FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", 
"FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", 
"FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", 
"FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", 
"FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", 
"FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", 
"FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", 
"FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", 
"FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", 
"FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", 
"FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", 
"FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", 
"FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", 
"FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", 
"FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", 
"FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", 
"FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", 
"FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", 
"FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", 
"FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", 
"FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", 
"FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", 
"FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", 
"FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", 
"FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", 
"FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", 
"FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", 
"FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", 
"FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", 
"FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", 
"FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", 
"FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", 
"FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", 
"FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", 
"FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", 
"FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", 
"FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", 
"FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", 
"FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", 
"FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", 
"FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", 
"FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", 
"FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", 
"FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", 
"FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", 
"FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", 
"FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", 
"FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", 
"FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", 
"FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", 
"FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", 
"FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", 
"FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", 
"FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", 
"FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", 
"FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", 
"FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", 
"FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", 
"FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", 
"FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", 
"FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", 
"FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", "FS0206", 
"FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", "FS1039", 
"FS1335", "FS0013", "FS0056", "FS0068", "FS0206", "FS0407", "FS0408", 
"FS0694", "FS0695", "FS1033", "FS1036", "FS1039", "FS1335", "FS0013", 
"FS0056", "FS0068", "FS0206", "FS0407", "FS0408", "FS0694", "FS0695", 
"FS1033", "FS1036", "FS1039", "FS1335", "FS0013", "FS0056", "FS0068", 
"FS0206", "FS0407", "FS0408", "FS0694", "FS0695", "FS1033", "FS1036", 
"FS1039", "FS1335"), LFF_On = c(1L, 1L, 0L, 0L, 0L, 1L, 1L, 1L, 
1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 0L, 
1L, 0L, 0L, 1L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 
0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 1L, 
0L, 1L, 0L, 0L, 1L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 
1L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 
0L, 1L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 0L, 1L, 
1L, 0L, 0L, 1L, 1L, 0L, 1L, 1L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 0L, 
0L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 1L, 1L, 0L, 
1L, 1L, 0L, 0L, 1L, 0L, 0L, 1L, 1L, 1L, 0L, 1L, 1L, 0L, 1L, 0L, 
1L, 0L, 1L, 1L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 
1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 1L, 
0L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 1L, 
0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 
0L, 0L, 1L, 0L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 
0L, 1L, 0L, 1L, 1L, 0L, 1L, 1L, 1L, 1L, 1L, 0L, 1L, 0L, 1L, 1L, 
1L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 1L, 1L, 0L, 
0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 0L, 
1L, 0L, 0L, 0L, 1L, 0L, 1L, 1L, 1L, 1L, 0L, 0L, 1L, 1L, 1L, 1L, 
0L, 0L, 1L, 1L, 1L, 0L, 0L, 1L, 1L, 1L, 1L, 0L, 1L, 1L, 1L, 1L, 
0L, 1L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 1L, 
0L, 1L, 1L, 0L, 0L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 1L, 1L, 
0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 
0L, 1L, 0L, 0L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 1L, 1L, 1L, 
0L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 0L, 
0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 1L, 
0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 1L, 
1L, 0L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 1L, 1L, 1L, 
1L, 1L, 1L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 1L, 0L, 0L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 0L, 0L, 1L, 1L, 1L, 0L, 1L, 0L, 0L, 1L, 1L, 
0L, 0L, 1L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 1L, 1L, 0L, 1L, 1L, 0L, 
1L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 1L, 
1L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 
1L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 
0L, 0L, 1L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 
0L, 0L, 0L, 0L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 1L, 
0L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 1L, 1L, 1L, 0L, 1L, 1L, 
0L, 0L, 0L, 1L, 0L, 1L, 1L, 1L, 1L, 1L, 0L, 1L, 0L, 0L, 1L, 1L, 
1L, 0L, 1L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 
1L, 1L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 
1L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 1L, 1L, 0L, 1L, 1L, 0L, 1L, 1L, 
1L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 1L, 1L, 0L, 0L, 
1L, 0L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 
1L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 1L, 0L, 
0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 0L, 
1L, 0L, 0L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 
1L, 0L, 0L, 0L), ip = c(9L, 9L, 5L, 15L, 10L, 13L, 7L, 8L, 6L, 
8L, 5L, 10L, 15L, 5L, 13L, 15L, 11L, 9L, 12L, 14L, 7L, 6L, 5L, 
14L, 11L, 13L, 8L, 8L, 13L, 6L, 15L, 12L, 5L, 10L, 10L, 12L, 
10L, 7L, 6L, 11L, 14L, 10L, 15L, 7L, 8L, 5L, 5L, 9L, 13L, 15L, 
7L, 9L, 12L, 5L, 11L, 5L, 8L, 7L, 9L, 7L, 6L, 7L, 13L, 5L, 14L, 
14L, 11L, 12L, 15L, 13L, 14L, 7L, 7L, 13L, 14L, 13L, 9L, 15L, 
10L, 13L, 5L, 10L, 8L, 11L, 8L, 7L, 7L, 12L, 11L, 11L, 8L, 10L, 
14L, 9L, 9L, 10L, 12L, 15L, 13L, 10L, 8L, 9L, 15L, 12L, 12L, 
5L, 5L, 10L, 8L, 6L, 13L, 13L, 6L, 10L, 5L, 11L, 9L, 9L, 9L, 
14L, 6L, 6L, 6L, 9L, 13L, 8L, 9L, 10L, 7L, 14L, 12L, 12L, 10L, 
11L, 8L, 7L, 8L, 12L, 11L, 7L, 8L, 12L, 10L, 11L, 12L, 5L, 14L, 
7L, 10L, 7L, 11L, 9L, 15L, 13L, 7L, 5L, 10L, 6L, 11L, 11L, 10L, 
14L, 12L, 10L, 8L, 8L, 10L, 5L, 13L, 5L, 6L, 15L, 13L, 13L, 9L, 
8L, 6L, 13L, 13L, 14L, 11L, 8L, 5L, 10L, 8L, 12L, 12L, 6L, 15L, 
13L, 5L, 12L, 7L, 10L, 9L, 11L, 9L, 11L, 11L, 5L, 10L, 5L, 14L, 
5L, 12L, 8L, 12L, 5L, 9L, 9L, 12L, 9L, 14L, 9L, 7L, 7L, 12L, 
10L, 9L, 11L, 9L, 9L, 11L, 9L, 6L, 6L, 5L, 13L, 11L, 15L, 7L, 
10L, 13L, 8L, 5L, 9L, 6L, 13L, 11L, 8L, 9L, 5L, 11L, 14L, 6L, 
6L, 9L, 12L, 7L, 6L, 6L, 8L, 13L, 6L, 13L, 6L, 6L, 14L, 5L, 12L, 
5L, 14L, 9L, 13L, 7L, 10L, 13L, 6L, 11L, 12L, 7L, 5L, 13L, 15L, 
15L, 10L, 14L, 15L, 14L, 9L, 9L, 5L, 10L, 10L, 10L, 10L, 12L, 
14L, 12L, 10L, 15L, 15L, 12L, 15L, 6L, 12L, 10L, 13L, 14L, 8L, 
5L, 12L, 9L, 5L, 8L, 14L, 9L, 7L, 6L, 15L, 10L, 8L, 12L, 6L, 
13L, 15L, 10L, 13L, 5L, 10L, 9L, 9L, 12L, 10L, 7L, 9L, 11L, 7L, 
11L, 9L, 5L, 12L, 14L, 14L, 15L, 8L, 14L, 9L, 7L, 11L, 9L, 12L, 
8L, 6L, 9L, 14L, 13L, 12L, 13L, 11L, 8L, 5L, 5L, 9L, 5L, 6L, 
14L, 14L, 12L, 10L, 7L, 15L, 8L, 5L, 12L, 15L, 14L, 5L, 7L, 7L, 
11L, 6L, 15L, 14L, 7L, 15L, 10L, 13L, 11L, 11L, 14L, 10L, 12L, 
8L, 8L, 8L, 8L, 14L, 7L, 9L, 10L, 11L, 10L, 8L, 10L, 14L, 14L, 
13L, 11L, 7L, 8L, 10L, 8L, 9L, 12L, 11L, 11L, 14L, 13L, 9L, 11L, 
7L, 7L, 9L, 13L, 6L, 10L, 9L, 11L, 8L, 7L, 7L, 11L, 12L, 8L, 
7L, 8L, 13L, 14L, 6L, 10L, 9L, 10L, 12L, 5L, 15L, 14L, 6L, 14L, 
12L, 13L, 9L, 7L, 9L, 11L, 14L, 8L, 10L, 8L, 10L, 13L, 13L, 13L, 
6L, 8L, 5L, 5L, 6L, 6L, 11L, 7L, 6L, 9L, 8L, 5L, 6L, 7L, 9L, 
5L, 9L, 12L, 10L, 11L, 10L, 6L, 5L, 11L, 9L, 9L, 10L, 7L, 10L, 
13L, 8L, 7L, 8L, 15L, 8L, 13L, 11L, 12L, 14L, 11L, 6L, 9L, 14L, 
15L, 9L, 14L, 15L, 10L, 8L, 11L, 13L, 6L, 6L, 9L, 10L, 6L, 7L, 
15L, 14L, 8L, 10L, 9L, 12L, 5L, 12L, 10L, 10L, 14L, 10L, 8L, 
14L, 7L, 10L, 12L, 12L, 15L, 14L, 10L, 9L, 14L, 10L, 7L, 9L, 
11L, 7L, 14L, 10L, 10L, 13L, 9L, 6L, 13L, 7L, 13L, 6L, 10L, 10L, 
14L, 9L, 11L, 14L, 6L, 6L, 12L, 8L, 6L, 6L, 5L, 6L, 15L, 9L, 
12L, 14L, 14L, 9L, 8L, 10L, 10L, 11L, 12L, 15L, 6L, 5L, 13L, 
6L, 7L, 7L, 11L, 11L, 7L, 15L, 9L, 6L, 10L, 14L, 6L, 5L, 15L, 
10L, 13L, 13L, 9L, 11L, 6L, 14L, 14L, 9L, 5L, 8L, 11L, 13L, 8L, 
11L, 5L, 7L, 11L, 8L, 13L, 11L, 8L, 14L, 15L, 13L, 6L, 8L, 9L, 
14L, 8L, 14L, 13L, 10L, 5L, 5L, 5L, 10L, 9L, 14L, 8L, 6L, 14L, 
8L, 10L, 11L, 12L, 13L, 15L, 5L, 11L, 14L, 14L, 6L, 8L, 9L, 15L, 
10L, 6L, 5L, 5L, 9L, 10L, 9L, 15L, 8L, 10L, 5L, 6L, 5L, 13L, 
8L, 5L, 14L, 14L, 10L, 8L, 11L, 15L, 15L, 13L, 13L, 14L, 9L, 
11L, 8L, 13L, 9L, 13L, 9L, 8L, 5L, 7L, 13L, 7L, 5L, 11L, 15L, 
8L, 13L, 6L, 9L, 5L, 6L, 15L, 8L, 15L, 11L, 6L, 6L, 10L, 12L, 
10L, 9L, 10L, 5L, 10L, 6L, 12L, 14L, 12L, 6L, 9L, 14L, 13L, 12L, 
12L, 12L, 7L, 12L, 9L, 14L, 6L, 6L, 9L, 12L, 14L, 10L, 8L, 9L, 
9L, 7L, 11L, 5L, 12L, 14L, 11L, 15L, 12L, 11L, 5L, 6L, 10L, 9L, 
7L, 12L, 13L, 11L, 9L, 6L, 6L, 14L, 5L, 8L, 11L, 7L, 12L, 8L, 
11L, 12L, 10L, 6L, 13L, 15L, 8L, 12L, 13L, 15L, 13L, 6L, 6L, 
11L, 14L, 13L, 13L, 6L, 6L, 14L, 14L, 11L), lm = c(3L, 4L, 1L, 
3L, 4L, 2L, 3L, 1L, 1L, 4L, 2L, 3L, 3L, 1L, 2L, 3L, 1L, 4L, 4L, 
1L, 3L, 1L, 4L, 3L, 1L, 4L, 3L, 2L, 4L, 4L, 3L, 2L, 2L, 2L, 2L, 
3L, 2L, 4L, 1L, 1L, 2L, 4L, 2L, 2L, 1L, 1L, 1L, 4L, 4L, 4L, 3L, 
4L, 1L, 2L, 1L, 4L, 2L, 3L, 1L, 3L, 2L, 1L, 3L, 2L, 1L, 4L, 1L, 
2L, 4L, 1L, 2L, 3L, 3L, 4L, 4L, 4L, 2L, 3L, 4L, 4L, 3L, 1L, 4L, 
3L, 3L, 4L, 1L, 4L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 4L, 4L, 
3L, 2L, 4L, 2L, 4L, 1L, 1L, 2L, 4L, 1L, 1L, 4L, 3L, 2L, 4L, 3L, 
4L, 2L, 4L, 3L, 3L, 4L, 2L, 4L, 1L, 1L, 2L, 1L, 2L, 3L, 3L, 1L, 
4L, 3L, 3L, 2L, 2L, 4L, 4L, 4L, 4L, 4L, 2L, 4L, 1L, 3L, 2L, 3L, 
1L, 3L, 3L, 2L, 4L, 1L, 1L, 2L, 1L, 2L, 2L, 1L, 3L, 1L, 3L, 3L, 
2L, 3L, 1L, 3L, 4L, 4L, 2L, 3L, 2L, 2L, 3L, 1L, 3L, 2L, 4L, 1L, 
4L, 2L, 4L, 1L, 3L, 2L, 1L, 4L, 3L, 1L, 3L, 4L, 4L, 1L, 3L, 2L, 
1L, 3L, 4L, 1L, 1L, 4L, 1L, 4L, 4L, 1L, 2L, 2L, 3L, 3L, 3L, 1L, 
2L, 4L, 2L, 4L, 1L, 2L, 3L, 4L, 4L, 4L, 1L, 2L, 3L, 4L, 1L, 3L, 
1L, 4L, 2L, 3L, 4L, 4L, 3L, 3L, 4L, 1L, 2L, 1L, 3L, 2L, 4L, 3L, 
4L, 1L, 2L, 2L, 4L, 1L, 4L, 3L, 3L, 4L, 3L, 2L, 4L, 4L, 4L, 1L, 
1L, 3L, 1L, 2L, 3L, 2L, 2L, 3L, 4L, 4L, 1L, 2L, 4L, 3L, 1L, 3L, 
3L, 2L, 4L, 3L, 1L, 1L, 2L, 3L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 1L, 
2L, 1L, 2L, 4L, 2L, 4L, 4L, 2L, 1L, 3L, 4L, 3L, 1L, 4L, 4L, 4L, 
3L, 1L, 4L, 1L, 3L, 2L, 3L, 3L, 2L, 4L, 2L, 2L, 3L, 3L, 3L, 4L, 
4L, 4L, 4L, 2L, 4L, 3L, 1L, 4L, 2L, 3L, 3L, 3L, 2L, 1L, 1L, 1L, 
3L, 4L, 4L, 2L, 2L, 2L, 2L, 4L, 3L, 2L, 3L, 4L, 1L, 2L, 1L, 3L, 
4L, 4L, 3L, 2L, 3L, 4L, 3L, 2L, 4L, 1L, 1L, 2L, 3L, 3L, 3L, 1L, 
2L, 3L, 3L, 4L, 1L, 2L, 3L, 4L, 1L, 4L, 4L, 3L, 3L, 2L, 4L, 4L, 
1L, 2L, 4L, 3L, 2L, 1L, 3L, 4L, 2L, 1L, 3L, 4L, 3L, 4L, 1L, 2L, 
2L, 1L, 3L, 3L, 1L, 4L, 4L, 2L, 4L, 1L, 2L, 2L, 2L, 2L, 1L, 4L, 
4L, 1L, 3L, 3L, 2L, 3L, 2L, 3L, 1L, 3L, 2L, 2L, 4L, 1L, 3L, 1L, 
3L, 4L, 3L, 4L, 2L, 3L, 4L, 3L, 2L, 3L, 1L, 1L, 3L, 1L, 1L, 3L, 
2L, 1L, 2L, 4L, 4L, 1L, 2L, 3L, 3L, 2L, 4L, 2L, 3L, 4L, 2L, 4L, 
4L, 4L, 3L, 2L, 4L, 3L, 3L, 2L, 1L, 2L, 2L, 4L, 1L, 4L, 2L, 4L, 
4L, 4L, 1L, 2L, 4L, 1L, 2L, 3L, 2L, 2L, 2L, 3L, 4L, 2L, 3L, 1L, 
1L, 2L, 3L, 2L, 4L, 2L, 2L, 4L, 1L, 2L, 3L, 2L, 1L, 1L, 2L, 2L, 
2L, 1L, 2L, 1L, 3L, 3L, 3L, 3L, 2L, 4L, 3L, 4L, 3L, 3L, 2L, 1L, 
2L, 1L, 2L, 2L, 4L, 3L, 2L, 2L, 1L, 1L, 1L, 2L, 4L, 1L, 2L, 3L, 
2L, 4L, 4L, 2L, 2L, 3L, 2L, 3L, 2L, 2L, 1L, 3L, 4L, 4L, 1L, 2L, 
4L, 2L, 1L, 1L, 4L, 3L, 1L, 4L, 3L, 1L, 4L, 1L, 2L, 3L, 2L, 4L, 
4L, 4L, 2L, 3L, 1L, 4L, 1L, 1L, 1L, 1L, 3L, 3L, 2L, 3L, 4L, 1L, 
1L, 1L, 2L, 4L, 4L, 1L, 2L, 3L, 4L, 2L, 1L, 2L, 1L, 3L, 4L, 1L, 
4L, 2L, 4L, 3L, 1L, 1L, 2L, 3L, 1L, 1L, 1L, 2L, 3L, 2L, 4L, 4L, 
3L, 1L, 1L, 3L, 3L, 4L, 3L, 2L, 2L, 3L, 2L, 4L, 3L, 4L, 1L, 2L, 
1L, 3L, 2L, 3L, 2L, 3L, 4L, 3L, 2L, 3L, 3L, 2L, 3L, 3L, 4L, 1L, 
3L, 1L, 2L, 3L, 3L, 1L, 3L, 2L, 3L, 1L, 4L, 1L, 4L, 2L, 1L, 4L, 
2L, 2L, 1L, 4L, 3L, 2L, 3L, 4L, 3L, 4L, 2L, 3L, 2L, 1L, 2L, 2L, 
3L, 4L, 3L, 2L, 4L, 4L, 2L, 2L, 1L, 4L, 1L, 3L, 4L, 2L, 2L, 1L, 
4L, 1L, 3L, 2L, 4L, 1L, 1L, 3L, 3L, 3L, 1L, 2L, 2L, 2L, 1L, 3L, 
4L, 1L, 2L, 1L, 4L, 1L, 2L, 4L, 1L, 4L, 1L, 3L, 1L, 1L, 4L, 2L, 
3L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 3L, 2L, 1L, 4L, 2L, 3L, 4L, 1L, 
4L, 4L, 2L, 1L, 4L, 1L, 3L, 2L, 4L, 1L, 4L, 3L, 4L, 4L, 4L, 2L, 
1L, 2L, 2L, 1L, 1L, 3L, 1L, 4L, 3L)), class = "data.frame", row.names = c(NA, 
-780L))

Can you post A and B as dput output with the function `dput(A)` — Dylan_Gomes, Nov 11 '20 at 19:36
If "not inclusive" on end, it seems like second row 147/F10 and last row 149/F11 should not change - am I mistaken? Also, for something like this I might consider a join with `data.table` or `fuzzy_left_join` from `fuzzy_join` package... — Ben, Nov 11 '20 at 19:54
Ben - you're right, my mistake. I've added some 'real' data now so hopefully will avoid that problem — keepalex, Nov 11 '20 at 20:41
keepalex, I had suggested an edit that made your question much more readable by enclosing the voluminous data within internally-scrolling code blocks. It's your call to revert my edit and go back to regular text, keep in mind that it will likely deter many readers. (I suggest you look at https://stackoverflow.com/editing-help#code.) — r2evans, Nov 11 '20 at 20:48
sorry r2evans, I must have been editing it when your changes came through and I didn't understand! Reverted now I think. — keepalex, Nov 11 '20 at 21:04

score 2 · Answer 1 · answered Nov 11 '20 at 20:44

One way to get that is to do a "range join" (aka "non-equi" join). This is supported in data.table, fuzzyjoin, and SQL in general (which can be done in native R using sqldf).

data.table

library(data.table)
setDT(A)
setDT(B)
B[A, on = .(farmID == farmID, start <= date, end >= date)
  ][, ip := fcoalesce(ip, i.ip)
    ][, lm := fcoalesce(lm, i.lm)
      ][, c("i.ip", "i.lm") := NULL ][]
#    farmID start end ip  lm
# 1:    F10   146 146 15 0.8
# 2:    F10   147 147 15 0.8
# 3:    F10   148 148  6 0.6
# 4:    F10   149 149  8 0.1
# 5:    F11   146 146 13 0.9
# 6:    F11   146 146 20 0.7
# 7:    F11   147 147 20 0.7
# 8:    F11   148 148 20 0.7
# 9:    F11   149 149 20 0.7

fuzzyjoin

(starting with data.frames, not data.tables)

library(fuzzyjoin)
out <- fuzzy_left_join(
  A, B,
  by = c(farmID = "farmID", date = "start", date = "end"),
  match_fun = list(`==`, `>=`, `<=`)
)
within(out, {
  farmID = farmID.x
  ip = ifelse(is.na(ip.y), ip.x, ip.y)
  lm = ifelse(is.na(lm.y), lm.x, lm.y)
  # remove side-specific versions of variables
  farmID.x <- farmID.y <- ip.x <- ip.y <- lm.x <- lm.y <- NULL
  # remove B$start/$end, not needed
  start <- end <- NULL
})
#   date  lm ip farmID
# 1  146 0.8 15    F10
# 2  147 0.8 15    F10
# 3  148 0.6  6    F10
# 4  149 0.1  8    F10
# 5  146 0.9 13    F11
# 6  146 0.7 20    F11
# 7  147 0.7 20    F11
# 8  148 0.7 20    F11
# 9  149 0.7 20    F11

SQL

If the data is already in a SQL DBMS before you load it, then it would be better to let the SQL server itself do this step ... if not, you can use sqldf to do it for you.

sqldf::sqldf(
  "select A.date, A.farmID,
     coalesce(B.ip, A.ip) as ip, coalesce(B.lm, A.lm) as lm
   from A left join B
     on A.farmID = b.farmID
     and A.date between B.start and B.end"
)
#   date farmID ip  lm
# 1  146    F10 15 0.8
# 2  147    F10 15 0.8
# 3  148    F10  6 0.6
# 4  149    F10  8 0.1
# 5  146    F11 13 0.9
# 6  146    F11 20 0.7
# 7  147    F11 20 0.7
# 8  148    F11 20 0.7
# 9  149    F11 20 0.7

I formulated this answer before your bigger-data edit, using this data instead:

A <- structure(list(date = c(146L, 147L, 148L, 149L, 146L, 147L, 148L, 149L), farmID = c("F10", "F10", "F10", "F10", "F11", "F11", "F11", "F11"), ip = c(10L, 11L, 6L, 8L, 10L, 12L, 4L, 5L), lm = c(0.5, 0.6, 0.6, 0.1, 0.4, 0.3, 0.5, 0.3)), row.names = c(NA, -8L), class = "data.frame")
B <- structure(list(farmID = c("F10", "F10", "F11", "F11"), start = c(140L, 140L, 145L, 146L), end = c(145L, 147L, 146L, 149L), ip = c(3L, 15L, 13L, 20L), lm = c(0.4, 0.8, 0.9, 0.7)), row.names = c(NA, -4L), class = "data.frame")

your data.table solution is where I was trying to go, except it seems to have edited B instead of A. I'm struggling work through the logic to make the changes. I've just posted some 'real' data which is more realistic - would you mind taking a look with your data.frame solution? — keepalex, Nov 11 '20 at 20:54
I had forgotten I'd given you another `data.table`-based answer elsewhere, glad it works well! — r2evans, Nov 11 '20 at 20:56
yes you did and I'm very grateful for your help! This is actually another version of the same problem. COuld you have a quick look at the new dataset included in the post? — keepalex, Nov 11 '20 at 20:59
For the most part (new columns notwithstanding) it appears to work much the same way. I'd likely add another coalescing line for `LFF_On` similar to the other two I did here. — r2evans, Nov 11 '20 at 21:04
One question on fcoalesce (I'm still struggling to work through the logic with this I'm afraid). On my main dataset I get: "Error in fcoalesce(LFF_On, i.LFF_On) : Item 2 is type double but the first item is type logical. Please coerce before coalescing." Changing LFF_On in both A and B to logical I get the opposite error: "Item 2 is type double but the first item is type logical. Please coerce before coalescing." Huh? - edit, something to do with NAs being treated as logical in fcoalesce — keepalex, Nov 11 '20 at 23:28
The most "base" level of `NA` is class `logical`, nothing more. I don't get it with my version of your data (old or new). You can always force one/both sides with `as.integer` or `as.numeric` (depending on your needs). — r2evans, Nov 12 '20 at 00:17

replacing nested loop in r (with multiple criteria)

EDIT

1 Answers1

data.table

fuzzyjoin

SQL