How do I split my date time column so that it recognises the 24 hour clock in r?

1
votes

I'm trying to split a column that contains both a date and time to seperate time, day, month, year, dayofweek, hour and minute columns. I'm able to split the columns but the only problem is that the AM and PM times are not separating and the only times showing are 1 - 12 instead of splitting out the AM and PM hours even though the original dataset uses the 24hr time. I was wondering if anyone knows a way to do this?

The code I'm using to split the column is:

taxijuly$Trip.Start.Timestamp <- as.POSIXct(taxijuly$Trip.Start.Timestamp, format = "%m/%d/%Y %H:%M:%S")

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

taxijuly$Trip.Start.Timestamp <- ymd_hms(taxijuly$Trip.Start.Timestamp)

taxijuly$day <- factor(day(taxijuly$Trip.Start.Timestamp))

taxijuly$month <- factor(month(taxijuly$Trip.Start.Timestamp, label = TRUE))

taxijuly$year <- factor(year(taxijuly$Trip.Start.Timestamp))

taxijuly$dayofweek <- factor(wday(taxijuly$Trip.Start.Timestamp, label = TRUE))

taxijuly$hour <- factor(hour(hms(taxijuly$Time)))

taxijuly$minute <- factor(minute(hms(taxijuly$Time)))

taxijuly$second <- factor(second(hms(taxijuly$Time)))

and the data in the column I'm trying to split looks like this:

Trip Start Timestamp

09/03/2019 00:00
09/03/2019 00:00
09/03/2019 00:00
09/03/2019 00:00

Any help would be hugely appreciated.

1
rdatetimesplit

1 Answers

2
votes

Once, we convert to 'Datetime', it is not needed to further do the conversion. As we are using lubridate, the convenient functions from the package can be used to extract the components

library(lubridate)
library(dplyr)
out <- taxijuly %>%
         mutate(Trip.Start.Timestamp = mdy_hms(Trip.Start.Timestamp), 
          day = day(Trip.Start.Timestamp),
          month = month(Trip.Start.Timestamp), 
          year = year(Trip.Start.Timestamp),
          dayofweek = wday(Trip.Start.Timestamp),
          minute = minute(Trip.Start.Timestamp),
          second = second(Trip.Start.Timestamp))

data

taxijuly <- structure(list(Trip.Start.Timestamp = c("09/03/2019 00:00", "09/03/2019 00:00", 
"09/03/2019 00:00", "09/03/2019 00:00")), class = "data.frame", row.names = c(NA, 
-4L))