在数据透视表中为不同列的行创建小计 [英] Create subtotals for rows in pivot table for different columns

问题描述

我正在用Pandas创建数据透视表，但是卡住了不同列(在相同值下)内行的小计一段时间，我对stackoverflow进行了一些研究，例如Pandas数据透视表行小计和此

I am creating a pivot table with Pandas but got stuck at the subtotals for rows within different columns (under the same Values) for a while, I've done some research on stackoverflow such as Pandas Pivot tables row subtotals and this Subtotals for Pandas pivot table index and column (actually this one is close to but some what also different from my case) but didn't find right hint for my case, so please for this topic, thanks.

在这里，我将提供我的情况和为获取数据透视表而编写的代码的简化数据框(原始数据框太大，无法在此处显示，因此它们的值确实无关紧要，格式就是我要的格式)

Here I will provide a simplified DataFrame (original one is too big to show here, so their values really do not matter, the format is what I am looking for) of my case and the code I wrote to get my pivot table.

import pandas as pd
import numpy as np
df = pd.DataFrame(
{
    'Co':['NN']*6,
    'Op':['A','B']*3,
    'Stk':[1.1,1.2,1.3]*2,
    'Tm':['07-01-2018','08-01-2018','09-01-2018']*2,
    'Qt':[100,200,300]*2
}
)
df

df输出如下:

Co  Op  Qt  Stk Tm
0   NN  A   100 1.1 07-01-2018
1   NN  B   200 1.2 08-01-2018
2   NN  A   300 1.3 09-01-2018
3   NN  B   100 1.1 07-01-2018
4   NN  A   200 1.2 08-01-2018
5   NN  B   300 1.3 09-01-2018

然后我通过以下方法将df上方的数据转换为我的数据透视表:

Then I convert above df to my pivot table by:

df['Qt'] = df['Qt'].apply(pd.to_numeric)
df['Stk']=df['Stk'].apply(pd.to_numeric)
df['Co'] = df['Co'].astype(str)
tb=pd.pivot_table(df,index=["Tm"],columns=["Co","Op","Stk"],aggfunc=np.sum,values=['Qt'], fill_value=0, margins=True, margins_name='All')
tb

生成的数据透视表如下:

The generated pivot table looks like:

            Qt
Co          NN                              All
Op          A              B    
Stk         1.1  1.2  1.3  1.1  1.2  1.3    
        Tm                          
07-01-2018  100  0    0    100  0    0      200
08-01-2018  0    200  0    0    200  0      400
09-01-2018  0    0    300  0    0    300    600
       All  100  200  300  100  200  300    1200

我真正期望的格式是:

            Qt
Co          NN                                                All
Op          A              ATotal   B               BTotal
Stk         1.1  1.2  1.3           1.1  1.2  1.3   
        Tm                          
07-01-2018  100  0    0    100      100  0    0     100       200
08-01-2018  0    200  0    200      0    200  0     200       400
09-01-2018  0    0    300  300      0    0    300   300       600
       All  100  200  300  600      100  200  300   600       1200

我试图创建这种完全相同的格式已有一段时间，但仍然无法获得相同的格式(我尝试过类似创建两个单独的A和B数据透视表并将它们合并在一起的操作，但是这样会弄乱所有边距).因此，这里确实需要帮助. p.s.我还是stackoverflow社区的新手，所以如果我的问题缺少某些方面，请原谅，谢谢.

I was trying to create this exact same format for a while and still can't get the same one (I tried something like creating two separate A and B pivot tables and union them together, but it will mess with the All margins). So help is really needed here. p.s. I am still new to stackoverflow community, so please pardon if my question is missing some aspects, thank you.

推荐答案

pivot_table不支持它，但是您可以自己计算并在以后将其连接:

pivot_table doesn't support it, but you can compute it yourself and concatenate it later:

tb.groupby(level='Op', axis=1).sum().add_suffix('Total')

Op          Total  ATotal  BTotal
Tm                               
07-01-2018    200     100     100
08-01-2018    400     200     200
09-01-2018    600     300     300
All          1200     600     600

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