**
使用或者下载仅与作者联系后方可，否则视为侵权！！！！！
**
2024 年（第 7 届）“泰迪杯”数据分析技能赛
A 题 自动化生产线数据分析
一等奖并获泰迪杯
一、背景
随着信息技术的快速发展，工业自动化领域的智能控制系统日益完善。自动化生产线能
够独立完成从物料输送到元件抓取，再到产品安装和质量检验的各个环节，这不仅极大提升
了制造效率和产品质量，也有效降低了生产成本。
为了使企业的管理层和工程技术人员及时掌握生产线的运行状况和潜在问题，需要对生
产线产生的数据进行深入分析，从而提高生产线的生产效率，减少产品的次品率。
二、目标
(1) 数据整理与统计。
(2) 生产线运行情况的可视化分析。
(3) 生产线影响因素分析。
三、任务
附件 1 中的文件 M101.csv、M102.csv 分别记录了某生产企业两条生产线全年加工处理
过程中各个工序的生产线数据。请编程完成以下任务，根据附件 2 中的模板文件保存结果，
并撰写报告，在报告中详细描述各项任务的处理思路、过程及必要的结果。所有实数类型的
结果保留两位小数。
任务 1 数据整理与统计
任务 1.1 分别统计两条生产线每天的产品总数（包含不合格产品）、合格产品数、不
合格产品数与合格率，将结果分别存放到 result1_1.xlsx 的 M101 和 M102 工作表中（格式见
表 1），并在报告中给出各生产线全年的产品总数、合格产品数、不合格产品数与合格率（格
式见表 2）。
导入所需库
import numpy as np
import pandas as pd
读取文件
M101 = pd.read_csv("./附件1/M101.csv", encoding="gbk")
M102 = pd.read_csv("./附件1/M102.csv", encoding="gbk")
print(M101['月份'].unique())
print(M101['日期'].value_counts())
print(M101['时间'].max())
print(12 * 28799)
# 月份一共12个月，日期为每个月的每天，时间为每天的总时长，按秒计算
# 分别统计两条生产线每天的产品总数（包含不合格产品）、合格产品数、不合格产品数与合格率
# 将结果分别存放到 result1_1.xlsx 的 M101 和 M102 工作表中（格式见表 1）
# 并在报告中给出各生产线全年的产品总数、合格产品数、不合格产品数与合格率（格式见表 2）。
grouped_101 = M101.groupby(["月份", "日期"]).agg({
"检测累计数": "max",
"合格产品累计数": "max",
"不合格产品累计数": "max"
})
grouped_101.columns = ['产品总数（件）', '合格产品数（件）', '不合格产品数（件）']
grouped_101['合格率（%）'] = round(
(grouped_101['合格产品数（件）'] / grouped_101['产品总数（件）'] * 100), 2)
grouped_101.reset_index(inplace=True)
grouped_102 = M102.groupby(["月份", "日期"]).agg({
"检测累计数": "max",
"合格产品累计数": "max",
"不合格产品累计数": "max"
})
grouped_102.columns = ['产品总数（件）', '合格产品数（件）', '不合格产品数（件）']
grouped_102['合格率（%）'] = round(
(grouped_102['合格产品数（件）'] / grouped_102['产品总数（件）'] * 100), 2)
grouped_102.reset_index(inplace=True)
from openpyxl import load_workbook
# 加载已存在的Excel文件
wb = load_workbook('./附件2/result1_1.xlsx')
# 获取工作表M101和M102
ws_101 = wb['M101']
ws_102 = wb['M102']
# 确定开始写入数据的行号，通常是已有数据的下一行
start_row_101 = ws_101.max_row + 1
start_row_102 = ws_102.max_row + 1
# 将grouped_101的数据写入M101工作表
for r_idx, row in enumerate(grouped_101.itertuples(index=False),
start=start_row_101):
for c_idx, value in enumerate(row, start=1):
ws_101.cell(row=r_idx, column=c_idx, value=value)
# 将grouped_102的数据写入M102工作表
for r_idx, row in enumerate(grouped_102.itertuples(index=False),
start=start_row_102):
for c_idx, value in enumerate(row, start=1):
ws_102.cell(row=r_idx, column=c_idx, value=value)
# 保存工作簿
wb.save('./附件2/result1_1.xlsx')
result1_1_M101 = pd.read_excel("./附件2/result1_1.xlsx", sheet_name="M101")
result1_1_M102 = pd.read_excel("./附件2/result1_1.xlsx", sheet_name="M102")
result1_1_M101['生产线'] = "M101"
result1_1_M102['生产线'] = "M102"
Repoet1_1 = pd.concat([result1_1_M101, result1_1_M102])
Repoet1_1 = Repoet1_1.groupby('生产线').agg({
"产品总数（件）": "sum",
"合格产品数（件）": "sum",
"不合格产品数（件）": "sum"
}).reset_index()
Repoet1_1['合格率'] = round((Repoet1_1['合格产品数（件）'] / Repoet1_1['产品总数（件）']) * 100,2)
# 生产线全年的产品总数、合格产品数、不合格产品数与合格率
Repoet1_1
任务 1.2 分别列出两条生产线每次故障的相关信息，按照月份、日期和开始时间升序
排列，将结果分别存放到 result1_2.xlsx 的 M101 和 M102 工作表中（格式见表 3），并在报
告中给出各生产线每种故障一年中第 25 次发生的相关信息（格式见表 4）。
# 筛选非空故障类别的行
filtered_M101 = M101[M101['故障类别'].notnull()]
# 选择 '月份'、'日期'、'时间' 和 '故障类别' 这四列
filtered_M101 = filtered_M101[['月份', '日期', '时间', '故障类别']]
# 计算每两个连续行之间的时间差，即持续时长（秒）。
# diff() 函数计算当前行与前一行之间的差值，fillna(0) 用于填充第一行的差值，因为第一行没有前一行
filtered_M101['持续时长（秒）'] = filtered_M101['时间'].diff().fillna(0)
# 创建一个段标识，用于区分不同的故障段。如果持续时长不等于1秒，则表示一个新的故障段开始，段标识增加1。
filtered_M101['段标识'] = (filtered_M101['持续时长（秒）'] != 1).cumsum()
# 按照 '月份'、'日期'、'故障类别' 和 '段标识' 进行分组，
# 然后对每组数据计算 '时间' 的第一个值（即开始时间）和 '持续时长（秒）' 的计数（即故障段的数量）。
result1_2_M101 = filtered_M101.groupby(['月份', '日期', '故障类别', '段标识']).agg({
'时间':
'first',
'持续时长（秒）':
'count'
}).reset_index()
# 删除段标识列
result1_2_M101 = result1_2_M101.drop('段标识', axis=1)
# 将 '时间' 列重命名为 '开始时间'
result1_2_M101.rename(columns={
"时间": "开始时间"}, inplace=True)
# 排序
result1_2_M101 = result1_2_M101.sort_values(['月份', '日期', '开始时间'])
result1_2_M101 = result1_2_M101[['月份', '日期', '开始时间', '故障类别', '持续时长（秒）']]
# 筛选非空故障类别的行
filtered_M102 = M102[M102['故障类别'].notnull()]
# 选择 '月份'、'日期'、'时间' 和 '故障类别' 这四列
filtered_M102 = filtered_M102[['月份', '日期', '时间', '故障类别']]
# 计算每两个连续行之间的时间差，即持续时长（秒）。
# diff() 函数计算当前行与前一行之间的差值，fillna(0) 用于填充第一行的差值，因为第一行没有前一行
filtered_M102['持续时长（秒）'] = filtered_M102['时间'].diff().fillna(0)
# 创建一个段标识，用于区分不同的故障段。如果持续时长不等于1秒，则表示一个新的故障段开始，段标识增加1。
filtered_M102['段标识'] = (filtered_M102['持续时长（秒）'] != 1).cumsum()
# 按照 '月份'、'日期'、'故障类别' 和 '段标识' 进行分组，
# 然后对每组数据计算 '时间' 的第一个值（即开始时间）和 '持续时长（秒）' 的计数（即故障段的数量）。
result1_2_M102 = filtered_M102.groupby(['月份', '日期', '故障类别', '段标识']).agg({
'时间':
'first',
'持续时长（秒）':
'count'
}).reset_index()
# 删除段标识列
result1_2_M102 = result1_2_M102.drop('段标识', axis=1)
# 将 '时间' 列重命名为 '开始时间'
result1_2_M102.rename(columns={
"时间": "开始时间"}, inplace=True)
# 排序
result1_2_M102 = result1_2_M102.sort_values(['月份', '日期', '开始时间'])
result1_2_M102 = result1_2_M102[['月份', '日期', '开始时间', '故障类别', '持续时长（秒）']]
# 加载已存在的Excel文件
wb = load_workbook('./附件2/result1_2.xlsx')
# 获取工作表M101和M102
ws_101 = wb['M101']
ws_102 = wb['M102']
# 确定开始写入数据的行号，通常是已有数据的下一行
start_row_101 = ws_101.max_row + 1
start_row_102 = ws_102.max_row + 1
# 将result1_2_M101的数据写入M101工作表
for r_idx, row in enumerate(result1_2_M101.itertuples(index=False),
start=start_row_101):
for c_idx, value in enumerate(row, start=1):
ws_101.cell(row=r_idx, column=c_idx, value=value)
# 将result1_2_M102的数据写入M102工作表
for r_idx, row in enumerate(result1_2_M102.itertuples(index=False),
start=start_row_102):
for c_idx, value in enumerate(row, start=1):
ws_102.cell(row=r_idx, column=c_idx, value=value)
# 保存工作簿
wb.save('./附件2/result1_2.xlsx')
# 在报告中给出各生产线每种故障一年中第 25 次发生的相关信息
result1_2_M101 = pd.read_excel("./附件2/result1_2.xlsx", sheet_name="M101")
result1_2_M102 = pd.read_excel("./附件2/result1_2.xlsx", sheet_name="M102")
result1_2_M101['生产线'] = "M101"
result1_2_M102['生产线'] = "M102"
Repoet1_2 = pd.concat([result1_2_M101, result1_2_M102])
# 对数据按照生产线和故障类别进行分组，并计算每种故障的发生次数
Repoet1_2['发生次数'] = Repoet1_2.groupby(['生产线', '故障类别']).cumcount() + 1
# 筛选出每种故障的第 25 次发生记录
Repoet1_2 = Repoet1_2[Repoet1_2['发生次数'] == 25]
# 选择所需的列
Repoet1_2 = Repoet1_2[['生产线','故障类别','月份', '日期', '开始时间','持续时长（秒）']] \
.sort_values(['生产线','故障类别'])
Repoet1_2
任务 1.3 根据任务 1.2 的结果，分别统计两条生产线各类故障每天发生的总次数和平
均持续时长，按照生产线、月份、日期、故障类别升序排列，将结果存放到 result1_3.xlsx 中
（格式见表 5；如果某天没有指定类别的故障，则该天该故障的总次数为 0，平均持续时长
为 Null）。在报告中分别给出两条生产线各类故障发生的总次数、平均持续时长、故障发生
频率，并进行汇总（格式见表 6）。
# 将2条生产线数据合并
M101_13 = pd.read_excel('./附件2/result1_2.xlsx', sheet_name="M101")
M101_13["生产线"] = "M101"
M102_13 = pd.read_excel('./附件2/result1_2.xlsx', sheet_name="M102")
M102_13["生产线"] = "M102"
data_13 = pd.concat([M101_13, M102_13], ignore_index=True)
result1_3 = data_13.groupby(['生产线', '月份', '日期', '故障类别']).agg({
"持续时长（秒）": 'mean',
"开始时间": "count" # 计算每个组的平均持续时长（秒）
}).reset_index()
result1_3.rename(columns={
"持续时长（秒）": "平均持续时长（秒）", "开始时间": "总次数"}, inplace=True)
resul