8
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Do you see the program eating all the available memory and using swap when running on a huge file? It feels like the problem might be that you keep the complete result in a single output dataframe..I think you should try dumping into CSV in chunks too. \$\endgroup\$ – alecxe Feb 15 '17 at 16:59
  • \$\begingroup\$ No, it stays at a pretty low memory usage and doesn't increase. It's not deviated from 48% by more than one or two percent. \$\endgroup\$ – Joshua Kidd Feb 15 '17 at 17:19
  • \$\begingroup\$ So what does python -m cProfile -s cumtime script_name.py | less give you for the 200MB file? What are the parts of the code that take the most time? \$\endgroup\$ – Graipher Feb 15 '17 at 23:57
1
\$\begingroup\$

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.

\$\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.