Pandas Pivot Table - Adding Subtotals to Multiindex Table

Question

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?

Answer 1

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.