How to convert weekly data to monthly in r (or in Julia)

Question

I have weekly series on financial risk index data as follows:

DATE NFCIRISK

1/8/1971 0.58

1/15/1971 0.61

......through

10/6/2017 -0.88

10/13/2017 -0.89

10/20/2017 -0.89

10/27/2017 -0.89

I want to convert them into a monthly(average of four weeks) series and I tried to use following in r but didnt work.

library(xlsx)

library(xts)

month.end <- endpoints(MyData, on = "months")

monthly <- period.apply(MyData, INDEX = month.end, FUN = mean)

head(monthly)

Could anyone please help me if any other ways I can try either in r or in Julia. Thanks and appreciating your response.

Answer 1

You can try following :

Use "Quandl" package in R. Which allows you to download data for Monthly, Quarterly, Weekly, Daily directly using single argument. It also provides the Index data.

Hope this will help you!!

Answer 2

See the endpoints function in R.

It returns a numeric vector corresponding to the last observation in each period specified by on, with a zero added to the beginning of the vector, and the index of the last raster in x at the end.

Valid values for the argument on include: “us” (microseconds), “microseconds”, “ms” (milliseconds), “milliseconds”, “secs” (seconds), “seconds”, “mins” (minutes), “minutes”, “hours”, “days”, “weeks”, “months”, “quarters”, and “years”.

Answer 3

Such computations can be handled by tapply, which is in R base.

Suppose your data is stored in a dataframe MyData, first column the timestamps, second column the values:

MyData <- read.table(text=
"DATE NFCIRISK
01/8/1971 0.58
01/15/1971 0.61
10/6/2017 -0.88
10/13/2017 -0.89
10/20/2017 -0.89
10/27/2017 -0.89",
sep = " ", stringsAsFactors = FALSE, header = TRUE)

MyData[[1]] <- as.Date(MyData[[1]], "%m/%d/%Y")
MyData

##         DATE NFCIRISK
## 1 1971-01-08     0.58
## 2 1971-01-15     0.61
## 3 2017-10-06    -0.88
## 4 2017-10-13    -0.89
## 5 2017-10-20    -0.89
## 6 2017-10-27    -0.89

Then you can simply write:

tapply(MyData[[2]], format(MyData[[1]], "%Y-%m"), mean)

and get

1971-01  2017-10 
 0.5950  -0.8875