rdatetimeposixct

Converting datetime from chr to datetime format using POSIXct function in R


I am learning to code in R and currently doing the Bellabeat Google Data Analytics Capstone project. For this, I need to transform a column with datetime values (i.e. 4/12/2016 12:00:00 AM) formatted as characters to a datetime format. I have been trying to use the as.POSIXct function, but everytime I do so, the entire column values change to NA. I looked up the problem and found that it is most likely because the I have a typo in the format string of the POSIXct function. However, I have verified that the format codes I am using are correct, as well as the order and the spaces in between. Yet, the function keeps changing the values to NA. I need help finding what is wrong with the way I am writing the function, please !

This is a subset of the data I am working with:

dput(head(hourly_calories$ActivityHour, 20))

c("4/12/2016 12:00:00 AM", "4/12/2016 1:00:00 AM", "4/12/2016 2:00:00 AM", 
"4/12/2016 3:00:00 AM", "4/12/2016 4:00:00 AM", "4/12/2016 5:00:00 AM", 
"4/12/2016 6:00:00 AM", "4/12/2016 7:00:00 AM", "4/12/2016 8:00:00 AM", 
"4/12/2016 9:00:00 AM", "4/12/2016 10:00:00 AM", "4/12/2016 11:00:00 AM", 
"4/12/2016 12:00:00 PM", "4/12/2016 1:00:00 PM", "4/12/2016 2:00:00 PM", 
"4/12/2016 3:00:00 PM", "4/12/2016 4:00:00 PM", "4/12/2016 5:00:00 PM", 
"4/12/2016 6:00:00 PM", "4/12/2016 7:00:00 PM")

Original table with datetime values in chr format

This is the code that I wrote:

hourly_calories$ActivityHour <- as.POSIXct(hourly_calories$ActivityHour, format="%m/%d/%Y %I:%M:%S %p", tz="UTC")

After running this code, I get this result:

dput(head(hourly_calories$ActivityHour, 20)

structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), class = c("POSIXct", "POSIXt"), tzone = "UTC")

Table with NA values after running the code above

Would appreciate any feedback !


Solution

  • what about the convert_to_datetime function in the janitor package?

    hourly_calories$datetime<- convert_to_datetime(hourly_calories$ActivityHour, character_fun = lubridate::dmy_hms)