python - pandas groupby 并在不同类型之间使用数字

Question

假设我有一个像这样的 df:

client   order_type    amount
John     Buy           100
John     Sell          100
Jeff     Buy           100
Jeff     Buy           100
Aaron    Buy           100
Aaron    Sell          100
Aaron    Buy           100

如果我这样做:

df.groupby(['client','order_type'])['amount'].sum()

我会得到类似的东西:

John    Buy   100
        Sell  100
Jeff    Buy   100
        Sell  100
Aaron   Buy   200
        Sell  100

如何在新数据框中获取诸如“购买 - 销售”列之类的内容:

Name      NetBuy
John      0
Jeff      200
Aaron     100

Answer 1

只需将您的 order_type 映射到一个符号，有很多方法可以做到这一点，但我认为最容易阅读的是:

df['sign'] = [1 if x == 'Buy' else -1 for x in df.order_type]
df['amount_adj'] = df.sign*df.amount
df.groupby(['client'])['amount_adj'].sum()

输出:

client
Aaron    100
Jeff     200
John       0

使用单行和更快的 np.where 得到相同的结果:

df = df.assign(amount=np.where(df.order_type.eq('Sell'), 
          df.amount*-1, df.amount)).groupby(['client'])['amount'].sum()

输出:

client
Aaron    100
Jeff     200
John       0