Credit Spread Prediction : Data Preparing

北辰Rick

于 2019-02-25 21:35:30 发布

阅读量186

点赞数 1

分类专栏： Python 数据处理文章标签： Python 数据处理

本文链接：https://blog.csdn.net/weixin_42339449/article/details/87925378

版权

Python 同时被 2 个专栏收录

15 篇文章 3 订阅

订阅专栏

数据处理

2 篇文章 0 订阅

订阅专栏

读取数据+数据预处理

import numpy as np
import pandas as pd
import os

Part One : 处理宏观指标表格

Step 1 : 读入Excel 并阅读前五行

data_Macro = pd.read_excel('data_Macro.xlsx')
data_Macro.head()

	Date	美元指数	M2增速	CPI	PPI	repo rate（回购利率）	S&P 500	TED	GDP(对数）	Bond Volatility(%)	...	360天波动率	20天波动率	120天波动率	150天波动率	10年期国债指数	5年期国债指数	3年期国债指数	2年期国债指数	1年期国债指数	工业增加值(Monthly%)
0	2004-12-17	82.16	5.4	3.5	5.0	2.215	1194.22	32.88	9.417626	0.397873	...	11.72	9.45	11.06	10.80	4.21	3.59	3.22	3.03	2.67	0.22
1	2004-12-20	81.67	5.4	3.5	5.0	2.105	1194.65	33.02	9.417626	1.674148	...	11.68	9.26	10.94	10.77	4.21	3.59	3.23	3.06	2.72	0.22
2	2004-12-21	81.94	5.4	3.5	5.0	2.105	1205.45	31.76	9.417626	0.863063	...	11.63	9.74	11.00	10.82	4.18	3.57	3.22	3.05	2.72	0.22
3	2004-12-22	81.93	5.4	3.5	5.0	2.105	1209.57	34.85	9.417626	-0.410095	...	11.61	9.71	10.93	10.82	4.21	3.57	3.21	3.04	2.71	0.22
4	2004-12-23	81.93	5.4	3.5	5.0	2.105	1210.13	36.78	9.417626	-0.027716	...	11.61	9.71	10.93	10.81	4.23	3.58	3.21	3.02	2.70	0.22

5 rows × 28 columns

Step 2 : 获取Excel的列名，存为List，并删除Date（Date不需要滞后）

column_list = list(data_Macro)
del column_list[0]

Step 3 ：设置Lag的List，这里做1，2，5，10，20项滞后

lag_list = [1, 2, 5, 10, 20]

Step 4 ：用字符串拼接获取新的列名，并用shift函数添加滞后列

for i in range(len(column_list)) :
    for j in range(len(lag_list)) :
        column_name = column_list[i] + '-lag' + str(lag_list[j])
        data_Macro[column_name] = data_Macro[column_list[i]].shift(lag_list[j])

Part Two ：处理ICE表格，批量读取并整合

Step 1 : 获取、设置并更改工作目录

path = os.getcwd()
path_ICE = path + '/Data_ICE'
os.chdir(path_ICE)

Step 2 : 获取文件夹内所有Excel文件名，存为List

filelist = []
for root,dirs,files in os.walk(path_ICE):
    for file in files:
        if os.path.splitext(file)[1] == '.xlsx':
            filelist.append(file)

Step 3 : 读取Excel表格，并把每个表格都存成DataFrame格式

注意：这里在读取Excel文件时又增加了Year和Month两列。因为这个表格内并没有Date这一列，而在与其他表格合并时，Date又是非常重要的一部分，所以我将文件名提取出来，化为两列“Year”和"Month", 其他表格中将Date拆分成三列“Year”“Month”“Day”，这样就可以将“Year”和“Month”作为“Key”来进行合并。

dflist = [0]*len(filelist)
for i in range(len(filelist)) :
    dflist[i] = pd.read_excel(filelist[i], skiprows =1 , skipfooter = 3)
    dflist[i]['Year'] = pd.Series(np.array([filelist[i][0:4]]*len(dflist[i].index)), index=dflist[i].index)
    dflist[i]['Month'] = pd.Series(np.array([filelist[i][4:6]]*len(dflist[i].index)), index=dflist[i].index)

Step 4 ：将所读取的表格纵向拼接（Concat函数）

data_ICE = pd.concat(dflist)

Part Three ：整合微观数据（Data_Finance)

Step 1 ：调整工作路径

path_Finance = path + '/Data_finance'
os.chdir(path_Finance)

Step 2 : 获取文件名，存为List

filelist2 = []
for root,dirs,files in os.walk(path_Finance):
    for file in files:
        if os.path.splitext(file)[1] == '.xlsx':
            filelist2.append(file)

Step 3 : 批量读取文件并增加Ticker列

注意：微观数据中仅有几个指标，并没有Ticker列，在与其他表格合并时，需要用Ticker和Date作为两重键才可以保证匹配无误。这里也是使用文件名中的字符串来作为Ticker。

dflist2 = [0]*len(filelist2)
for i in range(len(filelist2)) :
    dflist2[i] = pd.read_excel(filelist2[i])
    dflist2[i]['Ticker'] = pd.Series(np.array([filelist2[i][0:-5]]*len(dflist2[i])), index=dflist2[i].index)

Step 4 : 将每一个表格均增加滞后项

注意: 这里不先合并后增加滞后项是因为纵向拼接后，两个表的连接处会造成错误。所以先滞后，后拼接

column_list2 = list(dflist2[0])
column_list2.remove('Dates')
column_list2.remove('Ticker')

for i in range(len(dflist2)) :
    for k in range(len(column_list2)) :
        for j in range(len(lag_list)) :
            column_name = column_list2[k] + '-lag' + str(lag_list[j])
            dflist2[i][column_name] = dflist2[i][column_list2[k]].shift(lag_list[j])

Step 5 : 合并表格

Data_finance = pd.concat(dflist2)

Part Four : 处理BofMal&10Y-T Constant Maturity Rate

Step 1 ：调整工作目录

os.chdir(path)

Step 2 : 读取data_spread的Excel

data_spread = pd.read_excel('data_spread.xlsx')

list(data_spread)

['Date', 'BAMLC0A1CAAAEY', 'DGS10', 'Spread']

Step 3 ：增加滞后项

for i in range(len(lag_list)) :
    BAML_name = 'BAMLC0A1CAAAEY-lag' + str(lag_list[i])
    DG_name = 'DGS10-lag' + str(lag_list[i])
    data_spread[BAML_name] = data_spread['BAMLC0A1CAAAEY'].shift(lag_list[i])
    data_spread[DG_name] = data_spread['DGS10'].shift(lag_list[i])

Part Five : 处理Yield Data

Step 1 ：读取Excel文件

yield_data = pd.read_excel('yield_data.xlsx', index_col = 0)

Step 2 : 将表格拆分为每一列一个DataFrame

split_list = range(1, yield_data.shape[1])
dfs = np.split(yield_data, split_list, axis=1)

dfs[0].head()

	FRDFND 3.859 06/01/47 Corp
Date
2005-01-03	NaN
2005-01-04	NaN
2005-01-05	NaN
2005-01-06	NaN
2005-01-07	NaN

Step 3 : 读入INDEXER，为增加ISIN number列做准备

INDEX = pd.read_excel('INDEXER.xlsx', index_col = 1)

INDEX.head()

	ISIN number
INDEXER
FRDFND 3.859 06/01/47 Corp	US34531XAA28
HARVRD 3.15 07/15/46 Corp	US740816AM58
HARVRD 3.3 07/15/56 Corp	US740816AN32
HARVRD 3.619 10/01/37 Corp	US740816AL75
HARVRD 4.875 10/15/40 Corp	US740816AH63

Step 4 : 对每一个DataFrame增加滞后项，匹配ISIN，删除超出到期日的数据

###创建lag_list
lag_list = [1, 2, 5, 10, 20]
for i in range(len(dfs)) :
    ###匹配ISIN
    ISIN = INDEX.at[list(dfs[i])[0], 'ISIN number']
    ###增加ISIN列
    dfs[i]['ISIN number'] = ISIN
    ### 提取列名，并按空格分割字符串
    splitstr = list(dfs[i])[0].split()
    ### 提取字符串中的到期日并转化成Numpydatetime64格式
    splitstr_date = np.datetime64(pd.to_datetime(splitstr[2]))
    ###删除超过到期日的数据
    a = dfs[i].index.values
    for j in range(len(a)) :
        index_list = []
        if a[j] > splitstr_date :
            index_list.append(a[j])
    dfs[i] = dfs[i].drop(index_list)
    ###更改列名为Yield_real
    dfs[i].columns = ['Yield_real', 'ISIN number']
    ###增添滞后项
    for j in range(len(lag_list)) :
        column_name = "Yield-lag" + str(lag_list[j])
        dfs[i][column_name] = dfs[i]['Yield_real'].shift(lag_list[j])
    
    ###去除Index，将其转化为column
    dfs[i] = dfs[i].reset_index()

Step 5 : 合并Dataframe

data_yield = pd.concat(dfs)

合并表格

Step One ：合并宏观指标表格和ICE表格 Key：Year， Month

拆分宏观指标表格中的Date，得到Year， Month，Day

data_Macro['Year'],data_Macro['Month'],data_Macro['Day'] = data_Macro.Date.dt.year, data_Macro.Date.dt.month, data_Macro.Date.dt.day

data_Macro.Year.dtypes

dtype('int64')

data_ICE['Year'] = data_ICE['Year'].astype(int)
data_ICE['Month'] = data_ICE['Month'].astype(int)

dataset_merge = pd.merge(data_ICE, data_Macro, on = ['Year', 'Month'])

删除没用的列

dataset_merge = dataset_merge.drop(columns = ['Day', 'Month', 'Year'])

Step Two : 合并Spread Key : Date

dataset_merge = pd.merge(dataset_merge, data_spread)

Step Three : 合并Data_finance Key : Ticker, Date

注意：这里应使用left，而不是默认的Inner，右表中很多公司数据没有，所以重合的键少

dataset_merge = pd.merge(dataset_merge, Data_finance, how = 'left')

Step Four : 合并Yield 表格 Key ： ISIN ， Date

dataset_merge = pd.merge(dataset_merge, data_yield, on = ['ISIN number, Date'])

Step Five : 输出Excel

dataset_merge.to_excel('dataset_final.xlsx')

北辰Rick

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Credit Spread Prediction : Data Preparing

读取数据+数据预处理import numpy as npimport pandas as pdimport osPart One : 处理宏观指标表格Step 1 : 读入Excel 并阅读前五行data_Macro = pd.read_excel('data_Macro.xlsx')data_Macro.head() Date ...
复制链接

扫一扫