r - 如何在 dplyr 中创建日期序列？

Question

我有一个看起来像这样的数据集:

dt <- structure(list(servicerequestid = c("254475", "255470", "249438", 
"249398", "249399"), createdate = structure(c(1471592400, 1471874280, 
1470037140, 1470028740, 1470031020), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), closedate = structure(c(1473661860, 1472457480, 1470641700, 
1491918180, 1470293940), tzone = "UTC", class = c("POSIXct", 
"POSIXt"))), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-5L), .Names = c("servicerequestid", "createdate", "closedate"
))

# A tibble: 5 x 3
  servicerequestid          createdate           closedate
             <chr>              <dttm>              <dttm>
1           254475 2016-08-19 07:40:00 2016-09-12 06:31:00
2           255470 2016-08-22 13:58:00 2016-08-29 07:58:00
3           249438 2016-08-01 07:39:00 2016-08-08 07:35:00
4           249398 2016-08-01 05:19:00 2017-04-11 13:43:00
5           249399 2016-08-01 05:57:00 2016-08-04 06:59:00

每个servicerequestid是从createdate到closedate一直保持打开的服务请求的id。我想转换此数据集，以便每个 servicerequestid 的观察次数与票据保持开放的日期及其相应日期一样多。

例如，对于 servicerequestid== 255470，数据集如下所示:

# A tibble: 8 x 2
  servicerequestid       date
             <dbl>     <date>
1           255470 2016-08-22
2           255470 2016-08-23
3           255470 2016-08-24
4           255470 2016-08-25
5           255470 2016-08-26
6           255470 2016-08-27
7           255470 2016-08-28
8           255470 2016-08-29

我正在尝试下面的代码，但它不起作用:

dt %>%
  mutate(seq.Date(as.Date(createdate), as.Date(closedate), by="days"))

一些背景:我正在尝试在 ggplot 中创建一个动画密度图，我认为一种可能的方法是创建日常观察。这样，对于每一天，我应该能够可视化打开的工单数量。

Answer 1

这是一种方法:

library(tidyverse)
dt %>%
  mutate_if(~inherits(.x, "POSIXct"), as.Date) %>% # convert posix cols to date
  gather(var, date, -1) %>%                        # wide to long format 
  select(-var) %>%                                 # we don't need this 
  group_by(servicerequestid) %>%                   # for every id...
  expand(date = full_seq(date, 1)) %>%             # create the date range
  filter(servicerequestid == 255470)               # Then grab the example one
# # A tibble: 8 x 2
# # Groups: servicerequestid [1]
# servicerequestid date      
# <chr>            <date>    
# 1 255470           2016-08-22
# 2 255470           2016-08-23
# 3 255470           2016-08-24
# 4 255470           2016-08-25
# 5 255470           2016-08-26
# 6 255470           2016-08-27
# 7 255470           2016-08-28
# 8 255470           2016-08-29