获取按日期分组的和，按2个条件筛选的同一列

Question

有这样的数据集。你知道吗

    TransactionId   UserId transaction_date transaction_status amount_USD
0       3996625673  1298122       2015-08-11            CHARGED      10,96
1       5797849338  1125916       2015-08-11           DECLINED       14,7
2       9535361884  8009005       2015-08-11            CHARGED      10,61
3       8410989235  1123856       2015-07-29           DECLINED      10,96

如果是交易日，则需要按列金额\美元获得金额， 事务处理状态

transaction_date    CHARGED DECLINED
2015-07-29             0     10,96
2015-08-11           21,57   14,7

试着像这样做

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

Answer 1

首先使用^{}表示数字，然后使用^{}表示聚合sum，然后按^{}重新整形：

#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

可选择^{}，谢谢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

索引中最后一列使用^{}和^{}：

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