python - Pandas 使用日期作为索引加入/合并 2 个数据帧

Question

我有 2 个以日期为索引的大数据框。为了简单起见，我们假设它们看起来像这样(第一个数据框中特定日期的数据数量与第二个数据框中的数据数量不同):

df1:

      Date    X    Y
2000-01-01   x1   y1
2000-01-01   x2   y2
2000-01-02   x3   y3
2000-01-03   x4   y4
2000-01-03   x5   y5
2000-01-03   x6   y6

df2:

      Date  X_2  Y_2
2000-01-01   X1   Y1
2000-01-01   X2   Y2
2000-01-01   X3   Y3
2000-01-03   X4   Y4
2000-01-03   X5   Y5

输出应该是这样的(我只想合并两个数据框中都有日期的数据):

      Date    X    Y  X_2  Y_2
2000-01-01   x1   y1   X1   Y1
2000-01-01   x2   y2   X2   Y2
2000-01-01  NaN  NaN   X3   Y3
2000-01-03   x4   y4   X4   Y4
2000-01-03   x5   y5   X5   Y5
2000-01-03   x6   y6  NaN  NaN

我尝试了不同的代码组合，但我不断收到这样的重复数据:

      Date    X    Y  X_2  Y_2
2000-01-01   x1   y1   X1   Y1
2000-01-01   x1   y1   X2   Y2
2000-01-01   x1   y1   X3   Y3
2000-01-01   x2   y2   X1   Y1
2000-01-01   x2   y2   X2   Y2
2000-01-01   x2   y2   X3   Y3

我试过，例如result = pd.merge(df1,df2, how='inner', on='Date')要怎么做才能得到我想要的结果？

Answer 1

当按日期分组时，使用cumcount 对每组中的项目进行编号:

In [107]: df1['count'] = df1.groupby('Date').cumcount()

In [108]: df1
Out[108]: 
         Date   X   Y  count
0  2000-01-01  x1  y1      0
1  2000-01-01  x2  y2      1
2  2000-01-02  x3  y3      0
3  2000-01-03  x4  y4      0
4  2000-01-03  x5  y5      1
5  2000-01-03  x6  y6      2

In [109]: df2['count'] = df2.groupby('Date').cumcount()

In [110]: df2
Out[110]: 
         Date X_2 Y_2  count
0  2000-01-01  X1  Y1      0
1  2000-01-01  X2  Y2      1
2  2000-01-01  X3  Y3      2
3  2000-01-03  X4  Y4      0
4  2000-01-03  X5  Y5      1

通过添加 count 列，您现在可以在 Date 和 count 上合并，这让您接近您想要的结果:

In [111]: pd.merge(df1, df2, on=['Date', 'count'], how='outer')
Out[111]: 
         Date    X    Y  count  X_2  Y_2
0  2000-01-01   x1   y1      0   X1   Y1
1  2000-01-01   x2   y2      1   X2   Y2
2  2000-01-02   x3   y3      0  NaN  NaN
3  2000-01-03   x4   y4      0   X4   Y4
4  2000-01-03   x5   y5      1   X5   Y5
5  2000-01-03   x6   y6      2  NaN  NaN
6  2000-01-01  NaN  NaN      2   X3   Y3

您希望删除的行可以描述为计数等于 0 且 X 或 X_2 等于 NaN 的那些行。因此，您可以像这样使用 bool 掩码删除这些行:

mask = (result['count'] == 0) & pd.isnull(result).any(axis=1)
result = result.loc[~mask]

---

import pandas as pd

df1 = pd.DataFrame({'Date': ['2000-01-01',
  '2000-01-01',
  '2000-01-02',
  '2000-01-03',
  '2000-01-03',
  '2000-01-03'],
 'X': ['x1', 'x2', 'x3', 'x4', 'x5', 'x6'],
 'Y': ['y1', 'y2', 'y3', 'y4', 'y5', 'y6']})

df2 = pd.DataFrame({'Date': ['2000-01-01',
  '2000-01-01',
  '2000-01-01',
  '2000-01-03',
  '2000-01-03'],
 'X_2': ['X1', 'X2', 'X3', 'X4', 'X5'],
 'Y_2': ['Y1', 'Y2', 'Y3', 'Y4', 'Y5']})


df1['count'] = df1.groupby('Date').cumcount()
df2['count'] = df2.groupby('Date').cumcount()
result = pd.merge(df1, df2, on=['Date', 'count'], how='outer')
mask = (result['count'] == 0) & pd.isnull(result).any(axis=1)
result = result.loc[~mask]
result = result.drop('count', axis=1)

产量

         Date    X    Y  count  X_2  Y_2
0  2000-01-01   x1   y1      0   X1   Y1
1  2000-01-01   x2   y2      1   X2   Y2
3  2000-01-03   x4   y4      0   X4   Y4
4  2000-01-03   x5   y5      1   X5   Y5
5  2000-01-03   x6   y6      2  NaN  NaN
6  2000-01-01  NaN  NaN      2   X3   Y3

---

另一种将合并限制为仅对两者共有的日期的方法df1 和 df2 会找到 df1['Date'] 和df2['Date']，然后将pd.merge应用于df1和df2的子DataFrames其中仅包含那些日期:

import numpy as np
dates = np.intersect1d(df1['Date'], df2['Date'])
mask1 = df1['Date'].isin(dates)
mask2 = df2['Date'].isin(dates)
result = pd.merge(df1.loc[mask1], df2.loc[mask2], on=['Date', 'count'], how='outer')