制作图表展示

1、制作柱形图展示数据的对比关系

import xlwings as xw
app = xw.App(visible = True, add_book = False)
workbook = app.books.open('员工销售表.xlsx')
for i in workbook.sheets:
chart = i.chart.add(left = 200, top = 0, width = 55, height = 211)
chart.set_source_data(i['A1'].expand())
chart.chart_type = 'column_clustered' # 制作柱形图
workbook.save('柱形图.xlsx')
workbook.close()
app.quit()

# column_clustered:柱形图 pie:饼图 bar_clustered:条形图 doughnut:圆环图
# line：折线图 xy_scatter:散点图 area:面积图 radar:雷达图

# 2、批量条形图

import xlwings as xw
app = xw.App(visible = True, add_book = Fasle)
workbook = app.books.open('员工销售表.xlsx')
for i in workbook.sheets:
chart = i.charts.add(left = 200, top = 0, width = 355, height = 211)
chart.set_source_data(i['A1'].expand('table'))
chart.chart_type = 'bar_clustered' # 制作条形图
workbook.save('条形图.xlsx')
workbook.close()
app.quit()

# 3、制作折线图展示趋势

import pandas as pd
import matplotlib as plt
import xlwings as xw
df = pd.read_excel('月销售表.xlsx')
figure = plt.figure() # 创建一个绘图窗口
plt.rcParams['font.san-serif']=['SimHei']
plt.rcParams['axes.unicode_minus']=False
x = df['月份']
y = df['销售额']
plt.plot(x, y, color = 'red', linewidth = 3, linestyle = 'solid')
plt.title(lable = '月销售额趋势图', fontdict = {'color': 'black', 'size': 30}, loc = 'center')
for a,b in zip(x,y):
plt.text(a, b+0.2, (a, '%.0f' % b), ha = 'center', va = 'bottom', fontsize = 10) # 添加并设置数据标签
plt.axis('off') # 隐藏坐标轴
app = xw.App(visible = False)
workbook = app.books.open('月销售表.xlsx')
worksheet = workbook.sheets['Sheet1'] # 选中工作表Sheet1
worksheet.pictures.add(figure, name = '图片1', update = True, left = 200)
workbook.save('折线图.xlsx')
workbook.close()
app.quit()

# 4、折线图最高点添加数据标签

import pandas as pd
import matplotlib as plt
import xlwings as xw
df = pd.read_excel('月销售表.xlsx')
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
x = df['月份']
y = df['销售额']
plt.plot(x, y, color = 'red', linewidth = 3, linestyle = 'solid')
plt.title(lable = '月销售额趋势图', fontdict = {'color': 'black', 'size': 30}, loc = 'center')
max1 = df['销售额'].max()
df_max = df[df['销售额'] == max] # 选取最高销售额对应的行数据
for a,b in zip(df_max['月份'], df_max['销售额']):
plt.text(a, b + 0.05, (a, '%.0f' % b), ha = 'center', va = 'bottom', fontsize = 10) # 为最高点添加数据标签
plt.axis('off')
app = xw.App(visible = False)
workbook = app.books.open('月销售表.xlsx')
worksheet = workbook.sheets[Sheet1]
worksheet.pictures.add(figure, name = '图片1', update = True, left = 200)
workbook.save('显示最高点数据标签的折线图.xlsx')
workbook.close()
app.quit()

# 5、制作平滑折线图

import pandas as pd
import matplotlib as plt
import numpy as np
from scipy import interpolate
import xlwings as xw
df = pd.read_excel('月销售额.xlsx')
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
x = df['月份']
y = df['销售额']
xnew = np.arange(1, 12, 0.1)
func = interpolate.interpld(x, y, kind = 'cubic')
ynew = func(xnew)
plt.plot(xnew, ynew, color = 'red', linewidth = 3, linestyle = 'solid') # 制作平滑折线图
plt.title(label = '月销售额趋势图', fontdict = {'color': 'black', 'size': 30}, loc = 'center')
plt.xlabel('月份', donfdict = {'family': 'SimSun', 'color': 'black', 'size': 20}, labelpad = 20)
plt.ylabel('销售额', fontdict = {'family': 'SimSun', 'color': 'black', 'size': 20}, labelpad = 20)
plt.xlim(0, 12) # 设置图表X轴的取值范围
app = xw.App(visible = False)
workbook = app.books.open('月销售表.xlsx')
worksheet = workbook.sheets['Sheet1']
worksheet.pictures.add(figure, name = '图片1', update = True, left = 200)
workbook.save('平滑折线图.xlsx')
workbook.close()
app.quit()

# 6、散点图判断两组数据的关系

import pandas as pd
import matplotlib as plt
import xlwings as xw
df = pd.read_excel('汽车速度和刹车距离表.xlsx')
figure = plt.figure() # 绘制一个绘图窗口
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
x = df['汽车速度（km/h）']
y = df['刹车距离（m）']
# 制作散点图
plt.scatter(x, y, s = 400, color = 'red', marker = 'o', edgecolor = 'black')
# 添加并设置X轴
plt.xlabel1('汽车速度（km/h）', fontdict = {'family': 'Microsoft YaHei', 'color': 'black', 'size': 20}, labelpad = 20)
# 添加并设置y轴标题
plt.ylabel('刹车距离(m)', fontdict = {'family' : 'Microsoft YaHei', 'color' : 'black', 'size' : 20}, labelpad = 20)
plt.title('汽车速度与刹车距离关系图', fontdict = {'family': 'Microsoft YaHei', 'color': 'black', 'size': 30}, loc = 'center')
app = xw.App(visible = False)
workbook = app.books.open('汽车速度与刹车距离表.xlsx')
worksheet = workbook.sheets[0]
worksheet.pictures.add(figure, name = '图片1', update = True, left = 200)
workbook.save('散点图.xlsx')
workbook.close()
app.quit()
'''
# scatter(x,y,s,color,marker,linewidth,edgecolor)
# x:x坐标的值
# y：y坐标的值
# s：每个点的面积，如果有多个值，则每个点的大小都不一样，变成气泡图
# color:每个点的填充颜色
# marker：每个点的形状。
# linewidth：每个点的边框粗细
# edgecolor：每个点的边框颜色
'''

# 7、为散点图添加线性趋势图

import pandas as pd
import matplotlib as plt
import xlwings as xw
from xklearn import linear_model
df = pd.read_excel('距离表.xlsx')
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
x = df['汽车速度（km/h）']
y = df['刹车距离（m）']
plt.scatter(x, y, s= 400, color = 'red', marker = 'o', edgecolor = 'black')
plt.xlabel('汽车速度（km/h）', fontdict = {'family': 'Microsoft YaHei', 'color': 'black', 'size': 20}, labelpad = 20)
plt.ylabel('刹车距离（m）', fontdict = {'family': 'Microsoft YaHei', 'color': 'black', 'size': 20}, labelpad = 20)
plt.title('汽车速度与刹车距离关系图', fontdict = {'family' : 'Micro-soft YaHei', 'color' : 'black', 'size' : 30}, loc = 'center')
model = linear_model.LinearReGression().fit(x.values.reshape(-1,1), y)
pred = model.predict(x.values.reshape(-1, 1))
plt.plot(x, pred, color = 'black', linewidth = 3, linestyle = 'solid', label = '线性趋势线') # 绘制线性趋势线
plt.legend(loc = 'upper left')
app = xw.App(visible = False)
workbook = app.books.open('距离表.xlsx')
worksheet = workbook.sheets['表1']
worksheet.pictures.add(figure, name = '图片1', update = True, left = 200)
workbook.save('为散点图添加线性趋势线.xlsx')
workbook.close()
app.quit()

# 8、制作气泡图

import pandas as pd
import matplotlib as plt
import xlwings as xw
df = pd.read_excel('气泡图.xlsx')
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
x = df['销售量']
y = df['利润（万）']
z = df['产品名称']
# 制作气泡图
plt.scatter(x, y, s = y * 100, color = 'red', marker = 'o')
plt.xlabel('销售量', fontdict = {'family': 'Microsoft Yahei', 'color': 'black', 'size': 20}, labelpad = 20)
plt.ylabel('利润(万)', fontdict = {'family': 'Microsoft Yahei', 'color': 'black', 'size': 20}, labelpad = 20)
plt.title('销售量与利润关系图', fontdict = {'family': 'Microsoft Yahei', 'color': 'black', 'size': 20}, loc = 'center')
for a, b, c in zip(x, y, z):
plt.text(a, b, c, ha = 'center', va = 'center', fontsize = 30, color = 'white')
plt.xlim(0, 800)
plt.ylim(0,120)
app = xw.App(visible = False)
workbook = app.books.open('气泡图.xlsx')
worksheet = workbook.sheets[0]
worksheet.pictures.add(figure, name = '图片1', update = True, left = 200)
workbook.save('气泡图1.xlsx')
workbook.close()
app.quit()

# 9、制作气泡图

import pandas as pd
import matplotlib as plt
import xlwings as xw
df = pd.read_excel('气泡图.xlsx')
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
x = df['销售量']
y = df['利润(万)']
z = df['产品名称']
# 制作气泡图
plt.scatter(x, y, s = y * 100, color = 'red', marker = 'o')
plt.xlabel('销售量', fontdict = {'family': 'Microsoft Yahei', 'color': 'black', 'size': 20}, labelpad = 20)
plt.ylabel('利润（万）', fontdict = {'family': 'Microsoft Yahei', 'color': 'black', 'size': 20}, labelpad = 20)
plt.title('销量与利润关系图', fontdict = {'family': 'Microsoft Yahei', 'color': 'black', 'size': 30}, loc = 'center')
for a,b,c in zip(x,y,z):
plt.text(a, b, c, ha = 'center', va = 'center', fontsize = 30, color = 'white')
plt.xlim(0, 800)
plt.ylix(0, 120)
app = xw.App(visible = False)
workbook = app.books.open('气泡图.xlsx')
worksheet = workbook.sheets[0]
worksheet.pictures.add(figure, name = '图片1', update = True, left = 20)
workbook.save('气泡图1.xlsx')
workbook.close()
app.quit()

# 10、制作饼图展示部分和总体的比例关系

import pandas as pd
import matplotlib as plt
import xlwings as xw
df = pd.read_excel('')
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = Fasle
x = df['产品名称']
y = df['销售额']
# 制作饼图并分离饼图块
plt.pie(y, labels = x, labeldistance = 1.1, autopct = '%.2f%%', pctdistance = 0.8, startangle = 90,
radius =1.0, explode = [1, 0, 0, 0, 0.3, 0])
plt.title(label = '产品销售额占比图', fontdict = {'color': 'black', 'size': 30}, loc = 'center')
app = xw.App(visible = False)
workbook = app.books.open('饼图.xlsx')
worksheet = workbook.sheetsp[0]
worksheet.pictures.add(figure, name = '图片1', update = True, left = 200)
workbook.save()
workbook.close()
app.quit()
'''
pie(x,explode,labels,colors,autopct,pctdistance,shadow,labeldistance,startangle,radius,counterclock,center,frame)
x：饼图块的数据系列值
explode：一个列表，指定每个饼图块与圆心的距离
labels/colors:每个饼图块的数据标签内容/颜色
autopct：每个饼图块的百分比数值的格式
pctdistance：百分比数据值与饼图块中心的距离
shadow：是否为饼图绘制阴阳
labeldistance：数据标签与饼图块中心的距离
startangle:数据的第一个值对应的饼图块在饼图中的初始角度
radius：饼图半径
counterclock:是否让饼图逆时针显示
center：饼图中心位置
frame：是否显示饼图背后的图框
'''

# 11、制作圆环图

import pandas as pd
import matplotlib as plt
import xlwings as xw
df = pd.read_excel('饼图.xlsx')
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
x = df['产品名称']
y = df['销售额']
plt.pie(y, labels = x, autopct = '%.2f%%', pctdistance = 0.85, radius = 1.0, labeldistance = 1.1, wedgeprops = {'width': 0.3, 'linewidth': 2, 'edgecolor': 'white'})
plt.title(label = '产品销售额占比图', fontdict = {'color': 'black', 'size': 30}, loc = 'center')
app = xw.App(visible = False)
workbook = app.books.open('饼图.xlsx')
worksheet = workbook.sheets[0]
worksheet.pictures.add(figure, name = '图片1', update = True, left = 200)
workbook.save()
workbook.close()
app.quit()

# 12、制作雷达图对比多项指标

import pandas as pd
import numpy as np
import matplotlib as plt
df = pd.read_excel('雷达图.xlsx')
df = df.set_index('性能评价指标')
# 转置数据表格
df = df.T
df.index_name = '品牌'
def plot_radar(data, feature):
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
cols = ['动力性', '燃油经济性', '制动性', '通过性', '安全性', '环保性']
colors = ['green', 'blue', 'red', 'yellow']
# 根据要显示的指标个数对圆形进行等分
angles = np.linspace(0.1 * np.pi, 2.1 * np.pi, len(cols), end-point = False)
angles = np.concatenate((angles, [angles[0]])) # 连接刻度线数据
fig = plt.figure(figsize = (8, 8)) # 设置显示图表的窗口大小
# 设置图表在窗口中的显示位置，并设置坐标轴为极坐标体系
ax = fig.add_subplot(111, polar = True)
for i, c in enumerate(feature):
stats = data.loc[] # 获取品牌对应的指标数据
stats = np.concatenate((stats, [stats[0]])) # 连接品牌的指标数据
ax.plot(angles, stats, '-', linewidth = 6, c = colors[i], label = '%s' %(c)) # 制作雷达图
ax.fill(angles, stats, color = colors[i], alpha = 0.25) # 为雷达图填充颜色
ax.legend() # 为雷达图添加图例
ax.set_yticklabels([]) # 隐藏坐标轴数据
ax.set_thetagrids(angles * 180 / np.pi, cols, fontsize = 16) # 设置数据标签
plt.show() # 显示制作的雷达图
return fig
fig = plt.radar(df, ['A品牌', 'B品牌', 'C品牌', 'D品牌']) # 调用自定义函数制作雷达图

# linspace(start,stop,num=50,endpoint=True,retstep=False,dtype=None)
# start/stop:区间的起始终止值
# num:可选，指定生成样本数，非负，默认50
# endpoint：可选，指定终止值stop是否被包含在结果值中，True则有，false则无
# retstep、dtype：一般不使用

13、制作某一品牌性能评价指标雷达图

fig = plot_radar(df, ['A品牌'])

14、制作温度计图展示工作进度

import pandas as pd # 导入pandas模块
import matplotlib as plt # 导入Matplotlib模块
df = pd.read_excel('温度计图.xlsx') # 从指定工作簿中读取数据
sum = 0 # 定义变量sum，用于存储全年的实际销售业绩
for i in range(12): # for i in range(6) 可以统计上半年业绩图
sum = df['销售业绩（万元）'][i] + sum # 累加12个月的实际销售业绩，得到全年的实际销售业绩
goal = df['销售业绩（万元）'][13] # 获取全年的目标销售业绩
percentage = sum / goal # 计算全年的实际销售业绩占目标销售业绩的百分比
plt.bar(1, 1, color = 'yellow') # 制作柱形图展示全年的目标销售业绩，设置填充颜色为黄色
plt.bar(1, percentage, color = 'cyan') # 制作柱形图展示全年的实际销售业绩，设置填充颜色为青色
plt.xlim(0, 2) # 设置图表x轴的取值范围
plt.ylim(0, 1.2) # 设置图表y轴的取值范围
plt.text(1, percentage - 0.01, percentage, ha = 'center', va = 'top',
fontdict = {'color' : 'black', 'size' : 20}) # 添加并设置数据标签
plt.show() # 显示制作的温度计图