Converting Quarterly Data to Monthly Data and merging using R

Question

I am currently trying to figure out how to convert quarterly data to monthly data using R, since I want to merge my monthly return data with quarterly summary data.

What I currently have.

Quarterly Dataset

105  20080331 Y Y N N EQ

Monthly Dataset

105  20080131 -0.087372  
105  20080229 -0.01609  
105  20080331 -0.022895

What I want to achieve.

105  20080131 -0.087372 Y Y N N EQ  
105  20080229 -0.01609  Y Y N N EQ  
105  20080331 -0.022895 Y Y N N EQ

I will have to run this for over 350000 separate rows, which I why I am seeking help. I am assuming the quarter values are the same as the former 3 months.

Update

Thanks for the quick and helpful response. However, I made a mistake in the formatting.

Y1 can take on 3 levels, namely "", "Y", "N".
Y2 can take on 3 levels, namely "", "Y", "N".
N1 can take on 4 levels, namely "", "B", "D", "E".
N2 can take on 3 levels, namely "", "F", "N".
V is return that can take on any level.

My data also ranges from January of 2008 to December of 2019. I believe this makes the problem a lot more complicated.

https://i.stack.imgur.com/MYE3w.png

https://i.stack.imgur.com/SQBY1.png

In the data examples you provide there are no column names, but in your edit, you seem to be referring to column names - I think I can infer what you mean, but the way you present your problem is not very helpful. As Nurandi mentioned in his answer, please make yourself familiar with how to ask a [reproducible questions](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). Anyways, both answers should work for you even with the specifications you mention in your edit. — Till, Mar 26 '20 at 00:22
Also, when the provided answers do not satisfy you, you should comment on the answers rather than only editing your post. That way the authors of the answers are notified, while in this case I just randomly spotted your edit. — Till, Mar 26 '20 at 00:26

score 1 · Answer 1 · answered Mar 25 '20 at 21:13

Create data:

quart_df <- 
  tibble::tribble(
    ~V1, ~V2, ~V3, ~V4, ~V5, ~V6, ~V7,
    105, "20080331", "Y", "Y", "N", "N", "EQ"
  )

month_df <- 
  tibble::tribble(
    ~V1, ~V2, ~V3,
    105,  20080131, -0.087372, 
    105,  20080229, -0.01609, 
    105,  20080331, -0.022895
  )

Join using dplyr::full_join():

library(dplyr)

full_join(month_df,
          select(quart_df, -V2),
          by = c("V1" = "V1"))
#> # A tibble: 3 x 8
#>      V1       V2    V3.x V3.y  V4    V5    V6    V7   
#>   <dbl>    <dbl>   <dbl> <chr> <chr> <chr> <chr> <chr>
#> 1   105 20080131 -0.0874 Y     Y     N     N     EQ   
#> 2   105 20080229 -0.0161 Y     Y     N     N     EQ   
#> 3   105 20080331 -0.0229 Y     Y     N     N     EQ

score 0 · Answer 2 · answered Mar 25 '20 at 21:06

You can use merge:

Quarterly <- data.frame(Q=105, D=20080331, Y1="Y", Y2="Y", N1="N",  N2="N", E="EQ")

Monthly <- data.frame(Q = c(105,105,105),
                      D = c(20080131, 20080229, 20080331),
                      V = c(-0.087372,-0.01609, -0.022895))

merge(Monthly, Quarterly[,-2])

OR

merge(Monthly, Quarterly[,-which(names(Quarterly) == "D")])

Will output same result:

    Q        D         V Y1 Y2 N1 N2  E
1 105 20080131 -0.087372  Y  Y  N  N EQ
2 105 20080229 -0.016090  Y  Y  N  N EQ
3 105 20080331 -0.022895  Y  Y  N  N EQ

PS. For next time, please submit question in the reproductible format

Converting Quarterly Data to Monthly Data and merging using R

Update

2 Answers2