Calculating distance and time between waypoints for large files

Question

This is very similar to other code I've posted, however this is designed for very large CSV files, for example 35GB.

The files typically look like this:

9e77d54918dd25c3f9d2e5354ec86666,0,2015-10-01T14:14:15.000Z,45.0988,7.5811,,
9e77d54918dd25c3f9d2e5354ec86666,1,2015-10-01T14:17:15.000Z,45.0967,7.5793,,
9e77d54918dd25c3f9d2e5354ec86666,2,2015-10-01T14:20:15.000Z,45.1012,7.6144,,
9e77d54918dd25c3f9d2e5354ec86666,3,2015-10-01T14:23:15.000Z,45.0883,7.6479,,
9e77d54918dd25c3f9d2e5354ec86666,4,2015-10-01T14:26:15.000Z,45.0774,7.6444,,
9e77d5jhg35hg345hghge5354ec84751,0,2015-10-04T22:36:32.000Z,48.0723,7.6442,,
ect...

The code I've written works out the sum of the distance between each of the waypoints, as well as the time taken and total number of waypoints. Here it is in entirety:

import pandas as pd
import numpy as np


EarthRadius = 6371 #km

def distance(group): #calculate distances using Equirectangular approximation
    lat = np.radians(group.Lat)
    lon = np.radians(group.Lon)
    endLon = lon.shift(-1)
    endLat = lat.shift(-1)
    x = (endLon - lon) * np.cos(0.5 * (endLat + lat))
    y = endLat - lat
    D = EarthRadius * np.sqrt(x**2 + y**2)
    return D.sum()

def timedelt(group):  #calculate length of trip
    a = np.around((pd.to_datetime(group['Date']).max() - pd.to_datetime(group['Date']).min()).seconds/60, decimals=2)
    return a

def tripStatistics(trip):#Helper function to output distance and number of waypoints
    return pd.Series({"TripID":chunk.iloc[0,0],"Distance": distance(trip),"No. of Waypoints": len(trip),"Time Taken(Mins)": timedelt(trip)})


def iterateIDs(file): #create chunks based on tripID
    csv_reader = pd.read_csv(file, iterator=True, chunksize=1, header=None, usecols=[0, 2, 3, 4],names=['TripID', 'Date', 'Lat', 'Lon'])
    first_chunk = csv_reader.get_chunk()
    id = first_chunk.iloc[0,0]
    chunk = pd.DataFrame(first_chunk)
    for l in csv_reader:
        if id == l.iloc[0,0]:
            id = l.iloc[0,0]
            chunk = chunk.append(l)
            continue
        id = l.iloc[0,0]
        yield chunk
        chunk = pd.DataFrame(l)
    yield chunk

output = pd.DataFrame(columns=['TripID','Distance','No. of Waypoints','Time Taken(Mins)'])
chunkIterate = iterateIDs("TripRecordsReportWaypoints.csv")

for chunk in chunkIterate:
    temp = tripStatistics(chunk)
    output = output.append(temp, ignore_index=True)


output['Waypoint Frequency(Secs)'] = np.around(60*output['Time Taken(Mins)']/output['No. of Waypoints'], decimals=2)
output.reset_index().to_csv('TripDetails.csv', sep=',', index=False)

I'm wondering if there is anything in my code that might slow down processing speeds. I set it running on the previously mentioned 35GB file and it's been going for over 24 hours now. It's the first time I've used generators so I hope I've not made some simple error.

As a bit of a comparison, I had written a previous piece of code that is similar but without splitting the CSV file into chunks with the iterateIDs generator function. It processed a 200MB file in 100 seconds. A linear extrapolation of that would mean it would take only 3 hours to process a 35GB file.

Answer 1

This line looks like trouble:

        chunk = chunk.append(l)

The docs say it will "append rows of other to the end of this frame, returning a new object." If the new object were to be small, containing a reference to the old object, then you would win, but here I think we're looking at quadratic copying behavior. Consider using .concat() instead, with copy=False. Or perhaps you can preallocate. Verify that the columns of l always match those of the existing chunk.

I recommend adding periodic progress reports that mention throughput and chunk size, to see where performance falls off a cliff. Do a timing run with the call to output.append commented out. Verifying that expected group sizes are passed into distance() would be helpful, too. And definitely use cProfile, as Graipher suggests.