pandas生成excel并生成透视图

# coding=utf-8

import pandas as pd

def to_excel_with_charts(filename,**data):
    df = pd.DataFrame(data)
    group_df = df.groupby('url', as_index=False).count()  #分组
    group_df = group_df.sort_values(by='case')          #排序
    size = df.groupby('url', as_index=False).count().size
    writer = pd.ExcelWriter(filename, engine='xlsxwriter')     
    group_df.to_excel(writer, sheet_name='数据透视', index=False)
    df.to_excel(writer, sheet_name='数据详情')
    workbook = writer.book
    worksheet = writer.sheets['数据透视']
    chart = workbook.add_chart({'type': 'column'})  
    chart.add_series({'values': '=数据透视!$B$2:$B${}'.format(size + 1), "categories": '=数据透视!$A$2:$A${}'.format(size + 1)})
    worksheet.insert_chart('D2', chart, options={"x_scale": 2, "y_scale": 2})
    writer.save()

if __name__ == '__main__':
    data = {'case': ["001登录", "001登录1", "002登出", "003路由", "查询", "查询2", "新增"],
            'url': ["login", "login", "login", "router", "query", "query", "add"]}
    # 数据分组后，生成excel，数据详情，以及数据透视，生成分组后个数的透视图
    to_excel_with_charts("charts001.xlsx",**data)

生成excel如下：