背景：运维每周会给到一份全国‘失联’快递柜的数据，作为分析人员。需要知道每台柜子的失联原因。
需要做的事：将这份快递柜的数据拆分成全国6个大区，对应的6个文件，在每个文件的最后新增一个字段，添加数据有效性，让运维同事选择原因，然后回收6份数据，将数据汇总合并，统计失联原因的分布。
第一步：数据拆分，将文件拆解成6份
初始数据结构，典型中国式报表，前两列数据合并，区域数据错误，需要调整替换，最后还需要增加一列包含数据验证的列，方便一线同事选择，也方便后续数据汇总。
import os
import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Border,PatternFill,Font
import psycopg2
# 数据拆分
# 拆分生成新文件，设置数据有效性，设置表格样式，配置邮件发送
#---------------------------------------------------------------------------
#数据读取和清洗
file_path = r'C:\Users\james\Desktop\运维数据\11111111' #设置根目录
file_name = r'快递柜运力状态监控数据.xlsx' #设置excel文件名，一般为固定
#①连接数据库并获取维表数据及清洗
conn = psycopg2.connect(database="database",
user="root",
password="admin",
host="localhost",
port="443")
cursor = conn.cursor()
cursor.execute('select * from dim_city')
df_city = pd.DataFrame(cursor.fetchall())
df_city.columns = ['big_city_name','city_code','city_name','region']
#②读取excel数据表及清洗
path = file_path + '\\' + file_name
df = pd.read_excel(path)
df['城市'] = df['城市'].ffill() #对城市数据进行向下填充
df.dropna(subset=['失联状态'],inplace=True) #删除掉非失联状态的数据
#③两个数据合并，增加维度信息，以便后续数据拆分
df_new= pd.merge(df,df_city,how='left',left_on = '城市',right_on = 'city_name')
df['区域'] = df_new['region']
df['失联原因'] = None
cursor.close()
#---------------------------------------------------------------------------
#设置数据有效性规则
dv = DataValidation(type="list", formula1='"测试机-内部、代理商测试,外部因素-断电、纠纷等,业务待开发-用户没上来、要转移,硬件问题，其他等"', allow_blank=True)
# 设置错误信息提示
dv.error ='你输入的内容不符合规范'
dv.errorTitle = '非法的输入'
# 设置提示信息
dv.prompt = '请直接选择故障原因，勿修改表格表头结构'
dv.promptTitle = '下拉选择'
#---------------------------------------------------------------------------
#设置边框颜色
border = Border(left=Side(border_style='thin',color='000000'),
right=Side(border_style='thin',color='000000'),
top=Side(border_style='thin',color='000000'),
bottom=Side(border_style='thin',color='000000'))
#设置字体颜色
font = Font(size=10, bold=True, name='微软雅黑', color="000000")#字体大小，加粗，字体名称，字体名字
#设置背景颜色
fill = PatternFill(patternType="solid", start_color="5B9BD5")#纯色填充
#---------------------------------------------------------------------------
for i in set(df_city.region.dropna()): #通过for循环拆分文件
wb = Workbook()
ws = wb.active
df_i = df[df['区域']==i]
df_i.reset_index(inplace=True,drop=True)
for r in dataframe_to_rows(df_i, index=True, header=True):
if len(r)!=1:
ws.append(r)
print('读取_'+i+'_数据完成！')
c1 = 'R2'+':'+'R'+str(df_i.shape[0]+1)
c2 = 'A1'+':'+'R'+str(df_i.shape[0]+1)
dv.add(c1)
ws.add_data_validation(dv) #给单元格添加数据验证
for row in ws[c2]: #给单元格添加边框
for cell in row:
if cell.row == 1: #首行加粗，设置背景颜色
cell.font = font
cell.border = border
cell.fill = fill
cell.border = border
wb.save(file_path + '\\' + file_name.split('.')[0]+'_'+i+'.xlsx')
print('成功生成_'+i+'_失联快递柜数据！\n')
# 设置数据有效性并根据地区拆分多个excel文件
脚本执行的结果，生成所需的文件数据，方便后续发送给各地区同事。
新数据增加一列失联原因，添加数据有效性验证，方便选择及规范数据。也方便后期核对数据。
第二步：邮件发送模块
# email负责构造邮件，smtplib负责发送邮件
# coding:utf-8
# smtplib模块负责连接服务器和发送邮件
# MIMEText：定义邮件的文字数据
# MIMEImage：定义邮件的图片数据
# MIMEMultipart：负责将文字图片音频组装在一起添加附件
import smtplib # 加载smtplib模块
from email.mime.text import MIMEText
from email.utils import formataddr
from email.mime.application import MIMEApplication
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email import encoders
sender = 'james.wang@baidu.com' # 发件人邮箱账号
receive = 'harden.Li@baidu.com' # 收件人邮箱账号
cc_mail = 'kurry.Lin@baidu.com,thompson.xiong@baidu.com' # 抄送邮箱账号
passwd = 'james123'
mailserver = 'smtp.baidu.com'
port = '25'
sub = '快递柜失联原因监控数据'
try:
msg = MIMEMultipart('related')
msg['From'] = formataddr(["sender", sender]) # 发件人邮箱昵称、发件人邮箱账号
msg['To'] = formataddr(["receiver", receive]) # 收件人邮箱昵称、收件人邮箱账号
#msg['Cc'] = formataddr(["ccmail", cc_mail]) # 邮件抄送人
msg['Cc'] = cc_mail
msg['Subject'] = sub
print('读取账号信息完成')
#文本信息
#txt = MIMEText('this is a test mail', 'plain', 'utf-8')
#msg.attach(txt)
#正文内容
body = """
<b>本周快递柜失联明细:</b><br></br>
<!doctype html> <html><head> <meta charset="utf-8"></head>
<body><div>
<p>各位运维同事大家好，请认真填写选择快递柜失联原因，并于周四将数据提交数据分析同事，谢谢！:</P>
</div> <style type="text/css">p{text-indent: 2em; /*首行字符缩进设置*/}</style>
<span style="float:right;">数据分析：james</span>
</body>
</html>
"""
text = MIMEText(body, 'html') #, 'utf-8'
msg.attach(text)
print('正文信息构建完成')
path = file_path+'\\'+file_name
#明细附件信息 ①
attach = MIMEApplication(open(path,'rb').read(),encoding="utf8")
attach.add_header('Content-Disposition', 'attachment', filename='filename.xls')
msg.attach(attach)
#明细附件信息 ②
attach = MIMEApplication(open(path,'rb').read(),encoding="utf8")
attach.add_header('Content-Disposition', 'attachment', filename='filename.xls')
msg.attach(attach)
print('附件添加成功')
#发送邮件
server = smtplib.SMTP(mailserver, port) # 发件人邮箱中的SMTP服务器，端口是25
server.login(sender, passwd) # 发件人邮箱账号、邮箱密码
server.sendmail(sender, receive.split(',')+cc_mail.split(','), msg.as_string()) # 发件人邮箱账号、收件人邮箱账号、发送邮件
server.quit()
print('邮件发送成功')
except Exception as e:
print(e)将数据下发到各区域，由各地运维人员下载填写。
第三步：回收文件统计
# 运维回收数据合并
# 合并所有文件，统计所需字段数据
path = file_path+'\\'+'回收数据'
df = pd.DataFrame({'区域':[],'快递柜SN':[],'失联原因':[]})
n=1
for filename in os.listdir(path):
if '汇总' not in filename:
filename=path+"\\" + filename
print('读取第%d个文件' %n,filename )
data = pd.read_excel(filename)
print('读取第%d个文件成功' %n)
n = n + 1
df = pd.concat([df,data],join='inner')
df.drop_duplicates(subset='快递柜SN',keep='last',inplace = True) #防止各地区提交数据出现重复
df.to_excel(path+"\\"+'汇总文件.xlsx',encoding='utf-8')
print('\n')
print(df['失联原因'].value_counts(),'\n')
print(df['区域'].value_counts())
df['失联原因'].value_counts().plot(kind = 'pie',figsize=(15,8),fontsize=12)
做简单的数据可视化展示~
运营层面来说，沟通总是最耗时间的，和几个大区的同事沟通新数据填写和收集流程，耗时接近3个小时。第一周数据通过手动汇总，耗时共40分钟。但业务流程定下来后，通过脚本触发，第二周直接邮件给到一线同事，催下大家提交数据结果，最后通过脚本合并，第二周耗时低于2分钟。效率提升几十倍还是有的。技术和运营能很好结合的话，带来的效率提升回报还是很丰厚的。
412
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
