What is the correct procedure when "joining" data takes ~6 hours? I am dealing with bike-share data. I have 2 DataFrames:


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





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