我正在学习使用 python 和 pandas，我想知道如何根据两列中的单元格值组合不同行中的内容。 在这种情况下，我想合并来自同一部门和同一 ID 的分数。
这是我拥有的 DF 的简化版本:
id department name scoreA
abc sales eric 2
abc sales jack 3
abc marketing sofia 6
abc marketing anna 7
zzz sales jack 1
zzz sales eric 8
zzz marketing sofia 11
zzz marketing anna 1
这是我想要的 DF:
id department totalScoreA
abc sales 5
abc marketing 13
zzz sales 9
zzz marketing 12
我还有一个跟进问题。如果有两列用于计数，我想要这些计数的平均值，但在对这些值进行平均之前，我想将 scoreB 乘以 2。像这样:
id department name scoreA scoreB
abc sales eric 2 10
abc sales jack 3 6
abc marketing sofia 6 8
abc marketing anna 7 10
zzz sales eric 8 10
zzz sales jack 2 10
zzz marketing sofia 11 4
zzz marketing anna 1 10
这是我想要的 DF:
id department totalScoreA AverageScore((A+B*2)/2)
abc sales 5 18.5
abc marketing 13 24.5
zzz sales 10 25
zzz marketing 12 20
更新:
嘿，非常感谢@jezrael 的回答!第一个工作正常!
但是，对于第二个问题的定义，我可能有点过于含糊了。我想要的是为每个部门的每个组获取所有 scoreB*2 的 ScoreA 的“组合”平均值。我举一个带有值的例子来阐明这一点:
来自这里:
id department name scoreA scoreB
zzz marketing sofia 5 4
zzz marketing anna 2
为此:
meanAB 是(5+2+4*2)/3(数字 3 来自值的计数)。那么我将如何计算那个，因为我无法做到这一点，即使在您以前的解决方案的帮助下也是如此:/
id department meanA meanB meanAB
zzz marketing 3.5 4 5
请您参考如下方法:
首先是聚合 sum 与 DataFrame.groupby :
df1 = df.groupby(['id', 'department'], as_index=False, sort=False)['scoreA'].sum()
print (df1)
id department scoreA
0 abc sales 5
1 abc marketing 13
2 zzz sales 10
3 zzz marketing 12
第二个是第一个多列 scoreB，添加了 scoreA 并使用了 GroupBy.agg使用聚合函数字典，此处为 sum 和 mean:
df2 = (df.assign(scoreB = df['scoreB'] * 2 + df['scoreA'])
.groupby(['id', 'department'], as_index=False, sort=False)
.agg({'scoreA':'sum', 'scoreB':'mean'}))
print (df2)
id department scoreA scoreB
0 abc sales 5 18.5
1 abc marketing 13 24.5
2 zzz sales 10 25.0
3 zzz marketing 12 20.0
编辑:
print (df)
id department name scoreA scoreB
0 abc sales eric 2 10.0
1 abc sales jack 3 6.0
2 abc marketing sofia 6 8.0
3 abc marketing anna 7 10.0
4 abc marketing eric 8 10.0 <-changed data
5 zzz sales jack 2 10.0
6 zzz marketing sofia 5 4.0 <-changed data
7 zzz marketing anna 2 NaN <-changed data
创建新列 Count by DataFrame.count用于获取排除 NaN 的值数量的函数，然后聚合 sum 并除以 mean:
df2 = (df.assign(scoreB = df['scoreB'].mul(2).add(df['scoreA'], fill_value=0),
Count = df[['scoreA','scoreB']].count(1))
.groupby(['id', 'department'], as_index=False, sort=False)
.sum())
print (df2)
id department scoreA scoreB Count
0 abc sales 5 37.0 4
1 abc marketing 21 77.0 6
2 zzz sales 2 22.0 2
3 zzz marketing 7 15.0 3
df2['scoreB'] /= df2.pop('Count')
print (df2)
id department scoreA scoreB
0 abc sales 5 9.250000
1 abc marketing 21 12.833333
2 zzz sales 2 11.000000
3 zzz marketing 7 5.000000
详细信息:
print (df.assign(scoreB = df['scoreB'].mul(2).add(df['scoreA'], fill_value=0),
Count = df[['scoreA','scoreB']].count(1)))
id department name scoreA scoreB Count
0 abc sales eric 2 22.0 2
1 abc sales jack 3 15.0 2
2 abc marketing sofia 6 22.0 2
3 abc marketing anna 7 27.0 2
4 abc marketing eric 8 28.0 2
5 zzz sales jack 2 22.0 2
6 zzz marketing sofia 5 13.0 2
7 zzz marketing anna 2 2.0 1
