python - 获取按日期分组并按同一列按 2 个条件过滤的总和

Question

有类似的数据集。

    TransactionId   UserId transaction_date transaction_status amount_USD0       3996625673  1298122       2015-08-11            CHARGED      10,961       5797849338  1125916       2015-08-11           DECLINED       14,72       9535361884  8009005       2015-08-11            CHARGED      10,613       8410989235  1123856       2015-07-29           DECLINED      10,96

如果是 transaction_date，则需要按 amount_usd 列获取总和，交易状态

transaction_date    CHARGED DECLINED2015-07-29             0     10,962015-08-11           21,57   14,7

尝试以类似的方式进行

df[df['transaction_status']=='DECLINED']['amount_USD'].groupby('transaction_date').sum()

Answer 1

使用replace首先是数字，然后是 groupby聚合 sum，然后通过 unstack reshape :

#or use parameter decimal=',' to read_csv
df['amount_USD'] = df['amount_USD'].replace(',','.', regex=True).astype(float)

df = df.groupby(['transaction_date','transaction_status'])['amount_USD']
       .sum()
       .unstack(fill_value=0)
print (df)
transaction_status  CHARGED  DECLINED
transaction_date                     
2015-07-29             0.00     10.96
2015-08-11            21.57     14.70

替代pivot_table ，谢谢Bharath shetty :

df = df.pivot_table(index='transaction_date',
                    columns='transaction_status', 
                    values='amount_USD', 
                    aggfunc='sum', 
                    fill_value=0)
print (df)

transaction_status  CHARGED  DECLINED
transaction_date                     
2015-07-29             0.00     10.96
2015-08-11            21.57     14.70

索引使用的列的最后一个 reset_index和 rename_axis :

df = df.reset_index().rename_axis(None, axis=1)
print (df)
  transaction_date  CHARGED  DECLINED
0       2015-07-29     0.00     10.96
1       2015-08-11    21.57     14.70