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

问题描述

我正在使用熊猫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.

推荐答案

如果不将州和城市都放在行中，则将获得单独的边距.整形，然后得到您想要的桌子:

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屋！