三方数据调用

import numpy as np
import pandas as pd
import sqlalchemy
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,
JSON_UNQUOTE(JSON_EXTRACT( o.address, '$.mobile' )) AS phone
FROM ls_order as o where is_examine=1 """
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-01"]
import pandas as pd
import numpy as np
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)
ZT=order.merge(idcard,on="user_id",how="inner")
ZT['type']="牛牛商城"
ZT.drop_duplicates(subset="idcard",inplace=True)
ZT=ZT[ZT['idcard'].notna()]
ZT

# 看看数据库有啥

In[207]:

import numpy as np
import pymysql
import pandas as pd
conn=pymysql.connect(host="112.124.40.41",user="score",passwd="jxy7RwPaLBryzFF8",db='score',port=9806)
sql="""SELECT idcard from order """
order=pd.read_sql_query(sql,conn)
order

In[208]:

zt1=ZT.merge(order,on="idcard",how="inner")
ztid=zt1['idcard'].to_list()
ZT1=ZT[~ZT['idcard'].isin(ztid)]

In[209]:

ZT1=ZT1.head(20)

# 判断有无新增数据,有新数据导入数据库，没有直接结束。

In[204]:

if len(ZT1)>0:
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')
ZT1.to_sql("order", conn, if_exists='append',index=False)
ZT1=ZT1[['user_id','name','idcard','phone']]
import hashlib
def md5_jiami(x):
Jpwd = hashlib.md5()
Jpwd.update(x.encode('utf-8'))
return Jpwd.hexdigest()
ZT1['idcard_md5'] = ZT1['idcard'].apply(md5_jiami)
ZT1.reset_index(inplace=True)
import requests
response = requests.get('https://api.openapi.baiyizu.cn/api/open/access_token?account=baiyizu&signature=c3f0c807ac3240dabe4bc9d05506dd06')
import pandas as pd
token=pd.DataFrame(response.json())
token=token.at['access_token','data']
ZT2=ZT1.copy()
ZT2['data']=1
import requests
import json
headers = {"Content-Type": "application/json; charset=UTF-8"}
url = "https://api.openapi.baiyizu.cn/api/openapi/v1/gateway"
for i in range(len(ZT2)):
a=ZT2.at[i,'idcard']
b=ZT2.at[i,'name']
c=ZT2.at[i,'phone']
pyload ={"account":"baiyizu","access_token":token,"product_code":"11002",
"params":{"name":b,"id_card":a,"phone":c}}
response = requests.post(url, data=json.dumps(pyload), headers=headers).text
ZT2.iloc[i,6] = response
ZT2['data']=ZT2['data'].apply(lambda x:json.loads(x)['data']['data']['data']['result_detail'])
ZT2['channel']="xinyan"
ZT3=ZT1.copy()
ZT3['data']=1
import oss2
for i in range(len(ZT3)):
a=ZT3.at[i,'idcard']
b=ZT3.at[i,'name']
c=ZT3.at[i,'phone']
import requests
import json
headers = {"Content-Type": "application/json; charset=UTF-8"}
url = "https://api.openapi.baiyizu.cn/api/openapi/v1/gateway"
pyload ={"account":"baiyizu","access_token":token,"product_code":"12008",
"params":{"name":b,"id_card":a,"phone":c,"auth_file_url": "https://byzpro.oss-cn-chengdu.aliyuncs.com/contract/f7b19c34cd6835276de42583711c18b4.pdf"}}
response = requests.post(url, data=json.dumps(pyload), headers=headers).text
ZT3.iloc[i,6] = response
ZT3['data']=ZT3['data'].apply(lambda x:json.loads(x)['data'])
ZT3['channel']="yqtz"
data=pd.concat([ZT2,ZT3],axis=0)
data.drop(['index','idcard_md5'],axis=1,inplace=True)
data['data']=data['data'].apply(lambda x:json.dumps(x))
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')
data.to_sql("data", conn, if_exists='append',index=False)
data.to_sql("ceshi1", conn, if_exists='append',index=False)
def get_data_from_json(json_obj, key='max_performance_amt'):
if isinstance(json_obj, dict):
for k, v in json_obj.items():
if k == key:
return v
elif isinstance(v, (dict, list)):
result = get_data_from_json(v, key)
if result is not None:
return result
elif isinstance(json_obj, list):
for item in json_obj:
result = get_data_from_json(item, key)
if result is not None:
return result
return None
ZT2['data']=ZT2['data'].apply(lambda x:json.dumps(x))
ZT3['data']=ZT3['data'].apply(lambda x:json.dumps(x))
ZT2['近12个月贷款金额在1w以上的笔数'] = ZT2['data'].apply(lambda x: get_data_from_json(json.loads(x),key="B22170015") if isinstance(x, str) and x else None)
ZT2['消金贷款类机构最大授信额度'] = ZT2['data'].apply(lambda x: get_data_from_json(json.loads(x),key="C22180009") if isinstance(x, str) and x else None)
ZT2['近1个月履约贷款总金额'] = ZT2['data'].apply(lambda x: get_data_from_json(json.loads(x),key="B22170040") if isinstance(x, str) and x else None)
ZT2['申请准入分'] = ZT2['data'].apply(lambda x: get_data_from_json(json.loads(x),key="A22160001") if isinstance(x, str) and x else None)
ZT2['近3个月履约贷款总金额'] = ZT2['data'].apply(lambda x: get_data_from_json(json.loads(x),key="B22170041") if isinstance(x, str) and x else None)
ZT2['近1个月履约贷款次数'] = ZT2['data'].apply(lambda x: get_data_from_json(json.loads(x),key="B22170045") if isinstance(x, str) and x else None)
ZT2['近3个月履约贷款次数'] = ZT2['data'].apply(lambda x: get_data_from_json(json.loads(x),key="B22170046") if isinstance(x, str) and x else None)
ZT3['履约笔数'] = ZT3['data'].apply(lambda x: get_data_from_json(json.loads(x),key="count_performance") if isinstance(x, str) and x else None)
ZT3['最大逾期金额'] = ZT3['data'].apply(lambda x: get_data_from_json(json.loads(x),key="max_overdue_amt") if isinstance(x, str) and x else None)
PF=ZT2.merge(ZT3,on='idcard',how="inner")
PF=PF[['idcard','履约笔数',
'最大逾期金额',
'近12个月贷款金额在1w以上的笔数',
'消金贷款类机构最大授信额度',
'近1个月履约贷款总金额',
'申请准入分',
'近3个月履约贷款总金额',
'近1个月履约贷款次数',
'近3个月履约贷款次数']]
PF['类型']="牛牛商城"
nn=PF.copy()
for i in [ '履约笔数',
'近12个月贷款金额在1w以上的笔数', '消金贷款类机构最大授信额度', '申请准入分',
'近1个月履约贷款次数', '近3个月履约贷款次数']:
nn[i]=nn[i].apply(lambda x:np.nan if x"" else np.nan if x"其他" else np.nan if x=="np.nan" else x)
nn[i]=nn[i].astype('float')
def qj(x):
values=[0,10,50,np.inf]
index=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R']
for i in range(len(values)-1):
if values[i]<=x<values[i+1]:
return '{0}[{1},{2})'.format(index[i],values[i],values[i+1])
return '空值'
nn['近3个月履约贷款次数']=nn['近3个月履约贷款次数'].astype('float')
nn['近3个月履约贷款次数']=nn['近3个月履约贷款次数'].apply(qj)
def qj(x):
values=[0,1,5,17,np.inf]
index=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T']
for i in range(len(values)-1):
if values[i]<=x<values[i+1]:
return '{0}[{1},{2})'.format(index[i],values[i],values[i+1])
return '空值'
nn['近1个月履约贷款次数']=nn['近1个月履约贷款次数'].astype('float')
nn['近1个月履约贷款次数']=nn['近1个月履约贷款次数'].apply(qj)

def qj(x):
    values=[0,505,525,545,640,np.inf]
    index=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T']
    for i in range(len(values)-1):
        if values[i]<=x<values[i+1]:
            return '{0}_[{1},{2})'.format(index[i],values[i],values[i+1])
    return '空值'
nn['申请准入分']=nn['申请准入分'].astype('float')
nn['申请准入分']=nn['申请准入分'].apply(qj)

def qj(x):
    values=[0,10000,20000,50000,np.inf]
    index=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T']
    for i in range(len(values)-1):
        if values[i]<=x<values[i+1]:
            return '{0}_[{1},{2})'.format(index[i],values[i],values[i+1])
    return '空值'
nn['消金贷款类机构最大授信额度']=nn['消金贷款类机构最大授信额度'].astype('float')
nn['消金贷款类机构最大授信额度']=nn['消金贷款类机构最大授信额度'].apply(qj)

def qj(x):
    values=[0,1,2,8,np.inf]
    index=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T']
    for i in range(len(values)-1):
        if values[i]<=x<values[i+1]:
            return '{0}_[{1},{2})'.format(index[i],values[i],values[i+1])
    return '空值'
nn['近12个月贷款金额在1w以上的笔数']=nn['近12个月贷款金额在1w以上的笔数'].astype('float')
nn['近12个月贷款金额在1w以上的笔数']=nn['近12个月贷款金额在1w以上的笔数'].apply(qj)
def qj(x):
    values=[0,5,15,30,140,np.inf]
    index=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T']
    for i in range(len(values)-1):
        if values[i]<=x<values[i+1]:
            return '{0}_[{1},{2})'.format(index[i],values[i],values[i+1])
    return '空值'
nn['履约笔数']=nn['履约笔数'].astype('float')
nn['履约笔数']=nn['履约笔数'].apply(qj)

def qj4(x):
    if x=='0':
        return 'A_0'    
    if x=='1-1000':
        return 'B_1-1000'
    elif x=='1000-2000':
        return 'C_1000-2000'
    elif x=='[1000,2000)':
        return 'D_[1000,2000)'  
    elif x=='2000-3000':
        return 'E_2000-3000'
    elif x=='3000-4000':
        return 'F_3000-4000'
    elif x=='4000-6000':
        return 'G_4000-6000'
    elif x=='6000-8000':
        return 'H_6000-8000'
    elif x=='8000-10000':
        return 'I_8000-10000'
    elif x=='10000-20000':
        return 'J_10000-20000'
    elif x=='20000-40000':
        return 'K_20000-40000'
    else: return "空值"
nn['最大逾期金额']=nn['最大逾期金额'].apply(qj4)
def qj1(x):
    if x=='0':
        return 'A_0'    
    if x=='(0,500)':
        return 'B_(0,500)'
    elif x=='[500,1000)':
        return 'C_[500,1000)'
    elif x=='[1000,2000)':
        return 'D_[1000,2000)'  
    elif x=='[2000,3000)':
        return 'E_[2000,3000)'
    elif x=='[3000,5000)':
        return 'F_[3000,5000)'
    elif x=='[5000,10000)':
        return 'G_[5000,10000)'
    elif x=='[10000,20000)':
        return 'H_[10000,20000)'
    elif x=='[20000,30000)':
        return 'I_[20000,30000)'
    elif x=='[30000,50000)':
        return 'J_[30000,50000)'
    elif x=='[50000,+)':
        return 'K_[50000,+)'
    else: return "空值"
nn['近1个月履约贷款总金额']=nn['近1个月履约贷款总金额'].apply(qj1)
nn['近3个月履约贷款总金额']=nn['近3个月履约贷款总金额'].apply(qj1)
nn1=nn.copy()
pfk=pd.read_excel('/chenqianguang/pfk.xlsx',engine='openpyxl')
coll=['履约笔数',

'最大逾期金额',
'近12个月贷款金额在1w以上的笔数',
'消金贷款类机构最大授信额度',
'近1个月履约贷款总金额',
'申请准入分',
'近3个月履约贷款总金额',
'近1个月履约贷款次数',
'近3个月履约贷款次数']
ys=pfk
for i in coll:
dictys = dict(zip(ys[ys['变量']i]['区间'],ys[ys['变量']i]['得分']))
nn[i]=nn[i].map(dictys)
nn['fenshu'] = nn[coll].sum(axis=1)
nn['fenshu'] = 383+nn['fenshu']
nn.rename(columns={'fenshu':"TW_V100"},inplace=True)
nn.drop_duplicates(subset="idcard",inplace=True)
nn1.drop_duplicates(subset="idcard",inplace=True)
zt=nn1.merge(nn[['idcard','TW_V100']],on="idcard",how="left")
zt.drop_duplicates(subset='idcard',inplace=True)
def qj(x):
values=[0,262,316,352,374,390,405,416,428,445,np.inf]
index=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R']
for i in range(len(values)-1):
if values[i]<=x<values[i+1]:
return '{0}_[{1},{2})'.format(index[i],values[i],values[i+1])
return '空值'
zt['TW_V100']=zt['TW_V100'].astype('float')
zt['TW_V100_bin']=zt['TW_V100'].apply(qj)
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')
zt.to_sql("score", conn, if_exists='append',index=False)
else :print("结束运行")

发表于 2024-06-12 17:37 广爷天下无双阅读(7) 评论(0) 编辑收藏举报

刷新页面返回顶部