Is there a way to extract data from a df if an index value is close to an index in another df?

user_13209152 0
Viewed 48

So I have two dataframes, df1 consisting of a few thousand GPS indices and weather data, and df2 containing sightings of animals and their GPS indices (df1 is longer than df2).

Example (df1 = variables, df2 = sightings):

variables <- data.frame(gpsindex=c(100, 200, 300, 400, 500, 600), weather=c(3, 2, 1, 5, 4, 3))
sightings <- data.frame(gpsindex=c(199, 310, 324, 510), speciesindex=c(1, 2, 3, 4))

> variables
  gpsindex weather
1      100       3
2      200       2
3      300       1
4      400       5
5      500       4
6      600       3

> sightings
  gpsindex speciesindex
1      199            1
2      310            2
3      324            3
4      510            4

My goal is to use the gpsindex in sightings to extract the appropriate weather value in variables and then add it back to the sightings dataframe. 

  gpsindex speciesindex weather
1      199            1       3
2      310            2       1
3      324            3       1
4      510            4       4

Like this. However, to extract the correct weather value, gpsindex needs to be in between rows 1&2, 2&3, etc. Multiple sightings may also be within the same gpsindex interval.

I've been searching through StackOverflow for about a week now, and I cannot find any threads discussing this technique. Or (more likely) I'm searching for the wrong things/using the wrong vocabulary.

Anyone have any suggestions?

dataframe r indexing
2 Answers

If your variable data.frame is sorted according to gpsindex, you can do this:

idx = findInterval(sightings$gpsindex,variables$gpsindex)

data.frame(sightings,weather=variables$weather[idx])
  gpsindex speciesindex weather
1      199            1       3
2      310            2       1
3      324            3       1
4      510            4       4

The findInterval() function finds where an element falls along a vector, so in this case, it returns you exactly the row you need..

user_122584590

With data.table you could do a fast rolling join. If you use roll = TRUE then you will roll the prevailing value in gpsindex forward (similar to "last observation carried forward"). Other alternative options exist (see ?data.table for details).

library(data.table)

setDT(sightings)
setDT(variables)

variables[sightings, on = .(gpsindex), roll = T]

Output

   gpsindex weather speciesindex
1:      199       3            1
2:      310       1            2
3:      324       1            3
4:      510       4            4
user_34606700
Related