!/usr/bin/env python
coding: utf-8
In[1]:
from IPython.display import display, HTML
display(HTML(""))
# 整体运营情况
In[2]:
import numpy as np
import pandas as pd
import pymysql
conn=pymysql.connect(host="114.55.91.201",user="read",passwd="readrisk123456",db='likeshop')
sql="""SELECT o.user_id,
o.create_time,o.enjoy_time,o.pay_time,
order_review,is_examine,order_status
FROM ls_order as o """
order=pd.read_sql_query(sql,conn)
import datetime
def dates(x):
if pd.isnull(x):
return x
elif x==0:
return np.nan
else:
datetime_obj = datetime.datetime.fromtimestamp(x)
return datetime_obj.date()
#datetime_obj
#atetime_obj.time()
order["create_time"]=order["create_time"].apply(dates)
order["enjoy_time"]=order["enjoy_time"].apply(dates)
order["pay_time"]=order["pay_time"].apply(dates)
order['create_time']=order['create_time'].astype(str)
order=order[order['create_time']>="2024-05-02"]
import pandas as pd
import numpy as np
import pycard as pc
import warnings
warnings.filterwarnings('ignore')
warnings.simplefilter('ignore')
import numpy as np
import pandas as pd
import pymysql
conn=pymysql.connect(host="114.55.91.201",user="read",passwd="readrisk123456",db='likeshop')
sql="""select uid as user_id,name,id_num as idcard from ls_user_id_card where step =4 """
idcard=pd.read_sql_query(sql,conn)
order=order.merge(idcard,on="user_id",how="inner")
order['type']="牛牛商城"
ZT.drop_duplicates(subset="idcard",inplace=True)
order=order[order['idcard'].notna()]
order
In[3]:
order['月份']=order['create_time'].apply(lambda x:x[:7])
order
import datetime as dt
def get_monday_to_sunday(today):
today = dt.datetime.strptime(str(today), "%Y-%m-%d")
monday = dt.datetime.strftime(today - dt.timedelta(today.weekday()), "%Y-%m-%d")
monday_ = dt.datetime.strptime(monday, "%Y-%m-%d")
sunday = dt.datetime.strftime(monday_ + dt.timedelta(monday_.weekday() + 6), "%Y-%m-%d")
monday1=str(monday)
sunday1=str(sunday)
return monday1+"~"+sunday1
order['周']=order['create_time'].apply(get_monday_to_sunday)
order
In[4]:
order['create_time'].value_counts()
In[5]:
order1=order.copy()
order.drop_col(['idcard','name'],inplace=True)
order
In[6]:
import pymysql
from sqlalchemy import create_engine
conn=pymysql.connect(host="112.124.40.41",user="score",passwd="jxy7RwPaLBryzFF8",db='score',port=9806)
conn = create_engine('mysql+pymysql://score:jxy7RwPaLBryzFF8@112.124.40.41:9806/score')
order.to_sql("zttgl",conn,if_exists='replace',index=False)
# 评分分布逾期率
In[ ]:
import pandas as pd
import pymysql
from sqlalchemy import create_engine
conn=pymysql.connect(host="112.124.40.41",user="score",passwd="jxy7RwPaLBryzFF8",db='score',port=9806)
sql="""SELECT idcard,近12个月贷款金额在1w以上的笔数,消金贷款类机构最大授信额度,近1个月履约贷款总金额,申请准入分,近3个月履约贷款总金额,近1个月履约贷款次数,
近3个月履约贷款次数,履约笔数,最大逾期金额,
tw_v100_bin from score2 """
score=pd.read_sql_query(sql,conn)
score
In[ ]:
score['tw_v100_bin'].value_counts(dropna=False)
In[ ]:
order1=order1[order1['is_examine']==1]
order1
In[ ]:
order1=order1.merge(score,on="idcard",how="left")
order1
In[ ]:
order1[order1['tw_v100_bin'].isnull()]
In[ ]:
order2=order1[(order1['order_review']1)&(order1['is_examine']1)&(order1['order_status']!=4)&(order1['order_status']!=5)]
order2
In[ ]:
order[order1['tw_v100_bin'].isnull()]
In[ ]:
import datetime as dt
a=dt.datetime.now().date()
b=str(a)
order2['watchday']=b
for i in ['enjoy_time','pay_time','watchday']:
order2[i]=pd.to_datetime(arg=order2[i])
order2['datediff']=order2.apply(lambda x:(x.watchday-x.enjoy_time).days,axis=1)
order2
In[ ]:
def yq(x):
if pd.isnull(x.pay_time)&(x.watchday >x.enjoy_time):
return (x.watchday-x.enjoy_time).days
elif pd.isnull(x.pay_time)&(x.watchday <=x.enjoy_time):
return -999
elif x.pay_time > x.enjoy_time:
return (x.pay_time-x.enjoy_time).days
else: return 0
order2['overday']=order2.apply(yq,axis=1)
order2
In[ ]:
import pymysql
from sqlalchemy import create_engine
conn=pymysql.connect(host="localhost",user="root",passwd="select@20",db='score')
conn = create_engine('mysql+pymysql://root:select%4020@localhost:3306/score')
order2.to_sql("ztpf",conn,if_exists='replace',index=False)
order1.to_sql("qbpf",conn,if_exists='replace',index=False)
# 逾期率
In[ ]:
order2['max_overday']=order2.groupby("idcard")["overday"].transform("max")
order2['max_datediff']=order2.groupby("idcard")["datediff"].transform("max")
order2
In[ ]:
order2.drop_duplicates(subset="idcard",inplace=True)
order2
In[ ]:
order2[order2['tw_v100_bin'].isnull()]
In[ ]:
order2[order2['idcard']=="511525199805311938"]
In[ ]:
order2['tw_v100_bin'].value_counts(dropna=False)
In[ ]:
order2['dpd1+']=order2.apply(lambda x:'还没到期_1+'if x.max_datediff<1 else '1+' if
x.max_overday>=1 else '0',axis=1)
order2['dpd3+'] =order2.apply(lambda x:'还没到期_3+'if x.max_datediff<3 else '3+' if
x.max_overday>=3 else '0-2',axis=1)
order2['dpd7+'] =order2.apply(lambda x:'还没到期_7+'if x.max_datediff<7 else '7+' if
x.max_overday>=7 else '0-6',axis=1)
order2['dpd15+'] =order2.apply(lambda x:'还没到期_15+'if x.max_datediff<15 else '15+' if
x.max_overday>=15 else '0-14',axis=1)
In[ ]:
order2
In[ ]:
order2['tw_v100_bin'].value_counts(dropna=False)
In[ ]:
dpd1 = pd.DataFrame()
dpd3 = pd.DataFrame()
dpd7 = pd.DataFrame()
dpd15 = pd.DataFrame()
def f(x):
return x.nunique()
for i in ['tw_v100_bin']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd1+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd1+_rate'] = t1['1+']/(t1['1+']+t1['0'])
dpd1 = dpd1.append(t1)
In[ ]:
for i in ['tw_v100_bin']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd3+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd3+_rate'] = t1['3+']/(t1['3+']+t1['0-2'])
dpd3 = dpd3.append(t1)
In[ ]:
for i in ['tw_v100_bin']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd7+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd7+_rate'] = t1['7+']/(t1['7+']+t1['0-6'])
dpd7 = dpd7.append(t1)
In[ ]:
for i in ['tw_v100_bin']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd15+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd15+_rate'] = t1['15+']/(t1['15+']+t1['0-14'])
dpd15 = dpd15.append(t1)
In[ ]:
yq=dpd1.merge(dpd3,on=["区间",'变量'],how="left")
yq=yq.merge(dpd7,on=["区间",'变量'],how="left")
yq=yq.merge(dpd15,on=["区间",'变量'],how="left")
yq
In[ ]:
import pymysql
from sqlalchemy import create_engine
conn=pymysql.connect(host="112.124.40.41",user="score",passwd="jxy7RwPaLBryzFF8",db='score',port=9806)
conn = create_engine('mysql+pymysql://score:jxy7RwPaLBryzFF8@112.124.40.41:9806/score')
yq.to_sql("ztyq",conn,if_exists='replace',index=False)
# 分状态
In[ ]:
zd={"A_[0,262)":"1",
"B_[262,316)":"1",
"C_[316,352)":"1",
"D_[352,374)":"2",
"E_[374,390)":"2",
"F_[390,405)":"2",
"G_[405,416)":"2",
"H_[416,428)":"2",
"I_[428,445)":"3",
"J_[445,inf)":"3"}
order2['status']=order2['tw_v100_bin'].map(zd)
In[ ]:
order2['status'].value_counts(dropna=False)
In[ ]:
order2['status'].value_counts()
In[ ]:
order2['status']
In[ ]:
order2['status']=order2['status'].astype('str')
order2['status']=order2['status'].apply(lambda x:"建议拒绝" if x =="1" else "建议人审" if x =="2" else "建议通过")
order2
In[ ]:
dpd1 = pd.DataFrame()
dpd3 = pd.DataFrame()
dpd7 = pd.DataFrame()
dpd15 = pd.DataFrame()
def f(x):
return x.nunique()
for i in ['status']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd1+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd1+_rate'] = t1['1+']/(t1['1+']+t1['0'])
dpd1 = dpd1.append(t1)
In[ ]:
for i in ['status']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd3+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd3+_rate'] = t1['3+']/(t1['3+']+t1['0-2'])
dpd3 = dpd3.append(t1)
In[ ]:
for i in ['status']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd7+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd7+_rate'] = t1['7+']/(t1['7+']+t1['0-6'])
dpd7 = dpd7.append(t1)
In[ ]:
for i in ['status']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd15+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd15+_rate'] = t1['15+']/(t1['15+']+t1['0-14'])
dpd15 = dpd15.append(t1)
In[ ]:
yq=dpd1.merge(dpd3,on=["区间",'变量'],how="left")
yq=yq.merge(dpd7,on=["区间",'变量'],how="left")
yq=yq.merge(dpd15,on=["区间",'变量'],how="left")
yq
In[ ]:
import pymysql
from sqlalchemy import create_engine
conn=pymysql.connect(host="112.124.40.41",user="score",passwd="jxy7RwPaLBryzFF8",db='score',port=9806)
conn = create_engine('mysql+pymysql://score:jxy7RwPaLBryzFF8@112.124.40.41:9806/score')
yq.to_sql("ztyqstatus",conn,if_exists='replace',index=False)
In[ ]:
order2
In[ ]:
order3=order2[['user_id','create_time','月份', '周','近12个月贷款金额在1w以上的笔数', '消金贷款类机构最大授信额度', '近1个月履约贷款总金额', '申请准入分',
'近3个月履约贷款总金额', '近1个月履约贷款次数', '近3个月履约贷款次数', '履约笔数', '最大逾期金额']]
In[ ]:
import pymysql
from sqlalchemy import create_engine
conn=pymysql.connect(host="112.124.40.41",user="score",passwd="jxy7RwPaLBryzFF8",db='score',port=9806)
conn = create_engine('mysql+pymysql://score:jxy7RwPaLBryzFF8@112.124.40.41:9806/score')
order3.to_sql("blfb",conn,if_exists='replace',index=False)
In[ ]:
dpd1 = pd.DataFrame()
dpd3 = pd.DataFrame()
dpd7 = pd.DataFrame()
dpd15 = pd.DataFrame()
def f(x):
return x.nunique()
for i in ['近12个月贷款金额在1w以上的笔数', '消金贷款类机构最大授信额度', '近1个月履约贷款总金额', '申请准入分',
'近3个月履约贷款总金额', '近1个月履约贷款次数', '近3个月履约贷款次数', '履约笔数', '最大逾期金额']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd1+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd1+_rate'] = t1['1+']/(t1['1+']+t1['0'])
dpd1 = dpd1.append(t1)
In[ ]:
for i in ['近12个月贷款金额在1w以上的笔数', '消金贷款类机构最大授信额度', '近1个月履约贷款总金额', '申请准入分',
'近3个月履约贷款总金额', '近1个月履约贷款次数', '近3个月履约贷款次数', '履约笔数', '最大逾期金额']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd3+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd3+_rate'] = t1['3+']/(t1['3+']+t1['0-2'])
dpd3 = dpd3.append(t1)
In[ ]:
for i in ['近12个月贷款金额在1w以上的笔数', '消金贷款类机构最大授信额度', '近1个月履约贷款总金额', '申请准入分',
'近3个月履约贷款总金额', '近1个月履约贷款次数', '近3个月履约贷款次数', '履约笔数', '最大逾期金额']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd7+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd7+_rate'] = t1['7+']/(t1['7+']+t1['0-6'])
dpd7 = dpd7.append(t1)
In[ ]:
for i in ['近12个月贷款金额在1w以上的笔数', '消金贷款类机构最大授信额度', '近1个月履约贷款总金额', '申请准入分',
'近3个月履约贷款总金额', '近1个月履约贷款次数', '近3个月履约贷款次数', '履约笔数', '最大逾期金额']:
t1 = pd.pivot_table(data=order2,index=[i],columns='dpd15+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['dpd15+_rate'] = t1['15+']/(t1['15+']+t1['0-14'])
dpd15 = dpd15.append(t1)
In[ ]:
yq=dpd1.merge(dpd3,on=["区间",'变量'],how="left")
yq=yq.merge(dpd7,on=["区间",'变量'],how="left")
yq=yq.merge(dpd15,on=["区间",'变量'],how="left")
yq
In[ ]:
import pymysql
from sqlalchemy import create_engine
conn=pymysql.connect(host="112.124.40.41",user="score",passwd="jxy7RwPaLBryzFF8",db='score',port=9806)
conn = create_engine('mysql+pymysql://score:jxy7RwPaLBryzFF8@112.124.40.41:9806/score')
yq.to_sql("blyq",conn,if_exists='replace',index=False)
浙公网安备 33010602011771号