您现在的位置：程式師世界 >> 編程語言 > >> 更多編程語言 >> Python

Python 玩數據分析：統計 Excel 並用 Matplotlib 繪圖

編輯：Python

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)