R:如何根据数据帧的值添加行?

Question

目前，我有两个数据帧，我想合并。数据帧A包含daily酒店房间的能源和水的消耗数据和数据框架B包含住在房间里的人的信息。为了匹配数据帧A的数据结构，我需要解决以下关于数据帧B的问题:

数据帧B目前看起来像这样:

`Person ID``                     `Apartment`                    `contract_start`         `contract_end`
<chr>                             <chr>                          <date>                   <date>                
1 hnd48                             T217                           2021-09-16               2021-09-18            
2 jFDJu                             T217                           2021-09-19               2021-09-21            
3 kqKcX                             A705                           2021-09-16               2021-09-19            

为了匹配数据帧A的数据结构，一个人住在酒店房间里的每一天需要成为一个新的行。因此，我想添加一个新的列"日期"，它计算一个人住在房间里的每一天，从"contract_start"开始，以"contract_end"结束。因此，理想情况下，数据帧应该是这样的:

`Person ID``                     `Apartment`                    `dates`         
<chr>                             <chr>                          <date>                                 
1 hnd48                             T217                           2021-09-16
2 hnd48                             T217                           2021-09-17
3 hnd48                             T217                           2021-09-18                           
4 jFDJu                             T217                           2021-09-19
5 jFDJu                             T217                           2021-09-20
6 jFDJu                             T217                           2021-09-21                            
7 kqKcX                             A705                           2021-09-16
8 kqKcX                             A705                           2021-09-17
9 kqKcX                             A705                           2021-09-18
10kqKcX                             A705                           2021-09-19                             

我如何用代码做到这一点?

最诚挚的问候,文森特

Answer 1

library(tidyverse)
df = tribble(
~`Person ID`, ~Apartment, ~contract_start, ~contract_end,
"hnd48", "T217", "2021-09-16", "2021-09-18",
"jFDJu", "T217", "2021-09-19", "2021-09-21", 
"kqKcX", "A705", "2021-09-16", "2021-09-19"
) %>%
mutate(across(c(contract_start, contract_end), as.Date)) 
df %>% 
rowwise() %>% 
mutate(
dates = paste0(
as.character(
seq(contract_start, contract_end, by = "days")
), collapse = ",")
) %>%
select(-c(contract_start, contract_end)) %>% 
separate_rows(dates, sep = ",") %>% 
mutate(dates = as.Date(dates))
# A tibble: 10 x 3
`Person ID` Apartment dates     
<chr>       <chr>     <date>    
1 hnd48       T217      2021-09-16
2 hnd48       T217      2021-09-17
3 hnd48       T217      2021-09-18
4 jFDJu       T217      2021-09-19
5 jFDJu       T217      2021-09-20
6 jFDJu       T217      2021-09-21
7 kqKcX       A705      2021-09-16
8 kqKcX       A705      2021-09-17
9 kqKcX       A705      2021-09-18
10 kqKcX       A705      2021-09-19