monthly covariance calculation in panel data -r

user_9192448 0
Viewed 214

I have a panel dataframe like this 

date        firms   return   mkreturn
5/1/1988        A       5       4
6/1/1988        A       6       6
7/1/1988        A       4       12
8/1/1988        A       5       3
9/1/1988        A       6       12
11/1/1988       A       6       14
12/1/1988       A       12      5
13/01/1988      A       3       6
14/01/1988      A       2       4
15/01/1988      A       5       5
16/01/1988      A       2       6
18/01/1988      A       7       6
19/01/1988      A       3       2
20/01/1988      A       5       7
21/01/1988      A       7       2
22/01/1988      A       5       5
23/01/1988      A       9       7
25/01/1988      A       1       5
26/01/1988      A       5       6
27/01/1988      A       2       6
28/01/1988      A       7       12
29/01/1988      A       2       3
5/1/1988        B       5       2
6/1/1988        B       7       5
7/1/1988        B       5       5
8/1/1988        B       9       7
9/1/1988        B       1       5
11/1/1988       B       5       6
12/1/1988       B       2       12
13/01/1988      B       7       6
14/01/1988      B       2       11
15/01/1988      B       5       2
16/01/1988      B       6       14
18/01/1988      B       8       12
19/01/1988      B       5       15
20/01/1988      B       4       8
21/01/1988      B       3       9
22/01/1988      B       18      10
23/01/1988      B       5       3
25/01/1988      B       2       5
26/01/1988      B       7       6
27/01/1988      B       3       8
28/01/1988      B       9       5
29/01/1988      B       2       3

I want to find out monthly covariance of each firm's return with market return. Hence the expected output is like 

date        Firms       cov(return, mkreturn)
Jan-88       A             ....
Jan-88       B             ....

I use the following formula to find out monthly variance

df_var<-aggregate( return ~ Month+Year+firms, df , var )

How can I modify this formula to find out covariance? Please help me in this regard.

aggregate r
2 Answers

We convert the 'date' to Date class, format it to the 'month-year' format in the group_by step along with 'firms' and apply the cov 

library(dplyr)
library(lubridate)
df %>%
   group_by(date = format(dmy(date), '%b-%y'), firms) %>%
   summarise(cov = cov(return, mkreturn))

Or a similar option with data.table

library(data.table)
setDT(df)[, .(cov = cov(return, mkreturn)), 
     .(date = format(as.Date(date, '%d/%m/%Y'), '%b-%y'), firms)]
#     date firms      cov
#1: Jan-88     A 1.727273
#2: Jan-88     B 1.653680

Or with base R using split

lst1 <- split(df, list(df$firms, format(as.Date(df$date, '%d/%m/%Y'), 
                '%b-%y')), drop = TRUE)
sapply(lst1, function(x) cov(x$return, x$mkreturn))
user_37322710

A base way: Use by to split data and lapply to a function.

group1 <- strftime(as.Date(df$date, "%d/%m/%Y"), "%Y-%m")
group2 <- df$firms

Return of Covariance Matrix

by(df[-(1:2)], list(group1, group2), cov)

# : 1988-01
# : A
#            return  mkreturn
# return   6.712121  1.727273
# mkreturn 1.727273 11.160173
# ------------------------------------------------------------ 
# : 1988-01
# : B
#            return mkreturn
# return   13.30736  1.65368
# mkreturn  1.65368 14.18398

Return of Covariance

by(df[-(1:2)], list(group1, group2), function(x){
  cov(x[[1]], x[[2]])
})

# : 1988-01
# : A
# [1] 1.727273
# ------------------------------------------------------------ 
# : 1988-01
# : B
# [1] 1.65368
user_100689850
Related