import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import re
from scipy import stats

plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

df = pd.read_excel('./附件一.xlsx', '数据表')

#data preprocess
df['被保险人性别'].fillna('M', inplace=True, limit=3046)
df['被保险人性别'].fillna('F', inplace=True)
df.drop(columns=['风险类别（A最低，E最高）', '三者险保额', '已决赔款'], inplace = True)
df = df.dropna()
df['终止日期'] = pd.to_datetime(list(df['终止日期'])).normalize()
df['起保日期'] = pd.to_datetime(list(df['起保日期'])).normalize()
df = df.replace('是', 1)
df = df.replace('否', 0)
df = df.drop((df[df['保单性质'] == '转保'][df[df['保单性质'] == '转保']['是否续保'] == 1]['是否续保'] == 1).index)
df = df.drop((df[df['保单性质'] == '续保'][df[df['保单性质'] == '续保']['是否续保'] == 0]['是否续保'] == 0).index)

tmp = df['是否续保'].groupby(df['渠道']).count()
va_way_count = [tmp[['门店', '电网销']].sum(), tmp[['个人代理', '普通兼代(含银行代理)']].sum(), tmp[['交叉销售', '直拓']].sum(), tmp[['车商渠道', '专业中介']].sum()]
tmp = df['是否续保'].groupby(df['渠道']).sum()
va_way_sum = [tmp[['门店', '电网销']].sum(), tmp[['个人代理', '普通兼代(含银行代理)']].sum(), tmp[['交叉销售', '直拓']].sum(), tmp[['车商渠道', '专业中介']].sum()]
Si_way = [np.var([1]*va_way_sum[i] + [0]*(va_way_count[i]-va_way_sum[i])) for i in range(4)]
St_way = np.var(df['是否续保'])

#tmp = df['渠道'].value_counts().count()
alpha_way = (4/(4-1))*(1-(np.sum(Si_way))/St_way)

tmp = df['是否续保'].groupby(df['车龄']).count()
va_carage_count = [tmp[:2].sum(), tmp[2:5].sum(), tmp[5:7].sum(), tmp[7:].sum()]
tmp = df['是否续保'].groupby(df['车龄']).sum()
va_carage_sum = [tmp[:2].sum(), tmp[2:5].sum(), tmp[5:7].sum(), tmp[7:].sum()]
Si_carage = [np.var([1]*va_carage_sum[i] + [0]*(va_carage_count[i]-va_carage_sum[i])) for i in range(4)]
St_carage = np.var(df['是否续保'])

#tmp = len(df['车龄'].value_counts())
alpha_carage = (4/(4-1))*(1-(np.sum(Si_carage))/St_carage)

tmp = df['是否续保'].groupby(df['被保险人年龄']).count()
va_age_count = [tmp[:31].sum(), tmp[31:46].sum(), tmp[46:61].sum(), tmp[61:].sum()]
tmp = df['是否续保'].groupby(df['被保险人年龄']).sum()
va_age_sum = [tmp[:31].sum(), tmp[31:46].sum(), tmp[46:61].sum(), tmp[61:].sum()]
Si_age = [np.var([1]*va_age_sum[i] + [0]*(va_age_count[i]-va_age_sum[i])) for i in range(4)]
St_age = np.var(df['是否续保'])

#tmp = len(df['被保险人年龄'].value_counts())
alpha_age = (4/(4-1))*(1-(np.sum(Si_age))/St_age)

tmp = df['是否续保'].groupby(df['NCD']).count()
va_ncd_count = [tmp[[0, 1, 2, 3, 6, 7, 8, 10]].sum(), tmp[[4, 5, 11, 12]].sum(), tmp[[4, 5, 13, 14]].sum(), tmp[[9, -1]].sum()]
tmp = df['是否续保'].groupby(df['NCD']).sum()
va_ncd_sum = [tmp[[0, 1, 2, 3, 6, 7, 8, 10]].sum(), tmp[[4, 5, 11, 12]].sum(), tmp[[4, 5, 13, 14]].sum(), tmp[[9, -1]].sum()]
Si_ncd = [np.var([1]*va_ncd_sum[i] + [0]*(va_ncd_count[i]-va_ncd_sum[i])) for i in range(4)]
St_ncd = np.var(df['是否续保'])

#tmp = len(df['被保险人年龄'].value_counts())
alpha_ncd = (4/(4-1))*(1-(np.sum(Si_ncd))/St_ncd)

va_newcarprize_count = list(pd.cut(df['新车购置价'], [0, 50000, 100000, 300000, 500000, 1000000, 10000000]).value_counts(sort = False).values)
newcarprize_box = [0, 50000, 100000, 300000, 500000, 1000000, 10000000]
va_newcarprize_sum = [df[(df['新车购置价'] >= newcarprize_box[i]) & (df['新车购置价'] < newcarprize_box[i+1])]['是否续保'].sum() for i in range(len(newcarprize_box)-1)]
Si_newcarprize = [np.var([1]*va_newcarprize_sum[i] + [0]*(va_newcarprize_count[i]-va_newcarprize_sum[i])) for i in range(6)]
St_newcarprize = np.var(df['是否续保'])

#tmp = len(df['被保险人年龄'].value_counts())
alpha_newcarprize = (6/(6-1))*(1-(np.sum(Si_newcarprize))/St_newcarprize)

tmp = df['是否续保'].groupby(df['使用性质']).count()
va_use_count = [tmp['党政机关、事业团体用车'].sum(), tmp[['非营业货车']].sum(), tmp['家庭自用车'].sum(), tmp['企业非营业用车'].sum(), tmp['特种车'].sum(), tmp['营业货车'].sum()]
tmp = df['是否续保'].groupby(df['使用性质']).sum()
va_use_sum = [tmp['党政机关、事业团体用车'].sum(), tmp[['非营业货车']].sum(), tmp['家庭自用车'].sum(), tmp['企业非营业用车'].sum(), tmp['特种车'].sum(), tmp['营业货车'].sum()]
Si_use = [np.var([1]*va_use_sum[i] + [0]*(va_use_count[i]-va_use_sum[i])) for i in range(6)]
St_use = np.var(df['是否续保'])

#tmp = len(df['被保险人年龄'].value_counts())
alpha_use = (6/(6-1))*(1-(np.sum(Si_use))/St_use)

#time_indexes = pd.to_datetime(list(df['起保日期'])).normalize()
#df_date = pd.DataFrame(list(df['是否续保']), index=time_indexes)

#df_date = df[['起保日期', '是否续保']].copy()
#df_date['起保日期'] = pd.to_datetime(list(df_date['起保日期'])).normalize()

#df_date.sort_values('起保日期', inplace=True)
#df_date = df_date.reset_index()

#seq_quar = pd.date_range('2016-01-01', '2019-01-01', freq='QS-JAN')

#result = []
#for i in range(len(seq_quar)-1):
#    a = float((df_date[(df_date['起保日期'] >= seq_quar[i]) & (df_date['起保日期'] < seq_quar[i+1])][['是否续保']] == '是').sum())
#    b = float((df_date[df_date['起保日期'] < seq_quar[i+1]][['是否续保']] == '是').sum())
#    result.append(a/b)

way_index = ['门店、电网销', '个人代理、普通兼代', '交叉销售、直拓', '车商渠道、专业中介']
carage_index = ['0-1', '2-4', '5-7', '>7']
age_index = ['18-30', '31-45', '46-60', '61-75']
ncd_index = ['0-1次', '2-3次', '3次以上', '新过户与无记录']
newcarprize_index = ['0-5', '5-10', '10-30', '30-50', '50-100', '>100']

fig = plt.figure()
#fig, axes = plt.subplots(6, 1, sharex=True, sharey=True, figsize=(10,15))
fig, axes = plt.subplots(5, 1, sharey=True, figsize=(15,20))

[axes[i].set_ylabel('续保概率') for i in range(len(axes))]
axes[0].set_xlabel('渠道')
axes[1].set_xlabel('车龄（年）')
axes[2].set_xlabel('投保人年龄')
axes[3].set_xlabel('NCD系数')
axes[4].set_xlabel('新车购置价')

#axes[0].bar(way_index, np.divide([float((df[df['渠道'] == way_index[i]][['是否续保']] == 1).sum()) for i in range(len(way_index)-2)], [float(i) for i in df['渠道'].value_counts()][:-2]))
#axes[1].bar(carage_index, np.divide([(df[(df['车龄'] >= carage_box[i]) & (df['车龄'] < carage_box[i+1])]['是否续保'] == 1).sum() for i in range(len(carage_box)-1)], [df[(df['车龄'] >= carage_box[i]) & (df['车龄'] < carage_box[i+1])]['车龄'].count() for i in range(len(carage_box)-1)]))
#axes[2].bar(age_index, np.divide([(df[df['客户类别'] == '个人']['是否续保'][((df[df['客户类别'] == '个人']['被保险人年龄'] >= age_box[i]) & (df[df['客户类别'] == '个人']['被保险人年龄'] < age_box[i+1]))] == 1).sum() for i in range(len(age_box)-1)], [((df[df['客户类别'] == '个人']['被保险人年龄'] >= age_box[i]) & (df[df['客户类别'] == '个人']['被保险人年龄'] < age_box[i+1])).sum() for i in range(len(age_box)-1)]))
axes[0].plot(way_index, np.divide(va_way_sum, va_way_count), 'bo--')
axes[1].plot(carage_index, np.divide(va_carage_sum, va_carage_count), 'bo--')
axes[2].plot(age_index, np.divide(va_age_sum, va_age_count), 'bo--')
axes[3].plot(ncd_index, np.divide(va_ncd_sum, va_ncd_count), 'bo--')
axes[4].plot(newcarprize_index, np.divide(va_newcarprize_sum, va_newcarprize_count), 'bo--')

plt.subplots_adjust(wspace=None, hspace=None)
plt.savefig('./all.png', dpi=400, bbox_inches='tight')

<Figure size 432x288 with 0 Axes>

fig = plt.figure()
#fig, axes = plt.subplots(6, 1, sharex=True, sharey=True, figsize=(10,15))
fig, axes = plt.subplots(1, 1, sharey=True, figsize=(15,10))

axes.bar(['车龄', '年龄', '出险次数', '新车购置价', '使用性质'], [(1/abs(i))*3 for i in [alpha_carage, alpha_age, alpha_ncd, alpha_newcarprize, alpha_use]])
axes.title.set_text('五个因素的α信度值')
axes.set_ylabel('α信度')

plt.subplots_adjust(wspace=None, hspace=None)
plt.savefig('./view.png', dpi=400, bbox_inches='tight')

<Figure size 432x288 with 0 Axes>

fig = plt.figure()
#fig, axes = plt.subplots(6, 1, sharex=True, sharey=True, figsize=(10,15))
fig, axes = plt.subplots(1, 1, sharey=True, figsize=(15,8))

plt.plot(way_index, np.divide(va_way_sum, va_way_count), 'bo--')

plt.subplots_adjust(wspace=None, hspace=None)
plt.savefig('./1.png', dpi=400, bbox_inches='tight')

<Figure size 432x288 with 0 Axes>

fig = plt.figure()
#fig, axes = plt.subplots(6, 1, sharex=True, sharey=True, figsize=(10,15))
fig, axes = plt.subplots(1, 1, sharey=True, figsize=(15,8))

plt.plot(carage_index, np.divide(va_carage_sum, va_carage_count), 'bo--')

plt.subplots_adjust(wspace=None, hspace=None)
plt.savefig('./2.png', dpi=400, bbox_inches='tight')

<Figure size 432x288 with 0 Axes>

fig = plt.figure()
#fig, axes = plt.subplots(6, 1, sharex=True, sharey=True, figsize=(10,15))
fig, axes = plt.subplots(1, 1, sharey=True, figsize=(15,8))

plt.plot(age_index, np.divide(va_age_sum, va_age_count), 'bo--')

plt.subplots_adjust(wspace=None, hspace=None)
plt.savefig('./3.png', dpi=400, bbox_inches='tight')

<Figure size 432x288 with 0 Axes>

fig = plt.figure()
#fig, axes = plt.subplots(6, 1, sharex=True, sharey=True, figsize=(10,15))
fig, axes = plt.subplots(1, 1, sharey=True, figsize=(15,8))

plt.plot(ncd_index, np.divide(va_ncd_sum, va_ncd_count), 'bo--')

plt.subplots_adjust(wspace=None, hspace=None)
plt.savefig('./4.png', dpi=400, bbox_inches='tight')

<Figure size 432x288 with 0 Axes>

fig = plt.figure()
#fig, axes = plt.subplots(6, 1, sharex=True, sharey=True, figsize=(10,15))
fig, axes = plt.subplots(1, 1, sharey=True, figsize=(15,8))

plt.plot(newcarprize_index, np.divide(va_newcarprize_sum, va_newcarprize_count), 'bo--')

plt.subplots_adjust(wspace=None, hspace=None)
plt.savefig('./5.png', dpi=400, bbox_inches='tight')

<Figure size 432x288 with 0 Axes>

fig = plt.figure()
#fig, axes = plt.subplots(6, 1, sharex=True, sharey=True, figsize=(10,15))
fig, axes = plt.subplots(1, 1, sharey=True, figsize=(15,8))

use_index = ['党政机关、事业团体用车', '非营业货车', '家庭自用车', '企业非营业用车', '特种车', '营业货车']
plt.plot(use_index, np.divide(va_use_sum, va_use_count), 'bo--')

plt.subplots_adjust(wspace=None, hspace=None)
plt.savefig('./6.png', dpi=400, bbox_inches='tight')

<Figure size 432x288 with 0 Axes>

np.sum([(1/abs(i))*3*0.2 for i in [alpha_carage, alpha_age, alpha_ncd, alpha_newcarprize, alpha_use]])

0.7060621419595827

pd.Series([(1/abs(i))*3 for i in [alpha_carage, alpha_age, alpha_ncd, alpha_newcarprize, alpha_use]], index = ['车龄', '年龄', '出险次数', '新车购置价', '使用性质'])

车龄       0.852617
年龄       0.718229
出险次数     0.766123
新车购置价    0.453740
使用性质     0.739602
dtype: float64

time_data = df[(df['终止日期'] - df['起保日期'] != '365 days') & (df['终止日期'] - df['起保日期'] != '364 days')]

a = pd.date_range('1/1/2000', periods=100)

pd.to_datetime(list(time_data['起保日期'])).normalize()

DatetimeIndex(['2017-05-08', '2016-03-31', '2018-02-27', '2018-07-04',
               '2017-05-21', '2018-01-11', '2016-02-05', '2018-03-14',
               '2018-04-09', '2018-08-07',
               ...
               '2016-08-20', '2016-08-06', '2016-01-25', '2016-05-06',
               '2017-06-29', '2017-09-02', '2017-09-08', '2017-05-01',
               '2018-01-31', '2017-07-26'],
              dtype='datetime64[ns]', length=386, freq=None)

b = df['终止日期'] - df['起保日期']
b = b.dt.days
#a = pd.Series(['364']*65272)
#a = a.reindex_like(b)

li = []
li.append(((b > 0) & (b <= 350)).sum())
li.append(((b > 350) & (b <= 365)).sum())
li.append(((b > 30) & (b <= 380)).sum())
li.append((b > 380).sum())

li

[360, 60881, 61183, 0]

re.findall(r'\d+', 'a012a2')

['012', '2']

b.count()

61266

df[b != 364].columns

Index(['保单号', '起保日期', '终止日期', '渠道', '品牌', '车系', '保单性质', '续保年', '投保类别',
       '是否本省车牌', '使用性质', '车辆种类', '车辆用途', '新车购置价', '车龄', '险种', 'NCD', '客户类别',
       '被保险人性别', '被保险人年龄', '是否投保车损', '是否投保盗抢', '是否投保车上人员', '签单保费', '立案件数',
       '是否续保'],
      dtype='object')

class_ncd = ['1.连续3+无事故', '2.连续2年无事故', '3.上年未发生有责事故', '4.上年发生一次有责不涉及死亡的事故', '5.上年发生2+有责事故', '6.上年发生有责死亡事故', 'a.过去三年未出险', 'b.过去两年未出险', 'c.过去一年未出险', 'e.新过户', 'f.上年出险一次', 'g.上年出险二次', 'h.上年出险三次', 'i.上年出险四次', 'j.上年出险五次及以上', '新保无记录', '']
data = df[['NCD', '新车购置价', '投保类别', '险种', '签单保费']]
list_car_prize = [0, 50000, 100000, 150000, 200000, 300000, 500000, 1000000, 6000000]
first_result = []
tmp_ncd = []
for i in range(len(list_car_prize) - 1):
    for j in range(4):
        if j == 0:
            tmp_ncd.append(data[(data['新车购置价'] > list_car_prize[i]) & (data['新车购置价'] <= list_car_prize[i+1]) & (data['投保类别'] == '交商全保') & (data['险种'] == '商业险')][['签单保费']].mean())
        elif j == 1:
            tmp_ncd.append(data[(data['新车购置价'] > list_car_prize[i]) & (data['新车购置价'] <= list_car_prize[i+1]) & (data['投保类别'] == '交商全保') & (data['险种'] == '交强险')][['签单保费']].mean())
        elif j == 2:
            tmp_ncd.append(data[(data['新车购置价'] > list_car_prize[i]) & (data['新车购置价'] <= list_car_prize[i+1]) & (data['投保类别'] == '单商业')][['签单保费']].mean())
        elif j == 3:
            tmp_ncd.append(data[(data['新车购置价'] > list_car_prize[i]) & (data['新车购置价'] <= list_car_prize[i+1]) & (data['投保类别'] == '单交强')][['签单保费']].mean())
        else:
            break
    first_result.append([round(float(f),2) for f in tmp_ncd])
    tmp_ncd = []
    first_revision = np.transpose(np.array(first_result))

fig = plt.figure()
fig, axes = plt.subplots(4, 1, sharex=True, sharey=True, figsize=(10,15))

for i in range(4):
    axes[i].plot(first_revision[i], 'o')
    axes[i].set_yticks([1, 2000, 4000, 6000, 8000, 10000])
    
axes[0].set_ylabel('签单保费')
axes[0].title.set_text('"投保类型": 交商全保 & "险种": 商业险')
axes[1].set_ylabel('签单保费')
axes[1].title.set_text('"投保类型": 交商全保 & "险种": 交强险')
axes[2].set_ylabel('签单保费')
axes[2].title.set_text('"投保类别": 单商业')
axes[3].set_ylabel('签单保费')
axes[3].title.set_text('"投保类别": 单交强')
axes[3].set_xlabel('新车购置价')

for i in range(4):
    slope, intercept, r_value, p_value, std_err = stats.linregress(np.arange(8), first_revision[i])
    line = slope*np.arange(8) + intercept
    axes[i].plot(np.arange(8), line, 'r', label='y={:.2f}x+{:.2f}'.format(slope,intercept))

plt.subplots_adjust(wspace=None, hspace=None)
plt.savefig('./other.png', dpi=400, bbox_inches='tight')

<Figure size 432x288 with 0 Axes>

a = pd.DataFrame(np.random.randn(50).reshape((10,5)))
#create NaN values
a[0][1,3,4,6,9] = np.nan
a.loc[a[0].isnull(), 0] = 0
a[0][a[0].isnull()].fillna(value = '0', inplace = True)