Python 玩数据分析：统计 Excel 并用 Matplotlib 绘图
?作者：高玉涵
?时间：2022.6.19 11:07 父亲节
?博客：blog.csdn.net/cg_i
“人皆有所不忍，达之于其所忍，仁也；人皆有所不为，达之于其所为，义也。”——孟子曰
数据无处不在
?近日为验证新环境能否适配现有业务，临时从各组抽调 16 位老师分 5 个小组来帮助进行业务测试。为了能及时掌握测试工作整体、小组和个人进度，每日测试终了要求各位老师，将各自测试通过的交易和案例数进行上报汇总。
?本着不重复造轮子的指导思想。数据填报收集的工作。一开始，我是想自己写个简单的小程序。但，转念想“麻雀虽小”要做的事情可一样也少不了。例如，界面设计、代码编写、输入有效性检查、数据库、托管平台等等。原本计划也只是利用 2 周时间来完成测试工作，这样下来投入与产出比不高啊（说人话就是：划不来）。遂，在网上查找是否有现成替代方案，一找之下金山表单跃入眼帘，在这里我要吹爆它（以前我对金山的认识仅限于 WPS） 其丰富的模板和高度可定制化的功能，最最重要的是还免费。这些正是我需要的，摆在我眼前的第一个难题迎刃而解。下图就是我定制的表单界面。
我希望我懂得如何使用基本的电子公式
?解决了数据收集的问题，只能说解决了问题的一半。金山表单会将收集到的数据，以一张很大很复杂的 Excel 表格展现出来（这与你定制的表单、数据规模而异）你能从下图表格中一眼分析出你想要的结果数据吗？
?对于各位 Excel 高手来说（可能正是观看此文的你）有了这张表再利用 Excel 函数把相关数据进行分析，就足以得到想要的结果。不幸的是，我对 Excel 的认知仅限设置单元格四条边线是实还是虚。
一图胜千言
?大脑追逐图像，而非文字。在大脑的活动中，一第图片胜过千言万语。为了能说明问题并给阅读者营造丰富的感觉，我决定以图表的方式展现分析结果，下图就是生成的结果，是不是比枯燥的文字更让你感兴趣。
完整代码
?这里给出了分析 Excel 数据与绘制图表的完整代码，你可以把它用在你的程序或文档中。除非你使用的表格和我完全一样，否则需根据你的 Excel 数据做相应的修改。对于这种只写一次的程序，我并没有去优化算法，程序中存在冗余代码和硬编码，细心的你可能还发现缺少必要的异常处理。这些在我的应用里都不是问题，但如果是使用的你可能要特别小心了。
'''
功能：分析表格数据按需绘制图表
作者：高玉涵
时间：2022.6.19
'''
import numpy as np
import openpyxl
import matplotlib.pyplot as plt
def open_excel(fileName, sheetName):
work = openpyxl.load_workbook(fileName)
sheet = work.get_sheet_by_name(sheetName)
return sheet
def person(sheet, thead):
'''
按人统计
'''
record = {}
for row in range(2, sheet.max_row + 1):
'''
{} 初始化部分
'''
name = sheet.cell(row=row, column=thead['info']).value
if name not in record:
record.setdefault(name, {})
rq = sheet.cell(row=row, column=thead['rq']).value
rq = rq.strftime('%Y-%m-%d')
if rq not in record[name]:
record[name].setdefault(rq, {})
record[name][rq].setdefault('al', 0) # 案例数
record[name][rq].setdefault('jym', 0) # 交易数
for col in thead['jym']:
jym = sheet.cell(row=row, column=col).value
# 假定 每列只登记一个交易码
if jym != "" and jym != None and jym.find('无') == -1:
record[name][rq]['jym'] += 1
for col in thead['al']:
al = sheet.cell(row=row, column=col).value
if al != "" and al != None:
record[name][rq]['al'] += int(al)
return record
def thead_position(sheet):
'''
定位标题行 交易码,案例数,填报人等 在表格中的位置
'''
thead = {'jym':[], 'al':[], 'info':0, 'mk':0, 'rq':''}
for col in range(1, sheet.max_column + 1):
value = sheet.cell(row=1, column=col).value
if value.find("交易名称") >=0:
thead['jym'].append(col)
elif value.find("案例数") >=0:
thead['al'].append(col)
elif value.find("日期") >=0:
thead['rq'] = col
elif value.find("模块") >=0:
thead['mk'] = col
elif value.find("填报人") >=0:
thead['info'] = col
return thead
def graph_line(record):
'''
绘折线图
'''
dateCount = {} # 按日计数
for name in record.keys():
for rq in record[name].keys():
if rq not in dateCount:
dateCount.setdefault(rq, {'jym':0, 'al':0})
dateCount[rq]['jym'] += record[name][rq]['jym']
dateCount[rq]['al'] += record[name][rq]['al']
labels = [rq for rq in dateCount.keys()]
line_jym = []
line_al = []
sum_jym = 0
sum_al = 0
for rq in labels:
line_jym.append(dateCount[rq]['jym'])
line_al.append(dateCount[rq]['al'])
sum_jym += dateCount[rq]['jym']
sum_al += dateCount[rq]['al']
bar_width = 0.35
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
plt.title("X86 测试每日完成情况（单位：个）")
plt.xlabel(f"日期 数据截止:2022.6.18 交易：{sum_jym} 案例：{sum_al}")
plt.ylabel("数量")
plt.box(False)
line1, = plt.plot(labels,line_jym, color='r', label='交易')
line2, = plt.plot(labels, line_al, color='b', label='案例')
plt.legend(handles=[line1, line2], labels=['交易','案例'], loc='best')
for i, j in enumerate(line_jym):
plt.text(i-0.5*bar_width-0.05, j+0.1, str(j))
for i, j in enumerate(line_al):
plt.text(i+0.5*bar_width-0.05, j+0.1, str(j))
plt.show()
def mk(sheet, thead):
'''
按模块统计
'''
record = {}
for row in range(2, sheet.max_row + 1):
'''
{} 初始化部分
'''
mk = sheet.cell(row=row, column=thead['mk']).value
if mk not in record:
record.setdefault(mk, {'jym':0, 'al': 0})
for col in thead['jym']:
jym = sheet.cell(row=row, column=col).value
# 假定 每列只登记一个交易码
if jym != "" and jym != None and jym.find('无') == -1:
record[mk]['jym'] += 1
# 统计案例数
for col in thead['al']:
al = sheet.cell(row=row, column=col).value
if al != "" and al != None:
record[mk]['al'] += int(al)
return record
def graph_group(record):
'''
按组统计
'''
one = ['赵', '张', '闻', '沈']
two = ['花', '周', '韩', '李', '赵']
three = []
four = ['陈', '娄', '陈', '杨']
five = ['李', '郑', '张']
group_count = {
'one': {'jym':0, 'al':0},
'two':{'jym':0, 'al':0},
'three':{'jym':0, 'al':0},
'four':{'jym':0, 'al':0},
'five':{'jym':0, 'al':0}
}
for name in record.keys():
for rq in record[name].keys():
if name in one:
group_count['one']['jym'] += record[name][rq]['jym']
group_count['one']['al'] += record[name][rq]['al']
elif name in two:
group_count['two']['jym'] += record[name][rq]['jym']
group_count['two']['al'] += record[name][rq]['al']
elif name in three:
group_count['three']['jym'] += record[name][rq]['jym']
group_count['three']['al'] += record[name][rq]['al']
elif name in four:
group_count['four']['jym'] += record[name][rq]['jym']
group_count['four']['al'] += record[name][rq]['al']
elif name in five:
group_count['five']['jym'] += record[name][rq]['jym']
group_count['five']['al'] += record[name][rq]['al']
labels = ['第一组', '第二组', '第三组', '第四组', '第五组']
bar1 = []
bar2 = []
sum_jym = 0
sum_al = 0
for g in group_count.keys():
bar1.append( group_count[g]['jym'] )
bar2.append( group_count[g]['al'] )
sum_jym += group_count[g]['jym']
sum_al += group_count[g]['al']
bar_width = 0.35
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
plt.bar(np.arange(len(bar1))-0.5*bar_width, bar1, label='交易数',
width=bar_width, color='#58c9b9')
plt.bar(np.arange(len(bar2))+0.5*bar_width, bar2, label='案例数',
width=bar_width, color='#519d9e')
plt.xlabel(f"填报人 数据截止:2022.6.18 交易：{sum_jym} 案例：{sum_al}")
plt.ylabel("数量")
plt.title("X86 测试各组完成情况（单位：个）")
plt.ylim([1,600])
plt.legend()
plt.xticks(np.arange(len(labels)), labels, fontsize=13)
plt.box(False)
plt.grid(color='0.4', axis='y', linestyle='solid', alpha=0.1)
for i, j in enumerate(bar1):
plt.text(i-0.5*bar_width-0.05, j+0.1, str(j))
for i, j in enumerate(bar2):
plt.text(i+0.5*bar_width-0.05, j+0.1, str(j))
plt.show()
def graph_pie(sheet, thead):
'''
绘制饼图
'''
record = mk(sheet, thead)
labels = [m for m in record.keys()]
pie_jym = []
pie_al = []
for m in labels:
pie_jym.append(record[m]['jym'])
pie_al.append(record[m]['al'])
plt.rcParams['font.sans-serif'] = ['SimHei'] # 用来正常显示中文
plt.rcParams['axes.unicode_minus'] = False # 用来正常显负号
plt.pie(pie_jym, labels=labels, autopct='%1.2f%%')
plt.title('X86 完成的测试交易各模块占比')
plt.show()
def graph_bar(record):
'''
绘柱壮图
'''
labels = [name for name in record.keys()]
bar_width = 0.35
bar1 = []
bar2 = []
sum_jym = 0
sum_al = 0
for n in labels:
jym = 0
al = 0
# 统计
for rq in record[n].keys():
jym += record[n][rq]['jym']
al += record[n][rq]['al']
bar1.append(jym)
bar2.append(al)
sum_jym += jym
sum_al += al
plt.rcParams['font.sans-serif'] = ['SimHei'] # 用来正常显示中文
plt.rcParams['axes.unicode_minus'] = False # 用来正常显负号
plt.bar(np.arange(len(bar1))-0.5*bar_width, bar1, label='交易数',
width=bar_width, color='#58c9b9')
plt.bar(np.arange(len(bar2))+0.5*bar_width, bar2, label='案例数',
width=bar_width, color='#519d9e')
plt.xlabel(f"填报人 数据截止:2022.6.18 交易：{sum_jym} 案例：{sum_al}")
plt.ylabel("数量")
plt.title("X86 测试个人完成情况（单位：个）")
plt.ylim([1,200])
plt.legend()
plt.xticks(np.arange(len(labels)), labels, fontsize=13)
plt.box(False)
plt.grid(color='0.4', axis='y', linestyle='solid', alpha=0.1)
for i, j in enumerate(bar1):
plt.text(i-0.5*bar_width-0.05, j+0.1, str(j))
for i, j in enumerate(bar2):
plt.text(i+0.5*bar_width-0.05, j+0.1, str(j))
plt.show()
if __name__ == '__main__':
sheet = open_excel('xxx.xlsx', '收集表')
# 定位标题位置
thead = thead_position(sheet)
# 按人统计
record = person(sheet, thead)
# 绘柱壮图
graph_bar(record)
graph_group(record)
# 绘折线图
graph_line(record)
# 绘饼图
graph_pie(sheet, thead)