I would like make a new data frame by grouping the ID and summing the value in 'Payment' column based on the condition in 'Status' column. If status is Success then sum all the payments which are success into 'sum_payment_success' column otherwise sum into 'sum_Payment_fail' column.
Like this
data frame: df_transaction
| ID | Payment | Status |
|---|---|---|
| 0001 | 20.10 | Success |
| 0001 | 21.20 | Reject |
| 0002 | 18.20 | Cancel |
| 0002 | 17.20 | Success |
| 0002 | 16.30 | Cancel |
| 0003 | 10.00 | Success |
| 0003 | 20.00 | Reject |
| 0003 | 10.00 | Cancel |
into
| ID | Sum_Payment_success | Sum_Payment_fail |
|---|---|---|
| 0001 | 41.30 | 0 |
| 0002 | 17.20 | 34.50 |
| 0003 | 10.00 | 30.00 |
Thanks in advance
