"""
Created on Thu Mar 14 16:33:36 2019
@author: zhanggl21
"""
'''
此版较前一版更新：
图表配色参数修改
'''
'''
################Report##########################
.
.
.
.
'''
'''##########一.网络成交核心数据'''
'''1.1网络总单数与网络总业绩'''
import pandas as pd
netall_df=pd.read_excel('E:\\暂存\\月度成交分析报告\\Datasets\\'
'1.网络整体核心数据\\网络业绩及投入产出.xlsx',
sheet_name='网络整体业绩及投入产出')
netall_df=netall_df.set_index('月份').sort_index()
import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams['font.sans-serif']=['SimHei']
matplotlib.rcParams['axes.unicode_minus']=False
plt.figure(figsize=(16,6.5),dpi=300)
ax1=plt.subplot(211)
ax1.plot(range(len(netall_df.index)),netall_df['网络总业绩(实际)'],linewidth=2,\
color='r',label='网络总业绩(实际)(万)',marker='o',markerfacecolor='k')
plt.yticks(ticks=range(0,60000001,10000000),\
labels=[str(int(x/10000))+'万' for x in range(0,60000001,10000000)])
import datetime
plt.xticks(ticks=range(len(netall_df.index)),\
labels=[str(d1.year)+'年'+str(d1.month)+'月' for d1 in netall_df.index],\
weight='bold',fontsize=12)
for x,y in zip(range(len(netall_df.index)),netall_df['网络总业绩(实际)']):
plt.text(x,y+3000000,str(int(y/10000)),fontsize=12)
#添加环比数据
plt.annotate('环比：{:+.2%}\n公司环比:{:+.2%}'.format((netall_df.iloc[len(netall_df.index)-1,\
10]-netall_df.iloc[len(netall_df.index)-2,10])/netall_df.\
iloc[len(netall_df.index)-2,10],(netall_df.iloc[len(netall_df.index)-1,\
18]-netall_df.iloc[len(netall_df.index)-2,18])/netall_df.\
iloc[len(netall_df.index)-2,18]),xy=(range(len(netall_df.index))[-1],\
netall_df.iloc[len(netall_df.index)-1,10]*0.7),color='r',alpha=0.5,fontsize=10)
ax2=ax1.twinx()
ax2.bar(range(len(netall_df.index)),netall_df.网络总单数,\
color='dodgerblue',label='网络总单数')
plt.yticks(ticks=range(0,3001,500))
for x,y in zip(range(len(netall_df.index)),netall_df['网络总单数']):
plt.text(x,y-300,str(y),fontsize=12,ha='center',va='center')
ax1.legend(loc='upper left')
ax2.legend(loc='best')
'''1.2网络整体单均业绩及网络CTM/PTM单均业绩'''
ax=plt.subplot(212)
plt.plot(range(len(netall_df.index)),netall_df['网络单均业绩'],linewidth=2,\
color='red',label='网络单均业绩',marker='o',markerfacecolor='k')
plt.plot(range(len(netall_df.index)),netall_df['网络ctm单均业绩'],linewidth=2,\
color='lightcoral',label='网络ctm单均业绩',marker='o',markerfacecolor='k')
plt.plot(range(len(netall_df.index)),netall_df['网络ptm单均业绩'],linewidth=2,\
color='deeppink',label='网络ptm单均业绩',marker='o',markerfacecolor='k')
plt.ylim([0,netall_df['网络ptm单均业绩'].max()+20000])
plt.xticks(ticks=range(len(netall_df.index)),\
labels=[str(d1.year)+'年'+str(d1.month)+'月' for d1 in netall_df.index],\
weight='bold',fontsize=12)
for x,y in zip(range(len(netall_df.index)),netall_df['网络单均业绩']):
plt.text(x,y+5000,str(int(y)),fontsize=10,weight='bold',ha='center',va='center')
for x,y in zip(range(len(netall_df.index)),netall_df['网络ctm单均业绩']):
plt.text(x,y-5000,str(int(y)),fontsize=10,weight='bold',ha='center',va='center')
for x,y in zip(range(len(netall_df.index)),netall_df['网络ptm单均业绩']):
plt.text(x,y+5000,str(int(y)),fontsize=10,weight='bold',ha='center',va='center')
plt.legend(loc='best')
plt.show()
'''2.网络CTM与PTM总业绩及CTM业绩占比'''
plt.figure(figsize=(16,6.5),dpi=300)
ax1=plt.gca()
ax1.bar(range(len(netall_df.index)),netall_df['ctm总业绩'],color='crimson',\
label='网络ctm总业绩(万)')
ax1.bar(range(len(netall_df.index)),netall_df['ptm总业绩'],\
bottom=netall_df['ctm总业绩'],color='dodgerblue',label='网络ptm总业绩(万)')
ax1.legend(loc='upper left')
plt.yticks(ticks=range(0,80000000+1,10000000),\
labels=[str(int(x/10000))+'万' for x in range(0,\
80000000+1,10000000)])
for x,y in zip(range(len(netall_df.index)),netall_df['ctm总业绩']):
plt.text(x,y*0.5,str(int(y/10000)),fontsize=12,ha='center',va='center')
for x,y,z in zip(range(len(netall_df.index)),netall_df['ptm总业绩'],netall_df['ctm总业绩']):
plt.text(x,(y+z)*0.9,str(int(y/10000)),fontsize=12,ha='center',va='center')
ax2=ax1.twinx()
ax2.plot(range(len(netall_df.index)),netall_df['ctm业绩占比'],linewidth=2,\
color='r',marker='o',markerfacecolor='k',label='网络ctm业绩占比')
ax2.legend(loc='upper right')
plt.xticks(ticks=range(len(netall_df.index)),\
labels=[str(d1.year)+'年'+str(d1.month)+'月' for d1 in netall_df.index],\
weight='bold',fontsize=12)
plt.ylim(0,1)
for x,y in zip(range(len(netall_df.index)),netall_df['ctm业绩占比']):
plt.text(x,y+0.05,str('%.0f%%' % round(y*100,2)),fontsize=12,ha='center',va='bottom')
plt.show()
'''3.网络总业绩占上海中原全公司比例'''
plt.figure(figsize=(16,6.5),dpi=300)
#网络总业绩公司占比
plt.subplot(211)
plt.plot(range(len(netall_df.index)),netall_df['网络业绩占公司比例(实际)'],\
color='r',linewidth=2,marker='o',markerfacecolor='k',\
label='网络业绩占公司比例(实际)')
plt.plot(range(len(netall_df.index)),netall_df['网络业绩占公司比例(预估)'],\
color='r',alpha=0.5,linewidth=2,marker='o',markerfacecolor='k',\
linestyle='--',label='网络业绩占公司比例(预估)')
plt.ylim([0,0.6])
for x,y in zip(range(len(netall_df.index)),netall_df['网络业绩占公司比例(实际)']):
plt.text(x,y+0.03,str('%.0f%%' % round(y*100,2)),fontsize=12,\
ha='center',va='center')
for x,y in zip(range(len(netall_df.index)),netall_df['网络业绩占公司比例(预估)']):
plt.text(x,y+0.03,str('%.0f%%' % round(y*100,2)),fontsize=12,\
ha='center',va='center')
plt.xticks(ticks=range(len(netall_df.index)),\
labels=[str(d1.year)+'年'+str(d1.month)+'月' for d1 in netall_df.index],\
weight='bold',fontsize=12)
plt.legend(loc='upper right')
#各网站投入
import pandas as pd
net_invest=pd.read_excel('E:\\暂存\月度成交分析报告\\Datasets\\'
'1.网络整体核心数据\网络业绩及投入产出.xlsx',\
sheet_name='分网络投入')
net_invest_zy=net_invest[['日期','中原网投入']]
net_invest_aj=net_invest[['日期','安居客投入']]
net_invest_sf=net_invest[['日期','搜房投入']]
net_invest_wb=net_invest[['日期','58同城投入']]
plt.subplot(212)
plt.bar(range(len(net_invest_zy.index)),net_invest_zy.中原网投入,\
color='crimson',bottom=0,label='中原网投入(万)')
plt.bar(range(len(net_invest_aj.index)),net_invest_aj.安居客投入,\
color='royalblue',bottom=net_invest_zy.中原网投入,\
label='安居客投入(万)')
plt.bar(range(len(net_invest_sf.index)),net_invest_sf.搜房投入,\
color='cyan',bottom=net_invest_zy.中原网投入+net_invest_aj.\
安居客投入,label='搜房投入(万)')
plt.bar(range(len(net_invest_wb.index)),net_invest_wb['58同城投入'],\
color='darkgreen',bottom=net_invest_zy.中原网投入+net_invest_aj.\
安居客投入+net_invest_sf.搜房投入,label='58同城投入(万)')
plt.xticks(ticks=range(len(net_invest_zy.index)),\
labels=[str(d1.year)+'年'+str(d1.month)+'月' for d1 in net_invest_zy.set_index('日期').index],\
weight='bold',fontsize=12)
plt.ylim([0,int((net_invest_zy.中原网投入.max()+net_invest_aj.\
安居客投入.max()+net_invest_sf.搜房投入.max()\
+net_invest_wb['58同城投入'].max())*1.2)])
for x,y in zip(range(len(net_invest_zy)),net_invest_zy.中原网投入):
plt.text(x,y*0.5,str(int(y/10000)),fontsize=8,ha='center',va='center')
for x,y,z in zip(range(len(net_invest_aj)),net_invest_aj.安居客投入,\
net_invest_zy.中原网投入):
plt.text(x,(y+z)*0.7,str(int(y/10000)),fontsize=8,ha='center',va='center')
for x,y,z,w in zip(range(len(net_invest_sf)),net_invest_sf.搜房投入,\
net_invest_zy.中原网投入,net_invest_aj.安居客投入):
plt.text(x,(y+z+w)*0.93,str(int(y/10000)),fontsize=8,ha='center',va='center')
for x,y,z,w,p in zip(range(len(net_invest_wb)),net_invest_wb['58同城投入'],\
net_invest_sf.搜房投入,net_invest_zy.中原网投入,\
net_invest_aj.安居客投入):
plt.text(x,(y+z+w+p)*0.93,str(int(y/10000)),fontsize=8,ha='center',va='center')
for x,y,z,w,p in zip(range(len(net_invest_wb)),net_invest_wb['58同城投入'],\
net_invest_sf.搜房投入,net_invest_zy.中原网投入,\
net_invest_aj.安居客投入):
plt.text(x,(y+z+w+p)*1.1,str(int((y+z+w+p)/10000)),\
color='r',fontsize=12,\
fontweight='bold',ha='center',va='center')
plt.legend(loc='upper left')
plt.show()
'''
import pandas as pd
netall=pd.read_excel(r'E:\暂存\月度成交分析报告\Datasets\2.近半年月度业绩与投入产出走势(中原网VS合作网站)\网络总业绩全公司占比.xlsx')
netall=netall.set_index('日期')
netall=netall.sort_index()
import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams['font.sans-serif']=['SimHei']
matplotlib.rcParams['axes.unicode_minus']=False
plt.figure(figsize=(16,8),dpi=300)
plt.yticks([x/100 for x in range(0,51,10)],labels=[str(round(x/100,1)) for x in range(0,51,10)])
plt.plot(range(len(netall.index)),netall.网络CTM占全公司CTM,color='r',alpha=0.5,\
linestyle='--',linewidth=2,marker='o',markerfacecolor='k',label='网络CTM占全公司CTM')
plt.plot(range(len(netall.index)),netall.网络PTM占全公司PTM,color='orange',\
linestyle='--',linewidth=2,marker='o',markerfacecolor='k',label='网络PTM占全公司PTM')
plt.plot(range(len(netall.index)),netall.网络总业绩占全公司,color='r',\
linestyle='-',linewidth=3,marker='o',markerfacecolor='k',label='网络总业绩占全公司')
plt.legend(loc='upper left')
plt.xticks(ticks=range(len(netall.index)),labels=['2018年10月',\
'2018年11月','2018年12月','2019年1月','2019年2月','2019年3月'],fontsize=10)
plt.ylabel('占全公司比例')
for x,y in zip(range(len(netall.index)),netall.网络总业绩占全公司):
plt.text(x,y+0.03,str(round(y,2)),fontsize=12,va='center',ha='center')
plt.show()
'''
'''4.各网站总业绩及今年以来走势图'''
import pandas as pd
eachnet_df=pd.read_excel('E:\\暂存\\月度成交分析报告\\Datasets\\'
'1.网络整体核心数据\\网络业绩及投入产出.xlsx',\
sheet_name='分网络投入')
eachnet_df=eachnet_df.set_index('日期').sort_index()
import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams['font.sans-serif']=['SimHei']
matplotlib.rcParams['axes.unicode_minus']=False
plt.figure(figsize=(16,6.5),dpi=300)
#4.1各网站总业绩(拆分)
plt.subplot(211)
plt.bar(range(len(eachnet_df.index)),eachnet_df.中原网业绩,color='crimson',\
bottom=0,label='中原网业绩(万)')
plt.bar(range(len(eachnet_df.index)),eachnet_df.安居客业绩,color='royalblue',\
bottom=eachnet_df.中原网业绩,label='安居客业绩(万)')
plt.bar(range(len(eachnet_df.index)),eachnet_df.搜房业绩,color='cyan',\
bottom=eachnet_df.中原网业绩+eachnet_df.安居客业绩,label='搜房业绩(万)')
plt.bar(range(len(eachnet_df.index)),eachnet_df['58同城业绩'],color='darkgreen',\
bottom=eachnet_df.中原网业绩+eachnet_df.安居客业绩+eachnet_df.搜房业绩+eachnet_df.新浪业绩,\
label='58同城业绩(万)')
plt.xticks(ticks=range(len(eachnet_df.index)),\
labels=[str(d1.year)+'年'+str(d1.month)+'月' for d1 in eachnet_df.index],\
weight='bold',fontsize=12)
plt.yticks(ticks=range(0,80000000,20000000),\
labels=[str(int(x/10000))+'万' for x in range(0,80000000,20000000)])
for x,y in zip(range(len(eachnet_df.index)),eachnet_df.中原网业绩):
plt.text(x,y*0.5,str(int(y/10000)),va='center',ha='center',fontsize=8)
for x,y,z in zip(range(len(eachnet_df.index)),eachnet_df.安居客业绩,eachnet_df.中原网业绩):
plt.text(x,(y+z)*0.8,str(int(y/10000)),va='center',ha='center',fontsize=8)
for x,y,z,w in zip(range(len(eachnet_df.index)),eachnet_df.搜房业绩,\
eachnet_df.安居客业绩,eachnet_df.中原网业绩,):
plt.text(x,(y+z+w)*0.96,str(int(y/10000)),va='center',ha='center',fontsize=8)
for x,y,z,w,p in zip(range(len(eachnet_df.index)),eachnet_df['58同城业绩'],\
eachnet_df.安居客业绩,eachnet_df.中原网业绩,eachnet_df.搜房业绩):
plt.text(x,(y+z+w+p)*0.96,str(int(y/10000)),va='center',ha='center',fontsize=8)
plt.legend(loc='upper left')
#4.2安居客预估业绩(拆分)
plt.subplot(212)
plt.bar(range(len(eachnet_df.index)),eachnet_df['安居客业绩(预估)'],\
color='royalblue',label='安居客业绩(预估)')
for x,y in zip(range(len(eachnet_df.index)),eachnet_df['安居客业绩(预估)']):
plt.text(x,y*0.5,str(int(y/10000)),va='center',ha='center')
plt.xticks(ticks=range(len(eachnet_df.index)),\
labels=[str(d1.year)+'年'+str(d1.month)+'月' for d1 in eachnet_df.index],\
weight='bold',fontsize=12)
plt.legend(loc='upper left')
plt.show()
'''5.各网站业绩饼图'''
import pandas as pd
netp=pd.read_excel('E:\\暂存\\月度成交分析报告\\Datasets\\'
'1.网络整体核心数据\\网络业绩及投入产出.xlsx',
sheet_name='各网站拆分业绩及投入产出')
netp=netp.set_index('月份')
netp_zy=netp[(netp.index.isin(netp.index.unique()[:datetime.datetime.now().month-1]\
))&(netp.网站=='中原网')]
netp_zy=netp_zy.sort_index()
netp_aj=netp[(netp.index.isin(netp.index.unique()[:datetime.datetime.now().month-1]\
))&(netp.网站=='安居客')]
netp_aj=netp_aj.sort_index()
netp_aj_pre=netp[(netp.index.isin(netp.index.unique()[:datetime.datetime.now().month-1]\
))&(netp.网站=='安居客(预估)')]
netp_aj_pre=netp_aj_pre.sort_index()
netp_sf=netp[(netp.index.isin(netp.index.unique()[:datetime.datetime.now().month-1]\
))&(netp.网站=='搜房')]
netp_sf=netp_sf.sort_index()
netp_wb=netp[(netp.index.isin(netp.index.unique()[:datetime.datetime.now().month-1]\
))&(netp.网站=='58同城')]
netp_wb=netp_wb.sort_index()
netp_sum=netp[(netp.index.isin(netp.index.unique()[:datetime.datetime.now().month-1]\
))&(netp.网站=='网络整体')]
netp_sum=netp_sum.sort_index()
#画当月业绩饼图
import matplotlib.pyplot as plt
plt.figure(figsize=(8,8),dpi=100)
plt.subplot(111)
slices = [netp_zy.loc[netp_sf.index[-1],'总业绩'],netp_aj.loc[netp_sf.index[-1],'总业绩'],\
netp_sf.loc[netp_sf.index[-1],'总业绩'],netp_wb.loc[netp_sf.index[-1],'总业绩']]
net = ['中原网','安居客','搜房网','五八同城']
cols = ['crimson','royalblue','cyan','darkgreen']
plt.pie(slices,
labels=net,
colors=cols,
startangle=90,
shadow= True,
radius=1.5,
explode=(0.1,0.1,0.1,0.1),
textprops={'fontsize':12,'color':'k','fontweight':'bold'},
autopct='%1.1f%%')
import datetime
noww=datetime.datetime.now()
plt.title('2019年'+str(noww.month-1)+'月-各网站拆分业绩占比',\
pad=100,fontsize=16,fontweight='bold')
plt.show()
'''6.画中原网、安居客、搜房、五八同城投入产出走势图'''
import matplotlib.pyplot as plt
plt.figure(figsize=(16,6.5),dpi=300)
plt.plot(range(len(netp_zy.index)),netp_zy.投入产出,color='dodgerblue',\
linestyle='-',linewidth=1,marker='o',markerfacecolor='k',label='中原网')
plt.plot(range(len(netp_zy.index)),netp_aj.投入产出,color='orange',\
linestyle='-',linewidth=1,marker='o',markerfacecolor='k',label='安居客')
plt.plot(range(len(netp_zy.index)),netp_aj_pre.投入产出,color='orange',alpha=0.5,\
linestyle='--',linewidth=1,marker='o',markerfacecolor='k',label='安居客(预估)')
plt.plot(range(len(netp_zy.index)),netp_sf.投入产出,color='cyan',\
linestyle='-',linewidth=1,marker='o',markerfacecolor='k',label='搜房网')
plt.plot(range(len(netp_zy.index)),netp_wb.投入产出,color='darkgreen',\
linestyle='-',linewidth=1,marker='o',markerfacecolor='k',label='五八同城')
plt.plot(range(len(netp_sum.index)),netp_sum.投入产出,color='r',\
linestyle='--',linewidth=3,marker='o',markerfacecolor='k',label='网络整体')
plt.xticks(ticks=range(len(netp_zy.index)),\
labels=[str(d1.year)+'年'+str(d1.month)+'月' for d1 in netp_zy.index],\
fontsize=12)
plt.legend(loc='upper left')
plt.title('2019年中原网及合作网站－月度投入产出走势')
#plt.title('各网站一元投入产出走势',fontsize=16)
for x,y in zip(range(len(netp_zy.index)),netp_zy.投入产出):
plt.text(x+0.08,y+0.3,str(round(y,2)),fontsize=8,color='dodgerblue',va='center',ha='right')
for x,y in zip(range(len(netp_sum.index)),netp_sum.投入产出):
plt.text(x+0.06,y+0.3,str(round(y,2)),fontsize=8,color='r',va='center',ha='right')
for x,y in zip(range(len(netp_aj.index)),netp_aj.投入产出):
plt.text(x-0.06,y-0.3,str(round(y,2)),fontsize=8,color='orange',va='bottom',ha='right')
for x,y in zip(range(len(netp_aj_pre.index)),netp_aj_pre.投入产出):
plt.text(x+0.06,y-0.3,str(round(y,2)),fontsize=8,color='orange',va='center',ha='right')
plt.show()
'''##########二.分战区(区董)网络成交情况'''
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams['font.sans-serif']=['SimHei']
matplotlib.rcParams['axes.unicode_minus']=False
ctmdeal=pd.read_excel('E:\\暂存\\月度成交分析报告\\Datasets\\'
'2.大战区成交数据\\201910分区董CTM成交来电数据.xlsx')
import datetime
noww=datetime.datetime.now()
upward_month=datetime.date(noww.year,noww.month-1,1)
qdeal_ctm=ctmdeal[ctmdeal.月份==upward_month].\
replace({'余昆直带':'余昆','陈宇珏直带':'陈宇珏'})
qdeal_ctm_sum=qdeal_ctm[['Root Id','CTM业绩','成交类型','成交区董']].\
drop_duplicates().groupby(['成交区董']).agg(sum)
ptmdeal=pd.read_excel('E:\\暂存\\月度成交分析报告\\Datasets\\'
'2.大战区成交数据\\201910分区董PTM成交来电数据.xlsx')
ptmdeal=ptmdeal.rename(columns={'区董':'成交区董'})
qdeal_ptm=ptmdeal[['成交编号','计佣金额','成交区董']].\
drop_duplicates().groupby(['成交区董']).agg(sum)
qdeal_ptm.rename(columns={'计佣金额':'总业绩'},inplace=True)
qdeal_ctm_sum.columns=['总业绩']
qdeal_sum=pd.concat([qdeal_ctm_sum,qdeal_ptm],join='outer').\
groupby('成交区董').sum().sort_values(by='总业绩',ascending=False)
'''2.1分区董业务员人均总业绩 '''
import pandas as pd
lianluo_df=pd.read_excel('E:\\lele月工作记录\\数据匹配与高级筛选201712updated.xlsx',
sheet_name='匹配区董20180104updated')
lianluo_df=lianluo_df.rename(columns={'区董':'成交区董'})
#统一区董名称
if lianluo_df[lianluo_df.成交区董.str.find('直带')!=-1] is not None:
lianluo_df.成交区董=lianluo_df.成交区董.str.replace('直带','')
#筛选出营业董事
qd_lst=lianluo_df[lianluo_df.职位名称.str.contains('营业董事')].成交区董.unique()
qd_df=lianluo_df[lianluo_df.成交区董.isin(qd_lst)]
qd_salesman_df=qd_df.groupby(by='成交区董').职员代码.count().\
reset_index().rename(columns={'职员代码':'管辖业务员人数'})
qd_per_yeji=pd.merge(left=qdeal_sum.reset_index(),\
right=qd_salesman_df,left_on='成交区董',\
right_on='成交区董',how='inner')
qd_per_yeji['业务员人均总业绩']=qd_per_yeji.总业绩/qd_per_yeji.管辖业务员人数
qd_per_yeji=qd_per_yeji.sort_values(by='业务员人均总业绩',ascending=False)
import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams['axes.unicode_minus']=False
matplotlib.rcParams['font.sans-serif']=['SimHei']
#各区董人均总业绩柱形图
plt.figure(figsize=(16,6.5),dpi=300)
plt.bar(range(len(qd_per_yeji.index)),\
qd_per_yeji.业务员人均总业绩,color='dodgerblue',\
label='辖内业务员人均总业绩(元)')
plt.xticks(ticks=range(len(qd_per_yeji.index)),\
labels=qd_per_yeji.成交区董.values)
for x,y in zip(range(len(qd_per_yeji.index)),qd_per_yeji.业务员人均总业绩):
plt.text(x,y+100,str(int(y)),va='center',ha='center')
plt.legend(loc='upper right')
#业务员整体人均总业绩
saleman_per=qd_per_yeji.总业绩.sum()/qd_per_yeji.管辖业务员人数.sum()
plt.annotate('业务员整体人均总业绩： '+str(int(saleman_per)),\
xy=(11,6000),color='r',alpha=0.5,fontsize=12)
plt.title('2019年'+str(noww.month-1)+'月-大战区业务员人均总业绩',fontsize=16)
plt.show()
'''#2.2分区董总业绩'''
qdeal_sum=qdeal_sum[qdeal_sum.index.isin(qd_lst)]
plt.figure(figsize=(16,6.5),dpi=300)
plt.bar(range(len(qdeal_sum.index)),qdeal_sum.总业绩,color='dodgerblue',width=0.8)
plt.xticks(ticks=range(len(qdeal_sum.index)),\
labels=qdeal_sum.index,rotation=45,fontsize=12,fontweight='bold')
plt.yticks(ticks=range(0,10000000,1000000),\
labels=[str(int(x/10000))+'万' for x in range(0,10000000,1000000)])
plt.ylabel('总业绩')
plt.title('2019年'+str(noww.month-1)+'月-大战区网络总业绩',fontsize=16)
for x,y in zip(range(len(qdeal_sum.index)),qdeal_sum.总业绩):
plt.text(x,y+100000,str(int(y/10000)),fontsize=12,va='center',ha='center')
plt.plot(range(len(qdeal_sum.index)),\
[qdeal_sum.总业绩.sum()/len(qdeal_sum.index)]*len(qdeal_sum.index),'r--',alpha=0.5)
plt.annotate('大战区网络平均总业绩:{:,} '.\
format(int(qdeal_sum.总业绩.sum()/len(qdeal_sum.index))),\
xy=(10,2200000),fontsize=12,color='r',alpha=0.5)
plt.show()
'''#2.3分区董CTM业绩'''
qdeal_ctm_sum=qdeal_ctm_sum[qdeal_ctm_sum.index.isin(qd_lst)]
qdeal_ctm_sum=qdeal_ctm_sum.sort_values(by='总业绩',ascending=False)
plt.figure(figsize=(16,6.5),dpi=300)
plt.bar(range(len(qdeal_ctm_sum.index)),qdeal_ctm_sum.总业绩,color='dodgerblue',width=0.8)
plt.xticks(ticks=range(len(qdeal_ctm_sum.index)),\
labels=qdeal_ctm_sum.index,rotation=45,fontsize=12,fontweight='bold')
plt.yticks(ticks=range(0,10000000,1000000),\
labels=[str(int(x/10000))+'万' for x in range(0,10000000,1000000)])
plt.ylabel('CTM业绩')
plt.title('2019年'+str(noww.month-1)+'月-大战区网络CTM业绩',fontsize=16)
for x,y in zip(range(len(qdeal_ctm_sum.index)),qdeal_ctm_sum.总业绩):
plt.text(x,y+90000,str(int(y/10000)),fontsize=12,va='center',ha='center')
plt.plot(range(len(qdeal_ctm_sum.index)),\
[qdeal_ctm_sum.总业绩.sum()/len(qdeal_ctm_sum.\
index)]*len(qdeal_ctm_sum.index),'r--',alpha=0.5)
plt.annotate('大战区网络CTM平均业绩{:,} '.\
format(int(qdeal_ctm_sum.总业绩.sum()/len(qdeal_ctm_sum.index))),\
xy=(10,int(qdeal_ctm_sum.总业绩.sum()/len(qdeal_ctm_sum.index))+10000),\
fontsize=12,color='r',alpha=0.5)
plt.show()
'''#2.4分区董PTM业绩'''
qdeal_ptm=qdeal_ptm[qdeal_ptm.index.isin(qd_lst)]
qdeal_ptm=qdeal_ptm.sort_values(by='总业绩',ascending=False)
plt.figure(figsize=(16,6.5),dpi=300)
plt.bar(range(len(qdeal_ptm.index)),qdeal_ptm.总业绩,color='dodgerblue',width=0.8)
plt.xticks(ticks=range(len(qdeal_ptm.index)),\
labels=qdeal_ptm.index,rotation=45,fontsize=12,fontweight='bold')
plt.yticks(ticks=range(0,10000000,1000000),\
labels=[str(int(x/10000))+'万' for x in range(0,10000000,1000000)])
plt.ylabel('PTM业绩')
plt.title('2019年'+str(noww.month-1)+'月-大战区网络PTM业绩',fontsize=16)
for x,y in zip(range(len(qdeal_ptm.index)),qdeal_ptm.总业绩):
plt.text(x,y+30000,str(int(y/10000)),fontsize=12,va='center',ha='center')
plt.plot(range(len(qdeal_ptm.index)),\
[qdeal_ptm.总业绩.sum()/len(qdeal_ptm.index)]*len(qdeal_ptm.index),'r--',alpha=0.5)
plt.annotate('大战区网络PTM平均业绩: {:,} '.\
format(int(qdeal_ptm.总业绩.sum()/len(qdeal_ptm.index))),\
xy=(8,int(qdeal_ptm.总业绩.sum()/len(qdeal_ptm.index))+10000),\
fontsize=12,color='r',alpha=0.5)
plt.show()
'''#2.5分区董CTM单数及单均业绩'''
qdeal_ctm_sum=qdeal_ctm_sum[qdeal_ctm_sum.index.isin(qd_lst)]
qdeal_ctm_sum=qdeal_ctm_sum.sort_index()
qdeal_ctm_shu=qdeal_ctm[['Root Id','成交区董']].drop_duplicates().\
groupby('成交区董').count().sort_index()
qdeal_ctm_avg=qdeal_ctm_sum.总业绩/qdeal_ctm_shu['Root Id']
qdeal_ctm_avg=qdeal_ctm_avg.sort_values(ascending=False)
plt.figure(figsize=(16,6.5),dpi=300)
plt.subplot(211)
qdeal_ctm_shu=qdeal_ctm_shu.sort_values(by='Root Id',ascending=False)
qdeal_ctm_s=qdeal_ctm_shu['Root Id']
qdeal_ctm_s.plot(kind='bar',color='dodgerblue',label='大战区网络CTM单数')
plt.xlabel('')
plt.title('2019年'+str(noww.month-1)+'月-大战区网络CTM单数、单均业绩',fontsize=16)
plt.ylabel('大战区网络ctm成交单数')
plt.ylim([0,qdeal_ctm_s.max()+10])
plt.legend(loc='upper right')
plt.xticks(ticks=range(len(qdeal_ctm_s)),\
labels=qdeal_ctm_s.index,fontsize=12,rotation=360)
for x,y in zip(range(len(qdeal_ctm_s.index)),qdeal_ctm_s.values):
plt.text(x,y+2,str(int(y)),fontsize=12,va='center',ha='center')
plt.subplot(212)
qdeal_ctm_avg.plot(kind='bar',color='royalblue',label='大战区网络CTM单均业绩')
plt.xticks(ticks=range(len(qdeal_ctm_avg)),\
labels=qdeal_ctm_avg.index,fontsize=12,rotation=360)
for x,y in zip(range(len(qdeal_ctm_avg.index)),qdeal_ctm_avg.values):
plt.text(x,y+2000,str(int(y)),fontsize=12,va='center',ha='center')
ctm_avg=qdeal_ctm_sum.总业绩.sum()/qdeal_ctm_shu['Root Id'].sum()
plt.annotate(s=str(noww.month-1)+'月CTM单均业绩 '+str(int(ctm_avg)),\
xy=(10,int(ctm_avg)+1000),\
fontsize=12,color='r',alpha=0.5)
plt.ylabel('大战区CTM单均业绩')
plt.legend(loc='upper right')
plt.ylim([0,qdeal_ctm_avg.values.max()+5000])
plt.plot(range(len(qdeal_ctm_avg.index)),\
[int(ctm_avg)]*len(qdeal_ctm_avg.index),'r--',alpha=0.5)
plt.show()
'''#2.6分区董PTM单数及单均业绩'''
qdeal_ptm=qdeal_ptm[qdeal_ptm.index.isin(qd_lst)]
qdeal_ptm_sum=qdeal_ptm.sort_index()
qdeal_ptm_shu=ptmdeal[['成交编号','成交区董']].drop_duplicates().\
groupby('成交区董').count().sort_index()
qdeal_ptm_shu=qdeal_ptm_shu[qdeal_ptm_shu.index.isin(qd_lst)]
qdeal_ptm_avg=(qdeal_ptm_sum.总业绩/qdeal_ptm_shu.成交编号).\
sort_values(ascending=False)
plt.figure(figsize=(16,6.5),dpi=300)
plt.subplot(211)
qdeal_ptm_s=qdeal_ptm_shu.成交编号.sort_values(ascending=False)
qdeal_ptm_s.plot(kind='bar',color='dodgerblue',label='大战区网络PTM单数')
plt.ylim([0,qdeal_ptm_s.values.max()+5])
plt.xlabel('')
plt.title('2019年'+str(noww.month-1)+'月-大战区网络PTM单数、单均业绩',fontsize=16)
plt.ylabel('大战区网络ptm成交单数')
plt.legend(loc='upper right')
plt.xticks(ticks=range(len(qdeal_ptm_s)),\
labels=qdeal_ptm_s.index,fontsize=12,rotation=360)
for x,y in zip(range(len(qdeal_ptm_s.index)),qdeal_ptm_s.values):
plt.text(x,y+1,str(int(y)),fontsize=12,va='center',ha='center')
plt.subplot(212)
qdeal_ptm_avg.plot(kind='bar',color='royalblue',label='大战区网络PTM单均业绩')
if '孙举双' in qdeal_ptm_avg.index:
plt.ylim([0,qdeal_ptm_avg.values.max()+80000])
else:
plt.ylim([0,qdeal_ptm_avg.values.max()+10000])
plt.xticks(ticks=range(len(qdeal_ptm_avg)),\
labels=qdeal_ptm_avg.index,fontsize=12,rotation=360)
for x,y in zip(range(len(qdeal_ptm_avg.index)),qdeal_ptm_avg.values):
plt.text(x,y+10000,str(int(y)),fontsize=12,va='center',ha='center')
ptm_avg=qdeal_ptm_sum.总业绩.sum()/qdeal_ptm_shu.成交编号.sum()
plt.legend(loc='upper right')
if '孙举双' in qdeal_ptm_avg.index:
plt.annotate(s=str(noww.month-1)+'月网络PTM单均业绩 '+str(int(ptm_avg)),\
xy=(8,int(ptm_avg)+60000),\
fontsize=12,color='r',alpha=0.5)
else:
plt.annotate(s=str(noww.month-1)+'月网络PTM单均业绩 '+str(int(ptm_avg)),\
xy=(8,int(ptm_avg)+10000),\
fontsize=12,color='r',alpha=0.5)
plt.ylabel('大战区网络PTM单均业绩')
plt.plot(range(len(qdeal_ptm_avg.index)),\
[int(ptm_avg)]*len(qdeal_ptm_avg.index),'r--',alpha=0.5)
plt.show()
'''#2.7各区董分网络单数及业绩'''
import pandas as pd
qd_cdf=pd.read_excel('E:\\暂存\\月度成交分析报告\\Datasets\\'
'2.大战区成交数据\\201910分区董CTM成交来电数据.xlsx')
qd_cdf=qd_cdf[qd_cdf.月份==upward_month]
qd_cdf.replace({'陈宇珏直带':'陈宇珏','余昆直带':'余昆'},inplace=True)
qd_cdf=qd_cdf[qd_cdf.成交区董.isin(qd_lst)]
qd_pdf=pd.read_excel('E:\\暂存\\月度成交分析报告\\Datasets\\'
'2.大战区成交数据\\201910分区董PTM成交来电数据.xlsx')
qd_pdf=qd_pdf[qd_pdf.区董.isin(qd_lst)]
#看哪个网站对各大战区贡献CTM业绩最多
qd_cdf_eachnet=qd_cdf[['Root Id','成交类型','成交网站','月份','单数(拆分)',\
'业绩(拆分)','成交区董']].drop_duplicates().\
groupby(['成交区董','成交网站']).\
agg({'单数(拆分)':sum,'业绩(拆分)':sum}).\
reset_index().sort_values(by=['成交区董','业绩(拆分)'],\
ascending=[False,False])
qd_cdf_eachnet.to_excel('E:\\暂存\\月度成交分析报告\\Datasets\\'
'2.大战区成交数据\区董分网站ctm业绩.xlsx')
#看哪个网站对各大战区贡献PTM业绩最多
qd_pdf_eachnet=qd_pdf[['成交编号','交易类型','成交网站','月份','单数(拆分)',\
'业绩(拆分)','区董']].drop_duplicates().\
groupby(['区董','成交网站']).\
agg({'单数(拆分)':sum,'业绩(拆分)':sum}).\
reset_index().sort_values(by=['区董','业绩(拆分)'],\
ascending=[False,False])
qd_pdf_eachnet.to_excel('E:\\暂存\\月度成交分析报告\\Datasets\\'
'2.大战区成交数据\区董分网站ptm业绩.xlsx')
385
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
