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!
