How to align monthly time series next to weekly time series in R

Question

My goal is to combine two time series (weekly in 1st column and monthly in 2nd column and the date format of the weekly time series).

I have a weekly time series with values from 2004-01-04 up to 2018-06-24.

I also have a monthly time series with values from 2004-01-01 up to 2018-06-01. (It always takes the first day of the month as -01)

For the monthly time series, I want to fill up the NA with the same value for every week in the same month.

I have tried the na.locf function in R:

na.locf(merge(monthly, foo=zoo(NA, order.by=seq(start(monthly), end(monthly), "week")))[, 1])

However, it does not quiet work out right.

In the end it should look something like this picture:

Answer 1

Have you looked into the melt function? This might help you out in the right direction. (pressure is included in the default 'datasets'-package)

library(reshape2)

data <- head(pressure)

head(data)
melt(data, variable = "pressure")

    > head(data)
  temperature pressure
1           0   0.0002
2          20   0.0012
3          40   0.0060
4          60   0.0300
5          80   0.0900
6         100   0.2700

> melt(data, variable = "pressure")
      pressure   value
1  temperature 0.0e+00
2  temperature 2.0e+01
3  temperature 4.0e+01
4  temperature 6.0e+01
5  temperature 8.0e+01
6  temperature 1.0e+02
7     pressure 2.0e-04
8     pressure 1.2e-03
9     pressure 6.0e-03
10    pressure 3.0e-02
11    pressure 9.0e-02
12    pressure 2.7e-01

Answer 2

Not very clean but maybe it helps:

d<-data.frame(rep(c(3,NA,NA,NA,NA,4,NA,NA,NA,NA),7),seq(as.Date("1999-01-01",tz = "GMT","%Y-%m-%d"),as.Date("2000-05-01",tz = "GMT","%Y-%m-%d"), by="1 week"))
colnames(d)<-c("data","Date")
time<-d$Date
a<-aggregate(d,by=list(substr(d$Date,1,7)),FUN=function(x) max(x,na.rm=TRUE))
colnames(a)<-c("Date","Data","xyz")
d$Date<-substr(d$Date,1,7)
t<-merge(a[-3],d[-1],by = "Date")
t$Date<-time