CODEWITHSUNDEEP
rfor-looplapply
aaronmams
aaronmams

Reputation: 151

I want to understand why lapply exhausts memory but a for loop doesn't

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?

Upvotes: 0

Views: 568

Answers (2)

Parfait
Parfait

Reputation: 107687

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()

Upvotes: 1

Andrew Royal
Andrew Royal

Reputation: 346

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.

Upvotes: 2

Related Questions