1

I have the following R matrix:

Date MyVal
2016  1
2017  2
2018  3
....
2026  10

What I want to do is "blow it up" so that it goes like this (where monthly values are linearly interpolated):

Date        MyVal
01/01/2016    1
02/01/2016    ..
....
01/01/2017    2
....
01/01/2026    10

I realize I can easily generate the sequence using:

DateVec <- seq(as.Date(paste(minYear,"/01/01", sep = "")), as.Date(paste(maxYear, "/01/01", sep = "")), by = "month")

And I can use that to make a large matrix and then fill things in using a for loop over the DateVector in but I wonder if there's a more elegant R way to do this?

M--
  • 25,431
  • 8
  • 61
  • 93
user1357015
  • 11,168
  • 22
  • 66
  • 111
  • have a look at `?merge` – HubertL May 30 '17 at 23:22
  • If you provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) I can check the solution on it. – M-- May 31 '17 at 01:55
  • Check the solution below. Meanwhile, it needs some modification (not to the last line but to the data-frame preparation part) based on exact structure of your dataset. – M-- May 31 '17 at 13:46
  • @HubertL I am curious about how to use `merge` for interpolation. This is [one of my first questions here](https://stackoverflow.com/questions/42938001/time-series-interpolation). I was at first trying to use `zoo` and `merge` for it but ended up with `approx` (using `merge` I was getting y values repeated instead of interpolated). Would you take a look at it to see if you can provide a solution with that approach? Will be happy to accept it. Also, it can be helpful for this question as they fall into the same category (time series interpolation). – M-- May 31 '17 at 14:12
  • 1
    You're right @Masoud, I missed the "interpolated"... so did OP's example – HubertL May 31 '17 at 16:42

1 Answers1

1

You can use stats::approx:

library(stats)

ipc <- approx(df$Date, df$MyVal, xout = DateVec, 
rule = 1, method = "linear", ties = mean)

You probably need to first convert the data in your original data-frame to have month and day and also be in asPOSIXct or as.Date format.

Based on what you provided, this works:

#Make the reference data-frame for interpolation:
DateVec <- seq(min(df$Date, na.rm=T), 
               max(df$Date, na.rm=T), by = "month")

#Interpolation:
intrpltd_df <- approx(df$Date, df$MyVal, xout = DateVec, 
          rule = 1, method = "linear", ties = mean)

#            x        y 
# 1 2016-01-01 1.000000 
# 2 2016-02-01 1.084699 
# 3 2016-03-01 1.163934 
# 4 2016-04-01 1.248634 
# 5 2016-05-01 1.330601 
# 6 2016-06-01 1.415301

Data:

#reproducing the data-frame:
Date <- seq(2016,2026)
MyVal <- seq(1:11)
Date <- data.frame(as.Date(paste0(Date,"/01/01"))) #yyyy-mm-dd format
df <- cbind(Date, MyVal)
df <- as.data.frame(df)
colnames(df) <- c ("Date", "MyVal") #Changing Column Names
M--
  • 25,431
  • 8
  • 61
  • 93