python - 使用月份日期时间合并多个数据框

Question

我有三个数据框。每个数据框都有日期作为列。我想使用日期列左连接三个。日期以“yyyy-mm-dd”的形式出现。我想仅使用“yyyy-mm”合并数据框。

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

尝试:

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

结果:

         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

Answer 1

使用Series.dt.to_period以月份为周期并按 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

替代方案:

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