Select and merge pandas dataframe (dates)

Question

I have two dataframes and I need to subselect data from the first and merge with the second. Consider the first df1:

         ob_time air_temperature
0   2016-02-01 00:00            11.2
4   2016-02-01 01:00            11.1
8   2016-02-01 02:00            11.1
12  2016-02-01 03:00            10.8
16  2016-02-01 04:00            10.6
20  2016-02-01 05:00            10.8
24  2016-02-01 06:00            10.9
28  2016-02-01 07:00            10.7
32  2016-02-01 08:00            10.2
36  2016-02-01 09:00            10.9
44  2016-02-01 10:00              11
48  2016-02-01 11:00            11.5
52  2016-02-01 12:00            11.6
56  2016-02-01 13:00            12.7
60  2016-02-01 14:00            12.9
64  2016-02-01 15:00            12.6
68  2016-02-01 16:00              12
72  2016-02-01 17:00            11.1
76  2016-02-01 18:00            10.7
80  2016-02-01 19:00             9.5
84  2016-02-01 20:00             8.9
88  2016-02-01 21:00               9
92  2016-02-01 22:00             8.5
96  2016-02-01 23:00             8.7

705  2016-02-08 00:00               9
709  2016-02-08 01:00             8.9
713  2016-02-08 02:00             6.3
717  2016-02-08 03:00             6.6
721  2016-02-08 04:00             6.1
725  2016-02-08 05:00             5.3
729  2016-02-08 06:00             5.6
733  2016-02-08 07:00             5.1
737  2016-02-08 08:00             4.8
741  2016-02-08 09:00             6.3
750  2016-02-08 10:00               7
754  2016-02-08 11:00             7.4
758  2016-02-08 12:00             7.5
762  2016-02-08 13:00             7.9
766  2016-02-08 14:00             8.3
770  2016-02-08 15:00             7.5
774  2016-02-08 16:00             8.4
778  2016-02-08 17:00             7.7
782  2016-02-08 18:00             7.7
786  2016-02-08 19:00             7.5
790  2016-02-08 20:00               7
794  2016-02-08 21:00             6.5
798  2016-02-08 22:00               6
802  2016-02-08 23:00             5.6

and the second df2:

        summary  participant_id           response_date
156741     15.0              27 2016-02-01 11:38:22.816
157436     20.0              27 2016-02-08 13:19:10.496

I need to subselect data from the first df1, and put into the second df2 in the following way:

        summary  participant_id           response_date           ob_time  air_temperature
156741     15.0              27 2016-02-01 11:38:22.816  2016-02-01 11:00             11.5
157436     20.0              27 2016-02-08 13:19:10.496  2016-02-08 13:00              7.9

the idea is quite simple: merge two dataframes based on "response-date" and "ob_time", such that "air_temperature" (and "ob_date") are always followed by the "response_date".

I switched to pandas from matlab and now struggling with pythonian options. I am sure there are very simple pandas functions which can do it very easily. Any help would be highly appreciated.

Answer 1

You can use merge:

#if dtypes is not datetime
df1['ob_time'] = pd.to_datetime(df1.ob_time)
df2['response_date'] = pd.to_datetime(df2.response_date)

#replace minutes, seconds and microseconds to 0
#http://stackoverflow.com/a/28783971/2901002
df2['ob_time'] = df2.response_date.values.astype('<M8[h]')
print (df2)

        summary  participant_id           response_date             ob_time
156741     15.0              27 2016-02-01 11:38:22.816 2016-02-01 11:00:00
157436     20.0              27 2016-02-08 13:19:10.496 2016-02-08 13:00:00

print (pd.merge(df1,df2, on=['ob_time']))
              ob_time  air_temperature  summary  participant_id  \
0 2016-02-01 11:00:00             11.5     15.0              27   
1 2016-02-08 13:00:00              7.9     20.0              27   

            response_date  
0 2016-02-01 11:38:22.816  
1 2016-02-08 13:19:10.496  

Old method for replacing:

df2['ob_time'] = df2.response_date
                    .apply(lambda x: x.replace(minute=0, second=0, microsecond=0))
print (df2)