r - Series of Correlation coefficient calculation

Question

I want to analyse the default data set in R (mtcars data set). I am interested in creating column of correlation coefficients according to the below rule. Correlation coefficient of only first three observations ((i.e., row 1,2,3)) between "mpg" and "wt", then leaving the first row, calculate again correlation coefficient between next three observations (i.e., row 2,3,4) between mpg and wt then leaving the first two rows, calculate again correlation coefficient between next three observations (i.e., row 3,4,5) between mpg and wt and so on till end. For example

cor(mtcars$mpg[c(1,2,3)],mtcars$wt[c(1,2,3)])
cor(mtcars$mpg[c(2,3,4)],mtcars$wt[c(2,3,4)])
cor(mtcars$mpg[c(3,4,5)],mtcars$wt[c(3,4,5)]);

and so on. Can anyone help to how to automate this R code using loop etc.

Example, see how i need output, i have done it in excel but i need to do it in R.

Answer 1

The value of cor(mtcars$mpg[c(1,2,3)],mtcars$wt[c(1,2,3)]) is -0.8884586; however, the first value in the Correlation column of the output image in the question is not that so there is some error in the image shown relative to the description of what is wanted. We will assume that the description is correct and the sample output is not.

Try a rolling apply, rollapply. It applies the function cor2 to a rolling window of width 3. align = "left" means it uses the current row and the next 2 rows so that the NA values appear at the end as in the image in the question. fill = NA causes it to generate NA values for the last 2 elements since there are not 3 more elements for those.

library(zoo)

mtcars2 <- mtcars[c("mpg", "wt")]
cor2 <- function(x) cor(x[, 1], x[, 2])
transform(mtcars2, cor = rollapply(mtcars2, 3, cor2, by.column = FALSE,  
   align = "left", fill = NA))

giving:

                     mpg    wt         cor
Mazda RX4           21.0 2.620 -0.88845855
Mazda RX4 Wag       21.0 2.875 -0.82589964
Datsun 710          22.8 2.320 -0.87097656
Hornet 4 Drive      21.4 3.215 -0.99520846
Hornet Sportabout   18.7 3.440 -0.99985063
Valiant             18.1 3.460 -0.99534538
Duster 360          14.3 3.570 -0.97267882
Merc 240D           24.4 3.190 -0.90784130
Merc 230            22.8 3.150 -0.96247218
Merc 280            19.2 3.440 -0.86602540
Merc 280C           17.8 3.440 -0.99308187
Merc 450SE          16.4 4.070 -0.05428913
Merc 450SL          17.3 3.730 -0.96311366
Merc 450SLC         15.2 3.780 -0.99534934
Cadillac Fleetwood  10.4 5.250  0.05301502
Lincoln Continental 10.4 5.424 -0.98658763
Chrysler Imperial   14.7 5.345 -0.96899291
Fiat 128            32.4 2.200  0.44730718
Honda Civic         30.4 1.615 -0.86317499
Toyota Corolla      33.9 1.835 -0.94182141
Toyota Corona       21.5 2.465 -0.99341821
Dodge Challenger    15.5 3.520 -0.94720046
AMC Javelin         15.2 3.435  0.21168794
Camaro Z28          13.3 3.840 -0.90670560
Pontiac Firebird    19.2 3.845 -0.99864434
Fiat X1-9           27.3 1.935 -0.99939736
Porsche 914-2       26.0 2.140 -0.99630829
Lotus Europa        30.4 1.513 -0.99962223
Ford Pantera L      15.8 3.170 -0.93453339
Ferrari Dino        19.7 2.770 -0.96372018
Maserati Bora       15.0 3.570          NA
Volvo 142E          21.4 2.780          NA

Also see this SO post which is similar except in a data.table context: Rolling correlation with data.table