parseCreditPdf.py0709 154 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159
  1. #coding=utf-8
  2. import shutil
  3. import pdfplumber
  4. import pandas as pd
  5. import numpy as np;
  6. import sys
  7. import os
  8. import traceback
  9. from prp import PrpCrypt
  10. #指标相关
  11. import loanIndexParser as lip;
  12. import payRcdIndexParser as prp;
  13. import creditCardIndexParser as cip
  14. import queryInfoIndexParser as qip
  15. import requests
  16. import utils;
  17. import time;
  18. import consts;
  19. import math
  20. import dfParser;
  21. import gc
  22. import json
  23. from dbController import DbController
  24. from pboc.invokePboc import PBOC
  25. from ini_op import Config;
  26. base_dir = os.path.dirname(os.path.abspath(__file__))
  27. config = Config(base_dir+"/config.ini");
  28. productNumJz = config.get("baseconf","productNumJz")
  29. productNumXy = config.get("baseconf", "productNumXy")
  30. #连接数据库
  31. dbController = DbController();
  32. pd.set_option('mode.chained_assignment', None)
  33. import log
  34. logger = log.logger
  35. import xyHttp
  36. # 查询信息
  37. dfMap = {};
  38. allHeaders = [] # 所有表头
  39. queryInfoDf = pd.DataFrame();
  40. queryInfoDf_header = ["被查询者姓名", "被查询者证件类型", "被查询者证件号码", "查询机构", "查询原因"];
  41. dfMap["queryInfoDf"] = {"df": queryInfoDf, "nextDf": None};
  42. allHeaders.append(queryInfoDf_header);
  43. # 身份信息
  44. identityDf = pd.DataFrame();
  45. identity_header = ['性别', '出生日期', '婚姻状况', '学历', '学位', '就业状况', '国籍', '电子邮箱']
  46. addressDf = pd.DataFrame(); # 通讯地址
  47. dfMap["identityDf"] = {"df": identityDf, "nextDf": None, "mobiles": None};
  48. allHeaders.append(identity_header);
  49. # 配偶信息
  50. mateDf = pd.DataFrame();
  51. mateDf_header = ['姓名', '证件类型', '证件号码', '工作单位', '联系电话']
  52. dfMap["mateDf"] = {"df": mateDf, "nextDf": None};
  53. allHeaders.append(mateDf_header);
  54. # 居住信息====暂时该信息没有用到先不解析
  55. liveInfoDf = pd.DataFrame();
  56. liveInfoDf_header = ['编号', '居住地址', '住宅电话', '居住状况', '信息更新日期']
  57. dfMap["liveInfoDf"] = {"df": liveInfoDf, "nextDf": None};
  58. allHeaders.append(liveInfoDf_header);
  59. # 职业信息
  60. occupationDf = pd.DataFrame();
  61. occupationInfo_header = ['编号', '工作单位', '单位性质', '单位地址', '单位电话']
  62. occupationInfo_header1 = ['编号', '职业', '行业', '职务', '职称', '进入本单位年份', '信息更新日期']
  63. dfMap["occupationDf"] = ({"df": occupationDf, "nextDf": None});
  64. # allHeaders.append(occupationInfo_header1);
  65. allHeaders.append(occupationInfo_header);
  66. # 上次查询记录
  67. preQueryRcd_header0 = ['上一次查询记录']
  68. allHeaders.append(preQueryRcd_header0);
  69. # 查询记录概要
  70. # queryInfoBriefDf = pd.DataFrame();
  71. # queryInfoBrief_header0 = ['最近1个月内的查询机构数', '最近1个月内的查询次数', '最近2年内的查询次数']
  72. # queryInfoBrief_header1 = ['贷款审批', '信用卡审批', '贷款审批', '信用卡\n审批', '本人查询', '贷后管理', '担保资格\n审查', '特约商户\n实名审查']
  73. # dfMap["queryInfoBriefDf"] = ({"df": queryInfoBriefDf, "nextDf": None});
  74. # allHeaders.append(queryInfoBrief_header0);
  75. # allHeaders.append(queryInfoBrief_header1);
  76. # 信贷交易信息提示
  77. loanTradeInfoDf = pd.DataFrame();
  78. loanTradeInfo_header = ['业务类型', '账户数', '首笔业务发放月份'];
  79. dfMap["loanTradeInfoDf"] = ({"df": loanTradeInfoDf, "nextDf": None});
  80. allHeaders.append(loanTradeInfo_header)
  81. # 信贷交易违约信息概要
  82. # 被追偿信息汇总 资产处置和垫款业务
  83. recoveryInfoSumDf = pd.DataFrame();
  84. recoveryInfoSumDf_header = ['业务种类', '账户数', '余额'];
  85. dfMap["recoveryInfoSumDf"] = ({"df": recoveryInfoSumDf, "nextDf": None});
  86. allHeaders.append(recoveryInfoSumDf_header)
  87. # 呆账信息汇总
  88. badDebtsInfoSumDf = pd.DataFrame();
  89. badDebtsInfoSumDf_header = ['账户数', '余额']; # 被追偿信息汇总
  90. dfMap["badDebtsInfoSumDf"] = ({"df": badDebtsInfoSumDf, "nextDf": None});
  91. allHeaders.append(badDebtsInfoSumDf_header)
  92. # 逾期透资信息汇总
  93. overdueInfoSumDf = pd.DataFrame();
  94. overdueInfoSumDf_header = ['账户类型', '账户数', '月份数', '单月最高逾期/透支总额', '最长逾期/透支月数']
  95. dfMap["overdueInfoSumDf"] = ({"df": overdueInfoSumDf, "nextDf": None});
  96. allHeaders.append(overdueInfoSumDf_header)
  97. # 非循环贷账户信息汇总
  98. loanAccountInfoSumDf = pd.DataFrame();
  99. loanAccountInfoSumDf_header0 = ['非循环贷账户信息汇总']
  100. loanAccountInfoSumDf_header1 = ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款']
  101. dfMap["loanAccountInfoSumDf"] = ({"df": loanAccountInfoSumDf, "nextDf": None});
  102. allHeaders.append(loanAccountInfoSumDf_header0)
  103. allHeaders.append(loanAccountInfoSumDf_header1)
  104. # 循环额度下分账户信息汇总
  105. cycleCreditAccountInfoSumDf = pd.DataFrame();
  106. cycleCreditAccountInfoSumDf_header0 = ['循环额度下分账户信息汇总']
  107. cycleCreditAccountInfoSumDf_header1 = ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款'],
  108. dfMap["cycleCreditAccountInfoSumDf"] = ({"df": cycleCreditAccountInfoSumDf, "nextDf": None});
  109. allHeaders.append(cycleCreditAccountInfoSumDf_header0)
  110. allHeaders.append(cycleCreditAccountInfoSumDf_header1)
  111. # 循环贷账户信息汇总
  112. cycleLoanAccountInfoSumDf = pd.DataFrame();
  113. cycleLoanAccountInfoSumDf_header0 = ['循环贷账户信息汇总']
  114. cycleLoanAccountInfoSumDf_header1 = ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款']
  115. dfMap["cycleLoanAccountInfoSumDf"] = ({"df": cycleLoanAccountInfoSumDf, "nextDf": None});
  116. allHeaders.append(cycleLoanAccountInfoSumDf_header0)
  117. allHeaders.append(cycleLoanAccountInfoSumDf_header1)
  118. # 贷记卡账户信息汇总
  119. creditCardInfoSumDf = pd.DataFrame();
  120. creditCardInfoSumDf_header0 = ['贷记卡账户信息汇总']
  121. creditCardInfoSumDf_header1 = ['发卡机构数', '账户数', '授信总额', '单家机构最高\n授信额', '单家机构最低\n授信额', '已用额度', '最近6个月平\n均使用额度']
  122. dfMap["creditCardInfoSumDf"] = ({"df": creditCardInfoSumDf, "nextDf": None});
  123. allHeaders.append(creditCardInfoSumDf_header0)
  124. allHeaders.append(creditCardInfoSumDf_header1)
  125. # 准贷记卡账户信息汇总
  126. creditCardInfoSumDfZ = pd.DataFrame();
  127. creditCardInfoSumDfZ_header0 = ['准贷记卡账户信息汇总']#'准贷记卡账户信息汇总'
  128. creditCardInfoSumDfZ_header1 = ['发卡机构数', '账户数', '授信总额', '单家机构最高\n授信额', '单家机构最低\n授信额', '透支余额', '最近6个月平\n均透支余额']
  129. dfMap["creditCardInfoSumDfZ"] = ({"df": creditCardInfoSumDfZ, "nextDf": None});
  130. allHeaders.append(creditCardInfoSumDfZ_header0)
  131. allHeaders.append(creditCardInfoSumDfZ_header1)
  132. #公共信息概要
  133. publicInfoBriefDf = pd.DataFrame();
  134. publicInfoBriefDf_header0 = ['公共信息汇总']
  135. dfMap["publicInfoBriefDf"] = ({"df": publicInfoBriefDf, "nextDf": None});
  136. allHeaders.append(publicInfoBriefDf_header0)
  137. #查询记录汇总
  138. queryRecordSumDf_header0=['最近1个月内的查询机构数', '最近1个月内的查询次数', '最近2年内的查询次数']
  139. queryRecordSumDf = pd.DataFrame();
  140. dfMap["queryRecordSumDf"] = ({"df": queryRecordSumDf, "nextDf": None});
  141. allHeaders.append(queryRecordSumDf_header0)
  142. # 非循环贷账户,循环额度下分账户
  143. # 循环贷账户
  144. loan_header = ['管理机构', '账户标识', '开立日期', '到期日期', '借款金额', '账户币种']
  145. loanDfs = [];
  146. dfMap["loanDfs"] = ({"dfs": loanDfs, "nextDf": []});
  147. allHeaders.append(loan_header)
  148. # 贷记卡账户
  149. creditCard_header = ['发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '业务种类', '担保方式']
  150. creditCardDfs = [];
  151. dfMap["creditCardDfs"] = ({"dfs": creditCardDfs, "nextDf": []});
  152. allHeaders.append(creditCard_header)
  153. # 准备贷记卡账户
  154. creditCardZ_header = ['发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '担保方式']
  155. creditCardDfsZ = [];
  156. dfMap["creditCardDfsZ"] = ({"dfs": creditCardDfsZ, "nextDf": []});
  157. allHeaders.append(creditCardZ_header)
  158. #
  159. # 相关还款责任信息汇总 未使用到
  160. # 信贷交易信息明细
  161. # 被追偿信息 未使用到
  162. recoveryInfoDfs_header = ['管理机构','业务种类','债权接收日期','债权金额','债权转移时的还款状态']
  163. recoveryInfoDfs = [];
  164. dfMap["recoveryInfoDfs"] = ({"dfs": recoveryInfoDfs, "nextDf": []});
  165. allHeaders.append(recoveryInfoDfs_header)
  166. # 公共信息明细
  167. # 强制执行记录
  168. forceExecRcdDfs_header = ['编号', '执行法院', '执行案由', '立案日期', '结案方式']
  169. forceExecRcdDfs = [];
  170. dfMap["forceExecRcdDfs"] = ({"dfs": forceExecRcdDfs, "nextDf": []});
  171. allHeaders.append(forceExecRcdDfs_header)
  172. # 查询记录
  173. queryRecordDetailDf_header = ['编号', '查询日期', '查询机构', '查询原因']
  174. dfMap["queryRecordDetailDf"] = ({"df": pd.DataFrame(), "nextDf": []});
  175. allHeaders.append(queryRecordDetailDf_header)
  176. #住房公积金参缴记录
  177. housingFundRcdDfs_header =['参缴地', '参缴日期', '初缴月份', '缴至月份', '缴费状态', '月缴存额', '个人缴存比例', '单位缴存比例']
  178. housingFundRcdDfs = []
  179. dfMap["housingFundRcdDfs"] = ({"dfs": housingFundRcdDfs, "nextDf": []});
  180. allHeaders.append(housingFundRcdDfs_header)
  181. repaymentSumDf_header0=['相关还款责任信息汇总']
  182. dfMap["repaymentSumDf"] = ({"df": pd.DataFrame(), "nextDf": None});
  183. allHeaders.append(repaymentSumDf_header0)
  184. # 处理分页思路
  185. # df估计得放到对象里面,然后存储下一个df,一个对象里包含key
  186. # 然后判断对象的df的完整性,如果不完整代表被分页了,把nextdf合并到当前的df
  187. # 针对可合并的列的场景
  188. # =======
  189. keyList = [] # 存储所有的df的key列表
  190. # pd.Series()
  191. # 检查数据是否带表头
  192. # 应该是每一页开头的一行和每个表头对比一次,确认是不是表头,或者表头有什么共同的规律也可以看下
  193. import timeit
  194. # 定义指标部分======================start
  195. reportTime = ""; # 报告时间
  196. # 被查询者姓名
  197. queryInfoName = "";
  198. queryInfoCardId = "" # 被查询者证件号码
  199. # 定义指标部分======================end
  200. # 被查询信息-基础信息
  201. # 报告时间
  202. # 被查询者姓名
  203. # 被查询者证件号码
  204. # 基础信息
  205. queryInfo = {"reportTime":"","queryInfoCardId":""}
  206. # 身份信息
  207. identity = {}
  208. # 配偶信息
  209. mate = {}
  210. # 信贷交易信息提示-信用提示
  211. loanTradeInfo = {'perHouseLoanAccount': 0, 'perBusHouseLoanAccount': 0, 'otherLoanAccount': 0, 'loanMonthMin': 0,
  212. 'creditCardMonthMin': 0, 'creditAccount': 0, 'creditAccountZ': 0}
  213. # 逾期及违约信息概要
  214. overdueBrief = {}
  215. # 逾期及透资信息汇总
  216. # 贷款逾期账户数 loanOverdueAccount
  217. # 贷款逾期月份数 loanOverdueMonth
  218. # 贷款单月最高逾期总额 loanCurMonthOverdueMaxTotal
  219. # 贷款最长逾期月数 loanMaxOverdueMonth
  220. overdueInfo = {"loanOverdueAccount": "", "loanOverdueMonth": "", "loanCurMonthOverdueMaxTotal": "",
  221. "loanMaxOverdueMonth": "",
  222. "creditCardOverdueAccount": "", "creditCardOverdueMonth": "", "creditCardCurMonthOverdueMaxTotal": "",
  223. "creditCardMaxOverdueMonth": ""}
  224. # 未结清贷款信息汇总
  225. # ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款']
  226. loanAccountInfoSum = {"mgrOrgCount": 0, "account": 0, "creditTotalAmt": 0, "balance": 0, "last6AvgPayAmt": 0}
  227. # 未销户贷记卡发卡法人机构数
  228. # 未销户贷记卡发卡机构数
  229. # 未销户贷记卡账户数
  230. # 未销户贷记卡授信总额
  231. # 未销户贷记卡单家行最高授信额
  232. # 未销户贷记卡单家行最低授信额
  233. # 未销户贷记卡已用额度
  234. # 未销户贷记卡近6月平均使用额度
  235. # 未结清贷记卡信息汇总
  236. # ['发卡机构数', '账户数', '授信总额', '单家机构最高\n授信额', '单家机构最低\n授信额', '已用额度', '最近6个月平\n均使用额度']
  237. creditCardInfoSum = {"awardOrgCount": 0, "account": 0, "creditTotalAmt": 0, "perMaxCreditTotalAmt": 0,
  238. "perMinCreditTotalAmt": 0, "useAmt": 0, "last6AvgUseAmt": 0}
  239. # 信 贷 审 批 查 询 记 录 明 细
  240. queryRecordDetail = {"last1MonthQueryTimes": 0, "last3MothLoanApproveTimes": 0, "last3MonthQueryTimes": 0,
  241. "lastTimeLoanApproveMonth": 0}
  242. #最近一笔结清贷款的贷款金额 
  243. loanAccountInfo = {"lastSettleLoanAmt": 0}
  244. loanAccountDfs=[];#横向合并
  245. creditCardAccountDfs=[];#贷记卡账户合并
  246. creditCardAccountDfsZ=[];#准贷记卡账户合并
  247. recoveryInfoAccountDfs=[];#被追偿账户合并
  248. housingFundRcdAccountDfs=[];#公积金账户合并
  249. #============================指标定义区 start=============================
  250. #基本信息 拆分
  251. # basicInfoDf = pd.DataFrame(columns=consts.basicInfoHeader, index=[0])
  252. #身份信息
  253. identityInfoIndex = '身份信息'
  254. identityInfoDf = pd.DataFrame(columns=consts.identityInfoHeader,index=[identityInfoIndex])
  255. #配偶信息
  256. mateInfoIndex = '配偶信息'
  257. mateInfoDf = pd.DataFrame(columns=consts.mateInfoHeader,index=[mateInfoIndex])
  258. #居住信息
  259. liveInfoIndex = '居住信息'
  260. liveInfoDf = pd.DataFrame(columns=consts.liveInfoHeader,index=[liveInfoIndex])
  261. #职业信息
  262. occupationInfoIndex = '职业信息'
  263. occupationInfoDf = pd.DataFrame(columns=consts.occupationInfoHeader,index=[occupationInfoIndex])
  264. #信贷交易信息提示
  265. loanTradeInfoIndex = '信贷交易信息提示'
  266. briefInfoDf_loanTradeInfo = pd.DataFrame(columns=consts.briefInfoHeader_loanTradeInfo,index=[loanTradeInfoIndex])
  267. #被追偿信息汇总及呆账信息汇总
  268. recoveryInfoSumIndex = '信贷交易违约信息概要'
  269. briefInfoDf_recoveryInfoSum = pd.DataFrame(columns=consts.briefInfoHeader_recoveryInfo,index=[recoveryInfoSumIndex])
  270. #呆账信息汇总
  271. badDebtsInfoIndex = '呆账信息汇总'
  272. briefInfoDf_badDebtsInfoSum = pd.DataFrame(columns=consts.briefInfoHeader_badDebtsInfoSum,index=[badDebtsInfoIndex])
  273. #逾期(透支)信息汇总
  274. overdueInfoSumIndex='逾期(透支)信息汇总'
  275. briefInfoDf_overdueInfoSum = pd.DataFrame(columns=consts.briefInfoHeader_overdueInfoSum,index=[overdueInfoSumIndex])
  276. #信贷交易授信及负债信息概要
  277. loanTradeCreditInfoIndex='信贷交易授信及负债信息概要'
  278. briefInfoDf_loanTradeCreditInfo = pd.DataFrame(columns=consts.briefInfoHeader_loanTradeCreditInfo,index=[loanTradeCreditInfoIndex]).fillna(0.0)
  279. #公共信息概要
  280. publicInfoBriefIndex = '公共信息概要'
  281. publicInfoBriefDf = pd.DataFrame(columns=consts.publicInfoBriefHeader,index=[publicInfoBriefIndex])
  282. #查询记录汇总
  283. queryRecordSumIndex = '查询记录汇总'
  284. queryRecordSumDf = pd.DataFrame(columns=consts.queryRecordSumHeader,index=[queryRecordSumIndex])
  285. #信贷交易明细-被追偿信息
  286. recoveryInfoIndex='被追偿信息'
  287. creditTradeDetailDf_recoveryInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_recoveryInfo,index=[recoveryInfoIndex])
  288. #信贷交易明细-特殊交易
  289. specialTradeIndex='特殊交易'
  290. creditTradeDetailHeader_specialTrade = pd.DataFrame(columns=consts.creditTradeDetailHeader_specialTrade,index=[specialTradeIndex])
  291. #信贷交易明细
  292. #非循环贷账户
  293. loanInfoIndex='非循环贷账户'
  294. creditTradeDetailDf_loanAccountInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_loanAccountInfo,index=[loanInfoIndex])
  295. #循环额度下分账户
  296. cycleCreditAccountInfoIndex='循环额度下分账户'
  297. creditTradeDetailDf_cycleCreditAccountInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_cycleCreditAccountInfo,index=[cycleCreditAccountInfoIndex])
  298. #循环贷账户
  299. cycleLoanAccountInfoIndex='循环贷账户'
  300. creditTradeDetailDf_cycleLoanAccountInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_cycleLoanAccountInfo,index=[cycleLoanAccountInfoIndex])
  301. #贷款信息
  302. loanAccountInfoIndex='贷款信息'
  303. loanAccountInfoDf = pd.DataFrame(columns=consts.loanAccountInfoHeader,index=[loanAccountInfoIndex])
  304. #贷记卡信息
  305. creditCardAccountInfoIndex = '贷记卡账户'
  306. creditCardAccountInfoDf = pd.DataFrame(columns=consts.creditCardAccountInfoHeader,index=[creditCardAccountInfoIndex])
  307. #准贷记卡
  308. creditCardAccountInfoIndexZ = '准贷记卡账户'
  309. creditCardAccountInfoDfZ = pd.DataFrame(columns=consts.creditCardAccountInfoHeaderZ,index=[creditCardAccountInfoIndexZ])
  310. useRateIndex = '使用率'
  311. useRateDf = pd.DataFrame(columns=consts.creditTradeDetailHeader_useRate,index=[useRateIndex])
  312. openAccountIndex = '开户数'
  313. openAccountDf = pd.DataFrame(columns=consts.creditTradeDetailHeader_openAccount,index=[openAccountIndex])
  314. payRcdStatusIndex = '24期还款状态'
  315. payRcdStatusDf = pd.DataFrame(columns=consts.creditTradeDetailHeader_payRcdStatus,index=[payRcdStatusIndex])
  316. #查询记录明细指标
  317. queryRecordDetailIndex = '信贷审批查询记录明细'
  318. queryRecordDetailDf = pd.DataFrame(columns=consts.queryRecordDetailHeader,index=[queryRecordDetailIndex])
  319. #住房公积金
  320. housingFundRcdIndex = '住房公积金参缴记录'
  321. housingFundRcdDf = pd.DataFrame(columns=consts.housingFundRcdHeader,index=[housingFundRcdIndex])
  322. #============================指标定义区 end=============================
  323. # 解析被查询信息指标
  324. def parseQueryInfo(dfObj):
  325. df = dfObj["df"];
  326. reportTime = df.loc[0, :][3]
  327. reportTime = reportTime.split(":")[1]
  328. reportTime = reportTime.replace(".", "-"); # 报告时间
  329. queryInfo["reportTime"] = reportTime
  330. row = df.loc[2, :]
  331. queryInfo["queryInfoName"] = row[0]; # 被查询者姓名
  332. # basicInfoDf.loc[0, '姓名'] = row[0]
  333. queryInfo["queryInfoCardId"] = row[2].replace("\n", ""); # 被查询者证件号码
  334. # basicInfoDf.loc[0, '身份证'] = row[2].replace("\n", "")
  335. # 婚姻状况
  336. # 学历
  337. # 单位电话
  338. # 住宅电话
  339. # 通讯地址
  340. def parseIdentity(dfObj):
  341. df = dfObj["df"];
  342. if not df.empty:
  343. row1 = df.loc[1, :].dropna().reset_index(drop=True)
  344. # identity["marital"] = row1[3] # 婚姻状况
  345. # identity["education"] = row1[4] # 学历
  346. # identity["commAddress"] = row1[9].replace("\n", ""); # 通讯地址
  347. identityInfoDf.loc[identityInfoIndex, '性别'] = row1[0]
  348. identityInfoDf.loc[identityInfoIndex, '出生日期'] = dfParser.formatDate(row1[1])[0:7]
  349. identityInfoDf.loc[identityInfoIndex, '国籍'] = row1[6]
  350. identityInfoDf.loc[identityInfoIndex, '户籍地址'] = row1[9].replace("\n", "")
  351. identityInfoDf.loc[identityInfoIndex, '婚姻状况'] = row1[2]
  352. identityInfoDf.loc[identityInfoIndex, '学历'] = row1[3].replace("\n", "")
  353. identityInfoDf.loc[identityInfoIndex, '学位'] = row1[4]
  354. identityInfoDf.loc[identityInfoIndex, '通讯地址'] = row1[8].replace("\n", "")
  355. identityInfoDf.loc[identityInfoIndex, '就业状况'] = row1[5]
  356. mobileDf = dfObj["mobileDf"];
  357. identityInfoDf.loc[identityInfoIndex, '历史手机号码数'] = mobileDf.index.size
  358. reportTime = queryInfo["reportTime"]
  359. identityInfoDf.loc[identityInfoIndex, '近3个月手机号码数'] = getLastMonthMobileCount(mobileDf,3,reportTime)
  360. identityInfoDf.loc[identityInfoIndex, '近6个月手机号码数'] = getLastMonthMobileCount(mobileDf, 6,reportTime)
  361. identityInfoDf.loc[identityInfoIndex, '近12个月手机号码数'] = getLastMonthMobileCount(mobileDf, 12,reportTime)
  362. identityInfoDf.loc[identityInfoIndex, '近24个月手机号码数'] = getLastMonthMobileCount(mobileDf, 24,reportTime)
  363. #最近几个月电话号码数
  364. def getLastMonthMobileCount(df, month,reportTime):
  365. # 当前日期
  366. last1MonthDateStr = reportTime
  367. # 最近一个月
  368. lastMonthDate = np.datetime64(last1MonthDateStr, "D") - np.timedelta64(30 * month, 'D')
  369. lastMonthMobileDf = df[df[5] >= str(lastMonthDate)]
  370. return lastMonthMobileDf.shape[0];
  371. # 配偶姓名
  372. # 配偶证件号码
  373. # 配偶工作单位
  374. # 配偶联系电话
  375. def parseMate(dfObj):
  376. df = dfObj["df"];
  377. if not df.empty:
  378. row1 = df.loc[1, :]
  379. mate["mateName"] = row1[0] # 配偶姓名
  380. mate["mateCardId"] = row1[2] # 配偶证件号码
  381. mate["mateWorkCompany"] = row1[3].replace("\n", ""); # 配偶工作单位
  382. mate["mateContactTel"] = row1[4]; # 配偶联系电话
  383. mateInfoDf.loc[mateInfoIndex, '姓名'] = row1[0]
  384. mateInfoDf.loc[mateInfoIndex, '证件号码'] = row1[2]
  385. mateInfoDf.loc[mateInfoIndex, '工作单位'] = row1[3].replace("\n", "");
  386. mateInfoDf.loc[mateInfoIndex, '联系电话'] = row1[4].replace("\n", "");
  387. #解析居住信息
  388. def parseLiveInfo(dfObj):
  389. df = dfObj["df"];
  390. if not df.empty:
  391. row1 = df.loc[1, :]
  392. liveInfoDf.loc[liveInfoIndex, '居住地址'] = row1[1]
  393. liveInfoDf.loc[liveInfoIndex, '住宅电话'] = row1[2]
  394. liveInfoDf.loc[liveInfoIndex, '历史居住地址个数'] = df.index.size-1;
  395. curDate = np.datetime64(time.strftime("%Y-%m-%d"));
  396. last3year = str(curDate)[0:4]
  397. last3yearDate = str(int(last3year)-3)+str(curDate)[4:10]
  398. lastLiveDf = df[df[4]>=last3yearDate];
  399. liveInfoDf.loc[liveInfoIndex, '最近3年内居住地址个数'] = lastLiveDf.index.size-1;
  400. houseIndex = df[df[3]=='自置'].index.size>0
  401. if (houseIndex):
  402. houseStr = '是'
  403. else:
  404. houseStr= '否'
  405. liveInfoDf.loc[liveInfoIndex, '当前居住状况-是否具有自有住房'] = houseStr;
  406. liveInfoDf.loc[liveInfoIndex, '居住状况'] = row1[3]
  407. liveInfoDf.loc[liveInfoIndex, '信息更新日期'] = row1[4]
  408. #解析职业信息
  409. def parseOccupationInfoDf(dfObj):
  410. df = dfObj["df"];
  411. if not df.empty:
  412. occIndex1 = 0#判断职业从哪行开始
  413. for i in range(0,df.index.size):
  414. if df.loc[i,:].dropna().tolist()==occupationInfo_header1:
  415. occIndex1=i;
  416. break;
  417. occDf = df[1:occIndex1].reset_index(drop=True)#工作单位
  418. occDfNew = pd.DataFrame()
  419. occDf1New = pd.DataFrame()
  420. #删除为none的列 合并的bug TODO
  421. for i in range(0,occDf.index.size):
  422. occDfNew = occDfNew.append([pd.DataFrame(occDf.iloc[i].dropna().reset_index(drop=True)).T],ignore_index=True)
  423. occDf1 = df[occIndex1+1:df.index.size].reset_index(drop=True) #职业
  424. for i in range(0,occDf1.index.size):
  425. occDf1New = occDf1New.append([pd.DataFrame(occDf1.iloc[i].dropna().reset_index(drop=True)).T], ignore_index=True)
  426. occDf = pd.concat([occDfNew, occDf1New], axis=1, ignore_index=True)#合并df
  427. row = occDf.loc[0, :].dropna()#取最新
  428. occupationInfoDf.loc[occupationInfoIndex, '工作单位'] = row[1]
  429. last3yearDate = utils.getLastMonthDate(queryInfo['reportTime'],12*3)
  430. occDf = utils.replaceDateColIdx(occDf,occDf.columns.size-1)
  431. dateIndex = occDf.columns.size-1;#日期列
  432. last3yearOccDf = occDf[occDf[dateIndex]>=last3yearDate]
  433. occupationInfoDf.loc[occupationInfoIndex, '最近3年内工作单位数'] = last3yearOccDf.index.size;
  434. occupationInfoDf.loc[occupationInfoIndex, '单位电话'] = row[4];
  435. reportTime = queryInfo['reportTime']
  436. try:
  437. minDateIndex = np.argmin(occDf[dateIndex]);
  438. maxDateIndex = np.argmax(occDf[dateIndex]);
  439. rowYearMin = occDf.loc[minDateIndex, :].dropna()
  440. rowYearMax = occDf.loc[maxDateIndex, :].dropna()
  441. if rowYearMin[10]!="--":
  442. occupationInfoDf.loc[occupationInfoIndex, '最早进入本单位年份距报告日期时长'] = int(str(np.datetime64(reportTime, "Y")))-int(rowYearMin[10])
  443. if rowYearMax[10]!="--":
  444. occupationInfoDf.loc[occupationInfoIndex, '最新进入本单位年份距报告日期时长'] = int(str(np.datetime64(reportTime, "Y")))-int(rowYearMax[10])
  445. except:
  446. logger.error("最早进入本单位年份距报告日期时长解析异常")
  447. row0 = occDf.loc[0,:].dropna().reset_index(drop=True)#最新
  448. occupationInfoDf.loc[occupationInfoIndex, '单位性质'] =row0[2]
  449. occupationInfoDf.loc[occupationInfoIndex, '单位地址'] = row0[3].replace("\n","")
  450. occupationInfoDf.loc[occupationInfoIndex, '职业'] = row0[6]
  451. occupationInfoDf.loc[occupationInfoIndex, '行业'] = row0[7]
  452. occupationInfoDf.loc[occupationInfoIndex, '职务'] = row0[8]
  453. occupationInfoDf.loc[occupationInfoIndex, '职称'] = row0[9]
  454. occupationInfoDf.loc[occupationInfoIndex, '进入本单位年份'] = row0[10]
  455. occupationInfoDf.loc[occupationInfoIndex, '信息更新日期'] = row0[11]
  456. occupationInfoDf.loc[occupationInfoIndex, '历史工作单位数'] = occDf1.index.size
  457. # 日期相减离当前时间月份
  458. # 贷款账龄(月数)=当前日期(2020-04-01)-最小月份的1日(2019.2->2019-12-01)=4
  459. # def difMonth(dateStr):
  460. # return int(int(str(np.datetime64(time.strftime("%Y-%m-%d")) -
  461. # np.datetime64(dateStr.replace('.', '-'), "D")).split(" ")[0]) / 30);
  462. # 信贷交易明细汇总
  463. def parseLoanTradeInfo(dfObj):
  464. df = dfObj["df"];
  465. # row1 = df.loc[1, :]
  466. if not df.empty:
  467. loanMonthDf = df[1: 4]
  468. loanMonthDf = loanMonthDf.reset_index(drop=True)
  469. briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '个人住房贷款账户数'] = utils.toInt(loanMonthDf.loc[0, :][2])
  470. briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex,'个人商用房贷款(包括商住两用)账户数']=utils.toInt(loanMonthDf.loc[1, :][2])
  471. briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '其他类贷款账户数'] = utils.toInt(loanMonthDf.loc[2, :][2])
  472. creditCardDf = df[4: 6];
  473. creditCardDf = creditCardDf.reset_index(drop=True)
  474. briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '贷记卡账户数'] = utils.toInt(creditCardDf.loc[0, :][2])
  475. briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '准贷记卡账户数'] = utils.toInt(creditCardDf.loc[1, :][2])
  476. # 解析呆账信息汇总
  477. def parseBadDebtsInfoSumDf(dfObj):
  478. df = dfObj["df"];
  479. if not df.empty:
  480. row1 = df.loc[2, :]
  481. briefInfoDf_badDebtsInfoSum.loc[badDebtsInfoIndex, '账户数'] = row1[0];
  482. briefInfoDf_badDebtsInfoSum.loc[badDebtsInfoIndex, '余额'] = utils.replaceAmt(row1[1]);
  483. # 解析被追偿信息汇总
  484. def parseRecoveryInfoSum(dfObj):
  485. df = dfObj["df"];
  486. if not df.empty:
  487. row1 = df.loc[2, :]
  488. row2 = df.loc[3, :]
  489. row3 = df.loc[4, :]
  490. overdueBrief["disposalInfoSumAccount"] = row1[1]; # 资产处置信息汇总笔数
  491. briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '资产处置业务账户数'] = row1[1];
  492. overdueBrief["disposalInfoSumAmt"] = row1[2]; # 资产处置信息汇总余额
  493. briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '资产处置业务余额'] = utils.replaceAmt(row1[2]);
  494. overdueBrief["advanceInfoSumAccount"] = row2[1]; # 垫款业务笔数
  495. briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '垫款业务账户数'] = row2[1];
  496. overdueBrief["advanceInfoSumAmt"] = row2[2]; # 垫款业务余额
  497. briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '垫款业务余额'] = utils.replaceAmt(row2[2]);
  498. briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '合计总账户数'] = row3[1];
  499. briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '合计总余额'] = utils.replaceAmt(row3[2]);
  500. # 贷款逾期账户数
  501. # 贷款逾期月份数
  502. # 贷款单月最高逾期总额
  503. # 贷款最长逾期月数
  504. def parseOverdueInfoSum(dfObj):
  505. df = dfObj["df"];
  506. if not df.empty:
  507. row2= df.loc[2, :]
  508. row3 = df.loc[3, :]
  509. row4 = df.loc[4, :]
  510. row5 = df.loc[5, :]
  511. row6 = df.loc[6, :]
  512. #这块的数据需要进行出来 TODO
  513. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户账户数'] = utils.toInt(row2[1]);
  514. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户月份数'] = utils.toInt(row2[2]);
  515. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户单月最高逾期总额'] = utils.replaceAmt(row2[3]);
  516. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户最长逾期月数'] = utils.toInt(row2[4]);
  517. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户账户数'] = utils.toInt(row3[1]);
  518. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户月份数'] = utils.toInt(row3[2]);
  519. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户单月最高逾期总额'] = utils.replaceAmt(row3[3]);
  520. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户最长逾期月数'] = utils.toInt(row3[4]);
  521. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户账户数'] = utils.toInt(row4[1]);
  522. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户月份数'] = utils.toInt(row4[2]);
  523. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户单月最高逾期总额'] = utils.replaceAmt(row4[3]);
  524. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户最长逾期月数'] = utils.toInt(row4[4]);
  525. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户账户数'] = utils.toInt(row5[1]);
  526. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户月份数'] = utils.toInt(row5[2]);
  527. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户单月逾期总额'] = utils.replaceAmt(row5[3]);
  528. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户最长逾期月数'] = utils.toInt(row5[4]);
  529. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户账户数'] = utils.toInt(row6[1]);
  530. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户月份数'] = utils.toInt(row6[2]);
  531. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户单月透支总额'] = utils.replaceAmt(row6[3]);
  532. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户最长透支月数'] = utils.toInt(row6[4]);
  533. overdueInfoAccountDf = df[df[1] != '--'];
  534. overdueInfoAccountDf = overdueInfoAccountDf[2:7]
  535. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户所有逾期账户最长逾期/透支月数最大值']=np.max(overdueInfoAccountDf[4].astype('int'))
  536. #np.sum(overdueInfoAccountDf[1])
  537. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户所有逾期账户数加总']= np.sum(overdueInfoAccountDf[1].astype('int'))# TODO
  538. # briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户过去5年出现逾期的所有账户数目']=None# TODO
  539. # 未结清贷款法人机构数 从“未结清贷款信息汇总”中直接提取LoanLegalOrgNum
  540. # 未结清贷款机构数 从“未结清贷款信息汇总”中直接提取LoanOrgNum
  541. # 未结清贷款笔数 从“未结清贷款信息汇总”中直接提取CountNum
  542. # 未结清贷款合同总额 从“未结清贷款信息汇总”中直接提取ContractProfits
  543. # 未结清贷款合同余额 从“未结清贷款信息汇总”中直接提取Balance
  544. # 未结清贷款近6月平均应还款 从“未结清贷款信息汇总”中直接提取Last6MothsAvgRepayAmount
  545. # 个人贷款未结清笔数 "从“未结清贷款信息汇总”计算客户符合以下条件的贷款笔数
  546. # 1.贷款类型不为('%个人助学贷款%' ,'%农户贷款%')
  547. # 2.贷款额度>100元
  548. # 3.贷款状态不为“结清”"
  549. # 非循环贷账户信息汇总
  550. def doFilterCalc(dfx):
  551. dfx = dfx.replace('--', 0)
  552. return dfx;
  553. # 科学计数法转换
  554. def replaceAmt(dfx):
  555. return dfx.str.replace(',', '')
  556. # 非循环贷账户信息汇总
  557. def parseLoanAccountInfoSum(dfObj):
  558. df = dfObj["df"];
  559. if not df.empty:
  560. loanAccountInfoSumDf = df[2:3];
  561. loanAccountInfoSumDf = doFilterCalc(loanAccountInfoSumDf); # 替换--为0
  562. loanAccountInfoSumDf = loanAccountInfoSumDf.reset_index(drop=True)
  563. row0 = loanAccountInfoSumDf.loc[0,:]
  564. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户管理机构数'] = int(row0[0])
  565. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数'] = int(row0[1])
  566. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户授信总额'] = int(utils.replaceAmt(row0[2]))
  567. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户余额'] = int(utils.replaceAmt(row0[3]))
  568. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户6月平均应还款'] = int(utils.replaceAmt(row0[4]))
  569. # 循环额度下分账户
  570. def parseCycleCreditAccountInfoSum(dfObj):
  571. df = dfObj["df"];
  572. if not df.empty:
  573. cycleCreditAccountInfoSumDf = df[2:3];
  574. cycleCreditAccountInfoSumDf = doFilterCalc(cycleCreditAccountInfoSumDf); # 替换--为0
  575. cycleCreditAccountInfoSumDf = cycleCreditAccountInfoSumDf.reset_index(drop=True)
  576. row0 = cycleCreditAccountInfoSumDf.loc[0,:]
  577. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户管理机构数'] = int(row0[0])
  578. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户账户数'] = int(row0[1])
  579. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户授信总额'] = int(utils.replaceAmt(row0[2]))
  580. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户余额'] = int(utils.replaceAmt(row0[3]))
  581. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户6月平均应还款'] = int(utils.replaceAmt(row0[4]))
  582. # 循环贷账户信息
  583. def parseCyleLoanAccountInfoSum(dfObj):
  584. df = dfObj["df"];
  585. if not df.empty:
  586. cycleLoanAccountInfoSumDf = df[2:3];
  587. cycleLoanAccountInfoSumDf = doFilterCalc(cycleLoanAccountInfoSumDf); # 替换--为0
  588. cycleLoanAccountInfoSumDf = cycleLoanAccountInfoSumDf.reset_index(drop=True)
  589. row0 = cycleLoanAccountInfoSumDf.loc[0,:]
  590. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户管理机构数'] = int(row0[0])
  591. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户账户数'] = int(row0[1])
  592. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户授信总额'] = int(utils.replaceAmt(row0[2]))
  593. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户余额'] = int(utils.replaceAmt(row0[3]))
  594. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户6月平均应还款'] = int(utils.replaceAmt(row0[4]))
  595. # 解析贷记卡信息汇总,包含准贷记卡
  596. def parseCreditCardInfoSum(dfObj):
  597. df = dfObj["df"];
  598. if not df.empty:
  599. creditCardInfoSumDf = df[2:3];
  600. creditCardInfoSumDf = doFilterCalc(creditCardInfoSumDf); # 替换--为0
  601. creditCardInfoSumDf = creditCardInfoSumDf.reset_index(drop=True)
  602. row0 = creditCardInfoSumDf.loc[0, :]
  603. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡发卡机构数'] = int(row0[0])
  604. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡账户数'] = int(row0[1])
  605. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡授信总额'] = int(utils.replaceAmt(row0[2]))
  606. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡单家机构最高授信额'] = int(utils.replaceAmt(row0[3]))
  607. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡单家机构最低授信额'] = int(utils.replaceAmt(row0[4]))
  608. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡已用额度'] = int(utils.replaceAmt(row0[5]))
  609. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡最近6个月平均使用额度'] = int(utils.replaceAmt(row0[6]))
  610. # 解析贷记卡信息汇总,包含准贷记卡
  611. def parseCreditCardInfoSumZ(dfObj):
  612. df = dfObj["df"];
  613. if not df.empty:
  614. creditCardInfoSumDfZ = df[2:3];
  615. creditCardInfoSumDfZ = doFilterCalc(creditCardInfoSumDfZ);
  616. creditCardInfoSumDfZ = creditCardInfoSumDfZ.reset_index(drop=True)
  617. row0 = creditCardInfoSumDfZ.loc[0, :]
  618. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡发卡机构数'] = int(row0[0])
  619. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡账户数'] = int(row0[1])
  620. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡授信总额'] = int(utils.replaceAmt(row0[2]))
  621. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡单家机构最高授信额'] = int(utils.replaceAmt(row0[3]))
  622. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡单家机构最低授信额'] = int(utils.replaceAmt(row0[4]))
  623. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡已用额度'] = int(utils.replaceAmt(row0[5]))
  624. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡最近6个月平均使用额度'] = int(utils.replaceAmt(row0[6]))
  625. #相关还款责任
  626. def parseRepaymentSum(dfObj):
  627. df = dfObj["df"];
  628. if not df.empty:
  629. row4 = df.loc[4,:].dropna().reset_index(drop=True)#第4行 为个人
  630. row8 = None
  631. if df.index.size ==9:
  632. row8 = df.loc[8,:].dropna().reset_index(drop=True)#第8行 为企业
  633. perAccountNum = 0;#个人账户数
  634. orgAccountNum = 0; # 企业账户数
  635. totalAccountNum = 0;#总账户数
  636. guaranteeAccountNum = 0;#相关还款责任总账户数-担保责任
  637. otherAccountNum =0;#相关还款责任总账户数-其他
  638. perGuaranteeAmt = 0#个人担保金额及其他
  639. orgGuaranteeAmt = 0#企业担保金额及其他
  640. totalGuaranteeAmt = 0;#总担保金额
  641. guaranteeAmt = 0;#相关还款责任总担保金额
  642. otherPaymentAmt = 0;#其他还款责任金额
  643. perGuaranteeBalance = 0 # 个人担保余额及其他
  644. orgGuaranteeBalance = 0 # 企业担保余额及其他
  645. totalGuaranteeBalance = 0;#总担保余额
  646. guaranteeBalance = 0;#相关还款责任总担保余额
  647. otherPaymentBalance = 0; # 其他还款责任余额
  648. #计算总账户数
  649. if row4[0] !="--":
  650. perAccountNum=perAccountNum+utils.toInt(row4[0])
  651. guaranteeAccountNum = guaranteeAccountNum + utils.toInt(row4[0])#个人担保责任账户数
  652. if row4[3] !="--":
  653. perAccountNum = perAccountNum + utils.toInt(row4[3])#其他
  654. otherAccountNum = otherAccountNum + utils.toInt(row4[3]) # 其他
  655. if row8 != None:
  656. if row8[0] != "--":
  657. orgAccountNum = orgAccountNum + utils.toInt(row8[0])
  658. guaranteeAccountNum = guaranteeAccountNum + utils.toInt(row8[0])#企业担保责任账户数
  659. if row8[3] != "--":
  660. orgAccountNum = orgAccountNum + utils.toInt(row8[3])#其他
  661. otherAccountNum = otherAccountNum + utils.toInt(row8[3]) # 其他
  662. totalAccountNum = perAccountNum+orgAccountNum
  663. #计算担保金额
  664. if row4[1] !="--":
  665. perGuaranteeAmt=perGuaranteeAmt+utils.replaceAmt(row4[1])#担保
  666. guaranteeAmt = guaranteeAmt + utils.replaceAmt(row4[1]) # 担保
  667. if row4[4] !="--":
  668. perGuaranteeAmt = perGuaranteeAmt + utils.replaceAmt(row4[4])#其他
  669. otherPaymentAmt = otherPaymentAmt + utils.replaceAmt(row4[4]) # 其他
  670. if row8 != None:
  671. if row8[1] != "--":
  672. orgGuaranteeAmt = orgGuaranteeAmt + utils.replaceAmt(row8[1])#担保
  673. guaranteeAmt = guaranteeAmt + utils.replaceAmt(row8[1]) # 担保
  674. if row8[4] != "--":
  675. orgGuaranteeAmt = orgGuaranteeAmt + utils.replaceAmt(row8[4])#其他
  676. otherPaymentAmt = otherPaymentAmt + utils.replaceAmt(row8[4]) # 其他
  677. totalGuaranteeAmt = perGuaranteeAmt + orgGuaranteeAmt
  678. # 计算余额
  679. if row4[2] !="--":
  680. perGuaranteeBalance=perGuaranteeBalance+utils.replaceAmt(row4[2])
  681. guaranteeBalance=guaranteeBalance+utils.replaceAmt(row4[2])#个人担保余额
  682. if row4[5] !="--":
  683. perGuaranteeBalance = perGuaranteeBalance + utils.replaceAmt(row4[5])#其他
  684. otherPaymentBalance = otherPaymentBalance + utils.replaceAmt(row4[5]) # 其他
  685. if row8 != None:
  686. if row8[2] != "--":
  687. orgGuaranteeBalance = orgGuaranteeBalance + utils.replaceAmt(row8[2])
  688. guaranteeBalance = guaranteeBalance + utils.replaceAmt(row8[2])#企业担保余额
  689. if row8[5] != "--":
  690. orgGuaranteeBalance = orgGuaranteeBalance + utils.replaceAmt(row8[5])
  691. otherPaymentBalance = otherPaymentBalance + utils.replaceAmt(row8[5]) # 其他
  692. totalGuaranteeBalance = perGuaranteeBalance + orgGuaranteeBalance
  693. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数(担保+其他+个人+企业)'] =totalAccountNum
  694. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额+总还款责任金额(个人+企业)'] =totalGuaranteeAmt
  695. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额+总其他余额(个人+企业)'] =totalGuaranteeBalance
  696. if totalGuaranteeAmt !=0:
  697. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额+总其他余额(个人+企业)/相关还款责任账户总担保金额+总其他金额(个人+企业)'] =\
  698. round(totalGuaranteeBalance / totalGuaranteeAmt, 2)
  699. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任担保总账户数-个人'] =perAccountNum
  700. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-个人'] =perGuaranteeAmt
  701. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-个人'] =perGuaranteeBalance
  702. if perGuaranteeBalance !=0:
  703. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-个人/相关还款责任总担保金额-个人'] = round(perGuaranteeBalance/perGuaranteeBalance,2)
  704. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数-企业'] =orgAccountNum
  705. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-企业'] =orgGuaranteeAmt
  706. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-企业'] =orgGuaranteeBalance
  707. if orgGuaranteeAmt!=0:
  708. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-企业/相关还款责任总担保金额-企业'] = round(orgGuaranteeBalance/orgGuaranteeAmt,2)
  709. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数-担保责任'] =guaranteeAccountNum
  710. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-担保责任'] =guaranteeAmt
  711. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额-担保责任'] =guaranteeBalance
  712. if guaranteeAmt!=0:
  713. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-担保责任/相关还款责任总担保金额-担保责任'] =round(guaranteeBalance/guaranteeAmt,2)
  714. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数-其他'] =otherAccountNum
  715. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-其他'] =otherPaymentAmt
  716. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-其他'] =otherPaymentBalance
  717. if otherPaymentAmt!=0:
  718. briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额-其他/相关还款责任账户总担保金额-其他'] =round(otherPaymentBalance/otherPaymentAmt,2)
  719. #解析公共信息汇总
  720. def parsePublicInfoBrief(dfObj):
  721. df = dfObj["df"];
  722. if not df.empty:
  723. publicInfoBrief = df[1:6];
  724. publicInfoBrief = publicInfoBrief.reset_index(drop=True)
  725. row0 = publicInfoBrief.loc[0, :]
  726. row1 = publicInfoBrief.loc[1, :]
  727. row2 = publicInfoBrief.loc[2, :]
  728. row3 = publicInfoBrief.loc[3, :]
  729. publicInfoBriefDf.loc[publicInfoBriefIndex, '欠税信息-记录数'] = int(row0[1])
  730. publicInfoBriefDf.loc[publicInfoBriefIndex, '欠税信息-涉及金额'] = int(utils.replaceAmt(row0[2]))
  731. publicInfoBriefDf.loc[publicInfoBriefIndex, '民事判决信息-记录数'] = int(row1[1])
  732. publicInfoBriefDf.loc[publicInfoBriefIndex, '民事判决信息-涉及金额'] = int(utils.replaceAmt(row1[2]))
  733. publicInfoBriefDf.loc[publicInfoBriefIndex, '强制执行信息-记录数'] = int(row2[1])
  734. publicInfoBriefDf.loc[publicInfoBriefIndex, '强制执行信息-涉及金额'] = int(utils.replaceAmt(row2[2]))
  735. publicInfoBriefDf.loc[publicInfoBriefIndex, '行政处罚信息-记录数'] = int(row3[1])
  736. publicInfoBriefDf.loc[publicInfoBriefIndex, '行政处罚信息-涉及金额'] = int(utils.replaceAmt(row3[2]))
  737. #解析查询信息汇总
  738. def parseQueryRecordSum(dfObj):
  739. df = dfObj["df"];
  740. if not df.empty:
  741. queryRecordSumDfTmp = df[2:3];
  742. queryRecordSumDfTmp = queryRecordSumDfTmp.reset_index(drop=True)
  743. row0 = queryRecordSumDfTmp.loc[0, :]
  744. queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询机构数-贷款审批'] =int(row0[0])
  745. queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询机构数-信用卡审批'] =int(row0[1])
  746. queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询次数-贷款审批'] =int(row0[2])
  747. queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询次数-信用卡审批'] =int(row0[3])
  748. queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询次数-本人查询'] =int(row0[4])
  749. queryRecordSumDf.loc[queryRecordSumIndex, '近2年内的查询次数-贷后管理'] =int(row0[5])
  750. queryRecordSumDf.loc[queryRecordSumIndex, '近2年内的查询次数-担保资格审查'] =int(row0[6])
  751. # 解析查询记录明细
  752. def parseQueryInfoDetail(dfObj):
  753. df = dfObj["df"];
  754. reportTime = queryInfo["reportTime"];
  755. if not df.empty:
  756. df = utils.replaceDateCol(df)
  757. df = df[1:df.index.size] # 去掉表头
  758. queryRecordDetailDf.loc[queryRecordDetailIndex, '近1月查询次数'] =qip.getLastMonthQueryTimes(df, 1, "",reportTime)
  759. queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询次数'] =qip.getLastMonthQueryTimes(df, 3, "",reportTime)
  760. queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询次数'] =qip.getLastMonthQueryTimes(df, 6, "",reportTime)
  761. queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询次数'] =qip.getLastMonthQueryTimes(df, 12, "",reportTime)
  762. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近1个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 1, "", reportTime)
  763. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近3个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 3, "", reportTime)
  764. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近6个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 6, "", reportTime)
  765. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近12个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 12, "", reportTime)
  766. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 24, "", reportTime)
  767. queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询次数贷款审批'] =qip.getLastMonthQueryTimes(df, 3, consts.loanApprove, reportTime)
  768. queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询次数信用卡审批'] =qip.getLastMonthQueryTimes(df, 3, consts.creditCard, reportTime)
  769. queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询次数贷款审批'] =qip.getLastMonthQueryTimes(df, 6, consts.loanApprove, reportTime)
  770. queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询次数信用卡审批'] = qip.getLastMonthQueryTimes(df, 6, consts.creditCard, reportTime)
  771. queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询次数贷款审批'] = qip.getLastMonthQueryTimes(df, 12, consts.loanApprove, reportTime)
  772. queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询次数信用卡审批'] =qip.getLastMonthQueryTimes(df, 12, consts.creditCard, reportTime)
  773. queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询机构数贷款审批'] =qip.getLastMonthQueryOrgTimes(df, 3, consts.loanApprove, reportTime)
  774. queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询机构数信用卡审批'] =qip.getLastMonthQueryOrgTimes(df, 3, consts.creditCard, reportTime)
  775. queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询机构数贷款审批'] =qip.getLastMonthQueryOrgTimes(df, 6, consts.loanApprove, reportTime)
  776. queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询机构数信用卡审批'] = qip.getLastMonthQueryOrgTimes(df, 6, consts.creditCard,reportTime)
  777. queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询机构数贷款审批'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.loanApprove, reportTime)
  778. queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询机构数信用卡审批'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.creditCard,reportTime)
  779. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近6个月担保资格审查查询次数'] = qip.getLastMonthQueryOrgTimes(df, 6, consts.insuranceAprove,reportTime)
  780. queryRecordDetailDf.loc[queryRecordDetailIndex, '近12个月担保资格审查查询次数'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.insuranceAprove,reportTime)
  781. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近6个月贷后管理查询次数'] = qip.getLastMonthQueryOrgTimes(df, 6, consts.loanAfterMgr,reportTime)
  782. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近12个月贷后管理查询次数'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.loanAfterMgr,reportTime)
  783. queryRecordDetailDf.loc[queryRecordDetailIndex, '最后一次查询距离现在的月数贷款审批'] = qip.getLastTimeQueryMonth(df, consts.loanApprove,reportTime)
  784. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月贷后管理查询次数'] = qip.getLastMonthQueryTimes(df, 24, consts.loanAfterMgr, reportTime)
  785. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月贷款审批审批次数'] = qip.getLastMonthQueryTimes(df, 24, consts.loanApprove, reportTime)
  786. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月信用卡审批查询次数'] = qip.getLastMonthQueryTimes(df, 24, consts.creditCard,reportTime)
  787. queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月担保资格审查查询次数'] = qip.getLastMonthQueryTimes(df, 24, consts.insuranceAprove,reportTime)
  788. #解析住房公积金
  789. def parseHousingFundRcd(df):
  790. if not df.empty:
  791. lastHousingFundRcdDf = df.sort_values(by=["信息更新日期"] , ascending=(False)).reset_index(drop=True)
  792. lastHousingFundRcdDf = lastHousingFundRcdDf[0:1]#最新
  793. row1 = lastHousingFundRcdDf.loc[0,:].dropna().reset_index(drop=True)
  794. housingFundRcdDf.loc[housingFundRcdIndex, '参缴地'] =row1[1]
  795. housingFundRcdDf.loc[housingFundRcdIndex, '参缴日期'] =row1[2]
  796. housingFundRcdDf.loc[housingFundRcdIndex, '初缴月份'] =row1[3]#初缴日期
  797. housingFundRcdDf.loc[housingFundRcdIndex, '缴至月份'] =row1[4]
  798. housingFundRcdDf.loc[housingFundRcdIndex, '缴费状态'] =row1[5]
  799. housingFundRcdDf.loc[housingFundRcdIndex, '月缴存额'] =row1[6]
  800. housingFundRcdDf.loc[housingFundRcdIndex, '个人存缴比例'] =row1[7]
  801. housingFundRcdDf.loc[housingFundRcdIndex, '单位存缴比例'] =row1[8]
  802. housingFundRcdDf.loc[housingFundRcdIndex, '缴费单位'] =row1[9]#扣缴单位
  803. housingFundRcdDf.loc[housingFundRcdIndex, '信息更新日期'] =row1[10]
  804. reportTime = queryInfo["reportTime"];
  805. lastDateStr = utils.getLastMonthDate(reportTime,12)
  806. avgHousingFundDf = df[df['缴至月份']>=lastDateStr]
  807. housingFundRcdDf.loc[housingFundRcdIndex, '最近1年公积金平均值'] = round(np.mean(avgHousingFundDf['月缴存额']),2)
  808. lastDateStr = utils.getLastMonthDate(reportTime, 12*3)
  809. avgHousingFundDf = df[df['缴至月份'] >= lastDateStr]
  810. housingFundRcdDf.loc[housingFundRcdIndex, '最近3年公积金平均值']= round(np.mean(avgHousingFundDf['月缴存额']),2)
  811. #解析贷款还款记录指标
  812. def parseLoanMergeAndPayRecordDf(df,payRcdDf):
  813. if not df.empty and not payRcdDf.empty:
  814. #正常
  815. normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')]
  816. overduePayRcdDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)]
  817. overduePayRcdDf = utils.replacePayRcdStatus(overduePayRcdDf)
  818. #计算当前贷款,为还款记录的最后一期 0529
  819. curOverduePayRcdDf=overduePayRcdDf.sort_values(by=["账户编号", "还款日期"], ascending=(True, False))
  820. curOverduePayRcdDf = curOverduePayRcdDf.groupby(['账户编号']).head(1)
  821. curOverduePayRcdDf = curOverduePayRcdDf[curOverduePayRcdDf['还款状态'] > 0]
  822. #临时保存,不用过滤还款状态为0的
  823. payRcdMaxOverdueDf = overduePayRcdDf;
  824. #所有逾期的记录
  825. # overduePayRcdDf = overduePayRcdDf[overduePayRcdDf['还款状态']>0]
  826. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期账户数'] = curOverduePayRcdDf['账户编号'].unique().size
  827. if normalDf.index.size>0:
  828. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期账户数占比'] = round(loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期账户数']/normalDf.index.size,3)
  829. #存在逾期的贷款账户 非结清的过滤出逾期的账户号
  830. overdueLoanDf = normalDf[normalDf['账户编号'].isin(curOverduePayRcdDf['账户编号'].values)]
  831. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期机构数'] = overdueLoanDf['管理机构'].unique().size
  832. if normalDf['管理机构'].unique().size>0:
  833. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期机构数占比'] = round(loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期机构数'] / normalDf['管理机构'].unique().size,2)
  834. #还款记录按日期排序最近3笔的最大逾期期数
  835. loanAccountInfoDf.loc[loanAccountInfoIndex, '近1月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf,1);
  836. loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 3);
  837. loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 6);
  838. loanAccountInfoDf.loc[loanAccountInfoIndex, '近9月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 9);
  839. loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 12);
  840. loanAccountInfoDf.loc[loanAccountInfoIndex, '近24月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 24);
  841. reportTime = queryInfo["reportTime"]
  842. loanAccountInfoDf.loc[loanAccountInfoIndex, '近24月贷款最大逾期距离现在的月数'] = prp.getPayRcdMaxOverdueNumMonth(payRcdMaxOverdueDf,normalDf,reportTime, 24);
  843. payStatus= ["G","D","C","N","M","1","2","3","4","5","6","7"]
  844. # 贷款24期还款记录次数 剔除结清 转出 呆账
  845. payRcdTimesDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)]
  846. payRcdTimesDf = payRcdTimesDf.sort_values(by=["账户编号", "还款日期"], ascending=(True, False))
  847. payRcdTimesDf = payRcdTimesDf.groupby(['账户编号']).head(24)
  848. #从“贷款信息”中提取,剔除“账户状态”为结清、转出、呆账、呆帐后,各账户的还款次数统计“24个月(账户)还款状态”包含"G","D","C","N","M"及数字的个数,MAX(各账户的还款次数)
  849. payRcdTimesDf = payRcdTimesDf[payRcdTimesDf['还款状态'].isin(payStatus)]
  850. payRcdTimes = payRcdTimesDf.groupby(['账户编号'])['还款状态'].count()
  851. loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款24期还款记录次数'] = np.max(payRcdTimes)
  852. #解析信贷交易明细-特殊交易
  853. def parseSpecialTrade(df):
  854. if not df.empty:
  855. creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '当前用户发生特殊交易的严重程度'] = np.max(df['严重程度'])#加工的指标
  856. maxChangeMonthIndex = np.argmax(np.abs(df['变更月数']))
  857. meanMonthValue = np.mean(np.abs(df['变更月数']))
  858. row0 = df.loc[maxChangeMonthIndex, :]
  859. settleDf = df[(df['特殊交易类型']=='提前结清') | (df['特殊交易类型']=='提前还款')]
  860. debtDf = df[(df['特殊交易类型'] == '以资抵债')]
  861. creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户发生特殊交易变更月数的最大差值'] = row0[3]
  862. creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户发生特殊交易变更月数的平均差值'] = round(meanMonthValue,2)
  863. creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户特殊交易涉及的发生金额的最大值'] = np.max(df['发生金额'])
  864. creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户特殊交易涉及的发生金额的平均值'] = round(np.mean(df['发生金额']),2)
  865. creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户所有帐户发生提前还款交易的次数统计'] = settleDf.index.size
  866. creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户所有帐户发生不良特殊交易的次数统计'] = debtDf.index.size;
  867. #信贷交易明细-非循环贷账户
  868. def parseLoanAccountInfo(df):
  869. if not df.empty:
  870. loanAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数'])
  871. normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')].reset_index(drop=True)
  872. normalDf = normalDf[0:loanAccountNum]#根据非循环贷账户数进行计算进行截取
  873. creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '本月应还款(合计)'] = np.sum(normalDf['本月应还款'])
  874. creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '本月实还款(合计)'] = np.sum(normalDf['本月实还款'])
  875. creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '最近一次还款日期'] = np.max(normalDf['最近一次还款日期'])
  876. creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '当前一共逾期期数'] = np.sum(normalDf['当前逾期期数'])
  877. creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '当前一共逾期总额'] = np.sum(normalDf['当前逾期总额'])
  878. creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期31-60天未还本金(合计)'] = np.sum(normalDf['逾期31-60天未还本金'])
  879. creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期61-90天未还本金(合计)'] = np.sum(normalDf['逾期61-90天未还本金'])
  880. creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期91-180天未还本金(合计)'] = np.sum(normalDf['逾期91-180天未还本金'])
  881. creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期180天以上未还本金(合计)']= np.sum(normalDf['逾期180天以上未还本金'])
  882. #信贷交易明细-循环额度分账户
  883. def parseCycleCreditAccountInfo(df):
  884. if not df.empty:
  885. normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')].reset_index(drop=True)
  886. loanAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数'])
  887. cycleCreditAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户账户数'])
  888. normalDf = normalDf[loanAccountNum:(loanAccountNum + cycleCreditAccountNum)]
  889. if not normalDf.empty:
  890. creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '本月应还款(合计)'] = np.sum(normalDf['本月应还款'])
  891. creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '本月实还款(合计)'] = np.sum(normalDf['本月实还款'])
  892. creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '最近一次还款日期'] = np.max(normalDf['最近一次还款日期'])
  893. creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '当前一共逾期期数'] = np.sum(normalDf['当前逾期期数'])
  894. creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '当前一共逾期总额'] = np.sum(normalDf['当前逾期总额'])
  895. creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期31-60天未还本金(合计)'] = np.sum(normalDf['逾期31-60天未还本金'])
  896. creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期61-90天未还本金(合计)'] = np.sum(normalDf['逾期61-90天未还本金'])
  897. creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期91-180天未还本金(合计)'] = np.sum(normalDf['逾期91-180天未还本金'])
  898. creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期180天以上未还本金(合计)']= np.sum(normalDf['逾期180天以上未还本金'])
  899. #信贷交易明细-循环贷账户
  900. def parseCycleLoanAccountInfo(df):
  901. if not df.empty:
  902. normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')]
  903. loanAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数'])
  904. cycleCreditAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户账户数'])
  905. cycleAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户账户数'])
  906. normalDf = normalDf[(loanAccountNum+cycleCreditAccountNum):normalDf.index.size]
  907. if not normalDf.empty:
  908. creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '本月应还款(合计)'] = np.sum(normalDf['本月应还款'])
  909. creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '本月实还款(合计)'] = np.sum(normalDf['本月实还款'])
  910. creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '最近一次还款日期'] = np.max(normalDf['最近一次还款日期'])
  911. creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '当前一共逾期期数'] = np.sum(normalDf['当前逾期期数'])
  912. creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '当前一共逾期总额'] = np.sum(normalDf['当前逾期总额'])
  913. creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期31-60天未还本金(合计)'] = np.sum(normalDf['逾期31-60天未还本金'])
  914. creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期61-90天未还本金(合计)'] = np.sum(normalDf['逾期61-90天未还本金'])
  915. creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期91-180天未还本金(合计)'] = np.sum(normalDf['逾期91-180天未还本金'])
  916. creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期180天以上未还本金(合计)']= np.sum(normalDf['逾期180天以上未还本金'])
  917. #解析贷款账户信息指标
  918. def parseLoanMergeDf(df):
  919. if not df.empty:
  920. sortDf = df.sort_values(by=["账户关闭日期","借款金额(本金)"] , ascending=(False,False))
  921. sortDf = sortDf[sortDf['账户状态'] == '结清'];
  922. sortDf = sortDf.reset_index(drop=True)
  923. if not sortDf.empty:
  924. row0 = sortDf.loc[0, :]
  925. loanAccountInfo["lastSettleLoanAmt"] = row0['借款金额(本金)']
  926. loanAccountInfoDf.loc[loanAccountInfoIndex, '最近一笔结清贷款的贷款金额'] = row0['借款金额(本金)']
  927. openDate = dfParser.formatDate(row0['开立日期'])
  928. loanAccountInfoDf.loc[loanAccountInfoIndex, '最近一笔结清贷款的发放距今月数'] = utils.difMonthReportTime(openDate,queryInfo["reportTime"])
  929. settleDate = dfParser.formatDate(row0['账户关闭日期'])
  930. loanAccountInfoDf.loc[loanAccountInfoIndex, '最近一笔结清贷款的结清距今月数'] = utils.difMonthReportTime(settleDate,queryInfo["reportTime"])
  931. loanAccountInfoDf.loc[loanAccountInfoIndex, '历史贷款总法人机构数'] = df['管理机构'].unique().size
  932. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前同时在用的贷款机构数'] = df[df['余额(本金)']>0]['管理机构'].unique().size
  933. statusDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出')]
  934. bankDf = statusDf[statusDf['管理机构'].str.contains('银行')]
  935. #没有记录
  936. if statusDf.index.size==0:
  937. isNotBankCust = -1
  938. else:
  939. if bankDf.index.size >0:#有一条以上不为结清,请包含银行
  940. isNotBankCust = 1;
  941. else:
  942. isNotBankCust = 0;
  943. loanAccountInfoDf.loc[loanAccountInfoIndex, '是否有非银行贷款客户'] = isNotBankCust
  944. #最严重的五级分类
  945. # fiveType = ""
  946. # for fiveTypeTmp in consts.fiveType:
  947. # fiveTypeDf = statusDf[statusDf['五级分类']==fiveTypeTmp];
  948. # if not fiveTypeDf.empty:
  949. # fiveType = fiveTypeTmp;
  950. # break;
  951. # loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款五级分类'] = fiveType
  952. #当前贷款LTV
  953. # 从“贷款信息”中提取,剔除“账户状态”为结清及转出,并剔除“账户状态”为呆账且本金余额 = 0
  954. # 的记录后,SUM(本金余额) / SUM(贷款本金)
  955. # 如本金余额为空和贷款本金为0或为空,则当条记录不计算
  956. loanLtvDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['借款金额(本金)']>0) & (df['余额(本金)']!='--')]
  957. badSetDf = loanLtvDf[~((loanLtvDf['账户状态'] == '呆账') & (loanLtvDf['余额(本金)']==0))]
  958. balanceSum = np.sum(badSetDf['余额(本金)'].astype('int'))
  959. loanAmtSum = np.sum(badSetDf['借款金额(本金)'].astype('int'))
  960. if(loanAmtSum !=0):
  961. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款LTV'] = round(np.divide(balanceSum,loanAmtSum),2)
  962. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最高LTV'] = round(np.max(np.divide(badSetDf['余额(本金)'].astype('int'), badSetDf['借款金额(本金)'].astype('int'))),2)
  963. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最低LTV'] = round(np.min(np.divide(badSetDf['余额(本金)'].astype('int'), badSetDf['借款金额(本金)'].astype('int'))), 2)
  964. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款平均LTV'] = round(np.mean(np.divide(badSetDf['余额(本金)'].astype('int'), badSetDf['借款金额(本金)'].astype('int'))), 2)
  965. #['个人住房商业贷款','个人商用房(含商住两用)贷款','个人住房公积金贷款','房'],
  966. houseLtvList = consts.houseLtvList;
  967. # houseLtvDf = badSetDf[badSetDf['业务种类'].isin(houseLtvList)]
  968. # if not houseLtvDf.empty:
  969. # loanAccountInfoDf.loc[loanAccountInfoIndex, '当前房贷LTV'] = round(np.divide(np.sum(houseLtvDf['余额(本金)'].astype('int')),np.sum(houseLtvDf['借款金额(本金)'].astype('int'))), 2)
  970. #['个人住房贷款','个人商用房(包括商住两用)贷款']
  971. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前房贷LTV'] = lip.getCurLtv(badSetDf, houseLtvList)
  972. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款机构数量'] = loanLtvDf['管理机构'].unique().size
  973. cardLtvList = ['个人汽车消费贷款','车']
  974. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前车贷LTV'] = lip.getCurLtv(badSetDf, cardLtvList)
  975. operateLtvList = ['个人经营性贷款']
  976. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前经营贷LTV'] = lip.getCurLtv(badSetDf, operateLtvList)
  977. consumeLtvList = ['其他个人消费贷款']
  978. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前消费贷LTV'] = lip.getCurLtv(badSetDf, consumeLtvList)
  979. bankLtvList = ['商业银行','外资银行','村镇银行','住房储蓄银行','财务公司']
  980. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前银行贷LTV'] = lip.getCurBankLtv(badSetDf, bankLtvList)
  981. bankLtvList = ['消费金融公司','汽车金融公司','信托公司']# TODO
  982. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前消金贷LTV'] = lip.getCurBankLtv(badSetDf, bankLtvList)
  983. smallLoanLtvList = ['小额信贷公司']
  984. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前小贷LTV'] = lip.getCurBankLtv(badSetDf, smallLoanLtvList)
  985. #当前贷款最大逾期期数
  986. # 从“贷款信息”中提取,剔除“账户状态”为结清、转出、呆账、呆帐后,MAX(每笔贷款的当前逾期期数)
  987. loanOverdueLtvDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')]
  988. if not loanOverdueLtvDf.empty:
  989. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最大逾期期数'] = np.max(loanOverdueLtvDf['当前逾期期数'])
  990. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最大逾期金额'] = np.max(loanOverdueLtvDf['当前逾期总额'])
  991. loanOverdueLtvDf=loanOverdueLtvDf.reset_index(drop=True)
  992. maxOverdueIndex = np.argmax(loanOverdueLtvDf['当前逾期期数'])
  993. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最大逾期期数对应的最大逾期金额'] = loanOverdueLtvDf.loc[maxOverdueIndex,:]['当前逾期总额']
  994. loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月开户最高贷款本金'] = lip.getLastLoanAmtMax(df,queryInfo["reportTime"],3)#贷款指标加工单独放到一个文件里
  995. loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月开户最低贷款本金'] = lip.getLastLoanAmtMin(df, queryInfo["reportTime"], 3)
  996. loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月开户平均贷款本金'] = lip.getLastLoanAmtAvg(df, queryInfo["reportTime"], 3)
  997. loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月开户最高贷款本金'] = lip.getLastLoanAmtMax(df, queryInfo["reportTime"], 6)
  998. loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月开户最低贷款本金'] = lip.getLastLoanAmtMin(df, queryInfo["reportTime"], 6)
  999. loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月开户平均贷款本金'] = lip.getLastLoanAmtAvg(df, queryInfo["reportTime"], 6)
  1000. loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月开户最高贷款本金'] = lip.getLastLoanAmtMax(df, queryInfo["reportTime"], 12)
  1001. loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月开户最低贷款本金'] = lip.getLastLoanAmtMin(df, queryInfo["reportTime"], 12)
  1002. loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月开户平均贷款本金'] = lip.getLastLoanAmtAvg(df, queryInfo["reportTime"], 12)
  1003. lastLoanDf = loanOverdueLtvDf;
  1004. if not lastLoanDf.empty:
  1005. loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款最近一次还款日期距今时长'] = lip.getLastPayDateMinDays(lastLoanDf,queryInfo["reportTime"])
  1006. normalDf = df[(df['账户状态'] == '正常') & (df['当前逾期期数'] == 0)]
  1007. #未结清贷款总账户数:账户状态不等于结清和转出的记录数
  1008. notSettleDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出')]
  1009. if not notSettleDf.empty:
  1010. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户数'] = normalDf.index.size
  1011. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户数占比'] = round(normalDf.index.size/notSettleDf.index.size,2)
  1012. #当前未结清贷款余额总和
  1013. # ltvDf = tmpDf[tmpDf['业务种类'].isin(bizTypeList)]
  1014. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清贷款余额总和'] = np.sum(notSettleDf['余额(本金)'])
  1015. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清贷款余额总和'] = np.sum(notSettleDf['余额(本金)'])
  1016. # 当前未结清住房贷款余额总和
  1017. houseDf = notSettleDf[notSettleDf['业务种类'].isin(houseLtvList)]
  1018. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清住房贷款余额总和'] = np.sum(houseDf['余额(本金)'])
  1019. # 当前未结清汽车贷款余额总和
  1020. cardDf = notSettleDf[notSettleDf['业务种类'].isin(cardLtvList)]
  1021. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清汽车贷款余额总和'] = np.sum(cardDf['余额(本金)'])
  1022. # 当前未结清个人经营性贷款余额总和
  1023. operateLtvDf = notSettleDf[notSettleDf['业务种类'].isin(operateLtvList)]
  1024. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清个人经营性贷款余额总和'] = np.sum(operateLtvDf['余额(本金)'])
  1025. # 当前平均每月贷款余额总和
  1026. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前平均每月贷款余额总和'] = round(np.sum(notSettleDf['余额(本金)'])/12,2)
  1027. #当前正常贷款账户余额
  1028. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户余额'] = np.sum(normalDf['余额(本金)'])
  1029. # "从“贷款信息”中提取,剔除结清、转出,当前正常贷款账户余额/未结清贷款总余额(本金余额加总)
  1030. if np.sum(notSettleDf['余额(本金)']) >0:
  1031. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户余额占总余额比'] = round(np.sum(normalDf['余额(本金)'])/np.sum(notSettleDf['余额(本金)']),2)
  1032. settleDf = df[(df['账户状态'] == '结清')]
  1033. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常结清贷款账户数'] = settleDf.index.size
  1034. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常结清贷款账户数占比'] = round(settleDf.index.size/df.index.size,2)
  1035. #贷款24期还款记录次数 TODO
  1036. # 最近3个月个人消费贷款发放额度
  1037. loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款本月实还款金额'] = np.sum(loanOverdueLtvDf['本月应还款'])
  1038. loanAccountInfoDf.loc[loanAccountInfoIndex, '最近3个月个人消费贷款发放额度'] = lip.getLastPerConsumeAmt(df,3,queryInfo["reportTime"])
  1039. loanAccountInfoDf.loc[loanAccountInfoIndex, '最近6个月个人消费贷款发放额度'] = lip.getLastPerConsumeAmt(df, 6,queryInfo["reportTime"])
  1040. loanAccountInfoDf.loc[loanAccountInfoIndex, '最近12个月个人消费贷款发放额度'] = lip.getLastPerConsumeAmt(df, 12,queryInfo["reportTime"])
  1041. #未结清贷款平均剩余还款期数
  1042. payPieDf = notSettleDf[notSettleDf['还款期数']!='--']
  1043. if payPieDf.index.size!=0:
  1044. loanAccountInfoDf.loc[loanAccountInfoIndex, '未结清贷款平均剩余还款期数'] = round(np.sum(payPieDf['剩余还款期数'])/payPieDf.index.size,2)
  1045. # 当前贷款本月应还金额总和
  1046. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款本月应还金额总和'] = np.sum(notSettleDf['本月应还款'])
  1047. # 当前贷款本月实还金额总额
  1048. loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款本月实还金额总额'] = np.sum(notSettleDf['本月实还款'])
  1049. #解析贷记卡账户信息指标
  1050. def parseCreditCardMergeDf(df):
  1051. if not df.empty:
  1052. # 历史信用卡总法人机构数
  1053. # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex,'历史信用卡总法人机构数'] = df['发卡机构'].unique().size
  1054. # creditCardUseDf = df[df['已用额度']>0];
  1055. # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex,'当前同时在用的信用卡机构数'] = creditCardUseDf['发卡机构'].unique().size
  1056. #统一排除
  1057. creditDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '未激活') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')]
  1058. totalAmtDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '未激活') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')]
  1059. #大额专项分期额度(合计)
  1060. # 已用分期金额(合计)
  1061. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '大额专项分期额度(合计)'] = np.sum(creditDf['大额专项分期额度'])
  1062. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '已用分期金额(合计)'] = np.sum(creditDf['已用分期金额'])
  1063. # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex,'贷记卡账户当前总额度'] = cip.getMaxCreditAmt(creditDf)
  1064. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近新发放的3张贷记卡平均额度'] = cip.getAvgCreditAmt(creditDf)
  1065. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡额度使用率超过90%的机构数占比'] = cip.getUseRate(creditDf,df,0.9)
  1066. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡额度使用率超过100%的机构数占比'] = cip.getUseRate(creditDf, totalAmtDf, 1)
  1067. # 从“贷记卡信息”中提取,计算授信额度时剔除销户,计算已用额度时剔除呆账、呆帐、销户后,SUM(各账户已用额度) / SUM(各账户授信额度)
  1068. useCreditDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')]
  1069. totalCreditDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '销户')]
  1070. totalCreditAmt = np.sum(totalCreditDf['账户授信额度'])
  1071. if totalCreditAmt != 0:#授信额度不能为0
  1072. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡账户当前总额度使用率'] = round(np.sum(useCreditDf['已用额度'])/np.sum(totalCreditDf['账户授信额度']),2)
  1073. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡账户最高使用额度总的使用率'] = round(np.sum(useCreditDf['最大使用额']) / np.sum(totalCreditDf['账户授信额度']), 2)
  1074. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡账户近6月平均额度总的使用率'] = round(np.sum(useCreditDf['最近6个月平均使用额度']) / np.sum(totalCreditDf['账户授信额度']), 2)
  1075. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡最大逾期期数'] = np.max(creditDf['当前逾期期数'])#用于计算
  1076. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡最大逾期金额'] = np.max(creditDf['当前逾期总额'])
  1077. if not creditDf.empty:
  1078. creditDf = creditDf.reset_index(drop=True)
  1079. maxOverdueIndex = np.argmax(creditDf['当前逾期期数'])
  1080. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡最大逾期期数对应的最大逾期金额'] = creditDf.loc[maxOverdueIndex,:]['当前逾期总额']
  1081. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月开卡最高额度'] = cip.getLastMonthMaxCreditAmt(df,queryInfo["reportTime"],3)
  1082. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月开卡最低额度'] = cip.getLastMonthMinCreditAmt(df, queryInfo["reportTime"], 3)
  1083. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月开卡平均额度'] = cip.getLastMonthAvgCreditAmt(df, queryInfo["reportTime"], 3)
  1084. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月开卡最高额度'] = cip.getLastMonthMaxCreditAmt(df, queryInfo["reportTime"], 6)
  1085. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月开卡最低额度'] = cip.getLastMonthMinCreditAmt(df, queryInfo["reportTime"], 6)
  1086. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月开卡平均额度'] = cip.getLastMonthAvgCreditAmt(df, queryInfo["reportTime"], 6)
  1087. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月开卡最高额度'] = cip.getLastMonthMaxCreditAmt(df, queryInfo["reportTime"], 12)
  1088. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月开卡最低额度'] = cip.getLastMonthMinCreditAmt(df, queryInfo["reportTime"], 12)
  1089. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月开卡平均额度'] = cip.getLastMonthAvgCreditAmt(df, queryInfo["reportTime"], 12)
  1090. if not creditDf.empty:
  1091. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡最近一次还款日期距今时长'] = cip.getLastPayDateMinDays(creditDf,queryInfo["reportTime"])
  1092. paySo = np.sum(creditDf['本月应还款'])
  1093. if(paySo)!=0:
  1094. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡还款比例'] = round(np.sum(creditDf['本月实还款'])/np.sum(creditDf['本月应还款']),2)
  1095. creditDfTmp = creditDf[creditDf['本月应还款']>0]
  1096. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡最高还款比例'] = round(np.max(np.divide(creditDfTmp['本月实还款'] , creditDfTmp['本月应还款'])), 2)
  1097. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡最低还款比例'] = round(np.min(np.divide(creditDfTmp['本月实还款'] , creditDfTmp['本月应还款'])), 2)
  1098. normalDf = df[(df['币种'] == '人民币元') & (df['账户状态'] == '正常') & (df['当前逾期期数']==0)];
  1099. notCloseDf = df[(df['账户状态'] != '销户')]
  1100. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡账户数'] = normalDf.index.size
  1101. if not notCloseDf.empty and not normalDf.empty:
  1102. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡账户数占比'] = round(normalDf.index.size/notCloseDf.index.size,2)
  1103. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡已用额度'] = np.sum(normalDf['已用额度'])
  1104. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数'] = normalDf[normalDf['已用额度']>0].index.size
  1105. if not creditDf.empty:
  1106. creditUseAmt = np.sum(creditDf['已用额度'])
  1107. if creditUseAmt!=0:
  1108. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡账户余额占总余额比'] = round(np.sum(normalDf['已用额度']) / np.sum(creditDf['已用额度']), 2)
  1109. if notCloseDf.empty:
  1110. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数占比'] = -99
  1111. else:
  1112. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数占比'] = \
  1113. round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数']/notCloseDf.index.size,3)
  1114. #当前正常贷记卡账户余额占总余额比
  1115. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡本月实还金额总和'] = np.sum(creditDf['本月实还款'])
  1116. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡本月应还金额总和'] = np.sum(creditDf['本月应还款'])
  1117. maxAmtDf = df[(df['币种'] == '人民币元')]
  1118. if not maxAmtDf.empty:
  1119. maxAmtDf = maxAmtDf.reset_index(drop=True)
  1120. maxAmtIndex = np.argmax(maxAmtDf['账户授信额度'])
  1121. maxOpenDate = maxAmtDf.loc[maxAmtIndex,:]['开立日期'];
  1122. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '额度最高的人民币贷记卡开卡距今月份数'] = utils.difMonthReportTime(maxOpenDate,queryInfo["reportTime"]);
  1123. # 名下贷记卡数量-状态正常
  1124. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态正常'] = df[(df['账户状态'] != '销户')].index.size
  1125. # 名下贷记卡数量-状态未激活
  1126. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态未激活'] = df[(df['账户状态'] == '未激活')].index.size
  1127. # 名下贷记卡数量-状态异常--异常包含(2-冻结,3-止付,5-呆帐,10-其他)
  1128. abnormalList = ['冻结','止付','呆帐','其他']
  1129. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态异常'] = df[(df['账户状态'].isin(abnormalList))].index.size
  1130. # 名下贷记卡比例-状态正常
  1131. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡比例-状态正常'] = round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态正常'] / df.index.size,2)
  1132. # 名下贷记卡比例-状态未激活
  1133. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡比例-状态未激活'] =round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态未激活'] / df.index.size,2)
  1134. # 名下贷记卡比例-状态异常
  1135. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡比例-状态异常'] = round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态异常'] / df.index.size,2)
  1136. #解析准贷记卡账户信息指标
  1137. def parseCreditCardMergeDfZ(df,payRcd):
  1138. if not df.empty:
  1139. overdueCreditCardRcdDf = payRcd[payRcd['账户编号'].isin(df['账户编号'].values)];
  1140. overdueCreditCardRcdDf = utils.replacePayRcdStatusOverdue(overdueCreditCardRcdDf)
  1141. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '本月应还款(合计)'] = np.nansum(df['透支余额'])
  1142. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '本月实还款(合计)'] = np.nansum(df['本月实还款'])
  1143. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '最近一次还款日期'] = np.max(df['最近一次还款日期'])
  1144. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前一共透支期数'] = cip.getCurOverdueNum(overdueCreditCardRcdDf);
  1145. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前一共透支总额'] = np.nansum(df['透支余额'])
  1146. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '透支180天以上未支付余额(合计)'] = np.nansum(df['透支180天以上未付余额'])
  1147. creditDf = df[(df['账户状态'] != '未激活') & (df['账户状态'] != '销户')]
  1148. if not creditDf.empty:
  1149. totalAmt = np.nansum(creditDf['账户授信额度'])
  1150. creditAmt = np.nansum(creditDf['透支余额'])
  1151. if totalAmt !=0:
  1152. #从“贷记卡信息”中提取,剔除未激活、销户后,所有账户透支金额/所有账户账户授信额度。
  1153. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '全部准贷记卡账户当前总额度使用率']=round(creditAmt/totalAmt,2)
  1154. #从“贷记卡信息”中提取,剔除未激活、销户后,MAX(单账户最高透支金额/单账户授信额度)
  1155. creditMaxDf = creditDf[creditDf['账户授信额度']>0]
  1156. if not creditMaxDf.empty:
  1157. creditMaxDf = creditMaxDf.fillna(0.0)
  1158. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '准贷记卡账户最高使用额度总的使用率'] = round(np.max(np.divide(creditMaxDf['最大透支余额'],creditMaxDf['账户授信额度'])),2)
  1159. creditMaxDf = creditDf[creditDf['最大透支余额'] > 0]
  1160. if not creditMaxDf.empty:
  1161. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡最大透支金额'] = np.max(creditMaxDf['最大透支余额'])
  1162. #从“贷记卡信息”中提取,剔除未激活、销户后,当前透支准贷记卡账户数/总准贷记卡账户数,透支账户判断:透支余额不为0的账户
  1163. creditDfTmp = creditDf[creditDf['透支余额']>0]
  1164. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡透支账户数占比'] = round(creditDfTmp.index.size / creditDf.index.size,2)
  1165. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡本月应还金额总和'] = np.nansum(df['透支余额'])
  1166. creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡本月实还金额总和'] = np.nansum(df['本月实还款'])
  1167. #解析使用率 TODO 使用汇总计算还是使用明细计算
  1168. def parseUseRate():
  1169. # useRateDf.loc[useRateIndex, '贷记卡账户使用率(已用额度/授信总额)']
  1170. # 从“信贷交易授信及负债信息概要”中“非循环贷账户信息汇总”、“循环额度下分账户信息汇总”、“循环贷账户信息汇总”、“贷记卡账户信息汇总”和“准贷记卡账户信息汇总”里提取,SUM(
  1171. # 所有“余额”、“已用额度”和“透支余额”) / SUM(所有“授信总额”和“授信额度”)
  1172. loanUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户余额']
  1173. cycleCreditUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户余额']
  1174. cycleUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户余额']
  1175. creditUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡已用额度']
  1176. creditAmtUseZ = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡已用额度']
  1177. loanTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户授信总额']
  1178. cycleCreditTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户授信总额']
  1179. cycleTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户授信总额']
  1180. creditTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡授信总额']
  1181. creditAmtTotalZ = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡授信总额']
  1182. # if str(loanUseAmt)=="nan":
  1183. # loanUseAmt = 0;
  1184. # if str(cycleCreditUseAmt) == "nan":
  1185. # loanUseAmt = 0;
  1186. # if str(cycleCreditUseAmt) == "nan":
  1187. # loanUseAmt = 0;
  1188. useAmt = loanUseAmt+cycleCreditUseAmt+cycleUseAmt+creditUseAmt+creditAmtUseZ
  1189. totalAmt = loanTotalAmt+cycleCreditTotalAmt+cycleTotalAmt+creditTotalAmt+creditAmtTotalZ
  1190. if totalAmt !=0:
  1191. useRateDf.loc[useRateIndex, '全账户使用率(已用额度/授信总额)'] = round(useAmt / totalAmt,2)
  1192. if loanTotalAmt!=0:
  1193. useRateDf.loc[useRateIndex, '非循环贷账户使用率(已用额度/授信总额)'] = round(loanUseAmt / loanTotalAmt,2)
  1194. if cycleCreditTotalAmt !=0:
  1195. useRateDf.loc[useRateIndex, '循环额度下分账户使用率(已用额度/授信总额)'] = round(cycleCreditTotalAmt / cycleCreditTotalAmt,2)
  1196. if cycleTotalAmt !=0:
  1197. useRateDf.loc[useRateIndex, '循环贷账户使用率(已用额度/授信总额)'] = round(cycleUseAmt / cycleTotalAmt,2)
  1198. if creditTotalAmt !=0:
  1199. useRateDf.loc[useRateIndex, '贷记卡账户使用率(已用额度/授信总额)'] = round(creditUseAmt / creditTotalAmt,2)
  1200. if creditAmtTotalZ !=0:
  1201. useRateDf.loc[useRateIndex, '准贷记卡账户使用率(已用额度/授信总额)'] = round(creditAmtUseZ / creditAmtTotalZ,2)
  1202. #解析开户数
  1203. def parseOpenAccount(loanDf,creditCardDf,creditCardDfZ,recoveryInfoMergeDf,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ):
  1204. reportTime = queryInfo["reportTime"];
  1205. openAccountDf.loc[openAccountIndex, '近3个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,3)+cip.getOpenAccount(creditCardDf,reportTime,3)+cip.getOpenAccount(creditCardDfZ,reportTime,3)
  1206. openAccountDf.loc[openAccountIndex, '近6个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,6)+cip.getOpenAccount(creditCardDf,reportTime,6)+cip.getOpenAccount(creditCardDfZ,reportTime,6)
  1207. openAccountDf.loc[openAccountIndex, '近9个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,9)+cip.getOpenAccount(creditCardDf,reportTime,9)+cip.getOpenAccount(creditCardDfZ,reportTime,9)
  1208. openAccountDf.loc[openAccountIndex, '近12个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,12)+cip.getOpenAccount(creditCardDf,reportTime,12)+cip.getOpenAccount(creditCardDfZ,reportTime,12)
  1209. openAccountDf.loc[openAccountIndex, '近24个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,24)+cip.getOpenAccount(creditCardDf,reportTime,24)+cip.getOpenAccount(creditCardDfZ,reportTime,24)
  1210. openAccountDf.loc[openAccountIndex, '近3个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,3,consts.bankList)
  1211. openAccountDf.loc[openAccountIndex, '近6个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,6,consts.bankList)
  1212. openAccountDf.loc[openAccountIndex, '近9个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,9,consts.bankList)
  1213. openAccountDf.loc[openAccountIndex, '近12个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,12,consts.bankList)
  1214. openAccountDf.loc[openAccountIndex, '近24个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,24,consts.bankList)
  1215. openAccountDf.loc[openAccountIndex, '近3个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,3,"")
  1216. openAccountDf.loc[openAccountIndex, '近6个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,6,"")
  1217. openAccountDf.loc[openAccountIndex, '近9个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,9,"")
  1218. openAccountDf.loc[openAccountIndex, '近12个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,12,"")
  1219. openAccountDf.loc[openAccountIndex, '近24个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,24,"")
  1220. openAccountDf.loc[openAccountIndex, '近3个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,3)
  1221. openAccountDf.loc[openAccountIndex, '近6个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,6)
  1222. openAccountDf.loc[openAccountIndex, '近9个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,9)
  1223. openAccountDf.loc[openAccountIndex, '近12个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,12)
  1224. openAccountDf.loc[openAccountIndex, '近24个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,24)
  1225. openAccountDf.loc[openAccountIndex, '近3个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,3)
  1226. openAccountDf.loc[openAccountIndex, '近6个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,6)
  1227. openAccountDf.loc[openAccountIndex, '近9个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,9)
  1228. openAccountDf.loc[openAccountIndex, '近12个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,12)
  1229. openAccountDf.loc[openAccountIndex, '近24个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,24)
  1230. #从“信贷交易信息明细”中“非循环贷账户”、“循环额度下分账户”、“循环贷账户”、“贷记卡账户”和“准贷记卡账户”里提取,5年里账户还款状态出现“1、2、3、4、5、6、7、D、Z、G、B”的账户数/所有账户数
  1231. overdueLoanPayRcdDf = loanPayRecordMergeDf[loanPayRecordMergeDf['账户编号'].isin(loanDf['账户编号'].values)]
  1232. overdueLoanPayRcdDf = utils.replacePayRcdStatusOverdue(overdueLoanPayRcdDf)
  1233. overdueLoanPayRcdDf = overdueLoanPayRcdDf[overdueLoanPayRcdDf['还款状态'] > 0]
  1234. overdueCreditPayRcdDf = creditCardPayRecordMergeDf[creditCardPayRecordMergeDf['账户编号'].isin(creditCardDf['账户编号'].values)]
  1235. overdueCreditPayRcdDf = utils.replacePayRcdStatusOverdue(overdueCreditPayRcdDf)
  1236. overdueCreditPayRcdDf = overdueCreditPayRcdDf[overdueCreditPayRcdDf['还款状态'] > 0]
  1237. overdueCreditPayRcdDfZ = creditCardPayRecordMergeDfZ[creditCardPayRecordMergeDfZ['账户编号'].isin(creditCardDfZ['账户编号'].values)]
  1238. overdueCreditPayRcdDfZ = utils.replacePayRcdStatusOverdue(overdueCreditPayRcdDfZ)
  1239. overdueCreditPayRcdDfZ = overdueCreditPayRcdDfZ[overdueCreditPayRcdDfZ['还款状态'] > 0]
  1240. loanAccountNum = loanPayRecordMergeDf['账户编号'].unique().size
  1241. creditAccountNum = creditCardPayRecordMergeDf['账户编号'].unique().size
  1242. creditAccountNumZ = creditCardPayRecordMergeDfZ['账户编号'].unique().size
  1243. overdueLoanNum = overdueLoanPayRcdDf['账户编号'].unique().size
  1244. overdueCreditNum = overdueCreditPayRcdDf['账户编号'].unique().size
  1245. overdueCreditNumZ = overdueCreditPayRcdDfZ['账户编号'].unique().size
  1246. if (loanAccountNum+creditAccountNum+creditAccountNumZ) >0:
  1247. openAccountDf.loc[openAccountIndex, '有过逾期记录的账户/全账户数'] = round((overdueLoanNum+overdueCreditNum+overdueCreditNumZ)/(loanAccountNum+creditAccountNum+creditAccountNumZ),2)
  1248. otherPerLoanDf = loanDf[loanDf['业务种类'].isin(consts.bankList)]
  1249. otherPerLoanNum = otherPerLoanDf.index.size;
  1250. overdueOtherPerLoanNum = otherPerLoanDf[otherPerLoanDf['账户编号'].isin(overdueLoanPayRcdDf['账户编号'].values)].index.size;
  1251. if otherPerLoanNum!=0:
  1252. openAccountDf.loc[openAccountIndex, '有过逾期记录的消费金融类账户/全消费金融类账户数'] = round(overdueOtherPerLoanNum/otherPerLoanNum,2)
  1253. if loanAccountNum!=0:
  1254. openAccountDf.loc[openAccountIndex, '有过逾期记录的贷款账户/全贷款账户数'] = round(overdueLoanNum/loanAccountNum,2)
  1255. if creditAccountNum!=0:
  1256. openAccountDf.loc[openAccountIndex, '有过逾期记录的贷记卡账户/全贷记卡账户数'] = round(overdueCreditNum/creditAccountNum,2)
  1257. if creditAccountNumZ!=0:
  1258. openAccountDf.loc[openAccountIndex, '有过透支记录的准贷记卡账户/全准贷记卡账户数']= round(overdueCreditNumZ/creditAccountNumZ,2)
  1259. # 0525新增
  1260. pledgeLoanDf = loanDf[loanDf['担保方式'] =='抵押']
  1261. pledgeCreditCardDf = creditCardDf[creditCardDf['担保方式'] == '抵押']
  1262. pledgeCreditCardDfZ = creditCardDfZ[creditCardDfZ['担保方式'] == '抵押']
  1263. isPledge = "否"
  1264. if pledgeLoanDf.index.size+pledgeCreditCardDf.index.size+pledgeCreditCardDfZ.index.size >0:
  1265. isPledge = "是"
  1266. creditLoanDf = loanDf[loanDf['担保方式'] == '信用/免担保']
  1267. creditCreditCardDf = creditCardDf[creditCardDf['担保方式'] == '信用/免担保']
  1268. creditCreditCardDfZ = creditCardDfZ[creditCardDfZ['担保方式'] == '信用/免担保']
  1269. isCredit = 0
  1270. if creditLoanDf.index.size + creditCreditCardDf.index.size + creditCreditCardDfZ.index.size > 0:
  1271. isCredit = creditLoanDf.index.size + creditCreditCardDf.index.size + creditCreditCardDfZ.index.size
  1272. briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '是否存在担保方式为抵押的贷款'] = isPledge
  1273. briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '担保方式为信用的贷款数量'] = isCredit
  1274. #解析24期还款状态指标
  1275. def parsePayRcdStatus(loanMergeDf, creditCardMergeDf, creditCardMergeDfZ,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ):
  1276. #creditCardPayRecordMergeDf
  1277. # 去掉外币
  1278. creditCardMergeDf = creditCardMergeDf[creditCardMergeDf['币种']=='人民币元']
  1279. creditCardPayRecordMergeDf = creditCardPayRecordMergeDf[creditCardPayRecordMergeDf['账户编号'].isin(creditCardMergeDf['账户编号'].values)]
  1280. reportTime = queryInfo["reportTime"];
  1281. reportTime = str(np.datetime64(reportTime, "M"))+"-02"#06-02,统计24期还款状态报告期,按每月的2号,避免chu'xian
  1282. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近3月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,3)
  1283. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近6月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,6)
  1284. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,12)
  1285. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,24)
  1286. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近6月逾期期数大于或等于“2”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,6)
  1287. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或等于“2”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,12)
  1288. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“2”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,24)
  1289. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近6月逾期期数大于或等于“3”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,6)
  1290. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或等于“3”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,12)
  1291. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“3”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,24)
  1292. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或大等于“4”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,12)
  1293. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“4”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,24)
  1294. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近3月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,3)
  1295. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近6月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,6)
  1296. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,12)
  1297. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,24)
  1298. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近6月逾期期数大于或等于“2”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,6)
  1299. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“2”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,12)
  1300. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“2”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,24)
  1301. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近6月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,6)
  1302. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,12)
  1303. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,24)
  1304. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,12)
  1305. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,24)
  1306. payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近6月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,6)
  1307. payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近12月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,12)
  1308. payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近24月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,24)
  1309. payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近6月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,6)
  1310. payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近12月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,12)
  1311. payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近24月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,24)
  1312. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近3月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,3)\
  1313. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,3)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,3)
  1314. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近6月逾期期数大于或等于“1”的次数'] = \
  1315. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,6)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,6)\
  1316. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,6)
  1317. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“1”的次数'] = \
  1318. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,12)\
  1319. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,12)
  1320. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“1”的次数'] = \
  1321. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,24)\
  1322. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,24)
  1323. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近6月逾期期数大于或等于“2”的次数'] = \
  1324. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,6)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,6)\
  1325. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,2,6)
  1326. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“2”的次数'] = \
  1327. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,12)\
  1328. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,2,12)
  1329. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“2”的次数'] = \
  1330. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,24)\
  1331. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,2,24)
  1332. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近6月逾期期数大于或等于“3”的次数'] = \
  1333. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,6)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,6)\
  1334. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,6)
  1335. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“3”的次数'] = \
  1336. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,12)\
  1337. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,12)
  1338. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“3”的次数'] = \
  1339. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,24)\
  1340. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,24)
  1341. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“4”的次数'] = \
  1342. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,12)\
  1343. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,12)
  1344. payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“4”的次数'] = \
  1345. prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,24)\
  1346. +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,24)
  1347. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24个月是否出现"G"'] = prp.isExistsInd(loanPayRecordMergeDf,reportTime,"G",24)
  1348. payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24个月是否出现"G"'] = prp.isExistsInd(creditCardPayRecordMergeDf,reportTime,"G",24)
  1349. payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近24个月是否出现"G"'] = prp.isExistsInd(creditCardPayRecordMergeDfZ,reportTime,"G",24)
  1350. payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24个月是否出现"Z"'] = prp.isExistsInd(loanPayRecordMergeDf,reportTime,"Z",24)
  1351. payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷款账户过去24个月存在逾期的账户数目'] = prp.getLoanOverdueCount(loanPayRecordMergeDf,reportTime,24)
  1352. payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷款账户过去24个月状态正常账户数目'] = prp.getLoanNormalCount(loanPayRecordMergeDf,reportTime,24)
  1353. payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷记卡账户过去24个月存在逾期的账户数目'] = prp.getLoanOverdueCount(creditCardPayRecordMergeDf,reportTime,24)
  1354. payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷记卡账户过去24个月状态正常的账户数目'] = prp.getLoanNormalCount(creditCardPayRecordMergeDf,reportTime,24)
  1355. payRcdStatusDf.loc[payRcdStatusIndex, '用户所有准贷记卡账户过去24个月存在逾期的账户数目'] = prp.getLoanOverdueCount(creditCardPayRecordMergeDfZ,reportTime,24)
  1356. payRcdStatusDf.loc[payRcdStatusIndex, '用户所有准贷记卡账户过去24个月状态正常的账户数目'] = prp.getLoanNormalCount(creditCardPayRecordMergeDfZ,reportTime,24)
  1357. payRcdStatusDf.loc[payRcdStatusIndex, '用户过去3个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,3)
  1358. payRcdStatusDf.loc[payRcdStatusIndex, '用户过去6个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,6)
  1359. payRcdStatusDf.loc[payRcdStatusIndex, '用户过去12个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,12)
  1360. payRcdStatusDf.loc[payRcdStatusIndex, '用户过去24个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,24)
  1361. #概要信息里的字段,从还款状态计算
  1362. briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户过去5年出现逾期的所有账户数目'] = \
  1363. prp.getLoanOverdueCount(loanPayRecordMergeDf,reportTime,24*5)+prp.getLoanOverdueCount(creditCardPayRecordMergeDf,reportTime,24*5)\
  1364. +prp.getLoanOverdueCount(creditCardPayRecordMergeDfZ,reportTime,24*5)
  1365. #解析贷款还款记录指标
  1366. def parseCreditCardMergeAndPayRecordDf(df,payRcdDf):
  1367. if not df.empty and not payRcdDf.empty:
  1368. # 正常
  1369. normalDf = df[(df['账户状态'] != '未激活') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')]
  1370. if not normalDf.empty:
  1371. overduePayRcdDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)]
  1372. overduePayRcdDf = utils.replacePayRcdStatus(overduePayRcdDf)
  1373. # 计算当前贷款,为还款记录的最后一期 0529
  1374. curOverduePayRcdDf = overduePayRcdDf.sort_values(by=["账户编号", "还款日期"], ascending=(True, False))
  1375. curOverduePayRcdDf = curOverduePayRcdDf.groupby(['账户编号']).head(1)
  1376. curOverduePayRcdDf = curOverduePayRcdDf[curOverduePayRcdDf['还款状态'] > 0]
  1377. # 临时保存,不用过滤还款状态为0的
  1378. payRcdMaxOverdueDf = overduePayRcdDf;
  1379. # overduePayRcdDf = overduePayRcdDf[overduePayRcdDf['还款状态'] > 0]
  1380. # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期账户数'] = curOverduePayRcdDf['账户编号'].unique().size
  1381. #从“贷记卡信息”中提取,剔除“账户状态”为未激活、销户、呆账、呆帐后,“当前信用卡逾期账户数”/未销户贷记卡账户数(剔除“账户状态”为未激活、销户、呆账、呆帐后记录条数)
  1382. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期账户数占比'] = round(curOverduePayRcdDf['账户编号'].unique().size / normalDf.index.size, 2)
  1383. #从“贷记卡信息”中提取,剔除“账户状态”为未激活、销户、呆账、呆帐后,对(当前信用卡逾期账户数)按“开户机构代码”去重统计账户状态为逾期,按按“开户机构代码”去重后的记录条数
  1384. overdueCreditCardDf = normalDf[normalDf['账户编号'].isin(curOverduePayRcdDf['账户编号'].values)]
  1385. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期机构数'] = overdueCreditCardDf['发卡机构'].unique().size
  1386. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期机构数占比'] = round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期机构数'] / normalDf['发卡机构'].unique().size, 2)
  1387. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 3);
  1388. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 6);
  1389. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近9月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 9);
  1390. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 12);
  1391. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近24月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 24);
  1392. reportTime = queryInfo["reportTime"]
  1393. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近24月贷记卡最大逾期距离现在的月数'] = cip.getPayRcdMaxOverdueNumMonth(payRcdMaxOverdueDf,normalDf,reportTime, 24);
  1394. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近3个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,3);
  1395. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近6个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,6);
  1396. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近9个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,9);
  1397. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近12个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,12);
  1398. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近24个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,24);
  1399. payRcdTimesDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)]
  1400. payRcdTimesDf = payRcdTimesDf.sort_values(by=["账户编号", "还款日期"], ascending=(True, False))
  1401. payRcdTimesDf = payRcdTimesDf.groupby(['账户编号']).head(24)
  1402. payStatus = ["G", "D", "C", "N", "M", "1", "2", "3", "4", "5", "6", "7"]
  1403. payRcdTimesDf = payRcdTimesDf[payRcdTimesDf['还款状态'].isin(payStatus)]
  1404. payRcdTimes = payRcdTimesDf.groupby(['账户编号'])['还款状态'].count()
  1405. #从“贷记卡信息”中提取,剔除未激活、销户、呆账、呆帐后,各账户的还款次数统计“24个月(账户)还款状态”包含"G","D","C","N","M"及数字的个数
  1406. creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡24期还款记录次数'] = np.max(payRcdTimes)
  1407. # 解析被追偿信息汇总
  1408. def parseRecoveryInfoMergeDf(df):
  1409. if not df.empty:
  1410. recoveryMaxPayDf = df[df['债权转移时的还款状态'] !='--']
  1411. recoveryStatusCs = df[df['账户状态'] == '催收']
  1412. if not recoveryMaxPayDf.empty:
  1413. briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '债权转移时的最大还款状态'] = np.max(recoveryMaxPayDf['债权转移时的还款状态']);
  1414. briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '债权转移时属于催收状态的账户数'] = recoveryStatusCs.index.size;
  1415. briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '债权转移时属于催收状态的账户数/被追偿信息总数'] = round(recoveryStatusCs.index.size/df.index.size,2);
  1416. #creditTradeDetailDf_recoveryInfo
  1417. # 被追偿账户总数
  1418. creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex,'被追偿账户总数'] = df.index.size;
  1419. creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '被追偿业务种类'] = df['业务种类'].unique().size;
  1420. creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '最新一笔被追偿债券接收时间'] = np.max(df['债权接收日期']);
  1421. creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '总债权金额'] = np.max(df['债权金额']);
  1422. creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '债权转移时的最大还款状态'] = np.max(recoveryMaxPayDf['债权转移时的还款状态']);
  1423. def main(pdf_path):
  1424. # 解析pdf开始
  1425. fileName = os.path.basename(pdf_path)
  1426. print(fileName)
  1427. with pdfplumber.open(pdf_path) as pdf:
  1428. for p in range(0, len(pdf.pages)):
  1429. page = pdf.pages[p]
  1430. # first_page = pdf.pages[1]
  1431. # if p == 3:
  1432. # print(3)
  1433. tables = page.extract_tables();
  1434. for i in range(0, len(tables)):
  1435. table = tables[i]
  1436. df = pd.DataFrame(table);
  1437. # if p==12:
  1438. # logger.info(p)
  1439. if len(keyList) > 1 and i == 0: # 判断是否被分页了
  1440. if not utils.checkHeader(df, allHeaders):
  1441. key = keyList[-1];
  1442. dfObj = dfMap[key]
  1443. # dfObj["nextDf"]=df;
  1444. # 贷款信息 贷记卡信息 强制执行记录
  1445. if key == "loanDfs" or key == "creditCardDfs" or key == "creditCardDfsZ" or key == "forceExecRcdDfs" or key == 'recoveryInfoDfs' or key == "housingFundRcdDfs": # 属于列表
  1446. lastDfObj = dfObj["dfs"][-1];
  1447. lastDfObj["isByPage"] = str(p + 1);
  1448. if len(dfObj["dfs"][-1]["df"].columns) == len(df.columns): # 列数相同
  1449. lastDfObj["df"] = pd.concat([lastDfObj["df"], df], axis=0,ignore_index=True); # 去最后一个进行合并
  1450. # print("key-" + key + "-page-" + str(p + 1) + "-" + "###列数相同####-被分页")
  1451. else:
  1452. # print("key-" + key + "-page-" + str(p + 1) + "-" + "列数不同-被分页")
  1453. lastDfObj["df"] = pd.concat([lastDfObj["df"], df], axis=0, ignore_index=True);
  1454. else: # 查询记录明细 为单个列表
  1455. dfObj["isByPage"] = str(p + 1);
  1456. logger.info(fileName+"#"+key)
  1457. if len(dfObj["df"].columns) == len(df.columns):
  1458. # print("key-" + key + "-page-" + str(p + 1) + "-" + "###列数相同####-被分页")
  1459. dfObj["df"] = pd.concat([dfObj["df"], df], axis=0, ignore_index=True)
  1460. else:
  1461. # print("key-" + key + "-page-" + str(p + 1) + "-" + "列数不同-被分页")
  1462. dfObj["df"] = pd.concat([dfObj["df"], df], axis=0, ignore_index=True)
  1463. # dfObj["nextDf"] = df;
  1464. # 如果列数相等合并df
  1465. continue;
  1466. headerList0 = df.loc[0, :].tolist() # 第0行为表头
  1467. headerList0 = list(filter(None, headerList0))
  1468. headerList1 = []
  1469. if df.index.size>1:
  1470. headerList1 = df.loc[1, :].tolist() # 第1行为表头
  1471. headerList1 = list(filter(None, headerList1))
  1472. if headerList1 == queryInfoDf_header: # 被查询信息 第二行为数据
  1473. queryInfoDf = df;
  1474. dfKey = "queryInfoDf"
  1475. dfMap[dfKey]["df"] = df;
  1476. keyList.append(dfKey);
  1477. elif headerList0 == identity_header: # 身份信息
  1478. identityDf = df[:2] # 截取前2行
  1479. addressDf = df.loc[2:4,:] # 截取3到4行的第一和6
  1480. addressDf = addressDf.reset_index(drop=True)
  1481. mobileDf = utils.replaceDateColIdx(df[5:df.index.size], 5)
  1482. identityDf = pd.concat([identityDf, addressDf], axis=1, ignore_index=True) # 横向合并
  1483. dfKey = "identityDf"
  1484. dfMap[dfKey]["df"] = identityDf;
  1485. keyList.append(dfKey);
  1486. # 组装电话号码df
  1487. dfMap[dfKey]["mobileDf"] = mobileDf
  1488. elif headerList0 == mateDf_header: # 配偶信息
  1489. mateDf = df;
  1490. dfKey = "mateDf"
  1491. dfMap[dfKey]["df"] = df;
  1492. keyList.append(dfKey);
  1493. elif headerList0 == liveInfoDf_header: # 居住信息
  1494. mateDf = df;
  1495. dfKey = "liveInfoDf"
  1496. dfMap[dfKey]["df"] = df;
  1497. keyList.append(dfKey);
  1498. elif headerList0 == occupationInfo_header: # 职业信息 可能存在分页
  1499. occupationDf = df;
  1500. dfKey = "occupationDf"
  1501. dfMap[dfKey]["df"] = df;
  1502. keyList.append(dfKey);
  1503. # elif headerList0 == queryInfoBrief_header0 and headerList1 == queryInfoBrief_header1: # 查询信息概要 第二行为数据
  1504. # queryInfoBriefDf = df;
  1505. # dfKey = "queryInfoBriefDf"
  1506. # dfMap[dfKey]["df"] = df;
  1507. # keyList.append(dfKey);
  1508. elif headerList0 == loanTradeInfo_header: # 信贷交易信息
  1509. loanTradeInfoDf = df;
  1510. dfKey = "loanTradeInfoDf";
  1511. dfMap[dfKey]["df"] = df;
  1512. keyList.append(dfKey);
  1513. elif headerList1 == recoveryInfoSumDf_header: # 被追偿信息汇总
  1514. recoveryInfoSumDf = df;
  1515. dfKey = "recoveryInfoSumDf";
  1516. dfMap[dfKey]["df"] = df;
  1517. keyList.append(dfKey);
  1518. elif headerList1 == badDebtsInfoSumDf_header: # 呆账信息
  1519. badDebtsInfoSumDf = df;
  1520. dfKey = "badDebtsInfoSumDf";
  1521. dfMap[dfKey]["df"] = df;
  1522. keyList.append(dfKey);
  1523. elif headerList1 == overdueInfoSumDf_header: # 逾期透资信息汇总
  1524. overdueInfoSumDf = df;
  1525. dfKey = "overdueInfoSumDf";
  1526. dfMap[dfKey]["df"] = df;
  1527. keyList.append(dfKey);
  1528. elif headerList0 == loanAccountInfoSumDf_header0 and headerList1 == loanAccountInfoSumDf_header1: # 非循环贷账户信息汇总
  1529. loanAccountInfoSumDf = df;
  1530. dfKey = "loanAccountInfoSumDf";
  1531. dfMap[dfKey]["df"] = df;
  1532. keyList.append(dfKey);
  1533. elif headerList0 == creditCardInfoSumDf_header0 and headerList1 == creditCardInfoSumDf_header1: # 贷记卡信息汇总
  1534. creditCardInfoSumDf = df;
  1535. dfKey = "creditCardInfoSumDf";
  1536. dfMap[dfKey]["df"] = df;
  1537. keyList.append(dfKey);
  1538. elif headerList0 == creditCardInfoSumDfZ_header0 and headerList1 == creditCardInfoSumDfZ_header1: # 准贷记卡信息汇总 目前没有数据
  1539. dfKey = "creditCardInfoSumDfZ";
  1540. dfMap[dfKey]["df"] = df;
  1541. keyList.append(dfKey);
  1542. elif headerList0 == repaymentSumDf_header0:#相关还款责任汇总
  1543. dfKey = "repaymentSumDf";
  1544. dfMap[dfKey]["df"] = df;
  1545. keyList.append(dfKey);
  1546. elif headerList0 == publicInfoBriefDf_header0: #公共信息概要
  1547. dfKey = "publicInfoBriefDf";
  1548. dfMap[dfKey]["df"] = df;
  1549. keyList.append(dfKey);
  1550. elif headerList0 == queryRecordSumDf_header0:#查询记录汇总
  1551. dfKey = "queryRecordSumDf";
  1552. dfMap[dfKey]["df"] = df;
  1553. keyList.append(dfKey);
  1554. elif headerList0 == loan_header: # 贷款账户 包括循环贷,非循环贷 循环额度下分账户
  1555. dfKey = "loanDfs";
  1556. dfMap[dfKey]["dfs"].append({"df": df});
  1557. keyList.append(dfKey);
  1558. elif headerList0 == creditCard_header: # 贷记卡账户
  1559. dfKey = "creditCardDfs";
  1560. dfMap[dfKey]["dfs"].append({"df": df});
  1561. keyList.append(dfKey);
  1562. elif headerList0 == creditCardZ_header: # 准贷记卡账户 还不能和贷记卡合并
  1563. dfKey = "creditCardDfsZ";
  1564. dfMap[dfKey]["dfs"].append({"df": df});
  1565. keyList.append(dfKey);
  1566. elif headerList0 == queryRecordDetailDf_header: # 查询记录明细
  1567. dfKey = "queryRecordDetailDf";
  1568. dfMap[dfKey]["df"] = df;
  1569. keyList.append(dfKey);
  1570. elif headerList0 == housingFundRcdDfs_header: # 查询记录明细
  1571. dfKey = "housingFundRcdDfs";
  1572. dfMap[dfKey]["dfs"].append({"df": df});
  1573. keyList.append(dfKey);
  1574. elif headerList0 == forceExecRcdDfs_header: # 强制执行记录
  1575. dfKey = "forceExecRcdDfs";
  1576. dfMap[dfKey]["dfs"].append({"df": df});
  1577. keyList.append(dfKey);
  1578. elif headerList0 == recoveryInfoDfs_header: # 被追偿信息
  1579. dfKey = "recoveryInfoDfs";
  1580. dfMap[dfKey]["dfs"].append({"df": df});
  1581. keyList.append(dfKey);
  1582. # 设置分页
  1583. dfMap[dfKey]["page"] = p + 1;
  1584. logger.info(fileName+"#"+"组装pdf数据完成")
  1585. logger.info(fileName+"#"+"解析基础pdf数据开始")
  1586. # 打印结果解析并构建指标
  1587. for key in dfMap:
  1588. tempDfObjx = dfMap[key];
  1589. if tempDfObjx.__contains__("page"):
  1590. logger.info(fileName+"#"+key + "-page-" + str(tempDfObjx["page"]))
  1591. if tempDfObjx.__contains__("dfs"):
  1592. if key == "loanDfs": # 贷款账户
  1593. for idx in range(0, len(tempDfObjx["dfs"])):
  1594. tempDfObj = tempDfObjx["dfs"][idx];
  1595. loanAccountDfs.append(dfParser.mergeLoanDf(tempDfObj, idx,queryInfo['reportTime']))
  1596. elif key == "creditCardDfs": # 贷记卡账户合并
  1597. for idx in range(0, len(tempDfObjx["dfs"])):
  1598. tempDfObj = tempDfObjx["dfs"][idx];
  1599. tempCreditCardDf = dfParser.mergeCreditCardDf(tempDfObj, idx,queryInfo['reportTime']);
  1600. if tempCreditCardDf!=None:
  1601. creditCardAccountDfs.append(tempCreditCardDf)
  1602. elif key == "creditCardDfsZ": # 贷记卡账户合并
  1603. for idx in range(0, len(tempDfObjx["dfs"])):
  1604. tempDfObj = tempDfObjx["dfs"][idx];
  1605. tempCreditCardDfZ = dfParser.mergeCreditCardDfZ(tempDfObj, idx,queryInfo['reportTime'])
  1606. if tempCreditCardDfZ!=None:
  1607. creditCardAccountDfsZ.append(tempCreditCardDfZ)
  1608. elif key == "recoveryInfoDfs": # 贷记卡账户合并
  1609. for idx in range(0, len(tempDfObjx["dfs"])):
  1610. tempDfObj = tempDfObjx["dfs"][idx];
  1611. recoveryInfoAccountDfs.append(dfParser.mergeRecoveryInfoDf(tempDfObj, idx, queryInfo['reportTime']))
  1612. elif key == "housingFundRcdDfs": # 贷记卡账户合并
  1613. for idx in range(0, len(tempDfObjx["dfs"])):
  1614. tempDfObj = tempDfObjx["dfs"][idx];
  1615. housingFundRcdAccountDfs.append(dfParser.mergeHousingFundRcdDf(tempDfObj, idx, queryInfo['reportTime']))
  1616. else: # 其他
  1617. for tempDfObj in (tempDfObjx["dfs"]):
  1618. if tempDfObj.__contains__("isByPage"):
  1619. logger.info(fileName+"#"+key + "============其他被分页页数============" + str(tempDfObj["isByPage"]))
  1620. # logger.info(fileName+"#"+tempDfObj["df"].values)
  1621. else: # 单笔
  1622. tempDfObj = tempDfObjx;
  1623. if tempDfObj.__contains__("isByPage"):
  1624. logger.info(fileName+"#"+key + "============被分页页数================" + str(tempDfObj["isByPage"]))
  1625. # logger.info(fileName+"#"+tempDfObj["df"].values)
  1626. if key == "queryInfoDf": # 解析被查询信息
  1627. parseQueryInfo(tempDfObj);
  1628. # print("\033[1;31m +查询信息+ \033[0m")
  1629. # print(queryInfo)
  1630. elif key == "identityDf": # 身份信息
  1631. parseIdentity(tempDfObj)
  1632. # print("\033[1;31m +身份信息+ \033[0m")
  1633. # print(identity)
  1634. elif key == "mateDf": # 配偶信息
  1635. parseMate(tempDfObj)
  1636. # print("\033[1;31m +配偶信息+ \033[0m")
  1637. # print(mate)
  1638. elif key == "liveInfoDf": # 居住信息
  1639. parseLiveInfo(tempDfObj)
  1640. # print("\033[1;31m +居住信息+ \033[0m")
  1641. elif key == "occupationDf": # 居住信息
  1642. parseOccupationInfoDf(tempDfObj)
  1643. elif key == "loanTradeInfoDf": # 信贷交易信息提示
  1644. parseLoanTradeInfo(tempDfObj);
  1645. # print("\033[1;31m +信贷交易信息提示+ \033[0m")
  1646. # print(loanTradeInfo)
  1647. elif key == "badDebtsInfoSumDf": # 呆账信息汇总
  1648. parseBadDebtsInfoSumDf(tempDfObj)
  1649. # print("\033[1;31m +呆账信息汇总+ \033[0m")
  1650. # print(overdueBrief)
  1651. elif key == "recoveryInfoSumDf": # 被追偿信息汇总-资产处置和垫款
  1652. parseRecoveryInfoSum(tempDfObj)
  1653. # print("\033[1;31m +资产处置和垫款+ \033[0m")
  1654. # print(overdueBrief)
  1655. elif key == "overdueInfoSumDf": # 逾期(透支)信息汇总
  1656. parseOverdueInfoSum(tempDfObj)
  1657. # print("\033[1;31m +逾期(透支)信息汇总+ \033[0m")
  1658. # print(overdueInfo)
  1659. elif key == "loanAccountInfoSumDf": # 非循环贷账户信息汇总 TODO
  1660. parseLoanAccountInfoSum(tempDfObj)
  1661. elif key == "cycleCreditAccountInfoSumDf":#循环额度
  1662. parseCycleCreditAccountInfoSum(tempDfObj)
  1663. elif key == "cycleLoanAccountInfoSumDf":#循环贷
  1664. parseCyleLoanAccountInfoSum(tempDfObj)
  1665. elif key == "creditCardInfoSumDf":#贷记卡
  1666. parseCreditCardInfoSum(tempDfObj)
  1667. elif key == "creditCardInfoSumDfZ": # 准贷记卡
  1668. parseCreditCardInfoSumZ(tempDfObj)
  1669. elif key == "repaymentSumDf": # 相关还款责任
  1670. parseRepaymentSum(tempDfObj)
  1671. elif key == "publicInfoBriefDf":
  1672. parsePublicInfoBrief(tempDfObj);
  1673. elif key == "queryRecordSumDf":
  1674. parseQueryRecordSum(tempDfObj);
  1675. elif key == "queryRecordDetailDf": # 查询记录明细
  1676. parseQueryInfoDetail(tempDfObj)#
  1677. logger.info(fileName+"#"+"解析基础pdf数据完成")
  1678. result = "{"
  1679. # 基本信息
  1680. # result+=("\033[1;34m +身份信息+ \033[0m")+"\n"
  1681. result+=utils.toJson(identityInfoDf)+","
  1682. result += utils.toJson(mateInfoDf) + ","
  1683. result += utils.toJson(liveInfoDf) + ","
  1684. result += utils.toJson(occupationInfoDf) + ","
  1685. # result+=("\033[1;34m +概要信息+ \033[0m")+","
  1686. # result+=("\033[1;34m +信贷交易信息提示+ \033[0m")+","
  1687. # result+=utils.toJson(briefInfoDf_loanTradeInfo)+","
  1688. result += "briefInfoDf_loanTradeInfo" + "," # 占位符
  1689. # result+=("\033[1;34m +被追偿信息汇总及呆账信息汇总+ \033[0m")+","
  1690. result+="briefInfoDf_recoveryInfoSum"+"," #占位符
  1691. result += utils.toJson(briefInfoDf_badDebtsInfoSum) + ","
  1692. # result+=("\033[1;34m +逾期(透支)信息汇总+ \033[0m")+","
  1693. #此信息先占位
  1694. result+="briefInfoDf_overdueInfoSum"+","
  1695. # result+=("\033[1;34m +信贷交易授信及负债信息概要+ \033[0m")+","
  1696. result+=utils.toJson(briefInfoDf_loanTradeCreditInfo)+","
  1697. #公共信息
  1698. result += utils.toJson(publicInfoBriefDf) + ","
  1699. #查询记录汇总
  1700. result += utils.toJson(queryRecordSumDf) + ","
  1701. # 单独输出贷款df
  1702. # logger.info(fileName+"#"+"\033[1;34m +贷款信息Dataframe+ \033[0m")
  1703. # logger.info(fileName+"#"+dfParser.dfHeaderLoan)
  1704. logger.info(fileName+"#"+pdf_path+"解析贷款数据开始")
  1705. loanMergeDf = pd.DataFrame(columns=dfParser.dfHeaderLoan)
  1706. loanPayRecordMergeDf = pd.DataFrame(columns=dfParser.dfHeaderLoanPayRecord)
  1707. loanSpecialTradeMergeDf = pd.DataFrame(columns=dfParser.dfHeaderLoanSpecialTrade)#特殊交易
  1708. # 输出数据
  1709. for loanDfObj in loanAccountDfs:
  1710. loanMergeDf = pd.concat([loanMergeDf, loanDfObj["loanDf"]], axis=0, ignore_index=True);
  1711. loanPayRecordMergeDf = pd.concat([loanPayRecordMergeDf, loanDfObj["loanPayRecordDf"]], axis=0,ignore_index=True);
  1712. loanSpecialTradeMergeDf = pd.concat([loanSpecialTradeMergeDf, loanDfObj["specialTradeDf"]], axis=0, ignore_index=True);
  1713. # logger.info(fileName+"#"+loanMergeDf.values)
  1714. # logger.info(fileName+"#"+"\033[1;34m +贷款信息还款记录Dataframe+ \033[0m")
  1715. # logger.info(fileName+"#"+dfParser.dfHeaderLoanPayRecord)
  1716. # logger.info(fileName+"#"+loanPayRecordMergeDf.values)
  1717. #
  1718. #==============================信贷交易明细 ===============================
  1719. #被追偿信息
  1720. # 被追偿信息合并df
  1721. recoveryInfoMergeDf = pd.DataFrame(columns=dfParser.dfHeaderRecoveryInfo)
  1722. for recoveryInfoDfObj in recoveryInfoAccountDfs:
  1723. recoveryInfoMergeDf = pd.concat([recoveryInfoMergeDf, recoveryInfoDfObj["recoveryInfoDf"]], axis=0,
  1724. ignore_index=True);
  1725. parseRecoveryInfoMergeDf(recoveryInfoMergeDf);
  1726. #被追偿信息
  1727. result = result.replace("briefInfoDf_recoveryInfoSum", utils.toJson(briefInfoDf_recoveryInfoSum))#替换汇总中的指标
  1728. result += utils.toJson(creditTradeDetailDf_recoveryInfo) + "," #设置占位符,由于存在概要的指标在明细中计算
  1729. #特殊交易
  1730. parseSpecialTrade(loanSpecialTradeMergeDf)
  1731. result += utils.toJson(creditTradeDetailHeader_specialTrade) + ","
  1732. # 信贷交易明细-解析非循环贷账户
  1733. parseLoanAccountInfo(loanMergeDf);
  1734. result += utils.toJson(creditTradeDetailDf_loanAccountInfo) + ","
  1735. #循环额度分账户
  1736. parseCycleCreditAccountInfo(loanMergeDf);
  1737. result += utils.toJson(creditTradeDetailDf_cycleCreditAccountInfo) + ","
  1738. #循环贷
  1739. parseCycleLoanAccountInfo(loanMergeDf);
  1740. result += utils.toJson(creditTradeDetailDf_cycleLoanAccountInfo) + ","
  1741. # 解析贷款账户指标
  1742. parseLoanMergeDf(loanMergeDf);
  1743. # 解析还款记录相关指标
  1744. parseLoanMergeAndPayRecordDf(loanMergeDf, loanPayRecordMergeDf);
  1745. # logger.info(fileName+"#"+loanAccountInfo)
  1746. # logger.info(fileName+"#"+consts.loanAccountInfoHeader)
  1747. # logger.info(fileName+"#"+loanAccountInfoDf.values)
  1748. # result+=("\033[1;34m +贷款账户信息+ \033[0m")+","
  1749. result+=utils.toJson(loanAccountInfoDf)+","
  1750. logger.info(fileName+"#"+"解析贷款数据完成")
  1751. logger.info(fileName+"#"+"解析贷记卡数据开始")
  1752. #贷记卡合并df
  1753. creditCardMergeDf = pd.DataFrame(columns=dfParser.dfHeaderCreditCard)
  1754. creditCardPayRecordMergeDf = pd.DataFrame(columns=dfParser.dfHeaderCreditCardPayRecord)
  1755. # logger.info(fileName+"#"+"\033[1;34m +贷记卡信息Dataframe+ \033[0m")
  1756. # logger.info(fileName+"#"+dfParser.dfHeaderCreditCard)
  1757. # 输出数据
  1758. for creditCardDfObj in creditCardAccountDfs:
  1759. creditCardMergeDf = pd.concat([creditCardMergeDf, creditCardDfObj["creditCardDf"]], axis=0, ignore_index=True);
  1760. creditCardPayRecordMergeDf = pd.concat([creditCardPayRecordMergeDf, creditCardDfObj["creditCardPayRecordDf"]], axis=0,ignore_index=True);
  1761. # logger.info(fileName+"#"+creditCardMergeDf.values)
  1762. # 解析贷记卡账户指标
  1763. parseCreditCardMergeDf(creditCardMergeDf);
  1764. parseCreditCardMergeAndPayRecordDf(creditCardMergeDf,creditCardPayRecordMergeDf)
  1765. #准贷记卡合并df
  1766. creditCardMergeDfZ = pd.DataFrame(columns=dfParser.dfHeaderCreditCardZ)
  1767. creditCardPayRecordMergeDfZ = pd.DataFrame(columns=dfParser.dfHeaderCreditCardPayRecordZ)
  1768. for creditCardDfObj in creditCardAccountDfsZ:
  1769. creditCardMergeDfZ = pd.concat([creditCardMergeDfZ, creditCardDfObj["creditCardDfZ"]], axis=0, ignore_index=True);
  1770. creditCardPayRecordMergeDfZ = pd.concat([creditCardPayRecordMergeDfZ, creditCardDfObj["creditCardPayRecordDfZ"]], axis=0,ignore_index=True);
  1771. #解析准贷记卡相关指标
  1772. parseCreditCardMergeDfZ(creditCardMergeDfZ,creditCardPayRecordMergeDfZ);
  1773. logger.info(fileName+"#"+"解析贷记卡数据完成")
  1774. #加工使用率指标
  1775. # result+=("\033[1;34m +贷记卡账户信息+ \033[0m")+","
  1776. result+=utils.toJson(creditCardAccountInfoDf)+","
  1777. result += utils.toJson(creditCardAccountInfoDfZ) + ","
  1778. #使用率
  1779. parseUseRate()
  1780. result += utils.toJson(useRateDf) + ","
  1781. #开户数
  1782. parseOpenAccount(loanMergeDf, creditCardMergeDf, creditCardMergeDfZ,recoveryInfoMergeDf,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ)
  1783. result += utils.toJson(openAccountDf) + ","
  1784. #24期还款状态
  1785. parsePayRcdStatus(loanMergeDf, creditCardMergeDf, creditCardMergeDfZ,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ)
  1786. result += utils.toJson(payRcdStatusDf) + ","
  1787. #由于逾期汇总的指标再还款状态之后需要替换占位 TODO
  1788. result = result.replace("briefInfoDf_overdueInfoSum",utils.toJson(briefInfoDf_overdueInfoSum))
  1789. #0525 由于在开户数后,统计信贷信息概要的指标,替换占位符
  1790. result = result.replace("briefInfoDf_loanTradeInfo", utils.toJson(briefInfoDf_loanTradeInfo))
  1791. #公积金
  1792. # 被追偿信息合并df
  1793. housingFundRcdMergeDf = pd.DataFrame(columns=dfParser.dfHeaderHousingFundRcd)
  1794. for housingFundRcdDfObj in housingFundRcdAccountDfs:
  1795. housingFundRcdMergeDf = pd.concat([housingFundRcdMergeDf, housingFundRcdDfObj["housingFundRcdDf"]], axis=0,ignore_index=True);
  1796. parseHousingFundRcd(housingFundRcdMergeDf);
  1797. result += utils.toJson(housingFundRcdDf) + ","
  1798. # result+=("\033[1;34m +查询记录明细+ \033[0m")+","
  1799. result+=utils.toJson(queryRecordDetailDf)+""
  1800. result +="}"
  1801. return result;
  1802. #调用jar包
  1803. def invokePboc(basePath,pdf_path):
  1804. # ===================================
  1805. try:
  1806. # logger.error(pdf_path)
  1807. # fileName = os.path.basename(pdf_path)
  1808. # logger.error(fileName)
  1809. # jsonFileName = fileName.replace("pdf", 'txt')
  1810. businessNum = dbController.getBussinessNum(queryInfo["queryInfoCardId"]); # 根据身份证获取业务编号
  1811. pboc = PBOC(pdf_path.replace("pdf", 'txt'),businessNum)
  1812. jarTxt = pboc.calc();
  1813. result = json.loads(jarTxt)
  1814. logger.info(result)
  1815. if result.get("errcode")== None:
  1816. uploadAudit(result,businessNum)
  1817. else:
  1818. logger.error(result["errmsg"])
  1819. except:
  1820. info = sys.exc_info()
  1821. logger.error(info[0])
  1822. logger.error(info[1])
  1823. # logging.log(logging.ERROR, info[2])
  1824. logger.error(traceback.extract_tb(info[2], 1))
  1825. #上传审批结果
  1826. def uploadAudit(result,businessNum):
  1827. approvalType = result["approveResult"]
  1828. if approvalType=="1":
  1829. approvalOpinion = "征信通过"
  1830. approvalType = "4"
  1831. else:
  1832. approvalOpinion = "征信拒绝"
  1833. approvalType = "3"
  1834. taskKey = config.get("baseconf","taskKey")
  1835. appoveApiUrl = config.get("baseconf","appoveApiUrl")
  1836. key = config.get("baseconf", "AESKey")
  1837. data = {"header":{
  1838. "ticket": "2938123198320412343",
  1839. "timestamp": int(int(round(time.time() * 1000+60*1000))),
  1840. "nonce": config.get("baseconf", "nonce")
  1841. },
  1842. "body":{"approvalType": approvalType, "businessNum": businessNum,"taskKey":taskKey,"approvalOpinion":approvalOpinion}}
  1843. access_token = dbController.getToken()
  1844. appoveApiUrl = appoveApiUrl+"?access_token="+access_token
  1845. headers = {"Content-Type": "application/json"}
  1846. jsonStr = json.dumps(data);
  1847. jsonStr = jsonStr.replace('"',"\\\"")#必须替换才行
  1848. logger.info(jsonStr)
  1849. pboc = PBOC("","");
  1850. encryData = pboc.encrypt(jsonStr,key)
  1851. encryData = encryData[0:len(encryData)-2]
  1852. logger.info(encryData)
  1853. response = requests.post(appoveApiUrl, data=encryData,headers=headers)
  1854. text = response.text
  1855. p = PrpCrypt(key)
  1856. resultText = p.decrypt(text)
  1857. logger.info(businessNum + "#" + "upload_result:" + resultText)
  1858. def uploadReportResult(basePath,pdf_path):
  1859. # ===================================
  1860. try:
  1861. fileName = os.path.basename(pdf_path)
  1862. #上传文件逻辑
  1863. logger.info(fileName+"#"+fileName+"#"+"准备上传文件")
  1864. uploadApiUrl = config.get("baseconf", "uploadApiUrl");
  1865. uploadApiUrl = uploadApiUrl + "?access_token=" + dbController.getToken()
  1866. files = {'file': open(outPath, 'rb')}
  1867. businessNum = dbController.getBussinessNum(queryInfo["queryInfoCardId"]); # 根据身份证获取业务编号
  1868. logger.info(fileName+"#"+fileName+"#"+"businessNum:"+businessNum)
  1869. logger.info(fileName+"#"+"queryInfoCardId:" + queryInfo["queryInfoCardId"])
  1870. data = {'docType': "23", 'businessNum': businessNum}
  1871. response = requests.post(uploadApiUrl, files=files, data=data)
  1872. text = response.text
  1873. p = PrpCrypt(config.get("baseconf", "AESKey"))
  1874. # logger.info(fileName+"#"+"token:"+token)
  1875. # logger.info(fileName+"#"+url)
  1876. # logger.info(fileName+"#"+result.text)
  1877. resultText = p.decrypt(text)
  1878. logger.info(fileName+"#"+"upload_result:" + resultText)
  1879. descPdfPath = basePath + "execed/" + os.path.basename(pdf_path)
  1880. if not os.path.exists(basePath+"execed/"):
  1881. os.mkdir(basePath+"execed/")
  1882. shutil.move(pdf_path, descPdfPath)
  1883. # shutil.move(pdf_path.replace("pdf","txt"), descPdfPath.replace("pdf","txt"))
  1884. except:
  1885. info = sys.exc_info()
  1886. logger.error(info[0])
  1887. logger.error(info[1])
  1888. # logging.log(logging.ERROR, info[2])
  1889. logger.error(traceback.extract_tb(info[2], 1))
  1890. def updateParseInd(file_name):
  1891. # 检查是否存在已执行
  1892. parseInd = "1"
  1893. try:
  1894. if file_name != "":
  1895. arCert = file_name[0:-4].split("_")
  1896. if len(arCert) == 2:
  1897. cert_id = arCert[1]
  1898. parseIndTmp = dbController.getParseInd(cert_id)
  1899. if parseIndTmp =="1":
  1900. return True
  1901. dbController.updateParseInd(cert_id, parseInd)
  1902. except:
  1903. logger.error("update parse ind error")
  1904. return False
  1905. # grouped.to_csv(r'C:\Users\Mortal\Desktop\ex.csv',index=False, encoding='utf_8_sig')
  1906. if __name__ == '__main__':
  1907. file_name = ""
  1908. # basePath = "D:/mydocument/myproject/git/busscredit/20200414_report/";
  1909. basePath = "D:/mydocument/myprojects/creditreport/parse/"
  1910. # basePath = "Z:/cr/parse/"
  1911. # file_name = "周颖500108199002111229.pdf"#准贷记卡已销户 呆账
  1912. # file_name = "王思13052819911012122X.pdf"#公积金
  1913. # file_name = "杨夏龙440902198410014270.pdf"#转出
  1914. # file_name = "翟彦超230125199004174216.pdf"#准贷记卡 呆账
  1915. # file_name = "蔡月辉330326198502116146.pdf" # 配偶
  1916. # file_name = "周芳芳342501198706111782.pdf" #被追偿信息
  1917. # file_name = "付春雁533001198507220344.pdf" # 公积金记录
  1918. # pdf_path = basePath + "陈洁350122199005027726.pdf" # 相关还款责任
  1919. # file_name = "叶翔_330126197005200077.pdf" # 准贷记卡分页
  1920. file_name = "邱明阳_210103199405092412.pdf" #
  1921. # file_name = "姚钧_120101198903033539.pdf" #
  1922. pdf_path = basePath + file_name
  1923. if len(sys.argv)>1:
  1924. basePath = sys.argv[1]
  1925. pdf_path = basePath + sys.argv[2]
  1926. file_name = sys.argv[2]
  1927. # print(sys.argv)
  1928. isBat = False#批量的有问题
  1929. isPlt = config.get("baseconf", "isPlt");
  1930. if isBat:#批量生成数据不对
  1931. for file in os.listdir(basePath):
  1932. if file.endswith("pdf"):
  1933. start = timeit.default_timer();
  1934. pdf_path = basePath+file;
  1935. outPath = pdf_path.replace("pdf",'txt')
  1936. if os.path.exists(outPath):
  1937. continue;
  1938. logger.info(file + "解析开始...")
  1939. try:
  1940. result = main(pdf_path)
  1941. except:
  1942. info = sys.exc_info()
  1943. logger.error(info[0])
  1944. logger.error( info[1])
  1945. # logging.log(logging.ERROR, info[2])
  1946. logger.error(traceback.extract_tb(info[2], 1))
  1947. # print(result)
  1948. #输出到文件
  1949. sys.stdout = open(outPath, mode='w', encoding='utf-8')
  1950. print(result.replace("\033[1;34m","").replace("\033[0m",""))
  1951. logger.info(file+"解析完成")
  1952. gc.collect()
  1953. s = timeit.default_timer() - start;
  1954. logger.info(str(s) + " 秒")
  1955. else:
  1956. if pdf_path.endswith("pdf"):
  1957. start = timeit.default_timer();
  1958. outPath = pdf_path.replace("pdf", 'txt')
  1959. result = ""
  1960. if isPlt == "1":#生产模式
  1961. if not os.path.exists(outPath):#不存在才生成
  1962. try:
  1963. isExec = updateParseInd(file_name)
  1964. if not isExec:#没有在执行
  1965. logger.info(file_name + "解析开始...")
  1966. result = main(pdf_path)
  1967. sys.stdout = open(outPath, mode='w', encoding='utf-8')
  1968. print(result.replace("\033[1;34m", "").replace("\033[0m", ""))
  1969. logger.info(file_name + "解析完成")
  1970. s = timeit.default_timer() - start;
  1971. logger.info(file_name+"#"+str(s) + " 秒")
  1972. uploadReportResult(basePath,pdf_path);
  1973. #调用jar计算审批结果
  1974. cert_id = queryInfo["queryInfoCardId"]
  1975. productNum = dbController.getProductNum(cert_id)
  1976. if productNum == productNumJz:
  1977. invokePboc(basePath, pdf_path);
  1978. elif productNumXy.find(productNum) < 0:
  1979. xyResult = xyHttp.call_credit(result)
  1980. else:
  1981. xyHttp.callLocal(result)
  1982. except:
  1983. info = sys.exc_info()
  1984. logger.error(file_name+"#"+"解析失败")
  1985. logger.error(info[0])
  1986. logger.error(info[1])
  1987. logger.error(traceback.extract_tb(info[2]))
  1988. else:#如果已经执行过了,移动文件
  1989. logger.info("移动文件"+pdf_path)
  1990. # descPdfPath = basePath + "execed/" + os.path.basename(pdf_path)
  1991. # if not os.path.exists(basePath + "execed/"):
  1992. # os.mkdir(basePath + "execed/")
  1993. # shutil.move(pdf_path, descPdfPath)
  1994. else:
  1995. isExec = updateParseInd(file_name)
  1996. if not isExec: # 没有在执行
  1997. result = main(pdf_path)
  1998. sys.stdout = open(outPath, mode='w', encoding='utf-8')
  1999. print(result.replace("\033[1;34m", "").replace("\033[0m", ""))
  2000. logger.info(file_name + "解析完成")
  2001. s = timeit.default_timer() - start;
  2002. logger.info(file_name+"#"+str(s) + " 秒")
  2003. # uploadReportResult(basePath,pdf_path);
  2004. # 调用jar计算审批结果
  2005. cert_id = queryInfo["queryInfoCardId"]
  2006. productNum = dbController.getProductNum(cert_id)
  2007. productNumJz = config.get("baseconf", "productNumJz")
  2008. if productNum != "":
  2009. if productNum == productNumJz:
  2010. invokePboc(basePath, pdf_path);
  2011. elif productNumXy.find(productNum) >= 0:
  2012. xyHttp.call_credit(result)
  2013. else:
  2014. xyHttp.callLocal(result)