如下代码,仅需将原始已处理好的需变量分析的数据读入,Y值需将字段名称修改为‘15A’即可运行。
# -*- coding: utf-8 -*-
"""
"""
import pandas as pd
import numpy as np
import xlrd
#from pandasql import sqldf
#定义读入数据
def xlsxread(self):
content=xlrd.open_workbook(self,encoding_override='gdk')
df=pd.read_excel(content,engine='xlrd')
return df
data=xlsxread(r"C:a.xls")#分析样本路径 #需修改路径
data15A=data
#判断变量类型
#数值型变量
df_new_dtypes=pd.DataFrame(data.dtypes,columns={'type'})
df_new_dtypes['title1']=df_new_dtypes.index
df_type_int=df_new_dtypes[(df_new_dtypes['type']=='int64') | (df_new_dtypes['type']=='float64')]
df_type_int=df_type_int[:-1]#剔除y值那列
cols=list(df_type_int.index)
#分类型变量
type_vec=list(df_new_dtypes[(df_new_dtypes['type']=='object')].index)
#数值型变量进行分箱
def cutby_bfw(data,list_cut,var):
df=data.copy()
df1=data.copy()
df=df.dropna(subset=[var])
percentiles=list_cut
new_box=var+'_new'
num=pd.DataFrame(df[var].unique()).shape[0]
if num<=9:
df1[var]=df1[var].fillna(-999)
df1[new_box]=df1[var]
else:
l_bin =list(np.percentile(df[var], percentiles))
#取出源数据里的最大值最小值,将申请时的最大值最小值比较后插入分配列表
for i in range(len(l_bin)):
print(i,l_bin[i])
if i<len(l_bin)-1:
if l_bin[i]==l_bin[i+1]:
l_bin[i+1]+=1/1e10
#列表去重
l_bin = list(set(l_bin))
l_bin.sort()
l_bin.insert(0,-999)
l_bin[-1]+=1/1e10
df1[var]=df1[var].fillna(-999) #缺失值填补-999
#df1[new_box]=pd.cut(df1[var],l_bin,right=False,duplicates='drop',labels = False)#lables=False,返回的是分组后的组序号,TRUE为分组后的区间划分
df1[new_box]=pd.cut(df1[var],l_bin,right=False,duplicates='drop')
return df1[new_box]
#百分比切分点
cut_list=[0,10, 20, 30, 40,50,60,70,80,90,100]
#分箱-数值型变量,list_new为分箱后的变量列表
list_new=[] #连续变量分箱后的列名
for i in range(len(cols)):
var=cols[i]
new_var=var+'_new'
list_new.append(new_var)
data15A[new_var]=cutby_bfw(data15A,cut_list,var)
print(var)
writer=pd.ExcelWriter(r"C:new.xlsx") #建一个空表
numindex=0
cols_bin=list_new[:-1]
cols_bin=cols_bin+type_vec
data1=data
data1['15A_1']=data['15A'].apply(lambda x: 1 if x>0 else 0)
data1['15A']=data1['15A_1']
for i in cols_bin:
data_new=[]
data_Iv=[]
#data_z=[]
data1=data15A[data15A[i].isnull()] #data1为空数据集
data2=data15A[data15A[i].notnull()] #data2为非空数据集
data_z=pd.DataFrame([['缺失',data1['15A'].count(),data1['15A'].sum(),data1['15A'].count()-data1['15A'].sum()]],columns=[i,'total','bad','good'])
total=data2.groupby(i)['15A'].count()
total=pd.DataFrame({'total':total})
bad=data2.groupby(i)['15A'].sum()
bad=pd.DataFrame({'bad':bad})
data3=total.merge(bad,left_index=True,right_index=True,how='left')
data3['good']=data3['total']-data3['bad']
data_3=data3.reset_index() #重建索引
data_Iv=pd.concat([data_z,data_3])
#data_new=data_Iv
data_Iv['分组占比']=data_Iv['total']/data_Iv['total'].sum()
data_Iv['组内逾期']=data_Iv['bad']/data_Iv['total']
data_Iv['WOE']=np.log((data_Iv['bad']/data_Iv['bad'].sum())/(data_Iv['good']/data_Iv['good'].sum()))
data_Iv['IV']=data_Iv['WOE']*(data_Iv['bad']/data_Iv['bad'].sum()-data_Iv['good']/data_Iv['good'].sum())
#重新生成索引,并保留索引
#data_Iv=data_Iv.reset_index()
#生成一总计表,添加至上述表格的最后一行
data_Iv['分组占比']=data_Iv['total']/data_Iv['total'].sum()
data_Iv['组内逾期']=data_Iv['bad']/data_Iv['total']
data_Iv['WOE']=np.log((data_Iv['bad']/data_Iv['bad'].sum())/(data_Iv['good']/data_Iv['good'].sum()))
data_Iv['IV']=data_Iv['WOE']*(data_Iv['bad']/data_Iv['bad'].sum()-data_Iv['good']/data_Iv['good'].sum())
data_Iv.replace([-np.Inf,np.Inf],0,inplace=True)
#重新生成索引,并保留索引
#data_Iv=data_Iv.reset_index()
#生成一总计表,添加至上述表格的最后一行
data_Iv['total_good']=data_Iv['good'].cumsum()
data_Iv['total_bad']=data_Iv['bad'].cumsum()
data_Iv['total_good%']=data_Iv['total_good']/data_Iv['good'].sum()
data_Iv['total_bad%']=data_Iv['total_bad']/data_Iv['bad'].sum()
data_Iv['KS_value']=abs(data_Iv['total_bad%']-data_Iv['total_good%'])
data_Iv_z=pd.DataFrame([['总计',data_Iv['total'].sum(),data_Iv['bad'].sum(),data_Iv['good'].sum(),data_Iv['分组占比'].sum()
,data_Iv['bad'].sum()/data_Iv['total'].sum(),0,data_Iv['IV'].sum(),max(data_Iv['total_good']),max(data_Iv['total_bad'])
,max(data_Iv['total_good%']),max(data_Iv['total_bad%']),max(data_Iv['KS_value'])]]
,columns=[i,'total','bad','good','分组占比','组内逾期','WOE','IV','total_good','total_bad','total_good%','total_bad%','KS_value'])
#data_new=data_Iv.append(data_Iv_z,ignore_index=True)
data_new=pd.concat([data_Iv,data_Iv_z])
data_new.to_excel(writer,sheet_name='15A',startrow=numindex)
#data_new.to_excel(writer,sheet_name='15A',startrow=numindex,index=False)
numindex=numindex+data_new.shape[0]+3
writer.close()