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:

Add the Subtotal and Grand Total for 'ArTotal', 'ArCarr' e 'ArProd' fields
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.

`table.groupby(level=0).sum()` is what you need. You can just cut those rows out and paste them in an excel. — cs95, Sep 14 '17 at 13:18
Thanks @cᴏʟᴅsᴘᴇᴇᴅ but I need to do this with pandas, the ultimate goal is to render the result in a PDF from an HTML using df.to_html() — Franklin Januário, Sep 14 '17 at 13:27

Zero · Accepted Answer · 2017-09-14T13:49:20.287

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

I took a groupby approach let me know what you think about my solution to the problem too. — Bharath M Shetty, Sep 14 '17 at 14:19
Thanks John, Trying to assimilate logic, if I have more columns to add than 'Variedade', is returned "Empty DataFrame" can you tell me why? — Franklin Januário, Sep 14 '17 at 15:32
`values = ['Ár. Produção', 'Ár. Carreador', 'Ár. Total'] index = ['Quadra','Talhao','Variedade', 'Data Plantio','Corte Atual', 'Último Corte', 'TCH Anterior', 'Estimativa', 'TCH Atual', 'Observações'] table = pd.pivot_table(df, values=values,index=index, fill_value=0).reset_index(level=-1) print(table)` Results: Empty DataFrame Columns: [Corte Atual, Ár. Carreador, Ár. Produção, Ár. Total] Index: [] — Franklin Januário, Sep 14 '17 at 17:19

score 1 · Answer 2 · answered Sep 14 '17 at 14:17

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

Thanks @Bharathshetty , trying with this columns: `cols = ['Fazenda','Quadra', 'Talhao', 'Ár. Produção', 'Ár. Carreador', 'Ár. Total', 'Variedade', 'Data Plantio', 'Corte Atual', 'Último Corte', 'TCH Anterior', 'Estimativa', 'TCH Atual', 'Observações']` I get this: ValueError: labels ['Variedade' 'Data Plantio' 'Último Corte' 'Observações'] not contained in axis — Franklin Januário, Sep 14 '17 at 15:51

Add columns to pivot table with pandas

2 Answers2