6
\$\begingroup\$

I am looking for help in designing my code more efficiently and also in keeping the slope of my personal learning curve steep:

For a research project I have per car data from two different car sharing companies across multiple cities and at least for a year (and more). The data collected holds information about location, type, ... per car every 5 minutes. As you can imagine this amounts to quite a few entries and hence quite a few gigabytes of data. As I want to predict the charging demand for different locations using point of interest information to enhance my models I have to aggregate the data and find out how long a car has been at a parking location for and how much it could have potentially charged (100 - fuelLevel).

I have written for loops to tackle this problem however this takes quite some time and won't be a very efficient way to aggregate the data across many cities and companies. Hence I would like to ask the community to help me come up with a vectorized solution that might just run faster. I have tried but always found myself going back to for-loops...

Here's a few lines of data on one(!) car:

id                modelId latitude longitude fuelLevel estimatedRange charging timestamp
WBY1Z210X0V307780 bmw_i3 55.703844 12.578759 84         97            0 2016-07-29T14:55:06Z
WBY1Z210X0V307780 bmw_i3 55.703844 12.578759 84         97            0 2016-07-29T15:00:08Z
………... ………... ………... ………... ………...           ………...     ………...            ………...
WBY1Z210X0V307780 bmw_i3 55.703844 12.578759 84         98            0 2016-07-30T11:35:07Z
WBY1Z210X0V307780 bmw_i3 55.691963 12.578775 83         99            0 2016-07-30T11:55:06Z
WBY1Z210X0V307780 bmw_i3 55.683871 12.60167  78         105           0 2016-07-30T12:35:07Z
WBY1Z210X0V307780 bmw_i3 55.683871 12.60167  78         105           0 2016-07-30T12:40:08Z
………... ………... ………... ………... ………...            ………...     ………...          ………...
WBY1Z210X0V307780 bmw_i3 55.683871 12.60167  78         105           0 2016-07-30T15:55:08Z
WBY1Z210X0V307780 bmw_i3 55.683871 12.60167  78         105           0 2016-07-30T16:00:06Z
WBY1Z210X0V307780 bmw_i3 55.634054 12.588184 73         101           0 2016-07-30T16:35:07Z
WBY1Z210X0V307780 bmw_i3 55.234054 12.388184 95         145           1 2016-07-30T23:40:06Z
WBY1Z210X0V307780 bmw_i3 55.234054 12.388184 95         145           1 2016-07-30T23:45:06Z

Now I ran multiple loops to slim it down to what I need: parking time per parking instance. I started by figuring out the start and end of a parking occurrence/instance. I start by sub-setting the data per car, order it and then, if the parking location changes, assign start or stop of a parking instance:

df.full <- fread("data.csv")
df.stst <- df.full
df.stst$parking <- NA    
for (i in c(l.id)){

      df.id <- subset(df.full, df.full$id == i)
      df.id <- df.id[order(df.id$timestamp), ]
      l.ts <- as.list(unique(df.id$timestamp))
      l.length <- length(l.ts)

      for (j in 2:l.length) {

        t <- df.id$latitude[[j]]
        tm1 <- df.id$latitude[[j-1]]

          if (t == tm1) {
            next
          }
          df.stop <- df.id[j-1, ]
          df.stop$parking <- "stop"
          df.start <- df.id[j, ]
          df.start$parking <- "start"
          df.stst <- rbind(df.stst, df.stop)
          df.stst <- rbind(df.stst, df.start)
      }
    next
    }

If you look at the last three rows of the data you can see that even though the car was "on a trip" the fuelLevel is higher than before. This is because someone has rented the car, plugged it in after driving around and then when the car started charging the car sharing company automatically locked the car so it could continue charging and it looked like it was still on the trip. Naturally I have to account for this so I went on to calculate the average trip duration and fuel consumption by figuring this out per parking instance with the following operations:

For fuel consumption per trip:

l.length <- nrow(df.stst)
df.avg.fuel <- df.stst
df.avg.fuel$fuel_prev_trip <- NA

for (j in 2:l.length) {
      if (df.avg.fuel$parking[[j]] == "stop"){
        next
      }
      ft <- df.avg.fuel$fuelLevel[[j]]
      ftm1 <- df.avg.fuel$fuelLevel[[j-1]]
      v.trip <-  ftm1 - ft
      df.avg.fuel$fuel_prev_trip[[j]] <- v.trip
    }

For time per trip:

df.avg.fuel$prev_duration <- NA
l.length <- nrow(df.avg.fuel)

for (j in 2:l.length) {
  if (df.avg.fuel$parking[[j]] == "stop"){
    next
  }
  df.avg.fuel$prev_duration[[j]] <- difftime(df.avg.fuel$timestamp[[j]], 
                                             df.avg.fuel$timestamp[[j-1]], units = "mins")
}

Adjusting parking start time and fuel level:

l.length <- nrow(df.avg.fuel.trip)

for (j in 2:200) {
   if (is.na(df.avg.fuel.trip$fuel_prev_trip[[j]])){
              next 
            }
       if (df.avg.fuel.trip$fuel_prev_trip[[j]] < 0){
            df.avg.fuel.trip$fuelLevel[[j]] <- df.avg.fuel.trip$fuelLevel[[j]] -
                                    7.88 + df.avg.fuel.trip$fuel_prev_trip[[j]]
            df.avg.fuel.trip$timestamp[[j]] <- df.avg.fuel.trip$timestamp[[j]] +
               5.784815 * 60 * as.numeric(df.avg.fuel.trip$fuel_prev_trip[[j]])
          }
          next
        }
    df.avg.fuel.trip$fuelLevel <- pmax(df.avg.fuel.trip$fuelLevel, 1)
    df.avg.fuel.trip$estimatedRange <- pmin(df.avg.fuel.trip$estimatedRange, 185)

And last but not least I have to calculate the time per parking instance (from start to stop) and the potential charge given (time(parkingInstance) * chargingRate, max 100). I haven't written this one yet but it will be similar to the ones above.

If you guys have any suggestions of how I could run this faster because I will have to scale and process much more than I have before I would greatly appreciate those!

\$\endgroup\$
  • \$\begingroup\$ If you really want an answer, you should break down your problem into small pieces, i.e., functions (which is what you should do anyway) and provide for each function a minimal example. \$\endgroup\$ – NoBackingDown Apr 19 '17 at 20:49
1
\$\begingroup\$

Right now you have nearly 300 rows per day for a given car. You'll be happier if you can identify a small number of Events that will break the day into trip segments, with fuel levels and lat/long at begin/end of each segment. Daily, each car will have just a small number of trip segment rows.

Trip segment types that come to mind are:

  1. journey: position switches from constant to mobile, and fuel decreases
  2. charge: position constant, fuel increasing
  3. park: position constant, fuel constant (may occur once a charge interval has completed)

Moving up a level of abstraction, you'll want to group nearby lat/longs into "locations", and compute hourly aggregate statistics like number of available cars, median fuel level of available cars, and number of available charging stations.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.