我有这样一个csv:
client1,client2,client3,client4,client5,client6,amount
,,,Comp1,,,4.475000
,,,Comp2,,,16.305584
,,,Comp3,,,4.050000
Comp2,Comp1,,Comp4,,,21.000000
,,,Comp4,,,30.000000
,Comp1,,Comp2,,,5.137500
,,,Comp3,,,52.650000
,,,Comp1,,,2.650000
Comp3,,,Comp3,,,29.000000
Comp5,,,Comp2,,,20.809000
Comp5,,,Comp2,,,15.100000
Comp5,,,Comp2,,,52.404000
在将其读入数据帧df之后,我想分两步进行聚合:
第一步:
首先,我将金额相加:
client1 client2 client3 client4 client5 client6 amount
Comp1 7.125000
Comp2 16.305584
Comp3 56.700000
Comp4 30.000000
Comp1 Comp2 5.137500
Comp2 Comp1 Comp4 21.000000
Comp3 Comp3 29.000000
Comp5 Comp2 88.313000
然后,我想按每个客户机名称进行聚合,这样,如果多个客户机像第5组一样参与,那么5.1375必须在Comp1和Comp2之间平分。这样尝试:
df.groupby(['client1','client2','client3','client4','client5','client6']).apply(lambda x: x['amount'].sum()/len(x) if x.any().nunique()>=1 else x['amount'].sum())
client1 client2 client3 client4 client5 client6 0
0 Comp1 3.562500
1 Comp2 16.305584
2 Comp3 28.350000
3 Comp4 30.000000
4 Comp1 Comp2 5.137500
5 Comp2 Comp1 Comp4 21.000000
6 Comp3 Comp3 29.000000
7 Comp5 Comp2 29.437667
预期输出为:
Client Amount
Comp1 4.475+21/3+5.1375/2+2.65 = 16.69375
Comp2 16.305584+21/3+20.809/2+15.10/2+52.404/2 = 67.462084
Comp3 4.05+52.65+29 = 85.7
Comp4 21/3+30 = 37
Comp5 20.809/2+15.10/2+52.404/2 = 44.1565
我试过使用sum(axis=0)
,但没有用。你知道吗
我们可以在这里用一点数学知识
输出:
我会这样组织:
相关问题 更多 >
编程相关推荐