Add columns to pivot table with pandas

Question

I have the table as follow:

import pandas as pd
import numpy as np

#simple table
fazenda = [6010,6010,6010,6010]
quadra = [1,1,2,2]
talhao = [1,2,3,4]
arTotal = [32.12,33.13,34.14,35.15]
arCarr = [i/2 for i in arTotal]
arProd = [i/2 for i in arTotal]
varCan = ['RB1','RB2','RB3','RB4']
data = list(zip(fazenda,quadra,talhao,arTotal,arCarr,arProd,varCan))

#Pandas DataFrame
df = pd.DataFrame(data=data,columns=['Fazenda','Quadra','Talhao','ArTotal','ArCarr','ArProd','Variedade'])

#Pivot Table
table = pd.pivot_table(df, values=['ArTotal','ArCarr','ArProd'],index=['Quadra','Talhao'], fill_value=0)

print(table)

resulting in this:

               ArCarr  ArProd  ArTotal
Quadra Talhao                         
1      1       16.060  16.060    32.12
       2       16.565  16.565    33.13
2      3       17.070  17.070    34.14
       4       17.575  17.575    35.15

I need two aditional steps:

1. Add the Subtotal and Grand Total for 'ArTotal', 'ArCarr' e 'ArProd' fields
2. Add 'Variedade' field to table

I tried to add the column but the result was incorrect. Following some links about Total and Grand Total, I did not get the satisfactory result.

I'm having a hard time understanding pandas, I ask for help from more experienced colleagues.

Answer 1

I think John's solution beats me, but based on your current output you cant do that with pivot table you can have a series of steps using list comprehension of grouped data and then append the sums to do that i.e.

cols = ['Fazenda','Variedade','Quadra','Talhao']
ndf = pd.concat([i.append(i.drop(cols,1).sum(),1) for _,i in df.groupby('Quadra')])

ndf['Talhao'] = ndf[['Talhao']].fillna('Total')
ndf['Quadra'] = ndf['Quadra'].ffill()

new = ndf.set_index(['Quadra','Talhao']).drop(['Fazenda'],1)

new = new.append(pd.DataFrame(df.sum()).T.drop(cols,1).set_index(pd.MultiIndex.from_tuples([('Grand Total', '')]))).fillna('')

Output:

                    ArCarr  ArProd  ArTotal Variedade
Quadra      Talhao                                   
1.0         1.0     16.060  16.060    32.12       RB1
            2.0     16.565  16.565    33.13       RB2
            Total   32.625  32.625    65.25          
2.0         3.0     17.070  17.070    34.14       RB3
            4.0     17.575  17.575    35.15       RB4
            Total   34.645  34.645    69.29          
Grand Total         67.270  67.270   134.54          

Answer 2

Get the pivot right first.

In [404]: values = ['ArTotal','ArCarr','ArProd']

In [405]: table = pd.pivot_table(df, values=values, index=['Quadra','Talhao','Variedade'], 
                                 fill_value=0).reset_index(level=-1)

Get Grand totals

In [406]: Gt = table[values].sum()

Get Quadra level totals

In [407]: St = table.sum(level='Quadra')

Using append reshape the table

In [408]: (table.append(
                 St.assign(Talhao='Total').set_index('Talhao', append=True)
                ).sort_index()
                .append(pd.DataFrame([Gt.values], columns=Gt.index,
                                     index=pd.MultiIndex.from_tuples([('Grand Total', '')],
                                     names=['Quadra', 'Talhao']))
                ).fillna(''))
Out[408]:
                    ArCarr  ArProd  ArTotal Variedade
Quadra      Talhao
1           1       16.060  16.060    32.12       RB1
            2       16.565  16.565    33.13       RB2
            Total   32.625  32.625    65.25
2           3       17.070  17.070    34.14       RB3
            4       17.575  17.575    35.15       RB4
            Total   34.645  34.645    69.29
Grand Total         67.270  67.270   134.54

Details

In [409]: table
Out[409]:
              Variedade  ArCarr  ArProd  ArTotal
Quadra Talhao
1      1            RB1  16.060  16.060    32.12
       2            RB2  16.565  16.565    33.13
2      3            RB3  17.070  17.070    34.14
       4            RB4  17.575  17.575    35.15

In [410]: Gt
Out[410]:
ArTotal    134.54
ArCarr      67.27
ArProd      67.27
dtype: float64

In [411]: St
Out[411]:
        ArCarr  ArProd  ArTotal
Quadra
1       32.625  32.625    65.25
2       34.645  34.645    69.29