Pandas:在多列枢轴中添加子行和

Question

我有一个数据框架构建fromRecords django查询集，我枢轴2列得到它的仪表板视图。我设法对整个表的行和列进行全局求和，但我试图通过第一个枢轴列(每个组的第一列的行合计)获得总和。

我对熊猫一无所知，但我正在学习。

我的dataFrame看起来像:

    type                    amount      source  fund
0   Ressource Humaine CDD   -36470.36   Expense fund2
1   Mission                 -1686.47    Expense fund2
2   Fonctionnement          -817465.91  Expense fund1
3   Fonctionnement          1118691.65  Budget  fund1
4   Fonctionnement          -6000       Expense fund3
5   Fonctionnement          -23621.83   Expense fund2
6   Frais de Gestion        -53499      Expense fund2
7   Fonctionnement          15000       Budget  fund3
8   Frais de Gestion        53499       Budget  fund2
9   Fonctionnement          186718.78   Budget  fund2
10  Mission                 1686.47     Budget  fund2
1   Ressource Humaine CDD   38676.53    Budget  fund2

为了在仪表板中获得资金可用性的概述，我将其旋转为:piv=cpd.pivot_table(index="type", columns=["fund","source"], values="amount", aggfunc='sum', margins=True, margins_name='Sum')

获取:

fund                    fund1                   fund2                       fund3
source                  Budget      Expense     Budget      Expense         Budget      Expense
type
Fonctionnement          1118691.65  -817465.91  186718.78   -23621.83       15000.00    -6000.00
Frais de Gestion        NaN         NaN         53499.00    -53499.00       NaN         NaN
Mission                 NaN         NaN         1686.47     -1686.47        NaN         NaN
Ressource Humaine CDD   NaN         NaN         38676.53    -36470.36       NaN         NaN

(这里漏掉了总数，但我已经得到了)

我想降落在这样的地方:

fund                    fund1                                       fund2                                   fund3
source                  Budget      Expense         total fund1     Budget      Expense     total fund2     Budget      Expense     total fund3
type
Fonctionnement          1118691.65  -817465.91      301 226€        186718.78   -23621.83   163 097€        15000.00    -6000.00    9 000€
Frais de Gestion        NaN         NaN             NaN             53499.00    -53499.00   0               NaN         NaN         NaN 
Mission                 NaN         NaN             NaN             1686.47     -1686.47    0               NaN         NaN         NaN
Ressource Humaine CDD   NaN         NaN             NaN             38676.53    -36470.36   2 207€          NaN         NaN         NaN

我已经看到了一些使用pandas concat进行多索引数据透视的技巧(例如:pandas中的数据透视表小计)

我正在尝试按列循环或读取标题或…但我可以走得更远，因为我是个新手!

我如何插入/附加一个中间列与sum，以及如何计算这个子和?

Answer 1

您可以进行正常的枢轴运算，然后计算/追加总和:

# do a normal pivot
df = df.pivot_table(
    index="type",
    columns=["fund", "source"],
    values="amount",
    aggfunc="sum",
)
# compute "sum" dataframes
dfs = []
for c in df.columns.get_level_values(0).unique():
    s = df.loc[:, c].sum(axis=1, skipna=False)
    dfs.append(pd.DataFrame(s, index=s.index, columns=[(c, f"Total {c}")]))
# concat them together, sort the columns:
out = pd.concat([df, pd.concat(dfs, axis=1)], axis=1)
out = out[sorted(out.columns)]
print(out)

打印:

fund                        fund1                             fund2                          fund3                    
source                     Budget    Expense Total fund1     Budget   Expense Total fund2   Budget Expense Total fund3
type                                                                                                                  
Fonctionnement         1118691.65 -817465.91   301225.74  186718.78 -23621.83   163096.95  15000.0 -6000.0      9000.0
Frais de Gestion              NaN        NaN         NaN   53499.00 -53499.00        0.00      NaN     NaN         NaN
Mission                       NaN        NaN         NaN    1686.47  -1686.47        0.00      NaN     NaN         NaN
Ressource Humaine CDD         NaN        NaN         NaN   38676.53 -36470.36     2206.17      NaN     NaN         NaN