CODEWITHSUNDEEP
rdataframe
user11028193
user11028193

Reputation:

Compute the variance of a moving window in a dataframe

Hey I want to compute the variance of column. My dataframe is sorted by the as.Date() format. Here you can see a snippet of it:

Date       USA       ARG       BRA         CHL          COL        MEX PER
2012-04-01   1 0.2271531 0.4970299 0.001956865 0.0005341452 0.07341428  NA
2012-05-01   1 0.2218906 0.4675895 0.001911405 0.0005273186 0.07026524  NA
2012-06-01   1 0.2054076 0.4531661 0.001891352 0.0005292575 0.06897811  NA
2012-07-01   1 0.2033470 0.4596730 0.001950686 0.0005312600 0.07269619  NA
2012-08-01   1 0.1993882 0.4596039 0.001980537 0.0005271514 0.07268987  NA
2012-09-01   1 0.1967152 0.4593390 0.002011212 0.0005305549 0.07418838  NA
2012-10-01   1 0.1972730 0.4597584 0.002002203 0.0005284380 0.07428555  NA
2012-11-01   1 0.1937618 0.4519187 0.001979805 0.0005238670 0.07329656  NA
2012-12-01   1 0.1854037 0.4500448 0.001993309 0.0005323795 0.07453949  NA
2013-01-01   1 0.1866007 0.4607501 0.002013112 0.0005412329 0.07551040  NA
2013-02-01   1 0.1855950 0.4712956 0.002011067 0.0005359562 0.07554661  NA

The dataframe ranges from january 2004 up to dezember 2018. But I do not want to compute the compute the variance of the whole columnes. I want to compute the variance of one year (or 12 values) which is moving month by month.

I do not really know how to start. I can imagine using the zoo package and the rollapply. But here the problem is (I think) that R computes uses the values around it and not past it?

I also found this question: R: create a data frame out of a rolling window, so my idea was to get rid of the date column. It is easy to build the matrix, but now I do not understand how to apply the variance function to my data...

Is there a smart way to compute it all in one and also using the information of the date? If not, I also appreciate any other solution from you!

Upvotes: 1

Views: 299

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 270010

We can use rollappyr to perform the rolling computations. Since there are only 11 rows in the data in the question we can't take 12 month averages but using 3 month averages instead we can illustrate it. Remove fill = NA if you want to omit the NA rows or replace it with partial = TRUE if you want variances using fewer than 12 near the beginning. If you want a data frame result use fortify.zoo(zv) .

library(zoo)

z <- read.zoo(DF)
zv <- rollapplyr(z, 3, var, fill = NA)
zv

giving this zoo object:

           USA          ARG          BRA          CHL          COL          MEX PER
2012-04-01  NA           NA           NA           NA           NA           NA  NA
2012-05-01  NA           NA           NA           NA           NA           NA  NA
2012-06-01   0 1.287083e-04 4.998008e-04 1.126781e-09 1.237524e-11 5.208793e-06  NA
2012-07-01   0 1.033001e-04 5.217420e-05 9.109406e-10 3.883996e-12 3.565057e-06  NA
2012-08-01   0 9.358558e-06 1.396497e-05 2.060928e-09 4.221043e-12 4.600220e-06  NA
2012-09-01   0 1.113297e-05 3.108380e-08 9.159058e-10 4.826929e-12 7.453672e-07  NA
2012-10-01   0 1.988357e-06 4.498977e-08 2.485889e-10 2.953403e-12 8.001948e-07  NA
2012-11-01   0 3.560373e-06 1.944961e-05 2.615387e-10 1.168389e-11 2.971477e-07  NA
2012-12-01   0 3.717777e-05 2.655440e-05 1.271886e-10 1.814869e-11 4.312436e-07  NA
2013-01-01   0 2.042867e-05 3.268476e-05 2.806455e-10 7.540331e-11 1.231438e-06  NA
2013-02-01   0 4.134729e-07 1.129013e-04 1.186146e-10 1.983651e-11 3.263780e-07  NA

We can plot the log of the variances like this:

library(ggplot2)
autoplot(log(zv), facet = NULL) + geom_point() + ylab("log(var(.))")

screenshot

Note

We assume that the starting point is the data frame generated reproducibly below:

Lines <- "Date       USA       ARG       BRA         CHL          COL        MEX PER
2012-04-01   1 0.2271531 0.4970299 0.001956865 0.0005341452 0.07341428  NA
2012-05-01   1 0.2218906 0.4675895 0.001911405 0.0005273186 0.07026524  NA
2012-06-01   1 0.2054076 0.4531661 0.001891352 0.0005292575 0.06897811  NA
2012-07-01   1 0.2033470 0.4596730 0.001950686 0.0005312600 0.07269619  NA
2012-08-01   1 0.1993882 0.4596039 0.001980537 0.0005271514 0.07268987  NA
2012-09-01   1 0.1967152 0.4593390 0.002011212 0.0005305549 0.07418838  NA
2012-10-01   1 0.1972730 0.4597584 0.002002203 0.0005284380 0.07428555  NA
2012-11-01   1 0.1937618 0.4519187 0.001979805 0.0005238670 0.07329656  NA
2012-12-01   1 0.1854037 0.4500448 0.001993309 0.0005323795 0.07453949  NA
2013-01-01   1 0.1866007 0.4607501 0.002013112 0.0005412329 0.07551040  NA
2013-02-01   1 0.1855950 0.4712956 0.002011067 0.0005359562 0.07554661  NA"
DF <- read.table(text = Lines, header = TRUE)

Upvotes: 1

Related Questions