I have two data frames, one with room number and the other with dates. I want to add the dates to the room numbers.
| Rooms |
|---|
| A200 |
| A201 |
| Dates |
|---|
| 2022-09-07 |
| 2022-09-08 |
| 2022-09-09 |
The resulting data frame
| Rooms | Dates |
|---|---|
| A200 | 2022-09-07 |
| A200 | 2022-09-08 |
| A200 | 2022-09-09 |
| A201 | 2022-09-07 |
| A201 | 2022-09-08 |
| A201 | 2022-09-09 |
Is there an easy way to accomplish this using R or the Tidyverse?
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
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
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