I have done the following tasks manually and I am sure there is a way to write a loop, but I am not sure how to do so in Python.
Data looks like this:
df
a b c market ret
date id
2015-01-01 1 10 4 2 10 0.02
2015-01-01 2 20 3 5 15 0.03
2015-01-01 3 30 2 3 20 0.05
2015-01-01 4 40 1 10 25 0.01
2015-01-02 1 15 8 4 15 -0.03
2015-01-02 2 10 6 1 10 0.02
2015-01-02 3 25 10 2 22 0.06
2015-01-02 4 30 3 7 26 0.06
2015-01-03 1 25 2 2 16 -0.07
2015-01-03 2 10 6 1 18 0.01
2015-01-03 3 5 8 5 26 0.04
2015-01-03 4 30 1 6 21 -0.05
I do the following:
dfa = df
dfa['market'] = dfa.groupby(level = ['id']).market.shift()
dfa['port'] = dfa.groupby(['date'])['a'].transform(lambda x: pd.qcut(x, 4, labels = False))
# value-weighted portoflio returns
dfa = dfa.set_index(['port'], append = True)
dfa['tmktcap'] = dfa.groupby(['date','port'])['mktcap'].transform(sum)
dfa['w_ret'] = (dfa.mktcap / dfa.tmktcap) * dfa.ret
#reshape long to wide
dfa = dfa.groupby(['date', 'port'])['w_ret'].sum().shift(-4)
dfa = dfa['2006-01-01':].rename('a')
dfa = dfa.unstack()
dfa[4.0] = dfa[3.0] - dfroe[0.0]
dfa = dfa.stack().reset_index().set_index(['date'])
dfa['port'] = dfa['port'].map({0.0:'a0',1.0:'a1',2.0:'a2',3.0:'a3',4.0:'aL-S'})
dfa = dfa.reset_index().set_index(['date', 'port']).unstack()
But then I repeat this task for b and c.
So I start of by setting dfb = df and just change the a to b and follow this process when doing this for c.
I have had to do this for variables going from a to h in total (just some example data used here), so any help with writing a loop would be amazing!!!!!
Loop over a selection of columns. Then save your results in an array, list or dictionary. Here is an example whit a list.
results = [] # this list will store your results
columns_to_process = ['a', 'b','c','d','f']
for col in columns_to_process:
data = df.copy()
data['market'] = data.groupby(level = ['id']).market.shift()
data['port'] = data.groupby(['date'])[col].transform(lambda x: pd.qcut(x, 4, labels = False))
# do whatever you want with data
results.append(data) # this store the result in position 0 then 1 then 2 etc
#then use your result:
result[0] # for the dfa
result[1] # for dfb etc
Or, you may want to store all results in one DataFrame. To do that you just select the columns you want and save it in a DataFrame.
df['result_a'] = data.columns_i_want_to_save
You asked:
#Do I just change a to col where I change name of the column?
dfa['port'].map({0.0:'a0',1.0:'a1',2.0:'a2',3.0:'a3',4.0:'aL-S'})
You can do some 'string addition'. Somethings like:
dfa['port'].map({0.0:col+'0',
1.0:col+'1',
2.0:col+'2',
3.0:col+'3',
4.0:col+'L-S'})