Python_决策树应用

# -*- coding: utf-8 -*-
"""
Created on Wed Sep 16 16:06:42 2020

@author: zhaoyan
"""
import pandas as pd
import numpy as np

# =============================================================================
# 一、导入数据
# =============================================================================
tot=pd.read_excel('tot.xlsx',header=0)
mm=pd.read_excel('duxiaoman.xlsx',sheet_name='mm',header=0)
a=pd.read_excel('duxiaoman.xlsx',sheet_name='a',header=0)
b=pd.read_excel('duxiaoman.xlsx',sheet_name='b',header=0)
c=pd.read_excel('duxiaoman.xlsx',sheet_name='c',header=0)
d=pd.read_excel('duxiaoman.xlsx',sheet_name='d',header=0)
e=pd.read_excel('duxiaoman.xlsx',sheet_name='e',header=0)
f=pd.read_excel('duxiaoman.xlsx',sheet_name='f',header=0)
g=pd.read_excel('duxiaoman.xlsx',sheet_name='g',header=0)

# =============================================================================
# 二、汇总整理相关数据
# =============================================================================
tot_2019_app=tot[tot.time_decision_at>='2019-01-01 00:00:00'].loc[:,['transport_id','time_decision_at','sec_platform','revolving_type_apply','num','num_appro','num_release','ever_od30_n_plus','mob6_od30_n_plus','num_release_mob6','amt_contract_w']]
tot_2019_app_mm=pd.merge(tot_2019_app,mm,how='left',on='transport_id')
tot_2019_app_a=pd.merge(tot_2019_app_mm,a,how='left',on='id')
tot_2019_app_b=pd.merge(tot_2019_app_a,b,how='left',on='id')
tot_2019_app_c=pd.merge(tot_2019_app_b,c,how='left',on='id')
tot_2019_app_d=pd.merge(tot_2019_app_c,d,how='left',on='id')
tot_2019_app_e=pd.merge(tot_2019_app_d,e,how='left',on='id')
tot_2019_app_f=pd.merge(tot_2019_app_e,f,how='left',on='id')
tot_2019_app_g=pd.merge(tot_2019_app_f,g,how='left',on='id')
tot_2019_app_g=tot_2019_app_g[~tot_2019_app_g.duplicated('transport_id')] #申请层面去除transport_id是重复的行
tot_2019_app_g=tot_2019_app_g.reset_index(drop=True)#由于删除重复行，索引不是连续从0开始，需要重置。如果你不想保留原来的index，drop=True

tot_2019_lo=tot_2019_app_g[(tot_2019_app_g.num_release==1)&(tot_2019_app_g.id>=0)&(tot_2019_app_g.ever_od30_n_plus>=0)] #筛选数据放款层面
tot_2019_lo=tot_2019_lo.reset_index(drop=True)
lo_2019=tot_2019_lo[~tot_2019_lo.duplicated('transport_id')] #去除transport_id是重复的行

data=lo_2019.drop(['transport_id', 'time_decision_at', 'sec_platform','revolving_type_apply'],1)#删除与风险无关变量
data.dtypes #查看各变量类型
# =============================================================================
# 三、对data进行描述性统计
# =============================================================================
import math
import sys
from scipy.stats import ks_2samp
from sklearn.metrics import roc_auc_score


#离散型变量重新编码：
for feature in data.columns:
    if data[feature].dtype=='object':
        data[feature]=pd.Categorical(data[feature]).codes 
#编码后无数据NAN发现变为-1
data.replace(-1,np.nan,inplace=True) 
#ks定义
get_ks = lambda y_pred,y_true: ks_2samp(y_pred[y_true==1], y_pred[y_true==0]).statistic
#IV函数定义：等频分5组
def CalcIV(x,y):
    if(len(pd.value_counts(x)))<5:
       xi=pd.crosstab(x,y)
    else :
       x=pd.qcut(x,q=5,duplicates="drop")
       xi=pd.crosstab(x,y)
    xx=pd.value_counts(y)
    xir=xi/xx
    xir['woe']=np.log(xir[1]/xir[0])
    xir['iv']=(xir[1]-xir[0])*xir['woe']
    return  xir['iv'].sum()
#描述性变量统计：汇总在sva中
sva = pd.DataFrame(np.zeros([len(data.columns[0:150]),12]))
sva.shape
for i in range(150):
    key =data.iloc[:,i]
    x = data[~(np.isnan(key))].iloc[:,i]
    y = data[~(np.isnan(key))].loc[:,'ever_od30_n_plus']
    sva.iloc[i,0] =data.iloc[:,i].name
    sva.iloc[i,1] = len(key)
    sva.iloc[i,2] = len(x)
    sva.iloc[i,3] = y.sum()
    sva.iloc[i,4] = len(x)/(len(key))
    sva.iloc[i,5] = y.sum()/len(x)
    sva.iloc[i,6] = x.min()
    sva.iloc[i,7] = x.max()
    sva.iloc[i,8] = x.mean()
    sva.iloc[i,9] = x.var()
    sva.iloc[i,10] = CalcIV(x,y)
    sva.iloc[i,11] = get_ks(x,y)
#修改列名称   
sva.columns = ['names','num_all','num_use','num_bad','chade_rate','bad_rate','min','max','mean','var','iv','ks',]

#输出文件
sva.to_excel("sva.xlsx")
# =============================================================================
# 四、对data跑决策树   Y变量为'ever_od30_n_plus'
# ============================================================================= 

from sklearn import tree
from sklearn.tree import DecisionTreeClassifier, export_graphviz
import graphviz
from sklearn.model_selection import train_test_split# 数据划分
from sklearn.feature_extraction import DictVectorizer
data=tot_2019_lo[['a1','d1','e1','f1','g1','ever_od30_n_plus']]#调整入模变量
data=data.fillna(value=-99999999)#DecisionTreeClassifier要求X中不能有空值，填充-99999999
X_train, X_test, y_train, y_test = train_test_split(data.iloc[:,0:5], data['ever_od30_n_plus'],train_size = 0.99,random_state = 1234)#保证X中无Y变量
clf=tree.DecisionTreeClassifier(criterion='gini',max_depth=3,min_samples_split=2,min_samples_leaf=50) #建立决策树 
clf.fit(X_train,y_train)#决策树拟合
y_test_pre = clf.predict(X_test) #预测A

num = data.shape[0] #样本总数
num_train = X_train.shape[0] #训练集样本数目
num_test = num - num_train #测试集样本数目
acc = sum(y_test_pre == y_test) / num_test

#上面几行代码可以用下面一行代替
clf.score(X_test, y_test)

##导出决策树pdf格式
dot_data=tree.export_graphviz(clf,out_file=None,feature_names=X_train.columns,class_names=str(np.unique(y_train)),filled=True,rounded=True,special_characters=True)
graph = graphviz.Source(dot_data)

graph.render("DecisionTree")      #在同级目录下生成DecisionTree.pdf文件

# =============================================================================
# 五、汇总导出数据（申请+放款）根据sva及决策树筛选有效变量
# ============================================================================= 
#相关系数
corr_duxiaoman=tot_2019_lo[['a1','a4','a5','a6','a9','a10','a11','a27','a31','d1','e1','f1','g1','c3','c7','c16']].corr()

corr_duxiaoman.to_excel("corr_duxiaoman.xlsx")


######放款层面
#等频分组
tot_2019_lo['d1_cut_10']=pd.qcut(tot_2019_lo['d1'],q=10,duplicates="drop")
tot_2019_lo['g1_cut_10']=pd.qcut(tot_2019_lo['g1'],q=10,duplicates="drop")
tot_2019_lo['f1_cut_10']=pd.qcut(tot_2019_lo['f1'],q=10,duplicates="drop")
tot_2019_lo['a11_cut_10']=pd.qcut(tot_2019_lo['a11'],q=10,duplicates="drop")
tot_2019_lo['a6_cut_10']=pd.qcut(tot_2019_lo['a6'],q=10,duplicates="drop")
tot_2019_lo['a10_cut_10']=pd.qcut(tot_2019_lo['a10'],q=10,duplicates="drop")
tot_2019_lo['a5_cut_10']=pd.qcut(tot_2019_lo['a5'],q=10,duplicates="drop")
tot_2019_lo['a4_cut_10']=pd.qcut(tot_2019_lo['a4'],q=10,duplicates="drop")
tot_2019_lo['a9_cut_10']=pd.qcut(tot_2019_lo['a9'],q=10,duplicates="drop")
tot_2019_lo['a31_cut_10']=pd.qcut(tot_2019_lo['a31'],q=10,duplicates="drop")
tot_2019_lo['a27_cut_10']=pd.qcut(tot_2019_lo['a27'],q=10,duplicates="drop")
tot_2019_lo['a1_cut_10']=pd.qcut(tot_2019_lo['a1'],q=10,duplicates="drop")
tot_2019_lo['e1_cut_10']=pd.qcut(tot_2019_lo['e1'],q=10,duplicates="drop")

#设置拒贷规则
tot_2019_lo['rule_a']=tot_2019_lo.apply(lambda row:1 if row['a11']>=7 and row['d1']>=48  else 0,axis=1)
tot_2019_lo['rule_b']=tot_2019_lo.apply(lambda row:1 if row['a11']>=7 and row['a1']>=7 and row['a6']>=86  else 0,axis=1)
tot_2019_lo['rule_c']=tot_2019_lo.apply(lambda row:1 if row['f1']<=485 and row['e1']<=56 and row['g1']<=417  else 0,axis=1)
tot_2019_lo['rule_d']=tot_2019_lo.apply(lambda row:1 if row['c7']=='Ct' or row['c16']=='Ct'  else 0,axis=1)
tot_2019_lo['rule_e']=tot_2019_lo.apply(lambda row:1 if row['c3']=='A' or row['c3']=='B' or row['c3']=='C'  else 0,axis=1)
tot_2019_lo['rule_abcde']=tot_2019_lo.apply(lambda row:1 if (row['a11']>=7 and row['d1']>=48) or (row['a11']>=7 and row['a1']>=7 and row['a6']>=86) or (row['f1']<=485 and row['e1']<=56 and row['g1']<=417) or (row['c7']=='Ct' or row['c16']=='Ct') or (row['c3']=='A' or row['c3']=='B' or row['c3']=='C') else 0,axis=1)

for i in range(len(tot_2019_lo.index)):
    print(i)
  
    if (tot_2019_lo.iloc[i]['a11']>=7) & (tot_2019_lo.iloc[i]['d1']>=48):
       tot_2019_lo.loc[tot_2019_lo.index==i,'rule_seg'] ='rule_a'
    elif (tot_2019_lo.iloc[i]['a11']>=7) & (tot_2019_lo.iloc[i]['a1']>=7) & (tot_2019_lo.iloc[i]['a6']>=86):
          tot_2019_lo.loc[tot_2019_lo.index==i,'rule_seg'] ='rule_b'
    elif (tot_2019_lo.iloc[i]['f1']<=485) & (tot_2019_lo.iloc[i]['e1']<=56) & (tot_2019_lo.iloc[i]['g1']<=417):
          tot_2019_lo.loc[tot_2019_lo.index==i,'rule_seg'] ='rule_c'
    elif (tot_2019_lo.iloc[i]['c7']=='Ct') | (tot_2019_lo.iloc[i]['c16']=='Ct'):
          tot_2019_lo.loc[tot_2019_lo.index==i,'rule_seg'] ='rule_d'
    elif (tot_2019_lo.iloc[i]['c3']=='A' ) | (tot_2019_lo.iloc[i]['c3']=='B') | (tot_2019_lo.iloc[i]['c3']=='C'):
         tot_2019_lo.loc[tot_2019_lo.index==i,'rule_seg'] ='rule_e'
    else:
        tot_2019_lo.loc[tot_2019_lo.index==i,'rule_seg'] ='rule_null'

#导出放款层面表
tot_2019_lo.to_excel("tot_duxiaoman_data.xlsx")


#######申请层面

#设置拒贷规则
tot_2019_app_g['rule_a']=tot_2019_app_g.apply(lambda row:1 if row['a11']>=7 and row['d1']>=48  else 0,axis=1)
tot_2019_app_g['rule_b']=tot_2019_app_g.apply(lambda row:1 if row['a11']>=7 and row['a1']>=7 and row['a6']>=86  else 0,axis=1)
tot_2019_app_g['rule_c']=tot_2019_app_g.apply(lambda row:1 if row['f1']<=485 and row['e1']<=56 and row['g1']<=417   else 0,axis=1)
tot_2019_app_g['rule_d']=tot_2019_app_g.apply(lambda row:1 if row['c7']=='Ct' or row['c16']=='Ct'  else 0,axis=1)
tot_2019_app_g['rule_e']=tot_2019_app_g.apply(lambda row:1 if row['c3']=='A' or row['c3']=='B' or row['c3']=='C'  else 0,axis=1)
tot_2019_app_g['rule_abcde']=tot_2019_app_g.apply(lambda row:1 if (row['a11']>=7 and row['d1']>=48) or (row['a11']>=7 and row['a1']>=7 and row['a6']>=86) or (row['f1']<=485 and row['e1']<=56 and row['g1']<=417) or (row['c7']=='Ct' or row['c16']=='Ct') or (row['c3']=='A' or row['c3']=='B' or row['c3']=='C') else 0,axis=1)




#case when 

for i in range(len(tot_2019_app_g.index)):
    print(i)
  
    if (tot_2019_app_g.iloc[i]['a11']>=7) & (tot_2019_app_g.iloc[i]['d1']>=48):
       tot_2019_app_g.loc[tot_2019_app_g.index==i,'rule_seg'] ='rule_a'
    elif (tot_2019_app_g.iloc[i]['a11']>=7) & (tot_2019_app_g.iloc[i]['a1']>=7) & (tot_2019_app_g.iloc[i]['a6']>=86):
          tot_2019_app_g.loc[tot_2019_app_g.index==i,'rule_seg'] ='rule_b'
    elif (tot_2019_app_g.iloc[i]['f1']<=485) & (tot_2019_app_g.iloc[i]['e1']<=56) & (tot_2019_app_g.iloc[i]['g1']<=417):
          tot_2019_app_g.loc[tot_2019_app_g.index==i,'rule_seg'] ='rule_c'
    elif (tot_2019_app_g.iloc[i]['c7']=='Ct') | (tot_2019_app_g.iloc[i]['c16']=='Ct'):
          tot_2019_app_g.loc[tot_2019_app_g.index==i,'rule_seg'] ='rule_d'
    elif (tot_2019_app_g.iloc[i]['c3']=='A' ) | (tot_2019_app_g.iloc[i]['c3']=='B') | (tot_2019_app_g.iloc[i]['c3']=='C'):
         tot_2019_app_g.loc[tot_2019_app_g.index==i,'rule_seg'] ='rule_e'
    else:
        tot_2019_app_g.loc[tot_2019_app_g.index==i,'rule_seg'] ='rule_null'
        
        
#导出申请表
tot_2019_app_g.to_excel("tot_2019_app_g.xlsx")