CODEWITHSUNDEEP
r
user8959427
user8959427

Reputation: 2067

Joining two data frames together based on quarters and years

I have two data frames I want to join together. I have a series of dates and I want to join a quarter at time t up with its t+1 quarter.

I get a little stuck at quarter 4, joining up with the year + 1. i.e. Q4 of 2006 should be joined with Q1 of 2007.

The data I have is that I have an event which occurs once per year, say February 15th 2006 and another event which occurs March 3rd 2006. At the end of March I collect all the events together and then obtain a number between 1 and 5 for each document. I want to the track the monthly performance over the next 3 months (or in quarter 2 in this case).

Then I take the events which happened between April and June and track these from July - Sept.

Then take all the events which happened between July and September and track the performance from Oct to Dec.

Take all the events which happened between Oct and Dec and track the performance from Jan t+1 to Mar t+1.

How can this be done?

library(lubridate)

dates_A <- sample(seq(as.Date('2005/01/01'), as.Date('2010/01/01'), by="day"), 1000)
x_var_A <- rnorm(1000)

d_A <- data.frame(dates_A, x_var_A) %>%
  mutate(quarter_A = quarter(dates_A),
         year_A = year(dates_A))


dates_B <- sample(seq(as.Date('2005/01/01'), as.Date('2010/01/01'), by="day"), 1000)
x_var_B <- rnorm(1000)

d_B <- data.frame(dates_B, x_var_B) %>%
  mutate(quarter_B = quarter(dates_B),
         year_B = year(dates_B),
         quarter_plus_B = quarter(dates_B + months(3)))

Upvotes: 1

Views: 50

Answers (1)

Brian Fisher
Brian Fisher

Reputation: 1367

One way to accomplish this is to combine your year and quarter and join based on that. Your code already includes the quarter +1, so adding a line to each of your calls to mutate() and then using joining based on the new column. 

library(lubridate)
library(tidyverse)

dates_A <- sample(seq(as.Date('2005/01/01'), as.Date('2010/01/01'), by="day"), 1000)
x_var_A <- rnorm(1000)

d_A <- data.frame(dates_A, x_var_A) %>%
      mutate(quarter_A = quarter(dates_A),
             year_A = year(dates_A),
             YearQ = paste(year_A, quarter_A))
dates_B <- sample(seq(as.Date('2005/01/01'), as.Date('2010/01/01'), by="day"), 1000)
x_var_B <- rnorm(1000)

d_B <- data.frame(dates_B, x_var_B) %>%
      mutate(quarter_B = quarter(dates_B),
             year_B = year(dates_B),
             quarter_plus_B = quarter(dates_B + months(3)),
             YearQ = paste(year_B, quarter_plus_B))

final_d <- left_join(d_A, d_B))

Upvotes: 2

Related Questions