Merge dataframes with different datetime frequencies without NaNs

Question

I'm trying to merge two dataframes with different datetime frequencies and also filling up missing values with duplicates.

Dataframe df1 with minute frequency:

    time
0   2017-06-01 00:00:00
1   2017-06-01 00:01:00
2   2017-06-01 00:02:00
3   2017-06-01 00:03:00
4   2017-06-01 00:04:00

Dataframe df2 with hourly frequency:

    time2               temp    hum
1   2017-06-01 00:00:00 13.5    90.0
2   2017-06-01 01:00:00 12.2    95.0
3   2017-06-01 02:00:00 11.7    96.0
4   2017-06-01 03:00:00 11.5    96.0
5   2017-06-01 04:00:00 11.1    97.0

So far i merged these dataframe but get NaNs:

m2o_merge = df1.merge(df2, left_on= 'time', right_on= 'time2', how='outer')
m2o_merge.head()

    time       time2                temp    hum
0   2017-06-01 00:00:00 2017-06-01  13.5    90.0
1   2017-06-01 00:01:00 NaT         NaN     NaN
2   2017-06-01 00:02:00 NaT         NaN     NaN
3   2017-06-01 00:03:00 NaT         NaN     NaN
4   2017-06-01 00:04:00 NaT         NaN     NaN

My desired dataframe should look like this (NaN filled up with hourly value df2):

    time                temp    hum
0   2017-06-01 00:00:00 13.5    90.0
1   2017-06-01 00:01:00 13.5    90.0
2   2017-06-01 00:02:00 13.5    90.0
3   2017-06-01 00:03:00 13.5    90.0
4   2017-06-01 00:04:00 13.5    90.0
...

So far i found this solution: merge series/dataframe with different time frequencies in python, but the Datetime column is not my index. Does anyone know how to get there ?

In your case you should be able to use fillna() with the ffill method to get your results. Then drop the time2 column. pandas.pydata.org/pandas-docs/stable/reference/api/… — Ben Pap

Douy789 Douy789 · Accepted Answer · 2019-05-30T17:12:15

As suggested by Ben Pap i did the following two Steps as a solution:

import pandas as pd 
data1 = {'time':pd.date_range('2017-06-01 00:00:00','2017-06-01 00:09:00', freq='T')} 
data2 = {'time2':pd.date_range('2017-06-01 00:00:00','2017-06-01 04:00:00', freq='H'), 'temp':[13.5,12.2,11.7,11.5,11.1], 'hum':[90.0,95.0,96.0,96.0,97.0]}

# Create DataFrame
df1 = pd.DataFrame(data1) 
df2 = pd.DataFrame(data2) 

m2o_merge = df1.merge(df2, left_on= 'time', right_on= 'time2', how='outer')
m2o_merge.head()

m2o_merge.fillna(method='ffill', inplace=True)
filled_df = m2o_merge.drop(['time2'], axis=1)
filled_df.head()

Merge dataframes with different datetime frequencies without NaNs

1 Answers