我用基本的排序和累积函数做一些分析。我需要使用在sheet2中创建的数据在excel中创建散点图。在
我正在使用工作表、添加图表、添加系列、插入图表功能。但无法在同一工作表中创建图表。在
代码如下:# -*- coding: utf-8 -*-
"""
Created on Tue Nov 07 15:25:03
@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()
如果觉得《python创建excel图表_用python在Excel中创建图表》对你有帮助,请点赞、收藏,并留下你的观点哦!