pandas 的数据透视表小计 [英] Pivot table subtotals in Pandas

问题描述

我有以下数据:

Employee    Account Currency    Amount  Location
Test 2      Basic   USD         3000    Airport
Test 2      Net     USD         2000    Airport
Test 1      Basic   USD         4000    Town
Test 1      Net     USD         3000    Town
Test 3      Basic   GBP         5000    Town
Test 3      Net     GBP         4000    Town

通过执行以下操作，我可以设法解决问题:

I can manage to pivot by doing the following:

import pandas as pd
table = pd.pivot_table(df, values=['Amount'], index=['Location', 'Employee'], columns=['Account', 'Currency'], fill_value=0, aggfunc=np.sum, dropna=True)

输出:

                      Amount                  
Account            Basic         Net      
Currency             GBP   USD   GBP   USD
Location Employee                         
Airport  Test 2        0  3000     0  2000
Town     Test 1        0  4000     0  3000
         Test 3     5000     0  4000     0

我如何才能按位置获得小计，然后在底部获得最终的总计. 所需的输出:

How can I achieve subtotal by location and then a final grand total at the bottom. Desired output:

                  Amount                  
Account            Basic         Net      
Currency             GBP   USD   GBP   USD
Location Employee                         
Airport  Test 2        0  3000     0  2000
Airport  Total            3000     0  2000  
Town     Test 1        0  4000     0  3000
         Test 3     5000     0  4000     0
Town Total          5000  4000  4000  3000
Grand Total         5000  7000  4000  5000

我尝试按照遵循.但是它不能提供所需的输出. 谢谢.

I tried following the following. But it does not give the desired output. Thank you.

推荐答案

您的数据透视表

your pivot table

table = pd.pivot_table(df, values=['Amount'],
                       index=['Location', 'Employee'],
                       columns=['Account', 'Currency'],
                       fill_value=0, aggfunc=np.sum, dropna=True, )
print(table)

                  Amount                  
Account            Basic         Net      
Currency             GBP   USD   GBP   USD
Location Employee                         
Airport  Test 2        0  3000     0  2000
Town     Test 1        0  4000     0  3000
         Test 3     5000     0  4000     0

---

pandas.concat

---

pandas.concat

pd.concat([
    d.append(d.sum().rename((k, 'Total')))
    for k, d in table.groupby(level=0)
]).append(table.sum().rename(('Grand', 'Total')))


                  Amount                  
Account            Basic         Net      
Currency             GBP   USD   GBP   USD
Location Employee                         
Airport  2             0  3000     0  2000
         Total         0  3000     0  2000
Town     1             0  4000     0  3000
         3          5000     0  4000     0
         Total      5000  4000  4000  3000
Grand    Total      5000  7000  4000  5000

---

旧答案

后代

---

Old Answer

for posterity

构建子总计

build sub totals

tab_tots = table.groupby(level='Location').sum()
tab_tots.index = [tab_tots.index, ['Total'] * len(tab_tots)]
print(tab_tots)

               Amount                  
Account         Basic         Net      
Currency          GBP   USD   GBP   USD
Location                               
Airport  Total      0  3000     0  2000
Town     Total   5000  4000  4000  3000

---

全部

---

all together

pd.concat(
    [table, tab_tots]
).sort_index().append(
    table.sum().rename(('Grand', 'Total'))
)

这篇关于 pandas 的数据透视表小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！