Split date-time column into Date and time variables

Question

I have a 'date-time column 'Start' in the format "Y-m-d H:M:S". I want to split this column into a "Date" and a "time" column.

I have tried the following:

df$Date <- sapply(strsplit(as.character(df$Start), " "), "[", 1)
df$Time <- sapply(strsplit(as.character(df$Start), " "), "[", 2)

This works, however, if I use the function str(df)

# 'data.frame':   18363 obs. of  19 variables:<br>
#  $ Start    : Factor w/ 67 levels "2013-09-01 08:07:41.000",..: 1 1 1 1 1 1 1 1 1 1 ...
# [snip]

So now I only need to know how to convert the time and date from factor to 'time' and 'date'.

Answer 1

Assuming your data looks similar to this with one datetime column and many other columns

df <- data.frame(a = 1:5, datetime = as.POSIXct(c('2019-02-01 01:00:00', 
                 '2019-02-01 02:00:00', '2019-02-01 03:00:00', 
                 '2019-02-01 04:00:00', '2019-02-01 05:00:00')))

df
#  a            datetime
#1 1 2019-02-01 01:00:00
#2 2 2019-02-01 02:00:00
#3 3 2019-02-01 03:00:00
#4 4 2019-02-01 04:00:00
#5 5 2019-02-01 05:00:00

We can split the column on whitespace (or any other delimiter present) to get a separate date and time columns which can be done using tidyr::separate

library(tidyr)
separate(df, datetime, c("date", "time"), sep = " ")
#  a       date     time
#1 1 2019-02-01 01:00:00
#2 2 2019-02-01 02:00:00
#3 3 2019-02-01 03:00:00
#4 4 2019-02-01 04:00:00
#5 5 2019-02-01 05:00:00

If we want to keep the original column (datetime) we can add remove = FALSE.

---

separate is superseded and we can now use separate_wider_* functions to split the columns.

separate_wider_delim(df, datetime, delim = " ", names = c("date", "time"))

Answer 2

If you are open for a non-base alternative, you may use data.table::IDateTime which

takes a date-time input and returns a data table with columns date and time

...of class IDate* and ITime** respectively:

x = as.POSIXct("2013-09-01 08:07:41") + 0:2
IDateTime(x)
#         idate    itime
# 1: 2013-09-01 08:07:41
# 2: 2013-09-01 08:07:42
# 3: 2013-09-01 08:07:43

---

*IDate is a date class derived from Date. It has the same internal representation as the Date class, except the storage mode is integer.

**ITime is a time-of-day class stored as the integer number of seconds in the day.

Answer 3

df$Date <- as.Date(df$Start) # already got this one from the answers above
df$Time <- format(as.POSIXct(df$Start), format = "%H:%M:%S") 

Use as.Date to convert 'Start' to a variables of class Date. For the time variable, we first convert 'Start' to POSIXct. Then use format to extract the time component as a string.

Answer 4

You can use it in this method. It works very good

format(mdy(df_5star$Date4)

Hope it helps!

Answer 5

By seeing your column format, I'd say you could use as.POSIXct to properly format your column, and then use format() to extract the desired data.

This is the code I use when splitting a DateTime column,

df$Time <- format(as.POSIXct(df$Start,format="%Y:%m:%d %H:%M:%S"),"%H:%M:%S")

df$Date <- format(as.POSIXct(df$Start,format="%Y:%m:%d %H:%M:%S"),"%Y:%m:%d")

Answer 6

You might prefer to do something like this, avoiding the use of an lapply loop which isn't really necessary (but it's not a bad thing either!)...

#  If we had this data...
df <- data.frame( Start = c( "13:11:2013 15:39" , "13:11:2013 16:15" , "13:11:2013 17:52" ) )

#  We can directly make two columns from the split strings without
#  using a loop by call 'do.call'..
new <- do.call( rbind , strsplit( as.character( df$Start ) , " " ) )
#     [,1]         [,2]   
#[1,] "13:11:2013" "15:39"
#[2,] "13:11:2013" "16:15"
#[3,] "13:11:2013" "17:52"


#  Cbind them to the original data liek so...
cbind( df , Date = new[,2] , Time = new[,1] )
#             Start  Date       Time
#1 13:11:2013 15:39 15:39 13:11:2013
#2 13:11:2013 16:15 16:15 13:11:2013
#3 13:11:2013 17:52 17:52 13:11:2013

Answer 7

How about

df$Date <- as.Date(df$Start)

df$Time <- format(df$Start,"%H:%M:%S")