Merge data frames with annual observations and time-period observations: how to duplicate time-period observations so that they are annual

Question

I need to merge two data frames to create a new data frame (df3). I'm having trouble as one df has annual observations, while the other has "time period" observations.

df1 has observations at the organization level, grouped by country, taken in repeated survey waves across several time periods.

df2 has annual observations for each country. I made an key variable, ID, that matches by country.

I need df3 to have annual observations of each organization within each country.

df1 is structured as so, with many more "from"/"to" ranges of years for each country. Each organization/year-range combination is unique:

ID        from         to         organization     
AFG       1960         1965       Afghan.A         
AFG       1960         1965       Afghan.B
ALB       1960         1965       Alb.A
ALB       1960         1965       Alb.B

df2 is structured as so, with unique observations for each country in each year:

 ID       Year    GDP.per.capita
 AFG      1960    59.77319
 AFG      1961    59.86087
 ALB      1960    78.70639
 ALB      1961    82.09523

I need df3 to have this structure, with an observation for each ethnic group in each year for each country. I basically want to duplicate each observation in df1 so that each organization is observed every year, then merge in the annual variables from df2:

ID       Year     GDP.per.capita      organization         
AFG      1960     59.77319            Afghan.A
AFG      1960     59.77319            Afghan.B
AFG      1961     59.86087            Afghan.A
AFG      1961     59.86087            Afghan.B
ALB      1960     78.70639            Alb.A
ALB      1960     78.70639            Alb.B
ALB      1961     82.09523            Alb.A
ALB      1961     82.09523            Alb.B

Any advice would be much appreciated!

Karthik S Karthik S · Accepted Answer · 2020-10-15T10:51:49

Does this work:

> library(dplyr)
> df1 %>% select(-to) %>% inner_join(df2, by= c('ID' = 'ID')) %>% select(-from) %>% relocate(1,3,4,2)
# A tibble: 8 x 4
  ID     Year GDP.per.capita organization
  <chr> <dbl>          <dbl> <chr>       
1 AFG    1960           59.8 Afghan.A    
2 AFG    1961           59.9 Afghan.A    
3 AFG    1960           59.8 Afghan.B    
4 AFG    1961           59.9 Afghan.B    
5 ALB    1960           78.7 Alb.A       
6 ALB    1961           82.1 Alb.A       
7 ALB    1960           78.7 Alb.B       
8 ALB    1961           82.1 Alb.B

Merge data frames with annual observations and time-period observations: how to duplicate time-period observations so that they are annual

1 Answers