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

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

1. trips_df (subset shown), total entries = 1,048,568 1. 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.

• Is it recommended that I randomly sample n amount of entries from trips_df and use that? – Bn.F76 May 17 at 17:44
• Yes code would be helpful. How are you doing the timestamp lookup? Is the weather data hourly and complete? Is it all one location? Can you convert the weather data to hours-since-hour-0 so its a column of 0 to (n-1), do the same for trips and then its a row-index? Are your timestamps character and so you are spending all the time doing char-date conversion? Since you asked this 16 hours ago have you done this already now? – Spacedman May 18 at 8:09

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.

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.

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

if time2_ru in set_end:

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

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