Styjun

I am working in R and trying to understand the best way to join data frames when one of them is very large.

I have a data frame which is not excruciatingly large but also not small (~80K observations of 8 variables, 144 MB). I need to match observations from this data frame to observations from another smaller data frame on the basis of a date range. Specifically, I have:

events.df <- data.frame(individual=c('A','B','C','A','B','C'),
 event=c(1,1,1,2,2,2),
 time=as.POSIXct(c('2014-01-01 08:00:00','2014-01-05 13:00:00','2014-01-10 07:00:00','2014-05-01 01:00:00','2014-06-01 12:00:00','2014-08-01 10:00:00'),format="%Y-%m-%d %H:%M:%S"))

trips.df <- data.frame(individual=c('A','B','C'),trip=c('x1A','CA1B','XX78'),
 trip_start = as.POSIXct(c('2014-01-01 06:00:00','2014-01-04 03:00:00','2014-01-08 12:00:00'),format="%Y-%m-%d %H:%M:%S"),
 trip_end=as.POSIXct(c('2014-01-03 06:00:00','2014-01-06 03:00:00','2014-01-11 12:00:00'),format="%Y-%m-%d %H:%M:%S")) 

In my case events.df contains around 80,000 unique events and I am looking to match them to events from the trips.df data frame, which has around 200 unique trips. Each trip has a unique trip identifier ('trip'). I would like to match based on whether the event took place during the date range defining a trip.

First, I have tried fuzzy_inner_join from the fuzzyjoin library. It works great in principal:

fuzzy_inner_join(events.df,trips.df,by=c('individual'='individual','time'='trip_start','time'='trip_end'),match_fun=list(`==`,`>=`,`<=`))
 individual.x event time individual.y trip trip_start trip_end
1 A 1 2014-01-01 08:00:00 A x1A 2014-01-01 06:00:00 2014-01-03 06:00:00
2 B 1 2014-01-05 13:00:00 B CA1B 2014-01-04 03:00:00 2014-01-06 03:00:00
3 C 1 2014-01-10 07:00:00 C XX78 2014-01-08 12:00:00 2014-01-11 12:00:00
> 

but runs out of memory when I try to apply it to the larger data frames.

Here is a second solution I cobbled together:

trip.match <- function(tripid)
 individual <- trips.df$individual[trips$trip==tripid]
 start <- trips.df$trip_start[trips$trip==tripid]
 end <- trips.df$trip_end[trips$trip==tripid]

tmp <- events.df[events.df$individual==individual &
 events.df$time>= start &
 events.df$time<= end,]
tmp$trip <- tripid
return(tmp) 


result <- data.frame(rbindlist(lapply(unique(trips.df$trip),trip.match)

This solution also breaks down because the list object returned by lapply is 25GB and the attempt to cast this list to a data frame also exhausts the available memory.

I have been able to do what I need to do using a for loop. Basically, I append a column onto events.df and loop through the unique trip identifiers and populate the new column in events.df accordingly:

events.df$trip <- NA
for(i in unique(trips.df$trip))
 individual <- trips.df$individual[trips.df$trip==i]
 start <- min(trips.df$trip_start[trips.df$trip==i])
 end <- max(trips.df$trip_end[trips.df$trip==i]) 

 events.df$trip[events.df$individual==individual & events.df$time >= start & events.df$time <= end] <- i


> events.df
 individual event time trip
1 A 1 2014-01-01 08:00:00 x1A
2 B 1 2014-01-05 13:00:00 CA1B
3 C 1 2014-01-10 07:00:00 XX78
4 A 2 2014-05-01 01:00:00 <NA>
5 B 2 2014-06-01 12:00:00 <NA>
6 C 2 2014-08-01 10:00:00 <NA>

My question is this: I'm not a very advanced R programmer so I expect there is a more memory efficient way to accomplish what I'm trying to do. Is there?

Try creating a table that expands the trip ranges by hour and then merge with the event. Here is an example (using the data.table function because data.table outperforms data.frame for larger datasets):

library('data.table')
tripsV <- unique(trips.df$trip)
tripExpand <- function(t)
 dateV <- seq(trips.df$trip_start[trips.df$trip == t], 
 trips.df$trip_end[trips.df$trip == t], 
 by = 'hour')
 data.table(trip = t, time = dateV)


trips.dt <- rbindlist(
 lapply(tripsV, function(t) tripExpand(t))
 )

merge(events.df,
 trips.dt,
 by = 'time')

Output:

 time individual event trip
1 2014-01-01 08:00:00 A 1 x1A
2 2014-01-05 13:00:00 B 1 CA1B
3 2014-01-10 07:00:00 C 1 XX78

So you are basically translating the trip table to trip-hour long-form panel dataset. That makes for easy merging with the event dataset. I haven't benchmarked it to your current method but my hunch is that it will be more memory & cpu efficient.

Consider splitting your data with data.table's split and run each subset on fuzzy_inner_join then call rbindlist to bind all data frame elements together for single output.

df_list <- data.table::split(events.df, by="individual")

fuzzy_list <- lapply(df_list, function(sub.df) 
 fuzzy_inner_join(sub.df, trips.df, 
 by = c('individual'='individual', 'time'='trip_start', 'time'='trip_end'), 
 match_fun = list(`==`,`>=`,`<=`)
 )
)

# REMOVE TEMP OBJECT AND CALL GARBAGE COLLECTOR
rm(df_list); gc()

final_df <- rbindlist(fuzzy_list)

# REMOVE TEMP OBJECT AND CALL GARBAGE COLLECTOR
rm(fuzzy_list); gc()