python创建excel图表_用python在Excel中创建图表

我用基本的排序和累积函数做一些分析。我需要使用在sheet2中创建的数据在excel中创建散点图。在

我正在使用工作表、添加图表、添加系列、插入图表功能。但无法在同一工作表中创建图表。在

代码如下：# -*- coding: utf-8 -*-

"""

Created on Tue Nov 07 15:25:03 2017

@author: Harsh

"""

import numpy as np

import pandas as pd

import xlsxwriter

from pandas import ExcelWriter

from pandas import ExcelFile

#Define variables for storing file names and sheet names

input_file_path="C:\Users\paha7003\Documents\Adhoc Work\Training\python_work\OUT_Base_Sannap_1010.xls";

sheet_name = "Sheet1"

output_file_path="C:\Users\paha7003\Documents\Adhoc Work\Training\python_work\Pareto_Sannap_1010.xlsx";

#Read data from file

full_data = pd.read_excel(input_file_path, sheet_name)

#Pareto will be run on category, hence change the variable name

only_category = full_data.loc[full_data['PrdLevel_Name'] == 'CATEGORY_NAME']

#sort based on VAL

catg_sorted = only_category.sort('VAL', ascending = [0])

#Cummilative sum , percenetage and ctg_rank binning

catg_sorted['cum_sum'] = catg_sorted.SALVAL.cumsum()

catg_sorted['cum_perc'] = 100*catg_sorted.cum_sum/catg_sorted.SALVAL.sum()

bins = [0, 5, 10,15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85, 90, 95, 100]

labels = [5, 10,15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85, 90, 95, 100]

catg_sorted['ctg_rank'] = pd.cut(catg_sorted['cum_perc'], bins=bins, labels=labels)

#Merge the CTG ranks calculated on the cateogry to other level in the out file

final = pd.merge(full_data , catg_sorted, how = 'inner' , on='STORECODE' )

pivot = pd.pivot_table(final, values=['WDVAL_x' , 'PROJN_x'], index=['ctg_rank'], aggfunc=np.sum)

pivot['cum_sum_wdval'] = pivot.WDVAL_x.cumsum()

pivot['cum_sum_projn'] = pivot.PROJN_x.cumsum()

pivot['cum_perc_wdval'] = 100*pivot.cum_sum_wdval/pivot.WDVAL_x.sum()

pivot['cum_perc_projn'] = 100*pivot.cum_sum_projn/pivot.PROJN_x.sum()

#Writing output data to new file

writer = pd.ExcelWriter(output_file_path , engine='xlsxwriter')

full_data.to_excel(writer,'Sheet1')

pivot.to_excel(writer, 'Sheet2')

writer.save()

workbook = writer.book

worksheet = writer.sheets['Sheet2']

chart5 = workbook.add_chart({'type': 'scatter','subtype': 'smooth'})

# Configure the first series.

chart5.add_series({

'name': ['Sheet2', 2, 0],

'categories': ['Sheet2', 2, 0, 4, 0],

'values': ['Sheet2', 2, 6, 4, 6],

})

# Add a chart title and some axis labels.

chart5.set_title ({'name': 'Pareto Graph'})

chart5.set_x_axis({'name': '#Stores'})

chart5.set_y_axis({'name': 'Wdval'})

# Set an Excel chart style.

chart5.set_style(15)

worksheet.insert_chart('M7', chart5)

workbook.close()

writer.save()