How to write from loop to dataframe

Question

I'am trying to calculate 33 stock betas and write them to dataframe.

Unfortunately, I have an error in my code: cannot concatenate object of type ""; only pd.Series, pd.DataFrame, and pd.Panel (deprecated) objs are vali

import pandas as pd
import numpy as np
stock1=pd.read_excel(r"C:\Users\Кир\Desktop\Uni\Master\Nasdaq\Financials 11.05\Nasdaq last\clean data\01.xlsx", '1') #read second sheet of excel file
stock2=pd.read_excel(r"C:\Users\Кир\Desktop\Uni\Master\Nasdaq\Financials 11.05\Nasdaq last\clean data\01.xlsx", '2') #read second sheet of excel file
stock2['stockreturn']=np.log(stock2.AdjCloseStock / stock2.AdjCloseStock.shift(1)) #stock ln return
stock2['SP500return']=np.log(stock2.AdjCloseSP500 / stock2.AdjCloseSP500.shift(1)) #SP500 ln return
stock2 = stock2.iloc[1:] #delete first row in dataframe
betas = pd.DataFrame()
for i in range(0,(len(stock2.AdjCloseStock)//52)-1):
    betas = betas.append(stock2.stockreturn.iloc[i*52:(i+1)*52].cov(stock2.SP500return.iloc[i*52:(i+1)*52])/stock2.SP500return.iloc[i*52:(i+1)*52].cov(stock2.SP500return.iloc[i*52:(i+1)*52]))

My data looks like weekly stock and S&P index return for 33 years. So the output should have 33 betas.

Answer 1

I tried simplifying your code and creating an example. I think the problem is that your calculation returns a float. You want to make it a pd.Series. DataFrame.append takes:

DataFrame or Series/dict-like object, or list of these

np.random.seed(20)
df = pd.DataFrame(np.random.randn(33*53, 2),
                  columns=['a', 'b'])
betas = pd.DataFrame()
for year in range(len(df['a'])//52 -1):
    # Take some data
    in_slice = pd.IndexSlice[year*52:(year+1)*52]
    numerator = df['a'].iloc[in_slice].cov(df['b'].iloc[in_slice])
    denominator = df['b'].iloc[in_slice].cov(df['b'].iloc[in_slice])
    # Do some calculations and create a pd.Series from the result
    data = pd.Series(numerator / denominator, name = year)
    # Append to the DataFrame
    betas = betas.append(data)

betas.index.name = 'years'
betas.columns = ['beta']

betas.head():

           beta
years          
0      0.107669
1     -0.009302
2     -0.063200
3      0.025681
4     -0.000813