Create firm level market beta for crsp month stock file

crsp_mkt_beta.py

#!/usr/bin/python
 
 
 
# /* %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% */
# /*  mkt_beta.py
 
   # Compute market beta from monthly stock file
 
   # File created  15 October 2013
   # last modified 15 October 2013
 
# (c) Erik Loualiche and Valentin Haddad
    
 # %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%  */
 
 
import urllib, zipfile, os
import fred as fred
import pandas as pd

import numpy as np
from scipy.stats import linregress

import matplotlib
import pylab
import matplotlib.pyplot as plt

import datetime as datetime


#====================================================
# Useful paths and file names
datapath = '/path/to/dir/'

os.system('scp erikl@equity.mit.edu:/path/to/remotedir/crsp_reg.csv ' + datapath)

df = pd.read_csv(datapath + 'crsp_reg.csv')
# df = df.drop(['RET','vwretd','vwretx','ewretd','ewretx','sprtrn','spindx','totval','totcnt','usdval','usdcnt'], axis=1)
df = df.dropna(axis=0)

df_g = df.groupby('PERMNO')

beta = df_g.apply(lambda x: linregress( x['MKT_RF'], x['exret'])[0])

ret  = df_g.apply(lambda x: np.mean(x.exret))

scatter = pd.concat( [beta[1:], ret[1:]], axis=1)

scatter = scatter.rename(columns={0: 'beta', 1: 'exret'})
scatter.to_csv(datapath + 'scatter.csv')