Extend a data frame using another table in R-CodePudding

I have a data frame as following

df <- data.frame(id = c(12345,12345,12345,221,221,221),
                 range_key = c('w232022','w242022','w252022','w232022','w242022','w252022'),
                 country = c('US','US','US','CA','CA','CA'),
                 case = c('A','B','A','A','B','B'))

and another data frame which extend of weeks :

extended_week <- data.frame(range_key=c('w262022','w272022','w282022',...,'w522026'))

I want to add extend_week into the first data frame group by (id, country, case) The final table looks like follows:

id.   range_key country  case
12345.  w232022. US.      A
...     .....    ...      ...
12345.  W522026. US.      A

Means that each group extend to w52 year 2026.

CodePudding user response：

Using expand.grid you can get the unique combinations of your 3 columns, with your extended_week.

extended_week <- c(lapply(paste0(paste0('w', 26:52)), function(x) paste0(x, 2022:2026)) |> unlist())

expand.grid(df$id, df$country, df$case, extended_week, stringAsFactors = FALSE) |>
 unique() |> 
setNames(c("id","country","case","range_key"))



       id country case range_key
1   12345      US    A   w232022
2     221      US    A   w232022
3   12345      CA    A   w232022
4     221      CA    A   w232022
5   12345      US    B   w232022
6     221      US    B   w232022
7   12345      CA    B   w232022
8     221      CA    B   w232022
9   12345      US    A   w242022
10    221      US    A   w242022
11  12345      CA    A   w242022
12    221      CA    A   w242022
13  12345      US    B   w242022
14    221      US    B   w242022
15  12345      CA    B   w242022
16    221      CA    B   w242022
17  12345      US    A   w252022
18    221      US    A   w252022
19  12345      CA    A   w252022
20    221      CA    A   w252022