# coding=utf-8

import numpy as np
import pandas as pd

import matplotlib.pyplot as plt

from sklearn.metrics import roc_auc_score

import pymysql
import pymongo

import os
import pickle
import warnings
import datetime
from dateutil.relativedelta import relativedelta
from collections import OrderedDict

warnings.filterwarnings('ignore')


class ModelMonitor:
    def __init__(self, excel_path='./model_score.xlsx', sheet_name='mongo_model',
                 passdue_day=15, save_path='./image/',
                 num_month=4, min_user_group=500, max_psi=0.1, min_aucr=0.85,
                 if_save=True, if_load=False):

        # 考虑到数据库配置基本不变, 所以不设置创建对象时对应输入变量.
        self.mysql_engine = pymysql.connect(host='172.20.6.9',
                                            port=9030,
                                            user='fengkong_read_only',
                                            passwd='mT2HFUgI',
                                            db='risk_analysis',
                                            charset='utf8')

        self.mongo_client = pymongo.MongoClient(
            "mongodb://haoyue.shu:x2egwRHk7WhQ4So1@172.18.3.22:27017/?authSource=rc_mgo_feature_dp")
        self.mongo_db = self.mongo_client['rc_mgo_feature_dp']
        self.mongo_table = self.mongo_db['wf_audit_log_with_feature']

        # 读取整理在Excel中的模型相关信息.
        self.model_info_df = pd.read_excel(excel_path, sheet_name=sheet_name)
        self.model_name_list = self.model_info_df.model_name.tolist()
        self.model_feild_list = self.model_info_df.model_feild.tolist()
        self.model_feild_name_dict = dict(zip(self.model_feild_list, self.model_name_list))

        # 一些定义的常量
        self.passdue_day = passdue_day  # 逾期天数, 默认15.
        self.save_path = save_path  # 图片保存位置, 默认./image.
        self.num_month = num_month  # 取数的月数.
        self.min_user_group = min_user_group  # 最小客群数量.
        self.max_psi = max_psi  # 最大PSI, 超过视为异常.
        self.min_aucr = min_aucr  # 最小AUC比率, 小于视为异常.

        # 获取当天日期信息.
        self.current_date = (datetime.date.today() + relativedelta(days=-1)).strftime('%Y-%m-%d')
        self.response_date = (datetime.date.today() + relativedelta(days=-(31 + self.passdue_day))).strftime('%Y-%m-%d')
        self.first_date = (datetime.date.today() + relativedelta(months=-self.num_month + 1)).strftime('%Y-%m-01')

        self.current_month = (datetime.date.today() + datetime.timedelta(days=-1)).month
        self.response_month = (datetime.date.today() + relativedelta(days=-46)).month
        self.first_month = self.current_month - self.num_month + 1

        # 将会从数据库中读取的数据.
        self.mysql_df = None
        self.mongo_df = None
        self.merge_data = None

        # 统计数据记录.
        psi_cols = ['模型名称', '客群名称']
        auc_cols = ['模型名称', '客群名称']
        for m in range(self.first_month, self.current_month + 1):
            psi_cols.append(str(m) + '月数量')
            psi_cols.append(str(m) + '月PSI')
            auc_cols.append(str(m) + '月数量')
            auc_cols.append(str(m) + '月AUC')
            auc_cols.append(str(m) + '月AUCR')

        self.psi_info_df = pd.DataFrame(columns=psi_cols)
        self.auc_info_df = pd.DataFrame(columns=auc_cols)

        self.na_enough_data_psi_set = set()  # 一些新的模型没有足够数据用于统计.
        self.na_enough_data_auc_set = set()  # 一些新的模型没有足够数据用于统计.

        self.filed_bench_bins_ratio = None  # 每个模型分在总体样本上第一个月的分箱比例.
        self.filed_bench_auc = None  # 每个模型分在总体样本上第一个月的AUC

        # 程序数据读写模式.
        self.if_save = if_save  # 是否保存从数据库抽取的数据.
        self.if_load = if_load  # 是否从保存的数据加载数据, 而不从数据库读取.

    def sql_query(self, sql):
        '''
        连接MySQL数据库, 根据SQL返回数据.
        :param sql: str.
        :return: DataFrame.
        '''
        try:
            return pd.read_sql(sql, self.mysql_engine)
        except:
            print('SQL查询出现错误.')

    def mongo_query(self, condition, fields):
        '''
        连接MongoDB, 根据查询返回数据.
        :param condition: dict
        :param fields: dict
        :return: DataFrame
        '''
        try:
            return pd.DataFrame(list(self.mongo_table.find(condition, fields)))
        except:
            print('Mongo查询出现错误.')

    def int2str(self, x):
        '''
        将int转换为str, 用于日期.
        e.g. 5 --> 05
        :param x: int
        :return: str.
        '''
        if x >= 10:
            return str(x)
        else:
            return '0' + str(x)

    def make_bin(self, score_list):
        '''
        对传入的模型分进行等频分箱.
        :param score_list: pd.Series
        :return: list[num]
        '''
        score_list = score_list[score_list.notna()]
        try:
            bins = score_list.quantile([.1, .2, .3, .4, .5, .6, .7, .8, .9]).values.tolist()
            bins = [-99999999] + bins + [99999999]
            bins = [x for x in bins if pd.notna(x)]
            if len(set(bins)) < 11:
                return None
            return bins
        except:
            print('分箱出现错误.')
            with open('bin_error.pkl', 'wb') as f:
                pickle.dump(score_list, f)
                f.close()

    def calc_psi(self, array_1, array_2):
        '''
        计算PSI.
        :param array_1: array
        :param array_2: array
        :return: PSI
        '''
        try:
            psi = ((array_1 - array_2) * np.log10(array_1 / array_2)).sum()
            return psi
        except:
            return None

    def helper_psi(self, user_group_name=None, df=None, info_dict=None, field=None):
        '''
        信息提取函数.
        :param user_group_name: str, 客群名称.
        :param df: Dataframe, 对应客群数据.
        :return: None.
        '''
        print('正在处理%s客群数据.' % user_group_name)
        info_dict[user_group_name] = OrderedDict()
        month_list = list(sorted(df['month_label'].unique().tolist()))
        if 0 in month_list:
            month_list.remove(0)

        df_g = df.groupby(['month_label', 'bins']).agg({field: ['count']})

        df_g = df_g.reset_index()
        df_g = df_g.sort_values(['month_label', 'bins'])
        for i, m in enumerate(month_list):
            amt_in_bins = df_g.loc[df_g['month_label'] == m, field].values
            ## 某月样本量小于阈值, 放弃记录信息.
            if amt_in_bins.sum() < self.min_user_group:
                print('%d月样本量过小, 放弃提取信息.' % m)
                continue
            info_dict[user_group_name][str(m) + '月'] = {}
            info_dict[user_group_name][str(m) + '月']['该月样本量'] = amt_in_bins.sum()
            info_dict[user_group_name][str(m) + '月']['各分箱样本量'] = amt_in_bins
            info_dict[user_group_name][str(m) + '月']['各分箱样本占比'] = np.array([x[0] for x in amt_in_bins / amt_in_bins.sum()])
            ## 若为某模型分全样本, 则记录分箱比例.
            if user_group_name == '全样本' and self.filed_bench_bins_ratio is None:
                self.filed_bench_bins_ratio = info_dict[user_group_name][str(m) + '月']['各分箱样本占比']
            print('%d月样本量: %d' % (m, info_dict[user_group_name][str(m) + '月']['该月样本量']))
        # 计算PSI, 以样本量达标的第一个月为基准.
        for i, m in enumerate(info_dict[user_group_name]):
            if i == 0:
                if user_group_name == '全样本':
                    psi = 0
                else:
                    psi = self.calc_psi(self.filed_bench_bins_ratio, info_dict[user_group_name][m]['各分箱样本占比'])
                if psi is not None:
                    info_dict[user_group_name][m]['psi'] = psi
                else:
                    info_dict[user_group_name][m]['psi'] = -999
                    print('计算PSI出现错误.')
                bench_bins_ratio = info_dict[user_group_name][m]['各分箱样本占比']
            else:
                psi = self.calc_psi(bench_bins_ratio, info_dict[user_group_name][m]['各分箱样本占比'])
                if psi:
                    info_dict[user_group_name][m]['psi'] = psi
                else:
                    info_dict[user_group_name][m]['psi'] = -999
                    print('计算PSI出现错误.')
        print('处理完成.')
        print('=' * 40)

    def helper_auc(self, user_group_name=None, df=None, info_dict=None, field=None):
        '''
        信息提取函数.
        :param user_group_name: str, 客群名称.
        :param df: Dataframe, 对应客群数据.
        :return: None.
        '''
        print('正在处理%s客群数据.' % user_group_name)
        info_dict[user_group_name] = OrderedDict()
        month_list = list(sorted(df['month_label'].unique().tolist()))
        if 0 in month_list:
            month_list.remove(0)

        df_g = df.groupby(['month_label', 'bins'])['overdue'].agg({'overdue': ['count', 'sum', 'mean']})
        df_g.columns = ['_'.join(x) for x in df_g.columns.ravel()]
        df_g = df_g.reset_index()
        df_g = df_g.sort_values(['month_label', 'bins'])

        for i, m in enumerate(month_list):
            amt_in_bins = df_g.loc[df_g['month_label'] == m, 'overdue_count'].values
            # 某月样本量小于阈值, 放弃记录信息.
            if amt_in_bins.sum() < self.min_user_group:
                print('%d月样本量过小, 放弃提取信息.' % m)
                continue
            info_dict[user_group_name][str(m) + '月'] = {}

            info_dict[user_group_name][str(m) + '月']['该月样本量'] = amt_in_bins.sum()
            info_dict[user_group_name][str(m) + '月']['各分箱样本量'] = amt_in_bins
            info_dict[user_group_name][str(m) + '月']['各分箱逾期样本量'] = df_g.loc[
                df_g['month_label'] == m, 'overdue_sum'].values
            info_dict[user_group_name][str(m) + '月']['各分箱逾期率'] = df_g.loc[
                df_g['month_label'] == m, 'overdue_mean'].values
            print('%d月样本量: %d' % (m, info_dict[user_group_name][str(m) + '月']['该月样本量']))
            try:
                info_dict[user_group_name][str(m) + '月']['auc'] = roc_auc_score(
                    df.loc[(df['month_label'] == m) & (df[field].notna()), 'overdue'],
                    df.loc[(df['month_label'] == m) & (df[field].notna()), field])
            except:
                print('AUC计算发生错误.')
                info_dict[user_group_name][str(m) + '月']['auc'] = -999
            if user_group_name == '全样本' and self.filed_bench_auc is None:
                self.filed_bench_auc = info_dict[user_group_name][str(m) + '月']['auc']

        for i, m in enumerate(info_dict[user_group_name]):
            if i == 0:  # 基准月.
                info_dict[user_group_name][m]['aucR'] = info_dict[user_group_name][m]['auc'] / self.filed_bench_auc
                bench_month = m
            else:
                info_dict[user_group_name][m]['aucR'] = info_dict[user_group_name][m]['auc'] / \
                                                        info_dict[user_group_name][bench_month]['auc']

        print('处理完成.')
        print('=' * 40)

    def plot_psi(self, field):
        # 创建文件夹保存图片.
        if not os.path.exists(self.save_path):
            os.mkdir(self.save_path)
        if not os.path.exists(self.save_path + 'PSI/'):
            os.mkdir(self.save_path + 'PSI/')
        # 分离数据.
        df_copy = self.merge_data[[field, 'month_label', 'applied_type', 'applied_channel']].copy()

        # 对模型分进行分箱, 选取数据中该模型分最开始的那个月作为基准.
        for m in range(self.first_month, self.current_month + 1):
            bins = self.make_bin(df_copy.loc[df_copy['month_label'] == m, field])
            if bins:
                print('%s以%d月为基准月.' % (self.model_feild_name_dict[field], m))
                break
        if not bins:
            self.na_enough_data_psi_set.add(self.model_feild_name_dict[field])
            print('%s 数据时间跨度不足, 放弃画图.' % self.model_feild_name_dict[field])
            print('=' * 40)
            return None

        df_copy['bins'] = pd.cut(df_copy[field], bins)  # 根据分箱规则进行分箱.

        # 包含各种信息的字典.
        # 如: {'全样本':
        #               {'3月':
        #                       {'psi': 0,
        #                        '该月样本量': int.
        #                        '各分箱样本量': [...],
        #                        '各分箱样本占比': [...]}
        #                '4月':
        #                       {'psi': float,
        #                        '该月样本量': int.
        #                        '各分箱样本量': [...],
        #                        '各分箱样本占比': [...]}}}
        info_dict = {}

        # 全样本
        self.helper_psi('全样本', df_copy, info_dict, field)
        # 按申请类型划分.
        self.helper_psi('首申-全渠道', df_copy.loc[df_copy['applied_type'] == 1], info_dict, field)
        self.helper_psi('复申-全渠道', df_copy.loc[df_copy['applied_type'] == 2], info_dict, field)
        self.helper_psi('复贷-全渠道', df_copy.loc[df_copy['applied_type'] == 3], info_dict, field)

        # 按主要客群划分.
        ## 客群划分.
        ## user_group_dict = {'首申-融360': (1, 融360)}
        user_group_dict = {}
        app_type_dict = {1: '首申', 2: '复申', 3: '复贷'}
        df_copy_g = df_copy.groupby(['applied_type', 'applied_channel'])[field].count().sort_values(ascending=False)
        df_copy_g = df_copy_g.reset_index()
        ## 过滤小客群.
        df_copy_g = df_copy_g.loc[df_copy_g[field] > self.min_user_group * self.num_month]
        app_type_set = df_copy_g['applied_type'].unique()
        app_chan_set = df_copy_g['applied_channel'].unique()
        for app_type in app_type_set:
            for app_chan in app_chan_set:
                if df_copy_g.loc[
                    (df_copy_g['applied_type'] == app_type) & (df_copy_g['applied_channel'] == app_chan)].shape[0] != 0:
                    user_group_dict[app_type_dict[app_type] + '-' + app_chan] = (app_type, app_chan)
        del df_copy_g
        ## 按划分的客群处理数据.
        for user_group_name in user_group_dict:
            self.helper_psi(user_group_name, df_copy.loc[
                (df_copy['applied_type'] == user_group_dict[user_group_name][0]) & (
                            df_copy['applied_channel'] == user_group_dict[user_group_name][1])],
                            info_dict, field)
        # 过滤不包含信息的客群.
        remove_list = []
        for user_group_name in info_dict:
            if not info_dict[user_group_name]:
                remove_list.append(user_group_name)
        for user_group_name in remove_list:
            del info_dict[user_group_name]
        self.filed_bench_bins_ratio = None

        # 画图.
        print('开始画图.')
        print('=' * 40)
        for user_group_name in info_dict:
            print(self.model_feild_name_dict[field] + '-' + user_group_name)
            plt.figure(figsize=(16, 8))
            for m in info_dict[user_group_name]:
                # print(m)
                # print(info_dict[user_group_name][m]['psi'])
                # print(info_dict[user_group_name][m]['该月样本量'])
                plt.plot(range(len(info_dict[user_group_name][m]['各分箱样本占比'])),
                         [round(x, 3) for x in info_dict[user_group_name][m]['各分箱样本占比']],
                         label='%s PSI: %.3f \n 样本量: %d' % (
                         m, info_dict[user_group_name][m]['psi'], info_dict[user_group_name][m]['该月样本量']))
            plt.legend(loc='upper right')
            plt.title(self.model_feild_name_dict[field] + '-' + user_group_name)
            plt.savefig(self.save_path + 'PSI/' + self.model_feild_name_dict[field] + '-' + user_group_name)
            plt.show()

        # 保存统计信息.
        for user_group_name in info_dict:
            # print(self.model_feild_name_dict[field] + '-' + user_group_name)
            tmp_dict = {'模型名称': [self.model_feild_name_dict[field]],
                        '客群名称': [user_group_name]}
            for m in info_dict[user_group_name]:
                tmp_dict[m[0] + '月数量'] = [int(info_dict[user_group_name][m]['该月样本量'])]
                tmp_dict[m[0] + '月PSI'] = [round(info_dict[user_group_name][m]['psi'], 3)]
            self.psi_info_df = self.psi_info_df.append(pd.DataFrame(tmp_dict))

    def plot_auc(self, field):
        # 创建文件夹保存图片.
        if not os.path.exists(self.save_path):
            os.mkdir(self.save_path)
        if not os.path.exists(self.save_path + 'AUC/'):
            os.mkdir(self.save_path + 'AUC/')
        # 分离数据.
        df_copy = self.merge_data[
            [field, 'month_label', 'applied_type', 'applied_channel', 'overdue', 'passdue_day', 'applied_at']].copy()
        ## 筛选出放款, 且逾期表现的数据.
        if repr(df_copy['applied_at'].dtype) == "dtype('O')":
            df_copy = df_copy.loc[
                (df_copy[field].notna()) & (df_copy['applied_at'].apply(lambda x: x[:10]) <= self.response_date) & (
                            df_copy[field] > 0) & (df_copy['passdue_day'].notna())]
        else:
            df_copy = df_copy.loc[(df_copy[field].notna()) & (
                        df_copy['applied_at'].apply(lambda x: x.strftime('%Y-%m-%d')) <= self.response_date) & (
                                              df_copy[field] > 0) & (df_copy['passdue_day'].notna())]

        # 对模型分进行分箱, 选取数据中该模型分最开始的那个月作为基准.
        for m in range(self.first_month, self.response_month + 1):
            bins = self.make_bin(df_copy.loc[df_copy['month_label'] == m, field])
            if bins:
                print('%s以%d月为基准月.' % (self.model_feild_name_dict[field], m))
                break
        if not bins:
            self.na_enough_data_auc_set.add(self.model_feild_name_dict[field])
            print('%s 数据时间跨度不足, 放弃画图.' % self.model_feild_name_dict[field])
            print('=' * 40)
            return None

        df_copy['bins'] = pd.cut(df_copy[field], bins)  # 根据分箱规则进行分箱.

        # 包含各种信息的字典.
        # 如: {'全样本':
        #               {'3月':
        #                       {'该月样本量': int.
        #                        '各分箱样本量': [...],
        #                        '各分箱逾期样本量': [...],
        #                        '各分箱逾期率': [...],
        #                        'auc': float,
        #                        'aucR': 1}
        #                '4月':
        #                       {'该月样本量': int.
        #                        '各分箱样本量': [...],
        #                        '各分箱逾期样本量': [...],
        #                        '各分箱逾期率': [...],
        #                        'auc': float,
        #                        'aucR': float}}}
        info_dict = {}

        # 全样本
        self.helper_auc('全样本', df_copy, info_dict, field)
        # 按申请类型划分.
        self.helper_auc('首申-全渠道', df_copy.loc[df_copy['applied_type'] == 1], info_dict, field)
        self.helper_auc('复申-全渠道', df_copy.loc[df_copy['applied_type'] == 2], info_dict, field)
        self.helper_auc('复贷-全渠道', df_copy.loc[df_copy['applied_type'] == 3], info_dict, field)

        # 按主要客群划分.
        ## 客群划分.
        ## user_group_dict = {'首申-融360': (1, 融360)}
        user_group_dict = {}
        app_type_dict = {1: '首申', 2: '复申', 3: '复贷'}
        df_copy_g = df_copy.groupby(['applied_type', 'applied_channel'])[field].count().sort_values(ascending=False)
        df_copy_g = df_copy_g.reset_index()
        ## 过滤小客群.
        df_copy_g = df_copy_g.loc[df_copy_g[field] > self.min_user_group * (self.num_month - 1)]
        app_type_set = df_copy_g['applied_type'].unique()
        app_chan_set = df_copy_g['applied_channel'].unique()
        for app_type in app_type_set:
            for app_chan in app_chan_set:
                if df_copy_g.loc[
                    (df_copy_g['applied_type'] == app_type) & (df_copy_g['applied_channel'] == app_chan)].shape[0] != 0:
                    user_group_dict[app_type_dict[app_type] + '-' + app_chan] = (app_type, app_chan)
        del df_copy_g
        ## 按划分的客群处理数据.
        for user_group_name in user_group_dict:
            self.helper_auc(user_group_name,
                            df_copy.loc[(df_copy['applied_type'] == user_group_dict[user_group_name][0]) & (
                                    df_copy['applied_channel'] == user_group_dict[user_group_name][1])], info_dict,
                            field)
        # 过滤不包含信息的客群.
        remove_list = []
        for user_group_name in info_dict:
            if not info_dict[user_group_name]:
                remove_list.append(user_group_name)
        for user_group_name in remove_list:
            del info_dict[user_group_name]
        self.filed_bench_auc = None

        # 画图.
        print('开始画图.')
        print('=' * 40)
        for user_group_name in info_dict:
            print(self.model_feild_name_dict[field] + '-' + user_group_name)
            plt.figure(figsize=(16, 8))
            for m in info_dict[user_group_name]:
                ## 若某月数量少, 则放弃画图.
                if info_dict[user_group_name][m]['该月样本量'] < self.min_user_group:
                    print('样本量少于阈值, 放弃画图.')
                    break
                plt.plot(range(len(info_dict[user_group_name][m]['各分箱逾期率'])),
                         info_dict[user_group_name][m]['各分箱逾期率'],
                         label='%s AUC: %.3f AUCR: %.3f \n 样本量: %d' % (
                             m, info_dict[user_group_name][m]['auc'], info_dict[user_group_name][m]['aucR'],
                             info_dict[user_group_name][m]['该月样本量']))
            plt.legend(loc='upper right')
            plt.title(self.model_feild_name_dict[field] + '-' + user_group_name)
            plt.savefig(self.save_path + 'AUC/' + self.model_feild_name_dict[field] + '-' + user_group_name)
            plt.show()

        # 保存统计信息.
        for user_group_name in info_dict:
            tmp_dict = {'模型名称': [self.model_feild_name_dict[field]],
                        '客群名称': [user_group_name]}
            for m in info_dict[user_group_name]:
                tmp_dict[m[0] + '月数量'] = [int(info_dict[user_group_name][m]['该月样本量'])]
                tmp_dict[m[0] + '月AUC'] = [round(info_dict[user_group_name][m]['auc'], 3)]
                tmp_dict[m[0] + '月AUCR'] = [round(info_dict[user_group_name][m]['aucR'], 3)]

            self.auc_info_df = self.auc_info_df.append(pd.DataFrame(tmp_dict))

    def abnormal_psi(self):
        def is_abnormal_psi(data):
            first = True
            for idx in data.index:
                if 'PSI' in idx and pd.notna(data[idx]):
                    if first:
                        if data[idx] > self.max_psi * 5:
                            return True
                        else:
                            first = False
                    else:
                        if data[idx] > self.max_psi:
                            return True
            return False

        self.psi_info_df['是否异常'] = self.psi_info_df.apply(is_abnormal_psi, axis=1)

    def abnormal_auc(self):
        def is_abnormal_auc(data):
            for i in data.index:
                if 'AUCR' in i and data[i] < self.min_aucr:
                    return True
            return False

        self.auc_info_df['是否异常'] = self.auc_info_df.apply(is_abnormal_auc, axis=1)

    def run(self):
        # 获取MySQL数据, 取近期num_month个月数据(如今天7.27, 则这27天算进7月).
        if not self.if_load:
            self.mysql_df = self.sql_query('''SELECT order_no, applied_at,
                                            applied_type, applied_from, applied_channel, transacted, passdue_day
                                            FROM risk_analysis
                                            WHERE applied_at >= "%s 00:00:00"
                                            AND applied_at <= "%s 00:00:00"'''
                                           % (self.first_date, self.current_date))
            print('MySQL数据获取成功.')
            if self.if_save:
                self.mysql_df.to_csv('./mysql_data.csv', index=False)
        else:
            self.mysql_df = pd.read_csv('./mysql_data.csv')

        # 获取MongoDB数据, 取近期num_month个月数据(如今天7.27, 则这27天算进7月).
        if not self.if_load:
            condition = {'wf_created_at': {'$gte': '%s 00:00:00' % self.first_date,
                                           '$lte': '%s 00:00:00' % self.current_date}}
            fields = {'wf_biz_no': 1, 'wf_created_at': 1}
            for f in self.model_feild_list:  # 加入Excel中预置的模型分名称
                fields[f] = 1
            self.mongo_df = self.mongo_query(condition, fields)
            print('MongoDB数据获取成功.')
            if self.if_save:
                self.mongo_df.to_csv('./mongo_data.csv', index=False)
        else:
            self.mongo_df = pd.read_csv('./mongo_data.csv')

        # MySQL数据去重.
        self.mysql_df = self.mysql_df.sort_values('passdue_day')
        self.mysql_df = self.mysql_df.drop_duplicates('order_no', keep='first')
        print('数据去重完成.')

        # 拼接数据.
        self.merge_data = pd.merge(left=self.mysql_df, right=self.mongo_df,
                                   left_on='order_no', right_on='wf_biz_no', how='left')

        ## 定义逾期用户.
        def overdue(data):
            if pd.isnull(data):
                return np.nan
            else:
                return float(data > self.passdue_day)

        self.merge_data['overdue'] = self.merge_data['passdue_day'].apply(overdue)

        # 清洗数据.
        def clean_data(data):
            try:
                return float(data)
            except:
                return np.nan

        na_field_list = []
        for field in self.model_feild_list:
            if field in self.merge_data.columns.tolist():
                print('正在清洗%s' % self.model_feild_name_dict[field])
                self.merge_data[field] = self.merge_data[field].apply(clean_data)
            else:
                na_field_list.append(field)
        ## 去除因为一些原因未抽取到的字段.
        print('不包含以下字段:')
        for field in na_field_list:
            print(self.model_feild_name_dict[field])
            self.model_feild_list.remove(field)
            self.model_name_list.remove(self.model_feild_name_dict[field])
            del self.model_feild_name_dict[field]
        print('数据拼接完成.')

        # 数据按月划分.
        self.merge_data['month_label'] = 0
        for m in range(self.first_month, self.current_month + 1):
            self.merge_data.loc[
                (self.merge_data['applied_at'] >= '2019-%s-01 00:00:00' % self.int2str(m)) &
                (self.merge_data['applied_at'] < '2019-%s-01 00:00:00' % self.int2str(m + 1)),
                'month_label'] = m

        # 画图.

        ## PSI.
        print('开始画图-PSI.')
        for field in self.model_feild_list:
            self.plot_psi(field)
        ## AUC.
        print('开始画图-AUC.')
        for field in self.model_feild_list:
            self.plot_auc(field)

        # 输出数据不足的模型.
        print('PSI 数据不足以统计的模型:')
        for model_name in self.na_enough_data_psi_set:
            print(model_name)
        print('=' * 40)
        print('AUC 数据不足以统计的模型:')
        for model_name in self.na_enough_data_auc_set:
            print(model_name)
        print('=' * 40)

        # 检测是否异常.
        self.abnormal_psi()
        self.abnormal_auc()

        # 保存统计信息.
        self.psi_info_df.to_csv('./psi_info.csv', index=False)
        self.auc_info_df.to_csv('./auc_info.csv', index=False)
        print('统计信息保存成功.')

# if __name__ == '__main__':
#     pass
#     mm = ModelMonitor(excel_path='./model_score.xlsx', save_path='./image/', last_month=7, num_month=2)
