python - Pandas 数据透视表每列的小计

Question

我可以使用pandas中的pivot_table实现我想要的输出(如下所示)或与以下数据集类似的输出吗？我正在尝试做类似的事情:

pivot_table(df, rows=['region'], cols=['area','distributor','salesrep'], 
            aggfunc=np.sum, margins=True).stack(['area','distributor','salesrep'])

但我只获得每个区域的小计，如果我将区域从列移动到行，那么我将只获得每个区域的小计。

数据集:

region   area            distributor     salesrep       sales    invoice_countCentral  Butterworth     HIN MARKETING   TLS            500      25Central  Butterworth     HIN MARKETING   TLS            500      25Central  Butterworth     HIN MARKETING   OSE            500      25Central  Butterworth     HIN MARKETING   OSE            500      25Central  Butterworth     KWANG HENGG     TCS            500      25Central  Butterworth     KWANG HENGG     TCS            500      25Central  Butterworth     KWANG HENG      LBH            500      25Central  Butterworth     KWANG HENG      LBH            500      25Central  Ipoh            SGH EDERAN      CHAN           500      25Central  Ipoh            SGH EDERAN      CHAN           500      25Central  Ipoh            SGH EDERAN      KAMACHI        500      25Central  Ipoh            SGH EDERAN      KAMACHI        500      25Central  Ipoh            CORE SYN        LILIAN         500      25Central  Ipoh            CORE SYN        LILIAN         500      25Central  Ipoh            CORE SYN        TEOH           500      25Central  Ipoh            CORE SYN        TEOH           500      25East     JB              LEI WAH         NF05           500      25East     JB              LEI WAH         NF05           500      25East     JB              LEI WAH         NF06           500      25East     JB              LEI WAH         NF06           500      25East     JB              WONDER F&B      SEREN          500      25East     JB              WONDER F&B      SEREN          500      25East     JB              WONDER F&B      MONC           500      25East     JB              WONDER F&B      MONC           500      25East     PJ              PENGEDAR        NORM           500      25East     PJ              PENGEDAR        NORM           500      25East     PJ              PENGEDAR        SIMON          500      25East     PJ              PENGEDAR        SIMON          500      25East     PJ              HEBAT           OGI            500      25East     PJ              HEBAT           OGI            500      25East     PJ              HEBAT           MIGI           500      25East     PJ              HEBAT           MIGI           500      25

期望的输出:

region       area          distributor       salesrep             invoice_count salesGrand Total                                                                 800 16000Central      Central Total                                                  400  8000Central      Butterworth   Butterworth Total                                200  4000Central      Butterworth   HIN MARKETING     HIN MARKETING Total            100  2000Central      Butterworth   HIN MARKETING     OSE                             50  1000Central      Butterworth   HIN MARKETING     TLS                             50  1000Central      Butterworth   KWANG HENG        KWANG HENG Total               100  2000Central      Butterworth   KWANG HENG        LBH                             50  1000Central      Butterworth   KWANG HENG        TCS                             50  1000Central      Ipoh          Ipoh Total                                       200  4000Central      Ipoh          CORE SYN          CORE SYN Total                 100  2000Central      Ipoh          CORE SYN          LILIAN                          50  1000Central      Ipoh          CORE SYN          TEOH                            50  1000Central      Ipoh          SGH EDERAN        SGH EDERAN Total               100  2000Central      Ipoh          SGH EDERAN        CHAN                            50  1000Central      Ipoh          SGH EDERAN        KAMACHI                         50  1000East         East Total                                                     400  8000East         JB            JB Total                                         200  4000East         JB            LEI WAH           LEI WAH Total                  100  2000East         JB            LEI WAH           NF05                            50  1000East         JB            LEI WAH           NF06                            50  1000East         JB            WONDER F&B        WONDER F&B Total               100  2000East         JB            WONDER F&B        MONC                            50  1000East         JB            WONDER F&B        SEREN                           50  1000East         PJ            PJ Total                                         200  4000East         PJ            HEBAT             HEBAT Total                    100  2000East         PJ            HEBAT             MIGI                            50  1000East         PJ            HEBAT             OGI                             50  1000East         PJ            PENGEDAR          PENDEGAR Total                 100  2000East         PJ            PENGEDAR          NORM                            50  1000East         PJ            PENGEDAR          SIMON                           50  1000

Answer 1

我们可以使用groupby代替pivot_table:

import numpy as np
import pandas as pd


def label(ser):
    return '{s} Total'.format(s=ser)

filename = 'data.txt'
df = pd.read_table(filename, delimiter='\t')

total = pd.DataFrame({'region': ['Grand Total'],
                      'invoice_count': df['invoice_count'].sum(),
                      'sales': df['sales'].sum()})
total['total_rank'] = 1

region_total = df.groupby(['region'], as_index=False).sum()
region_total['area'] = region_total['region'].apply(label)
region_total['region_rank'] = 1

area_total = df.groupby(['region', 'area'], as_index=False).sum()
area_total['distributor'] = area_total['area'].apply(label)
area_total['area_rank'] = 1

dist_total = df.groupby(
    ['region', 'area', 'distributor'], as_index=False).sum()
dist_total['salesrep'] = dist_total['distributor'].apply(label)

rep_total = df.groupby(
    ['region', 'area', 'distributor', 'salesrep'], as_index=False).sum()

# UNION the DataFrames into one DataFrame
result = pd.concat([total, region_total, area_total, dist_total, rep_total])

# Replace NaNs with empty strings
result.fillna({'region': '', 'area': '', 'distributor': '', 'salesrep':
              ''}, inplace=True)

# Reorder the rows
sorter = np.lexsort((
    result['distributor'].rank(),
    result['area_rank'].rank(),
    result['area'].rank(),
    result['region_rank'].rank(),
    result['region'].rank(),
    result['total_rank'].rank()))
result = result.take(sorter)
result = result.reindex(
    columns=['region', 'area', 'distributor', 'salesrep', 'invoice_count', 'sales'])
print(result.to_string(index=False))

产量

      region           area        distributor             salesrep  invoice_count  sales
 Grand Total                                                                   800  16000
     Central  Central Total                                                    400   8000
     Central    Butterworth  Butterworth Total                                 200   4000
     Central    Butterworth      HIN MARKETING  HIN MARKETING Total            100   2000
     Central    Butterworth      HIN MARKETING                  OSE             50   1000
     Central    Butterworth      HIN MARKETING                  TLS             50   1000
     Central    Butterworth         KWANG HENG     KWANG HENG Total            100   2000
     Central    Butterworth         KWANG HENG                  LBH             50   1000
     Central    Butterworth         KWANG HENG                  TCS             50   1000
     Central           Ipoh         Ipoh Total                                 200   4000
     Central           Ipoh           CORE SYN       CORE SYN Total            100   2000
     Central           Ipoh           CORE SYN               LILIAN             50   1000
     Central           Ipoh           CORE SYN                 TEOH             50   1000
     Central           Ipoh         SGH EDERAN     SGH EDERAN Total            100   2000
     Central           Ipoh         SGH EDERAN                 CHAN             50   1000
     Central           Ipoh         SGH EDERAN              KAMACHI             50   1000
        East     East Total                                                    400   8000
        East             JB           JB Total                                 200   4000
        East             JB            LEI WAH        LEI WAH Total            100   2000
        East             JB            LEI WAH                 NF05             50   1000
        East             JB            LEI WAH                 NF06             50   1000
        East             JB         WONDER F&B     WONDER F&B Total            100   2000
        East             JB         WONDER F&B                 MONC             50   1000
        East             JB         WONDER F&B                SEREN             50   1000
        East             PJ           PJ Total                                 200   4000
        East             PJ              HEBAT          HEBAT Total            100   2000
        East             PJ              HEBAT                 MIGI             50   1000
        East             PJ              HEBAT                  OGI             50   1000
        East             PJ           PENGEDAR       PENGEDAR Total            100   2000
        East             PJ           PENGEDAR                 NORM             50   1000
        East             PJ           PENGEDAR                SIMON             50   1000