我有一个这样的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
在将其读入pandas数据框df之后,我想将其聚合为两个步骤:
步骤1:
首先,我将金额相加:
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)
但没用。 最佳答案
我们这里可以用点数学
cols = ['amount']
# Divide the amount by non null fields
df['new'] = df['amount']/df.drop(cols,1).notnull().sum(1)
#Set the index as new by droping amount column, unstack and drop the nans.
x = df.drop(cols,1).set_index('new').unstack().dropna()
#Create dataframe just from amount and the clients
ndf = pd.DataFrame({'amount':x.index.droplevel(0).values,'clients':x.values})
#Groupby client and get the sum
ndf.groupby('clients').sum()
输出:
数量
客户
综合16.360417
公司编号:69.697501
公司3 85700000
综合4 36.666667
综合5 44.15650
关于python - 跨行对Pandas数据框进行分组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46995997/