本数据为滴滴司机使用油品贷的数据。
油品贷的坏账率高达5%，非常高，一定是会赔钱的。并且能够通过欺诈检测。
来申请油品贷的司机本身是已经有A卡了，A卡评级为A-F。本来是只有F不放款，但是在油品贷上只有评级为A放款才能不亏钱。
滴滴是和很多加油站有合作的，加油站会给滴滴提供司机数据。

导入相关的包

import pandas as pd
import numpy as np#消除Warning
import warnings
warnings.filterwarnings("ignore")

读取油品数据集

data = pd.read_excel("oil_data_for_tree.xlsx")
data.head()

查看数据相关信息

set(data.class_new)
#{'A', 'B', 'C', 'D', 'E', 'F'}

计算下坏账率

set(data.bad_ind)
#{0, 1}
data.bad_ind.sum()/data.bad_ind.count()
#0.01776363887846035

将特征根据不同情况分为3类

• org_lst 不需要做特殊变换，直接去重
• agg_lst 数值型变量做聚合
• dstc_lst 文本型变量做cnt

org_lst = ['uid','create_dt','oil_actv_dt','class_new','bad_ind']
agg_lst = ['oil_amount','discount_amount','sale_amount','amount','pay_amount','coupon_amount','payment_coupon_amount']
dstc_lst = ['channel_code','oil_code','scene','source_app','call_source']

数据重组

#复制，查看缺失情况
df = data[org_lst].copy()
df[agg_lst] = data[agg_lst].copy()
df[dstc_lst] = data[dstc_lst].copy()

查看缺失情况

df.isna().sum()
#uid                         0
#create_dt                4944
#oil_actv_dt                 0
#class_new                   0
#bad_ind                     0
#oil_amount               4944
#discount_amount          4944
#sale_amount              4944
#amount                   4944
#pay_amount               4944
#coupon_amount            4944
#payment_coupon_amount    4946
#channel_code                0
#oil_code                    0
#scene                       0
#source_app                  0
#call_source                 0
#dtype: int64

对特征creat_dt进行补全

对creat_dt做补全，用oil_actv_dt来填补，并且截取6个月的数据。
构造变量的时候不能直接对历史所有数据做累加。
否则随着时间推移，变量分布会有很大的变化。

def time_isna(x,y):if str(x) == "NaT":x = yelse:x = xreturn x
df2 = df.sort_values(["uid","create_dt"],ascending = False)
df2["create_dt"] = df2.apply(lambda x:time_isna(x.create_dt,x.oil_actv_dt),axis = 1)
df2["dtn"] = (df2.oil_actv_dt - df2.create_dt).apply(lambda x:x.days)
df = df2[df2["dtn"] < 180]
df
#()

对特征org_list进行处理

对org_list求历史贷款天数的最大间隔，并且去重

base = df[org_lst]
base["dtn"] = df["dtn"]
base = base.sort_values(['uid','create_dt'],ascending = False)
base = base.drop_duplicates(['uid'],keep = 'first')    #去重操作
base.shape
#(11099, 6)

变量衍生（重点）

#变量衍生
gn = pd.DataFrame()
for i in agg_lst:  tp = pd.DataFrame(df.groupby('uid').apply(lambda df:len(df[i])).reset_index())  tp.columns = ['uid',i + '_cnt']  if gn.empty == True:  gn = tp  else:  gn = pd.merge(gn,tp,on = 'uid',how = 'left')      tp = pd.DataFrame(df.groupby('uid').apply(lambda df:np.where(df[i]>0,1,0).sum()).reset_index())  tp.columns = ['uid',i + '_num']  if gn.empty == True:  gn = tp  else:  gn = pd.merge(gn,tp,on = 'uid',how = 'left')  tp = pd.DataFrame(df.groupby('uid').apply(lambda df:np.nansum(df[i])).reset_index())  tp.columns = ['uid',i + '_tot']  if gn.empty == True:  gn = tp  else:  gn = pd.merge(gn,tp,on = 'uid',how = 'left')  tp = pd.DataFrame(df.groupby('uid').apply(lambda df:np.nanmean(df[i])).reset_index())  tp.columns = ['uid',i + '_avg']  if gn.empty == True:  gn = tp  else:  gn = pd.merge(gn,tp,on = 'uid',how = 'left')  tp = pd.DataFrame(df.groupby('uid').apply(lambda df:np.nanmax(df[i])).reset_index())  tp.columns = ['uid',i + '_max']  if gn.empty == True:  gn = tp  else:  gn = pd.merge(gn,tp,on = 'uid',how = 'left')  tp = pd.DataFrame(df.groupby('uid').apply(lambda df:np.nanmin(df[i])).reset_index())  tp.columns = ['uid',i + '_min']  if gn.empty == True:  gn = tp  else:  gn = pd.merge(gn,tp,on = 'uid',how = 'left')  tp = pd.DataFrame(df.groupby('uid').apply(lambda df:np.nanvar(df[i])).reset_index())  tp.columns = ['uid',i + '_var']  if gn.empty == True:  gn = tp  else:  gn = pd.merge(gn,tp,on = 'uid',how = 'left')  tp = pd.DataFrame(df.groupby('uid').apply(lambda df:np.nanmean(df[i])/max(np.nanvar(df[i]),1)).reset_index())  tp.columns = ['uid',i + '_bianyi']  if gn.empty == True:  gn = tp  else:  gn = pd.merge(gn,tp,on = 'uid',how = 'left') 

对dstc_lst变量求distinct个数

gc = pd.DataFrame()
for i in dstc_lst:tp = pd.DataFrame(df.groupby('uid').apply(lambda df:len(set(df[i]))).reset_index())tp.columns = ['uid',i + '_dstc']  if gc.empty == True:  gc = tp  else:  gc = pd.merge(gc,tp,on = 'uid',how = 'left')  

将变量组合在一起

fn = pd.merge(base,gn,on= 'uid')
fn = pd.merge(fn,gc,on= 'uid') 
fn

fn = fn.fillna(0) #组合后会有很多空值，此处填充为0

用决策树进行训练

x = fn.drop(['uid','oil_actv_dt','create_dt','bad_ind','class_new'],axis = 1)
y = fn.bad_ind.copy()
from sklearn import tree
dtree = tree.DecisionTreeRegressor(max_depth = 2,min_samples_leaf = 500,min_samples_split = 5000)
#限制树的最大深度
#叶子最少包含样本的个数
#节点必须包含训练样本的个数
dtree = dtree.fit(x,y) #利用x的值，预测y是否是坏人

输出决策树图像，并作出决策

feature_names = x.columns
import matplotlib.pyplot as plt
plt.figure(figsize=(12,9),dpi=80)
tree.plot_tree(dtree,filled = True,feature_names = feature_names)

value就是badrate
重新计算坏账率

sum(fn.bad_ind)/len(fn.bad_ind)
#0.04658077304261645

#生成策略
dff1 = fn.loc[(fn.amount_tot>48077.5)&(fn.coupon_amount_cnt>3.5)].copy()
dff1['level'] = 'oil_A'
dff2 = fn.loc[(fn.amount_tot>48077.5)&(fn.coupon_amount_cnt<=3.5)].copy()
dff2['level'] = 'oil_B'
dff3 = fn.loc[(fn.amount_tot<=48077.5)].copy()
dff3['level'] = 'oil_C'dff1.head()

dff1 = dff1.append(dff2)
dff1 = dff1.append(dff3)
dff1 = dff1.reset_index(drop = True)
dff1.head()

last = dff1[['class_new','level','bad_ind','uid','oil_actv_dt','bad_ind']].copy()
last['oil_actv_dt'] = last['oil_actv_dt'] .apply(lambda x:str(x)[:7]).copy()
last.head(5)

​​​​​​​​