金融风控项目-# 相关业务介绍

* 现金贷  

* 单期产品
* 多期产品
* 循环额度产品

* 砍头息：短期产品， （服务费）
* 等额本金
* 等额本息

  * 购买有支付牌照机构的POS机进行套现，手续费0.6%

    import pandas as pd
    import datetime
    from pyecharts.charts import *
    from pyecharts import options as opts
    df1 = pd.read_excel('data/业务数据.xls') 
    #要使用原始数据构建新指标，所以保留原始数据，copy新的数据，在新的数据中创建新指标
    df2 = df1.copy()
    df2.head()

    df2.info()

>     <class 'pandas.core.frame.DataFrame'>
>     RangeIndex: 5257 entries, 0 to 5256
>     Data columns (total 14 columns):
>     #   Column  Non-Null Count  Dtype  
>     ---  ------  --------------  -----  
>     0   销售      5257 non-null   object
>     1   账单状态    5257 non-null   object
>     2   账单周期    5257 non-null   object
>     3   账单金额    5257 non-null   float64
>     4   开票金额    5010 non-null   float64
>     5   实收金额    4470 non-null   float64
>     6   未收金额    5010 non-null   float64
>     7   预计付款日   5256 non-null   object
>     8   应付日期    5257 non-null   object
>     9   商务催收日期  5257 non-null   object
>     10  账期      5257 non-null   int64  
>     11  实际到账日   4387 non-null   object
>     12  开票日期    4996 non-null   object
>     13  客服      5257 non-null   object
>     dtypes: float64(4), int64(1), object(9)
>     memory usage: 575.1+ KB
>  
>  
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>       
>  
>

    df2.describe()

    # 获取最大的日期，作为当前时间
    today_time = pd.to_datetime(df2.实际到账日.fillna('0').max())
    
    df2['实收金额'] = df2.实收金额.fillna(0)
    df2['开票金额'] = df2.开票金额.fillna(0)
    df2['未收金额'] = df2.未收金额.fillna(0)
    
    df2['账单周期'] = pd.to_datetime(df2.账单周期)
    df2['应付日期'] = pd.to_datetime(df2.应付日期)
    
    df2['实际到账日'] = pd.to_datetime(df2.实际到账日).fillna(today_time)

    df2['是否到期'] = df2.apply(lambda x : 0 if x.应付日期 > today_time else 1,axis=1)
    
    df2['是否到期90天'] =  ( today_time - df2.应付日期 ).map(lambda x : 1 if x.days >= 90 else 0)
    
    df2['未收金额2'] =  (df2.账单金额 - df2.实收金额)
    
    df2['历史逾期天数'] = df2.apply(lambda x : (x.实际到账日 -  x.应付日期).days if x.未收金额2 == 0  else  (today_time - x.应付日期).days,axis=1)
    
    df2['当前逾期天数'] = df2.apply(lambda x : (x.历史逾期天数) if x.未收金额2 > 0  else 0 ,axis = 1)

    df3 =df2.copy()
    #创建’账单季度‘字段，将日期转换成季度
    df3['账单季度'] = df3['账单周期'].map(lambda x : x.to_period('Q'))
    #提取2017年3季度到2018年4季度数据
    df3 = df3[(df3['账单季度']<='2018Q4') & (df3['账单季度']>='2017Q3')]
    df3.shape

>     (3856, 21)
>  
>  
>       
>  
>

    #账单金额
    fn1 = df3.groupby('账单季度')[['账单金额']].sum()
    fn1.columns = ['账单金额']
    fn1

    #90天到期金额
    df4 = df3[(df3.是否到期90天 == 1)]
    fn2 = df4.groupby('账单季度')[['账单金额']].sum()
    fn2.columns = ['到期金额']
    fn2

    df4 = df3[(df3.是否到期90天 == 1)]
    fn3 = df4.groupby('账单季度')[['未收金额2']].sum()
    fn3.columns = ['当前逾期90+金额']
    fn3

    dfs = [fn1,fn2,fn3]
    final1 = pd.concat(dfs,axis=1)
    final1

    final1['90+净坏账率'] = round(final1['当前逾期90+金额'] / final1.到期金额,3)
    final1

    bar = (
    Bar()
    .add_xaxis(list(final1.index.values.astype(str)))
    .add_yaxis(
        "账单金额",
        list(final1.账单金额),
        yaxis_index=0,
        color="#5793f3",
    )
    .set_global_opts(
        title_opts=opts.TitleOpts(title="90+净坏账率"),
    )
    .extend_axis(
        yaxis=opts.AxisOpts(
            name="90+净坏账率",
            type_="value",
            min_=0,
            max_=0.014,
            position="right",
            axisline_opts=opts.AxisLineOpts(
                linestyle_opts=opts.LineStyleOpts(color="#d14a61")
            ),
            axislabel_opts=opts.LabelOpts(formatter="{value}"),
        )
    )
    )
    line = (
    Line()
    .add_xaxis(list(final1.index.values.astype(str)))
    .add_yaxis(
        "90+净坏账率",
        list(final1['90+净坏账率']),
        yaxis_index=1,
        color="#675bba",
        label_opts=opts.LabelOpts(is_show=False),
    )
    )
    bar.overlap(line).render_notebook()

    #60天账期的账单金额
    df4 = df3[(df3.账期 == 60)&(df3.是否到期 == 1)]
    fn1 = df4.groupby('账单季度')[['账单金额']].sum()
    fn1.columns = ['60天账期的账单金额']
    #60天账期的入催金额
    df4 = df3[(df3.账期 == 60)&(df3.是否到期 == 1)&(df3.历史逾期天数>0)]
    fn2 = df4.groupby('账单季度')[['未收金额2']].sum()
    fn2.columns = ['60天账期的入催金额']
    #90天账期的账单金额
    df4 = df3[(df3.账期 == 90)&(df3.是否到期 == 1)]
    fn3 = df4.groupby('账单季度')[['账单金额']].sum()
    fn3.columns = ['90天账期的账单金额']
    #90天账期的入催金额
    df4 = df3[(df3.账期 == 90)&(df3.是否到期 == 1)&(df3.历史逾期天数>0)]
    fn4 = df4.groupby('账单季度')[['未收金额2']].sum()
    fn4.columns = ['90天账期的入催金额']

    dfs = [fn1,fn2,fn3,fn4]
    final2 = pd.concat(dfs,axis=1)
    # final2 = fn1.merge(fn2,on='账单季度').merge(fn3,on='账单季度',how='left').merge(fn4,on='账单季度')
    final2['60天账期入催率'] = round(final2['60天账期的入催金额'] / final2['60天账期的账单金额'],3)
    final2['90天账期入催率'] = round(final2['90天账期的入催金额']/final2['90天账期的账单金额'],3)
    final2

    line = (
    Line()
    .add_xaxis(list(final1.index.values.astype(str)))
    .add_yaxis(
        "60天账期入催率",
        list(final2['60天账期入催率']),
        yaxis_index=0,
        color="#675bba",
        label_opts=opts.LabelOpts(is_show=False),
    )
    .set_global_opts(
    title_opts=opts.TitleOpts(title="不同账期入催率"),
    )
    .add_xaxis(list(final1.index.values.astype(str)))
    .add_yaxis(
        "90天账期入催率",
        list(final2['90天账期入催率']),
        yaxis_index=0,
        color="#d14a61",
        label_opts=opts.LabelOpts(is_show=False),
    )
    )
    line.render_notebook()

    df6 = df3[(df3.未收金额2 == 0)&(df3.是否到期 == 1)].copy()
    #使用cut，讲数据按照逾期天数分箱，然后添加分箱之后结果标签
    df6['历史逾期天数'] = pd.cut(df6['历史逾期天数'],bins=[-999,0,5,10,15,20,30,60,90,999],right=True,
                       labels=['0','1-5','6-10','11-15','16-20','21-30','31-60','61-90','91+'])
    final3 = df6.groupby('历史逾期天数')[['账期']].count()
    final3.columns = ['回收账单数']
    final3

    ydata = final3['回收账单数'].values.tolist()
    bar = (
    Bar()
    .add_xaxis(list(final3.index.values.tolist()))
    .add_yaxis("收回账单数",ydata,yaxis_index=0,color="#675bba")
    .set_global_opts(
    title_opts=opts.TitleOpts(title="不同逾期天数的已收回账单数"),
    )
    )
    bar.render_notebook()