第8节，用pandas 制作excel透视表

先来看数据

接下来逐步讲解如何用pandas制作透视表，首先读取数据

import pandas as pd
import numpy as np


filename = "./data/透视表.xlsx"
df = pd.read_excel(filename)

1. 指定index

制作透视表，需要使用pivot_table函数，必须为它指定一个index，这里我们要以店铺为单位查看销售额和利润的平均值

res = pd.pivot_table(df, index=[u'店铺'], aggfunc=[np.mean])
print(res)

输出结果

     mean        
       利润     销售额
店铺               
1店  16.25  105.00
2店  16.50  111.25

如果不指定aggfunc参数，默认也是会计算平均值。

将index 设置为季度就会以季度为单位进行统计

res = pd.pivot_table(df, index=[u'季度'], aggfunc=[np.mean])
print(res)

输出结果

    mean       
      利润    销售额
季度             
冬   17.5  107.5
夏   13.5  107.5
春   15.0  105.0
秋   19.5  112.5

将店铺和季度都设置为index是什么效果呢

res = pd.pivot_table(df, index=['店铺', '季度'], aggfunc=[np.mean])
print(res)

输出结果

      mean     
        利润  销售额
店铺 季度          
1店 冬    20  115
   夏    10   95
   春    15  100
   秋    20  110
2店 冬    15  100
   夏    17  120
   春    15  110
   秋    19  115

2. 设置values

如果表有很多列，在制作透视表时，未必都需要，可以通过values参数来指定需要的列

res = pd.pivot_table(df, index=['店铺'], aggfunc=[np.mean], values=['销售额'])
print(res)

输出结果

      mean
       销售额
店铺        
1店  105.00
2店  111.25

3. 设置columns

res = pd.pivot_table(df, index=['店铺'], columns=['季度'] ,aggfunc=[np.mean], values=['销售额'])
print(res)

输出结果

   mean               
    销售额               
季度    冬    夏    春    秋
店铺                    
1店  115   95  100  110
2店  100  120  110  115

4. 写入excel

这一次写excel，既不写入新的excle，也不写入到新的sheet中，而是在原文件的sheet 中写入

import pandas as pd
import numpy as np
from openpyxl import load_workbook


filename = "./data/透视表.xlsx"

book = load_workbook(filename)
df = pd.read_excel(filename)

res = pd.pivot_table(df, index=['店铺'], columns=['季度'] ,aggfunc=[np.mean], values=['销售额'])

# 让数据能够回写到原来的sheet中
writer = pd.ExcelWriter(filename, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

res.to_excel(writer, sheet_name="透视表", startrow=12)

writer.save()

最终效果