大家新年好哇，今天小编来给大家分享如何在Excel文档当中来绘制可视化图表，并且制作一个可视化大屏，非常的容易，这里我们会用到openpyxl模块，那么首先第一步便是调用该模块来读取Excel文件，代码如下
# 读取Excel文档并且指定工作表的名称
file_name = 'Bike_Sales_Playground.xlsx'
df = pd.read_excel(file_name,sheet_name='bike_buyers')当然为了保险起见，我们这里还是拷贝一份源数据，并且新建一个新的工作表，代码如下
代码语言：javascript复制# 新建一张工作表
with pd.ExcelWriter(file_name,#文档的名称
engine='openpyxl',#调用模块的名称
mode='a',#添加的模式
if_sheet_exists="replace" #如果已经存在，就替换掉
) as writer:
df.to_excel(writer, sheet_name='Working_Sheet',index = False)# 设置Index为False
# 从新的工作表当中来读取数据
df = pd.read_excel(file_name,sheet_name='Working_Sheet')数据清洗
下一步我们进行数据的清洗，例如去掉重复值、针对一些数值做一些替换，代码如下
代码语言：javascript复制# 去掉重复值
df.drop_duplicates(keep='first', inplace=True, ignore_index=False)
# 针对婚姻状况这一列，“已婚”替换成“M”，“单身”替换成“S”
df['Marital Status'] = df['Marital Status'].replace('M','Married').replace('S','Single')
# 针对性别这一列，“男性”替换成“F”，而“女性”替换成“M”
df['Gender'] = df['Gender'].replace('F','Female').replace('M','Male')
# 查看表格的前5行
df.head()
# 年龄数值的处理
df['Age brackets'] = df['Age'].apply(lambda x: 'Less than 30' if x<=30 else('Greater than 55' if x>55 else '31 to 55'))
# 通勤距离的数值的替换
df['Commute Distance'] = df['Commute Distance'].replace('10 Miles','More than 10 Miles')output
代码语言：javascript复制 ID Marital Status Gender ... Age Purchased Bike Age brackets
0 12496 Married Female ... 42 No 31 to 55
1 24107 Married Male ... 43 No 31 to 55
2 14177 Married Male ... 60 No Greater than 55
3 24381 Single Male ... 41 Yes 31 to 55
4 25597 Single Male ... 36 Yes 31 to 55绘制图表
接下来我们尝试来绘制几张可视化图表，下面所示的代码绘制的是柱状图，而绘制其余两张折线图的代码与下面是雷同的
代码语言：javascript复制# 透视表1
# 制作数据透视表
avg_gender_income_df = np.round(pd.pivot_table(bike_df,
values = 'Income',
index = ['Gender'],
columns = ['Purchased Bike'],
aggfunc = np.mean
),2)
# 将数据透视表放入Excel表格中，并且指定工作表
with pd.ExcelWriter(file_name,#工作表的名称
engine='openpyxl',#引擎的名称
mode='a',#Append模式
if_sheet_exists="replace" #如果已经存在，就替换掉
) as writer:
avg_gender_income_df.to_excel(writer, sheet_name='Average_Gender_Income')
# 加载文档，并且指定工作表
wb = load_workbook(file_name)
sheet = wb['Average_Gender_Income']
# 创建柱状图
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "基于性别与消费数据之下的平均收入"
chart1.y_axis.title = '性别'
chart1.x_axis.title = '收入'
# 将绘制出来的柱状图放在单元格中去
data1 = Reference(sheet, min_col=2, min_row=1, max_row=3, max_col=3)#Including Headers
cats1 = Reference(sheet, min_col=1, min_row=2, max_row=3)#Not including headers
chart1.add_data(data1, titles_from_data=True)
chart1.dataLabels = DataLabelList()
chart1.dataLabels.showVal = True
chart1.set_categories(cats1)
chart1.shape = 4
sheet.add_chart(chart1, "A10")
wb.save(file_name)output
生成可视化大屏
我们尝试将绘制完成的图表生成可视化大屏，代码如下
代码语言：javascript复制# 创建一个空的DataFrame表格
title_df = pd.DataFrame()
# 将结果放入至Excel文件当中去
with pd.ExcelWriter(file_name,#工作表的名称
engine='openpyxl',#引擎的名称
mode='a',#Append模式
if_sheet_exists="replace" #如果已经存在，就替换掉
) as writer:
title_df.to_excel(writer, sheet_name='Dashboard')
# 加载文档，指定工作表是哪个
wb = load_workbook(file_name)
sheet = wb['Dashboard']
for x in range(1,22):
sheet.merge_cells('A1:R4')
cell = sheet.cell(row=1, column=1)
cell.value = 'Bike Sales Dashboard'
cell.alignment = Alignment(horizontal='center', vertical='center')
cell.font = Font(b=True, color="F8F8F8",size = 46)
cell.fill = PatternFill("solid", fgColor="2591DB")
# 将绘制出来的图表放置到Excel文档中
sheet.add_chart(chart1,'A5')
sheet.add_chart(chart2,'J5')
chart3.width = 31
sheet.add_chart(chart3,'A20')
wb.save(file_name)最后我们来看一下绘制出来的结果，如下所示
最后的最后，我们将上面所有的代码封装成一个函数，方便我们来调用，代码如下
代码语言：javascript复制import Bikes_Sales_Report_Automation as auto
# 填入文件的名称
auto.automate_excel_dashboard('Bike_Sales_Playground.xlsx')这样我们就可以一步到位创建出想要的可视化大屏了
