用python计算工程量_使用python计算vintage

#-*- coding: utf-8 -*-

"""Created on Mon Jan 14 18:57:19 2019

@author: hinnc"""

importnumpy as npimportpandas as pd#from pandas.tseries.offsets import DateOffset

from datetime importtimedelta, datetimedef 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 insorted(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 intmp.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)