Excel快速完成数据合并和统计分析的自动处理示例（python代码）-- 精益办公示例

2022-09-11 18:18:00

Excel快速完成数据合并到基础统计分析的自动处理实例分享（python代码）-- 精益办公示例

1、背景描述：

社会的发展不断深化了经济对科技和数字化的依赖，企业将不得不面对越来越多，甚至是海量的数据和信息。Excel（或类似软件WPS）是当前绝大多数企业首选的办公软件，毕竟能操作编程和机器学习分析的白领是少数，使用Excel文件更利于企业的内部沟通。因此，如何既节约人工时间又能快速应用Excel处理分析数据，获取对业务有价值有影响的结果，是很多企业在提升竞争力（精益办公室）和数据价值所面临的关键问题。本文借用工业制造的案例，完整陈述从合并多个数据记录表到完成分类统计（数据基础分析）的自动处理过程，并将所有结果都以xlsx文件形式（Excel文件）保存下来，利于企业其他人员（非编程员工）的查看和应用。其中的关键代码和方法，可以使用到各种行业和场景，如商业分析、订单分析、净重分析、产品分析、人力资源，旅游线路等等。

2、方法步骤：

一、数据的快速合并

收集数据源文件夹中的数据文件名称(workbook_names)
建立新的数据汇总文件 – 用于收集并保存所有的数据
打开并读取Excel文件,收集每个数据文件中的表格名称（sheet_names)
按表格将数据转换为dataframe,并汇总在一起
将dataframe转换为Excel文件（便于其他人查看和使用）

二、数据的探索和清洗

将Excel(xlsx)文件转换为csv文件
读取csv文件的数据并转换为dataframe, 查看数据整体状态
删除无用的数据列(Unnamed:0, 序号等)
数据集的整体信息查看，了解缺失值的分布情况
删除含有缺失值的记录并检查
检查数据集中重复值的状态并删除重复值

三、数据的统计分析

建立根据任务要求分别统计蛋白质和固形物的自定义函数（方法）
关键参数的赋值
实现根据产品名称分别对蛋白质和固形物进行统计
将两种统计结果汇总在一起，写入Excel文件中并保存

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

数据介绍：
2个文件6个表格收集的产品蛋白质和固形物测量结果

任务要求：

将2个文件中的6张数据表格进行合并，自动生成新的xlsx文件
对合并后的数据自动进行数据清洗和检查，删除无效的数据和冗余数据（重复值）
根据产品名称对2种测量指标分别进行自动的统计分析：取样次数、平均值、标准偏差、最大值、最小值、极值等
将所有的统计信息转换并自动生成xlsx文件

4. 示例代码

一、数据的快速合并

# 1. 收集数据源文件夹中的数据文件名称（workbook_name)
file_dir = "d:/test/test_data"
file_list = os.listdir(file_dir)
print("合并的文件包括:", file_list)

# 2. 建立新的数据汇总文件 -- 用于收集并保存所有的数据
new_file = "d:/test/Summary/Data_Summary.xlsx"
new_list = []
sheet_num = 0

# 3. 打开并读取Excel文件，收集每个数据文件中的表格名称(sheet_names)
for file in file_list:
    file_path = os.path.join(file_dir, file)
    wb = openpyxl.load_workbook(file_path)
    sheet_names = wb.sheetnames
    sheet_num += len(sheet_names)
    
# 4. 按表格将数据转换位dataframe, 并汇总在一起
    for sn in sheet_names:
        dataframe = pd.read_excel(file_path, sheet_name=sn, header=0)
        new_list.append(dataframe)

# 5. 将dataframe转为Excel
df = pd.concat(new_list)
df.to_excel(new_file, sheet_name="Data")

# 6. 检查数据汇总的结果：
print("合并的表格数量：", sheet_num)
print("数据的汇总表格已经产生，请检查！")

数据汇总表格的结果：

二、数据清洗和探索

# 1.将Excel(xlsx)文件转换为csv文件
#  (dataframe读取Excel的数据比较复杂，而读取csv则比较快速便捷且内存消耗小) 
def xlsx_to_csv(file_path, newfile_path):
    data_xlsx = pd.read_excel(file_path, index_col=0)
    data_xlsx.to_csv(newfile_path, encoding="utf-8")
    
file_xlsx = "d:/test/Summary/Data_Summary.xlsx"
file_csv = "d:/test/Summary/Data_Summary_CSV.csv"
xlsx_to_csv(file_xlsx, file_csv)

# 2.读取csv文件的数据并转换为dataframe，查看数据整体状态
data_raw = pd.read_csv(file_csv, header=0)    # header设定为0：是为了使第1行的数据成为列的字段名
data_raw.head()

小结：数据集中含有无效数据列：Unnamed:0, 序号等，应删除

# 3.删除无用的数据列(Unnamed:0, 序号等)
remove_col = ["Unnamed: 0", "序号"]
data_prep0 = data_raw.drop(columns=remove_col, axis=1, inplace=None)
data_prep0.head()

小结：已经删除无效数据

# 4.数据集的整体信息查看，了解缺失值的分布情况
data_raw.info()

小结：产品列中含有4个空值，这些记录为无效记录，应删除

# 5.删除含有缺失值的记录并检查
data_prep = data_prep0.dropna(subset=["产品"], axis=0)
data_prep.info()

小结：含缺失值记录已经被删除

# 6.检查数据集中重复值的状态并删除重复值
print("数据集中的重复值数量：", np.sum(data_prep.duplicated()))

至此：数据集的基本探索和清洁已经完成，数据集可用于数据分析

三、数据的基础统计分析

# 1.建立根据任务要求分别统计蛋白质和固形物的自定义函数（方法）
def prod_describe(data, classify, category, remove_col):
    desc = data.groupby([classify])[category].describe()
    desc.drop(columns=remove_col, axis=1, inplace=True)
    
    # Extreme(极值) = max - min
    desc["极值"] = desc["max"] - desc["min"]
    # 更新统计数据的列名称（英文--》中文）
    desc = desc.rename(columns={"count": "样品数量",
                               "mean": "平均值",
                               "std": "标准偏差",
                               "min": "最小值",
                               "max": "最大值"})
    
    desc = desc.sort_values(by=["样品数量"], axis=0, ascending=False)
    return desc

# 由于报告不需要分位数的统计量，故删除这些字段[25%, 50%, 70%]
remove_col = ["25%", "50%", "75%"]

# 2.关键参数的赋值
classify = "产品"
protein = "蛋白质"
dry = "固形物"
data = data_prep.copy()

# 3.实现根据产品名称分别对蛋白质和固形物进行统计
protein_desc = prod_describe(data, classify, protein, remove_col)
dry_desc = prod_describe(data, classify, dry, remove_col)

# 4.将两种统计结果汇总在一起，写入Excel文件中并保存
result = pd.concat([protein_desc, dry_desc], axis=1, keys=["蛋白质", "固形物"])
file = "d:/test/Summary/Data_Statistics.xlsx"
result.to_excel(file, sheet_name="Statistics")
print("已经全部完成，请检查！")

3个新文件已分别自动生成，打开文件“Data_Statistics.xlsx”，就能获取统计分析结果，任务达成！

结束语

当前很多企业的办公室白领每天都要花费不少时间在做一些重复性的数据信息合并和转移等处理工作，这是一种极大的人员能力浪费。因此，使用一些自动数据处理的方法将很好地把这些接受过高等教育的白领从简单而重复的”copy-paste"中解放出来，使他们更专注于自身领域的高阶决策分析和细致的实际执行，体现更高的个人价值并为企业提供更好更专业的服务！

（注：数据的统计分析、数据挖掘、数据图示化处理等部分由于受限于博文篇幅，将在另一篇博文中展示）

码农公寓