I have two data frames, one with room number and the other with dates. I want to add the dates to the room numbers.

The resulting data frame

Is there an easy way to accomplish this using R or the Tidyverse?

CodePudding user response：

A solution in base R:

df1 <- data.frame("rooms" = c("A200", "A201"))
df2 <- data.frame("dates" = c("2022-09-07", "2022-09-08", "2022-09-09"))
                
df <- merge(df1, df2)    
df_grouped <- df[order(df$rooms), ]

Result:

  rooms      dates
1  A200 2022-09-07
3  A200 2022-09-08
5  A200 2022-09-09
2  A201 2022-09-07
4  A201 2022-09-08
6  A201 2022-09-09

CodePudding user response：

Here is a different approach: We first bring all dates in row. Then we keep only so many rows as in df1. After that we cbind both together (both have same row counts), with separate_rows() we get the desired outputput:

library(lubridate)
library(tidyverse)

df2 %>% 
  mutate(Dates = toString(Dates)) %>% 
  slice(1:nrow(df1)) %>% 
  cbind(df1) %>% 
  separate_rows(Dates, sep = ",") %>% 
  mutate(Dates = ymd(Dates))

 Dates      Rooms
  <date>     <chr>
1 2022-09-07 A200 
2 2022-09-08 A200 
3 2022-09-09 A200 
4 2022-09-07 A201 
5 2022-09-08 A201 
6 2022-09-09 A201 

CodePudding user response：

We could use crossing

library(tidyr)
crossing(df1, df2)
# A tibble: 6 × 2
  rooms dates     
  <chr> <chr>     
1 A200  2022-09-07
2 A200  2022-09-08
3 A200  2022-09-09
4 A201  2022-09-07
5 A201  2022-09-08
6 A201  2022-09-09