Pandas分组并在不同类型之间使用数字

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

首先将sell值强制转换为负数，然后使用groupby.sum：

df['amount'] = np.where(df['order_type'].eq('Sell'), -df['amount'], df['amount'])

df.groupby('client', as_index=False)['amount'].sum()

---

  client  amount
0  Aaron     100
1   Jeff     200
2   John       0

Answer 2

只需将您的订单类型映射到一个标志，有很多方法可以做到这一点，但在我看来，最容易阅读的方法是：

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