Split date-time column into Date and time variables
Solution 1
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.
Solution 2
How about
df$Date <- as.Date(df$Start)
df$Time <- format(df$Start,"%H:%M:%S")
Solution 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")
Solution 4
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
.
Solution 5
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
Jalalala
Updated on July 10, 2022Comments
-
Jalalala almost 2 years
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'.