Excel 数据的统计分析及绘图自动处理的python示例（精益办公实战2）

Excel 数据统计分析及绘图的自动处理python示例（精益办公实战2）

1、背景描述：

“看数不如看表，看表不如看图”

2、数据准备和任务要求：

数据准备
一份已经经过数据清洗的数据，无缺失值和重复值。
这份数据中涵括3种产品，随机收集的“蛋白质”和“固形物”的数据记录

任务要求
对数据集进行统计分析，通过图表来展示特征数据之间的关系，对数据进行挖掘以寻找有效的数据价值，并且结果需要以Excel文件的形式保存，以方便企业其他人员的查阅和使用。

3、方法步骤：

3.1 导入数据并查看

1、导入需要用到的python库
2、读取Excel文件并将数据转换为dataframe
3、查看数据集的基本信息
（数据的合并和清洗等操作请查看：Excel数据合并到统计分析自动处理的python示例（精益办公室实战1）

3.2 数据可视化–绘图

1、关键参数的赋值，并建立收集图形的列表
2、绘制柱状图
3、绘制箱型图
4、绘制分簇散点图
5、绘制散点图
6、绘制密度图
7、绘制聚合密度图
8、绘制回归图

3.3 建模并预测

建模并预测

3.4 将图表保存到Excel文件中

1、计算之前一共生成了多少个图形
2、建立收集所有图表的文件夹和图片文件的名字
3、构建图表保存的文件路径
4、将所有图表从.jpg转换为.png文件并保存
5、建立用于保存分析图表的Excel文件
6、检查文件结果

4、示例代码

4.1 导入数据

# 1、导入需要用的的python库
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import openpyxl
import os
# 支持中文显示和负号显示
import matplotlib as mpl
mpl.rcParams["font.family"] = "SimHei"
mpl.rcParams["axes.unicode_minus"] = False
import warnings
warnings.filterwarnings("ignore")

# 2、从Excel中导入数据并转换为dataframe
file_path = "d:/test/Summary/Data_Summary.xlsx"
data = pd.read_excel(file_path, header=0, index_col=0)

# 3、查看数据集
data.head()

# 查看数据集的基本统计信息
data.info()

4.2 数据可视化–绘图

# 关键参数赋值 
chart_path = "d:/test/chart"
product = "产品"
category1 = "固形物"
category2 = "蛋白质"
# 收集图形的列表
fig_li = []
# 对图形显示的颜色进行设定
cur_palette = sns.color_palette("hls", 3) 
# 建立产品目录表  
prod_li = data[product].unique()        

# 1、柱状图
fig1, axes = plt.subplots(1, 1, figsize=(10, 5))
sns.countplot(x=product, data=data, palette=cur_palette)
axes.set_title("产品的取样数量统计")
fig_li.append(fig1)
plt.show()

# 2、箱型图
fig2, axes = plt.subplots(1, 2, figsize=(10, 5))
sns.boxplot(x=product, y=category1, data=data, ax=axes[0])
sns.boxplot(x=product, y=category2, data=data, ax=axes[1])
axes[0].set_title("产品固形物的箱型图")
axes[1].set_title("产品蛋白质的箱型图")
fig_li.append(fig2)
plt.show()

# 3.分簇散点图
fig3, axes = plt.subplots(1, 2, figsize=(10, 5))
sns.swarmplot(x=product, y=category1, data=data, ax=axes[0])
sns.swarmplot(x=product, y=category2, data=data, ax=axes[1])
axes[0].set_title("产品固形物的分簇散点图")
axes[1].set_title("产品蛋白质的分簇散点图")
fig_li.append(fig3)
plt.show()

# 4.散点图
fig4, axes = plt.subplots(1, 1, figsize=(10, 5))
sns.scatterplot(x=category1, y=category2, hue=product, data=data)
axes.set_title("产品固形物vs.蛋白质的散点图")
fig_li.append(fig4)
plt.show()

# 5.密度图
fig5, axes = plt.subplots(1, 2, figsize=(10, 5))
sns.kdeplot(x=category2, data=data, shade=True, ax=axes[0])
sns.kdeplot(x=category2, hue=product, data=data, shade=True, ax=axes[1])
axes[0].set_title("所有蛋白质数据的密度图")
axes[1].set_title("分产品呈现蛋白质数据的密度图")
fig_li.append(fig5)
plt.show()

# 6.聚合密度图
cmap_li = ["Greens", "Blues", "Reds"]

for i in range(len(prod_li)):
    li = data[data[product] == prod_li[i]]
    sns.kdeplot(x=category1, y=category2, data=li, cmap=cmap_li[i])

# 7.回归图
sns.lmplot(x=category1, y=category2, hue=product, data=data)
plt.show()

4.3 建模并预测

# 建模并预测：
from sklearn.linear_model import LinearRegression

prod_li = data[product].unique()
line_reg = LinearRegression()

for prod in prod_li:
    x_train = pd.DataFrame(data[data[product] == prod][category1])
    y_train = data[data[product] == prod][category2]
    line_reg.fit(x_train, y_train)
    print("产品：", prod)
    print("回归方程的截距：", line_reg.intercept_.round(3))
    print("回归方程的系数：", line_reg.coef_.round(3))
    print(30*"-")

这3种产品的回归方程式（x=“固形物”， y=“蛋白质”）：
产品A001: y = -6.628 + 0.458 * x
产品A002: y = -8.844 + 0.628 * x
产品B003: y = 1.053 + 0.198 * x

4.4 将图表保存到Excel文件中

import os
import xlsxwriter

# 1、计算之前一共生成了多少个图形
num = len(fig_li)

# 2、建立收集所有图表的文件夹和图片文件的名字
file_dir = "d:/test/chart"
fig_path_li = []
for i in range(num):
    file_name = "chart" + str(i+1) + ".png"
    
# 3、构建图表保存的文件路径
    fig_path = os.path.join(file_dir, file_name)
    fig_path_li.append(fig_path)

# 4、将所有图表从.jpg转换为.png文件并保存
    fig_li[i].savefig(fig_path)

# 5、建立用于保存分析图表的Excel文件
file_chart = "d:/test/Summary/test_chart.xlsx"
writer = pd.ExcelWriter(file_chart, engine="xlsxwriter")
sheet = writer.book.add_worksheet(name="chart")
i = 0                                           # 用于计算每张图写入的行距离（每张图是25行的距离）
for path in fig_path_li:
    sheet.insert_image(i, 0, path)     # 往Excel的表格中按指定的位置写入图片
    i += 25
    
# 6、检查文件结果
writer.close()
print("执行完毕，请检查结果！")

打开Excel文件（test_chart.xlsx）就得到这些图表

任务完成了！

结束语