Pandas合并两个数据帧，对相似的列求和，只保留具有匹配键的行（内部联接）

Question

我被困在pandasconcat和merge函数之间，努力实现两者的最佳效果。我需要在name和date上对行进行内部联接，对公共列A和B求和，并保留category中的值（如果需要，我可以将其添加到联接中，它们匹配）

示例-

df1

| name | date     | A | B | category |
|------|----------|---|---|----------|
| W    | 1/1/2020 | 1 | 1 | home     |
| W    | 1/2/2020 | 1 | 1 | home     |
| Y    | 1/3/2020 | 1 | 1 | garden   |
| Y    | 1/4/2020 | 1 | 1 | garden   |

df2

| name | date     | A | B | category |
|------|----------|---|---|----------|
| W    | 1/1/2020 | 2 | 2 | home     |
| W    | 1/2/2020 | 1 | 1 | home     |
| Y    | 1/3/2020 | 1 | 1 | garden   |
| Y    | 1/5/2020 | 1 | 1 | garden   |

期望结果-

| name | date     | A | B | category |
|------|----------|---|---|----------|
| W    | 1/1/2020 | 3 | 3 | home     |
| W    | 1/2/2020 | 2 | 2 | home     |
| Y    | 1/3/2020 | 2 | 2 | garden   |

我发现merge将连接行，但复制未连接的任何列，而不是对它们求和

concat将对行求和，但不进行内部联接，因此我只从一个数据帧或另一个数据帧获取数据行。我试过pd.concat([df_1, df_2], join='inner'，但“内部”并不能满足我的需要

Answer 1

这个怎么样：

dff = df1
    .set_index(['name', 'date', 'category'])
    .add(df2.set_index(['name', 'date', 'category'])
    .reset_index()
    .dropna()
    .reindex(columns=df1.columns)

dff

输出：

    name    date    A   B   category
 0  W   1/1/2020    3   3   home
 1  W   1/2/2020    2   2   home
 2  Y   1/3/2020    2   2   garden

这就是你想要的

Answer 2

我会在name、date和category上使用groupby，并使用sum聚合（在A和B上求和）。但这会提供额外的列，因为日期为1/4/2020和1/5/2020的行不会消失，而是总和为1

代码如下：

import pandas as pd

df = pd.DataFrame({'name': ['W', 'W', 'Y', 'Y'], 
                   'date': ['1/1/2020', '1/2/2020', '1/3/2020', '1/4/2020 '],
                   'A': [1, 1, 1, 1],
                   'B': [1, 1, 1, 1],
                   'category': ['home', 'home', 'garden', 'garden']})

df2 = pd.DataFrame({'name': ['W', 'W', 'Y', 'Y'], 
                   'date': ['1/1/2020', '1/2/2020', '1/3/2020', '1/5/2020 '],
                   'A': [2, 1, 1, 1],
                   'B': [2, 1, 1, 1],
                   'category': ['home', 'home', 'garden', 'garden']})

df3 = pd.concat([df, df2]).groupby(by=['name', 'date', 'category']).sum()

这将为您提供：

                         A  B
name date      category      
W    1/1/2020  home      3  3
     1/2/2020  home      2  2
Y    1/3/2020  garden    2  2
     1/4/2020  garden    1  1
     1/5/2020  garden    1  1

然后，如果不想看到sum=1的行，可以对A或/和B的值进行筛选

希望这有帮助

Answer 3

第一个“幼稚”解决方案：

d3 = pd.merge(d1,d2, left_on = ["name","date","category"], right_on = ["name","date","category"])
d3 = d3.assign(A = d3.A_x + d3.A_y, B = d3.B_x + d3.B_y, ).drop(columns=["A_y","A_x","B_y","B_x"])

更好的解决方案，无需手动添加列：

key = ["name","date","category"]
d3 = pd.merge(d1,d2, left_on = key, right_on = key)[key]
d4 = pd.concat([d1, d2]).groupby(by=key).sum()
d5 = pd.merge(d3,d4,right_on = key, left_on = key)

结果:

     name        date    category  A  B
0   W       1/1/2020    home       3  3
1   W       1/2/2020    home       2  2
2   Y       1/3/2020    garden     2  2

使用pd.merge可以指定组合键["name","date","category"]连接2个数据帧