等频分箱代码

   如下代码，仅需将原始已处理好的需变量分析的数据读入，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()