时间戳

首页 > 解决方案 > 熊猫与条件聚合合并

python - 熊猫与条件聚合合并

问题描述

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

这是一个例子：

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

谢谢你的帮助。

标签： pythonpandasmerge

解决方案

好的，我终于明白了！

按代码和 SG 合并（LEFT JOIN）

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']

.prod()我们在这里将无限分配给 True 值只是为了避免在使用 NaN执行函数时出现 Pandas 错误

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()功能实现这一点，但是，如果您的 DataFrame 变得更大，这会减慢您的速度。

希望我能帮上忙！我花了两个小时来思考这段代码！

编辑：

正如@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()

如果需要，您可以将“索引”重命名为其他名称，以使其更加明确。

希望我能帮上忙！

推荐阅读