How to multiply datetimes and rotate a dimension column in R

Question

I have such a data frame

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) 

I'm trying to pick dates in this format

 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.. 

In doing so, I would also like to rotate the “Measurement” column into its separate columns with each category counted

My desired result

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

I tried something like this to get date ranges

 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 

As a subset according to this problem. I tried to do this with dcast, but did not understand.

 df2 <- dcast(df1, Datetime ~ Measurement) 

Please provide some guidance on this.

Answer 1

This seems to suit your needs (as per your comments). I simply create a sequence from the first date to the last in days, and then use the findInterval function to match the days. Then a simple dcast gives you what you need.

 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