added a c column total to get total by column name

from pprint import pprint

import pandas as pd

input = [
    {"item": "i1", "balance": 11, "warehouse": "W1"},
    {"item": "i1", "balance": 12, "warehouse": "W4"},
    {"item": "i1", "balance": 13, "warehouse": "W3"},

    {"item": "i2", "balance": 11, "warehouse": "W2"},
    {"item": "i2", "balance": 10, "warehouse": "W1"},
    {"item": "i3", "balance": 10, "warehouse": "W3"},
]

df = pd.DataFrame(input)
df_pivot = df.pivot_table(
    index=["item"], columns="warehouse", values="balance", fill_value=0
)
print(df_pivot)
output = df_pivot.reset_index().to_dict(orient="records")
pprint(output)



    warehouse  W1  W2  W3  W4
item                     
i1         11   0  13  12
i2         10  11   0   0
i3          0   0  10   0

[{'W1': 11, 'W2': 0, 'W3': 13, 'W4': 12, 'item': 'i1'},
 {'W1': 10, 'W2': 11, 'W3': 0, 'W4': 0, 'item': 'i2'},
 {'W1': 0, 'W2': 0, 'W3': 10, 'W4': 0, 'item': 'i3'}]

I want to add a total column where is the sum of for(w1,w2,..) in its row:

 [
 {'W1': 11, 'W2': 0, 'W3': 13, 'W4': 12, 'item': 'i1',total: 36},
 {'W1': 10, 'W2': 11, 'W3': 0, 'W4': 0, 'item': 'i2',total: 21},
 {'W1': 0, 'W2': 0, 'W3': 10, 'W4': 0, 'item': 'i3', total: 10}
  ]

>Solution :

Use the margins parameter of pivot_table and aggfunc='sum':

df_pivot = df.pivot_table(
    index=["item"], columns="warehouse", values="balance", fill_value=0,
    margins=True, margins_name='total', aggfunc='sum'
).drop('total')

output = df_pivot.reset_index().to_dict(orient="records")

Output:

[{'W1': 11, 'W2': 0, 'W3': 13, 'W4': 12, 'item': 'i1', 'total': 36},
 {'W1': 10, 'W2': 11, 'W3': 0, 'W4': 0, 'item': 'i2', 'total': 21},
 {'W1': 0, 'W2': 0, 'W3': 10, 'W4': 0, 'item': 'i3', 'total': 10}]