Python:如果DataFrames之间的其他值匹配，则对DataFrame中的值求和

2022-01-09 00:00:00 python pandas dataframe sum conditional-statements

问题描述

我有两个不同长度的数据框:

I have two dataframes of different length like those:

数据帧 A:

FirstName    LastName
Adam         Smith
John         Johnson

数据帧 B:

First        Last        Value
Adam         Smith       1.2
Adam         Smith       1.5
Adam         Smith       3.0
John         Johnson     2.5

想象一下，我想做的是在DataFrame A"中创建一个新列，将所有具有匹配姓氏的值相加，因此A"中的输出将是:

Imagine that what I want to do is to create a new column in "DataFrame A" summing all the values with matching last names, so the output in "A" would be:

FirstName    LastName    Sums
Adam         Smith       5.7
John         Johnson     2.5

如果我在 Excel 中，我会使用

If I were in Excel, I'd use 

=SUMIF(dfB!B:B, B2, dfB!C:C)

在 Python 中，我一直在尝试多种解决方案，但同时使用 np.where、df.sum()、删除索引等，但我迷路了.下面的代码返回ValueError:只能比较标记相同的系列对象"，但我认为它无论如何都写不正确.

In Python I've been trying multiple solutions but using both np.where, df.sum(), dropping indexes etc., but I'm lost. Below code is returning "ValueError: Can only compare identically-labeled Series objects", but I don't think it's written correctly anyways. 

df_a['Sums'] = df_a[df_a['LastName'] == df_b['Last']].sum()['Value']

非常感谢您的任何帮助.

Huge thanks in advance for any help.

解决方案

使用 布尔索引 与 Series.isin 进行过滤然后聚合sum:

df = (df_b[df_b['Last'].isin(df_a['LastName'])]
           .groupby(['First','Last'], as_index=False)['Value']
           .sum())

如果想同时匹配名字和姓氏:

If want match both, first and last name:

df = (df_b.merge(df_a, left_on=['First','Last'], right_on=['FirstName','LastName'])
           .groupby(['First','Last'], as_index=False)['Value']
           .sum())

相关文章