CODEWITHSUNDEEP
pythonpandasgroup-bypivot-table
onlyf
onlyf

Reputation: 883

Python - Pandas subtotals on groupby

here's a sample of the data i m using :

SCENARIO    DATE    POD         AREA    IDOC    STATUS  TYPE
AAA   02.06.2015    JKJKJKJKJKK 4210    713375  51         1
AAA   02.06.2015    JWERWERE    4210    713375  51         1
AAA   02.06.2015    JAFDFDFDFD  4210    713375  51         9
BBB   02.06.2015    AAAAAAAA    5400    713504  51        43
CCC   05.06.2015    BBBBBBBBBB  4100    756443  51       187
AAA   05.06.2015    EEEEEEEE    4100    756457  53       228

I have written the following code in pandas to groupby:

import pandas as pd
import numpy as np

xl = pd.ExcelFile("MRD.xlsx")
df = xl.parse("Sheet3") 
#print (df.column.values)

# The following gave ValueError: Cannot label index with a null key
# dfi = df.pivot('SCENARIO)

# Here i do not actually need it to count every column, just a specific one
table = df.groupby(["SCENARIO", "STATUS", "TYPE"]).agg(['count'])
writer = pd.ExcelWriter('pandas.out.xlsx', engine='xlsxwriter')
table.to_excel(writer, sheet_name='Sheet1')
writer.save()


table2 = pd.DataFrame(df.groupby(["SCENARIO", "STATUS", "TYPE"])['TYPE'].count())
print (table2)
writer2 = pd.ExcelWriter('pandas2.out.xlsx', engine='xlsxwriter')
table2.to_excel(writer2, sheet_name='Sheet1')
writer2.save()

this yields a result :

SCENARIO  STATUS  TYPE    TYPE
AAA       51      1       2
                  9       1
          53      228     1
BBB       51      43      1
CCC       51      187     1
Name: TYPE, dtype: int64

How could i add subtotals per group? Ideally i would want to achieve something like:

SCENARIO  STATUS  TYPE    TYPE
AAA       51      1       2
                  9       1
          Total           3
          53      228     1
          Total           1
BBB       51      43      1
          Total           1
CCC       51      187     1
          Total           1
Name: TYPE, dtype: int64

Is this possible?

Upvotes: 3

Views: 23574

Answers (3)

Jayshwor Khadka
Jayshwor Khadka

Reputation: 57

The same thing can be achived with pandas pivot table:

table = pd.pivot_table(df, values=['TYPE'], index=['SCENARIO', 'STATUS'], aggfunc='count')
table

enter image description here

Upvotes: 2

Canute S
Canute S

Reputation: 394

Chris Moffitt has created a library named sidetable to ease this process which can be used with the groupby object with an accessor making it very easy. That said, the accepted answer and comments are a gold mine, which I feel it's worth checking it out first.

Upvotes: 1

jezrael
jezrael

Reputation: 863226

Use:

#if necessary convert TYPE column to string
df['TYPE'] = df['TYPE'].astype(str)
df = df.groupby(["SCENARIO", "STATUS", "TYPE"])['TYPE'].count()

#aggregate sum by first 2 levels
df1 = df.groupby(["SCENARIO", "STATUS"]).sum()
#add 3 level of MultiIndex 
df1.index = [df1.index.get_level_values(0),
            df1.index.get_level_values(1),
            ['Total'] * len(df1)]

#thanks MaxU for improving
#df1 = df1.set_index(np.array(['Total'] * len(df1)), append=True) 

print (df1)
SCENARIO  STATUS       
AAA       51      Total    3
          53      Total    1
BBB       51      Total    1
CCC       51      Total    1
Name: TYPE, dtype: int64

#join together and sorts
df = pd.concat([df, df1]).sort_index(level=[0,1])
print (df)
SCENARIO  STATUS  TYPE 
AAA       51      1        2
                  9        1
                  Total    3
          53      228      1
                  Total    1
BBB       51      43       1
                  Total    1
CCC       51      187      1
                  Total    1
Name: TYPE, dtype: int64

Upvotes: 12

Related Questions