本文章涉及Python读取并处理Excel数据，pandas数据处理转化，matplotlib汇总柱状图、折线图，表格、双y轴柱状+折线图 。
一、首先导入相关的包
#导入相关的包import numpy as npimport pandas as pdfrom pandas import Series,DataFrameimport reimport matplotlib.pyplot as plt
1.1对matplotlib进行相关设置
#高清显示图片%matplotlib inline%config InlineBackend.figure_format = 'retina'#显示中文plt.rcParams['font.sans-serif'] = ['SimHei']#正常显示负号plt.rcParams['axes.unicode_minus'] = False
二、定义文件路径并获取数据
#日期集合riqi = ['02.**','02.**','02.**','02.**','02.**']#文件路径path = 'C:/Users/chen/Downloads/' + riqi[i]+'日外呼.xls'#获取数据data = pd.read_excel(path,1)#读取Excel文件第二个工作表的内容，CSV文件读取用pd.read_csv(path)#展示数据display(data.head(5),data.tail(5))#展示前5行，展示后5行#查看数据分布情况#data.info()#查看数据类型/n#data.duplicated().sum()#查看非空、重复值情况#查看数据是否有异常值#data.describe()#描述性统计
2.1对数据进行转化（由于数据转化后续也会用到所以定义一个函数）
#定义数据处理函数def sjchuli(data)#处理模板名称ccmc = ['a话术','abcde','bcft']#原始需处理的名称ccmc1 = ['a','ab','bc']#需要的替换后的名称l = len(ccmc)#获取需处理的数量hs = data['模板名称']#存储需处理的数据#循环处理模板名称for i in hs.index:n = str(hs[i])for m in range(0,l):ret = re.findall(".*" + ccmc[m]+ ".*",n)#display(n,ret)#判断是否需进行处理if len(ret) > 0:hs[i] = n.replace(ccmc[m],ccmc1[m])break#display(hs1[i],m,ccmc1[m],ccmc[m])hs = pd.DataFrame(hs)#将数据转化格式#display(hs)data['模板名称'] = hs#将处理后的数据赋值替换#处理模板名称，提取中间或者前面的话术#示例： 模板名称：张三_话费券活动_a分行 转化为话术名称： 话费券活动mbname = data['模板名称'].str.split('_',expand = True) #对模板名称拆分并存储#循环判断并获取需要的话术名称huashu = []#定义话术存储变量for i in mbname.index:n1 = str(mbname.loc[i][0])#获取列1n2 = str(mbname.loc[i][1])#获取列2if len(n1) >5:huashu.append(n1)else:huashu.append(n2)#display(huashu)huashu = pd.DataFrame(huashu)#数据类型转化#插入话术列data['话术'] = huashu#处理分行名称#删除前缀‘a’data['组织'] = data['组织'].str.strip("a")#删除后缀‘b’data['组织'] = data['组织'].str.strip("b")#display(data)return data
三、处理分行数据并画图
3.1处理分行数据
#处理分行数据i = 1#日期索引#定义线路数xlshu = [['a',4], ['b',3], ['c',1]]xl = pd.DataFrame(jqrenshu)#数据转化xl.columns = ['组织','线路数']#重命名列名xl = jqr.set_index('组织')#重新定义行索引#分组fenzu = data.groupby(['组织'])['总外呼数','接通数']#按组织分组，对'总外呼数'等计算#汇总fhhuizong1 = fenzu.sum()#特殊值计算#display(fhhuizong1.head(),xl)fhhuizong1 = pd.merge(fhhuizong1,xl['线路数'],how = 'left',on = ['组织'])fhhuizong1['单线路外呼数'] =round(fhhuizong1['总外呼数']/fhhuizong1['线路数'])fhhuizong1['接通率/%'] =round(fhhuizong1['接通数']/fhhuizong1['总外呼数'] * 100,1)fhhuizong1 = fhhuizong1.loc[:,['总外呼数','单线路外呼数','接通数','接通率/%']]fhhuizong = fhhuizong1.sort_values(by='总外呼数',axis=0,ascending=False)#外呼量排序#display(fenhanghuizong.head())#分行与话术fenzu01 = data.groupby(['组织','话术'])['总外呼数']fhhuashu1 = fenzu01.sum()fhhuashu1 = fhhuashu1.reset_index()#拆分复合索引#display(fhhuashu1)#话术维度fenzu1 = data[:-1].groupby(['话术'])['总外呼数','接通数']fhhshuizong1 = fenzu1.sum()#分行话术汇总fhhshuizong1['接通率/%'] = round(fhhshuizong1['接通数']/fhhshuizong1['总外呼数'] * 100,2)fhhshuizong = fhhshuizong1.loc[:,['总外呼数','接通数','接通率/%']]fhhshuizong= fhhshuizong.sort_values(by='总外呼数',axis=0,ascending=True)#排序
3.1分行数据画图
#matplotlib画图#设置画布plt.figure(figsize = (14,17),dpi = 150)#增加画布标题plt.suptitle('{}外呼情况'.format(riqi[i]),fontsize=18)#增加标题#绘制外呼量+单线路外呼量，柱状图（图一）ax1 = plt.subplot(221)x = np.arange(len(fenhanghuizong.index))#获取x轴位置，等差为1的数列width =0.4 #设置柱宽x = x-0.2 #偏移半个柱宽方便画图ax1.bar(x,fhhuizong.loc[:,'总外呼数'],width = width,label = '总外呼数')#总外呼数ax1.bar(x+0.4,fhhuizong.loc[:,'单线路外呼数'],width = width,label = '单线路外呼数')#循环添加标签for a,b in zip(x,fhhuizong.loc[:,'总外呼数']):ax1.text(a,b+3,b, ha='center', va= 'bottom',fontsize=9)for a,b in zip(x+0.4,fhhuizong.loc[:,'单线路外呼数']):ax1.text(a,b-8,b, ha='center', va= 'bottom',fontsize=8)ax1.legend()#增加图例ax1.set_title('{}外呼量+单线路外呼量'.format(riqi[i]),fontsize=16)#给该图表增加标题plt.xticks(x+0.2,labels = fhhuizong.index,rotation = 15)#重新定义x轴，旋转15度#plt.show#绘制接通率，折线图（图二）ax2 = plt.subplot(222)#x轴位置x = np.arange(len(fhhuizong.index))ax2.plot(x,fhhuizong.loc[:,'接通率/%'],label = '接通率/%')for a,b in zip(x,fhhuizong.loc[:,'接通率/%']):ax2.text(a,b+1,b, ha='center', va= 'top',fontsize=10)plt.ylim((0,100))#重新定义y轴ax2.legend()#增加图例ax2.set_title('{}接通率+意向率'.format(riqi[i]),fontsize=16)#增加标题plt.xticks(x,labels = fhhuizong.index,rotation = 15)#重新定义x轴#plt.show#绘制分行外呼场景，表格（图三）plt.subplot(223)tab = plt.table(colLabels=fhhuashu1.columns,cellText=fhhuashu1.values, #添加表头和表格内容colWidths=[0.2,0.6,0.2],#设置每列的宽度colColours = plt.cm.Greys([0.35]*fhhuashu1.shape[1]), #设置表头颜色loc='center', cellLoc='center',fontsize = 12)#,#字体大小设置，受限于单元格内文字多少，字体会自动调整缩小#rowLoc='center')tab.scale(1,1.5) #设置行高比例，文字大小的1.5倍#tab.auto_set_font_size(True)#plt.subplots_adjust(wspace=0.1, hspace = 0.1)plt.title("分行外呼场景",fontsize = 16)#增加标题plt.axis('off')#关闭坐标轴#绘制外呼量+接通率，柱状图+折线图（图四）ax4 =plt.subplot(224)x = np.arange(len(fhhshuizong.index))#获取x轴位置width =0.4 #柱宽#x = x-0.2ax4.bar(x,fhhshuizong.loc[:,'总外呼数'],width = width,label = '总外呼数',alpha=0.6)#不透明度为0.6plt.xticks(x,fhhshuizong.index,rotation = -30)#重新定义x轴，涉及旋转轴标签需在此设置ax01 =ax4.twinx()#坐标轴对称翻转ax01.plot(x,fhhshuizong.loc[:,'接通率/%'],label = '接通率/%')#添加数据标签for a,b in zip(x,fhhshuizong.loc[:,'总外呼数']):ax4.text(a,b,b, ha='center', va= 'bottom',fontsize=9)for a,b in zip(x,fhhshuizong.loc[:,'接通率/%']):ax01.text(a,b,b, ha='center', va= 'bottom',fontsize=9)plt.ylim((0,100))#重新定义y轴范围ax4.legend(loc = 'upper left')#增加图例ax01.legend(loc = 'upper left',bbox_to_anchor = (0.3,1))#增加图例并调整图例位置ax4.set_title('{}话术外呼量、接通率'.format(riqi[i]),fontsize=16)#增加标题plt.showplt.savefig("./{}外呼情况.png".format(riqi[i]))#输出为png图片
四、处理多表格数据并画图
4.1定义汇总数据处理函数
#获取汇总数据def hzshuju(data):#统计总体数据#统计使用分行及相关数量data = datafenhang = list(set(data['组织'][data['组织'].notnull()]))#获取分行名称并去重fhshu = len(fenhang)#统计未使用分行xyfenhang = ['a', 'b', 'c', 'd']#定义现有分行的集合wfenhang= list(set(xyfenhang)-set(fenhang))#现有分行与使用分行取补集，得到未使用分行wfhshu =len(wfenhang)#display(fenhang,wfenhang)#统计话术场景changjing = list(set(data['话术'][:-1]))#最后一列为汇总类数据话术名称为空changjingshu = len(changjing)#统计使用场景数量#display(changjing)#外呼量,接通量，接通率data1 = data.iloc[0:-1]waihuliang = data1['总外呼数'].sum(axis =0)jietongliang = data1['接通数'].sum(axis =0)#jietonglv = str(round(jietongliang/waihuliang*100,2)) + '%'jietonglv = round(jietongliang/waihuliang*100,2)#存入字典并返回dict = {"使用分行":fenhang,"使用分行数":fhshu,"未使用分行":wfenhang,"未使用分行数":wfenhangshu,"话术场景":changjing,"话术场景数":changjingshu,"外呼量":waihuliang,"接通量":jietongliang,"接通率%":jietonglv}return dict#pass
4.2调用函数并获取相关数据
#日期集合 riqi = ['02.**','02.**','02.**','02.**','02.**']datajihe ={}#定义一个空的集合，用于存储汇总处理的数据#循环取数for i in range(0,len(riqi)):#文件路径path = 'C:/Users/chen/Downloads/' + riqi[i]+'日外呼.xls'#获取数据data = pd.read_excel(path,1)#读取Excel文件第二个工作表的内容data1 = sjchuli(data)data2 = hzshuju(data = data1)datajihe[n] = data2datajihe1 = pd.DataFrame(datajihe)display(datajihe1)#展示数据#特殊数据获取#五天均有使用的分行(five days）FDfenhang = list(set(datajihe1.iloc[0,0])&set(datajihe1.iloc[0,1])&set(datajihe1.iloc[0,2])&set(datajihe1.iloc[0,3])&set(datajihe1.iloc[0,4]))#超过三天未使用的分行（three days）con = pd.DataFrame(datajihe1.iloc[2,0] + datajihe1.iloc[2,1]+ datajihe1.iloc[2,2]+ datajihe1.iloc[2,3]+ datajihe1.iloc[2,4])#合并相关数据con['计数'] =1#增加计数列辅助判断con = con.groupby(0)['计数'].count()#分组并计数stwwhfenhang = list((con[con >2]).index)#五天均有使用的场景(five days）FDchangjing = list(set(datajihe1.iloc[4,0])&set(datajihe1.iloc[4,1])&set(datajihe1.iloc[4,2])&set(datajihe1.iloc[4,3])&set(datajihe1.iloc[4,4]))
4.3绘制相关图表
#汇总数据#matplotlib画图#设置画布plt.figure(figsize = (12,12),dpi = 150)#增加画布标题plt.suptitle('近五天使用情况',fontsize=18)#增加标题#绘制使用分行+未使用分行+场景数，柱状图n1 = list(riqi)#横坐标轴ax1 = plt.subplot(221)#plt.subplot(221)#x轴位置x = np.arange(len(n1))width =0.3 #柱宽x = x-0.3ax1.bar(x,datajihe1.loc['使用分行数'],width = width,label = '使用分行数')ax1.bar(x+width,datajihe1.loc['未使用分行数'],width = width,label = '未使用分行数')ax1.bar(x+2*width,datajihe1.loc['话术场景数'],width = width,label = '话术场景数')#添加数据标签for a,b in zip(x,datajihe1.loc['使用分行数']):ax1.text(a,b+0.1,b, ha='center', va= 'bottom',fontsize=9)for a,b in zip(x+0.3,datajihe1.loc['未使用分行数']):ax1.text(a,b+0.1,b, ha='center', va= 'bottom',fontsize=9)for a,b in zip(x+0.55,datajihe1.loc['话术场景数']):ax1.text(a,b+0.1,b, ha='center', va= 'bottom',fontsize=9)plt.ylim((0,22))#重新定义y轴ax1.legend(ncol = 2,fancybox = False,loc = 'best')#增加图例ax1.set_title('近5天分行+场景数',fontsize=16)#增加标题plt.xticks(x+0.3,labels = riqi)#重新定义x轴#plt.show#绘制外呼量+接通率，柱状图+折线图ax2 =plt.subplot(222)#x同上width =0.4 #柱宽ax2.bar(x,datajihe1.loc['外呼量'],width = width,label = '外呼量',alpha=0.6)plt.ylim((0,4000))#重新定义y轴ax01 =ax2.twinx()#坐标轴对称翻转ax01.plot(x,datajihe1.loc['接通率%'],label = '接通率/%')#添加数据标签for a,b in zip(x,datajihe1.loc['外呼量']):ax2.text(a,b,b, ha='center', va= 'bottom',fontsize=9)for a,b in zip(x,datajihe1.loc['接通率%']):ax01.text(a,b,b, ha='center', va= 'bottom',fontsize=9)ax2.legend(loc = 'upper left',fancybox = False,)#增加图例ax01.legend(loc = 'upper left',ncol = 2,bbox_to_anchor = (0.3,1))#增加图例ax2.set_title('近5天总外呼量+接通意向率',fontsize=16)#增加标题plt.ylim((0,100))#重新定义y轴plt.xticks(x,riqi)#重新定义x轴#plt.show#绘制分行使用和场景使用的情况，表格a1,b1,c1 = [],[],[]#定义存储数据的变量，原始数据太长直接绘制表格文字太小，需切分转化for i in FDfenhang:a1.append(i)#五天均有使用的分行for i in TDfenhang:b1.append(i) #超过三天未使用的分行for i in FDchangjing:c1.append(i) #五天均有使用的场景#display(a1,b1,c1)xpd = pd.DataFrame([a1,b1,c1])#格式转换方便处理abc1 = xpd.T #转置abc1["序号"] = abc1.index +1 #增加计数列abc1 = abc1.loc[:,["序号",0,1,2]] #调整列顺序abc1.replace({None:"/"},inplace = True) #将None，替换成 / #display(abc1)plt.subplot(223)head1 = ["序号","5天外呼分行","3天未外呼分行","5天有外呼话术"] #定义表头tab = plt.table(colLabels=head1,cellText=abc1.values,colWidths=[0.1,0.3,0.3,0.6],#设置每列的宽度colColours = plt.cm.Greys([0.35]*len(head1)), #设置表头颜色loc ='center', cellLoc='center',fontsize = 16)#rowLoc='center')tab.scale(1,1.8) #调整行高#tab.auto_set_font_size(True)#plt.subplots_adjust(wspace=0.1, hspace = 0.1)plt.title("详细情况",fontsize = 16)#添加标题plt.axis('off') #关闭坐标轴plt.show #图表展示plt.savefig("./近5天外呼情况（{}）.png".format(riqi[0]))#输出png图片
