Pandas Pivot Table - Adding Subtotals to Multiindex Table

user_6370359 0
Viewed 89

I have a table of data structured as it follows:

Name Card Payment ID Amount
John Doe t077 7312637 54
John Doe t077 1323131 34
Jane Doe s044 1231321 13
John Doe j544 4634564 53

The output I want to achieve is to have a pivot table with a similar format:

Name Number of Transactions Sum
John Doe 3 141
--- t077 2 88
--- j544 1 53
Jane Doe 1 13
--- s044 1 13

Please keep in mind that:

  • Payment ID uniquely identifies the transaction (every line in the table)
  • Every Name can have one or multiple transactions with one or multiple cards

I tried using pandas pivot_table, however I cannot find a way to structure the data as I want (including subtotals per Name), I can only group by Name and Card using

pd.pivot_table(df, values='Amount', index=['Name','Card'], aggfunc=(np.sum, len))

Sorry for the poor formatting on the table, my markdown skills are quite limited.

Any help on this?

pandas pivot-table python
1 Answers

Pivot table is a good approach, try:

table = pd.pivot_table(
    df, 
    values=['Amount'],
    index=['Name', 'Card'],
    aggfunc=['count', 'sum'],
)

# Adds subtotals, and sorts:
pd.concat([
    d.append(d.sum().rename((k, 'Total')))
    for k, d in table.groupby(level=0)
]).sort_index(ascending=[False, True])

Output:

                count    sum
               Amount Amount
Name     Card
Joe Doe  Total      3    141
         j544       1     53
         t077       2     88
Jane Doe Total      1     13
         s044       1     13

Subtotal reference: link.

user_186247780
Related