from hinnc，添加了后面的
if __name__ == '__main__'
# -*- coding: utf-8 -*- """ Created on Mon Jan 14 18:57:19 2019 @author: hinnc """ import numpy as np import pandas as pd #from pandas.tseries.offsets import DateOffset from datetime import timedelta, datetime def vintageCreation(contract, sjhk, release_ym = '放款月份', dpd_m = 30, contract_key = '申请编号', sjhk_key = '单号', repay_dt = '账单日', act_repay_dt = '结清日期', sjhk_need_amt = '实还本息', contract_need_amt = '本息和', start_dt = '2015-04-01', end_dt = '2019-01-01', file_name = 'Vintage'): ''' contract: DataFrame, 合同信息表, 需包含release_ym, contract_key和contract_need_amt sjhk: DataFrame, 实际还款表, 需包含 sjhk_key, repay_dt, act_repay_dt和sjhk_need_amt release_ym: str, 放款年月 dpd_m: int, 逾期定义的分界点, 函数按大于此天数计算逾期情况 contract_key: str, contract表的唯一标示，需要与sjhk的sjhk_key匹配 sjhk_key: str, 还款计划表的唯一标示，需要与contract表的contract_key匹配 repay_dt: str, 应还款日期 act_repay_dt: str, 实际还款日期 sjhk_need_amt: str, 实际还款金额 contract_need_amt: str, 合同中应还款金额 start_dt: str, Vintage表格中列的起始时间 end_dt: str, Vintage表格中列的结束时间 ''' # 生成列表头，即观察时点，'pd.date_range' function set the 'freq' (frequency) to 'M' (month end frequency) obs_list = [str(i.date()) for i in (pd.date_range(start = start_dt, end= end_dt, freq = 'M')).tolist()] # 预留 Vintage金额和合同数的 DataFrame vintage = pd.DataFrame(columns = obs_list) vintage_prin = pd.DataFrame(columns = obs_list) vintage_n = pd.DataFrame(columns = obs_list) vintage_num = pd.DataFrame(columns = obs_list) for i in sorted(contract[release_ym].unique()): tmp = pd.DataFrame(columns = obs_list) tmp_num = pd.DataFrame(columns = obs_list) df_sjhk = sjhk.loc[sjhk[sjhk_key].isin(contract.loc[contract[release_ym] == i, contract_key]), :] #每一个观察时点分别计算 for j in tmp.columns.tolist(): df_sjhk_tmp = df_sjhk.loc[df_sjhk[repay_dt] < (pd.to_datetime(j) + timedelta(days = 1)), [sjhk_key, repay_dt, act_repay_dt, sjhk_need_amt]] if len(df_sjhk_tmp) == 0: tmp[j] = [0] tmp_num[j] = [0] else: #当前观察时点逾期天数 df_sjhk_tmp.loc[pd.notnull(df_sjhk_tmp[act_repay_dt]) & (df_sjhk_tmp[act_repay_dt] < (pd.to_datetime(j) + timedelta(days = 1))), 'dpd'] = 0 df_sjhk_tmp.loc[pd.isnull(df_sjhk_tmp[act_repay_dt]) | (df_sjhk_tmp[act_repay_dt] >= (pd.to_datetime(j) + timedelta(days = 1))), 'dpd'] = (pd.to_datetime(j) - df_sjhk_tmp.loc[pd.isnull(df_sjhk_tmp[act_repay_dt]) | (df_sjhk_tmp[act_repay_dt] >= (pd.to_datetime(j) + timedelta(days = 1))), repay_dt]).dt.days current = df_sjhk_tmp.groupby(sjhk_key)[['dpd']].max() current.reset_index(inplace = True) current_m = current.loc[current['dpd'] > dpd_m, :] #当前逾期金额 = 总金额 - 已还金额 tmp[j] = [(contract.loc[contract[contract_key].isin(current_m[sjhk_key]), contract_need_amt].sum() - df_sjhk_tmp.loc[(df_sjhk_tmp[sjhk_key].isin(current_m[sjhk_key])) & (df_sjhk_tmp['dpd'] == 0), sjhk_need_amt].sum())] #当前逾期合同数 tmp_num[j] = [len(current_m)] # Vintage金额比例的分子/分母（逾期本金 or 逾期本息/放款本金 or 放款本息） vintage = pd.concat([vintage, tmp]) prin_tmp = np.array([contract.loc[contract[release_ym] == i, contract_need_amt].sum()] * vintage_prin.shape[1]).reshape((1, vintage_prin.shape[1])) prin_df = pd.DataFrame(prin_tmp, columns = obs_list) vintage_prin = pd.concat([vintage_prin, prin_df]) # Vintage合同数比例的分子/分母（逾期合同数/放款合同数） vintage_n = pd.concat([vintage_n, tmp_num]) num_tmp = np.array([len(contract.loc[contract[release_ym] == i, :])] * vintage_num.shape[1]).reshape((1, vintage_num.shape[1])) num_df = pd.DataFrame(num_tmp, columns = obs_list) vintage_num = pd.concat([vintage_num, num_df]) vintage.set_index(keys = pd.Series(sorted(contract['放款月份'].unique())).map(lambda x: str(x)), inplace = True) vintage_prin.set_index(keys = pd.Series(sorted(contract['放款月份'].unique())).map(lambda x: str(x)), inplace = True) vintage_pct = vintage/vintage_prin vintage_n.set_index(keys = pd.Series(sorted(contract['放款月份'].unique())).map(lambda x: str(x)), inplace = True) vintage_num.set_index(keys = pd.Series(sorted(contract['放款月份'].unique())).map(lambda x: str(x)), inplace = True) vintage_n_pct = vintage_n/vintage_num #输出结果 writer = pd.ExcelWriter(('{}_{}.xlsx'.format(file_name, datetime.now().strftime('%Y%m%d')))) vintage_pct.to_excel(writer, sheet_name ='vintage_金额比例', index = True) vintage.to_excel(writer, sheet_name ='vintage_逾期金额', index = True) vintage_prin.to_excel(writer, sheet_name ='vintage_放款金额', index = True) vintage_n_pct.to_excel(writer, sheet_name ='vintage_数量比例', index = True) vintage_n.to_excel(writer, sheet_name ='vintage_逾期合同数', index = True) vintage_num.to_excel(writer, sheet_name ='vintage_放款合同数', index = True) writer.save() if __name__ == '__main__': contract=pd.read_excel('xxx\contract.xlsx') sjhk=pd.read_excel('xxx\shqk.xlsx') vintageCreation(contract, sjhk)