一次数据爬取及处理的实践

2019-11-03 本文已影响0人 whybask

数据分析的六大步骤

1. 需求分析

1.1 业务需求

在班组管理或敏捷开发过程中，都提倡用“班前会”或“晨间会”的形势来推进、协调工作。我们也一直坚持召开每天的班前会，该短会的议程包含：跟踪延误任务、通报需协调事项等。

其中 “延误任务数据” 来自于“任务控制系统” （以下简称CC系统），示例如下：

CC系统截图

以上示例的数据很全面，但在分享到“班前会”进行讨论方面，易用性存在问题：

每个人登录CC系统看到的数据不一样，一般上级可以看到所有下级，但下级只能看到自己的。如果有“统一的、较全面的版本”会更适合班前会讨论；
以上实例混杂了主任务、子任务数据，实际上班前会重点关注子任务，精简后可以不必进行繁琐的“翻页”操作；
以上示例中的部分字段对班前会没有意义，如“创建人、责任科室”等。

总之，CC系统的任务管理功能很全面，具有完整的数据。但在服务“班前会”方面的易用性（可阅读性）并不太友好。所以，为了便于有效沟通，我每天在开会前，要把缺省展示的数据，整理成想要的格式（删除不必要字段、调整字段顺序、设置字体、设置背景颜色、填写任务跟进要求等），如下所示：

更好的可读性

把以上较简洁且适合班前会的数据发布在工作沟通群后、再召开会议。

1.2 流程分析

老的流程依托人工操作，大概步骤是：

用浏览器打开CC系统登录界面，输入用户名、密码完成登录；
在主页上点击“延误”链接，进入延误任务展示界面；
点击“导出当前查询结果”按钮，下载excel版的延误任务数据；
打开excel，删除不必要字段、调整字段顺序、设置字体、设置背景颜色、填写任务跟进要求等；
截图发送到工作沟通群。

新的流程依托Python程序处理数据，大概的步骤是：

运行程序，得到处理好excel文件；
打开excel，截图发送到工作沟通群。

1.3 效能分析

如果开发Python程序代替人工操作，可以在如下三个方面取得成效：

提高工作效率：经试验，熟练人员手工操作耗时大约180 秒，如果用程序实现，可在3 秒内完成，因此，一年按照200个工作日算，全年可节约(180-3)*200/3600≈9.83 小时。
统一工作质量：用程序替代人工操作，首先学习成本大幅下降，其次不存在“因人而异” 的工作误差，能够得到质量一致的成果。
掌握关键技术：用程序实现以上需求，涉及到“从网页爬取数据”，以及“对数据进行预处理”等技术，实践成功并加以推广对“处理外部异构数据”是一种有益的补充方案。

2. 程序开发

2.1 行为分析

利用“浏览器的调试模式”，对每次人工操作的交互数据进行分析。可以得到在本次案例中，实际上只需要进行2次http交互，就可以从CC系统下载到延误任务数据，如下表所示：

目的	URL	方法
登录验证	http://192.1.1.xx/newcc/login	POST
下载数据	http://192.1.1.xx/newcc/task/export	GET

注：每次交互传输的http参数可参见下方示例代码

在本次试验中，使用jupyter notebook作为开发工具，因为该工具特别适合“数据科学领域的探索性编程”场景，可以对每一个步骤进行试验、优化。但它不适合生产环境，因此建议“探索性试验”成功后，把代码发布为生产版本。

2.2 模拟登录

因为CC系统有用户验证功能，必须登录后才能访问后台数据，因此要先模拟登录、并保持会话状态。

# 引用必要的库：http 请求
import requests

# 创建http 请求对象，该对象可以保持会话状态
s = requests.session()

# 设置登录地址
login_url = 'http://192.1.1.xx/newcc/login'

# 设置登录表单数据（表单值做了脱敏处理）
login_data = {'empNo':'******', 'password':'******'}

# 发送登录请求
rs = s.post(login_url, login_data)

# 返回状态码，该值等于200代表访问成功
print(rs.status_code)

# 获取SessionID
session_id = (s.cookies.get_dict())['JSESSIONID']

# SessionID，获得该值就是登录成功了
# 如要查看完整cookies，可以使用cookies.get_dict()
print(session_id)

2.3 数据收集

从CC系统中下载数据，并保存在excel文件中。

# 声明下载数据的地址
data_url = 'http://192.1.1.xx/newcc/task/export?department=4&state=DELAY'

# 向服务器发送请求，并得到返回内容
resp = s.get(data_url)

# 返回的状态码等于200代表成功
print(resp.status_code)

# 查看返回的http头信息，该信息中应包含下载文件的名字。
print(resp.headers)

# 返回示例如下
# {'Content-Disposition': 'attachment; filename=%E4%BB%BB%E5%8A%A1%E5%88%97%E8%A1%A82019-11-03.xlsx', 'Content-Type': 'application/octet-stream;charset=utf-8', 'Transfer-Encoding': 'chunked', 'Date': 'Sun, 03 Nov 2019 03:44:50 GMT'}

# 把下载数据保存到excel 文件中。
with open('D:\\CCTasks-v20191103.xlsx', 'wb') as f:
    f.write(resp.content)

2.4 数据处理

从下载得到的excel文件中加载数据，并对数据进行预处理。

# 引用必要的模块：处理表格数据
import pandas as pd

# 从指定的excel文件中加载数据，保存在df变量中。
df = pd.DataFrame(pd.read_excel('D:\\CCTasks-v20191103.xlsx', skiprows=[0]))

# df的类型是DataFrame，使用它的head()方法得到前5行数据
# 在“探索性编程”时，可以帮助我们理解数据结构
print(df.head())

以上调用head()方法后，可以看到如下的数据结构：

默认数据

# "申请审核日期"不为空，说明已经完成任务，但等待审核
# 为此，把这些数据的“备注”列设置为“跟踪直至关闭”
df.loc[df[df['申请审核日期'].notnull()].index, ['备注']] = '跟踪直至关闭'

# 按“延误天数倒序、责任人正序”对数据排序
df=df.sort_values(by=['延误天数','责任人'], ascending=(False,True))

# 显示整理后的数据
print(df)

注：上面这段代码很容易理解其意图，但看不到其它编程语言常用的for循环。这也是Python、pandas.DataFrame处理数据的魅力。整理后的数据如下：

整理及排序后的效果

2.5 数据展现—格式化数据

最后一步是把数据按照需求整理成想要的格式，包括以下内容：

只保留“班前会”需要关注的字段，并按一定的顺序排列；
设置好看的字体（微软雅黑）；
根据不同的责任人，把数据行设置为不同的背景色；
把“备注”列设置为醒目的红色字体;
为便于阅读，设置合适的列宽。

# 引用必要的类库：操作excel
import openpyxl as opxl

# 打开目标excel 文件
wb = opxl.load_workbook('D:\\CCTasks-v20191103.xlsx')

# 不动excel原有的工作簿，而是新建立一个工作簿
ws = wb.create_sheet("延误报告")

# 设置默认字体
font = opxl.styles.Font(name="微软雅黑", size=10)
font_head = opxl.styles.Font(name="微软雅黑", size=10, bold=True, color='ffffff')
font_memo = opxl.styles.Font(name="微软雅黑", size=10, color='ff0000')

# 设置默认边框
border = opxl.styles.Border(left=opxl.styles.Side(border_style='thin',color='000000'),
right=opxl.styles.Side(border_style='thin',color='000000'),
top=opxl.styles.Side(border_style='thin',color='000000'),
bottom=opxl.styles.Side(border_style='thin',color='000000'))

# 设置默认对齐方式
align_head = opxl.styles.Alignment(horizontal='center', vertical='center')
align_wrap = opxl.styles.Alignment(horizontal='left', vertical='center', wrap_text=True)

# 设置不同责任人对应的背景颜色
fill_colors = {'th':'888888', '王XX':'ffffff', '杨XX':'ffffcc',
'刘X':'fce4d6', '王XX':'e2efda','陈X':'ffccff',
'洪XX':'ddebf7', '沈XX':'ebe2d5', '罗XX':'ccffff',
'龚XX':'ff99cc', '呂X':'cc99ff'}

# 定义最终输出的列的顺序
cols = ['标号','重要性','标题','子任务名称','延误天数','责任人','申请审核日期','备注']

# 写入表头
for j in range(len(cols)):
    cell = ws.cell(row=1, column=j+1)
    cell.font = font_head
    cell.border = border
    cell.fill = opxl.styles.PatternFill("solid", fgColor=fill_colors['th'])
    cell.alignment = align_head
    cell.value = cols[j]

# 写入表数据
for i in range(len(df)):
    fill = opxl.styles.PatternFill("solid", fgColor=fill_colors[df.iloc[i]['责任人']])
    
    cell = ws.cell(row=i+2, column=1)
    cell.font = font
    cell.border = border
    cell.fill = fill
    cell.value = df.iloc[i]['标号']

    cell = ws.cell(row=i+2, column=2)
    cell.font = font
    cell.border = border
    cell.fill = fill
    cell.value = df.iloc[i]['重要性']

    cell = ws.cell(row=i+2, column=3)
    cell.font = font
    cell.border = border
    cell.fill = fill
    cell.alignment = align_wrap
    cell.value = df.iloc[i]['标题']

    cell = ws.cell(row=i+2, column=4)
    cell.font = font
    cell.border = border
    cell.fill = fill
    cell.alignment = align_wrap
    cell.value = df.iloc[i]['子任务名称']

    cell = ws.cell(row=i+2, column=5)
    cell.font = font
    cell.border = border
    cell.fill = fill
    cell.value = df.iloc[i]['延误天数']

    cell = ws.cell(row=i+2, column=6)
    cell.font = font
    cell.border = border
    cell.fill = fill
    cell.value = df.iloc[i]['责任人']

    cell = ws.cell(row=i+2, column=7)
    cell.font = font
    cell.border = border
    cell.fill = fill
    try:
        cell.value = '{0:%Y-%m-%d}'.format(df.iloc[i]['申请审核日期'])
    except:
        cell.value = None

    cell = ws.cell(row=i+2, column=8)
    cell.font = font_memo
    cell.border = border
    cell.fill = fill
    cell.alignment = align_wrap
    cell.value = df.iloc[i]['备注']
    
# 因为“标题”、“子任务名称”，“备注”列内容较多
# 所以，适当调整列宽
ws.column_dimensions['C'].width = 20.0
ws.column_dimensions['D'].width = 20.0
ws.column_dimensions['H'].width = 20.0

# 保存到excel文件
wb.save('D:\\CCTasks-v20191103.xlsx')

以上代码执行成功后，打开excle文件，可以在“延误报告”工作簿中看到想要的最终效果：

最终效果

3. 小结

3.1 思考

信息化与数字化。没有CC系统，就不可能进行本案例的数据试验。 “工作制度化、制度流程化、流程信息化”非常重要，厉害的组织或个人应擅长打造生产工具。
问题痛点与机遇挑战。把工作中遇到的问题当作机遇，而不是习以为常，“成就感”来自于不断挑战。
效能分析与需求质量。本案例的效能虽然不够伟大，但重视做事情能达到的效能，更容易把事情做好。
知识范围与能力大小。知识越广博、越扎实，解决问题的能力就越强。要不断学习通用知识、专业知识、管理知识。

3.2 用到的知识点

写作工具：Markdown、XMind
基础理论：HTTP 协议、HTML
开发语言：Python 语言及模块（request、pandas、openpyxl）
开发工具：浏览器调试、Jupyter notebook

3.3 可完善的地方

虽然最终的输出已经满足业务需求，但还有以下方面可以优化：

仅关注了正常情况的处理，对异常处理的代码非常少。如要达到“生产级别”还需要完善；
可增加“定时机制”增加自动化程度，进一步减少人为操作提高效率；
“数据展现—格式化数据”章节的代码较繁琐，可以继续优化。