import pandas as pd
import numpy as np
from pandas import Series,DataFrame
from decimal import Decimal
import sys
import importlib
import datetime
import numpy as np
from pandas import Series,DataFrame
from decimal import Decimal
import sys
import importlib
import datetime
importlib.reload(sys)
data_T=datetime.date.today()
print(data_T)
Ref_file=r"美元债/"+str(data_T)+r"/1.csv"
data_T=datetime.date.today()
print(data_T)
Ref_file=r"美元债/"+str(data_T)+r"/1.csv"
#print(Ref_file)
#读取csv文件
df1=pd.read_csv(Ref_file)
#读取csv文件
df1=pd.read_csv(Ref_file)
#print(df1)
#**********************************************************************基础数据加工**************************************************************
#数据清洗流程
#print(df1.shape[0])
df2=df1.drop(df1[((df1.INT_ACC_DT=="N.A.")|(df1.INT_ACC_DT.isnull()))|((df1.CPN_FREQ!=0)&(df1.PENULTIMATE_CPN_DT=="N.A."))|((df1.CPN_FREQ!=0)&(df1.PENULTIMATE_CPN_DT.isnull())|((df1.CPN_FREQ==0)&(df1.PENULTIMATE_CPN_DT!="N.A."))|((df1.CPN_FREQ.isnull())|(df1.CPN_FREQ=="N.A.")))].index)
#df2[['INT_ACC_DT','CPN_FREQ','PENULTIMATE_CPN_DT']]
print(df2)
df3=df2.drop(df2[((df2.SECURITIES=="N.A.")|(df2.SECURITIES.isnull())|df2.SECURITIES.str.isspace())
|((df2.CRNCY=="N.A.")|(df2.CRNCY.isnull())|df2.CRNCY.str.isspace())
|((df2.YRS_TO_MTY_ISSUE=="N.A.")|(df2.YRS_TO_MTY_ISSUE.isnull())|df2.YRS_TO_MTY_ISSUE.str.isspace())
|((df2.ISSUE_DT=="N.A.")|(df2.ISSUE_DT.isnull())|df2.ISSUE_DT.str.isspace())
|((df2.CPN=="N.A.")|(df2.CPN.isnull())|df2.CPN.str.isspace())
|((df2.MATURITY=="N.A.")|(df2.MATURITY.isnull())|df2.MATURITY.str.isspace())
|((df2.SECURITY_NAME=="N.A.")|(df2.SECURITY_NAME.isnull())|df2.SECURITY_NAME.str.isspace())
|((df2.FIRST_CPN_DT=="N.A.")|(df2.FIRST_CPN_DT.isnull())|df2.FIRST_CPN_DT.str.isspace())
|((df2.FIRST_CPN_PERIOD_TYP=="N.A.")|(df2.FIRST_CPN_PERIOD_TYP.isnull())|df2.FIRST_CPN_PERIOD_TYP.str.isspace())
|((df2.LAST_CPN_PERIOD_TYP=="N.A.")|(df2.LAST_CPN_PERIOD_TYP.isnull())|(df2.LAST_CPN_PERIOD_TYP.str.isspace()))
].index)
#print(df3['YRS_TO_MTY_ISSUE'])
#读取白名单
bmd_file=r"美元债/"+r"bmd.csv"
df4= pd.read_csv(bmd_file)
#以tmp表为主表，左关联白名单表
df5=pd.merge(df3,df4,on='LONG_COMP_NAME',how='left')
#df5.to_csv('out10.csv')
#**********************************************************************基础数据加工**************************************************************
#数据清洗流程
#print(df1.shape[0])
df2=df1.drop(df1[((df1.INT_ACC_DT=="N.A.")|(df1.INT_ACC_DT.isnull()))|((df1.CPN_FREQ!=0)&(df1.PENULTIMATE_CPN_DT=="N.A."))|((df1.CPN_FREQ!=0)&(df1.PENULTIMATE_CPN_DT.isnull())|((df1.CPN_FREQ==0)&(df1.PENULTIMATE_CPN_DT!="N.A."))|((df1.CPN_FREQ.isnull())|(df1.CPN_FREQ=="N.A.")))].index)
#df2[['INT_ACC_DT','CPN_FREQ','PENULTIMATE_CPN_DT']]
print(df2)
df3=df2.drop(df2[((df2.SECURITIES=="N.A.")|(df2.SECURITIES.isnull())|df2.SECURITIES.str.isspace())
|((df2.CRNCY=="N.A.")|(df2.CRNCY.isnull())|df2.CRNCY.str.isspace())
|((df2.YRS_TO_MTY_ISSUE=="N.A.")|(df2.YRS_TO_MTY_ISSUE.isnull())|df2.YRS_TO_MTY_ISSUE.str.isspace())
|((df2.ISSUE_DT=="N.A.")|(df2.ISSUE_DT.isnull())|df2.ISSUE_DT.str.isspace())
|((df2.CPN=="N.A.")|(df2.CPN.isnull())|df2.CPN.str.isspace())
|((df2.MATURITY=="N.A.")|(df2.MATURITY.isnull())|df2.MATURITY.str.isspace())
|((df2.SECURITY_NAME=="N.A.")|(df2.SECURITY_NAME.isnull())|df2.SECURITY_NAME.str.isspace())
|((df2.FIRST_CPN_DT=="N.A.")|(df2.FIRST_CPN_DT.isnull())|df2.FIRST_CPN_DT.str.isspace())
|((df2.FIRST_CPN_PERIOD_TYP=="N.A.")|(df2.FIRST_CPN_PERIOD_TYP.isnull())|df2.FIRST_CPN_PERIOD_TYP.str.isspace())
|((df2.LAST_CPN_PERIOD_TYP=="N.A.")|(df2.LAST_CPN_PERIOD_TYP.isnull())|(df2.LAST_CPN_PERIOD_TYP.str.isspace()))
].index)
#print(df3['YRS_TO_MTY_ISSUE'])
#读取白名单
bmd_file=r"美元债/"+r"bmd.csv"
df4= pd.read_csv(bmd_file)
#以tmp表为主表，左关联白名单表
df5=pd.merge(df3,df4,on='LONG_COMP_NAME',how='left')
#df5.to_csv('out10.csv')
#**********************************************************************债券资料表**************************************************************
#df4.to_csv('out.csv')
df5['ZQDM']=df5['SECURITIES']
df5['ISSUE_DT']=df5['ISSUE_DT'].str[2:4]
df5['CPN']=df5['CPN'].map(lambda x:str(x))
for i in df5['规则']:
if pd.isnull(i):
df5['ZQJC']=df5['ISSUE_DT'].str.cat(df5['SECURITY_NAME'],sep=' ').str.cat(df5['CPN'],sep=' ').str.cat(df5['MATURITY'],sep=' ')
else:
df5['ZQJC']=df5['ISSUE_DT'].str.cat(df5['规则'],sep=' ').str.cat(df5['CPN'],sep=' ').str.cat(df5['MATURITY'],sep=' ')
if pd.isnull(i):
df5['ZQJC']=df5['ISSUE_DT'].str.cat(df5['SECURITY_NAME'],sep=' ').str.cat(df5['CPN'],sep=' ').str.cat(df5['MATURITY'],sep=' ')
else:
df5['ZQJC']=df5['ISSUE_DT'].str.cat(df5['规则'],sep=' ').str.cat(df5['CPN'],sep=' ').str.cat(df5['MATURITY'],sep=' ')
df5.loc[df5['CRNCY']=='CNY','CRNCY']='01'
df5.loc[df5['CRNCY']=='USD','CRNCY']='02'
df5.loc[df5['CRNCY']=='HKD','CRNCY']='03'
df5.loc[df5['CRNCY']=='EUR','CRNCY']='04'
df5.loc[df5['CRNCY']=='GBP','CRNCY']='05'
df5.loc[df5['CRNCY']=='JPY','CRNCY']='06'
df5.loc[df5['CRNCY']=='SDR','CRNCY']='07'
df5.loc[df5['CRNCY']=='USD','CRNCY']='02'
df5.loc[df5['CRNCY']=='HKD','CRNCY']='03'
df5.loc[df5['CRNCY']=='EUR','CRNCY']='04'
df5.loc[df5['CRNCY']=='GBP','CRNCY']='05'
df5.loc[df5['CRNCY']=='JPY','CRNCY']='06'
df5.loc[df5['CRNCY']=='SDR','CRNCY']='07'
df5['BZDM']=df5['CRNCY']
df5['ZQXZDM']=44
df5['ZQXZDM']=44
df5['ZQQX']=[Decimal(df5['YRS_TO_MTY_ISSUE'].values[index]).quantize(Decimal("0")) if item==1 else int(df5['MATURITY'].values[index][0:4])-int(df5['INT_ACC_DT'].values[index][0:4]) for index ,item in enumerate([1 if float(status)>1 else 0 for status in df5['YRS_TO_MTY_ISSUE']])]
df5['ZQQXDW']=['Y' if item=='1' else 'D' for index ,item in enumerate([1 if float(status)>1 else 0 for status in df5['YRS_TO_MTY_ISSUE']])]
#print(df5['YRS_TO_MTY_ISSUE'].index)
# for index ,values in enumerate(df5['YRS_TO_MTY_ISSUE']):
# if float(values)>1:
# df5["ZQQXDW"].values[index]='Y'
# else:
# df5['ZQQXDW'].values[index]='D'
# for index ,values in enumerate(df5['YRS_TO_MTY_ISSUE']):
# if float(values)>1:
# print(Decimal(df5['YRS_TO_MTY_ISSUE'].values[index]).quantize(Decimal("0")))
# else:
# print(int(df5['MATURITY'].values[index][0:4])-int(df5['INT_ACC_DT'].values[index][0:4]))
df5['PMLL'] = df5['CPN'].map(lambda x: float(x)/100)
#print(df5[['PMLL','CPN']])
df5['QXR']=df5['INT_ACC_DT']
df5['DQR']=df5['MATURITY']
df5['FXSJCLL']=0
df5['FXSLC']=0
#print(df5[['PMLL','CPN']])
df5['QXR']=df5['INT_ACC_DT']
df5['DQR']=df5['MATURITY']
df5['FXSJCLL']=0
df5['FXSLC']=0
#print((df.a/df.b).replace(np.inf,0))
df5['12']=12
df5['FXPL']=df5['12']/df5.CPN_FREQ.replace(np.inf,0)
df5['12']=12
df5['FXPL']=df5['12']/df5.CPN_FREQ.replace(np.inf,0)
df5.loc[df5['CPN_FREQ']==0,'JXFSDM']=20
df5.loc[df5['CPN_FREQ']!=0,'JXFSDM']=31
df5.loc[df5['CPN_FREQ']!=0,'JXFSDM']=31
df5['JHFXZE']=df5['AMT_OUTSTANDING']
df5['SJFXZE']=df5['AMT_OUTSTANDING']
df5['SJQBBZ']=2
df5['SJLYBZ']=7
df5['CTZBZ']=0
df5['SJLYBZ']=7
df5['CTZBZ']=0
df5.loc[df5['PRVT_PLACE']=='Y','SFSMZ']=1
df5.loc[df5['PRVT_PLACE']=='N','SFSMZ']=0
df5.loc[df5['PRVT_PLACE']=='N','SFSMZ']=0
#df5.loc[df5[df5['FIRST_CPN_PERIOD_TYP'].str.contains('Normal')==False and df5['LAST_CPN_PERIOD_TYP'].str.contains('Normal')==False],'FXGZBZ']=0
#df5.loc[df5[df5['FIRST_CPN_PERIOD_TYP'].str.contains('Normal')==True and df5['LAST_CPN_PERIOD_TYP'].str.contains('Normal')==True],'FXGZBZ']=1
#df5.loc[df5[df5['FIRST_CPN_PERIOD_TYP'].str.contains('Normal')==True and df5['LAST_CPN_PERIOD_TYP'].str.contains('Normal')==True],'FXGZBZ']=1
df5['FQHBBZ']=0
df5['FXGZBZ']=np.where(df5['FIRST_CPN_PERIOD_TYP'].str.contains('Normal') & df5['LAST_CPN_PERIOD_TYP'].str.contains('Normal'),1,0)
#print(df5[['FXGZBZ','FIRST_CPN_PERIOD_TYP']])
#print(df5[['DQR','MATURITY']])
df5['FXGZBZ']=np.where(df5['FIRST_CPN_PERIOD_TYP'].str.contains('Normal') & df5['LAST_CPN_PERIOD_TYP'].str.contains('Normal'),1,0)
#print(df5[['FXGZBZ','FIRST_CPN_PERIOD_TYP']])
#print(df5[['DQR','MATURITY']])
df5['ZQXH']=' '
df5['ZQMC']=' '
df5['ZQLXDM']=' '
df5['ZQPZDM']=' '
df5['ZQLBDM']=' '
df5['FXRQ']=' '
df5['JZGHR']=' '
df5['ZQZWDJR']=' '
df5['ZQDJRJG']=' '
df5['JZGHRJG']=' '
df5['JZLLZLDM']=' '
df5['LXFBFSDM']=' '
df5['FDLLQDFSDM']=' '
df5['BDLLBZ']=' '
df5['BDLL']=' '
df5['FXSGDLL']=' '
df5['FXSTHPZL']=' '
df5['FXFSDM']=' '
df5['FXJG']=' '
df5['DQBJZ']=' '
df5['LJZJFXZE']=' '
df5['FXRTGZH']=' '
df5['JKRQ']=' '
df5['LTBZ']=' '
df5['DBFSDM']=' '
df5['TQSHXZQBZ']=' '
df5['XZQZLDM']=' '
df5['XZQXSJZR']=' '
df5['XZQXSRYHDLL']=' '
df5['TQSHRQ']=' '
df5['ZQHFSDM']=' '
df5['CFSXDM']=' '
df5['FDDQR']=' '
df5['ZCSJ']=' '
df5['WJZSZLDM']=' '
df5['NDJXTS']=' '
df5['RNJXTS']=' '
df5['JXBZ']=' '
df5['YJXTS']=' '
df5['ZQXYZJFSDM']=' '
df5['ZQZTDM']=' '
df5['PJLBDM']=' '
df5['JRZSYBR']=' '
df5['TCZSYBR']=' '
df5['JLZTDM']=' '
df5['ZQXYJB']=' '
df5['ZQPJJGMC']=' '
df5['SJGXSJ']=' '
df5['JHZQBZ']=' '
df5['ADSJZSJ']=' '
df5['GKPMLL']=' '
df5['CZSXDM']=' '
df5['ZQXSDM']=' '
df5['FXLL']=' '
df5['ZJKSR']=' '
df5['ISIN']=' '
df5['SFGKFX']=' '
zqzl_df=df5[['ZQDM','ZQXH','ZQMC','ZQJC','ZQLXDM','ZQPZDM','ZQLBDM','ZQXZDM','BZDM','ZQQX','ZQQXDW','PMLL','FXRQ','QXR','DQR','JZGHR','ZQZWDJR','ZQDJRJG','JZGHRJG','FXSJCLL','FXSLC','JZLLZLDM','FXPL','JXFSDM','LXFBFSDM','FDLLQDFSDM','BDLLBZ','BDLL','FXSGDLL','FXSTHPZL','FXFSDM','FXJG','DQBJZ','JHFXZE','SJFXZE','LJZJFXZE','FXRTGZH','JKRQ','LTBZ','DBFSDM','TQSHXZQBZ','XZQZLDM','XZQXSJZR','XZQXSRYHDLL','TQSHRQ','ZQHFSDM','CFSXDM','FDDQR','ZCSJ','WJZSZLDM','NDJXTS','RNJXTS','JXBZ','YJXTS','ZQXYZJFSDM','ZQZTDM','PJLBDM','JRZSYBR','TCZSYBR','JLZTDM','ZQXYJB','ZQPJJGMC','SJGXSJ','JHZQBZ','ADSJZSJ','GKPMLL','CZSXDM','ZQXSDM','FXLL','ZJKSR','SJQBBZ','SJLYBZ','CTZBZ','SFSMZ','FXGZBZ','FQHBBZ','ISIN','SFGKFX']]
df5['ZQMC']=' '
df5['ZQLXDM']=' '
df5['ZQPZDM']=' '
df5['ZQLBDM']=' '
df5['FXRQ']=' '
df5['JZGHR']=' '
df5['ZQZWDJR']=' '
df5['ZQDJRJG']=' '
df5['JZGHRJG']=' '
df5['JZLLZLDM']=' '
df5['LXFBFSDM']=' '
df5['FDLLQDFSDM']=' '
df5['BDLLBZ']=' '
df5['BDLL']=' '
df5['FXSGDLL']=' '
df5['FXSTHPZL']=' '
df5['FXFSDM']=' '
df5['FXJG']=' '
df5['DQBJZ']=' '
df5['LJZJFXZE']=' '
df5['FXRTGZH']=' '
df5['JKRQ']=' '
df5['LTBZ']=' '
df5['DBFSDM']=' '
df5['TQSHXZQBZ']=' '
df5['XZQZLDM']=' '
df5['XZQXSJZR']=' '
df5['XZQXSRYHDLL']=' '
df5['TQSHRQ']=' '
df5['ZQHFSDM']=' '
df5['CFSXDM']=' '
df5['FDDQR']=' '
df5['ZCSJ']=' '
df5['WJZSZLDM']=' '
df5['NDJXTS']=' '
df5['RNJXTS']=' '
df5['JXBZ']=' '
df5['YJXTS']=' '
df5['ZQXYZJFSDM']=' '
df5['ZQZTDM']=' '
df5['PJLBDM']=' '
df5['JRZSYBR']=' '
df5['TCZSYBR']=' '
df5['JLZTDM']=' '
df5['ZQXYJB']=' '
df5['ZQPJJGMC']=' '
df5['SJGXSJ']=' '
df5['JHZQBZ']=' '
df5['ADSJZSJ']=' '
df5['GKPMLL']=' '
df5['CZSXDM']=' '
df5['ZQXSDM']=' '
df5['FXLL']=' '
df5['ZJKSR']=' '
df5['ISIN']=' '
df5['SFGKFX']=' '
zqzl_df=df5[['ZQDM','ZQXH','ZQMC','ZQJC','ZQLXDM','ZQPZDM','ZQLBDM','ZQXZDM','BZDM','ZQQX','ZQQXDW','PMLL','FXRQ','QXR','DQR','JZGHR','ZQZWDJR','ZQDJRJG','JZGHRJG','FXSJCLL','FXSLC','JZLLZLDM','FXPL','JXFSDM','LXFBFSDM','FDLLQDFSDM','BDLLBZ','BDLL','FXSGDLL','FXSTHPZL','FXFSDM','FXJG','DQBJZ','JHFXZE','SJFXZE','LJZJFXZE','FXRTGZH','JKRQ','LTBZ','DBFSDM','TQSHXZQBZ','XZQZLDM','XZQXSJZR','XZQXSRYHDLL','TQSHRQ','ZQHFSDM','CFSXDM','FDDQR','ZCSJ','WJZSZLDM','NDJXTS','RNJXTS','JXBZ','YJXTS','ZQXYZJFSDM','ZQZTDM','PJLBDM','JRZSYBR','TCZSYBR','JLZTDM','ZQXYJB','ZQPJJGMC','SJGXSJ','JHZQBZ','ADSJZSJ','GKPMLL','CZSXDM','ZQXSDM','FXLL','ZJKSR','SJQBBZ','SJLYBZ','CTZBZ','SFSMZ','FXGZBZ','FQHBBZ','ISIN','SFGKFX']]
zqzl_file=r"美元债/"+r"债券资料表_"+str(data_T)+".csv"
#zqzl_df.to_csv(zqzl_file)
#zqzl_df.to_csv(zqzl_file)
#**********************************************************************债券辅助信息表**************************************************************
df5['ZQDM']=df5['SECURITIES']
df5['ZQFXRDM']='06'
df5['SJQBBZ']=2
df5['SJLYBZ']=7
df5['ISINM']=''
df5['FXRZDDM']=''
df5['ZJZFRJG']=''
df5['PZWH']=''
df5['BJDFBZ']=''
df5['ZQDBJGMC']=''
df5['BJDFQSJXQ']=''
df5['FSSXFL']=''
df5['DFSXFL']=''
df5['ZCXSJC']=''
df5['WTJGTGZH']=''
df5['LXZFSJ']=''
df5['WTJGMC']=''
df5['HKFWJGZH']=''
df5['HKFWJGMC']=''
df5['ZJBGJGZH']=''
df5['ZJBGJGMC']=''
df5['JYGLRMC']=''
df5['ZCCMC']=''
df5['SXLLBZ']=''
df5['SXLL']=''
df5['JZLLSXRQ']=''
df5['JZLLSXFSDM']=''
df5['JZLLSXTJDM']=''
df5['SXSJJG']=''
df5['SJJGZLDM']=''
df5['ZDSXRQ']=''
df5['ZCLBDM']=''
df5['DYZCGLJGMC']=''
df5['DBJGMC']=''
df5['DBJGPJJGMC']=''
df5['DBJGXYJB']=''
df5['JGHXYZJMS']=''
df5['JGDCDM']=''
df5['JQPJQX']=''
df5['PJQXDWDM']=''
df5['YJDQR']=''
df5['CXQLYWFXBZ']=''
df5['ZCZLDM']=''
df5['DYCQXR']=''
df5['DJBZ']=''
df5['DYCFXR']=''
df5['DJRQ']=''
df5['JDRQ']=''
df5['DJYY']=''
df5['JDYY']=''
df5['ZCXSTGZH']=''
df5['BZ']=''
df5['SJGXSJ']=''
df5['ADSJZSJ']=''
df5['ZQDM']=df5['SECURITIES']
df5['ZQFXRDM']='06'
df5['SJQBBZ']=2
df5['SJLYBZ']=7
df5['ISINM']=''
df5['FXRZDDM']=''
df5['ZJZFRJG']=''
df5['PZWH']=''
df5['BJDFBZ']=''
df5['ZQDBJGMC']=''
df5['BJDFQSJXQ']=''
df5['FSSXFL']=''
df5['DFSXFL']=''
df5['ZCXSJC']=''
df5['WTJGTGZH']=''
df5['LXZFSJ']=''
df5['WTJGMC']=''
df5['HKFWJGZH']=''
df5['HKFWJGMC']=''
df5['ZJBGJGZH']=''
df5['ZJBGJGMC']=''
df5['JYGLRMC']=''
df5['ZCCMC']=''
df5['SXLLBZ']=''
df5['SXLL']=''
df5['JZLLSXRQ']=''
df5['JZLLSXFSDM']=''
df5['JZLLSXTJDM']=''
df5['SXSJJG']=''
df5['SJJGZLDM']=''
df5['ZDSXRQ']=''
df5['ZCLBDM']=''
df5['DYZCGLJGMC']=''
df5['DBJGMC']=''
df5['DBJGPJJGMC']=''
df5['DBJGXYJB']=''
df5['JGHXYZJMS']=''
df5['JGDCDM']=''
df5['JQPJQX']=''
df5['PJQXDWDM']=''
df5['YJDQR']=''
df5['CXQLYWFXBZ']=''
df5['ZCZLDM']=''
df5['DYCQXR']=''
df5['DJBZ']=''
df5['DYCFXR']=''
df5['DJRQ']=''
df5['JDRQ']=''
df5['DJYY']=''
df5['JDYY']=''
df5['ZCXSTGZH']=''
df5['BZ']=''
df5['SJGXSJ']=''
df5['ADSJZSJ']=''
#print(df5[['ZQDM','SECURITIES']])
zqfzxx_df=df5[['ZQDM','ISINM','FXRZDDM','ZJZFRJG','ZQFXRDM','PZWH','BJDFBZ','ZQDBJGMC','BJDFQSJXQ','FSSXFL','DFSXFL','ZCXSJC','WTJGTGZH','LXZFSJ','WTJGMC','HKFWJGZH','HKFWJGMC','ZJBGJGZH','ZJBGJGMC','JYGLRMC','ZCCMC','SXLLBZ','SXLL','JZLLSXRQ','JZLLSXFSDM','JZLLSXTJDM','SXSJJG','SJJGZLDM','ZDSXRQ','ZCLBDM','DYZCGLJGMC','DBJGMC','DBJGPJJGMC','DBJGXYJB','JGHXYZJMS','JGDCDM','JQPJQX','PJQXDWDM','YJDQR','CXQLYWFXBZ','ZCZLDM','DYCQXR','DJBZ','DYCFXR','DJRQ','JDRQ','DJYY','JDYY','ZCXSTGZH','BZ','SJGXSJ','ADSJZSJ','SJQBBZ','SJLYBZ']]
zqfzxx_file=r"美元债/"+r"债券辅助信息表_"+str(data_T)+".csv"
#zqfzxx_df.to_csv(zqfzxx_file)
zqfzxx_df=df5[['ZQDM','ISINM','FXRZDDM','ZJZFRJG','ZQFXRDM','PZWH','BJDFBZ','ZQDBJGMC','BJDFQSJXQ','FSSXFL','DFSXFL','ZCXSJC','WTJGTGZH','LXZFSJ','WTJGMC','HKFWJGZH','HKFWJGMC','ZJBGJGZH','ZJBGJGMC','JYGLRMC','ZCCMC','SXLLBZ','SXLL','JZLLSXRQ','JZLLSXFSDM','JZLLSXTJDM','SXSJJG','SJJGZLDM','ZDSXRQ','ZCLBDM','DYZCGLJGMC','DBJGMC','DBJGPJJGMC','DBJGXYJB','JGHXYZJMS','JGDCDM','JQPJQX','PJQXDWDM','YJDQR','CXQLYWFXBZ','ZCZLDM','DYCQXR','DJBZ','DYCFXR','DJRQ','JDRQ','DJYY','JDYY','ZCXSTGZH','BZ','SJGXSJ','ADSJZSJ','SJQBBZ','SJLYBZ']]
zqfzxx_file=r"美元债/"+r"债券辅助信息表_"+str(data_T)+".csv"
#zqfzxx_df.to_csv(zqfzxx_file)
#**********************************************************************追加注册表**************************************************************
from pandas.core.frame import DataFrame
df5['ZQDM']=df5['SECURITIES']
df5['ZJZCCS']=0
df5['ZJZCRQ']=df5['ISSUE_DT']
df5['BCJHFXE']=df5['AMT_OUTSTANDING']
df5['BCSJFXE']=df5['AMT_OUTSTANDING']
#print(df5['ISSUE_PX'].str.isspace())
#print(df5['FIXED_REOFFER_PX'].str.isspace())
list=[]
for i,j in enumerate(df5['ISSUE_PX'].str.isspace()):
#print(i)
if j==False:
#print(i)
list.append(df5['ISSUE_PX'].values[i])
#print(df5['FIXED_REOFFER_PX'].values[i])
from pandas.core.frame import DataFrame
df5['ZQDM']=df5['SECURITIES']
df5['ZJZCCS']=0
df5['ZJZCRQ']=df5['ISSUE_DT']
df5['BCJHFXE']=df5['AMT_OUTSTANDING']
df5['BCSJFXE']=df5['AMT_OUTSTANDING']
#print(df5['ISSUE_PX'].str.isspace())
#print(df5['FIXED_REOFFER_PX'].str.isspace())
list=[]
for i,j in enumerate(df5['ISSUE_PX'].str.isspace()):
#print(i)
if j==False:
#print(i)
list.append(df5['ISSUE_PX'].values[i])
#print(df5['FIXED_REOFFER_PX'].values[i])
elif len(df5['FIXED_REOFFER_PX'].values[i])!=0:
list.append(df5['FIXED_REOFFER_PX'].values[i])
else:
list.append(100)
#print(list)
df5['FXJG']=list
df5['YHJSCLTR']=df5['INT_ACC_DT']
df5['SJQBBZ']=2
df5['SJLYBZ']=7
#print(df5['FXJG'])
list.append(df5['FIXED_REOFFER_PX'].values[i])
else:
list.append(100)
#print(list)
df5['FXJG']=list
df5['YHJSCLTR']=df5['INT_ACC_DT']
df5['SJQBBZ']=2
df5['SJLYBZ']=7
#print(df5['FXJG'])
df5['TGZCFSDM']=''
df5['FXQYDM']=''
df5['FXFSDM']=''
df5['FXCC']=''
df5['ZBFSDM']=''
df5['ZHFSDM']=''
df5['FXFWDM']=''
df5['GGR']=''
df5['FXKSR']=''
df5['FXJSR']=''
df5['FFKSR']=''
df5['FFJSR']=''
df5['YHJSCLTBZ']=''
df5['LTFSDM']=''
df5['FXSXFJSYJDM']=''
df5['DJFWFJFFSDM']=''
df5['CXCYJFXBZ']=''
df5['ZJHFRGKBZ']=''
df5['YFXBZ']=''
df5['ZCGM']=''
df5['JLZTDM']=''
df5['FXSXFL']=''
df5['SJFXBJE']=''
df5['JFRQ']=''
df5['FXCS']=''
df5['LTCSDM']=''
df5['LTCSMC']=''
df5['CKSYL']=''
df5['SJGXSJ']=''
df5['ADSJZSJ']=''
zjzc_df=df5[['ZQDM','ZJZCCS','ZJZCRQ','TGZCFSDM','BCJHFXE','BCSJFXE','FXQYDM','FXFSDM','FXCC','ZBFSDM','ZHFSDM','FXFWDM','FXJG','GGR','FXKSR','FXJSR','FFKSR','FFJSR','YHJSCLTBZ','LTFSDM','YHJSCLTR','FXSXFJSYJDM','DJFWFJFFSDM','CXCYJFXBZ','ZJHFRGKBZ','YFXBZ','ZCGM','JLZTDM','FXSXFL','SJFXBJE','JFRQ','FXCS','LTCSDM','LTCSMC','CKSYL','SJGXSJ','ADSJZSJ','SJQBBZ','SJLYBZ']]
zjzc_file=r"美元债/"+r"追加注册表_"+str(data_T)+".csv"
#zjzc_df.to_csv(zjzc_file)
zjzc_file=r"美元债/"+r"追加注册表_"+str(data_T)+".csv"
#zjzc_df.to_csv(zjzc_file)
#******************************************************债券流通场所表**************************************
df5['ZQDM']=df5['SECURITIES']
df5['LTCSDM']=50
df5['LTXKBZ']=1
df5['LTRQ']=df5['INT_ACC_DT']
df5['M_LTCSZQDM']=df5['SECURITIES']
df5['M_LTCSZQJC']=df5['SECURITY_NAME']
df5['M_LTCSZQJC']=df5['ZQJC']
df5['SJQBBZ']=2
df5['SJLYBZ']=7
df5['SJGXSJ']=''
df5['ADSJZSJ']=''
zqltcsb_df=df5[['ZQDM','LTCSDM','LTXKBZ','LTRQ','M_LTCSZQDM','M_LTCSZQJC','SJGXSJ','ADSJZSJ','SJQBBZ','SJLYBZ']]
zqltcsb_file=r"美元债/"+r"债券流通场所表_"+str(data_T)+".csv"
#zqltcsb_df.to_csv(zqltcsb_file)
zqltcsb_file=r"美元债/"+r"债券流通场所表_"+str(data_T)+".csv"
#zqltcsb_df.to_csv(zqltcsb_file)
#******************************************************计息期次表**************************************
import datetime
import time
def add_month(str1,b):
a=time.strptime(str1,'%Y/%m/%d')
new_day={}
if b<12:
year=a.tm_year
mon=a.tm_mon+int(b)
day=a.tm_mday
new_day={'year':year,'mon':mon,'day':day}
else:
year=a.tm_year+round(b/12)
day=a.tm_mday
mon=a.tm_mon+b%12
new_day={'year':year,'mon':mon,'day':day}
datestr=str(new_day['year'])+'/'+str(new_day['mon'])+'/'+str(new_day['day'])
return datestr
import datetime
import time
def add_month(str1,b):
a=time.strptime(str1,'%Y/%m/%d')
new_day={}
if b<12:
year=a.tm_year
mon=a.tm_mon+int(b)
day=a.tm_mday
new_day={'year':year,'mon':mon,'day':day}
else:
year=a.tm_year+round(b/12)
day=a.tm_mday
mon=a.tm_mon+b%12
new_day={'year':year,'mon':mon,'day':day}
datestr=str(new_day['year'])+'/'+str(new_day['mon'])+'/'+str(new_day['day'])
return datestr
def mon_diff(day2,day1):
year1=time.strptime(day1,'%Y/%m/%d')
year2=time.strptime(day2,'%Y/%m/%d')
mon=(year1.tm_year-year2.tm_year)*12+year1.tm_mon-year2.tm_mon
return mon
year1=time.strptime(day1,'%Y/%m/%d')
year2=time.strptime(day2,'%Y/%m/%d')
mon=(year1.tm_year-year2.tm_year)*12+year1.tm_mon-year2.tm_mon
return mon
df6=df5[['SECURITIES','INT_ACC_DT','FIRST_CPN_DT','MATURITY','YRS_TO_MTY_ISSUE','CPN','CPN_FREQ','PENULTIMATE_CPN_DT']]
list1=[]
for i in df6.values.tolist():
if i[4] is not None and i[1]!="N.A." and i[6] is not None and i[6]!=0:
total=mon_diff(i[2],i[3])
m_interval=12/i[6]
total=(total/m_interval)+1
n=int(total)
for j in range(n):
if j==1:
ZQDM=i[0]
FXQC=j
JXFSDM=31
BQQXR=i[1]
BQJXR=i[2]
BQJCLL=0
BQLC=0
BQZQNLL=float(i[5])/100
BQBJZ=100
BQDFBJZ=0
SJQBBZ=2
SJLYBZ=7
content=[ZQDM,FXQC,JXFSDM,BQQXR,BQJXR,BQJCLL,BQLC,BQZQNLL,BQBJZ,BQDFBJZ,SJQBBZ,SJLYBZ]
list1.append(content)
elif j>1 and j<total:
#print('j',j)
b2=int((j-2)*m_interval)
b1=int((j-1)*m_interval)
ZQDM=i[0]
FXQC=j
JXFSDM=31
BQQXR=add_month(i[2],b2)
#print('BQQXR',BQQXR)
BQJXR=add_month(i[2],b1)
BQJCLL=0
BQLC=0
BQZQNLL=float(i[5])/100
BQBJZ=100
BQDFBJZ=0
SJQBBZ=2
SJLYBZ=7
content=[ZQDM,FXQC,JXFSDM,BQQXR,BQJXR,BQJCLL,BQLC,BQZQNLL,BQBJZ,BQDFBJZ,SJQBBZ,SJLYBZ]
list1.append(content)
#print(list1)
else:
ZQDM=i[0]
FXQC=j
JXFSDM=31
BQQXR=i[7]
BQJXR=i[3]
BQJCLL=0
BQLC=0
BQZQNLL=float(i[5])/100
BQBJZ=100
BQDFBJZ=100
SJQBBZ=2
SJLYBZ=7
content=[ZQDM,FXQC,JXFSDM,BQQXR,BQJXR,BQJCLL,BQLC,BQZQNLL,BQBJZ,BQDFBJZ,SJQBBZ,SJLYBZ]
list1.append(content)
elif i[4] is not None and i[1]!="N.A." and i[6] is not None and i[6]==0:
ZQDM=i[0]
FXQC=j
JXFSDM=31
BQQXR=i[7]
BQJXR=i[3]
BQJCLL=0
BQLC=0
BQZQNLL=float(i[5])/100
BQBJZ=100
BQDFBJZ=100
SJQBBZ=2
SJLYBZ=7
content=[ZQDM,FXQC,JXFSDM,BQQXR,BQJXR,BQJCLL,BQLC,BQZQNLL,BQBJZ,BQDFBJZ,SJQBBZ,SJLYBZ]
list1.append(content)
df7=pd.DataFrame(list1)
# dict1={}
# for i,key in enumerate(['ZQDM','FXQC','JXFSDM','FDLLQDFSDM','ZQDJR','BQQXR','BQJXR','ZTGTBR','BQJCLL','BQLC','LLBGWH','BQTHPZL','WJZSBGWH','BQZQNLL','BQZXLL','BQGDLL','BQBJZ','BQDFBJZ','FSBZ','JLZTDM','SJGXSJ','ADSJZSJ','SJQBBZ','SJLYBZ']):
# dict1[i]=key
#jxqcb_df=df7.rename(columns=dict1)
df7.rename(columns={
0: 'ZQDM',
1: 'FXQC',
2: 'JXFSDM',
3: 'BQQXR',
4: 'BQJXR',
5: 'BQJCLL',
6: 'BQLC',
7: 'BQZQNLL',
8: 'BQBJZ',
9: 'BQDFBJZ',
10: 'SJQBBZ',
11: 'SJLYBZ',
}, inplace=True)
list1=[]
for i in df6.values.tolist():
if i[4] is not None and i[1]!="N.A." and i[6] is not None and i[6]!=0:
total=mon_diff(i[2],i[3])
m_interval=12/i[6]
total=(total/m_interval)+1
n=int(total)
for j in range(n):
if j==1:
ZQDM=i[0]
FXQC=j
JXFSDM=31
BQQXR=i[1]
BQJXR=i[2]
BQJCLL=0
BQLC=0
BQZQNLL=float(i[5])/100
BQBJZ=100
BQDFBJZ=0
SJQBBZ=2
SJLYBZ=7
content=[ZQDM,FXQC,JXFSDM,BQQXR,BQJXR,BQJCLL,BQLC,BQZQNLL,BQBJZ,BQDFBJZ,SJQBBZ,SJLYBZ]
list1.append(content)
elif j>1 and j<total:
#print('j',j)
b2=int((j-2)*m_interval)
b1=int((j-1)*m_interval)
ZQDM=i[0]
FXQC=j
JXFSDM=31
BQQXR=add_month(i[2],b2)
#print('BQQXR',BQQXR)
BQJXR=add_month(i[2],b1)
BQJCLL=0
BQLC=0
BQZQNLL=float(i[5])/100
BQBJZ=100
BQDFBJZ=0
SJQBBZ=2
SJLYBZ=7
content=[ZQDM,FXQC,JXFSDM,BQQXR,BQJXR,BQJCLL,BQLC,BQZQNLL,BQBJZ,BQDFBJZ,SJQBBZ,SJLYBZ]
list1.append(content)
#print(list1)
else:
ZQDM=i[0]
FXQC=j
JXFSDM=31
BQQXR=i[7]
BQJXR=i[3]
BQJCLL=0
BQLC=0
BQZQNLL=float(i[5])/100
BQBJZ=100
BQDFBJZ=100
SJQBBZ=2
SJLYBZ=7
content=[ZQDM,FXQC,JXFSDM,BQQXR,BQJXR,BQJCLL,BQLC,BQZQNLL,BQBJZ,BQDFBJZ,SJQBBZ,SJLYBZ]
list1.append(content)
elif i[4] is not None and i[1]!="N.A." and i[6] is not None and i[6]==0:
ZQDM=i[0]
FXQC=j
JXFSDM=31
BQQXR=i[7]
BQJXR=i[3]
BQJCLL=0
BQLC=0
BQZQNLL=float(i[5])/100
BQBJZ=100
BQDFBJZ=100
SJQBBZ=2
SJLYBZ=7
content=[ZQDM,FXQC,JXFSDM,BQQXR,BQJXR,BQJCLL,BQLC,BQZQNLL,BQBJZ,BQDFBJZ,SJQBBZ,SJLYBZ]
list1.append(content)
df7=pd.DataFrame(list1)
# dict1={}
# for i,key in enumerate(['ZQDM','FXQC','JXFSDM','FDLLQDFSDM','ZQDJR','BQQXR','BQJXR','ZTGTBR','BQJCLL','BQLC','LLBGWH','BQTHPZL','WJZSBGWH','BQZQNLL','BQZXLL','BQGDLL','BQBJZ','BQDFBJZ','FSBZ','JLZTDM','SJGXSJ','ADSJZSJ','SJQBBZ','SJLYBZ']):
# dict1[i]=key
#jxqcb_df=df7.rename(columns=dict1)
df7.rename(columns={
0: 'ZQDM',
1: 'FXQC',
2: 'JXFSDM',
3: 'BQQXR',
4: 'BQJXR',
5: 'BQJCLL',
6: 'BQLC',
7: 'BQZQNLL',
8: 'BQBJZ',
9: 'BQDFBJZ',
10: 'SJQBBZ',
11: 'SJLYBZ',
}, inplace=True)
df7['FDLLQDFSDM']=''
df7['ZQDJR']=''
df7['LLBGWH']=''
df7['ZTGTBR']=''
df7['LLBGWH']=''
df7['BQTHPZL']=''
df7['WJZSBGWH']=''
df7['BQZXLL']=''
df7['BQGDLL']=''
df7['FSBZ']=''
df7['JLZTDM']=''
df7['SJGXSJ']=''
df7['ADSJZSJ']=''
df7['ZQDJR']=''
df7['LLBGWH']=''
df7['ZTGTBR']=''
df7['LLBGWH']=''
df7['BQTHPZL']=''
df7['WJZSBGWH']=''
df7['BQZXLL']=''
df7['BQGDLL']=''
df7['FSBZ']=''
df7['JLZTDM']=''
df7['SJGXSJ']=''
df7['ADSJZSJ']=''
jxqcb_df=df7
#print(jxqcb_df['BQJXR']) #[ZQDM,FXQC,JXFSDM,BQQXR,BQJXR,BQJCLL,BQLC,BQZQNLL,BQBJZ,SJQBBZ,SJLYBZ,SJLYBZ]
#print(jxqcb_df['BQQXR'])
jxqcb_file=r"美元债/"+r"计息期次表_"+str(data_T)+".csv"
#jxqcb_df.to_csv(jxqcb_file)
#print(jxqcb_df['BQQXR'])
jxqcb_file=r"美元债/"+r"计息期次表_"+str(data_T)+".csv"
#jxqcb_df.to_csv(jxqcb_file)
#print(pd7)
#******************************************************债券托管量表**************************************
df8=df5[['SECURITIES','AMOUNT_OUTSTANDING_HISTORY']]
list2=[]
for i in df8.values.tolist():
if i[0] is None:
print('True')
else:
attr=i[1].split(';')
#print('attr',attr)
for j in range(len(attr)):
if attr[j]=='13':
#n = n + 1
a=attr[j-1]
#a=datetime.datetime.strptime(str_p,'%Y/%m/%d')
b=attr[j+1]
tm_struct = time.strptime(a, "%m/%d/%y")
strftime=time.strftime("%Y/%m/%d", tm_struct)
# dict2['ZQDM']=i[0]
# dict2['RQ']=a
# dict2['TGL']=b
# dict2['LTCSDM']=50
ZQDM=i[0]
RQ=strftime
TGL=b
LTCSDM=50
content=[ZQDM,RQ,TGL,LTCSDM]
list2.append(content)
df9=pd.DataFrame(list2)
dict2={}
for i,key in enumerate(['ZQDM','RQ','TGL','LTCSDM']):
dict2[i]=key
zqtglb_df=df9.rename(columns=dict2)
#print(zqtglb_df)
zqtglb_file=r"美元债/"+r"债券托管量表_"+str(data_T)+".csv"
zqtglb_df.to_csv(zqtglb_file)
print('True')
#print(zqtglb_df)
#print(zqtglb_df)
zqtglb_file=r"美元债/"+r"债券托管量表_"+str(data_T)+".csv"
zqtglb_df.to_csv(zqtglb_file)
print('True')
#print(zqtglb_df)
