Pandas数据透视表行小计

Question

我正在使用Pandas 0.10.1版本

考虑以下数据框：

Date       State   City    SalesToday  SalesMTD  SalesYTD
20130320     stA    ctA            20       400      1000
20130320     stA    ctB            30       500      1100
20130320     stB    ctC            10       500       900
20130320     stB    ctD            40       200      1300
20130320     stC    ctF            30       300       800

我该如何按州分组小计？

State   City  SalesToday  SalesMTD  SalesYTD
  stA    ALL          50       900      2100
  stA    ctA          20       400      1000
  stA    ctB          30       500      1100

我尝试使用数据透视表，但我只能在列中得到小计。

table = pivot_table(df, values=['SalesToday', 'SalesMTD','SalesYTD'],\
                     rows=['State','City'], aggfunc=np.sum, margins=True)

我可以用 Excel 的数据透视表来实现这个。

- Ivan Pereira

Answer 1

如果你不将州和城市都放在行中，你会得到分开的边距。重塑表格，你就可以得到你想要的表格：

In [10]: table = pivot_table(df, values=['SalesToday', 'SalesMTD','SalesYTD'],\
                     rows=['State'], cols=['City'], aggfunc=np.sum, margins=True)


In [11]: table.stack('City')
Out[11]: 
            SalesMTD  SalesToday  SalesYTD
State City                                
stA   All        900          50      2100
      ctA        400          20      1000
      ctB        500          30      1100
stB   All        700          50      2200
      ctC        500          10       900
      ctD        200          40      1300
stC   All        300          30       800
      ctF        300          30       800
All   All       1900         130      5100
      ctA        400          20      1000
      ctB        500          30      1100
      ctC        500          10       900
      ctD        200          40      1300
      ctF        300          30       800

我承认这并不是完全显而易见的。

- Wes McKinney

---

如果我们有values=，那么这个方法可以使用；如果列是从columns=创建的，将只会有一个“全部”列。 - Winand

针对 pandas 0.25.1 版本，rows不是一个参数。我指定了一个由3列组成的索引，但输出结果只返回总计而不是索引列的小计。 - datalifenyc

Answer 2

你可以通过在“State”列上使用groupby()来获取汇总值。

让我们首先制作一些示例数据：

import pandas as pd
import StringIO

incsv = StringIO.StringIO("""Date,State,City,SalesToday,SalesMTD,SalesYTD
20130320,stA,ctA,20,400,1000
20130320,stA,ctB,30,500,1100
20130320,stB,ctC,10,500,900
20130320,stB,ctD,40,200,1300
20130320,stC,ctF,30,300,800""")

df = pd.read_csv(incsv, index_col=['Date'], parse_dates=True)

然后应用groupby函数并添加一个名为City的列：

dfsum = df.groupby('State', as_index=False).sum()
dfsum['City'] = 'All'

print dfsum

  State  SalesToday  SalesMTD  SalesYTD City
0   stA          50       900      2100  All
1   stB          50       700      2200  All
2   stC          30       300       800  All

我们可以使用append将原始数据添加到总和df中：

dfsum.append(df).set_index(['State','City']).sort_index()

print dfsum

            SalesMTD  SalesToday  SalesYTD
State City                                
stA   All        900          50      2100
      ctA        400          20      1000
      ctB        500          30      1100
stB   All        700          50      2200
      ctC        500          10       900
      ctD        200          40      1300
stC   All        300          30       800
      ctF        300          30       800

我添加了set_index和sort_index以使其看起来更像您的示例输出，这并不是必须的结果。

- Rutger Kassies

---

另一个可选的函数是concat()，它允许我通过设置参数ignore_index来更好地控制索引。pd.concat([x, y], ignore_index=True)。这篇文章还提到，与pandas.append()相比，concat()更有效率。 - Michael Revlis

Answer 3

我认为这个小计示例代码是您想要的（类似于Excel的小计）。

我假设您想按列A、B、C、D分组，然后计算E列的值。

main_df.groupby(['A', 'B', 'C']).apply(lambda sub_df:
    sub_df.pivot_table(index=['D'], values=['E'], aggfunc='count', margins=True))

输出：

          E
A B C  D
a a a  a  1
       b  2
       c  2
     all  5
b b a  a  3
       b  2
       c  2
     all  7
b b b  a  3
       b  6
       c  2
       d  3
     all 14

- hs moon

---

以防万一有人尝试此操作但省略了 margins=True 参数：它是必需的，以获取添加了 all 的行。 - hlongmore

@Parfait 在你的例子中，有没有一种方法可以包括一个总计？ - Clickinaway

@Clickinaway...这不是我的答案，我只是帮忙编辑。但是根据pivot_table的规范，您可以获得行小计和列总计。 - Parfait

@Parfait，我目前在做这件事情上失败了，并且整个早上一直在查看其他SE帖子。我的帖子在这里https://stackoverflow.com/questions/53266032/adding-a-grand-total-to-a-pandas-pivot-table - Clickinaway

@Clickinaway，这个方法没有包括总计，因为它是分别计算每个 groupby(['A', 'B', 'C']) 的总计，所以没有总计。但是你可以很容易地手动添加一个总计，比如使用 df.loc[('Grand Total', '', '', ''), :] = df.sum()。 - wisbucky

Answer 4

这个怎么样？

table = pd.pivot_table(data, index=['State'],columns = ['City'],values=['SalesToday', 'SalesMTD','SalesYTD'],\
                      aggfunc=np.sum, margins=True)

- Richard Mao

Answer 5

如果您感兴趣，我刚刚创建了一个小函数，使得在许多表格上应用此函数“subtotal”更加容易。它适用于通过pivot_table()和groupby()创建的表格。在这个stackoverflow页面上提供了一个使用它的表格示例：Sub Total in pandas pivot Table

def get_subtotal(table, sub_total='subtotal', get_total=False, total='TOTAL'):
    """
    Parameters
    ----------
    table : dataframe, table with multi-index resulting from pd.pivot_table() or 
    df.groupby().
    sub_total : str, optional
        Name given to the subtotal. The default is '_Sous-total'.
    get_total : boolean, optional
        Precise if you want to add the final total (in case you used groupeby()). 
        The default is False.
    total : str, optional
        Name given to the total. The default is 'TOTAL'.

    Returns
    -------
    A table with the total and subtotal added.
    """
    index_name1 = table.index.names[0]
    index_name2 = table.index.names[1]
    
    pvt = table.unstack(0)
    mask = pvt.columns.get_level_values(index_name1) != 'All'
    #print (mask)
    pvt.loc[sub_total] = pvt.loc[:, mask].sum()
    
    pvt = pvt.stack().swaplevel(0,1).sort_index()
    pvt = pvt[pvt.columns[1:].tolist() + pvt.columns[:1].tolist()]
    
    if get_total:
        mask = pvt.index.get_level_values(index_name2) != sub_total
        pvt.loc[(total, '' ),: ] = pvt.loc[mask].sum()
    print (pvt)
    return(pvt)

- Tomtomtom

Answer 6

table = pd.pivot_table(df, index=['A'], values=['B', 'C'], columns=['D', 'E'], fill_value='0', aggfunc=np.sum/'count'/etc., margins=True, margins_name='Total')

print(table)

- Sourav Dey