<jupyter_start><jupyter_text>Uploading datafirst using quandl API<jupyter_code>with open("API.json", "r") as file:
    API = json.load(file)
quandl.ApiConfig.api_key=API['Quandl']
start_m = '1959-01-01'
end_m = '2019-12-31'

#Quaterly dataframe
df_q = quandl.get('FRED/GDPC1',start_date = start_m, end_date = end_m, collapse = 'quaterly')
df_q['GDPC1'] = quandl.get('FRED/GDPC1',start_date = start_m, end_date = end_m, collapse = 'quaterly')
df_q = df_q.drop(columns = 'Value')


#Monthly dataframe
df_m = quandl.get('FRED/'+'GS10',start_date = start_m, end_date = end_m, collapse = 'monthly')

fred_series_m = ['USREC','GS10', 'TB3MS', 'GS1', 'AAA10YM', 'DTB6', 'BAA10YM', 'FEDFUNDS','WPU102301', 'WPU101', 'WTISPLC','BOGMBASE', 'M1NS', 'M2NS','UNRATE', 'PERMIT', 'LNS12032195', 'PAYEMS','INDPRO', 'W875RX1']
for series in fred_series_m:
        df_m[series] = quandl.get('FRED/'+series, start_date = start_m, end_date = end_m, collapse = 'monthly')

#Non fred data series
df_m['MAN_PMI'] = quandl.get('ISM/MAN_PMI', start_date = start_m, end_date = end_m, collapse = 'monthly')
df_m['MAN_DELIV'] = quandl.get('ISM/MAN_DELIV.5', start_date = start_m, end_date = end_m, collapse = 'monthly')
df_m['MAN_NEWORDERS'] = quandl.get('ISM/MAN_NEWORDERS.5', start_date = start_m, end_date = end_m, collapse = 'monthly')

df_m = df_m.drop(columns = 'Value')
        
#Daily dataframe
df_d = quandl.get('FRED/'+'DGS10',start_date = start_m, end_date = end_m, collapse = 'daily')
fred_series_d = ['DGS10', 'DGS1', 'DTB3']
for series in fred_series_d:
        df_d[series] = quandl.get('FRED/'+series, start_date = start_m, end_date = end_m, collapse = 'daily')
df_d = df_d.drop(columns = 'Value')     
df_q
df_m
df_d<jupyter_output><empty_output><jupyter_text>Data from csvSome data was not available through the API, hence I downloaded the csv and will concatenate the data from this csv to the previously constructed dataframe<jupyter_code>#extradata csv upload and concatenation
extra_df = pd.read_csv('extradata.csv', index_col = 'Date')
df_m = pd.concat([df_m, extra_df], axis = 1)
df_m<jupyter_output><empty_output><jupyter_text>Consumer sentiment uploadThis series was done separatedly becasue the data up to 1978 is only available quarter by quarter. Hence to use it in the monthly analsysis the quaterly measurements had to be used 3 times (one per month).<jupyter_code>#Consumer sentiment
# CS = pd.read_csv('consumer_sentiment_q.csv', index_col = 'QUARTER')
CS = pd.read_csv('consumer_sentiment.csv', index_col = 'Month')
CS = list(CS.ICS_ALL.values)
CS_m = []

for i in range(18,20):
    CS_m.append(CS[i])
    CS_m.append(CS[i])
    CS_m.append(CS[i])
    CS_m.append(CS[i])
    CS_m.append(CS[i])
    CS_m.append(CS[i])    

for i in range(20, 92):
    CS_m.append(CS[i])
    CS_m.append(CS[i])
    CS_m.append(CS[i])


for i in range(92,len(CS)-6):
    CS_m.append(CS[i])


CS_m

# CS_m = CS_m[:len(CS_m)-6]
# len(CS_m)
df_m['CS_m'] = CS_m
df_m
#Additional data series combinations and other adjustments

#Yield curves
df_m['10Y-3M'] = df_m['GS10'] - df_m['TB3MS']
df_m['10Y-1Y'] = df_m['GS10'] - df_m['GS1']

df_d['mad_10Y-3M'] = df_d['DGS10'] - df_d['DTB3']
df_d['mad_10Y-1Y'] = df_d['DGS10'] - df_d['DGS1']

#Getting AAA and BAA returns
df_m['AAA'] = df_m['AAA10YM'] + df_m['GS10']
df_m['BAA'] = df_m['BAA10YM'] + df_m['GS10']

#CR sum and DR dummy for simultaneous recessions
df_m['USREC'] = pd.to_numeric(df_m['USREC'], downcast = 'integer')
df_m['CR'] = df_m['USREC'] + df_m['Australia'] + df_m['Canada'] + df_m['Germany'] + df_m['Japan'] + df_m['UK']   
CR_list = list(df_m['CR'].values)

DR_list = []
for i in range(len(CR_list)):
    if CR_list[i] >= 3:
        DR_list.append(1)
    else:
        DR_list.append(0)

df_m['DR'] = DR_list

#All series that need unit changed to billions as units
df_m['BOGMBASE'] = df_m['BOGMBASE']/1000
df_m['Unfilled Orders'] = df_m['Unfilled Orders']/1000
df_m['MABMM301USM189S'] = df_m['MABMM301USM189S']/1000000000

#Inflation Correction
nominal_series = ['WTISPLC', 'BOGMBASE','M1NS','M2NS','Unfilled Orders','W875RX1','MABMM301USM189S','S&P500']

df_m['deflator'] = df_m['CPIAUCNS']/100

for series in nominal_series:
    df_m['R_'+series] = df_m[series]/df_m['deflator']
    
#Dropping series that we don't need when nominal
df_m = df_m.drop(columns = ['WTISPLC','Unfilled Orders','W875RX1','S&P500'])

#All series that need to be turned into differences
df_m['R_Unfilled Orders'] = df_m['R_Unfilled Orders'].diff()
df_q['GDPC1'] = df_q['GDPC1'].diff()*100/df_q['GDPC1']
df_m['R_S&P500_change'] = df_m['R_S&P500'].diff()*100/df_m['R_S&P500']

#Create the lags for monthly
lag1_series = ['WPU102301','WPU101', 'BOGMBASE','M1NS', 'M2NS', 'R_BOGMBASE','R_M1NS', 'R_M2NS','CPIAUCNS', 'UNRATE', 'MAN_PMI', 'MAN_DELIV', 'MAN_NEWORDERS', 'PERMIT', 'CS_m', 'LNS12032195', 'PAYEMS','INDPRO']
lag2_series = ['R_Unfilled Orders', 'R_W875RX1', 'MABMM301USM189S','R_MABMM301USM189S']
lag9_series = ['Australia', 'Canada', 'Germany', 'Japan', 'UK', 'CR', 'DR']


for series in lag1_series:
    df_m[series] = df_m[series].shift(1)

for series in lag2_series:
    df_m[series] = df_m[series].shift(2)
    
for series in lag9_series:
    df_m[series] = df_m[series].shift(9)

#Lagged dependent variables (1 quarter and 2 quarter)

df_m['USREC_lag3'] = df_m['USREC'].shift(3)
df_m['USREC_lag6'] = df_m['USREC'].shift(6)

df_q['GDPC1_lag1'] = df_q['GDPC1'].shift(1)
df_q['GDPC1_lag2'] = df_q['GDPC1'].shift(2)

#Mad calculations
#need daily S&P500
SP500_d = pd.read_csv('^GSPC_daily.csv', index_col = 'Date')

#Calculating mean absolute deviation
mad_SP500_d = SP500_d.groupby(pd.PeriodIndex(SP500_d.index, freq = 'M'), axis = 0).mad()
mad_df_d = df_d.groupby(pd.PeriodIndex(df_d.index, freq = 'M'), axis = 0).mad()

#fixing the dataframe and getting rid of extra data
mad_df_d = pd.concat([mad_df_d, mad_SP500_d], axis = 1)
mad_df_d = mad_df_d.drop(columns = ['DGS10', 'DGS1', 'DTB3']) 

df_q<jupyter_output><empty_output><jupyter_text>Calculating Mean Absolute Deviation for 10Y-3M and 10Y-1Y Spread as well as for S&P500<jupyter_code>#Mad calculations
#need daily S&P500
SP500_d = pd.read_csv('^GSPC_daily.csv', index_col = 'Date')

#Calculating mad
mad_SP500_d = SP500_d.groupby(pd.PeriodIndex(SP500_d.index, freq = 'M'), axis = 0).mad()
mad_df_d = df_d.groupby(pd.PeriodIndex(df_d.index, freq = 'M'), axis = 0).mad()

#fixing the dataframe and getting rid of extra data
mad_df_d = pd.concat([mad_df_d, mad_SP500_d], axis = 1)
mad_df_d = mad_df_d.drop(columns = ['DGS10', 'DGS1', 'DTB3']) 

mad_df_d<jupyter_output><empty_output><jupyter_text>Cutting dataframe to 1962This is necessary becasue the daily measures used to calculate the mad only go back to January 1962. Unfortunately this means that one of the recessionary periods is missed.<jupyter_code>#Slice up to January 1962
df_m = df_m[36:]
#The following is needed to have index in the same format as mad_df_d
df_m = df_m.groupby(pd.PeriodIndex(df_m.index, freq = 'M'), axis = 0).mean()
#since this it's a mean of a each month, df_m values won't change

#Concatenating df_m and mad_df_d
df_m = pd.concat([df_m, mad_df_d], axis = 1)
df_m<jupyter_output><empty_output><jupyter_text>Average monthly series to quarterly<jupyter_code>df_q2 = df_m.groupby(pd.PeriodIndex(df_m.index, freq='Q'), axis=0).mean()

#The following is needed to have index in the same format as df_q2
df_q = df_q.groupby(pd.PeriodIndex(df_q.index, freq='Q'), axis=0).mean()
#since this it's a mean of a each quarter, df_m values won't change

#Concatenating the strictly quaterly series with the monthly quarter average series
df_q = pd.concat([df_q, df_q2], axis = 1)
df_q = df_q.drop(columns = ['USREC', 'Australia', 'Canada','Germany','Japan','UK', 'USREC_lag3'])
df_q = df_q[12:]
df_q<jupyter_output><empty_output><jupyter_text>Download to csv<jupyter_code>df_m.to_csv('df_m.csv')
df_q.to_csv('df_q.csv')<jupyter_output><empty_output><jupyter_text>Testing for null values<jupyter_code>df_m.isnull().values.any()
df_q.isnull().values.any()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1618360492598582, 'fraction_numerical': 0.04117427540738898, 'mean_word_length': 5.973113616652212, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '160228', 'license': 'MIT', 'n_tokens_mistral': 3579, 'n_tokens_neox': 3322, 'n_words': 696, 'path': 'Capstone data gathering.ipynb', 'repo_name': 'Davide-Bestagno/Master_Dissertation_Public', 'size': '8039'}