r - Speed up for loop on a large dataset -

i running analysis of citibike data , have created code works - extracts of trips of bikes started different station bike last stopped (that is, instances start.station.id different previous end.station.id, means bike moved truck. monthly datasets, however, large, summer months containing on 1 million individual trips (you can find them here: citibike data.

here snapshot of dataset:

  head(nov2015, n = 20)    tripduration          starttime           stoptime start.station.id                start.station.name 1          1110 11/1/2015 00:00:00 11/1/2015 00:18:31              537           lexington ave & e 24 st 2          1094 11/1/2015 00:00:01 11/1/2015 00:18:15              537           lexington ave & e 24 st 3           520 11/1/2015 00:00:05 11/1/2015 00:08:45              536                   1 ave & e 30 st 4           753 11/1/2015 00:00:15 11/1/2015 00:12:48              229                    great jones st 5           353 11/1/2015 00:00:22 11/1/2015 00:06:15              285                broadway & e 14 st 6          1285 11/1/2015 00:00:22 11/1/2015 00:21:48              268             howard st & centre st 7           477 11/1/2015 00:00:25 11/1/2015 00:08:23              379                   w 31 st & 7 ave 8           362 11/1/2015 00:00:28 11/1/2015 00:06:30              407              henry st & poplar st 9          2316 11/1/2015 00:00:37 11/1/2015 00:39:14              147          greenwich st & warren st 10          627 11/1/2015 00:00:42 11/1/2015 00:11:10              521                   8 ave & w 31 st 11         2304 11/1/2015 00:00:44 11/1/2015 00:39:08              147          greenwich st & warren st 12         1471 11/1/2015 00:01:04 11/1/2015 00:25:35              281 grand army plaza & central park s 13         1484 11/1/2015 00:01:36 11/1/2015 00:26:21              281 grand army plaza & central park s 14          284 11/1/2015 00:01:36 11/1/2015 00:06:20              247            perry st & bleecker st 15          886 11/1/2015 00:01:39 11/1/2015 00:16:25              492                   w 33 st & 7 ave 16          886 11/1/2015 00:01:42 11/1/2015 00:16:28              492                   w 33 st & 7 ave 17         1379 11/1/2015 00:01:44 11/1/2015 00:24:44              512                   w 29 st & 9 ave 18          179 11/1/2015 00:01:47 11/1/2015 00:04:47              319              fulton st & broadway 19          309 11/1/2015 00:01:51 11/1/2015 00:07:00              160           e 37 st & lexington ave 20          616 11/1/2015 00:02:08 11/1/2015 00:12:24              479                   9 ave & w 45 st    start.station.latitude start.station.longitude end.station.id         end.station.name 1                40.74026               -73.98409            531   forsyth st & broome st 2                40.74026               -73.98409            531   forsyth st & broome st 3                40.74144               -73.97536            498       broadway & w 32 st 4                40.72743               -73.99379            328  watts st & greenwich st 5                40.73455               -73.99074            151 cleveland pl & spring st 6                40.71911               -73.99973            476          e 31 st & 3 ave 7                40.74916               -73.99160            546     e 30 st & park ave s 8                40.70047               -73.99145            310      state st & smith st 9                40.71542               -74.01122            441          e 52 st & 2 ave 10               40.75097               -73.99444            285       broadway & e 14 st 11               40.71542               -74.01122            441          e 52 st & 2 ave 12               40.76440               -73.97371            367  e 53 st & lexington ave 13               40.76440               -73.97371            367  e 53 st & lexington ave 14               40.73535               -74.00483            453          w 22 st & 8 ave 15               40.75020               -73.99093            377         6 ave & canal st 16               40.75020               -73.99093            377         6 ave & canal st 17               40.75007               -73.99839            445       e 10 st & avenue 18               40.71107               -74.00945            264     maiden ln & pearl st 19               40.74824               -73.97831            362       broadway & w 37 st 20               40.76019               -73.99126            440          e 45 st & 3 ave    end.station.latitude end.station.longitude bikeid   usertype birth.year gender 1              40.71894             -73.99266  22545 subscriber       1981      2 2              40.71894             -73.99266  23959 subscriber       1980      1 3              40.74855             -73.98808  22251 subscriber       1988      1 4              40.72406             -74.00966  15869 subscriber       1981      1 5              40.72210             -73.99725  21645 subscriber       1987      1 6              40.74394             -73.97966  14788   customer         na      0 7              40.74445             -73.98304  21128 subscriber       1962      2 8              40.68927             -73.98913  21016 subscriber       1978      1 9              40.75601             -73.96742  24117 subscriber       1988      2 10             40.73455             -73.99074  17048 subscriber       1986      2 11             40.75601             -73.96742  18241 subscriber       1984      1 12             40.75828             -73.97069  24223   customer         na      0 13             40.75828             -73.97069  16779   customer         na      0 14             40.74475             -73.99915  17272 subscriber       1976      1 15             40.72244             -74.00566  15008 subscriber       1981      1 16             40.72244             -74.00566  23019 subscriber       1982      1 17             40.72741             -73.98142  23843 subscriber       1962      2 18             40.70706             -74.00732  22538 subscriber       1981      1 19             40.75173             -73.98754  22042 subscriber       1988      1 20             40.75255             -73.97283  22699 subscriber       1982      1  

and of code used extract "hidden" bike movements , put them coherent data.frame:

raw_data = read.csv("201511-citibike-tripdata.csv") unique_id = unique(raw_data$bikeid)   output <- data.frame("bikeid"= integer(0), "end.station.id"= integer(0), "start.station.id" = integer(0), "diff.time" = numeric(0),  "stoptime" = character(),"starttime" = character(), stringsasfactors=false)  (bikeid in unique_id) { onebike <- raw_data[ which(raw_data$bikeid== bikeid), ]  if(nrow(onebike) >=2 ){ for(i in 2:nrow(onebike )) { if(is.integer(onebike[i-1,"end.station.id"]) & is.integer(onebike[i,"start.station.id"]) & onebike[i-1,"end.station.id"] != onebike[i,"start.station.id"]){ diff_time <- as.double(difftime(strptime(onebike[i,"starttime"], "%m/%d/%y %h:%m:%s"),                                 strptime(onebike[i-1,"stoptime"], "%m/%d/%y %h:%m:%s")                                 ,units = "mins")) new_row <- c(bikeid, onebike[i-1,"end.station.id"], onebike[i,"start.station.id"], diff_time, as.character(onebike[i-1,"stoptime"]), as.character(onebike[i,"starttime"])) output[nrow(output) + 1,] = new_row } } } } 

because uses loop, extraction takes long time. there way speed process or rewrite code in way avoids using loop?

the output should stay same. namely:

 head(output)   bikeid end.station.id start.station.id        diff.time           stoptime          starttime 1  22545            520              529 24.8166666666667 11/2/2015 08:38:22 11/2/2015 09:03:11 2  22545            520              517 537.483333333333 11/2/2015 09:39:19 11/2/2015 18:36:48 3  22545           2004             3230 563.066666666667 11/2/2015 22:06:27 11/3/2015 07:29:31 4  22545            296             3236 471.783333333333 11/4/2015 23:40:29 11/5/2015 07:32:16 5  22545            520              449 43.4166666666667 11/9/2015 08:24:06 11/9/2015 09:07:31 6  22545            359              519 30.7166666666667 11/9/2015 09:14:46 11/9/2015 09:45:29 

this solution using data.table takes few minutes me.

uses shift function , adds previous row's data current row bikeid existing data.table.

then filter !is.na(end.station.id)&(end.station.id!=start.station.id) followed removing columns not needed , setting column order.

library(data.table) bikedata<-fread('201511-citibike-tripdata.csv') colnames(bikedata)<-make.names(colnames(bikedata))   bikedata[,c("end.station.id",             "diff.time",             "stoptime",             "starttime") :=            list(shift(end.station.id,1l,type="lag"),                 as.double(difftime(strptime(starttime,                                              "%m/%d/%y %h:%m:%s"),                                    strptime(shift(stoptime,1l,type="lag"),                                              "%m/%d/%y %h:%m:%s")                                    ,units = "mins")),                 as.character(shift(stoptime,1l,type="lag")),                 as.character(starttime)            ),          by=bikeid]  bikedatamoved<-bikedata[!is.na(end.station.id)&(end.station.id!=start.station.id)]  bikedatamoved[,               setdiff(colnames(bikedatamoved),c("bikeid","end.station.id",                                                 "start.station.id",                                                 "diff.time",                                                 "stoptime",                                                 "starttime")):=null]  setcolorder(bikedatamoved, c("bikeid",                               "end.station.id",                               "start.station.id",                                      "diff.time",                                         "stoptime",                                        "starttime"))