How to extract start and end dates from single column in large dataset in R using lubridate?

1
votes

I'd like to accomplish something very similar to what is being done in this question. I have a large data.table (or data.frame) with a single column that has a Base Time Stamp (BST). I'd need to determine number of days for each of the unique IDs which may be many tens of thousands of rows. All the lubridate tutorials I'm finding start with the very simple start to end example... (this is a great intro but not the answer I'm looking for).

I basically need to loop through my BST column and identify a start and end date for each ID.

Here is example data:

library(data.table)

myID <- c(1,1,1,1,1,1,2,2,2,2,2,2)
BST <- c("2017-06-01 00:00:01", "2017-06-01 00:00:02",
         "2017-06-02 00:00:01", "2017-06-02 00:00:02", 
         "2017-06-03 00:00:01", "2017-06-03 00:00:02",
         "2017-06-01 00:00:01", "2017-06-01 00:00:02", 
         "2017-06-03 00:00:01", "2017-06-03 00:00:02", 
         "2017-06-05 00:00:01", "2017-06-05 00:00:02")
V3 <- c("a", "a", "a", "a", "a", "a", "b", "b", "b","b", "b", "b")
dt1 <- data.table(myID, BST, V3)

And desired result:

enter image description here

And then how is it accomplished while keeping all the original rows... a la dplyr::mutate() ?

Second desired result:

enter image description here

1
rtimelubridate

1 Answers

3
votes

You can try converting BST to date/time using lubridate::ymd_hms and then group on myID to take minimum of BST as startDates and maximum of BST as endDates. 

library(data.table)
library(lubridate)
dt1[,.(startDates= min(ymd_hms(BST)), endDates = max(ymd_hms(BST))), by=myID]
#   myID          startDates            endDates
#1:    1 2017-06-01 00:00:01 2017-06-03 00:00:02
#2:    2 2017-06-01 00:00:01 2017-06-05 00:00:02