资管过程日报指标异常分析

本文主要是介绍资管过程日报指标异常分析，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

资管过程日报指标异常分析

1.数据处理

2.异常值标准制定

3.画图

4.导出word

#!/usr/bin/env python
# coding: utf-8import matplotlib.pyplot as plt
import pandas as pd 
import numpy as np
plt.rcParams['font.sans-serif']=['Simhei'] #显示中文
plt.rcParams['axes.unicode_minus']=False   #显示负号col=['日期','类别','队列','队列人数','当日剩余案件量','尝试呼叫次数','当日人均案件量','尝试呼叫次数','接通次数','接通率','当日有效呼出次数','联络客户数','可联客户数','有效联络客户数','可联客户占比','有效联络率','PTP']
xlsx = pd.ExcelFile('D:\我的项目\资管过程日报指标异常值分析\资管过程日报.xlsx')
df = pd.read_excel(xlsx, '原始数据分开展示',header=0,usecols=col)import datetime
jsontime = '2021-03-01'  # 数据开始时间，注意调整
date_start=datetime.datetime.strptime(jsontime,"%Y-%m-%d")
df_21=df[df['日期']>=date_start]nan_lines = df_21['类别'].isnull()  # 找出类别为空的行
mm=df_21[nan_lines]
df_remain=df_21.drop(index=mm.index, axis=0) # 删除s类别为空的行df_remain2=df_remain.drop(['日期', '类别','队列'], axis=1) df_remain2=df_remain2.astype('float')
df_remain2.dtypes
df_remain2.info()df_remain1 = df_remain.reindex(columns=['日期', '类别','队列'])df2 = pd.concat([df_remain1, df_remain2], axis=1)
queue=['城市信贷_M0(资管整体)','城市信贷_M0_预测后手工队列','城市信贷_M0_预测外呼队列','城市信贷_M1_冠军队列','城市信贷_M1_挑战者队列','城市信贷M2','城市信贷M3','城市信贷高期M4-M6','城市信贷高期M7-M12','城市信贷高期M13+','宜人贷预催收','宜人贷初期手工','宜人贷中期M2','宜人贷中期M3','宜人贷高期M4-M6','宜人贷高期M7-M12','宜人贷高期M13+','宜人小贷_M0(资管整体)','宜人小贷初期','宜人小贷M2','宜车贷M2','宜车贷M3','宜车贷高期M4-M6','宜车贷高期M7+']
df3=df2[df2['类别'].isin(queue)]
df_group = df3.groupby(['类别','日期']).agg({'队列人数':'sum','当日剩余案件量':'sum','尝试呼叫次数':'sum','接通次数':'sum','当日有效呼出次数':'sum','联络客户数':'sum','可联客户数':'sum','有效联络客户数':'sum','PTP':'sum'})df_group['人均案件量']=df_group['当日剩余案件量']/df_group['队列人数']
df_group['人均接通量']=df_group['接通次数']/df_group['队列人数']
df_group['人均尝试呼叫量']=df_group['尝试呼叫次数']/df_group['队列人数']
df_group['呼叫强度']=df_group['尝试呼叫次数']/df_group['当日剩余案件量']
df_group['接通率']=df_group['接通次数']/df_group['尝试呼叫次数']
df_group['可联率']=df_group['可联客户数']/df_group['联络客户数']
df_group['PTP率']=df_group['PTP']/df_group['可联客户数']
df_group=df_group.rename(columns={"当日剩余案件量":"分案量","队列人数":"催收员数量"})#识别异常值index_list=['分案量','催收员数量','人均案件量','人均接通量','人均尝试呼叫量','呼叫强度','接通率','可联率','PTP率']def outlier(dataframe,a, threshold=2.2):dd = dataframe[a]ff = dataframe.reindex(columns=[a])   MAD = (dd - dd.median()).abs().median()if MAD!=0:zscore = ((dd - dd.median())* 0.6475 /MAD).abs()        ff.loc[:,'isAnomaly'] = zscore > thresholdelse:ff.loc[:,'isAnomaly']=Falseff.loc[:,'med'] = dd.median()ff.loc[:,'mad'] = MADreturn ffdf_outlier = pd.DataFrame(data=[])
for group in queue:df_group_tag=df_group.xs(key=group, level='类别')for ind in index_list:da = outlier(df_group_tag,ind)da['index']=indda.columns=['value','isAnomaly','med','mad','index']da['group']=groupdf_outlier=pd.concat((da,df_outlier), axis=0)        df_outlier2=df_outlier.loc[df_outlier.isAnomaly==True,:]    prd=lambda x: '城市信贷' if '城市信贷' in x else ('宜人贷' if '宜人贷' in x else ('宜人小贷' if '宜人小贷' in x else '宜车贷'))
df_outlier2['prd']=df_outlier2['group'].apply(prd)
df_outlier['prd']=df_outlier['group'].apply(prd)df11 = pd.pivot_table(df_outlier, index = ['group','日期'], columns = ['index'], aggfunc = {'value':'sum'}).reset_index()df11.drop([0, 1], axis=0, inplace=True)
df11.columns=['group','日期','PTP率','人均尝试呼叫量','人均接通量','人均案件量','催收员数量','分案量','可联率','呼叫强度','接通率']import time
today=time.strftime('%Y-%m-%d',time.localtime(time.time()))
path="D:/我的项目/资管过程日报指标异常值分析/"
with pd.ExcelWriter(path+"资管过程日报异常值_%s.xlsx" %today) as writer:df_outlier.to_excel(writer, sheet_name='所有指标')df_outlier2.to_excel(writer, sheet_name='异常数据')# df11.to_excel(writer, sheet_name='透视表')writer = pd.ExcelWriter(path+"资管过程日报异常值_%s_拆分.xlsx" %today)
for group in list(queue):df12 = df11[df11['group'] == group]df12.to_excel(writer, sheet_name= group ,index=False)
writer.save()# 作图
for group in queue:df_group_tag=df_group.xs(key=group, level='类别')for ind in index_list:da = outlier(df_group_tag,ind)da['index']=indda.columns=['value','isAnomaly','med','mad','index']da['date']=da.indexda['date']=da['date'].apply(lambda x:x.strftime('%m-%d')) #转换成月日格式fig = plt.figure()fig.set_size_inches(10,6)ax1 = fig.add_subplot(2, 1, 1)ax2 = fig.add_subplot(2, 1, 2)        ax1.plot(da['date'],da['value'],color='#27727B',lw=1.5)        ax2.plot(da['date'],da['isAnomaly'],color='#C1232B',lw=1.5)plt.title("%s" %ind, fontsize=12)plt.tight_layout()fig.savefig('D:/我的项目/资管过程日报指标异常值分析/图片/%s_%s.png' %(group,ind))
#         print(group)plt.close()   #图片太多的情况下，需要关闭plt，否则会出现warning# 图片导入到word里from docx import Document #创建文档
from docx.oxml.ns import qn #中文
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT #段落
from docx.shared import Pt,RGBColor,Mm,Cm#大小磅数/字号
from openpyxl import load_workbook
from docx.enum.section import WD_ORIENTATION, WD_SECTION_START # 导入节方向和分解符类型
from docx.enum.text import WD_ALIGN_PARAGRAPHword_document = Document()  # 创建word文档对象
word_document.styles['Normal'].font.name = u'微软雅黑'  # 正文/标题1/标题2 （英文）
word_document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑')  # 中文# ----段落创建P1 标题行-----
name='资管过程日报异常值分析'
run = word_document.add_heading('', level=1).add_run(u"%s" %name)  #添加标题
run.font.name=u'微软雅黑'
run._element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑') 
run.font.size = Pt(20)
run.bold = Truefor title in queue:p2 = word_document.add_heading('', level=2).add_run(u"%s" %title)  #添加标题p2.font.name=u'微软雅黑'p2._element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑') p2.font.size = Pt(16)p2.bold = Truefor ind in index_list:# ----段落创建-----   p5 = word_document.add_paragraph()  # 向word添加段落p5.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER  # 段落居中对齐# ----添加标题---p5 = word_document.add_heading('', level=3).add_run(u"%s" %ind)  #添加标题p5.font.name=u'微软雅黑'p5._element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑') p5.font.size = Pt(16)p5.bold = True# ----段落创建---p4 = word_document.add_paragraph()  # 向word添加段落p4.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER  # 段落居中对齐# ----添加图片---run_text_4 = p4.add_run()run_text_4.add_picture('D:/我的项目/资管过程日报指标异常值分析/图片/%s_%s.png' %(title,ind) ,width=Mm(200))
#         print('pic/%s_%s.png' %(title,ind))word_document.save('D:/我的项目/资管过程日报指标异常值分析/结果/%s_%s.docx' % (name,today))