loveJx宝宝
问题描述
我有两个不同长度的数据框:
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())
