合并条件聚合

Question

我想基于组合键合并两个数据帧。考虑到键，第二个数据帧具有重复的行。请注意，键在第一个数据帧中也不是唯一的，因为实际数据中实际上还有许多列。我需要在第二个数据帧上与聚合（产品）值合并，但在日期上有一个条件。要聚合的行的日期应低于从第一个数据帧开始的行的日期

以下是一个例子：

df1 = pd.DataFrame({
    'Code': ['Code1', 'Code1', 'Code1', 'Code2', 'Code3', 'Code4'],
    'SG': ['SG1', 'SG1', 'SG1', 'SG2', 'SG3', 'SG3'],
    'Date':
    ['2020-02-01', '2020-02-01', '2020-03-01', '2020-01-01', '2020-02-01', '2020-02-01']
})



print(df1)
    Code   SG        Date
0  Code1  SG1  2020-02-01
1  Code1  SG1  2020-02-01
2  Code1  SG1  2020-03-01
3  Code2  SG2  2020-01-01
4  Code3  SG3  2020-02-01
5  Code4  SG3  2020-02-01

df2 = pd.DataFrame({
    'Code': ['Code1', 'Code1', 'Code2', 'Code3'],
    'SG': ['SG1', 'SG1', 'SG2', 'SG3'],
    'Date': ["2019-01-01", "2020-02-25", "2020-01-13", "2020-01-25"],
    'Coef': [0.5, 0.7, 0.3, 0.3]
})

print(df2)

    Code   SG        Date  Coef
0  Code1  SG1  2019-01-01   0.5
1  Code1  SG1  2020-02-25   0.7
2  Code2  SG2  2020-01-13   0.3
3  Code3  SG3  2020-01-25   0.3

我想要以下结果：第二行聚合了coef 0.5x0.7=0.35，因为对应键的所有df2.Date都低于df1.Date

    Code   SG        Date  Coef
0  Code1  SG1  2020-02-01  0.50
1  Code1  SG1  2020-02-01  0.50
2  Code1  SG1  2020-03-01  0.35
3  Code2  SG2  2020-01-01   NaN
4  Code3  SG3  2020-02-01  0.30
5  Code4  SG3  2020-02-01   NaN

谢谢你的帮助

Answer 1

好了，我终于明白了

按代码和SG合并（左连接）

df_group = pd.merge(df1,df2, on=['Code','SG'], how='left', suffixes=('','_result'))

为较低日期创建筛选器

df_group['lower_date_mask'] = df_group['Date_result'] <= df_group['Date']

使用NAN过滤Coef列

df_group.loc[df_group['lower_date_mask'] == False,'lower_date_mask'] = np.nan
df_group['Coef'] = df_group['Coef'] * df_group['lower_date_mask']

我们在这里将无限赋值给真值，只是为了避免在使用NaNs执行.prod()函数时出现错误

df_group.loc[df_group['lower_date_mask'] == 1.0,'lower_date_mask'] = np.inf

关于nan的聚合函数的Github问题：https://github.com/pandas-dev/pandas/issues/20824

按.prod（）聚合

df_group = df_group.groupby(['Code','SG','Date']).prod()

创建最终数据帧

df_group.reset_index(inplace = True)
df_group.loc[df_group['lower_date_mask'] == 1.0,'Coef'] = np.nan
df_group.drop(columns = ['lower_date_mask'],inplace = True)

最终产量

    Code    SG  Date    Coef
    0   Code1   SG1 2020-02-01  0.50
    1   Code1   SG1 2020-03-01  0.35
    2   Code2   SG2 2020-01-01  NaN
    3   Code3   SG3 2020-02-01  0.30
    4   Code4   SG3 2020-02-01  NaN

值得一提的是，您可以通过.apply()函数实现这一点，但是，如果您的数据帧变大，这将降低您的速度

希望我能帮忙！我花了两个小时才把这段代码想清楚

编辑：

正如@codesensei所提到的，他的数据库中还有其他列使得组合['Code','SG','Date']不是唯一的。在这种情况下，有两种可能的解决方法。首先，如果df1或df2中有其他列使组合唯一，只需将它们添加到分组中，如下所示：

df_group = df_group.groupby(['Code','SG','Date','column_of_interest']).prod()

其次，如果更容易通过某种ID（比如df1的索引）使组合唯一，那么可以执行以下操作：

df1.reset_index(inplace = True)
# merge dataframes and follow the other steps as stated earlier in this answer
df_group = df_group.groupby(['Code','SG','Date','index']).prod()

如果需要，可以将“索引”重命名为其他名称，只是为了使其更显式

希望我能帮忙