Pandas 数据透视表行小计 [英] Pandas Pivot tables row subtotals

问题描述

我使用的是 Pandas 0.10.1

I'm using 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

如何对每个州的小计进行分组?

How can i group subtotals per state?

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

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

I tried with a pivot table but i only can have subtotals in columns

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

我可以使用数据透视表在 excel 上实现这一点.

I can achieve this on excel, with a pivot table.

推荐答案

如果您不将 State 和 City 都放在行中，您将获得单独的边距.重塑，你得到你想要的桌子:

If you put State and City not both in the rows, you'll get separate margins. Reshape and you get the table you're after:

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

我承认这并不完全显而易见.

I admit this isn't totally obvious.

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