read_pdf_plt/dfParser.py

#横向合并贷款账户，设置为1行
#账户编号	管理机构	账户标识	开立日期	到期日期	借款金额（本金）	账户币种	业务种类	担保方式	还款期数	还款频率	还款方式	共同借款标志	账户状态	五级分类	余额（本金）	剩余还款期数	本月应还款	应还款日	本月实还款	最近一次还款日期	当前逾期期数	当前逾期总额	逾期31-60天未还本金	逾期61-90天未还本金	逾期61-90天未还本金	逾期91-180天未还本金	逾期180天以上未还本金	账户关闭日期	管理机构类型（银行/小贷公司 从B字段解析出来）
import pandas as pd
import numpy as np;
import utils;
import traceback
import sys
import log
logger = log.logger

#贷款明细大表表头
dfHeaderLoan = ['账户编号','管理机构','账户标识','开立日期','到期日期','借款金额（本金)','账户币种',
            '业务种类','担保方式','还款期数','还款频率','还款方式','共同借款标志',
            '账户状态','五级分类','余额（本金)','剩余还款期数','本月应还款','应还款日','本月实还款','最近一次还款日期',
            '当前逾期期数','当前逾期总额','逾期31-60天未还本金','逾期61-90天未还本金','逾期91-180天未还本金','逾期180天以上未还本金',
            '账户关闭日期','管理机构类型','截至日期','转出月份',"贷款负债"]
#贷记卡表头
dfHeaderCreditCard = ['账户编号','发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '业务种类', '担保方式',
                      '账户状态','余额','已用额度','未出单的大额专项分期余额','剩余分期期数','最近6个月平均使用额度','最大使用额',
                      '账单日','本月应还款','本月实还款','最近一次还款日期','当前逾期期数','当前逾期总额',
                      '大额专项分期额度','分期额度生效日期','分期额度到期日期','已用分期金额']

#准贷记卡表 考虑纳入贷记里，相同含义字段对上 TODO
dfHeaderCreditCardZ = ['账户编号','发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '担保方式',
                       '账户状态','透支余额','最近6个月平均透支余额','最大透支余额','账单日',
                       '本月实还款','最近一次还款日期','透支180天以上未付余额']

#还款记录表头
dfHeaderLoanPayRecord=['账户编号','还款日期','还款状态','还款状态值']

#特殊交易类型
#严重程度 指标为加工出来
dfHeaderLoanSpecialTrade=['账户编号','特殊交易类型','发生日期','变更月数','发生金额','明细记录','严重程度']

#贷记卡记录表头
dfHeaderCreditCardPayRecord=['账户编号','还款日期','还款状态','还款状态值']

#贷记卡记录表头
dfHeaderCreditCardPayRecordZ=['账户编号','还款日期','还款状态','还款状态值']
#被追偿信息表头
dfHeaderRecoveryInfo=['账户编号','管理机构','业务种类','债权接收日期','债权金额','债权转移时的还款状态',
                      '账户状态','账户关闭日期','特殊交易类型','发生日期','变更月份','发生金额','明细记录','截至日期']

#公积金
dfHeaderHousingFundRcd=['账户编号','参缴地', '参缴日期', '初缴月份', '缴至月份', '缴费状态', '月缴存额', '个人缴存比例', '单位缴存比例','缴费单位','信息更新日期']
#
#截止日期
def formatDateJz(str):
    return str[2:len(str)].replace('年','-').replace('月','-').replace('日','');

#替换日期
def formatDate(str):
    return str.replace('.','-');

# 科学计数法转换
def replaceAmt(value):
    if str(value)=="" or str(value)=="--" or str(value)=="nan":
        return value.replace(',', '')
    else:
        return float(value.replace(',', ''))
#转整型
def toInt(value):
    if str(value)=="" or str(value)=="--" or str(value)=="nan":
        return 0
    else:
        return int(value)

#合并贷款记录dataframe为宽表
def mergeLoanDf(dfObj,idx,reportTime):
    loanDf = pd.DataFrame(columns=dfHeaderLoan, index=[0])
    df = dfObj["df"]
    # print(df)
    df1 = df.loc[1, :].dropna().reset_index(drop=True)
    df3 = df.loc[3, :].dropna().reset_index(drop=True)
    df4 = df.loc[4, :].dropna().reset_index(drop=True)
    df6 = df.loc[6, :].dropna().reset_index(drop=True)

    loanDf.loc[0, '账户编号'] = idx+1;# 账户编号
    loanDf.loc[0, '管理机构'] = df1[0].replace('\n','');
    loanDf.loc[0, '账户标识'] = df1[1];
    loanDf.loc[0, '开立日期'] = formatDate(df1[2]);
    loanDf.loc[0, '到期日期'] = formatDate(df1[3]);
    loanDf.loc[0, '借款金额（本金)'] = replaceAmt(df1[4]);
    loanDf.loc[0, '账户币种'] = df1[5];

    loanDf.loc[0, '业务种类'] = df3[0];
    loanDf.loc[0, '担保方式'] = df3[1];
    loanDf.loc[0, '还款期数'] = toInt(df3[2]);
    loanDf.loc[0, '还款频率'] = df3[3];
    loanDf.loc[0, '还款方式'] = df3[4];
    loanDf.loc[0, '共同借款标志'] = df3[5];
    loanDf.loc[0, '截至日期'] = formatDateJz(df4[0]);

    accountStatus = df6[0]
    loanDf.loc[0, '账户状态'] = accountStatus;
    # print("状态之前")
    # print(accountStatus)
    if accountStatus != "结清" and accountStatus!="呆账" and accountStatus!="转出":
        df8 = df.loc[8, :].dropna().reset_index(drop=True)# 逾期信息
        loanDf.loc[0, '五级分类'] = df6[1]
        loanDf.loc[0, '余额（本金)'] = replaceAmt(df6[2])
        loanDf.loc[0, '剩余还款期数'] = toInt(df6[3])
        loanDf.loc[0, '本月应还款'] = replaceAmt(df6[4])
        loanDf.loc[0, '应还款日'] = formatDate(df6[5])
        loanDf.loc[0, '本月实还款'] = replaceAmt(df6[6])
        loanDf.loc[0, '最近一次还款日期'] = formatDate(df6[7])
        #'当前逾期期数','当前逾期总额','逾期31-60天未还本金','逾期61-90天未还本金','逾期91-180天未还本金','逾期180天以上未还本金'
        loanDf.loc[0, '当前逾期期数'] = toInt(df8[0])
        loanDf.loc[0, '当前逾期总额'] = replaceAmt(df8[1])
        loanDf.loc[0, '逾期31-60天未还本金'] = replaceAmt(df8[2])
        loanDf.loc[0, '逾期61-90天未还本金'] = replaceAmt(df8[3])
        loanDf.loc[0, '逾期91-180天未还本金'] = replaceAmt(df8[4])
        loanDf.loc[0, '逾期180天以上未还本金'] = replaceAmt(df8[5])
        loanDf.loc[0, '截至日期月份'] = utils.difMonthReportTime(formatDateJz(df4[0]), reportTime);  # 截止日期离报告时间月份
    elif accountStatus == "结清":
        loanDf.loc[0, '账户关闭日期'] = formatDate(df6[1]);
    elif accountStatus == "转出":
        loanDf.loc[0, '转出月份'] = formatDate(df6[1]);
    elif accountStatus == "呆账":
        loanDf.loc[0, '余额（本金)'] = replaceAmt(df6[1]);
        loanDf.loc[0, '最近一次还款日期'] = formatDate(df6[2]);
    if df1[0].find("\"")>0:
        loanDf.loc[0, '管理机构类型'] = df1[0].split("\"")[0].replace('\n','');
    dfObj["loanDf"] = loanDf;
    # print(loanDf)
    #解析还款记录
    loanPayRecord = pd.DataFrame()
    #特殊交易
    specialTradeDf = pd.DataFrame()
    if accountStatus == "正常":#正常
        recordIndexBegin = 11;
        loanPayRecord = mergeLoanPayRecordDf(recordIndexBegin, df, idx,loanPayRecord)
        specialTradeDf = mergeLoanSpecialTrade(recordIndexBegin, df, idx,specialTradeDf)
    elif accountStatus == "逾期":#如果长度小于12后续无需处理
        #14 11 有提前最新还款记录的为14
        print("是逾期吗")
        recordIndexBegin = 11;
        loanPayRecord = mergeLoanPayRecordDf(recordIndexBegin, df, idx,loanPayRecord)
        print("是逾期吗1")
        specialTradeDf = mergeLoanSpecialTrade(recordIndexBegin, df, idx, specialTradeDf)
        print("是逾期吗2")
    elif accountStatus == "结清":
        recordIndexBegin = 9;
        loanPayRecord = mergeLoanPayRecordDf(recordIndexBegin, df, idx, loanPayRecord)
        specialTradeDf = mergeLoanSpecialTrade(recordIndexBegin, df, idx, specialTradeDf)
    elif accountStatus == "呆账" or  accountStatus == "转出":
        recordIndexBegin = 9;
        loanPayRecord = mergeLoanPayRecordDf(recordIndexBegin,df,idx,loanPayRecord)
        specialTradeDf = mergeLoanSpecialTrade(recordIndexBegin, df, idx, specialTradeDf)
    else:
        print("===================还款状态不满足条件===================")
    dfObj["loanPayRecordDf"] = loanPayRecord;
    # print(loanPayRecord)
    print("声明")
    dfObj["specialTradeDf"] = specialTradeDf;
    # print(specialTradeDf)
    # print(dfObj)
    return dfObj;

#计算特殊交易
# 从“信贷交易信息明细”中提取，搜索所有发生有“特殊交易”的账户，统计所有“特殊交易类型”。交易的严重程度：

# 取max
# 说明特殊交易类型的代码。
# 代码表如下：
# 1-展期
# 2-担保人（第三方）代偿
# 3-以资抵债
# 4-提前还款（包括提前归还部分本金、还款期限不变，以
# 及缩短还款期限两种情况）
# 5-提前结清
# 6-强制平仓，未结清
# 7-强制平仓，已结清
# 8-司法追偿
# 9-其他
# 11-债务减免
# 12-资产剥离
# 13-资产转让
# 14-信用卡个性化分期
# 16-银行主动延期
# 17-强制平仓

# 0= tradetype in ("提前还款（部分）","提前还款（全部）")；
# 0 = tradetype in ("其他") and find(content,"提前")>0;
# 1= tradetype in ("担保人代还")；
# 2 = tradetype in ("其他") and content not in ("担保人代还取消","合同展期");
# 2 = tradetype in ("展期（延期）") and content in ("专升本或研究生入学展期");
# 3 = tradetype in ("其他") and content in ("担保人代还取消","合同展期");
# 3=tradetype in ("展期（延期）") and content not in ("专升本或研究生入学展期") ;
# 4=tradetype in ("以资抵债")。
def mergeLoanSpecialTrade(recordIndexBegin,df,idx,specialTradeDf):
    for i in range(recordIndexBegin, df.index.size):
        dfRecord = df.loc[i, :].dropna().reset_index(drop=True)
        # print(df.loc[i, :].dropna())
        if len(dfRecord) == 5:  # 特殊交易
            # print(dfRecord)
            specialTradeDfTmp = pd.DataFrame(columns=dfHeaderLoanSpecialTrade, index=[0])
            if dfRecord[0]!='特殊交易类型':
                specialTradeDfTmp.loc[0, '账户编号'] = idx + 1;  # 账户编号
                specialTradeDfTmp.loc[0,'特殊交易类型'] = dfRecord[0]
                specialTradeDfTmp.loc[0,'发生日期'] = formatDate(dfRecord[1])
                print("日期")
                # specialTradeDfTmp.loc[0,'变更月数'] = toInt(dfRecord[1]) # yuan
                print("变更")
                specialTradeDfTmp.loc[0, '发生金额'] = replaceAmt(dfRecord[3])
                specialTradeDfTmp.loc[0, '明细记录'] = dfRecord[3].replace("\n","")
                tradeType = dfRecord[0]
                content = dfRecord[3].replace("\n","")#明细记录
                #TODO  加工严重程度 逻辑待确认
                severity = None
                if tradeType == '提前还款' or tradeType == '提前结清':
                    severity = 0
                elif tradeType == '其他' and content.find("提前")>=0:
                    severity = 0
                elif tradeType == '担保人（第三方）代偿':
                    severity= 1
                # elif tradeType == "其他" and content.find("担保人代还取消")<0 and  content.find("合同展期")<0:
                #     severity = 2
                #同时出现代还和取消
                elif tradeType == "其他" and (content.find("代还") < 0 or  content.find("代偿") < 0) and content.find("取消") < 0 and content.find("合同展期") < 0:
                    severity = 2
                elif (tradeType == "展期" or tradeType.find('延期')>=0) and content.find("专升本或研究生入学展期")>=0:
                    severity = 2
                # elif tradeType == "其他" and (content.find("担保人代还取消")>=0 or  content.find("合同展期")>=0):
                #     severity = 3
                elif tradeType == "其他" and (( (content.find("代还") < 0 or  content.find("代偿") < 0)  and content.find("取消") >=0) or (content.find("合同展期") >= 0)):
                    severity = 3
                elif (tradeType == "展期" or tradeType.find('延期')>=0)  and content.find("专升本或研究生入学展期")<0:
                    severity = 3
                elif tradeType == "以资抵债":
                    severity = 4
                specialTradeDfTmp.loc[0, '严重程度'] = severity
                specialTradeDf = pd.concat([specialTradeDf, specialTradeDfTmp], axis=0, ignore_index=True);
    return specialTradeDf;
    # '特殊交易类型','发生日期','变更月数','发生金额','明细记录'
#合并贷款还款记录明细
def mergeLoanPayRecordDf(recordIndexBegin,df,idx,loanPayRecord):
    for i in range(recordIndexBegin, df.index.size):
        dfRecord = df.loc[i, :].dropna().reset_index(drop=True)
        if len(dfRecord) == 13:
            loanPayRecordTmp = pd.DataFrame(columns=dfHeaderLoanPayRecord, index=[0])
            loanPayRecordTmp.loc[0, '账户编号'] = idx + 1;  # 账户编号
            payYear = dfRecord[0];
            # for j in range(0,12):
            #     payMonth = payYear +"-" +str(j+1)+"-1"
            # loanPayRecordTmp.loc[0, '还款日期'] = payMonth;
            # loanPayRecordTmp.loc[0, '还款状态'] = dfRecord[j+1];
        elif len(dfRecord) == 12:
            for j in range(0, 12):
                if j < 9:
                    payMonth = payYear + "-0" + str(j + 1) + "-01"
                else:
                    payMonth = payYear + "-" + str(j + 1) + "-01"
                loanPayRecordTmp.loc[0, '还款日期'] = payMonth;
                loanPayRecordTmp.loc[0, '还款状态值'] = dfRecord[j];
                dfRecordPre = df.loc[i - 1, :].dropna().reset_index(drop=True)
                loanPayRecordTmp.loc[0, '还款状态'] = dfRecordPre[j + 1];
                # loanPayRecords.append(loanPayRecord)
                loanPayRecord = pd.concat([loanPayRecord, loanPayRecordTmp], axis=0, ignore_index=True);
        # elif len(dfRecord)==5:#特殊交易
            #'特殊交易类型','发生日期','变更月数','发生金额','明细记录'
    if not loanPayRecord.empty:
        loanPayRecord = loanPayRecord[loanPayRecord['还款状态']!=""];
    return loanPayRecord;
#合并贷记卡为宽表
# ['账户编号','发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '业务种类', '担保方式',
#                       '账户状态','余额','已用额度','未出单的大额专项分期余额','剩余分期期数','最近6个月平均使用额度','最大使用额',
#                       '账单日','本月应还款','本月实还款','最近一次还款日期','当前逾期期数','当前逾期总额',
#                       '大额专项分期额度','分期额度生效日期','分期额度到期日期','已用分期金额']
def mergeCreditCardDf(dfObj,idx,reportTime):
    creditCardDf = pd.DataFrame(columns=dfHeaderCreditCard, index=[0])
    df = dfObj["df"]
    df1 = df.loc[1, :].dropna().reset_index(drop=True)
    # print(df)
    if df.index.size==2:
        logger.info("账户："+str(idx+1)+"数据问题，未激活分页")
    if df.index.size > 2:
        df2 = df.loc[2, :].dropna().reset_index(drop=True)
    creditCardDf.loc[0, '账户编号'] = idx + 1;  # 账户编号
    creditCardDf.loc[0, '发卡机构'] = df1[0].replace('\n', '');
    creditCardDf.loc[0, '账户标识'] = df1[1]
    creditCardDf.loc[0, '开立日期'] = formatDate(df1[2])
    creditCardDf.loc[0, '账户授信额度'] = replaceAmt(df1[3])
    creditCardDf.loc[0, '共享授信额度'] = replaceAmt(df1[4])
    #排除美元027
    if df1[5].find('美元')>=0:
        return None
    creditCardDf.loc[0, '币种'] = df1[5]
    creditCardDf.loc[0, '业务种类'] = df1[6]
    creditCardDf.loc[0, '担保方式'] = df1[7]
    if df.index.size>2:
        creditCardDf.loc[0, '截至日期'] = formatDateJz(df2[0]);
    accountStatus = ""
    if df.index.size > 3 and df2[0].find('未激活')<0:#非未激活
        # print("非未激活")
        df4 = df.loc[4, :].dropna().reset_index(drop=True)
        if df4[0] == "呆账":
            # df6 = df.loc[6, :].dropna().reset_index(drop=True)
            accountStatus = df4[0]
            creditCardDf.loc[0, '账户状态'] = accountStatus
            creditCardDf.loc[0, '余额'] = replaceAmt(df4[1])
            creditCardDf.loc[0, '最近一次还款日期'] = formatDate(df4[2])
            creditCardDf.loc[0, '截至日期月份'] = utils.difMonthReportTime(formatDateJz(df2[0]), reportTime);  # 截止日期离报告时间月份
        elif df4[0] != '销户':
            try:
                df6 = df.loc[6, :].dropna().reset_index(drop=True)
                accountStatus = df4[0]
                creditCardDf.loc[0, '账户状态'] = df4[0]
                creditCardDf.loc[0, '余额'] = replaceAmt(df4[1])
                creditCardDf.loc[0, '已用额度'] = replaceAmt(df4[2])
                dividePeriodBalance = df4[3]  # 未出单的大额专项分期余额
                creditCardDf.loc[0, '未出单的大额专项分期余额'] = replaceAmt(df4[3])
                creditCardDf.loc[0, '剩余分期期数'] = toInt(df4[4])
                creditCardDf.loc[0, '最近6个月平均使用额度'] = replaceAmt(df4[5])
                creditCardDf.loc[0, '最大使用额'] = replaceAmt(df4[6])
                creditCardDf.loc[0, '账单日'] = formatDate(df6[0])
                creditCardDf.loc[0, '本月应还款'] = replaceAmt(df6[1])
                creditCardDf.loc[0, '本月实还款'] = replaceAmt(df6[2])
                creditCardDf.loc[0, '最近一次还款日期'] = formatDate(df6[3])
                creditCardDf.loc[0, '当前逾期期数'] = toInt(df6[4])
                creditCardDf.loc[0, '当前逾期总额'] = replaceAmt(df6[5])
                creditCardDf.loc[0, '截至日期月份'] = utils.difMonthReportTime(formatDateJz(df2[0]), reportTime);  # 截止日期离报告时间月份
                # print("非销户")
            except Exception:
                logger.info("解析贷记卡异常-"+idx);
                info = sys.exc_info()
                logger.error(info[0])
                logger.error(info[1])
                logger.error(traceback.extract_tb(info[2]))
            if dividePeriodBalance !="--":
                #'大额专项分期额度','分期额度生效日期','分期额度到期日期','已用分期金额']
                df9 = df.loc[9, :].dropna().reset_index(drop=True)
                creditCardDf.loc[0, '大额专项分期额度'] = replaceAmt(df9[0])
                creditCardDf.loc[0, '分期额度生效日期'] = formatDate(df9[1])
                creditCardDf.loc[0, '分期额度到期日期'] = formatDate(df9[2])
                creditCardDf.loc[0, '已用分期金额'] = replaceAmt(df9[3])
        else:
            accountStatus = '销户'
            creditCardDf.loc[0, '账户状态'] = '销户'
    # if df.index.size > 3 and df4[0] == '销户':
    #     creditCardDf.loc[0, '账户状态'] = '销户'
    # elif df.index.size > 3 and df4[0] != '销户':
    #     creditCardDf.loc[0, '账户状态'] = df4[0]
    else:#未激活
        creditCardDf.loc[0, '账户状态'] = '未激活'
        accountStatus= '未激活'
        if df.index.size > 2:
            creditCardDf.loc[0, '截至日期'] = creditCardDf.loc[0, '截至日期'][0:10]#去掉未激活字样
    dfObj["creditCardDf"] = creditCardDf;

    # 解析还款记录
    creditCardPayRecord = pd.DataFrame()
    if accountStatus == "正常" or accountStatus=="冻结" or accountStatus=="止付":  # 正常
        recordIndexBegin = 9;
        print("正常合并之前")
        creditCardPayRecord = mergeCreditCardPayRecordDf(recordIndexBegin, df, idx, creditCardPayRecord)
        # print("正常合并之后")
    elif accountStatus == "未激活":
        recordIndexBegin = 0;
    elif accountStatus == "销户" or accountStatus == "呆账":
        recordIndexBegin = 7;
        creditCardPayRecord = mergeCreditCardPayRecordDf(recordIndexBegin, df, idx, creditCardPayRecord)
    else:
        print("账户#"+str(idx+1)+"#"+accountStatus)
        print("===================还款状态不满足条件===================")
    dfObj["creditCardPayRecordDf"] = creditCardPayRecord;

    return dfObj;

#合并贷记卡还款记录明细
def mergeCreditCardPayRecordDf(recordIndexBegin,df,idx,creditCardPayRecord):
    for i in range(recordIndexBegin, df.index.size):
        dfRecord = df.loc[i, :].dropna().reset_index(drop=True)
        # print(len(dfRecord))
        if len(dfRecord) == 13:
            # print(dfRecord)
            if dfRecord[0]!='':
                # print("非空")
                recordTmp = pd.DataFrame(columns=dfHeaderCreditCardPayRecord, index=[0])
                recordTmp.loc[0, '账户编号'] = idx + 1;  # 账户编号
                payYear = dfRecord[0];
            #如果为13条记录，年份为空，说明是被合并的记录，此条记录计算为还款状态
            if dfRecord[0] =='':
                print("是空")
                # ，这块逻辑有问题yuan
                for j in range(1, 13):
                    if j < 10:
                        payMonth = payYear + "-0" + str(j ) + "-01"
                    else:
                        payMonth = payYear + "-" + str(j) + "-01"
                    recordTmp.loc[0, '还款日期'] = payMonth;
                    recordTmp.loc[0, '还款状态值'] = dfRecord[j];
                    dfRecordPre = df.loc[i - 1, :].dropna().reset_index(drop=True)
                    recordTmp.loc[0, '还款状态'] = dfRecordPre[j];
                    creditCardPayRecord = pd.concat([creditCardPayRecord, recordTmp], axis=0, ignore_index=True);
        elif len(dfRecord) == 12:
            for j in range(0, 12):
                if j < 9:
                    payMonth = payYear + "-0" + str(j + 1) + "-01"
                else:
                    payMonth = payYear + "-" + str(j + 1) + "-01"
                recordTmp.loc[0, '还款日期'] = payMonth;
                recordTmp.loc[0, '还款状态值'] = dfRecord[j];
                dfRecordPre = df.loc[i - 1, :].dropna().reset_index(drop=True)
                recordTmp.loc[0, '还款状态'] = dfRecordPre[j + 1];
                creditCardPayRecord = pd.concat([creditCardPayRecord, recordTmp], axis=0, ignore_index=True);
    if not creditCardPayRecord.empty:
        # print("空不空")
        creditCardPayRecord = creditCardPayRecord[creditCardPayRecord['还款状态'] != ""];
    return creditCardPayRecord;

#合并被追偿信息
def mergeRecoveryInfoDf(dfObj,idx,reportTime):
    recoveryInfoDf = pd.DataFrame(columns=dfHeaderCreditCard, index=[0])
    df = dfObj["df"]
    df1 = df.loc[1, :].dropna().reset_index(drop=True)
    df2 = df.loc[2, :].dropna().reset_index(drop=True)
    df3 = df.loc[4, :].dropna().reset_index(drop=True)

    recoveryInfoDf.loc[0, '账户编号'] = idx + 1;  # 账户编号
    recoveryInfoDf.loc[0, '管理机构'] = df1[0].replace('\n', '');
    recoveryInfoDf.loc[0, '业务种类'] = df1[1]
    recoveryInfoDf.loc[0, '债权接收日期'] = formatDate(df1[2])
    recoveryInfoDf.loc[0, '债权金额'] = replaceAmt(df1[3])
    recoveryInfoDf.loc[0, '债权转移时的还款状态'] = df1[4]
    recoveryInfoDf.loc[0, '账户状态'] = df3[0]
    accountStatus = df3[0]#账户状态
    if df.index.size==7:
        df4 = df.loc[5, :].dropna().reset_index(drop=True)
        recoveryInfoDf.loc[0, '特殊交易类型'] = df4[0]
        recoveryInfoDf.loc[0, '发生日期'] = df4[1]
        recoveryInfoDf.loc[0, '变更月份'] = df4[2]
        recoveryInfoDf.loc[0, '发生金额'] = df4[3]
        recoveryInfoDf.loc[0, '明细记录'] = df4[4]
    if accountStatus == "催收":
        recoveryInfoDf.loc[0, '余额'] = replaceAmt(df3[1])
        recoveryInfoDf.loc[0, '最近一次还款日期'] = formatDate(df3[2])
    else:
        recoveryInfoDf.loc[0, '账户关闭日期'] = df3[1]
    recoveryInfoDf.loc[0, '截至日期'] = formatDateJz(df2[0]);
    dfObj["recoveryInfoDf"] = recoveryInfoDf
    return dfObj

#合并准贷记卡账户
# ['账户编号','发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '担保方式',
#                        '账户状态','透支余额','最近6个月平均透支余额','最大透支余额','账单日',
#                        '本月实还款','最近一次还款日期','透支180天以上未付余额']
def mergeCreditCardDfZ(dfObj,idx,reportTime):
    creditCardDf = pd.DataFrame(columns=dfHeaderCreditCardZ, index=[0])
    df = dfObj["df"]
    # print(df)
    df1 = df.loc[1, :].dropna().reset_index(drop=True)
    if df.index.size==2:
        logger.info("账户："+str(idx+1)+"数据问题")
        return None
    df2 = df.loc[2, :].dropna().reset_index(drop=True)

    creditCardDf.loc[0, '账户编号'] = idx + 1;  # 账户编号
    creditCardDf.loc[0, '发卡机构'] = df1[0].replace('\n', '');
    creditCardDf.loc[0, '账户标识'] = df1[1]
    creditCardDf.loc[0, '开立日期'] = formatDate(df1[2])
    creditCardDf.loc[0, '账户授信额度'] = replaceAmt(df1[3])
    creditCardDf.loc[0, '共享授信额度'] = replaceAmt(df1[4])
    creditCardDf.loc[0, '币种'] = df1[5]
    creditCardDf.loc[0, '担保方式'] = df1[6]
    if df1[5].find('美元')>=0:
        return None
    creditCardDf.loc[0, '截至日期'] = formatDateJz(df2[0]);
    accountStatus = ""
    if df.index.size > 3 and df2[0].find('未激活')<0:#非未激活
        df4 = df.loc[4, :].dropna().reset_index(drop=True)
        if df4[0] == "呆账":
            # df6 = df.loc[6, :].dropna().reset_index(drop=True)
            accountStatus = df4[0]
            creditCardDf.loc[0, '账户状态'] = accountStatus
            creditCardDf.loc[0, '余额'] = replaceAmt(df4[1])
            creditCardDf.loc[0, '透支余额'] = replaceAmt(df4[1])
            creditCardDf.loc[0, '最近一次还款日期'] = formatDate(df4[2])
            creditCardDf.loc[0, '截至日期月份'] = utils.difMonthReportTime(formatDateJz(df2[0]), reportTime);  # 截止日期离报告时间月份
        elif df4[0] != '销户':
            try:
                # df6 = df.loc[6, :].dropna().reset_index(drop=True)
                accountStatus = df4[0]
                creditCardDf.loc[0, '账户状态'] = df4[0]
                creditCardDf.loc[0, '透支余额'] = replaceAmt(df4[1])
                creditCardDf.loc[0, '最近6个月平均透支余额'] = replaceAmt(df4[2])
                creditCardDf.loc[0, '最大透支余额'] = replaceAmt(df4[3])
                creditCardDf.loc[0, '账单日'] = formatDate(df4[4])
                creditCardDf.loc[0, '本月实还款'] = replaceAmt(df4[5])
                creditCardDf.loc[0, '最近一次还款日期'] = formatDate(df4[6])
                creditCardDf.loc[0, '透支180天以上未付余额'] = replaceAmt(df4[7])
                creditCardDf.loc[0, '截至日期月份'] = utils.difMonthReportTime(formatDateJz(df2[0]), reportTime);  # 截止日期离报告时间月份
            except:
                logger.info("解析准贷记卡异常-"+idx);
        else:
            accountStatus = '销户'
            creditCardDf.loc[0, '账户状态'] = '销户'
            creditCardDf.loc[0, '最近一次还款日期']=""
            # if df.index.size > 3 and df4[0] == '销户':
    #     creditCardDf.loc[0, '账户状态'] = '销户'
    # elif df.index.size > 3 and df4[0] != '销户':
    #     creditCardDf.loc[0, '账户状态'] = df4[0]
    else:#未激活
        creditCardDf.loc[0, '账户状态'] = '未激活'
        accountStatus= '未激活'
        creditCardDf.loc[0, '最近一次还款日期'] = ""
        creditCardDf.loc[0, '截至日期'] = creditCardDf.loc[0, '截至日期'][0:10]#去掉未激活字样
    dfObj["creditCardDfZ"] = creditCardDf;

    # 解析还款记录
    creditCardPayRecord = pd.DataFrame()
    if accountStatus == "正常" or accountStatus=="冻结" or accountStatus=="止付":  # 正常
        recordIndexBegin = 9;
        creditCardPayRecord = mergeCreditCardPayRecordDf(recordIndexBegin, df, idx, creditCardPayRecord)
    elif accountStatus == "未激活":
        recordIndexBegin = 0;
    elif accountStatus == "销户" or accountStatus == "呆账":
        recordIndexBegin = 7;
        creditCardPayRecord = mergeCreditCardPayRecordDf(recordIndexBegin, df, idx, creditCardPayRecord)
    else:
        print("账户#"+str(idx+1)+"#"+accountStatus)
        print("===================还款状态不满足条件===================")
    dfObj["creditCardPayRecordDfZ"] = creditCardPayRecord;

    return dfObj;

#合并公积金
#['账户编号','参缴地', '参缴日期', '初缴月份', '缴至月份', '缴费状态', '月缴存额', '个人缴存比例', '单位缴存比例','缴费单位','信息更新日期']
def mergeHousingFundRcdDf(dfObj,idx,reportTime):
    housingFundRcdDf = pd.DataFrame(columns=dfHeaderHousingFundRcd, index=[0])
    df = dfObj["df"]
    row1 = df.loc[1, :].dropna().reset_index(drop=True)
    row3 = df.loc[3, :].dropna().reset_index(drop=True)
    housingFundRcdDf.loc[0, '账户编号'] = idx + 1;  # 账户编号
    housingFundRcdDf.loc[0, '参缴地'] = row1[0].replace("\n","")
    housingFundRcdDf.loc[0, '参缴日期'] = utils.formatDate(row1[1])
    housingFundRcdDf.loc[0, '初缴月份'] = utils.formatDate(row1[2])
    housingFundRcdDf.loc[0, '缴至月份'] = utils.formatDate(row1[3])
    housingFundRcdDf.loc[0, '缴费状态'] = row1[4]
    housingFundRcdDf.loc[0, '月缴存额'] = utils.replaceAmt(row1[5])
    housingFundRcdDf.loc[0, '个人缴存比例'] = row1[6]
    housingFundRcdDf.loc[0, '单位缴存比例'] = row1[7]
    housingFundRcdDf.loc[0, '缴费单位'] = row3[0]
    housingFundRcdDf.loc[0, '信息更新日期'] = utils.formatDate(row3[1])
    dfObj["housingFundRcdDf"] = housingFundRcdDf
    return dfObj