Split date time

Question

I have a df with a column datetime (DD:MM:yyyy HH:mm:ss)named "Start" and I would like to split this column into two named "date" and "time".
Now 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) (I cut it short so you can mostly see the variables of my concern).

'data.frame': 18363 obs. of 19 variables:
$ Start : Factor w/ 67 levels "2013-09-01 08:07:41.000",..: 1 1 1 1 1 1 1 1 1 1 ...
$ Interval : int 47259 47259 47259 47259 47259 47259 47259 47259 47259 47259 ...
$ DateTime : Factor w/ 18363 levels "2013-09-01 08:07:41.350",..: 1 2 3 4 5 6 7 8 9 10 ...
$ TimeSensor: num 158489 158489 158490 158490 158491 ...

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

If someone knows the solution I would be very grateful! I am a noob concerning R so please do not burn me to the ground..

Thanks a million!

Answer 1

Sorry for this late answer! Anyways, I got help from someone at the university and he came up with the following, very simple, adjustment of my time-code..:

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") 

This converts the factors to "date" and "POSIXct", just how I wanted it.

Thank all of you for your help! I hope I can return some kind of favour in the future, although I doubt if it will be with programming..!

Answer 2

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 3

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 4

How about

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

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

Answer 5

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

format(mdy(df_5star$Date4)

Hope it helps!

Answer 6

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

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.