Merging data using dates

Question

Index
_d.index incident data	DatetimeIndex(['2011-01-01 00:03:43', '2011-01-01 00:03:55', '2011-01-01 00:05:03', '2011-01-01 00:04:46', '2011-01-01 00:06:07', '2011-01-01 00:06:24', '2011-01-01 00:06:48', '2011-01-01 00:08:26', '2011-01-01 00:08:56', '2011-01-01 00:09:33', ... '2016-12-31 23:34:26', '2016-12-31 23:40:22', '2016-12-31 23:47:01', '2016-12-31 23:48:07', '2016-12-31 23:52:56', '2016-12-31 23:52:49', '2016-12-31 23:55:44', '2016-12-31 23:56:01', '2016-12-31 23:59:18', '2016-12-31 23:59:52'], dtype='datetime64[ns]', name='incident_date_time', length=720370, freq=None)
_dw.index weather data	DatetimeIndex(['2011-01-01 00:00:00', '2011-01-01 01:00:00', '2011-01-01 02:00:00', '2011-01-01 02:41:00', '2011-01-01 03:00:00', '2011-01-01 04:00:00', '2011-01-01 07:00:00', '2011-01-01 08:00:00', '2011-01-01 09:00:00', '2011-01-01 10:00:00', ... '2016-12-31 17:00:00', '2016-12-31 18:00:00', '2016-12-31 19:00:00', '2016-12-31 20:00:00', '2016-12-31 21:00:00', '2016-12-31 21:19:00', '2016-12-31 22:00:00', '2016-12-31 22:10:00', '2016-12-31 22:50:00', '2016-12-31 23:00:00'], dtype='datetime64[ns]', name='date', length=62021, freq=None)

 

For the TFS project, I would like to join the weather to the data by date and hour. for example the weather at 2011-01-01 01:00:00 to all the incidents for that hour 2011-01-01 01:00:00 and 2011-01-01 01:10:00 etc. One weather report to many incidents.

 

Answer 1

One way is creating a new column in the incident dataset and round the datetime to the same hour. You can use pandas.DatetimeIndex.round for this purpose. After having this extra column, you can create other columns in the incident dataset and wrtie some queries to find the weather information of that hour and copy in front of each incident record in those columns.

Comments

Perfect that works.....

Thanks