虚拟号码来电数据统计-python脚本

# -*- coding: utf-8 -*-
"""
Created on Fri Mar 29 10:36:39 2019

@author: zhanggl21
"""

'''
运行此脚本前，
1.先把导出的excel表格式整理好.
2.再把字段“通话时长 秒”修改为“通话时长”
3.并放在桌面，命名为“虚拟号码通话记录”
4.另，'所有号码数'要不定时更新。
'''


#将数据表导入内存
import pandas as pd

df1=pd.read_excel(r'E:\暂存\虚拟号码通话记录\2019年(3.15-5.4)虚拟号码通话记录.xlsx',\
                 sheet_name='虚拟号码通话记录')

df2=pd.read_excel(r'E:\暂存\虚拟号码通话记录\5.5-5.13虚拟号码通话记录.xlsx',\
                 sheet_name='虚拟号码通话记录')

lst=[df1,df2]

df=pd.concat(lst,join='outer',ignore_index=True)

#删掉“序号”列
del df['序号']

#查看虚拟号码有无缺失值，有缺失值记录删掉
df=df.dropna(subset=['被叫号码'])


#计算已经拨打的号码数并将结果存入结果表

df_result=df[['被叫号码','分机号']].drop_duplicates().groupby('被叫号码').\
count().sort_index()

df_result=df_result.rename(columns={'分机号':'已经拨打的号码数'})

#增加新列"所有号码的个数"
df_result['所有号码的个数']=7490*4

#增加新列"拨打完成率"
df_result['拨打完成率']=df_result['已经拨打的号码数']/df_result['所有号码的个数']

#增加新列'接通的号码数'
df_result['接通的号码数']=df[(df.是否接通=='是')][['被叫号码','分机号']].\
drop_duplicates().groupby('被叫号码').count().sort_index().values

#增加新列'号码接通率'
df_result['号码接通率']=df_result['接通的号码数']/df_result['已经拨打的号码数']

#增加新列'接通的通话记录'
df_result['接通的通话记录']=df[(df.是否接通=='是')][['被叫号码','分机号']].\
groupby('被叫号码').count().sort_index().values

#增加新列'30s以上通话记录'
df_result['30s以上通话记录']=df[(df.是否接通=='是')&(df['通话时长']>=30)]\
[['被叫号码','分机号']].groupby('被叫号码').count().sort_index().values



#增加新列‘30s以上占比’
df_result['30s以上占比']=df_result['30s以上通话记录']/df_result['接通的通话记录']




#增加汇总行(先初始化再赋值)
df_result.loc['汇总',['已经拨打的号码数','所有号码的个数','接通的号码数',\
                    '接通的通话记录','30s以上通话记录']]=0
                    
df_result.loc['汇总',['30s以上通话记录','接通的通话记录']]=df_result[0:6].\
apply(lambda x: x.sum())

df_result.loc['汇总','已经拨打的号码数']=df.分机号.drop_duplicates().count()

df_result.loc['汇总','所有号码的个数']=7490*4

df_result.loc['汇总','接通的号码数']=df[(df.是否接通=='是')].\
分机号.drop_duplicates().count()

df_result.loc['汇总','拨打完成率']=df_result.loc\
['汇总','已经拨打的号码数']/df_result.loc['汇总','所有号码的个数']

df_result.loc['汇总','号码接通率']=df_result.loc\
['汇总','接通的号码数']/df_result.loc['汇总','已经拨打的号码数']

df_result.loc['汇总','30s以上占比']=df_result.loc\
['汇总','30s以上通话记录']/df_result.loc['汇总','接通的通话记录']

#将分机号替换为大区名称
df_result=df_result.rename(index={18121493732:'余坤',\
                                  18121493741:'陈宇珏',\
                                  18121493739:'吕尚伦',\
                                  18121493735:'李锦德',\
                                  18121493625:'新房',\
                                  18121493614:'海外'})

#将结果表导出到桌面
df_result.to_excel(r'd:\Users\zhanggl21\Desktop\new虚拟号码通话数据总结.xlsx')





#还未拨打的分机号
import pandas as pd
qb=pd.read_excel(r'E:\全部分机号-截至4.16.xlsx')
qb.info()
daochu=qb[~qb.分机号.isin(df.分机号.drop_duplicates())]
print('还有',len(daochu.分机号.unique()),'个分机号没打')