CODEWITHSUNDEEP
pythonpandas
Reggie
Reggie

Reputation: 79

Joining Pandas dataframe on hour range

I have two Pandas dataframes, one with weather information, given hourly, and one with bus arrival data, given approximately every minute or so.

I want to join the dataframes so bus data is associated with weather for that hour e.g. bus data between 1pm and 2pm on 01/06/2012 will be associated with corresponding weather data.

e.g Bus Data

DateTime                LineID    Longitude    Latitude
2013-01-01 00:00:27     4.0       -6.279000    53.416683
2013-01-01 00:01:33     4.0       -6.279321    53.416697
2013-01-01 00:02:44     4.0       -6.279435    53.416492
2013-01-01 00:03:28     4.0       -6.279553    53.416596
2013-01-01 00:04:31     4.0       -6.279868    53.416530

e.g Weather Data

DateTime                Temperature    Rainfall 
2013-01-01 00:00:00     10             0    
2013-01-01 01:00:00     10             0    
2013-01-01 02:00:00     11             0
2013-01-01 03:00:00     11             0.1
2013-01-01 04:00:00     11             0.2

Is there a way to do this? Any help is greatly appreciated.

Upvotes: 1

Views: 146

Answers (1)

Scott Boston
Scott Boston

Reputation: 153510

IIUC, I think you need pd.merge_asof:

pd.merge_asof(bus_df,weather_df, on='DateTime')

Output:

             DateTime  LineID  Longitude   Latitude  Temperature  Rainfall
0 2013-01-01 00:00:27     4.0  -6.279000  53.416683           10       0.0
1 2013-01-01 00:01:33     4.0  -6.279321  53.416697           10       0.0
2 2013-01-01 00:02:44     4.0  -6.279435  53.416492           10       0.0
3 2013-01-01 00:03:28     4.0  -6.279553  53.416596           10       0.0
4 2013-01-01 00:04:31     4.0  -6.279868  53.416530           10       0.0

Upvotes: 3

Related Questions