Patusz - 7 months ago 48

R Question

I have a matrix containing monthly stock returns over 16 years for approx 1000 companies, another matrix containing annual size measurements for the companies and a third matrix containing annual book equity to market equity for the same companies. so for every stock I have 192 returns observations, 12 size observations and 12 book to market observations. The matrices are ordered as such:

`U:APC(P#T) U:AR(P#T) U:APA(P#T) 89589Q(P#T)`

0.05 0.03 -0.2 0.11

0.02 0.0l2 -0.1 0.12

0.01 0.013 0 0.02

... ... ... ....

I would like to sort the data into Fama French 2015 portfolios.

First I would like to sort the stocks on the size variable. I would like to use size at time t to sort all stocks in year t into quintiles. once this is done i would like the take every individual group of stocks and sort by book to market (time t-1) also into quintiles. Once sorted there should be 25 groups of stocks (5 b/m groups for every size group), now I would like to take the average of these groups (once average for every month). I would like to re-sort once every year so that in the end i get a list of 25 portfolios over 192 months that are composed of different stocks every year. How can I do this in R? I am quite new to R.

There is a similar question here:

Nested double sort in Matlab

however it relates to Matlab.

Answer

One of the several possible generic approaches to your question based on data.table (library from Matt Dowle and Co) could be as follows:

- Convert your data.frame(s) say DF1, DF2, DF3 to DT1, DT2, DT3 using as.data.table
- Define column types for DTs (you have dates and numeric types)
- Merge the DTs call the new table bigDT
Define sort order

`sortBy <- c('companySize','otherVariable1','otherVariable2') sortType <- c(1,1,-1) # 1 for ascending, -1 for descending setorderv(bigDT,sortBy,sortType)`

Source (Stackoverflow)