r - 如何在 R 中对日期时间进行子集化并旋转测量列

Question

我有一个这样的数据框

Datetime <- c("2015-12-31 08:30:13", "2015-12-31 12:45:00", "2016-01-01 02:53:20", "2016-01-01 03:22:18", 
              "2016-01-01 09:42:10", "2016-01-01 20:55:50", "2016-01-01 21:14:10", "2016-01-02 05:42:16",
              "2016-01-02 08:31:15", "2016-01-02 09:13:10", "2016-01-03 00:45:14", "2016-01-03 05:56:00", 
              "2016-01-03 13:44:00", "2016-01-03 14:41:20", "2016-01-03 15:33:10", "2016-01-04 04:24:00",
              "2016-01-04 17:24:12", "2016-01-04 17:28:16", "2016-01-04 18:22:34", "2016-01-05 02:34:31")

Measurement <- c("Length","Breadth","Height","Length",
                 "Breadth","Breadth","Breadth","Length",
                 "Length","Breadth","Height","Height",
                 "Height","Length","Height","Length",
                 "Length","Breadth","Breadth","Breadth")

df1 <- data.frame(Datetime,Measurement)

我正在尝试以这种格式对日期进行子集化

Day1 = December 31st,2015 at 6:30AM to January 1st 2016 6:30AM
Day2 = January 1st,2015 at 6:30AM to January 2nd 2016 6:30AM

etc..

在执行此操作时，我还想将“度量”列转换为包含每个类别计数的单独列

我想要的输出是

Days Length Breadth Height
Day1      2       1      1
Day2      1       3      0
Day3      1       1      2
Day4      2       0      2
Day5      1       3      0

我试过这样的事情来获取日期范围

today <- as.POSIXlt(Sys.time())
today$mday <- today$mday + (today$wday-(today$wday+27)) 
today$hour = "6";today$min = "30";today$sec = "0"
Back1Day <- today 
Back1Day$mday <- today$mday-1

我如何根据这个问题进行子集化。我尝试使用 dcast 来做到这一点，但没有做对。

df2 <- dcast(df1, Datetime ~ Measurement)

请就此提供一些指导。

Answer 1

这似乎满足了您的需求(根据您的评论)。我只是每天创建一个从第一个日期到最后一个日期的序列，然后使用 findInterval功能以匹配日期。然后，一个简单的 dcast给你你需要的东西。

library(data.table)
setDT(df1)[, Datetime := as.POSIXct(Datetime)] ## First need to convert to POSIXct class
df1[, Days := paste0("Day", findInterval(Datetime, 
                              seq(as.POSIXct(paste(as.Date(Datetime[1L]), "6:30")), 
                                  as.POSIXct(paste(as.Date(Datetime[.N]), "6:30")), 
                             by = "day")))]
dcast(df1, Days ~ Measurement)
#    Days Breadth Height Length
# 1: Day1       1      1      2
# 2: Day2       3      0      1
# 3: Day3       1      2      1
# 4: Day4       0      2      2
# 5: Day5       3      0      1