Adding a Grand Total to a Pandas Pivot Table











up vote
1
down vote

favorite












I'm stuck. In my code below I can successfully create the subtotaled pivot table I'm looking for but cannot produce a grand total.
[The following code leverages the arcgis module; this simply converts a table (in this case an MSSQL table) to a NumPy array]



import numpy as np

import pandas as pd

import arcpy



table = "\\filserver\MAP_PROJECTS\LV_WEB\SDE_CONNECTIONS\LV_NEXUS.sde\LV_NEXUS.DATAOWNER.NORTHEAST\LV_NEXUS.DATAOWNER.NE_HARVEST_OPS"

HUID = "669-NMTC-139"

whereClause = """ "LV_HARVEST_UNIT_ID" = '{0}' """.format(HUID)

tableArray = arcpy.da.TableToNumPyArray(table, ['STAND_NUMB', 'SUPER_TYPE','STRATA', 'OS_TYPE', 'SILV_PRES', 'ACRES'], where_clause = whereClause)

df = pd.DataFrame(tableArray)

report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df:  sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'],aggfunc=np.sum, margins=True,margins_name= 'TOTAL'))

np.round(report,1)


enter image description here



This provides a total for each 'SUPER_TYPE' group, but I cannot create a grand total. I tried the following:



grandtotal = np.round(np.sum(report),1)

grandtotal.name = 'Grand Total'

report.append(grandtotal)


and that just creates a terrible mess. It appends the grand total but destroys the formatting of my data frame.



Dataframe pasted below not sure how to keep the formatting



   STAND_NUMB SUPER_TYPE   STRATA OS_TYPE    SILV_PRES      ACRES

0        3113         SH     SH3B    SH3B  OSR/2SS/SCC   0.612748

1        3608         HW     H3AB     H3B  OSR/2SS/SCC  12.936038

2        3105         HW     H3AB     H3B  OSR/2SS/SCC  35.199887

3        3607         HS     HS3B    HS3B  OSR/2SS/SCC  27.683348

4        3601         HW     H3AB     H3A  OSR/2SS/SCC  11.941338

5        3603         HW      H4B     H4B  OSR/2SS/SCC  25.307238

6        3092         HS     HS3B    HS3B  OSR/2SS/SCC  17.331220

7        3600         HW      H4B     H4B  OSR/2SS/SCC  13.443112

8        3596         HW     H3AB     H3B  OSR/2SS/SCC  12.375962

9        3597         HW     H3AB     H3A  OSR/2SS/SCC  41.639072

10       3591         SW     S4BC     S4A  OSR/2SS/SCC  11.355869

11       3594         HS     HS3B    HS3B  OSR/2SS/SCC  31.747874

12       3586         HW     H3AB     H3B  OSR/2SS/SCC  19.437834

13       3588         HW     H3AB     H3A  OSR/2SS/SCC  18.129702

14       3587         HS     HS3B    HS3B  OSR/2SS/SCC  13.788853

15       3585         HW     H3AB     H3A  OSR/2SS/SCC  25.775322

16       3582         SH     SH3B    SH3B  OSR/2SS/SCC  11.026199

17       3581         HS     HS3B    HS3B  OSR/2SS/SCC  16.634195

18       3589         HW     H3AB     H3A  OSR/2SS/SCC  54.684222

19       3579         SH     SH3B    SH3B  OSR/2SS/SCC  17.313354

20       3578         HW  H4C_H2C     H4C  OSR/2SS/SCC  30.255013

21       3576         HW      H3C     H3C  OSR/2SS/SCC  11.310230

22       3573         HW     H3AB     H3A  OSR/2SS/SCC  30.369559

23       3575         HW  H4C_H2C     H4C  OSR/2SS/SCC  53.088547

24       3569         HW      H4A     H4A  OSR/2SS/SCC  12.809001

25       3567         HW      H4B     H4B  OSR/2SS/SCC  24.026682

26       3568         HW     H3AB     H3B  OSR/2SS/SCC  57.934207

27       3565         HW      H4B     H4B  OSR/2SS/SCC  33.545768

28       3605         HW     H3AB     H3B  OSR/2SS/SCC  74.424945

29       3580         HS     HS3B    HS3B  OSR/2SS/SCC   8.062028

30       3571         HW     H3AB     H3A  OSR/2SS/SCC  30.718121

31       3562         HW     H3AB     H3B  OSR/2SS/SCC  22.774026

32       3110         SW      S3C     S3C  OSR/2SS/SCC   2.240600

33     3120.1         SH     SH3B    SH3B  OSR/2SS/SCC   3.726728





python pandas pivot-table







share|improve this question




















  • 1




    can you do something like this: df.loc[('', 'TOTAL','','',''), :] = df['ACRES'].sum()
    – Chris
    5 hours ago








  • 1




    Have a look at the solutions to Pivot table subtotals in Pandas
    – ALollz
    5 hours ago








  • 1




    @ALollz I've actually been trying to make that work for the last 30 minutes or so but no luck.
    – Clickinaway
    4 hours ago






  • 1




    @Clickinaway I am not getting an error on my test data you should be able to do df.loc[('', 'Grand Total','','',''), :] = df[df.index.get_level_values(1) != 'TOTAL'].sum()
    – Chris
    4 hours ago






  • 1




    Please set up a reproducible example including all import lines and data we can run in our empty Python environments.
    – Parfait
    4 hours ago















up vote
1
down vote

favorite












I'm stuck. In my code below I can successfully create the subtotaled pivot table I'm looking for but cannot produce a grand total.
[The following code leverages the arcgis module; this simply converts a table (in this case an MSSQL table) to a NumPy array]



import numpy as np

import pandas as pd

import arcpy



table = "\\filserver\MAP_PROJECTS\LV_WEB\SDE_CONNECTIONS\LV_NEXUS.sde\LV_NEXUS.DATAOWNER.NORTHEAST\LV_NEXUS.DATAOWNER.NE_HARVEST_OPS"

HUID = "669-NMTC-139"

whereClause = """ "LV_HARVEST_UNIT_ID" = '{0}' """.format(HUID)

tableArray = arcpy.da.TableToNumPyArray(table, ['STAND_NUMB', 'SUPER_TYPE','STRATA', 'OS_TYPE', 'SILV_PRES', 'ACRES'], where_clause = whereClause)

df = pd.DataFrame(tableArray)

report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df:  sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'],aggfunc=np.sum, margins=True,margins_name= 'TOTAL'))

np.round(report,1)


enter image description here



This provides a total for each 'SUPER_TYPE' group, but I cannot create a grand total. I tried the following:



grandtotal = np.round(np.sum(report),1)

grandtotal.name = 'Grand Total'

report.append(grandtotal)


and that just creates a terrible mess. It appends the grand total but destroys the formatting of my data frame.



Dataframe pasted below not sure how to keep the formatting



   STAND_NUMB SUPER_TYPE   STRATA OS_TYPE    SILV_PRES      ACRES

0        3113         SH     SH3B    SH3B  OSR/2SS/SCC   0.612748

1        3608         HW     H3AB     H3B  OSR/2SS/SCC  12.936038

2        3105         HW     H3AB     H3B  OSR/2SS/SCC  35.199887

3        3607         HS     HS3B    HS3B  OSR/2SS/SCC  27.683348

4        3601         HW     H3AB     H3A  OSR/2SS/SCC  11.941338

5        3603         HW      H4B     H4B  OSR/2SS/SCC  25.307238

6        3092         HS     HS3B    HS3B  OSR/2SS/SCC  17.331220

7        3600         HW      H4B     H4B  OSR/2SS/SCC  13.443112

8        3596         HW     H3AB     H3B  OSR/2SS/SCC  12.375962

9        3597         HW     H3AB     H3A  OSR/2SS/SCC  41.639072

10       3591         SW     S4BC     S4A  OSR/2SS/SCC  11.355869

11       3594         HS     HS3B    HS3B  OSR/2SS/SCC  31.747874

12       3586         HW     H3AB     H3B  OSR/2SS/SCC  19.437834

13       3588         HW     H3AB     H3A  OSR/2SS/SCC  18.129702

14       3587         HS     HS3B    HS3B  OSR/2SS/SCC  13.788853

15       3585         HW     H3AB     H3A  OSR/2SS/SCC  25.775322

16       3582         SH     SH3B    SH3B  OSR/2SS/SCC  11.026199

17       3581         HS     HS3B    HS3B  OSR/2SS/SCC  16.634195

18       3589         HW     H3AB     H3A  OSR/2SS/SCC  54.684222

19       3579         SH     SH3B    SH3B  OSR/2SS/SCC  17.313354

20       3578         HW  H4C_H2C     H4C  OSR/2SS/SCC  30.255013

21       3576         HW      H3C     H3C  OSR/2SS/SCC  11.310230

22       3573         HW     H3AB     H3A  OSR/2SS/SCC  30.369559

23       3575         HW  H4C_H2C     H4C  OSR/2SS/SCC  53.088547

24       3569         HW      H4A     H4A  OSR/2SS/SCC  12.809001

25       3567         HW      H4B     H4B  OSR/2SS/SCC  24.026682

26       3568         HW     H3AB     H3B  OSR/2SS/SCC  57.934207

27       3565         HW      H4B     H4B  OSR/2SS/SCC  33.545768

28       3605         HW     H3AB     H3B  OSR/2SS/SCC  74.424945

29       3580         HS     HS3B    HS3B  OSR/2SS/SCC   8.062028

30       3571         HW     H3AB     H3A  OSR/2SS/SCC  30.718121

31       3562         HW     H3AB     H3B  OSR/2SS/SCC  22.774026

32       3110         SW      S3C     S3C  OSR/2SS/SCC   2.240600

33     3120.1         SH     SH3B    SH3B  OSR/2SS/SCC   3.726728





python pandas pivot-table







share|improve this question




















  • 1




    can you do something like this: df.loc[('', 'TOTAL','','',''), :] = df['ACRES'].sum()
    – Chris
    5 hours ago








  • 1




    Have a look at the solutions to Pivot table subtotals in Pandas
    – ALollz
    5 hours ago








  • 1




    @ALollz I've actually been trying to make that work for the last 30 minutes or so but no luck.
    – Clickinaway
    4 hours ago






  • 1




    @Clickinaway I am not getting an error on my test data you should be able to do df.loc[('', 'Grand Total','','',''), :] = df[df.index.get_level_values(1) != 'TOTAL'].sum()
    – Chris
    4 hours ago






  • 1




    Please set up a reproducible example including all import lines and data we can run in our empty Python environments.
    – Parfait
    4 hours ago













up vote
1
down vote

favorite









up vote
1
down vote

favorite











I'm stuck. In my code below I can successfully create the subtotaled pivot table I'm looking for but cannot produce a grand total.
[The following code leverages the arcgis module; this simply converts a table (in this case an MSSQL table) to a NumPy array]



import numpy as np

import pandas as pd

import arcpy



table = "\\filserver\MAP_PROJECTS\LV_WEB\SDE_CONNECTIONS\LV_NEXUS.sde\LV_NEXUS.DATAOWNER.NORTHEAST\LV_NEXUS.DATAOWNER.NE_HARVEST_OPS"

HUID = "669-NMTC-139"

whereClause = """ "LV_HARVEST_UNIT_ID" = '{0}' """.format(HUID)

tableArray = arcpy.da.TableToNumPyArray(table, ['STAND_NUMB', 'SUPER_TYPE','STRATA', 'OS_TYPE', 'SILV_PRES', 'ACRES'], where_clause = whereClause)

df = pd.DataFrame(tableArray)

report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df:  sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'],aggfunc=np.sum, margins=True,margins_name= 'TOTAL'))

np.round(report,1)


enter image description here



This provides a total for each 'SUPER_TYPE' group, but I cannot create a grand total. I tried the following:



grandtotal = np.round(np.sum(report),1)

grandtotal.name = 'Grand Total'

report.append(grandtotal)


and that just creates a terrible mess. It appends the grand total but destroys the formatting of my data frame.



Dataframe pasted below not sure how to keep the formatting



   STAND_NUMB SUPER_TYPE   STRATA OS_TYPE    SILV_PRES      ACRES

0        3113         SH     SH3B    SH3B  OSR/2SS/SCC   0.612748

1        3608         HW     H3AB     H3B  OSR/2SS/SCC  12.936038

2        3105         HW     H3AB     H3B  OSR/2SS/SCC  35.199887

3        3607         HS     HS3B    HS3B  OSR/2SS/SCC  27.683348

4        3601         HW     H3AB     H3A  OSR/2SS/SCC  11.941338

5        3603         HW      H4B     H4B  OSR/2SS/SCC  25.307238

6        3092         HS     HS3B    HS3B  OSR/2SS/SCC  17.331220

7        3600         HW      H4B     H4B  OSR/2SS/SCC  13.443112

8        3596         HW     H3AB     H3B  OSR/2SS/SCC  12.375962

9        3597         HW     H3AB     H3A  OSR/2SS/SCC  41.639072

10       3591         SW     S4BC     S4A  OSR/2SS/SCC  11.355869

11       3594         HS     HS3B    HS3B  OSR/2SS/SCC  31.747874

12       3586         HW     H3AB     H3B  OSR/2SS/SCC  19.437834

13       3588         HW     H3AB     H3A  OSR/2SS/SCC  18.129702

14       3587         HS     HS3B    HS3B  OSR/2SS/SCC  13.788853

15       3585         HW     H3AB     H3A  OSR/2SS/SCC  25.775322

16       3582         SH     SH3B    SH3B  OSR/2SS/SCC  11.026199

17       3581         HS     HS3B    HS3B  OSR/2SS/SCC  16.634195

18       3589         HW     H3AB     H3A  OSR/2SS/SCC  54.684222

19       3579         SH     SH3B    SH3B  OSR/2SS/SCC  17.313354

20       3578         HW  H4C_H2C     H4C  OSR/2SS/SCC  30.255013

21       3576         HW      H3C     H3C  OSR/2SS/SCC  11.310230

22       3573         HW     H3AB     H3A  OSR/2SS/SCC  30.369559

23       3575         HW  H4C_H2C     H4C  OSR/2SS/SCC  53.088547

24       3569         HW      H4A     H4A  OSR/2SS/SCC  12.809001

25       3567         HW      H4B     H4B  OSR/2SS/SCC  24.026682

26       3568         HW     H3AB     H3B  OSR/2SS/SCC  57.934207

27       3565         HW      H4B     H4B  OSR/2SS/SCC  33.545768

28       3605         HW     H3AB     H3B  OSR/2SS/SCC  74.424945

29       3580         HS     HS3B    HS3B  OSR/2SS/SCC   8.062028

30       3571         HW     H3AB     H3A  OSR/2SS/SCC  30.718121

31       3562         HW     H3AB     H3B  OSR/2SS/SCC  22.774026

32       3110         SW      S3C     S3C  OSR/2SS/SCC   2.240600

33     3120.1         SH     SH3B    SH3B  OSR/2SS/SCC   3.726728





python pandas pivot-table







share|improve this question















I'm stuck. In my code below I can successfully create the subtotaled pivot table I'm looking for but cannot produce a grand total.
[The following code leverages the arcgis module; this simply converts a table (in this case an MSSQL table) to a NumPy array]



import numpy as np

import pandas as pd

import arcpy



table = "\\filserver\MAP_PROJECTS\LV_WEB\SDE_CONNECTIONS\LV_NEXUS.sde\LV_NEXUS.DATAOWNER.NORTHEAST\LV_NEXUS.DATAOWNER.NE_HARVEST_OPS"

HUID = "669-NMTC-139"

whereClause = """ "LV_HARVEST_UNIT_ID" = '{0}' """.format(HUID)

tableArray = arcpy.da.TableToNumPyArray(table, ['STAND_NUMB', 'SUPER_TYPE','STRATA', 'OS_TYPE', 'SILV_PRES', 'ACRES'], where_clause = whereClause)

df = pd.DataFrame(tableArray)

report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df:  sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'],aggfunc=np.sum, margins=True,margins_name= 'TOTAL'))

np.round(report,1)


enter image description here



This provides a total for each 'SUPER_TYPE' group, but I cannot create a grand total. I tried the following:



grandtotal = np.round(np.sum(report),1)

grandtotal.name = 'Grand Total'

report.append(grandtotal)


and that just creates a terrible mess. It appends the grand total but destroys the formatting of my data frame.



Dataframe pasted below not sure how to keep the formatting



   STAND_NUMB SUPER_TYPE   STRATA OS_TYPE    SILV_PRES      ACRES

0        3113         SH     SH3B    SH3B  OSR/2SS/SCC   0.612748

1        3608         HW     H3AB     H3B  OSR/2SS/SCC  12.936038

2        3105         HW     H3AB     H3B  OSR/2SS/SCC  35.199887

3        3607         HS     HS3B    HS3B  OSR/2SS/SCC  27.683348

4        3601         HW     H3AB     H3A  OSR/2SS/SCC  11.941338

5        3603         HW      H4B     H4B  OSR/2SS/SCC  25.307238

6        3092         HS     HS3B    HS3B  OSR/2SS/SCC  17.331220

7        3600         HW      H4B     H4B  OSR/2SS/SCC  13.443112

8        3596         HW     H3AB     H3B  OSR/2SS/SCC  12.375962

9        3597         HW     H3AB     H3A  OSR/2SS/SCC  41.639072

10       3591         SW     S4BC     S4A  OSR/2SS/SCC  11.355869

11       3594         HS     HS3B    HS3B  OSR/2SS/SCC  31.747874

12       3586         HW     H3AB     H3B  OSR/2SS/SCC  19.437834

13       3588         HW     H3AB     H3A  OSR/2SS/SCC  18.129702

14       3587         HS     HS3B    HS3B  OSR/2SS/SCC  13.788853

15       3585         HW     H3AB     H3A  OSR/2SS/SCC  25.775322

16       3582         SH     SH3B    SH3B  OSR/2SS/SCC  11.026199

17       3581         HS     HS3B    HS3B  OSR/2SS/SCC  16.634195

18       3589         HW     H3AB     H3A  OSR/2SS/SCC  54.684222

19       3579         SH     SH3B    SH3B  OSR/2SS/SCC  17.313354

20       3578         HW  H4C_H2C     H4C  OSR/2SS/SCC  30.255013

21       3576         HW      H3C     H3C  OSR/2SS/SCC  11.310230

22       3573         HW     H3AB     H3A  OSR/2SS/SCC  30.369559

23       3575         HW  H4C_H2C     H4C  OSR/2SS/SCC  53.088547

24       3569         HW      H4A     H4A  OSR/2SS/SCC  12.809001

25       3567         HW      H4B     H4B  OSR/2SS/SCC  24.026682

26       3568         HW     H3AB     H3B  OSR/2SS/SCC  57.934207

27       3565         HW      H4B     H4B  OSR/2SS/SCC  33.545768

28       3605         HW     H3AB     H3B  OSR/2SS/SCC  74.424945

29       3580         HS     HS3B    HS3B  OSR/2SS/SCC   8.062028

30       3571         HW     H3AB     H3A  OSR/2SS/SCC  30.718121

31       3562         HW     H3AB     H3B  OSR/2SS/SCC  22.774026

32       3110         SW      S3C     S3C  OSR/2SS/SCC   2.240600

33     3120.1         SH     SH3B    SH3B  OSR/2SS/SCC   3.726728





python pandas pivot-table




python pandas pivot-table






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 2 hours ago









Parfait

47.6k84065




47.6k84065










asked 5 hours ago









Clickinaway

12015




12015








  • 1




    can you do something like this: df.loc[('', 'TOTAL','','',''), :] = df['ACRES'].sum()
    – Chris
    5 hours ago








  • 1




    Have a look at the solutions to Pivot table subtotals in Pandas
    – ALollz
    5 hours ago








  • 1




    @ALollz I've actually been trying to make that work for the last 30 minutes or so but no luck.
    – Clickinaway
    4 hours ago






  • 1




    @Clickinaway I am not getting an error on my test data you should be able to do df.loc[('', 'Grand Total','','',''), :] = df[df.index.get_level_values(1) != 'TOTAL'].sum()
    – Chris
    4 hours ago






  • 1




    Please set up a reproducible example including all import lines and data we can run in our empty Python environments.
    – Parfait
    4 hours ago














  • 1




    can you do something like this: df.loc[('', 'TOTAL','','',''), :] = df['ACRES'].sum()
    – Chris
    5 hours ago








  • 1




    Have a look at the solutions to Pivot table subtotals in Pandas
    – ALollz
    5 hours ago








  • 1




    @ALollz I've actually been trying to make that work for the last 30 minutes or so but no luck.
    – Clickinaway
    4 hours ago






  • 1




    @Clickinaway I am not getting an error on my test data you should be able to do df.loc[('', 'Grand Total','','',''), :] = df[df.index.get_level_values(1) != 'TOTAL'].sum()
    – Chris
    4 hours ago






  • 1




    Please set up a reproducible example including all import lines and data we can run in our empty Python environments.
    – Parfait
    4 hours ago








1




1




can you do something like this: df.loc[('', 'TOTAL','','',''), :] = df['ACRES'].sum()
– Chris
5 hours ago






can you do something like this: df.loc[('', 'TOTAL','','',''), :] = df['ACRES'].sum()
– Chris
5 hours ago






1




1




Have a look at the solutions to Pivot table subtotals in Pandas
– ALollz
5 hours ago






Have a look at the solutions to Pivot table subtotals in Pandas
– ALollz
5 hours ago






1




1




@ALollz I've actually been trying to make that work for the last 30 minutes or so but no luck.
– Clickinaway
4 hours ago




@ALollz I've actually been trying to make that work for the last 30 minutes or so but no luck.
– Clickinaway
4 hours ago




1




1




@Clickinaway I am not getting an error on my test data you should be able to do df.loc[('', 'Grand Total','','',''), :] = df[df.index.get_level_values(1) != 'TOTAL'].sum()
– Chris
4 hours ago




@Clickinaway I am not getting an error on my test data you should be able to do df.loc[('', 'Grand Total','','',''), :] = df[df.index.get_level_values(1) != 'TOTAL'].sum()
– Chris
4 hours ago




1




1




Please set up a reproducible example including all import lines and data we can run in our empty Python environments.
– Parfait
4 hours ago




Please set up a reproducible example including all import lines and data we can run in our empty Python environments.
– Parfait
4 hours ago












1 Answer
1






active

oldest

votes

















up vote
1
down vote



accepted










Based on your example that is posted:



# read your data from clipboard

df = pd.read_clipboard()



# run your pivot_table code from above

report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df:  sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'],aggfunc=np.sum, margins=True,margins_name= 'TOTAL'))



# this is creating a new row at level(1) called grand total

# set it equal to the sum of ACRES where level(1) != 'Total' so you are not counting the  calculated totals in the total sum

report.loc[('', 'Grand Total','','',''), :] = report[report.index.get_level_values(1) != 'TOTAL'].sum()

report





                                                     ACRES

SUPER_TYPE  STRATA  OS_TYPE STAND_NUMB  SILV_PRES   

HS          HS3B    HS3B      3092.0    OSR/2SS/SCC 17.3

                              3580.0    OSR/2SS/SCC 8.1

                              3581.0    OSR/2SS/SCC 16.6

                              3587.0    OSR/2SS/SCC 13.8

                              3594.0    OSR/2SS/SCC 31.7

                              3607.0    OSR/2SS/SCC 27.7

            TOTAL                                   115.2

HW          H3AB       H3A    3571.0    OSR/2SS/SCC 30.7

                              3573.0    OSR/2SS/SCC 30.4

                              3585.0    OSR/2SS/SCC 25.8

                              3588.0    OSR/2SS/SCC 18.1

                              3589.0    OSR/2SS/SCC 54.7

                              3597.0    OSR/2SS/SCC 41.6

                              3601.0    OSR/2SS/SCC 11.9

                     . . . 



         Grand Total                                813.6





share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53266032%2fadding-a-grand-total-to-a-pandas-pivot-table%23new-answer', 'question_page');
    }
    );

    Post as a guest


















    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    1
    down vote



    accepted










    Based on your example that is posted:



    # read your data from clipboard
    
df = pd.read_clipboard()
    

    
# run your pivot_table code from above
    
report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df:  sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'],aggfunc=np.sum, margins=True,margins_name= 'TOTAL'))
    

    
# this is creating a new row at level(1) called grand total
    
# set it equal to the sum of ACRES where level(1) != 'Total' so you are not counting the  calculated totals in the total sum
    
report.loc[('', 'Grand Total','','',''), :] = report[report.index.get_level_values(1) != 'TOTAL'].sum()
    
report
    

    

    
                                                     ACRES
    
SUPER_TYPE  STRATA  OS_TYPE STAND_NUMB  SILV_PRES   
    
HS          HS3B    HS3B      3092.0    OSR/2SS/SCC 17.3
    
                              3580.0    OSR/2SS/SCC 8.1
    
                              3581.0    OSR/2SS/SCC 16.6
    
                              3587.0    OSR/2SS/SCC 13.8
    
                              3594.0    OSR/2SS/SCC 31.7
    
                              3607.0    OSR/2SS/SCC 27.7
    
            TOTAL                                   115.2
    
HW          H3AB       H3A    3571.0    OSR/2SS/SCC 30.7
    
                              3573.0    OSR/2SS/SCC 30.4
    
                              3585.0    OSR/2SS/SCC 25.8
    
                              3588.0    OSR/2SS/SCC 18.1
    
                              3589.0    OSR/2SS/SCC 54.7
    
                              3597.0    OSR/2SS/SCC 41.6
    
                              3601.0    OSR/2SS/SCC 11.9
    
                     . . . 
    

    
         Grand Total                                813.6





    share|improve this answer

























      up vote
      1
      down vote



      accepted










      Based on your example that is posted:



      # read your data from clipboard
      
df = pd.read_clipboard()
      

      
# run your pivot_table code from above
      
report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df:  sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'],aggfunc=np.sum, margins=True,margins_name= 'TOTAL'))
      

      
# this is creating a new row at level(1) called grand total
      
# set it equal to the sum of ACRES where level(1) != 'Total' so you are not counting the  calculated totals in the total sum
      
report.loc[('', 'Grand Total','','',''), :] = report[report.index.get_level_values(1) != 'TOTAL'].sum()
      
report
      

      

      
                                                     ACRES
      
SUPER_TYPE  STRATA  OS_TYPE STAND_NUMB  SILV_PRES   
      
HS          HS3B    HS3B      3092.0    OSR/2SS/SCC 17.3
      
                              3580.0    OSR/2SS/SCC 8.1
      
                              3581.0    OSR/2SS/SCC 16.6
      
                              3587.0    OSR/2SS/SCC 13.8
      
                              3594.0    OSR/2SS/SCC 31.7
      
                              3607.0    OSR/2SS/SCC 27.7
      
            TOTAL                                   115.2
      
HW          H3AB       H3A    3571.0    OSR/2SS/SCC 30.7
      
                              3573.0    OSR/2SS/SCC 30.4
      
                              3585.0    OSR/2SS/SCC 25.8
      
                              3588.0    OSR/2SS/SCC 18.1
      
                              3589.0    OSR/2SS/SCC 54.7
      
                              3597.0    OSR/2SS/SCC 41.6
      
                              3601.0    OSR/2SS/SCC 11.9
      
                     . . . 
      

      
         Grand Total                                813.6





      share|improve this answer























        up vote
        1
        down vote



        accepted







        up vote
        1
        down vote



        accepted






        Based on your example that is posted:



        # read your data from clipboard
        
df = pd.read_clipboard()
        

        
# run your pivot_table code from above
        
report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df:  sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'],aggfunc=np.sum, margins=True,margins_name= 'TOTAL'))
        

        
# this is creating a new row at level(1) called grand total
        
# set it equal to the sum of ACRES where level(1) != 'Total' so you are not counting the  calculated totals in the total sum
        
report.loc[('', 'Grand Total','','',''), :] = report[report.index.get_level_values(1) != 'TOTAL'].sum()
        
report
        

        

        
                                                     ACRES
        
SUPER_TYPE  STRATA  OS_TYPE STAND_NUMB  SILV_PRES   
        
HS          HS3B    HS3B      3092.0    OSR/2SS/SCC 17.3
        
                              3580.0    OSR/2SS/SCC 8.1
        
                              3581.0    OSR/2SS/SCC 16.6
        
                              3587.0    OSR/2SS/SCC 13.8
        
                              3594.0    OSR/2SS/SCC 31.7
        
                              3607.0    OSR/2SS/SCC 27.7
        
            TOTAL                                   115.2
        
HW          H3AB       H3A    3571.0    OSR/2SS/SCC 30.7
        
                              3573.0    OSR/2SS/SCC 30.4
        
                              3585.0    OSR/2SS/SCC 25.8
        
                              3588.0    OSR/2SS/SCC 18.1
        
                              3589.0    OSR/2SS/SCC 54.7
        
                              3597.0    OSR/2SS/SCC 41.6
        
                              3601.0    OSR/2SS/SCC 11.9
        
                     . . . 
        

        
         Grand Total                                813.6





        share|improve this answer












        Based on your example that is posted:



        # read your data from clipboard
        
df = pd.read_clipboard()
        

        
# run your pivot_table code from above
        
report = df.groupby(['SUPER_TYPE']).apply(lambda sub_df:  sub_df.pivot_table(index=['STRATA', 'OS_TYPE', 'STAND_NUMB', 'SILV_PRES'], values=['ACRES'],aggfunc=np.sum, margins=True,margins_name= 'TOTAL'))
        

        
# this is creating a new row at level(1) called grand total
        
# set it equal to the sum of ACRES where level(1) != 'Total' so you are not counting the  calculated totals in the total sum
        
report.loc[('', 'Grand Total','','',''), :] = report[report.index.get_level_values(1) != 'TOTAL'].sum()
        
report
        

        

        
                                                     ACRES
        
SUPER_TYPE  STRATA  OS_TYPE STAND_NUMB  SILV_PRES   
        
HS          HS3B    HS3B      3092.0    OSR/2SS/SCC 17.3
        
                              3580.0    OSR/2SS/SCC 8.1
        
                              3581.0    OSR/2SS/SCC 16.6
        
                              3587.0    OSR/2SS/SCC 13.8
        
                              3594.0    OSR/2SS/SCC 31.7
        
                              3607.0    OSR/2SS/SCC 27.7
        
            TOTAL                                   115.2
        
HW          H3AB       H3A    3571.0    OSR/2SS/SCC 30.7
        
                              3573.0    OSR/2SS/SCC 30.4
        
                              3585.0    OSR/2SS/SCC 25.8
        
                              3588.0    OSR/2SS/SCC 18.1
        
                              3589.0    OSR/2SS/SCC 54.7
        
                              3597.0    OSR/2SS/SCC 41.6
        
                              3601.0    OSR/2SS/SCC 11.9
        
                     . . . 
        

        
         Grand Total                                813.6






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 1 hour ago









        Chris

        1,161110




        1,161110






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53266032%2fadding-a-grand-total-to-a-pandas-pivot-table%23new-answer', 'question_page');
            }
            );

            Post as a guest






































































            -

            Popular posts from this blog

            How to change which sound is reproduced for terminal bell?

            Image
            3 Recently upgrade to 18.10 from 18.04, and using the terminal is becoming annoying: the new alert sound is a drip ( /usr/share/sounds/gnome/default/alerts/drip.ogg ), and the previous one is still located at ( /usr/share/sounds/ubuntu/stereo/bell.ogg ). My issue is that at the gnome-terminal, when autocompleting or pressing left BOTH sounds are reproduced, at the same time. I want to just have ubuntu/stereo/bell.ogg active, not both . At the moment I have been only able to reduce the volume of the alerts (but still both are played), or turn the alerts down (but I dislike both "solutions"). gnome-terminal 18.10 teminal-bell share | improve this question
            Read more

            Title Spacing in Bjornstrup Chapter, Removing Chapter Number From Contents

            Image
            up vote 5 down vote favorite 2 I would like to make it so that the vertical space below the title of each chapter is the same when using the Bjornstrup package (i.e. so that the descenders do not affect the spacing - as shown by the red arrow in the picture below). I think I'm fairly happy with the spacing around "A chapter", so I'd really like titles without descenders (e.g. "Contents") to have the same vertical spacing as "A chapter" below the title. I would also like to remove the large "0" in the contents title. Any help appreciated, as always. documentclass[11pt,a4paper]{book} % Packages[![enter image description here][1]][1] usepackage[left=3.5cm, right=3.5cm, top=4.3cm, bottom=4.25cm]{geometry} usepackage[utf8]{inputenc} usepackage{libertine} usepackage[libertine]{newtx
            Read more

            Can I use Tabulator js library in my java Spring + Thymeleaf project?

            Image
            0 I want to add Tabulator js to my java project. I added tabulator.min.js, tabulator.min.css to resources folder. After that added to my html page these dependencies as recommending in readme.md. <link rel="stylesheet" th:href="@{/css/tabulator.min.css}"> <script type="text/javascript" th:src="@{/js/tabulator.min.js}"></script> Now my tables view all elements, that I have in the html page, but not correct. In the documentation of Tabulator is example how to create header and table, var table = new Tabulator("#example-table", { columns:[ {title:"Name", field:"name", sorter:"string", width:200, editor:true}, {title:"Age", field:"age", sorter:"
            Read more