#coding=utf-8 import shutil import pdfplumber import pandas as pd import numpy as np; import sys import os import traceback from prp import PrpCrypt #指标相关 import loanIndexParser as lip; import payRcdIndexParser as prp; import creditCardIndexParser as cip import queryInfoIndexParser as qip import requests import utils; import time; import consts; import math import dfParser; import gc from dbController import DbController from ini_op import Config; base_dir = os.path.dirname(os.path.abspath(__file__)) config = Config(base_dir+"/config.ini"); #连接数据库 dbController = DbController(); pd.set_option('mode.chained_assignment', None) import log logger = log.logger # 查询信息 dfMap = {}; allHeaders = [] # 所有表头 queryInfoDf = pd.DataFrame(); queryInfoDf_header = ["被查询者姓名", "被查询者证件类型", "被查询者证件号码", "查询机构", "查询原因"]; dfMap["queryInfoDf"] = {"df": queryInfoDf, "nextDf": None}; allHeaders.append(queryInfoDf_header); # 身份信息 identityDf = pd.DataFrame(); identity_header = ['性别', '出生日期', '婚姻状况', '学历', '学位', '就业状况', '国籍', '电子邮箱'] addressDf = pd.DataFrame(); # 通讯地址 dfMap["identityDf"] = {"df": identityDf, "nextDf": None, "mobiles": None}; allHeaders.append(identity_header); # 配偶信息 mateDf = pd.DataFrame(); mateDf_header = ['姓名', '证件类型', '证件号码', '工作单位', '联系电话'] dfMap["mateDf"] = {"df": mateDf, "nextDf": None}; allHeaders.append(mateDf_header); # 居住信息====暂时该信息没有用到先不解析 liveInfoDf = pd.DataFrame(); liveInfoDf_header = ['编号', '居住地址', '住宅电话', '居住状况', '信息更新日期'] dfMap["liveInfoDf"] = {"df": liveInfoDf, "nextDf": None}; allHeaders.append(liveInfoDf_header); # 职业信息 occupationDf = pd.DataFrame(); occupationInfo_header = ['编号', '工作单位', '单位性质', '单位地址', '单位电话'] occupationInfo_header1 = ['编号', '职业', '行业', '职务', '职称', '进入本单位年份', '信息更新日期'] dfMap["occupationDf"] = ({"df": occupationDf, "nextDf": None}); # allHeaders.append(occupationInfo_header1); allHeaders.append(occupationInfo_header); # 上次查询记录 preQueryRcd_header0 = ['上一次查询记录'] allHeaders.append(preQueryRcd_header0); # 查询记录概要 # queryInfoBriefDf = pd.DataFrame(); # queryInfoBrief_header0 = ['最近1个月内的查询机构数', '最近1个月内的查询次数', '最近2年内的查询次数'] # queryInfoBrief_header1 = ['贷款审批', '信用卡审批', '贷款审批', '信用卡\n审批', '本人查询', '贷后管理', '担保资格\n审查', '特约商户\n实名审查'] # dfMap["queryInfoBriefDf"] = ({"df": queryInfoBriefDf, "nextDf": None}); # allHeaders.append(queryInfoBrief_header0); # allHeaders.append(queryInfoBrief_header1); # 信贷交易信息提示 loanTradeInfoDf = pd.DataFrame(); loanTradeInfo_header = ['业务类型', '账户数', '首笔业务发放月份']; dfMap["loanTradeInfoDf"] = ({"df": loanTradeInfoDf, "nextDf": None}); allHeaders.append(loanTradeInfo_header) # 信贷交易违约信息概要 # 被追偿信息汇总 资产处置和垫款业务 recoveryInfoSumDf = pd.DataFrame(); recoveryInfoSumDf_header = ['业务种类', '账户数', '余额']; dfMap["recoveryInfoSumDf"] = ({"df": recoveryInfoSumDf, "nextDf": None}); allHeaders.append(recoveryInfoSumDf_header) # 呆账信息汇总 badDebtsInfoSumDf = pd.DataFrame(); badDebtsInfoSumDf_header = ['账户数', '余额']; # 被追偿信息汇总 dfMap["badDebtsInfoSumDf"] = ({"df": badDebtsInfoSumDf, "nextDf": None}); allHeaders.append(badDebtsInfoSumDf_header) # 逾期透资信息汇总 overdueInfoSumDf = pd.DataFrame(); overdueInfoSumDf_header = ['账户类型', '账户数', '月份数', '单月最高逾期/透支总额', '最长逾期/透支月数'] dfMap["overdueInfoSumDf"] = ({"df": overdueInfoSumDf, "nextDf": None}); allHeaders.append(overdueInfoSumDf_header) # 非循环贷账户信息汇总 loanAccountInfoSumDf = pd.DataFrame(); loanAccountInfoSumDf_header0 = ['非循环贷账户信息汇总'] loanAccountInfoSumDf_header1 = ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款'] dfMap["loanAccountInfoSumDf"] = ({"df": loanAccountInfoSumDf, "nextDf": None}); allHeaders.append(loanAccountInfoSumDf_header0) allHeaders.append(loanAccountInfoSumDf_header1) # 循环额度下分账户信息汇总 cycleCreditAccountInfoSumDf = pd.DataFrame(); cycleCreditAccountInfoSumDf_header0 = ['循环额度下分账户信息汇总'] cycleCreditAccountInfoSumDf_header1 = ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款'], dfMap["cycleCreditAccountInfoSumDf"] = ({"df": cycleCreditAccountInfoSumDf, "nextDf": None}); allHeaders.append(cycleCreditAccountInfoSumDf_header0) allHeaders.append(cycleCreditAccountInfoSumDf_header1) # 循环贷账户信息汇总 cycleLoanAccountInfoSumDf = pd.DataFrame(); cycleLoanAccountInfoSumDf_header0 = ['循环贷账户信息汇总'] cycleLoanAccountInfoSumDf_header1 = ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款'] dfMap["cycleLoanAccountInfoSumDf"] = ({"df": cycleLoanAccountInfoSumDf, "nextDf": None}); allHeaders.append(cycleLoanAccountInfoSumDf_header0) allHeaders.append(cycleLoanAccountInfoSumDf_header1) # 贷记卡账户信息汇总 creditCardInfoSumDf = pd.DataFrame(); creditCardInfoSumDf_header0 = ['贷记卡账户信息汇总'] creditCardInfoSumDf_header1 = ['发卡机构数', '账户数', '授信总额', '单家机构最高\n授信额', '单家机构最低\n授信额', '已用额度', '最近6个月平\n均使用额度'] dfMap["creditCardInfoSumDf"] = ({"df": creditCardInfoSumDf, "nextDf": None}); allHeaders.append(creditCardInfoSumDf_header0) allHeaders.append(creditCardInfoSumDf_header1) # 准贷记卡账户信息汇总 creditCardInfoSumDfZ = pd.DataFrame(); creditCardInfoSumDfZ_header0 = ['准贷记卡账户信息汇总']#'准贷记卡账户信息汇总' creditCardInfoSumDfZ_header1 = ['发卡机构数', '账户数', '授信总额', '单家机构最高\n授信额', '单家机构最低\n授信额', '透支余额', '最近6个月平\n均透支余额'] dfMap["creditCardInfoSumDfZ"] = ({"df": creditCardInfoSumDfZ, "nextDf": None}); allHeaders.append(creditCardInfoSumDfZ_header0) allHeaders.append(creditCardInfoSumDfZ_header1) #公共信息概要 publicInfoBriefDf = pd.DataFrame(); publicInfoBriefDf_header0 = ['公共信息汇总'] dfMap["publicInfoBriefDf"] = ({"df": publicInfoBriefDf, "nextDf": None}); allHeaders.append(publicInfoBriefDf_header0) #查询记录汇总 queryRecordSumDf_header0=['最近1个月内的查询机构数', '最近1个月内的查询次数', '最近2年内的查询次数'] queryRecordSumDf = pd.DataFrame(); dfMap["queryRecordSumDf"] = ({"df": queryRecordSumDf, "nextDf": None}); allHeaders.append(queryRecordSumDf_header0) # 非循环贷账户，循环额度下分账户 # 循环贷账户 loan_header = ['管理机构', '账户标识', '开立日期', '到期日期', '借款金额', '账户币种'] loanDfs = []; dfMap["loanDfs"] = ({"dfs": loanDfs, "nextDf": []}); allHeaders.append(loan_header) # 贷记卡账户 creditCard_header = ['发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '业务种类', '担保方式'] creditCardDfs = []; dfMap["creditCardDfs"] = ({"dfs": creditCardDfs, "nextDf": []}); allHeaders.append(creditCard_header) # 准备贷记卡账户 creditCardZ_header = ['发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '担保方式'] creditCardDfsZ = []; dfMap["creditCardDfsZ"] = ({"dfs": creditCardDfsZ, "nextDf": []}); allHeaders.append(creditCardZ_header) # # 相关还款责任信息汇总 未使用到 # 信贷交易信息明细 # 被追偿信息 未使用到 recoveryInfoDfs_header = ['管理机构','业务种类','债权接收日期','债权金额','债权转移时的还款状态'] recoveryInfoDfs = []; dfMap["recoveryInfoDfs"] = ({"dfs": recoveryInfoDfs, "nextDf": []}); allHeaders.append(recoveryInfoDfs_header) # 公共信息明细 # 强制执行记录 forceExecRcdDfs_header = ['编号', '执行法院', '执行案由', '立案日期', '结案方式'] forceExecRcdDfs = []; dfMap["forceExecRcdDfs"] = ({"dfs": forceExecRcdDfs, "nextDf": []}); allHeaders.append(forceExecRcdDfs_header) # 查询记录 queryRecordDetailDf_header = ['编号', '查询日期', '查询机构', '查询原因'] dfMap["queryRecordDetailDf"] = ({"df": pd.DataFrame(), "nextDf": []}); allHeaders.append(queryRecordDetailDf_header) #住房公积金参缴记录 housingFundRcdDfs_header =['参缴地', '参缴日期', '初缴月份', '缴至月份', '缴费状态', '月缴存额', '个人缴存比例', '单位缴存比例'] housingFundRcdDfs = [] dfMap["housingFundRcdDfs"] = ({"dfs": housingFundRcdDfs, "nextDf": []}); allHeaders.append(housingFundRcdDfs_header) repaymentSumDf_header0=['相关还款责任信息汇总'] dfMap["repaymentSumDf"] = ({"df": pd.DataFrame(), "nextDf": None}); allHeaders.append(repaymentSumDf_header0) # 处理分页思路 # df估计得放到对象里面,然后存储下一个df，一个对象里包含key # 然后判断对象的df的完整性，如果不完整代表被分页了，把nextdf合并到当前的df # 针对可合并的列的场景 # ======= keyList = [] # 存储所有的df的key列表 # pd.Series() # 检查数据是否带表头 # 应该是每一页开头的一行和每个表头对比一次，确认是不是表头，或者表头有什么共同的规律也可以看下 import timeit # 定义指标部分======================start reportTime = ""; # 报告时间 # 被查询者姓名 queryInfoName = ""; queryInfoCardId = "" # 被查询者证件号码 # 定义指标部分======================end # 被查询信息-基础信息 # 报告时间 # 被查询者姓名 # 被查询者证件号码 # 基础信息 queryInfo = {"reportTime":"","queryInfoCardId":""} # 身份信息 identity = {} # 配偶信息 mate = {} # 信贷交易信息提示-信用提示 loanTradeInfo = {'perHouseLoanAccount': 0, 'perBusHouseLoanAccount': 0, 'otherLoanAccount': 0, 'loanMonthMin': 0, 'creditCardMonthMin': 0, 'creditAccount': 0, 'creditAccountZ': 0} # 逾期及违约信息概要 overdueBrief = {} # 逾期及透资信息汇总 # 贷款逾期账户数 loanOverdueAccount # 贷款逾期月份数 loanOverdueMonth # 贷款单月最高逾期总额 loanCurMonthOverdueMaxTotal # 贷款最长逾期月数 loanMaxOverdueMonth overdueInfo = {"loanOverdueAccount": "", "loanOverdueMonth": "", "loanCurMonthOverdueMaxTotal": "", "loanMaxOverdueMonth": "", "creditCardOverdueAccount": "", "creditCardOverdueMonth": "", "creditCardCurMonthOverdueMaxTotal": "", "creditCardMaxOverdueMonth": ""} # 未结清贷款信息汇总 # ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款'] loanAccountInfoSum = {"mgrOrgCount": 0, "account": 0, "creditTotalAmt": 0, "balance": 0, "last6AvgPayAmt": 0} # 未销户贷记卡发卡法人机构数 # 未销户贷记卡发卡机构数 # 未销户贷记卡账户数 # 未销户贷记卡授信总额 # 未销户贷记卡单家行最高授信额 # 未销户贷记卡单家行最低授信额 # 未销户贷记卡已用额度 # 未销户贷记卡近6月平均使用额度 # 未结清贷记卡信息汇总 # ['发卡机构数', '账户数', '授信总额', '单家机构最高\n授信额', '单家机构最低\n授信额', '已用额度', '最近6个月平\n均使用额度'] creditCardInfoSum = {"awardOrgCount": 0, "account": 0, "creditTotalAmt": 0, "perMaxCreditTotalAmt": 0, "perMinCreditTotalAmt": 0, "useAmt": 0, "last6AvgUseAmt": 0} # 信 贷 审 批 查 询 记 录 明 细 queryRecordDetail = {"last1MonthQueryTimes": 0, "last3MothLoanApproveTimes": 0, "last3MonthQueryTimes": 0, "lastTimeLoanApproveMonth": 0} #最近一笔结清贷款的贷款金额  loanAccountInfo = {"lastSettleLoanAmt": 0} loanAccountDfs=[];#横向合并 creditCardAccountDfs=[];#贷记卡账户合并 creditCardAccountDfsZ=[];#准贷记卡账户合并 recoveryInfoAccountDfs=[];#被追偿账户合并 housingFundRcdAccountDfs=[];#公积金账户合并 #============================指标定义区 start============================= #基本信息 拆分 # basicInfoDf = pd.DataFrame(columns=consts.basicInfoHeader, index=[0]) #身份信息 identityInfoIndex = '身份信息' identityInfoDf = pd.DataFrame(columns=consts.identityInfoHeader,index=[identityInfoIndex]) #配偶信息 mateInfoIndex = '配偶信息' mateInfoDf = pd.DataFrame(columns=consts.mateInfoHeader,index=[mateInfoIndex]) #居住信息 liveInfoIndex = '居住信息' liveInfoDf = pd.DataFrame(columns=consts.liveInfoHeader,index=[liveInfoIndex]) #职业信息 occupationInfoIndex = '职业信息' occupationInfoDf = pd.DataFrame(columns=consts.occupationInfoHeader,index=[occupationInfoIndex]) #信贷交易信息提示 loanTradeInfoIndex = '信贷交易信息提示' briefInfoDf_loanTradeInfo = pd.DataFrame(columns=consts.briefInfoHeader_loanTradeInfo,index=[loanTradeInfoIndex]) #被追偿信息汇总及呆账信息汇总 recoveryInfoSumIndex = '信贷交易违约信息概要' briefInfoDf_recoveryInfoSum = pd.DataFrame(columns=consts.briefInfoHeader_recoveryInfo,index=[recoveryInfoSumIndex]) #呆账信息汇总 badDebtsInfoIndex = '呆账信息汇总' briefInfoDf_badDebtsInfoSum = pd.DataFrame(columns=consts.briefInfoHeader_badDebtsInfoSum,index=[badDebtsInfoIndex]) #逾期（透支）信息汇总 overdueInfoSumIndex='逾期（透支）信息汇总' briefInfoDf_overdueInfoSum = pd.DataFrame(columns=consts.briefInfoHeader_overdueInfoSum,index=[overdueInfoSumIndex]) #信贷交易授信及负债信息概要 loanTradeCreditInfoIndex='信贷交易授信及负债信息概要' briefInfoDf_loanTradeCreditInfo = pd.DataFrame(columns=consts.briefInfoHeader_loanTradeCreditInfo,index=[loanTradeCreditInfoIndex]).fillna(0.0) #公共信息概要 publicInfoBriefIndex = '公共信息概要' publicInfoBriefDf = pd.DataFrame(columns=consts.publicInfoBriefHeader,index=[publicInfoBriefIndex]) #查询记录汇总 queryRecordSumIndex = '查询记录汇总' queryRecordSumDf = pd.DataFrame(columns=consts.queryRecordSumHeader,index=[queryRecordSumIndex]) #信贷交易明细-被追偿信息 recoveryInfoIndex='被追偿信息' creditTradeDetailDf_recoveryInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_recoveryInfo,index=[recoveryInfoIndex]) #信贷交易明细-特殊交易 specialTradeIndex='特殊交易' creditTradeDetailHeader_specialTrade = pd.DataFrame(columns=consts.creditTradeDetailHeader_specialTrade,index=[specialTradeIndex]) #信贷交易明细 #非循环贷账户 loanInfoIndex='非循环贷账户' creditTradeDetailDf_loanAccountInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_loanAccountInfo,index=[loanInfoIndex]) #循环额度下分账户 cycleCreditAccountInfoIndex='循环额度下分账户' creditTradeDetailDf_cycleCreditAccountInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_cycleCreditAccountInfo,index=[cycleCreditAccountInfoIndex]) #循环贷账户 cycleLoanAccountInfoIndex='循环贷账户' creditTradeDetailDf_cycleLoanAccountInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_cycleLoanAccountInfo,index=[cycleLoanAccountInfoIndex]) #贷款信息 loanAccountInfoIndex='贷款信息' loanAccountInfoDf = pd.DataFrame(columns=consts.loanAccountInfoHeader,index=[loanAccountInfoIndex]) #贷记卡信息 creditCardAccountInfoIndex = '贷记卡账户' creditCardAccountInfoDf = pd.DataFrame(columns=consts.creditCardAccountInfoHeader,index=[creditCardAccountInfoIndex]) #准贷记卡 creditCardAccountInfoIndexZ = '准贷记卡账户' creditCardAccountInfoDfZ = pd.DataFrame(columns=consts.creditCardAccountInfoHeaderZ,index=[creditCardAccountInfoIndexZ]) useRateIndex = '使用率' useRateDf = pd.DataFrame(columns=consts.creditTradeDetailHeader_useRate,index=[useRateIndex]) openAccountIndex = '开户数' openAccountDf = pd.DataFrame(columns=consts.creditTradeDetailHeader_openAccount,index=[openAccountIndex]) payRcdStatusIndex = '24期还款状态' payRcdStatusDf = pd.DataFrame(columns=consts.creditTradeDetailHeader_payRcdStatus,index=[payRcdStatusIndex]) #查询记录明细指标 queryRecordDetailIndex = '信贷审批查询记录明细' queryRecordDetailDf = pd.DataFrame(columns=consts.queryRecordDetailHeader,index=[queryRecordDetailIndex]) #住房公积金 housingFundRcdIndex = '住房公积金参缴记录' housingFundRcdDf = pd.DataFrame(columns=consts.housingFundRcdHeader,index=[housingFundRcdIndex]) #============================指标定义区 end============================= # 解析被查询信息指标 def parseQueryInfo(dfObj): df = dfObj["df"]; reportTime = df.loc[0, :][3] reportTime = reportTime.split("：")[1] reportTime = reportTime.replace(".", "-"); # 报告时间 queryInfo["reportTime"] = reportTime row = df.loc[2, :] queryInfo["queryInfoName"] = row[0]; # 被查询者姓名 # basicInfoDf.loc[0, '姓名'] = row[0] queryInfo["queryInfoCardId"] = row[2].replace("\n", ""); # 被查询者证件号码 # basicInfoDf.loc[0, '身份证'] = row[2].replace("\n", "") # 婚姻状况 # 学历 # 单位电话 # 住宅电话 # 通讯地址 def parseIdentity(dfObj): df = dfObj["df"]; row1 = df.loc[1, :].dropna().reset_index(drop=True) # identity["marital"] = row1[3] # 婚姻状况 # identity["education"] = row1[4] # 学历 # identity["commAddress"] = row1[9].replace("\n", ""); # 通讯地址 identityInfoDf.loc[identityInfoIndex, '性别'] = row1[0] identityInfoDf.loc[identityInfoIndex, '出生日期'] = dfParser.formatDate(row1[1])[0:7] identityInfoDf.loc[identityInfoIndex, '国籍'] = row1[6] identityInfoDf.loc[identityInfoIndex, '户籍地址'] = row1[9].replace("\n", "") identityInfoDf.loc[identityInfoIndex, '婚姻状况'] = row1[2] identityInfoDf.loc[identityInfoIndex, '学位'] = row1[4] identityInfoDf.loc[identityInfoIndex, '通讯地址'] = row1[8].replace("\n", "") identityInfoDf.loc[identityInfoIndex, '就业状况'] = row1[5] mobileDf = dfObj["mobileDf"]; identityInfoDf.loc[identityInfoIndex, '历史手机号码数'] = mobileDf.index.size reportTime = queryInfo["reportTime"] identityInfoDf.loc[identityInfoIndex, '近3个月手机号码数'] = getLastMonthMobileCount(mobileDf,3,reportTime) identityInfoDf.loc[identityInfoIndex, '近6个月手机号码数'] = getLastMonthMobileCount(mobileDf, 6,reportTime) identityInfoDf.loc[identityInfoIndex, '近12个月手机号码数'] = getLastMonthMobileCount(mobileDf, 12,reportTime) identityInfoDf.loc[identityInfoIndex, '近24个月手机号码数'] = getLastMonthMobileCount(mobileDf, 24,reportTime) #最近几个月电话号码数 def getLastMonthMobileCount(df, month,reportTime): # 当前日期 last1MonthDateStr = reportTime # 最近一个月 lastMonthDate = np.datetime64(last1MonthDateStr, "D") - np.timedelta64(30 * month, 'D') lastMonthMobileDf = df[df[5] >= str(lastMonthDate)] return lastMonthMobileDf.shape[0]; # 配偶姓名 # 配偶证件号码 # 配偶工作单位 # 配偶联系电话 def parseMate(dfObj): df = dfObj["df"]; if not df.empty: row1 = df.loc[1, :] mate["mateName"] = row1[0] # 配偶姓名 mate["mateCardId"] = row1[2] # 配偶证件号码 mate["mateWorkCompany"] = row1[3].replace("\n", ""); # 配偶工作单位 mate["mateContactTel"] = row1[4]; # 配偶联系电话 mateInfoDf.loc[mateInfoIndex, '姓名'] = row1[0] mateInfoDf.loc[mateInfoIndex, '证件号码'] = row1[2] mateInfoDf.loc[mateInfoIndex, '工作单位'] = row1[3].replace("\n", ""); mateInfoDf.loc[mateInfoIndex, '联系电话'] = row1[4].replace("\n", ""); #解析居住信息 def parseLiveInfo(dfObj): df = dfObj["df"]; if not df.empty: row1 = df.loc[1, :] liveInfoDf.loc[liveInfoIndex, '居住地址'] = row1[1] liveInfoDf.loc[liveInfoIndex, '住宅电话'] = row1[2] liveInfoDf.loc[liveInfoIndex, '历史居住地址个数'] = df.index.size-1; curDate = np.datetime64(time.strftime("%Y-%m-%d")); last3year = str(curDate)[0:4] last3yearDate = str(int(last3year)-3)+str(curDate)[4:10] lastLiveDf = df[df[4]>=last3yearDate]; liveInfoDf.loc[liveInfoIndex, '最近3年内居住地址个数'] = lastLiveDf.index.size-1; houseIndex = df[df[3]=='自置'].index.size>0 if (houseIndex): houseStr = '是' else: houseStr= '否' liveInfoDf.loc[liveInfoIndex, '当前居住状况-是否具有自有住房'] = houseStr; liveInfoDf.loc[liveInfoIndex, '居住状况'] = row1[3] liveInfoDf.loc[liveInfoIndex, '信息更新日期'] = row1[4] #解析职业信息 def parseOccupationInfoDf(dfObj): df = dfObj["df"]; if not df.empty: occIndex1 = 0#判断职业从哪行开始 for i in range(0,df.index.size): if df.loc[i,:].dropna().tolist()==occupationInfo_header1: occIndex1=i; break; occDf = df[1:occIndex1].reset_index(drop=True)#工作单位 occDfNew = pd.DataFrame() occDf1New = pd.DataFrame() #删除为none的列 合并的bug TODO for i in range(0,occDf.index.size): occDfNew = occDfNew.append([pd.DataFrame(occDf.iloc[i].dropna().reset_index(drop=True)).T],ignore_index=True) occDf1 = df[occIndex1+1:df.index.size].reset_index(drop=True) #职业 for i in range(0,occDf1.index.size): occDf1New = occDf1New.append([pd.DataFrame(occDf1.iloc[i].dropna().reset_index(drop=True)).T], ignore_index=True) occDf = pd.concat([occDfNew, occDf1New], axis=1, ignore_index=True)#合并df row = occDf.loc[0, :].dropna()#取最新 occupationInfoDf.loc[occupationInfoIndex, '工作单位'] = row[1] last3yearDate = utils.getLastMonthDate(queryInfo['reportTime'],12*3) occDf = utils.replaceDateColIdx(occDf,occDf.columns.size-1) dateIndex = occDf.columns.size-1;#日期列 last3yearOccDf = occDf[occDf[dateIndex]>=last3yearDate] occupationInfoDf.loc[occupationInfoIndex, '最近3年内工作单位数'] = last3yearOccDf.index.size; occupationInfoDf.loc[occupationInfoIndex, '单位电话'] = row[4]; reportTime = queryInfo['reportTime'] try: minDateIndex = np.argmin(occDf[dateIndex]); maxDateIndex = np.argmax(occDf[dateIndex]); rowYearMin = occDf.loc[minDateIndex, :].dropna() rowYearMax = occDf.loc[maxDateIndex, :].dropna() if rowYearMin[10]!="--": occupationInfoDf.loc[occupationInfoIndex, '最早进入本单位年份距报告日期时长'] = int(str(np.datetime64(reportTime, "Y")))-int(rowYearMin[10]) if rowYearMax[10]!="--": occupationInfoDf.loc[occupationInfoIndex, '最新进入本单位年份距报告日期时长'] = int(str(np.datetime64(reportTime, "Y")))-int(rowYearMax[10]) except: logger.error("最早进入本单位年份距报告日期时长解析异常") row0 = occDf.loc[0,:].dropna().reset_index(drop=True)#最新 occupationInfoDf.loc[occupationInfoIndex, '单位性质'] =row0[2] occupationInfoDf.loc[occupationInfoIndex, '单位地址'] = row0[3].replace("\n","") occupationInfoDf.loc[occupationInfoIndex, '职业'] = row0[6] occupationInfoDf.loc[occupationInfoIndex, '行业'] = row0[7] occupationInfoDf.loc[occupationInfoIndex, '职务'] = row0[8] occupationInfoDf.loc[occupationInfoIndex, '职称'] = row0[9] occupationInfoDf.loc[occupationInfoIndex, '进入本单位年份'] = row0[10] occupationInfoDf.loc[occupationInfoIndex, '信息更新日期'] = row0[11] occupationInfoDf.loc[occupationInfoIndex, '历史工作单位数'] = occDf1.index.size # 日期相减离当前时间月份 # 贷款账龄（月数）=当前日期(2020-04-01)-最小月份的1日(2019.2->2019-12-01)=4 # def difMonth(dateStr): # return int(int(str(np.datetime64(time.strftime("%Y-%m-%d")) - # np.datetime64(dateStr.replace('.', '-'), "D")).split(" ")[0]) / 30); # 信贷交易明细汇总 def parseLoanTradeInfo(dfObj): df = dfObj["df"]; # row1 = df.loc[1, :] loanMonthDf = df[1: 4] loanMonthDf = loanMonthDf.reset_index(drop=True) briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '个人住房贷款账户数'] = utils.toInt(loanMonthDf.loc[0, :][2]) briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex,'个人商用房贷款（包括商住两用）账户数']=utils.toInt(loanMonthDf.loc[1, :][2]) briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '其他类贷款账户数'] = utils.toInt(loanMonthDf.loc[2, :][2]) creditCardDf = df[4: 6]; creditCardDf = creditCardDf.reset_index(drop=True) briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '贷记卡账户数'] = utils.toInt(creditCardDf.loc[0, :][2]) briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '准贷记卡账户数'] = utils.toInt(creditCardDf.loc[1, :][2]) # 解析呆账信息汇总 def parseBadDebtsInfoSumDf(dfObj): df = dfObj["df"]; if not df.empty: row1 = df.loc[2, :] briefInfoDf_badDebtsInfoSum.loc[badDebtsInfoIndex, '账户数'] = row1[0]; briefInfoDf_badDebtsInfoSum.loc[badDebtsInfoIndex, '余额'] = utils.replaceAmt(row1[1]); # 解析被追偿信息汇总 def parseRecoveryInfoSum(dfObj): df = dfObj["df"]; if not df.empty: row1 = df.loc[2, :] row2 = df.loc[3, :] row3 = df.loc[4, :] overdueBrief["disposalInfoSumAccount"] = row1[1]; # 资产处置信息汇总笔数 briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '资产处置业务账户数'] = row1[1]; overdueBrief["disposalInfoSumAmt"] = row1[2]; # 资产处置信息汇总余额 briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '资产处置业务余额'] = utils.replaceAmt(row1[2]); overdueBrief["advanceInfoSumAccount"] = row2[1]; # 垫款业务笔数 briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '垫款业务账户数'] = row2[1]; overdueBrief["advanceInfoSumAmt"] = row2[2]; # 垫款业务余额 briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '垫款业务余额'] = utils.replaceAmt(row2[2]); briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '合计总账户数'] = row3[1]; briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '合计总余额'] = utils.replaceAmt(row3[2]); # 贷款逾期账户数 # 贷款逾期月份数 # 贷款单月最高逾期总额 # 贷款最长逾期月数 def parseOverdueInfoSum(dfObj): df = dfObj["df"]; if not df.empty: row2= df.loc[2, :] row3 = df.loc[3, :] row4 = df.loc[4, :] row5 = df.loc[5, :] row6 = df.loc[6, :] #这块的数据需要进行出来 TODO briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户账户数'] = utils.toInt(row2[1]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户月份数'] = utils.toInt(row2[2]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户单月最高逾期总额'] = utils.replaceAmt(row2[3]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户最长逾期月数'] = utils.toInt(row2[4]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户账户数'] = utils.toInt(row3[1]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户月份数'] = utils.toInt(row3[2]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户单月最高逾期总额'] = utils.replaceAmt(row3[3]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户最长逾期月数'] = utils.toInt(row3[4]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户账户数'] = utils.toInt(row4[1]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户月份数'] = utils.toInt(row4[2]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户单月最高逾期总额'] = utils.replaceAmt(row4[3]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户最长逾期月数'] = utils.toInt(row4[4]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户账户数'] = utils.toInt(row5[1]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户月份数'] = utils.toInt(row5[2]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户单月逾期总额'] = utils.replaceAmt(row5[3]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户最长逾期月数'] = utils.toInt(row5[4]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户账户数'] = utils.toInt(row6[1]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户月份数'] = utils.toInt(row6[2]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户单月透支总额'] = utils.replaceAmt(row6[3]); briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户最长透支月数'] = utils.toInt(row6[4]); overdueInfoAccountDf = df[df[1] != '--']; overdueInfoAccountDf = overdueInfoAccountDf[2:7] briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户所有逾期账户最长逾期/透支月数最大值']=np.max(overdueInfoAccountDf[4].astype('int')) #np.sum(overdueInfoAccountDf[1]) briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户所有逾期账户数加总']= np.sum(overdueInfoAccountDf[1].astype('int'))# TODO # briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户过去5年出现逾期的所有账户数目']=None# TODO # 未结清贷款法人机构数 从“未结清贷款信息汇总”中直接提取LoanLegalOrgNum # 未结清贷款机构数 从“未结清贷款信息汇总”中直接提取LoanOrgNum # 未结清贷款笔数 从“未结清贷款信息汇总”中直接提取CountNum # 未结清贷款合同总额 从“未结清贷款信息汇总”中直接提取ContractProfits # 未结清贷款合同余额 从“未结清贷款信息汇总”中直接提取Balance # 未结清贷款近6月平均应还款 从“未结清贷款信息汇总”中直接提取Last6MothsAvgRepayAmount # 个人贷款未结清笔数 "从“未结清贷款信息汇总”计算客户符合以下条件的贷款笔数 # 1.贷款类型不为（'%个人助学贷款%' ，'%农户贷款%'） # 2.贷款额度>100元 # 3.贷款状态不为“结清”" # 非循环贷账户信息汇总 def doFilterCalc(dfx): dfx = dfx.replace('--', 0) return dfx; # 科学计数法转换 def replaceAmt(dfx): return dfx.str.replace(',', '') # 非循环贷账户信息汇总 def parseLoanAccountInfoSum(dfObj): df = dfObj["df"]; if not df.empty: loanAccountInfoSumDf = df[2:3]; loanAccountInfoSumDf = doFilterCalc(loanAccountInfoSumDf); # 替换--为0 loanAccountInfoSumDf = loanAccountInfoSumDf.reset_index(drop=True) row0 = loanAccountInfoSumDf.loc[0,:] briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户管理机构数'] = int(row0[0]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数'] = int(row0[1]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户授信总额'] = int(utils.replaceAmt(row0[2])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户余额'] = int(utils.replaceAmt(row0[3])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户6月平均应还款'] = int(utils.replaceAmt(row0[4])) # 循环额度下分账户 def parseCycleCreditAccountInfoSum(dfObj): df = dfObj["df"]; if not df.empty: cycleCreditAccountInfoSumDf = df[2:3]; cycleCreditAccountInfoSumDf = doFilterCalc(cycleCreditAccountInfoSumDf); # 替换--为0 cycleCreditAccountInfoSumDf = cycleCreditAccountInfoSumDf.reset_index(drop=True) row0 = cycleCreditAccountInfoSumDf.loc[0,:] briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户管理机构数'] = int(row0[0]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户账户数'] = int(row0[1]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户授信总额'] = int(utils.replaceAmt(row0[2])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户余额'] = int(utils.replaceAmt(row0[3])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户6月平均应还款'] = int(utils.replaceAmt(row0[4])) # 循环贷账户信息 def parseCyleLoanAccountInfoSum(dfObj): df = dfObj["df"]; if not df.empty: cycleLoanAccountInfoSumDf = df[2:3]; cycleLoanAccountInfoSumDf = doFilterCalc(cycleLoanAccountInfoSumDf); # 替换--为0 cycleLoanAccountInfoSumDf = cycleLoanAccountInfoSumDf.reset_index(drop=True) row0 = cycleLoanAccountInfoSumDf.loc[0,:] briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户管理机构数'] = int(row0[0]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户账户数'] = int(row0[1]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户授信总额'] = int(utils.replaceAmt(row0[2])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户余额'] = int(utils.replaceAmt(row0[3])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户6月平均应还款'] = int(utils.replaceAmt(row0[4])) # 解析贷记卡信息汇总,包含准贷记卡 def parseCreditCardInfoSum(dfObj): df = dfObj["df"]; if not df.empty: creditCardInfoSumDf = df[2:3]; creditCardInfoSumDf = doFilterCalc(creditCardInfoSumDf); # 替换--为0 creditCardInfoSumDf = creditCardInfoSumDf.reset_index(drop=True) row0 = creditCardInfoSumDf.loc[0, :] briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡发卡机构数'] = int(row0[0]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡账户数'] = int(row0[1]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡授信总额'] = int(utils.replaceAmt(row0[2])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡单家机构最高授信额'] = int(utils.replaceAmt(row0[3])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡单家机构最低授信额'] = int(utils.replaceAmt(row0[4])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡已用额度'] = int(utils.replaceAmt(row0[5])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡最近6个月平均使用额度'] = int(utils.replaceAmt(row0[6])) # 解析贷记卡信息汇总,包含准贷记卡 def parseCreditCardInfoSumZ(dfObj): df = dfObj["df"]; if not df.empty: creditCardInfoSumDfZ = df[2:3]; creditCardInfoSumDfZ = doFilterCalc(creditCardInfoSumDfZ); creditCardInfoSumDfZ = creditCardInfoSumDfZ.reset_index(drop=True) row0 = creditCardInfoSumDfZ.loc[0, :] briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡发卡机构数'] = int(row0[0]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡账户数'] = int(row0[1]) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡授信总额'] = int(utils.replaceAmt(row0[2])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡单家机构最高授信额'] = int(utils.replaceAmt(row0[3])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡单家机构最低授信额'] = int(utils.replaceAmt(row0[4])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡已用额度'] = int(utils.replaceAmt(row0[5])) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡最近6个月平均使用额度'] = int(utils.replaceAmt(row0[6])) #相关还款责任 def parseRepaymentSum(dfObj): df = dfObj["df"]; if not df.empty: row4 = df.loc[4,:].dropna().reset_index(drop=True)#第4行 为个人 row8 = None if df.index.size ==9: row8 = df.loc[8,:].dropna().reset_index(drop=True)#第8行 为企业 perAccountNum = 0;#个人账户数 orgAccountNum = 0; # 企业账户数 totalAccountNum = 0;#总账户数 guaranteeAccountNum = 0;#相关还款责任总账户数-担保责任 otherAccountNum =0;#相关还款责任总账户数-其他 perGuaranteeAmt = 0#个人担保金额及其他 orgGuaranteeAmt = 0#企业担保金额及其他 totalGuaranteeAmt = 0;#总担保金额 guaranteeAmt = 0;#相关还款责任总担保金额 otherPaymentAmt = 0;#其他还款责任金额 perGuaranteeBalance = 0 # 个人担保余额及其他 orgGuaranteeBalance = 0 # 企业担保余额及其他 totalGuaranteeBalance = 0;#总担保余额 guaranteeBalance = 0;#相关还款责任总担保余额 otherPaymentBalance = 0; # 其他还款责任余额 #计算总账户数 if row4[0] !="--": perAccountNum=perAccountNum+utils.toInt(row4[0]) guaranteeAccountNum = guaranteeAccountNum + utils.toInt(row4[0])#个人担保责任账户数 if row4[3] !="--": perAccountNum = perAccountNum + utils.toInt(row4[3])#其他 otherAccountNum = otherAccountNum + utils.toInt(row4[3]) # 其他 if row8 != None: if row8[0] != "--": orgAccountNum = orgAccountNum + utils.toInt(row8[0]) guaranteeAccountNum = guaranteeAccountNum + utils.toInt(row8[0])#企业担保责任账户数 if row8[3] != "--": orgAccountNum = orgAccountNum + utils.toInt(row8[3])#其他 otherAccountNum = otherAccountNum + utils.toInt(row8[3]) # 其他 totalAccountNum = perAccountNum+orgAccountNum #计算担保金额 if row4[1] !="--": perGuaranteeAmt=perGuaranteeAmt+utils.replaceAmt(row4[1])#担保 guaranteeAmt = guaranteeAmt + utils.replaceAmt(row4[1]) # 担保 if row4[4] !="--": perGuaranteeAmt = perGuaranteeAmt + utils.replaceAmt(row4[4])#其他 otherPaymentAmt = otherPaymentAmt + utils.replaceAmt(row4[4]) # 其他 if row8 != None: if row8[1] != "--": orgGuaranteeAmt = orgGuaranteeAmt + utils.replaceAmt(row8[1])#担保 guaranteeAmt = guaranteeAmt + utils.replaceAmt(row8[1]) # 担保 if row8[4] != "--": orgGuaranteeAmt = orgGuaranteeAmt + utils.replaceAmt(row8[4])#其他 otherPaymentAmt = otherPaymentAmt + utils.replaceAmt(row8[4]) # 其他 totalGuaranteeAmt = perGuaranteeAmt + orgGuaranteeAmt # 计算余额 if row4[2] !="--": perGuaranteeBalance=perGuaranteeBalance+utils.replaceAmt(row4[2]) guaranteeBalance=guaranteeBalance+utils.replaceAmt(row4[2])#个人担保余额 if row4[5] !="--": perGuaranteeBalance = perGuaranteeBalance + utils.replaceAmt(row4[5])#其他 otherPaymentBalance = otherPaymentBalance + utils.replaceAmt(row4[5]) # 其他 if row8 != None: if row8[2] != "--": orgGuaranteeBalance = orgGuaranteeBalance + utils.replaceAmt(row8[2]) guaranteeBalance = guaranteeBalance + utils.replaceAmt(row8[2])#企业担保余额 if row8[5] != "--": orgGuaranteeBalance = orgGuaranteeBalance + utils.replaceAmt(row8[5]) otherPaymentBalance = otherPaymentBalance + utils.replaceAmt(row8[5]) # 其他 totalGuaranteeBalance = perGuaranteeBalance + orgGuaranteeBalance briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数（担保+其他+个人+企业）'] =totalAccountNum briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额+总还款责任金额（个人+企业）'] =totalGuaranteeAmt briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额+总其他余额（个人+企业）'] =totalGuaranteeBalance if totalGuaranteeAmt !=0: briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额+总其他余额（个人+企业）/相关还款责任账户总担保金额+总其他金额（个人+企业）'] =\ round(totalGuaranteeBalance / totalGuaranteeAmt, 2) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任担保总账户数-个人'] =perAccountNum briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-个人'] =perGuaranteeAmt briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-个人'] =perGuaranteeBalance if perGuaranteeBalance !=0: briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-个人/相关还款责任总担保金额-个人'] = round(perGuaranteeBalance/perGuaranteeBalance,2) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数-企业'] =orgAccountNum briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-企业'] =orgGuaranteeAmt briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-企业'] =orgGuaranteeBalance if orgGuaranteeAmt!=0: briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-企业/相关还款责任总担保金额-企业'] = round(orgGuaranteeBalance/orgGuaranteeAmt,2) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数-担保责任'] =guaranteeAccountNum briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-担保责任'] =guaranteeAmt briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额-担保责任'] =guaranteeBalance if guaranteeAmt!=0: briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-担保责任/相关还款责任总担保金额-担保责任'] =round(guaranteeBalance/guaranteeAmt,2) briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数-其他'] =otherAccountNum briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-其他'] =otherPaymentAmt briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-其他'] =otherPaymentBalance if otherPaymentAmt!=0: briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额-其他/相关还款责任账户总担保金额-其他'] =round(otherPaymentBalance/otherPaymentAmt,2) #解析公共信息汇总 def parsePublicInfoBrief(dfObj): df = dfObj["df"]; if not df.empty: publicInfoBrief = df[1:6]; publicInfoBrief = publicInfoBrief.reset_index(drop=True) row0 = publicInfoBrief.loc[0, :] row1 = publicInfoBrief.loc[1, :] row2 = publicInfoBrief.loc[2, :] row3 = publicInfoBrief.loc[3, :] publicInfoBriefDf.loc[publicInfoBriefIndex, '欠税信息-记录数'] = int(row0[1]) publicInfoBriefDf.loc[publicInfoBriefIndex, '欠税信息-涉及金额'] = int(utils.replaceAmt(row0[2])) publicInfoBriefDf.loc[publicInfoBriefIndex, '民事判决信息-记录数'] = int(row1[1]) publicInfoBriefDf.loc[publicInfoBriefIndex, '民事判决信息-涉及金额'] = int(utils.replaceAmt(row1[2])) publicInfoBriefDf.loc[publicInfoBriefIndex, '强制执行信息-记录数'] = int(row2[1]) publicInfoBriefDf.loc[publicInfoBriefIndex, '强制执行信息-涉及金额'] = int(utils.replaceAmt(row2[2])) publicInfoBriefDf.loc[publicInfoBriefIndex, '行政处罚信息-记录数'] = int(row3[1]) publicInfoBriefDf.loc[publicInfoBriefIndex, '行政处罚信息-涉及金额'] = int(utils.replaceAmt(row3[2])) #解析查询信息汇总 def parseQueryRecordSum(dfObj): df = dfObj["df"]; if not df.empty: queryRecordSumDfTmp = df[2:3]; queryRecordSumDfTmp = queryRecordSumDfTmp.reset_index(drop=True) row0 = queryRecordSumDfTmp.loc[0, :] queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询机构数-贷款审批'] =int(row0[0]) queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询机构数-信用卡审批'] =int(row0[1]) queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询次数-贷款审批'] =int(row0[2]) queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询次数-信用卡审批'] =int(row0[3]) queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询次数-本人查询'] =int(row0[4]) queryRecordSumDf.loc[queryRecordSumIndex, '近2年内的查询次数-贷后管理'] =int(row0[5]) queryRecordSumDf.loc[queryRecordSumIndex, '近2年内的查询次数-担保资格审查'] =int(row0[6]) # 解析查询记录明细 def parseQueryInfoDetail(dfObj): df = dfObj["df"]; reportTime = queryInfo["reportTime"]; if not df.empty: df = utils.replaceDateCol(df) df = df[1:df.index.size] # 去掉表头 queryRecordDetailDf.loc[queryRecordDetailIndex, '近1月查询次数'] =qip.getLastMonthQueryTimes(df, 1, "",reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询次数'] =qip.getLastMonthQueryTimes(df, 3, "",reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询次数'] =qip.getLastMonthQueryTimes(df, 6, "",reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询次数'] =qip.getLastMonthQueryTimes(df, 12, "",reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近1个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 1, "", reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近3个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 3, "", reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近6个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 6, "", reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近12个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 12, "", reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 24, "", reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询次数贷款审批'] =qip.getLastMonthQueryTimes(df, 3, consts.loanApprove, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询次数信用卡审批'] =qip.getLastMonthQueryTimes(df, 3, consts.creditCard, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询次数贷款审批'] =qip.getLastMonthQueryTimes(df, 6, consts.loanApprove, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询次数信用卡审批'] = qip.getLastMonthQueryTimes(df, 6, consts.creditCard, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询次数贷款审批'] = qip.getLastMonthQueryTimes(df, 12, consts.loanApprove, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询次数信用卡审批'] =qip.getLastMonthQueryTimes(df, 12, consts.creditCard, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询机构数贷款审批'] =qip.getLastMonthQueryOrgTimes(df, 3, consts.loanApprove, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询机构数信用卡审批'] =qip.getLastMonthQueryOrgTimes(df, 3, consts.creditCard, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询机构数贷款审批'] =qip.getLastMonthQueryOrgTimes(df, 6, consts.loanApprove, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询机构数信用卡审批'] = qip.getLastMonthQueryOrgTimes(df, 6, consts.creditCard,reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询机构数贷款审批'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.loanApprove, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询机构数信用卡审批'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.creditCard,reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近6个月担保资格审查查询次数'] = qip.getLastMonthQueryOrgTimes(df, 6, consts.insuranceAprove,reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '近12个月担保资格审查查询次数'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.insuranceAprove,reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近6个月贷后管理查询次数'] = qip.getLastMonthQueryOrgTimes(df, 6, consts.loanAfterMgr,reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近12个月贷后管理查询次数'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.loanAfterMgr,reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最后一次查询距离现在的月数贷款审批'] = qip.getLastTimeQueryMonth(df, consts.loanApprove,reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月贷后管理查询次数'] = qip.getLastMonthQueryTimes(df, 24, consts.loanAfterMgr, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月贷款审批审批次数'] = qip.getLastMonthQueryTimes(df, 24, consts.loanApprove, reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月信用卡审批查询次数'] = qip.getLastMonthQueryTimes(df, 24, consts.creditCard,reportTime) queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月担保资格审查查询次数'] = qip.getLastMonthQueryTimes(df, 24, consts.insuranceAprove,reportTime) #解析住房公积金 def parseHousingFundRcd(df): if not df.empty: lastHousingFundRcdDf = df.sort_values(by=["信息更新日期"] , ascending=(False)).reset_index(drop=True) lastHousingFundRcdDf = lastHousingFundRcdDf[0:1]#最新 row1 = lastHousingFundRcdDf.loc[0,:].dropna().reset_index(drop=True) housingFundRcdDf.loc[housingFundRcdIndex, '参缴地'] =row1[1] housingFundRcdDf.loc[housingFundRcdIndex, '参缴日期'] =row1[2] housingFundRcdDf.loc[housingFundRcdIndex, '初缴月份'] =row1[3]#初缴日期 housingFundRcdDf.loc[housingFundRcdIndex, '缴至月份'] =row1[4] housingFundRcdDf.loc[housingFundRcdIndex, '缴费状态'] =row1[5] housingFundRcdDf.loc[housingFundRcdIndex, '月缴存额'] =row1[6] housingFundRcdDf.loc[housingFundRcdIndex, '个人存缴比例'] =row1[7] housingFundRcdDf.loc[housingFundRcdIndex, '单位存缴比例'] =row1[8] housingFundRcdDf.loc[housingFundRcdIndex, '缴费单位'] =row1[9]#扣缴单位 housingFundRcdDf.loc[housingFundRcdIndex, '信息更新日期'] =row1[10] reportTime = queryInfo["reportTime"]; lastDateStr = utils.getLastMonthDate(reportTime,12) avgHousingFundDf = df[df['缴至月份']>=lastDateStr] housingFundRcdDf.loc[housingFundRcdIndex, '最近1年公积金平均值'] = round(np.mean(avgHousingFundDf['月缴存额']),2) lastDateStr = utils.getLastMonthDate(reportTime, 12*3) avgHousingFundDf = df[df['缴至月份'] >= lastDateStr] housingFundRcdDf.loc[housingFundRcdIndex, '最近3年公积金平均值']= round(np.mean(avgHousingFundDf['月缴存额']),2) #解析贷款还款记录指标 def parseLoanMergeAndPayRecordDf(df,payRcdDf): if not df.empty and not payRcdDf.empty: #正常 normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')] overduePayRcdDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)] overduePayRcdDf = utils.replacePayRcdStatus(overduePayRcdDf) #临时保存,不用过滤还款状态为0的 payRcdMaxOverdueDf = overduePayRcdDf; overduePayRcdDf = overduePayRcdDf[overduePayRcdDf['还款状态']>0] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期账户数'] = overduePayRcdDf['账户编号'].unique().size loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期账户数占比'] = round(loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期账户数']/df.index.size,2) #存在逾期的贷款账户 非结清的过滤出逾期的账户号 overdueLoanDf = normalDf[normalDf['账户编号'].isin(overduePayRcdDf['账户编号'].values)] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期机构数'] = overdueLoanDf['管理机构'].unique().size loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期机构数占比'] = round(loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期机构数'] / df['管理机构'].unique().size,2) #还款记录按日期排序最近3笔的最大逾期期数 loanAccountInfoDf.loc[loanAccountInfoIndex, '近1月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf,1); loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 3); loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 6); loanAccountInfoDf.loc[loanAccountInfoIndex, '近9月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 9); loanAccountInfoDf.loc[loanAccountInfoIndex, '近24月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 24); reportTime = queryInfo["reportTime"] loanAccountInfoDf.loc[loanAccountInfoIndex, '近24月贷款最大逾期距离现在的月数'] = prp.getPayRcdMaxOverdueNumMonth(payRcdMaxOverdueDf,normalDf,reportTime, 24); payStatus= ["G","D","C","N","M","1","2","3","4","5","6","7"] # 贷款24期还款记录次数 剔除结清 转出 呆账 payRcdTimesDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)] #从“贷款信息”中提取，剔除“账户状态”为结清、转出、呆账、呆帐后，各账户的还款次数统计“24个月（账户）还款状态”包含"G","D","C","N","M"及数字的个数，MAX（各账户的还款次数） payRcdTimesDf = payRcdTimesDf[payRcdTimesDf['还款状态'].isin(payStatus)] payRcdTimes = payRcdTimesDf.groupby(['账户编号'])['还款状态'].count() #payRcdDf[(payRcdDf['还款状态']!='') & (payRcdDf['账户编号']==1)].index.size loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款24期还款记录次数'] = np.max(payRcdTimes) #解析信贷交易明细-特殊交易 def parseSpecialTrade(df): if not df.empty: creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '当前用户发生特殊交易的严重程度'] = np.max(df['严重程度'])#加工的指标 maxChangeMonthIndex = np.argmax(np.abs(df['变更月数'])) meanMonthValue = np.mean(np.abs(df['变更月数'])) row0 = df.loc[maxChangeMonthIndex, :] settleDf = df[(df['特殊交易类型']=='提前结清') | (df['特殊交易类型']=='提前还款')] debtDf = df[(df['特殊交易类型'] == '以资抵债')] creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户发生特殊交易变更月数的最大差值'] = row0[3] creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户发生特殊交易变更月数的平均差值'] = round(meanMonthValue,2) creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户特殊交易涉及的发生金额的最大值'] = np.max(df['发生金额']) creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户特殊交易涉及的发生金额的平均值'] = round(np.mean(df['发生金额']),2) creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户所有帐户发生提前还款交易的次数统计'] = settleDf.index.size creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户所有帐户发生不良特殊交易的次数统计'] = debtDf.index.size; #信贷交易明细-非循环贷账户 def parseLoanAccountInfo(df): if not df.empty: loanAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数']) normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')].reset_index(drop=True) normalDf = normalDf[0:loanAccountNum]#根据非循环贷账户数进行计算进行截取 creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '本月应还款（合计）'] = np.sum(normalDf['本月应还款']) creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '本月实还款（合计）'] = np.sum(normalDf['本月实还款']) creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '最近一次还款日期'] = np.max(normalDf['最近一次还款日期']) creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '当前一共逾期期数'] = np.sum(normalDf['当前逾期期数']) creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '当前一共逾期总额'] = np.sum(normalDf['当前逾期总额']) creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期31-60天未还本金（合计）'] = np.sum(normalDf['逾期31-60天未还本金']) creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期61-90天未还本金（合计）'] = np.sum(normalDf['逾期61-90天未还本金']) creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期91-180天未还本金（合计）'] = np.sum(normalDf['逾期91-180天未还本金']) creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期180天以上未还本金（合计）']= np.sum(normalDf['逾期180天以上未还本金']) #信贷交易明细-循环额度分账户 def parseCycleCreditAccountInfo(df): if not df.empty: normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')].reset_index(drop=True) loanAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数']) cycleCreditAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户账户数']) normalDf = normalDf[loanAccountNum:(loanAccountNum + cycleCreditAccountNum)] if not normalDf.empty: creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '本月应还款（合计）'] = np.sum(normalDf['本月应还款']) creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '本月实还款（合计）'] = np.sum(normalDf['本月实还款']) creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '最近一次还款日期'] = np.max(normalDf['最近一次还款日期']) creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '当前一共逾期期数'] = np.sum(normalDf['当前逾期期数']) creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '当前一共逾期总额'] = np.sum(normalDf['当前逾期总额']) creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期31-60天未还本金（合计）'] = np.sum(normalDf['逾期31-60天未还本金']) creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期61-90天未还本金（合计）'] = np.sum(normalDf['逾期61-90天未还本金']) creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期91-180天未还本金（合计）'] = np.sum(normalDf['逾期91-180天未还本金']) creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期180天以上未还本金（合计）']= np.sum(normalDf['逾期180天以上未还本金']) #信贷交易明细-循环贷账户 def parseCycleLoanAccountInfo(df): if not df.empty: normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')] loanAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数']) cycleCreditAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户账户数']) cycleAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户账户数']) normalDf = normalDf[(loanAccountNum+cycleCreditAccountNum):normalDf.index.size] if not normalDf.empty: creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '本月应还款（合计）'] = np.sum(normalDf['本月应还款']) creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '本月实还款（合计）'] = np.sum(normalDf['本月实还款']) creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '最近一次还款日期'] = np.max(normalDf['最近一次还款日期']) creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '当前一共逾期期数'] = np.sum(normalDf['当前逾期期数']) creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '当前一共逾期总额'] = np.sum(normalDf['当前逾期总额']) creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期31-60天未还本金（合计）'] = np.sum(normalDf['逾期31-60天未还本金']) creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期61-90天未还本金（合计）'] = np.sum(normalDf['逾期61-90天未还本金']) creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期91-180天未还本金（合计）'] = np.sum(normalDf['逾期91-180天未还本金']) creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期180天以上未还本金（合计）']= np.sum(normalDf['逾期180天以上未还本金']) #解析贷款账户信息指标 def parseLoanMergeDf(df): if not df.empty: sortDf = df.sort_values(by=["账户关闭日期","借款金额（本金)"] , ascending=(False,False)) sortDf = sortDf[sortDf['账户状态'] == '结清']; sortDf = sortDf.reset_index(drop=True) if not sortDf.empty: row0 = sortDf.loc[0, :] loanAccountInfo["lastSettleLoanAmt"] = row0['借款金额（本金)'] loanAccountInfoDf.loc[loanAccountInfoIndex, '最近一笔结清贷款的贷款金额'] = row0['借款金额（本金)'] openDate = dfParser.formatDate(row0['开立日期']) loanAccountInfoDf.loc[loanAccountInfoIndex, '最近一笔结清贷款的发放距今月数'] = utils.difMonthReportTime(openDate,queryInfo["reportTime"]) settleDate = dfParser.formatDate(row0['账户关闭日期']) loanAccountInfoDf.loc[loanAccountInfoIndex, '最近一笔结清贷款的结清距今月数'] = utils.difMonthReportTime(settleDate,queryInfo["reportTime"]) loanAccountInfoDf.loc[loanAccountInfoIndex, '历史贷款总法人机构数'] = df['管理机构'].unique().size loanAccountInfoDf.loc[loanAccountInfoIndex, '当前同时在用的贷款机构数'] = df[df['余额（本金)']>0]['管理机构'].unique().size statusDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出')] bankDf = statusDf[statusDf['管理机构'].str.contains('银行')] #没有记录 if statusDf.index.size==0: isNotBankCust = -1 else: if bankDf.index.size >0:#有一条以上不为结清，请包含银行 isNotBankCust = 1; else: isNotBankCust = 0; loanAccountInfoDf.loc[loanAccountInfoIndex, '是否有非银行贷款客户'] = isNotBankCust #最严重的五级分类 # fiveType = "" # for fiveTypeTmp in consts.fiveType: # fiveTypeDf = statusDf[statusDf['五级分类']==fiveTypeTmp]; # if not fiveTypeDf.empty: # fiveType = fiveTypeTmp; # break; # loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款五级分类'] = fiveType #当前贷款LTV # 从“贷款信息”中提取，剔除“账户状态”为结清及转出，并剔除“账户状态”为呆账且本金余额 = 0 # 的记录后，SUM(本金余额) / SUM(贷款本金) # 如本金余额为空和贷款本金为0或为空，则当条记录不计算 loanLtvDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['借款金额（本金)']>0) & (df['余额（本金)']!='--')] badSetDf = loanLtvDf[~((loanLtvDf['账户状态'] == '呆账') & (loanLtvDf['余额（本金)']==0))] balanceSum = np.sum(badSetDf['余额（本金)'].astype('int')) loanAmtSum = np.sum(badSetDf['借款金额（本金)'].astype('int')) if(loanAmtSum !=0): loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款LTV'] = round(np.divide(balanceSum,loanAmtSum),2) loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最高LTV'] = round(np.max(np.divide(badSetDf['余额（本金)'].astype('int'), badSetDf['借款金额（本金)'].astype('int'))),2) loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最低LTV'] = round(np.min(np.divide(badSetDf['余额（本金)'].astype('int'), badSetDf['借款金额（本金)'].astype('int'))), 2) loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款平均LTV'] = round(np.mean(np.divide(badSetDf['余额（本金)'].astype('int'), badSetDf['借款金额（本金)'].astype('int'))), 2) #['个人住房商业贷款','个人商用房（含商住两用）贷款','个人住房公积金贷款','房'], houseLtvList = consts.houseLtvList; # houseLtvDf = badSetDf[badSetDf['业务种类'].isin(houseLtvList)] # if not houseLtvDf.empty: # loanAccountInfoDf.loc[loanAccountInfoIndex, '当前房贷LTV'] = round(np.divide(np.sum(houseLtvDf['余额（本金)'].astype('int')),np.sum(houseLtvDf['借款金额（本金)'].astype('int'))), 2) #['个人住房贷款','个人商用房（包括商住两用）贷款'] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前房贷LTV'] = lip.getCurLtv(badSetDf, houseLtvList) loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款机构数量'] = loanLtvDf['管理机构'].unique().size cardLtvList = ['个人汽车消费贷款','车'] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前车贷LTV'] = lip.getCurLtv(badSetDf, cardLtvList) operateLtvList = ['个人经营性贷款'] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前经营贷LTV'] = lip.getCurLtv(badSetDf, operateLtvList) consumeLtvList = ['其他个人消费贷款'] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前消费贷LTV'] = lip.getCurLtv(badSetDf, consumeLtvList) bankLtvList = ['商业银行','外资银行','村镇银行','住房储蓄银行','财务公司'] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前银行贷LTV'] = lip.getCurBankLtv(badSetDf, bankLtvList) bankLtvList = ['消费金融公司','汽车金融公司','信托公司']# TODO loanAccountInfoDf.loc[loanAccountInfoIndex, '当前消金贷LTV'] = lip.getCurBankLtv(badSetDf, bankLtvList) smallLoanLtvList = ['小额信贷公司'] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前小贷LTV'] = lip.getCurBankLtv(badSetDf, smallLoanLtvList) #当前贷款最大逾期期数 # 从“贷款信息”中提取，剔除“账户状态”为结清、转出、呆账、呆帐后，MAX(每笔贷款的当前逾期期数) loanOverdueLtvDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')] if not loanOverdueLtvDf.empty: loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最大逾期期数'] = np.max(loanOverdueLtvDf['当前逾期期数']) loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最大逾期金额'] = np.max(loanOverdueLtvDf['当前逾期总额']) loanOverdueLtvDf=loanOverdueLtvDf.reset_index(drop=True) maxOverdueIndex = np.argmax(loanOverdueLtvDf['当前逾期期数']) loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最大逾期期数对应的最大逾期金额'] = loanOverdueLtvDf.loc[maxOverdueIndex,:]['当前逾期总额'] loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月开户最高贷款本金'] = lip.getLastLoanAmtMax(df,queryInfo["reportTime"],3)#贷款指标加工单独放到一个文件里 loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月开户最低贷款本金'] = lip.getLastLoanAmtMin(df, queryInfo["reportTime"], 3) loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月开户平均贷款本金'] = lip.getLastLoanAmtAvg(df, queryInfo["reportTime"], 3) loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月开户最高贷款本金'] = lip.getLastLoanAmtMax(df, queryInfo["reportTime"], 6) loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月开户最低贷款本金'] = lip.getLastLoanAmtMin(df, queryInfo["reportTime"], 6) loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月开户平均贷款本金'] = lip.getLastLoanAmtAvg(df, queryInfo["reportTime"], 6) loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月开户最高贷款本金'] = lip.getLastLoanAmtMax(df, queryInfo["reportTime"], 12) loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月开户最低贷款本金'] = lip.getLastLoanAmtMin(df, queryInfo["reportTime"], 12) loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月开户平均贷款本金'] = lip.getLastLoanAmtAvg(df, queryInfo["reportTime"], 12) lastLoanDf = loanOverdueLtvDf; if not lastLoanDf.empty: loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款最近一次还款日期距今时长'] = lip.getLastPayDateMinDays(lastLoanDf,queryInfo["reportTime"]) normalDf = df[(df['账户状态'] == '正常') & (df['当前逾期期数'] == 0)] #未结清贷款总账户数：账户状态不等于结清和转出的记录数 notSettleDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出')] if not notSettleDf.empty: loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户数'] = normalDf.index.size loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户数占比'] = round(normalDf.index.size/notSettleDf.index.size,2) #当前未结清贷款余额总和 # ltvDf = tmpDf[tmpDf['业务种类'].isin(bizTypeList)] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清贷款余额总和'] = np.sum(notSettleDf['余额（本金)']) loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清贷款余额总和'] = np.sum(notSettleDf['余额（本金)']) # 当前未结清住房贷款余额总和 houseDf = notSettleDf[notSettleDf['业务种类'].isin(houseLtvList)] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清住房贷款余额总和'] = np.sum(houseDf['余额（本金)']) # 当前未结清汽车贷款余额总和 cardDf = notSettleDf[notSettleDf['业务种类'].isin(cardLtvList)] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清汽车贷款余额总和'] = np.sum(cardDf['余额（本金)']) # 当前未结清个人经营性贷款余额总和 operateLtvDf = notSettleDf[notSettleDf['业务种类'].isin(operateLtvList)] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清个人经营性贷款余额总和'] = np.sum(operateLtvDf['余额（本金)']) # 当前平均每月贷款余额总和 loanAccountInfoDf.loc[loanAccountInfoIndex, '当前平均每月贷款余额总和'] = round(np.sum(normalDf['余额（本金)'])/12,2) #当前正常贷款账户余额 loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户余额'] = np.sum(normalDf['余额（本金)']) # "从“贷款信息”中提取，剔除结清、转出，当前正常贷款账户余额/未结清贷款总余额（本金余额加总） loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户余额占总余额比'] = round(np.sum(normalDf['余额（本金)'])/np.sum(notSettleDf['余额（本金)'])) settleDf = df[(df['账户状态'] == '结清')] loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常结清贷款账户数'] = settleDf.index.size loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常结清贷款账户数占比'] = round(settleDf.index.size/df.index.size,2) #贷款24期还款记录次数 TODO # 最近3个月个人消费贷款发放额度 loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款本月实还款金额'] = np.sum(loanOverdueLtvDf['本月应还款']) loanAccountInfoDf.loc[loanAccountInfoIndex, '最近3个月个人消费贷款发放额度'] = lip.getLastPerConsumeAmt(df,3,queryInfo["reportTime"]) loanAccountInfoDf.loc[loanAccountInfoIndex, '最近6个月个人消费贷款发放额度'] = lip.getLastPerConsumeAmt(df, 6,queryInfo["reportTime"]) loanAccountInfoDf.loc[loanAccountInfoIndex, '最近12个月个人消费贷款发放额度'] = lip.getLastPerConsumeAmt(df, 12,queryInfo["reportTime"]) #未结清贷款平均剩余还款期数 payPieDf = notSettleDf[notSettleDf['还款期数']!='--'] if payPieDf.index.size!=0: loanAccountInfoDf.loc[loanAccountInfoIndex, '未结清贷款平均剩余还款期数'] = round(np.sum(payPieDf['剩余还款期数'])/payPieDf.index.size,2) # 当前贷款本月应还金额总和 loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款本月应还金额总和'] = np.sum(notSettleDf['本月应还款']) # 当前贷款本月实还金额总额 loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款本月实还金额总额'] = np.sum(notSettleDf['本月实还款']) #解析贷记卡账户信息指标 def parseCreditCardMergeDf(df): if not df.empty: # 历史信用卡总法人机构数 # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex,'历史信用卡总法人机构数'] = df['发卡机构'].unique().size # creditCardUseDf = df[df['已用额度']>0]; # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex,'当前同时在用的信用卡机构数'] = creditCardUseDf['发卡机构'].unique().size #统一排除 creditDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '未激活') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')] totalAmtDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '未激活') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')] #大额专项分期额度（合计） # 已用分期金额(合计） creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '大额专项分期额度（合计）'] = np.sum(creditDf['大额专项分期额度']) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '已用分期金额(合计）'] = np.sum(creditDf['已用分期金额']) # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex,'贷记卡账户当前总额度'] = cip.getMaxCreditAmt(creditDf) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近新发放的3张贷记卡平均额度'] = cip.getAvgCreditAmt(creditDf) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡额度使用率超过90%的机构数占比'] = cip.getUseRate(creditDf,df,0.9) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡额度使用率超过100%的机构数占比'] = cip.getUseRate(creditDf, totalAmtDf, 1) # 从“贷记卡信息”中提取，计算授信额度时剔除销户，计算已用额度时剔除呆账、呆帐、销户后，SUM(各账户已用额度) / SUM(各账户授信额度) useCreditDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')] totalCreditDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '销户')] totalCreditAmt = np.sum(totalCreditDf['账户授信额度']) if totalCreditAmt != 0:#授信额度不能为0 creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡账户当前总额度使用率'] = round(np.sum(useCreditDf['已用额度'])/np.sum(totalCreditDf['账户授信额度']),2) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡账户最高使用额度总的使用率'] = round(np.sum(useCreditDf['最大使用额']) / np.sum(totalCreditDf['账户授信额度']), 2) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡账户近6月平均额度总的使用率'] = round(np.sum(useCreditDf['最近6个月平均使用额度']) / np.sum(totalCreditDf['账户授信额度']), 2) # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡最大逾期期数'] = np.max(creditDf['当前逾期期数'])#用于计算 creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡最大逾期金额'] = np.max(creditDf['当前逾期总额']) if not creditDf.empty: creditDf = creditDf.reset_index(drop=True) maxOverdueIndex = np.argmax(creditDf['当前逾期期数']) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡最大逾期期数对应的最大逾期金额'] = creditDf.loc[maxOverdueIndex,:]['当前逾期总额'] creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月开卡最高额度'] = cip.getLastMonthMaxCreditAmt(df,queryInfo["reportTime"],3) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月开卡最低额度'] = cip.getLastMonthMinCreditAmt(df, queryInfo["reportTime"], 3) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月开卡平均额度'] = cip.getLastMonthAvgCreditAmt(df, queryInfo["reportTime"], 3) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月开卡最高额度'] = cip.getLastMonthMaxCreditAmt(df, queryInfo["reportTime"], 6) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月开卡最低额度'] = cip.getLastMonthMinCreditAmt(df, queryInfo["reportTime"], 6) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月开卡平均额度'] = cip.getLastMonthAvgCreditAmt(df, queryInfo["reportTime"], 6) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月开卡最高额度'] = cip.getLastMonthMaxCreditAmt(df, queryInfo["reportTime"], 12) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月开卡最低额度'] = cip.getLastMonthMinCreditAmt(df, queryInfo["reportTime"], 12) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月开卡平均额度'] = cip.getLastMonthAvgCreditAmt(df, queryInfo["reportTime"], 12) if not creditDf.empty: creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡最近一次还款日期距今时长'] = cip.getLastPayDateMinDays(creditDf,queryInfo["reportTime"]) paySo = np.sum(creditDf['本月应还款']) if(paySo)!=0: creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡还款比例'] = round(np.sum(creditDf['本月实还款'])/np.sum(creditDf['本月应还款']),2) creditDfTmp = creditDf[creditDf['本月应还款']>0] creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡最高还款比例'] = round(np.max(np.divide(creditDfTmp['本月实还款'] , creditDfTmp['本月应还款'])), 2) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡最低还款比例'] = round(np.min(np.divide(creditDfTmp['本月实还款'] , creditDfTmp['本月应还款'])), 2) normalDf = df[(df['币种'] == '人民币元') & (df['账户状态'] == '正常') & (df['当前逾期期数']==0)]; notCloseDf = df[(df['账户状态'] != '销户')] creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡账户数'] = normalDf.index.size if not notCloseDf.empty and not normalDf.empty: creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡账户数占比'] = round(normalDf.index.size/notCloseDf.index.size,2) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡已用额度'] = np.sum(normalDf['已用额度']) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数'] = normalDf[normalDf['已用额度']>0].index.size if not creditDf.empty: creditUseAmt = np.sum(creditDf['已用额度']) if creditUseAmt!=0: creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡账户余额占总余额比'] = round(np.sum(normalDf['已用额度']) / np.sum(creditDf['已用额度']), 2) if notCloseDf.empty: creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数占比'] = -99 else: creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数占比'] = \ round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数']/notCloseDf.index.size,3) #当前正常贷记卡账户余额占总余额比 creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡本月实还金额总和'] = np.sum(creditDf['本月实还款']) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡本月应还金额总和'] = np.sum(creditDf['本月应还款']) maxAmtDf = df[(df['币种'] == '人民币元')] if not maxAmtDf.empty: maxAmtDf = maxAmtDf.reset_index(drop=True) maxAmtIndex = np.argmax(maxAmtDf['账户授信额度']) maxOpenDate = maxAmtDf.loc[maxAmtIndex,:]['开立日期']; creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '额度最高的人民币贷记卡开卡距今月份数'] = utils.difMonthReportTime(maxOpenDate,queryInfo["reportTime"]); # 名下贷记卡数量-状态正常 creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态正常'] = df[(df['账户状态'] != '销户')].index.size # 名下贷记卡数量-状态未激活 creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态未激活'] = df[(df['账户状态'] == '未激活')].index.size # 名下贷记卡数量-状态异常--异常包含（2－冻结,3－止付,5－呆帐，10-其他） abnormalList = ['冻结','止付','呆帐','其他'] creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态异常'] = df[(df['账户状态'].isin(abnormalList))].index.size # 名下贷记卡比例-状态正常 creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡比例-状态正常'] = round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态正常'] / df.index.size,2) # 名下贷记卡比例-状态未激活 creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡比例-状态未激活'] =round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态未激活'] / df.index.size,2) # 名下贷记卡比例-状态异常 creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡比例-状态异常'] = round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态异常'] / df.index.size,2) #解析准贷记卡账户信息指标 def parseCreditCardMergeDfZ(df,payRcd): if not df.empty: overdueCreditCardRcdDf = payRcd[payRcd['账户编号'].isin(df['账户编号'].values)]; overdueCreditCardRcdDf = utils.replacePayRcdStatusOverdue(overdueCreditCardRcdDf) creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '本月应还款（合计）'] = np.nansum(df['透支余额']) creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '本月实还款（合计）'] = np.nansum(df['本月实还款']) creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '最近一次还款日期'] = np.max(df['最近一次还款日期']) creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前一共透支期数'] = cip.getCurOverdueNum(overdueCreditCardRcdDf); creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前一共透支总额'] = np.nansum(df['透支余额']) creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '透支180天以上未支付余额（合计）'] = np.nansum(df['透支180天以上未付余额']) creditDf = df[(df['账户状态'] != '未激活') & (df['账户状态'] != '销户')] if not creditDf.empty: totalAmt = np.nansum(creditDf['账户授信额度']) creditAmt = np.nansum(creditDf['透支余额']) if totalAmt !=0: #从“贷记卡信息”中提取，剔除未激活、销户后，所有账户透支金额/所有账户账户授信额度。 creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '全部准贷记卡账户当前总额度使用率']=round(creditAmt/totalAmt,2) #从“贷记卡信息”中提取，剔除未激活、销户后，MAX（单账户最高透支金额/单账户授信额度） creditMaxDf = creditDf[creditDf['账户授信额度']>0] if not creditMaxDf.empty: creditMaxDf = creditMaxDf.fillna(0.0) creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '准贷记卡账户最高使用额度总的使用率'] = round(np.max(np.divide(creditMaxDf['最大透支余额'],creditMaxDf['账户授信额度'])),2) creditMaxDf = creditDf[creditDf['最大透支余额'] > 0] if not creditMaxDf.empty: creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡最大透支金额'] = np.max(creditMaxDf['最大透支余额']) #从“贷记卡信息”中提取，剔除未激活、销户后，当前透支准贷记卡账户数/总准贷记卡账户数，透支账户判断：透支余额不为0的账户 creditDfTmp = creditDf[creditDf['透支余额']>0] creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡透支账户数占比'] = round(creditDfTmp.index.size / creditDf.index.size,2) creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡本月应还金额总和'] = np.nansum(df['透支余额']) creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡本月实还金额总和'] = np.nansum(df['本月实还款']) #解析使用率 TODO 使用汇总计算还是使用明细计算 def parseUseRate(): # useRateDf.loc[useRateIndex, '贷记卡账户使用率（已用额度/授信总额）'] # 从“信贷交易授信及负债信息概要”中“非循环贷账户信息汇总”、“循环额度下分账户信息汇总”、“循环贷账户信息汇总”、“贷记卡账户信息汇总”和“准贷记卡账户信息汇总”里提取，SUM( # 所有“余额”、“已用额度”和“透支余额”） / SUM(所有“授信总额”和“授信额度”) loanUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户余额'] cycleCreditUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户余额'] cycleUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户余额'] creditUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡已用额度'] creditAmtUseZ = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡已用额度'] loanTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户授信总额'] cycleCreditTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户授信总额'] cycleTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户授信总额'] creditTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡授信总额'] creditAmtTotalZ = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡授信总额'] # if str(loanUseAmt)=="nan": # loanUseAmt = 0; # if str(cycleCreditUseAmt) == "nan": # loanUseAmt = 0; # if str(cycleCreditUseAmt) == "nan": # loanUseAmt = 0; useAmt = loanUseAmt+cycleCreditUseAmt+cycleUseAmt+creditUseAmt+creditAmtUseZ totalAmt = loanTotalAmt+cycleCreditTotalAmt+cycleTotalAmt+creditTotalAmt+creditAmtTotalZ if totalAmt !=0: useRateDf.loc[useRateIndex, '全账户使用率（已用额度/授信总额）'] = round(useAmt / totalAmt,2) if loanTotalAmt!=0: useRateDf.loc[useRateIndex, '非循环贷账户使用率（已用额度/授信总额）'] = round(loanUseAmt / loanTotalAmt,2) if cycleCreditTotalAmt !=0: useRateDf.loc[useRateIndex, '循环额度下分账户使用率（已用额度/授信总额）'] = round(cycleCreditTotalAmt / cycleCreditTotalAmt,2) if cycleTotalAmt !=0: useRateDf.loc[useRateIndex, '循环贷账户使用率（已用额度/授信总额）'] = round(cycleUseAmt / cycleTotalAmt,2) if creditTotalAmt !=0: useRateDf.loc[useRateIndex, '贷记卡账户使用率（已用额度/授信总额）'] = round(creditUseAmt / creditTotalAmt,2) if creditAmtTotalZ !=0: useRateDf.loc[useRateIndex, '准贷记卡账户使用率（已用额度/授信总额）'] = round(creditAmtUseZ / creditAmtTotalZ,2) #解析开户数 def parseOpenAccount(loanDf,creditCardDf,creditCardDfZ,recoveryInfoMergeDf,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ): reportTime = queryInfo["reportTime"]; openAccountDf.loc[openAccountIndex, '近3个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,3)+cip.getOpenAccount(creditCardDf,reportTime,3)+cip.getOpenAccount(creditCardDfZ,reportTime,3) openAccountDf.loc[openAccountIndex, '近6个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,6)+cip.getOpenAccount(creditCardDf,reportTime,6)+cip.getOpenAccount(creditCardDfZ,reportTime,6) openAccountDf.loc[openAccountIndex, '近9个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,9)+cip.getOpenAccount(creditCardDf,reportTime,9)+cip.getOpenAccount(creditCardDfZ,reportTime,9) openAccountDf.loc[openAccountIndex, '近12个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,12)+cip.getOpenAccount(creditCardDf,reportTime,12)+cip.getOpenAccount(creditCardDfZ,reportTime,12) openAccountDf.loc[openAccountIndex, '近24个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,24)+cip.getOpenAccount(creditCardDf,reportTime,24)+cip.getOpenAccount(creditCardDfZ,reportTime,24) openAccountDf.loc[openAccountIndex, '近3个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,3,consts.bankList) openAccountDf.loc[openAccountIndex, '近6个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,6,consts.bankList) openAccountDf.loc[openAccountIndex, '近9个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,9,consts.bankList) openAccountDf.loc[openAccountIndex, '近12个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,12,consts.bankList) openAccountDf.loc[openAccountIndex, '近24个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,24,consts.bankList) openAccountDf.loc[openAccountIndex, '近3个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,3,"") openAccountDf.loc[openAccountIndex, '近6个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,6,"") openAccountDf.loc[openAccountIndex, '近9个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,9,"") openAccountDf.loc[openAccountIndex, '近12个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,12,"") openAccountDf.loc[openAccountIndex, '近24个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,24,"") openAccountDf.loc[openAccountIndex, '近3个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,3) openAccountDf.loc[openAccountIndex, '近6个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,6) openAccountDf.loc[openAccountIndex, '近9个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,9) openAccountDf.loc[openAccountIndex, '近12个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,12) openAccountDf.loc[openAccountIndex, '近24个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,24) openAccountDf.loc[openAccountIndex, '近3个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,3) openAccountDf.loc[openAccountIndex, '近6个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,6) openAccountDf.loc[openAccountIndex, '近9个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,9) openAccountDf.loc[openAccountIndex, '近12个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,12) openAccountDf.loc[openAccountIndex, '近24个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,24) #从“信贷交易信息明细”中“非循环贷账户”、“循环额度下分账户”、“循环贷账户”、“贷记卡账户”和“准贷记卡账户”里提取，5年里账户还款状态出现“1、2、3、4、5、6、7、D、Z、G、B”的账户数/所有账户数 overdueLoanPayRcdDf = loanPayRecordMergeDf[loanPayRecordMergeDf['账户编号'].isin(loanDf['账户编号'].values)] overdueLoanPayRcdDf = utils.replacePayRcdStatusOverdue(overdueLoanPayRcdDf) overdueLoanPayRcdDf = overdueLoanPayRcdDf[overdueLoanPayRcdDf['还款状态'] > 0] overdueCreditPayRcdDf = creditCardPayRecordMergeDf[creditCardPayRecordMergeDf['账户编号'].isin(creditCardDf['账户编号'].values)] overdueCreditPayRcdDf = utils.replacePayRcdStatusOverdue(overdueCreditPayRcdDf) overdueCreditPayRcdDf = overdueCreditPayRcdDf[overdueCreditPayRcdDf['还款状态'] > 0] overdueCreditPayRcdDfZ = creditCardPayRecordMergeDfZ[creditCardPayRecordMergeDfZ['账户编号'].isin(creditCardDfZ['账户编号'].values)] overdueCreditPayRcdDfZ = utils.replacePayRcdStatusOverdue(overdueCreditPayRcdDfZ) overdueCreditPayRcdDfZ = overdueCreditPayRcdDfZ[overdueCreditPayRcdDfZ['还款状态'] > 0] loanAccountNum = loanPayRecordMergeDf['账户编号'].unique().size creditAccountNum = creditCardPayRecordMergeDf['账户编号'].unique().size creditAccountNumZ = creditCardPayRecordMergeDfZ['账户编号'].unique().size overdueLoanNum = overdueLoanPayRcdDf['账户编号'].unique().size overdueCreditNum = overdueCreditPayRcdDf['账户编号'].unique().size overdueCreditNumZ = overdueCreditPayRcdDfZ['账户编号'].unique().size openAccountDf.loc[openAccountIndex, '有过逾期记录的账户/全账户数'] = round((overdueLoanNum+overdueCreditNum+overdueCreditNumZ)/(loanAccountNum+creditAccountNum+creditAccountNumZ),2) otherPerLoanDf = loanDf[loanDf['业务种类'].isin(consts.otherPerLoan)] otherPerLoanNum = otherPerLoanDf.index.size; overdueOtherPerLoanNum = otherPerLoanDf[otherPerLoanDf['账户编号'].isin(overdueLoanPayRcdDf['账户编号'].values)].index.size; if otherPerLoanNum!=0: openAccountDf.loc[openAccountIndex, '有过逾期记录的消费金融类账户/全消费金融类账户数'] = round(overdueOtherPerLoanNum/otherPerLoanNum,2) if loanAccountNum!=0: openAccountDf.loc[openAccountIndex, '有过逾期记录的贷款账户/全贷款账户数'] = round(overdueLoanNum/loanAccountNum,2) if creditAccountNum!=0: openAccountDf.loc[openAccountIndex, '有过逾期记录的贷记卡账户/全贷记卡账户数'] = round(overdueCreditNum/creditAccountNum,2) if creditAccountNumZ!=0: openAccountDf.loc[openAccountIndex, '有过透支记录的准贷记卡账户/全准贷记卡账户数']= round(overdueCreditNumZ/creditAccountNumZ,2) #解析24期还款状态指标 def parsePayRcdStatus(loanMergeDf, creditCardMergeDf, creditCardMergeDfZ,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ): #creditCardPayRecordMergeDf # 去掉外币 creditCardMergeDf = creditCardMergeDf[creditCardMergeDf['币种']=='人民币元'] creditCardPayRecordMergeDf = creditCardPayRecordMergeDf[creditCardPayRecordMergeDf['账户编号'].isin(creditCardMergeDf['账户编号'].values)] reportTime = queryInfo["reportTime"]; payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近3月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,3) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近6月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,6) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,12) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,24) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近6月逾期期数大于或等于“2”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,6) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或等于“2”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,12) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“2”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,24) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近6月逾期期数大于或等于“3”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,6) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或等于“3”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,12) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“3”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,24) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或大等于“4”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,12) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“4”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,24) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近3月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,3) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近6月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,6) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,12) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,24) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近6月逾期期数大于或等于“2”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,6) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“2”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,12) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“2”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,24) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近6月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,6) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,12) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,24) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,12) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,24) payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近6月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,6) payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近12月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,12) payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近24月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,24) payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近6月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,6) payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近12月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,12) payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近24月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,24) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近3月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,3)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,3)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,3) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近6月逾期期数大于或等于“1”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,6)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,6)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,6) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“1”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,12)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,12) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“1”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,24)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,24) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近6月逾期期数大于或等于“2”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,6)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,6)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,2,6) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“2”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,12)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,2,12) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“2”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,24)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,2,24) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近6月逾期期数大于或等于“3”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,6)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,6)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,6) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“3”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,12)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,12) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“3”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,24)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,24) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“4”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,12)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,12) payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“4”的次数'] = \ prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,24)\ +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,24) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24个月是否出现"G"'] = prp.isExistsInd(loanPayRecordMergeDf,reportTime,"G",24) payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24个月是否出现"G"'] = prp.isExistsInd(creditCardPayRecordMergeDf,reportTime,"G",24) payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近24个月是否出现"G"'] = prp.isExistsInd(creditCardPayRecordMergeDfZ,reportTime,"G",24) payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24个月是否出现"Z"'] = prp.isExistsInd(loanPayRecordMergeDf,reportTime,"Z",24) payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷款账户过去24个月存在逾期的账户数目'] = prp.getLoanOverdueCount(loanPayRecordMergeDf,reportTime,24) payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷款账户过去24个月状态正常账户数目'] = prp.getLoanNormalCount(loanPayRecordMergeDf,reportTime,24) payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷记卡账户过去24个月存在逾期的账户数目'] = prp.getLoanOverdueCount(creditCardPayRecordMergeDf,reportTime,24) payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷记卡账户过去24个月状态正常的账户数目'] = prp.getLoanNormalCount(creditCardPayRecordMergeDf,reportTime,24) payRcdStatusDf.loc[payRcdStatusIndex, '用户所有准贷记卡账户过去24个月存在逾期的账户数目'] = prp.getLoanOverdueCount(creditCardPayRecordMergeDfZ,reportTime,24) payRcdStatusDf.loc[payRcdStatusIndex, '用户所有准贷记卡账户过去24个月状态正常的账户数目'] = prp.getLoanNormalCount(creditCardPayRecordMergeDfZ,reportTime,24) payRcdStatusDf.loc[payRcdStatusIndex, '用户过去3个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,3) payRcdStatusDf.loc[payRcdStatusIndex, '用户过去6个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,6) payRcdStatusDf.loc[payRcdStatusIndex, '用户过去12个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,12) payRcdStatusDf.loc[payRcdStatusIndex, '用户过去24个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,24) #概要信息里的字段，从还款状态计算 briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户过去5年出现逾期的所有账户数目'] = \ prp.getLoanOverdueCount(loanPayRecordMergeDf,reportTime,24*5)+prp.getLoanOverdueCount(creditCardPayRecordMergeDf,reportTime,24*5)\ +prp.getLoanOverdueCount(creditCardPayRecordMergeDfZ,reportTime,24*5) #解析贷款还款记录指标 def parseCreditCardMergeAndPayRecordDf(df,payRcdDf): if not df.empty and not payRcdDf.empty: # 正常 normalDf = df[(df['账户状态'] != '未激活') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')] if not normalDf.empty: overduePayRcdDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)] overduePayRcdDf = utils.replacePayRcdStatus(overduePayRcdDf) # 临时保存,不用过滤还款状态为0的 payRcdMaxOverdueDf = overduePayRcdDf; overduePayRcdDf = overduePayRcdDf[overduePayRcdDf['还款状态'] > 0] # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期账户数'] = overduePayRcdDf['账户编号'].unique().size #从“贷记卡信息”中提取，剔除“账户状态”为未激活、销户、呆账、呆帐后，“当前信用卡逾期账户数”/未销户贷记卡账户数（剔除“账户状态”为未激活、销户、呆账、呆帐后记录条数） creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期账户数占比'] = round(overduePayRcdDf['账户编号'].unique().size / normalDf.index.size, 2) #从“贷记卡信息”中提取，剔除“账户状态”为未激活、销户、呆账、呆帐后，对（当前信用卡逾期账户数）按“开户机构代码”去重统计账户状态为逾期，按按“开户机构代码”去重后的记录条数 overdueCreditCardDf = normalDf[normalDf['账户编号'].isin(overduePayRcdDf['账户编号'].values)] creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期机构数'] = overdueCreditCardDf['发卡机构'].unique().size creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期机构数占比'] = round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期机构数'] / normalDf['发卡机构'].unique().size, 2) creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 3); creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 6); creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近9月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 9); creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 12); creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近24月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 24); reportTime = queryInfo["reportTime"] creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近24月贷记卡最大逾期距离现在的月数'] = cip.getPayRcdMaxOverdueNumMonth(payRcdMaxOverdueDf,normalDf,reportTime, 24); creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近3个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,3); creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近6个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,6); creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近9个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,9); creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近12个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,12); creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近24个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,24); payRcdTimesDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)] payRcdTimesDf = payRcdTimesDf.sort_values(by=["账户编号", "还款日期"], ascending=(True, False)) payRcdTimesDf = payRcdTimesDf.groupby(['账户编号']).head(24) payRcdTimesDf = payRcdTimesDf[ payRcdTimesDf['还款状态'].isin(['G', 'D', 'C', 'N', 'M', '1', '2', '3', '4', '5', '6', '7'])]#从“贷记卡信息”中提取，剔除未激活、销户、呆账、呆帐后，各账户的还款次数统计“24个月（账户）还款状态”包含"G","D","C","N","M"及数字的个数 creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡24期还款记录次数'] = payRcdTimesDf.index.size # 解析被追偿信息汇总 def parseRecoveryInfoMergeDf(df): if not df.empty: recoveryMaxPayDf = df[df['债权转移时的还款状态'] !='--'] recoveryStatusCs = df[df['账户状态'] == '催收'] if not recoveryMaxPayDf.empty: briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '债权转移时的最大还款状态'] = np.max(recoveryMaxPayDf['债权转移时的还款状态']); briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '债权转移时属于催收状态的账户数'] = recoveryStatusCs.index.size; briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '债权转移时属于催收状态的账户数/被追偿信息总数'] = round(recoveryStatusCs.index.size/df.index.size,2); #creditTradeDetailDf_recoveryInfo # 被追偿账户总数 creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex,'被追偿账户总数'] = df.index.size; creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '被追偿业务种类'] = df['业务种类'].unique().size; creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '最新一笔被追偿债券接收时间'] = np.max(df['债权接收日期']); creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '总债权金额'] = np.max(df['债权金额']); creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '债权转移时的最大还款状态'] = np.max(recoveryMaxPayDf['债权转移时的还款状态']); def main(pdf_path): # 解析pdf开始 with pdfplumber.open(pdf_path) as pdf: for p in range(0, len(pdf.pages)): page = pdf.pages[p] # first_page = pdf.pages[1] # if p == 3: # print(3) tables = page.extract_tables(); for i in range(0, len(tables)): table = tables[i] df = pd.DataFrame(table); if len(keyList) > 1 and i == 0: # 判断是否被分页了 if not utils.checkHeader(df, allHeaders): key = keyList[-1]; dfObj = dfMap[key] # dfObj["nextDf"]=df; # 贷款信息 贷记卡信息 强制执行记录 if key == "loanDfs" or key == "creditCardDfs" or key == "forceExecRcdDfs" or key == 'recoveryInfoDfs' or key == "housingFundRcdDfs": # 属于列表 lastDfObj = dfObj["dfs"][-1]; lastDfObj["isByPage"] = str(p + 1); if len(dfObj["dfs"][-1]["df"].columns) == len(df.columns): # 列数相同 lastDfObj["df"] = pd.concat([lastDfObj["df"], df], axis=0,ignore_index=True); # 去最后一个进行合并 # print("key-" + key + "-page-" + str(p + 1) + "-" + "###列数相同####-被分页") else: # print("key-" + key + "-page-" + str(p + 1) + "-" + "列数不同-被分页") lastDfObj["df"] = pd.concat([lastDfObj["df"], df], axis=0, ignore_index=True); else: # 查询记录明细 为单个列表 dfObj["isByPage"] = str(p + 1); logger.info(key) if len(dfObj["df"].columns) == len(df.columns): # print("key-" + key + "-page-" + str(p + 1) + "-" + "###列数相同####-被分页") dfObj["df"] = pd.concat([dfObj["df"], df], axis=0, ignore_index=True) else: # print("key-" + key + "-page-" + str(p + 1) + "-" + "列数不同-被分页") dfObj["df"] = pd.concat([dfObj["df"], df], axis=0, ignore_index=True) # dfObj["nextDf"] = df; # 如果列数相等合并df continue; headerList0 = df.loc[0, :].tolist() # 第0行为表头 headerList0 = list(filter(None, headerList0)) headerList1 = [] if df.index.size>1: headerList1 = df.loc[1, :].tolist() # 第1行为表头 headerList1 = list(filter(None, headerList1)) if headerList1 == queryInfoDf_header: # 被查询信息 第二行为数据 queryInfoDf = df; dfKey = "queryInfoDf" dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == identity_header: # 身份信息 identityDf = df[:2] # 截取前2行 addressDf = df.loc[2:4,:] # 截取3到4行的第一和6 addressDf = addressDf.reset_index(drop=True) mobileDf = utils.replaceDateColIdx(df[5:df.index.size], 5) identityDf = pd.concat([identityDf, addressDf], axis=1, ignore_index=True) # 横向合并 dfKey = "identityDf" dfMap[dfKey]["df"] = identityDf; keyList.append(dfKey); # 组装电话号码df dfMap[dfKey]["mobileDf"] = mobileDf elif headerList0 == mateDf_header: # 配偶信息 mateDf = df; dfKey = "mateDf" dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == liveInfoDf_header: # 居住信息 mateDf = df; dfKey = "liveInfoDf" dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == occupationInfo_header: # 职业信息 可能存在分页 occupationDf = df; dfKey = "occupationDf" dfMap[dfKey]["df"] = df; keyList.append(dfKey); # elif headerList0 == queryInfoBrief_header0 and headerList1 == queryInfoBrief_header1: # 查询信息概要 第二行为数据 # queryInfoBriefDf = df; # dfKey = "queryInfoBriefDf" # dfMap[dfKey]["df"] = df; # keyList.append(dfKey); elif headerList0 == loanTradeInfo_header: # 信贷交易信息 loanTradeInfoDf = df; dfKey = "loanTradeInfoDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList1 == recoveryInfoSumDf_header: # 被追偿信息汇总 recoveryInfoSumDf = df; dfKey = "recoveryInfoSumDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList1 == badDebtsInfoSumDf_header: # 呆账信息 badDebtsInfoSumDf = df; dfKey = "badDebtsInfoSumDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList1 == overdueInfoSumDf_header: # 逾期透资信息汇总 overdueInfoSumDf = df; dfKey = "overdueInfoSumDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == loanAccountInfoSumDf_header0 and headerList1 == loanAccountInfoSumDf_header1: # 非循环贷账户信息汇总 loanAccountInfoSumDf = df; dfKey = "loanAccountInfoSumDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == creditCardInfoSumDf_header0 and headerList1 == creditCardInfoSumDf_header1: # 贷记卡信息汇总 creditCardInfoSumDf = df; dfKey = "creditCardInfoSumDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == creditCardInfoSumDfZ_header0 and headerList1 == creditCardInfoSumDfZ_header1: # 准贷记卡信息汇总 目前没有数据 dfKey = "creditCardInfoSumDfZ"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == repaymentSumDf_header0:#相关还款责任汇总 dfKey = "repaymentSumDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == publicInfoBriefDf_header0: #公共信息概要 dfKey = "publicInfoBriefDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == queryRecordSumDf_header0:#查询记录汇总 dfKey = "queryRecordSumDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == loan_header: # 贷款账户 包括循环贷，非循环贷 循环额度下分账户 dfKey = "loanDfs"; dfMap[dfKey]["dfs"].append({"df": df}); keyList.append(dfKey); elif headerList0 == creditCard_header: # 贷记卡账户 dfKey = "creditCardDfs"; dfMap[dfKey]["dfs"].append({"df": df}); keyList.append(dfKey); elif headerList0 == creditCardZ_header: # 准贷记卡账户 还不能和贷记卡合并 dfKey = "creditCardDfsZ"; dfMap[dfKey]["dfs"].append({"df": df}); keyList.append(dfKey); elif headerList0 == queryRecordDetailDf_header: # 查询记录明细 dfKey = "queryRecordDetailDf"; dfMap[dfKey]["df"] = df; keyList.append(dfKey); elif headerList0 == housingFundRcdDfs_header: # 查询记录明细 dfKey = "housingFundRcdDfs"; dfMap[dfKey]["dfs"].append({"df": df}); keyList.append(dfKey); elif headerList0 == forceExecRcdDfs_header: # 强制执行记录 dfKey = "forceExecRcdDfs"; dfMap[dfKey]["dfs"].append({"df": df}); keyList.append(dfKey); elif headerList0 == recoveryInfoDfs_header: # 被追偿信息 dfKey = "recoveryInfoDfs"; dfMap[dfKey]["dfs"].append({"df": df}); keyList.append(dfKey); # 设置分页 dfMap[dfKey]["page"] = p + 1; logger.info("组装pdf数据完成") logger.info("解析基础pdf数据开始") # 打印结果解析并构建指标 for key in dfMap: tempDfObjx = dfMap[key]; if tempDfObjx.__contains__("page"): logger.info(key + "-page-" + str(tempDfObjx["page"])) if tempDfObjx.__contains__("dfs"): if key == "loanDfs": # 贷款账户 for idx in range(0, len(tempDfObjx["dfs"])): tempDfObj = tempDfObjx["dfs"][idx]; loanAccountDfs.append(dfParser.mergeLoanDf(tempDfObj, idx,queryInfo['reportTime'])) elif key == "creditCardDfs": # 贷记卡账户合并 for idx in range(0, len(tempDfObjx["dfs"])): tempDfObj = tempDfObjx["dfs"][idx]; tempCreditCardDf = dfParser.mergeCreditCardDf(tempDfObj, idx,queryInfo['reportTime']); if tempCreditCardDf!=None: creditCardAccountDfs.append(tempCreditCardDf) elif key == "creditCardDfsZ": # 贷记卡账户合并 for idx in range(0, len(tempDfObjx["dfs"])): tempDfObj = tempDfObjx["dfs"][idx]; tempCreditCardDfZ = dfParser.mergeCreditCardDfZ(tempDfObj, idx,queryInfo['reportTime']) if tempCreditCardDfZ!=None: creditCardAccountDfsZ.append(tempCreditCardDfZ) elif key == "recoveryInfoDfs": # 贷记卡账户合并 for idx in range(0, len(tempDfObjx["dfs"])): tempDfObj = tempDfObjx["dfs"][idx]; recoveryInfoAccountDfs.append(dfParser.mergeRecoveryInfoDf(tempDfObj, idx, queryInfo['reportTime'])) elif key == "housingFundRcdDfs": # 贷记卡账户合并 for idx in range(0, len(tempDfObjx["dfs"])): tempDfObj = tempDfObjx["dfs"][idx]; housingFundRcdAccountDfs.append(dfParser.mergeHousingFundRcdDf(tempDfObj, idx, queryInfo['reportTime'])) else: # 其他 for tempDfObj in (tempDfObjx["dfs"]): if tempDfObj.__contains__("isByPage"): logger.info(key + "============其他被分页页数============" + str(tempDfObj["isByPage"])) # logger.info(tempDfObj["df"].values) else: # 单笔 tempDfObj = tempDfObjx; if tempDfObj.__contains__("isByPage"): logger.info(key + "============被分页页数================" + str(tempDfObj["isByPage"])) # logger.info(tempDfObj["df"].values) if key == "queryInfoDf": # 解析被查询信息 parseQueryInfo(tempDfObj); # print("\033[1;31m +查询信息+ \033[0m") # print(queryInfo) elif key == "identityDf": # 身份信息 parseIdentity(tempDfObj) # print("\033[1;31m +身份信息+ \033[0m") # print(identity) elif key == "mateDf": # 配偶信息 parseMate(tempDfObj) # print("\033[1;31m +配偶信息+ \033[0m") # print(mate) elif key == "liveInfoDf": # 居住信息 parseLiveInfo(tempDfObj) # print("\033[1;31m +居住信息+ \033[0m") elif key == "occupationDf": # 居住信息 parseOccupationInfoDf(tempDfObj) elif key == "loanTradeInfoDf": # 信贷交易信息提示 parseLoanTradeInfo(tempDfObj); # print("\033[1;31m +信贷交易信息提示+ \033[0m") # print(loanTradeInfo) elif key == "badDebtsInfoSumDf": # 呆账信息汇总 parseBadDebtsInfoSumDf(tempDfObj) # print("\033[1;31m +呆账信息汇总+ \033[0m") # print(overdueBrief) elif key == "recoveryInfoSumDf": # 被追偿信息汇总-资产处置和垫款 parseRecoveryInfoSum(tempDfObj) # print("\033[1;31m +资产处置和垫款+ \033[0m") # print(overdueBrief) elif key == "overdueInfoSumDf": # 逾期（透支）信息汇总 parseOverdueInfoSum(tempDfObj) # print("\033[1;31m +逾期（透支）信息汇总+ \033[0m") # print(overdueInfo) elif key == "loanAccountInfoSumDf": # 非循环贷账户信息汇总 TODO parseLoanAccountInfoSum(tempDfObj) elif key == "cycleCreditAccountInfoSumDf":#循环额度 parseCycleCreditAccountInfoSum(tempDfObj) elif key == "cycleLoanAccountInfoSumDf":#循环贷 parseCyleLoanAccountInfoSum(tempDfObj) elif key == "creditCardInfoSumDf":#贷记卡 parseCreditCardInfoSum(tempDfObj) elif key == "creditCardInfoSumDfZ": # 准贷记卡 parseCreditCardInfoSumZ(tempDfObj) elif key == "repaymentSumDf": # 相关还款责任 parseRepaymentSum(tempDfObj) elif key == "publicInfoBriefDf": parsePublicInfoBrief(tempDfObj); elif key == "queryRecordSumDf": parseQueryRecordSum(tempDfObj); elif key == "queryRecordDetailDf": # 查询记录明细 parseQueryInfoDetail(tempDfObj)# logger.info("解析基础pdf数据完成") result = "{" # 基本信息 # result+=("\033[1;34m +身份信息+ \033[0m")+"\n" result+=utils.toJson(identityInfoDf)+"," result += utils.toJson(mateInfoDf) + "," result += utils.toJson(liveInfoDf) + "," result += utils.toJson(occupationInfoDf) + "," # result+=("\033[1;34m +概要信息+ \033[0m")+"," # result+=("\033[1;34m +信贷交易信息提示+ \033[0m")+"," result+=utils.toJson(briefInfoDf_loanTradeInfo)+"," # result+=("\033[1;34m +被追偿信息汇总及呆账信息汇总+ \033[0m")+"," result+="briefInfoDf_recoveryInfoSum"+"," #占位符 result += utils.toJson(briefInfoDf_badDebtsInfoSum) + "," # result+=("\033[1;34m +逾期（透支）信息汇总+ \033[0m")+"," #此信息先占位 result+="briefInfoDf_overdueInfoSum"+"," # result+=("\033[1;34m +信贷交易授信及负债信息概要+ \033[0m")+"," result+=utils.toJson(briefInfoDf_loanTradeCreditInfo)+"," #公共信息 result += utils.toJson(publicInfoBriefDf) + "," #查询记录汇总 result += utils.toJson(queryRecordSumDf) + "," # 单独输出贷款df # logger.info("\033[1;34m +贷款信息Dataframe+ \033[0m") # logger.info(dfParser.dfHeaderLoan) logger.info("解析贷款数据开始") loanMergeDf = pd.DataFrame(columns=dfParser.dfHeaderLoan) loanPayRecordMergeDf = pd.DataFrame(columns=dfParser.dfHeaderLoanPayRecord) loanSpecialTradeMergeDf = pd.DataFrame(columns=dfParser.dfHeaderLoanSpecialTrade)#特殊交易 # 输出数据 for loanDfObj in loanAccountDfs: loanMergeDf = pd.concat([loanMergeDf, loanDfObj["loanDf"]], axis=0, ignore_index=True); loanPayRecordMergeDf = pd.concat([loanPayRecordMergeDf, loanDfObj["loanPayRecordDf"]], axis=0,ignore_index=True); loanSpecialTradeMergeDf = pd.concat([loanSpecialTradeMergeDf, loanDfObj["specialTradeDf"]], axis=0, ignore_index=True); # logger.info(loanMergeDf.values) # logger.info("\033[1;34m +贷款信息还款记录Dataframe+ \033[0m") # logger.info(dfParser.dfHeaderLoanPayRecord) # logger.info(loanPayRecordMergeDf.values) # #==============================信贷交易明细 =============================== #被追偿信息 # 被追偿信息合并df recoveryInfoMergeDf = pd.DataFrame(columns=dfParser.dfHeaderRecoveryInfo) for recoveryInfoDfObj in recoveryInfoAccountDfs: recoveryInfoMergeDf = pd.concat([recoveryInfoMergeDf, recoveryInfoDfObj["recoveryInfoDf"]], axis=0, ignore_index=True); parseRecoveryInfoMergeDf(recoveryInfoMergeDf); #被追偿信息 result = result.replace("briefInfoDf_recoveryInfoSum", utils.toJson(briefInfoDf_recoveryInfoSum))#替换汇总中的指标 result += utils.toJson(creditTradeDetailDf_recoveryInfo) + "," #设置占位符，由于存在概要的指标在明细中计算 #特殊交易 parseSpecialTrade(loanSpecialTradeMergeDf) result += utils.toJson(creditTradeDetailHeader_specialTrade) + "," # 信贷交易明细-解析非循环贷账户 parseLoanAccountInfo(loanMergeDf); result += utils.toJson(creditTradeDetailDf_loanAccountInfo) + "," #循环额度分账户 parseCycleCreditAccountInfo(loanMergeDf); result += utils.toJson(creditTradeDetailDf_cycleCreditAccountInfo) + "," #循环贷 parseCycleLoanAccountInfo(loanMergeDf); result += utils.toJson(creditTradeDetailDf_cycleLoanAccountInfo) + "," # 解析贷款账户指标 parseLoanMergeDf(loanMergeDf); # 解析还款记录相关指标 parseLoanMergeAndPayRecordDf(loanMergeDf, loanPayRecordMergeDf); # logger.info(loanAccountInfo) # logger.info(consts.loanAccountInfoHeader) # logger.info(loanAccountInfoDf.values) # result+=("\033[1;34m +贷款账户信息+ \033[0m")+"," result+=utils.toJson(loanAccountInfoDf)+"," logger.info("解析贷款数据完成") logger.info("解析贷记卡数据开始") #贷记卡合并df creditCardMergeDf = pd.DataFrame(columns=dfParser.dfHeaderCreditCard) creditCardPayRecordMergeDf = pd.DataFrame(columns=dfParser.dfHeaderCreditCardPayRecord) # logger.info("\033[1;34m +贷记卡信息Dataframe+ \033[0m") # logger.info(dfParser.dfHeaderCreditCard) # 输出数据 for creditCardDfObj in creditCardAccountDfs: creditCardMergeDf = pd.concat([creditCardMergeDf, creditCardDfObj["creditCardDf"]], axis=0, ignore_index=True); creditCardPayRecordMergeDf = pd.concat([creditCardPayRecordMergeDf, creditCardDfObj["creditCardPayRecordDf"]], axis=0,ignore_index=True); # logger.info(creditCardMergeDf.values) # 解析贷记卡账户指标 parseCreditCardMergeDf(creditCardMergeDf); parseCreditCardMergeAndPayRecordDf(creditCardMergeDf,creditCardPayRecordMergeDf) #准贷记卡合并df creditCardMergeDfZ = pd.DataFrame(columns=dfParser.dfHeaderCreditCardZ) creditCardPayRecordMergeDfZ = pd.DataFrame(columns=dfParser.dfHeaderCreditCardPayRecordZ) for creditCardDfObj in creditCardAccountDfsZ: creditCardMergeDfZ = pd.concat([creditCardMergeDfZ, creditCardDfObj["creditCardDfZ"]], axis=0, ignore_index=True); creditCardPayRecordMergeDfZ = pd.concat([creditCardPayRecordMergeDfZ, creditCardDfObj["creditCardPayRecordDfZ"]], axis=0,ignore_index=True); #解析准贷记卡相关指标 parseCreditCardMergeDfZ(creditCardMergeDfZ,creditCardPayRecordMergeDfZ); logger.info("解析贷记卡数据完成") #加工使用率指标 # result+=("\033[1;34m +贷记卡账户信息+ \033[0m")+"," result+=utils.toJson(creditCardAccountInfoDf)+"," result += utils.toJson(creditCardAccountInfoDfZ) + "," #使用率 parseUseRate() result += utils.toJson(useRateDf) + "," #开户数 parseOpenAccount(loanMergeDf, creditCardMergeDf, creditCardMergeDfZ,recoveryInfoMergeDf,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ) result += utils.toJson(openAccountDf) + "," #24期还款状态 parsePayRcdStatus(loanMergeDf, creditCardMergeDf, creditCardMergeDfZ,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ) result += utils.toJson(payRcdStatusDf) + "," #由于逾期汇总的指标再还款状态之后需要替换占位 TODO result = result.replace("briefInfoDf_overdueInfoSum",utils.toJson(briefInfoDf_overdueInfoSum)) #公积金 # 被追偿信息合并df housingFundRcdMergeDf = pd.DataFrame(columns=dfParser.dfHeaderHousingFundRcd) for housingFundRcdDfObj in housingFundRcdAccountDfs: housingFundRcdMergeDf = pd.concat([housingFundRcdMergeDf, housingFundRcdDfObj["housingFundRcdDf"]], axis=0,ignore_index=True); parseHousingFundRcd(housingFundRcdMergeDf); result += utils.toJson(housingFundRcdDf) + "," # result+=("\033[1;34m +查询记录明细+ \033[0m")+"," result+=utils.toJson(queryRecordDetailDf)+"" result +="}" return result; def uploadReportResult(basePath,pdf_path): # =================================== logger.info("准备上传文件") uploadApiUrl = config.get("baseconf", "uploadApiUrl"); uploadApiUrl = uploadApiUrl + "?access_token=" + dbController.getToken() files = {'file': open(outPath, 'rb')} businessNum = dbController.getBussinessNum(queryInfo["queryInfoCardId"]); # 根据身份证获取业务编号 logger.info("businessNum:"+businessNum) logger.info("queryInfoCardId:" + queryInfo["queryInfoCardId"]) data = {'docType': "23", 'businessNum': businessNum} response = requests.post(uploadApiUrl, files=files, data=data) text = response.text p = PrpCrypt(config.get("baseconf", "AESKey")) # logger.info("token:"+token) # logger.info(url) # logger.info(result.text) resultText = p.decrypt(text) logger.info("upload_result:" + resultText) try: descPdfPath = basePath + "execed/" + os.path.basename(pdf_path) if not os.path.exists(basePath+"execed/"): os.mkdir(basePath+"execed/") shutil.move(pdf_path, descPdfPath) # shutil.move(pdf_path.replace("pdf","txt"), descPdfPath.replace("pdf","txt")) except: info = sys.exc_info() logger.error(info[0]) logger.error(info[1]) # logging.log(logging.ERROR, info[2]) logger.error(traceback.extract_tb(info[2], 1)) def updateParseInd(file_name): # 检查是否存在已执行 parseInd = "1" try: if file_name != "": arCert = file_name[0:-4].split("_") if len(arCert) == 2: cert_id = arCert[1] parseIndTmp = dbController.getParseInd(cert_id) if parseIndTmp =="1": return True dbController.updateParseInd(cert_id, parseInd) except: logger.error("update parse ind error") return False # grouped.to_csv(r'C:\Users\Mortal\Desktop\ex.csv',index=False, encoding='utf_8_sig') if __name__ == '__main__': file_name = "" # basePath = "D:/mydocument/myproject/git/busscredit/20200430_report/"; basePath = "D:/mydocument/myprojects/creditreport/parse/" # file_name = "周颖500108199002111229.pdf"#准贷记卡已销户 呆账 # file_name = "王思13052819911012122X.pdf"#公积金 # file_name = "杨夏龙440902198410014270.pdf"#转出 # file_name = "翟彦超230125199004174216.pdf"#准贷记卡 呆账 # file_name = "蔡月辉330326198502116146.pdf" # 配偶 # file_name = "周芳芳342501198706111782.pdf" #被追偿信息 # file_name = "付春雁533001198507220344.pdf" # 公积金记录 # pdf_path = basePath + "陈洁350122199005027726.pdf" # 相关还款责任 file_name = "郑晓敏_440582199707062040.pdf" # 相关还款责任 pdf_path = basePath + file_name if len(sys.argv)>1: basePath = sys.argv[1] pdf_path = basePath + sys.argv[2] file_name = sys.argv[2] # print(sys.argv) isBat = False#批量的有问题 isPlt = config.get("baseconf", "isPlt"); if isBat:#批量生成数据不对 for file in os.listdir(basePath): if file.endswith("pdf"): start = timeit.default_timer(); pdf_path = basePath+file; outPath = pdf_path.replace("pdf",'txt') if os.path.exists(outPath): continue; logger.info(file + "解析开始...") try: result = main(pdf_path) except: info = sys.exc_info() logger.error(info[0]) logger.error( info[1]) # logging.log(logging.ERROR, info[2]) logger.error(traceback.extract_tb(info[2], 1)) # print(result) #输出到文件 sys.stdout = open(outPath, mode='w', encoding='utf-8') print(result.replace("\033[1;34m","").replace("\033[0m","")) logger.info(file+"解析完成") gc.collect() s = timeit.default_timer() - start; logger.info(str(s) + " 秒") else: if pdf_path.endswith("pdf"): start = timeit.default_timer(); outPath = pdf_path.replace("pdf", 'txt') result = "" if isPlt == "1":#生产模式 if not os.path.exists(outPath):#不存在才生成 isExec = updateParseInd(file_name) if not isExec:#没有在执行 try: logger.info(pdf_path + "解析开始...") result = main(pdf_path) sys.stdout = open(outPath, mode='w', encoding='utf-8') print(result.replace("\033[1;34m", "").replace("\033[0m", "")) logger.info(pdf_path + "解析完成") s = timeit.default_timer() - start; logger.info(str(s) + " 秒") uploadReportResult(basePath,pdf_path); except: info = sys.exc_info() logger.error(pdf_path+"#"+"解析失败") logger.error(info[0]) logger.error(info[1]) logger.error(traceback.extract_tb(info[2])) else: isExec = updateParseInd(file_name) if not isExec: # 没有在执行 result = main(pdf_path) sys.stdout = open(outPath, mode='w', encoding='utf-8') print(result.replace("\033[1;34m", "").replace("\033[0m", "")) logger.info(pdf_path + "解析完成") s = timeit.default_timer() - start; logger.info(str(s) + " 秒") uploadReportResult(basePath,pdf_path);