python读取excel数据,并可视化展现

时间:2023-03-09 19:53:23
python读取excel数据,并可视化展现
#-*- coding: utf-8 -*-
import pandas as pda
import matplotlib.pyplot as pyl
import matplotlib.font_manager
from collections import Counter '''配置变量区--start--'''
zhibanfile = 'zhibanbiao.xlsx'
week = 6 # 排班排了几周
colstart = 2 # 从0开始,要统计数据开始列
colend = 9 # 从0开始,要统计数据结束列
rowend = week*4+1
dimDict = {
'': '***', '': '***', '': '***', '': '***', '': '***', '': '***', '': '***',
}
print('读取的文件为:' + zhibanfile)
print('排班排了几周:' + str(week))
print('参与值班的人员:')
print(dimDict)
'''配置变量区--end--''' '''读取文件进行处理--start--'''
data = pda.read_excel(zhibanfile)
content = data.values
listday = []
listnight = []
for row in range(2,rowend,4):
for col in range(2,colend):
cellorginal = str(content[row][col])
cell = cellorginal.strip().replace('\n',',').split(',')[0:2]
listday.append(cell[0])
listday.append(cell[1]) for row in range(3,25,4):
for col in range(2,colend):
cellorginal = str(content[row][col])
cell = cellorginal.strip().replace('\n',',').split(',')[0:2]
listnight.append(cell[0])
listnight.append(cell[1]) # 下面为白班处理
daydic = Counter(listday) # 对list中数据根据个数进行统计,形成key,value的格式
xday = list(daydic.keys()) # 值班人员编号
yday = list(daydic.values()) # 值班人员班次个数
xdaylabel = []
for x in xday:
xdaylabel.append(dimDict[x])
daydic = Counter(listday)
xday = list(daydic.keys())
yday = list(daydic.values())
# 下面为夜班处理
nightdic = Counter(listnight)
xnight = list(nightdic.keys())
ynight = list(nightdic.values())
xnightlabel = []
for x in xnight:
xnightlabel.append(dimDict[x])
'''读取文件进行处理--end--''' '''画图--start--'''
f = pyl.figure('监控室值班统计')
zhfont = matplotlib.font_manager.FontProperties(fname='C:\Windows\Fonts\simkai.ttf') # 设置字体格式
pyl.subplot(2,1,1) # 白班
pyl.xticks(range(len(xdaylabel)),xdaylabel,fontproperties=zhfont)
pyl.plot(yday,'b')
pyl.ylim(0,12)
pyl.grid(True)
pyl.title('白班',fontproperties=zhfont) pyl.subplot(2,1,2) # 夜班
pyl.xticks(range(len(xnightlabel)),xnightlabel,fontproperties=zhfont)
pyl.plot(ynight,'g')
pyl.ylim(0,12)
pyl.grid(True)
pyl.title('夜班',fontproperties=zhfont) pyl.show()
'''画图--end--'''