CODEWITHSUNDEEP
rstringlubridate
Dylan
Dylan

Reputation: 23

How can I separate this date time column into different columns?

I am currently trying to separate my date and time data into seperate columns.

My data is in the format of dd/mm/yyyy hh:mm:ss a.m (or p.m). What I need to do is separate the date and time into 2 separate columns. Then subset my date into day, month and year. I also want to convert the time into a 24 hour time. I have tried using strsplit, as.Date, many many others but I still cannot get my data to a point that I can actually do anything with it. There has just been countless errors.

To make things understandable, my date and time column of data is inside a larger dataset. The heading for this column of information required is 'Date_Time'. In this column an example date would be '17/09/2019 9:15:27 a.m.'. As you can imagine it is incredibly frustrating trying to work with this.

What I ideally want is...

day month year time
17 9 2019 09:15:27

...whilst still preserving the original Date_Time column

Upvotes: 1

Views: 1269

Answers (1)

GKi
GKi

Reputation: 39707

You can convert it into POSIXct and use then format to extract day, month, year and time.

x <- c("17/09/2019 9:15:27 a.m.", "17/09/2019 9:15:27 p.m.")
x <- gsub("\\.", "", x) #Remove the . in a.m.
x <- as.POSIXct(x, format="%d/%m/%Y %I:%M:%S %p") #convert to POSIX
data.frame(day   = format(x, "%d"), 
           month = format(x, "%m"),
           year  = format(x, "%Y"),
           time  = format(x, "%T"))
#  day month year     time
#1  17    09 2019 09:15:27
#2  17    09 2019 21:15:27

In case only splitting up into columns is enough, I would use strsplit and split on / or .

x <- c("17/09/2019 9:15:27 a.m.", "17/09/2019 9:15:27 p.m.")
do.call(rbind, strsplit(x, "[/ ]"))
#     [,1] [,2] [,3]   [,4]      [,5]  
#[1,] "17" "09" "2019" "9:15:27" "a.m."
#[2,] "17" "09" "2019" "9:15:27" "p.m."

Upvotes: 3

Related Questions