CODEWITHSUNDEEP
rdataframeapply
Niccola Tartaglia
Niccola Tartaglia

Reputation: 1667

Compute 3 Month return for dataframe of monthly returns

I have a dataframe of monthly stock returns:

d<-data.frame(replicate(5,sample(rnorm(1),6,rep=TRUE)))

Now I would like to transform these returns to n-month returns in the following manner (e.g. for n=3):

d[1,1]=(1+d[1,1])*(1+d[2,1])*(1+d[3,1])
d[2,1]=(1+d[2,1])*(1+d[3,1])*(1+d[4,1])
d[3,1]=(1+d[3,1])*(1+d[4,1])*(1+d[5,1])

And then the same for the next column:

d[1,2]=(1+d[1,2])*(1+d[2,2])*(1+d[3,2])
d[2,2]=(1+d[2,2])*(1+d[3,2])*(1+d[4,2])
d[3,2]=(1+d[3,2])*(1+d[4,2])*(1+d[5,2])

I think you get the idea.

Now, the way I was thinking to proceed is as follows:

apply(d, 2, fun)

where fun is defined as:

fun<-function(df_column)
{
  # Loop over df_column rows
 for (row in 1:nrow(df_column)) {
   d[row]=(1+d[row])*(1+d[row+1])*(1+d[row+2])
 }
}

Does this approach make sense or is there a more elegant way?

Upvotes: 1

Views: 111

Answers (2)

Kevin Arseneau
Kevin Arseneau

Reputation: 6264

You can use purrr::map_df and dplyr::lead to achieve what you are after.

library(dplyr)
library(purrr)

set.seed(42)

df <- data.frame(replicate(5, rnorm(6)))

df %>%
  map_df(function(x) {

    (1 + x) * (1 + lead(x, 1)) * (1 + lead(x, 2))

  })

# # A tibble: 6 x 5
#          X1       X2         X3         X4         X5
#       <dbl>    <dbl>      <dbl>      <dbl>      <dbl>
# 1 1.4068610 6.863243 -0.2430606 -2.3172461  1.2246900
# 2 0.9688954 2.561324  1.0225645 -1.2568729 -0.3228241
# 3 3.1256224 6.520767  1.0148172 -0.4485965 -0.8276191
# 4 2.0496361 7.100212 -1.9395879 -1.4328679 -0.4011510
# 5        NA       NA         NA         NA         NA
# 6        NA       NA         NA         NA         NA

N.B. As you are looking ahead two records, note that the final two rows will only produce NA. You could add na.omit or tidyr::drop_na to your pipe if you want them to be excluded.

Upvotes: 1

G. Grothendieck
G. Grothendieck

Reputation: 269481

Try rollapply in one of these forms depending on what it is you prefer. The first puts NAs in the last two rows. The second fills them with partial products and the third drops the last two rows. The 3 argument specifies the width of the rolling product and could be changed if you want to try other widths. See ?rollapply for more info.

library(zoo)

rollapplyr(d + 1, 3, prod, align = "left", fill = NA)

rollapplyr(d + 1, 3, prod, align = "left", partial = TRUE)

rollapplyr(d + 1, 3, prod, align = "left")

Upvotes: 2

Related Questions