Python 玩数据分析：统计 Excel 并用 Matplotlib 绘图

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)

Original: https://blog.csdn.net/cg_i/article/details/125358544
Author: 半点闲
Title: Python 玩数据分析：统计 Excel 并用 Matplotlib 绘图