Merging multiple dataframe using month datetime

Question

I have three dataframes. Each dataframe has date as column. I want to left join the three using date column. Date are present in the form 'yyyy-mm-dd'. I want to merge the dataframe using 'yyyy-mm' only.

df1

Date            X
31-05-2014  1
30-06-2014  2
31-07-2014  3
31-08-2014  4
30-09-2014  5
31-10-2014  6
30-11-2014  7
31-12-2014  8
31-01-2015  1
28-02-2015  3
31-03-2015  4
30-04-2015  5

df2

Date            Y
01-09-2014  1
01-10-2014  4
01-11-2014  6
01-12-2014  7
01-01-2015  2
01-02-2015  3
01-03-2015  6
01-04-2015  4
01-05-2015  3
01-06-2015  4
01-07-2015  5
01-08-2015  2

df3

Date            Z
01-07-2015  9
01-08-2015  2
01-09-2015  4
01-10-2015  1
01-11-2015  2
01-12-2015  3
01-01-2016  7
01-02-2016  4
01-03-2016  9
01-04-2016  2
01-05-2016  4
01-06-2016  1

Try:

df4 = pd.merge(df1,df2, how='left', on='Date')

Result:

         Date  X   Y
0  2014-05-31  1 NaN
1  2014-06-30  2 NaN
2  2014-07-31  3 NaN
3  2014-08-31  4 NaN
4  2014-09-30  5 NaN
5  2014-10-31  6 NaN
6  2014-11-30  7 NaN
7  2014-12-31  8 NaN
8  2015-01-31  1 NaN
9  2015-02-28  3 NaN
10 2015-03-31  4 NaN
11 2015-04-30  5 NaN

jezrael · Accepted Answer · 2019-06-12T11:43:18.367

Use Series.dt.to_period with months periods and merge by multiple DataFrames in list:

import functools

dfs = [df1, df2, df3]

dfs = [x.assign(per=x['Date'].dt.to_period('m')) for x in dfs]
df = functools.reduce(lambda left,right: pd.merge(left,right,on='per', how='left'), dfs)
print (df)
       Date_x  X      per     Date_y    Y Date   Z
0  2014-05-31  1  2014-05        NaT  NaN  NaT NaN
1  2014-06-30  2  2014-06        NaT  NaN  NaT NaN
2  2014-07-31  3  2014-07        NaT  NaN  NaT NaN
3  2014-08-31  4  2014-08        NaT  NaN  NaT NaN
4  2014-09-30  5  2014-09 2014-09-01  1.0  NaT NaN
5  2014-10-31  6  2014-10 2014-10-01  4.0  NaT NaN
6  2014-11-30  7  2014-11 2014-11-01  6.0  NaT NaN
7  2014-12-31  8  2014-12 2014-12-01  7.0  NaT NaN
8  2015-01-31  1  2015-01 2015-01-01  2.0  NaT NaN
9  2015-02-28  3  2015-02 2015-02-01  3.0  NaT NaN
10 2015-03-31  4  2015-03 2015-03-01  6.0  NaT NaN
11 2015-04-30  5  2015-04 2015-04-01  4.0  NaT NaN

Alternative:

df1['per'] = df1['Date'].dt.to_period('m')
df2['per'] = df2['Date'].dt.to_period('m')
df3['per'] = df3['Date'].dt.to_period('m')

df4 = pd.merge(df1,df2, how='left', on='per').merge(df3, how='left', on='per')
print (df4)

       Date_x  X      per     Date_y    Y Date   Z
0  2014-05-31  1  2014-05        NaT  NaN  NaT NaN
1  2014-06-30  2  2014-06        NaT  NaN  NaT NaN
2  2014-07-31  3  2014-07        NaT  NaN  NaT NaN
3  2014-08-31  4  2014-08        NaT  NaN  NaT NaN
4  2014-09-30  5  2014-09 2014-09-01  1.0  NaT NaN
5  2014-10-31  6  2014-10 2014-10-01  4.0  NaT NaN
6  2014-11-30  7  2014-11 2014-11-01  6.0  NaT NaN
7  2014-12-31  8  2014-12 2014-12-01  7.0  NaT NaN
8  2015-01-31  1  2015-01 2015-01-01  2.0  NaT NaN
9  2015-02-28  3  2015-02 2015-02-01  3.0  NaT NaN
10 2015-03-31  4  2015-03 2015-03-01  6.0  NaT NaN
11 2015-04-30  5  2015-04 2015-04-01  4.0  NaT NaN

Merging multiple dataframe using month datetime

1 Answers1