添加列转动与熊猫

Question

我有表如下表：

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) 

造成这样的：

   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 

我需要两个aditional的步骤：

1. 添加小计和'ArTotal'，'ArCarr'和'ArProd'字段的总计总计
2. 将'Variedade'字段添加到表

我尝试添加列，但结果是不正确的。关于Total和Total Total的一些链接，我没有得到满意的结果。

我很难理解熊猫，我向更有经验的同事寻求帮助。

Answer 1

获得pivot右侧第一个。

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) 

获得总计

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

获取Quadra水平总计

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

使用append重塑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 

详细

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 

Answer 2

我认为约翰的解决方案我百思不得其解，但是根据当前的输出，你不能做到这一点与数据透视表，你可以有一个系列产品采用分组数据的列表理解步骤，然后追加资金来做到这一点即

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('') 

输出：

 
        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