What is the correct procedure when “joining” data takes ~6 hours?

Question

I am dealing with bike-share data. I have 2 DataFrames:

1. trips_df (subset shown), total entries = 1,048,568

2. weather_df (subset shown), total entries = 2,654

I am trying to calculate and attach the total_precipitation for each trip, as a column. I do this by looking up the start_timestamp and end_timestamp datetime for each trip from trips_df, in the weather_df, and summing the precipitation_amount within those times, then attaching that value back in the trips_df under the new column. I can attach the code if it's helpful.

I ran the code on a subset of 65 entries and it took ~1.3s. (CPU times: user 1.27 s, sys: 8.77 ms, total: 1.28 s, Wall time: 1.28 s). Extrapolating that performance to my entire data, it would take (1.3 * 1048568)/65 = 20971.36seconds or 5.8hours.

What am I supposed to do in this situation? For context, this is a Kaggle style data science project so I'll have to do further data wrangling, and data extraction then apply a predictive model.

Answer 1

There is no ideal way of doing that type of join efficiently in Pandas.

One possible option is using numpy.piecewise to define matches based on the timestamps from each dataframe. An example of apply numpy.piecewise can be found here.

Answer 2

Maybe you could check if a timestamp from the weather data is within your trip timestamps, then change the weather timestamps to match the trip timestamp. After that merge both data frames on the timestamp columns. Then use group_by().sum() to aggregate on the timestamps and sum up the precipitation column. Now take the summed up precipitation column and append it to your initial trip dataframe.

Answer 3

As hinted at by @spacedman I was doing the timestamp lookups wrong.

I was forming a set(weather_data['start_precipitation_datetime']) and set(weather_data['end_precipitation_datetime']) for every lookup. Here is the working (more efficient) code.

CPU times: user 118 ms, sys: 4.23 ms, total: 122 ms Wall time: 124 ms for 65 rows. Roughly 35minutes instead of 6hours for the entire DataFrame.

def sum_precipitation(datetime1, datetime2, weather_data, start_dates_set, end_dates_set):

    time1_rd = datetime1.replace(minute=0, second=0)
    time2_ru = datetime2.replace(minute=0, second=0) + dt.timedelta(hours=1)

    if time1_rd in set_start:

        start_idx = weather_data.start_precipitation_datetime[
            weather_data.start_precipitation_datetime==time1_rd].index[0]

        if time2_ru in set_end:

            end_idx = weather_data.end_precipitation_datetime[
                weather_data.end_precipitation_datetime==time2_ru].index[0]

            precipitation_sum = weather_data.iloc[start_idx:end_idx+1, 7].sum()

        else: precipitation_sum = 0
    else: precipitation_sum = 0

    return round(precipitation_sum, 3)

def join_weather_to_trips(trips_data, weather_data):

    trips_weather_df = trips_data.copy()

    start_hr_set = set(weather_data['start_precipitation_datetime'])
    end_hr_set = set(weather_data['end_precipitation_datetime'])

    fn = lambda row : sum_precipitation(row.start_timestamp, row.end_timestamp, weather_data, 
                                        start_hr_set, end_hr_set)
    col = trips_data.apply(fn, axis=1)
    trips_weather_df = trips_weather_df.assign(total_precipitation=col.values)

    return trips_weather_df