0

I'm trying to convert characters to dates in R.

My data as the following format:

df <- data.frame(Date = c("23Jul2019 11:51:09 AM","23Jul2019 11:53:09 AM","19Jul2019 2:30:06 PM","01Aug2019 3:00:17 PM"))

Based on the solution found here: Convert character to Date in R

I could use

> as.Date(df$Date, "%d/%b/%Y %I:%M:%S %p") 
[1] NA NA NA NA

%I is for decimal hour (12h) and %p Locale-specific AM/PM (https://www.stat.berkeley.edu/~s133/dates.html) but for some reason, it returns NAs.

My goal is to sort the rows of a dataframe by date and time once dates in the character format converted to Dates in R.

What is the issue with the code I'm using?

ACLAN
  • 401
  • 3
  • 9
  • Try this: `anytime::anydate(df$Date)` – sm925 Jan 14 '20 at 15:32
  • It works to get the dates but not the time – ACLAN Jan 14 '20 at 15:34
  • 3
    Your format needs to match what you actually have in your data. Your dates aren't written with `"%d/%b/%Y"`, they're written with `"%d%b%Y"` – camille Jan 14 '20 at 15:40
  • Also, `as.Date` is for dates, not date-times. You want a POSIX, not a Date object – camille Jan 14 '20 at 15:42
  • 1
    Try `as.POSIXct(df$Date, format = "%d%b%Y %H:%M:%S %p")` You had a couple of errors in your format. The separating slashes and you defined hours as `%I` instead `%H` – Sotos Jan 14 '20 at 15:44
  • Using the as.POSIXct(df$Date, format = "%d%b%Y %H:%M:%S %p") gives "2019-07-23 11:51:09 CEST" "2019-07-23 11:53:09 CEST" "2019-07-19 02:30:06 CEST" "2019-08-01 03:00:17 CEST" which does not allow me to distinguished between am and pm. There is why I used %I instead of %H – ACLAN Jan 14 '20 at 15:53
  • But the code works. Thanks – ACLAN Jan 14 '20 at 16:12
  • AFAIK Posix objects are going to put time in terms of 24 hours. I think you're confusing the date-time object and the way it's printed; you can have the information encoded as out of 24 hours (no am/pm), but then format it as a string out of 12 hours (with am/pm) – camille Jan 14 '20 at 17:38

1 Answers1

1

This should solve it

library(tidyverse)
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following object is masked from 'package:base':
#> 
#>     date
df <- data.frame(Date = c("23Jul2019 11:51:09 AM","23Jul2019 11:53:09 AM","19Jul2019 2:30:06 PM","01Aug2019 3:00:17 PM"))

df %>% 
  mutate(r_date_time = Date %>% dmy_hms)
#>                    Date         r_date_time
#> 1 23Jul2019 11:51:09 AM 2019-07-23 11:51:09
#> 2 23Jul2019 11:53:09 AM 2019-07-23 11:53:09
#> 3  19Jul2019 2:30:06 PM 2019-07-19 14:30:06
#> 4  01Aug2019 3:00:17 PM 2019-08-01 15:00:17

dmy_hms(df$Date) 
#> [1] "2019-07-23 11:51:09 UTC" "2019-07-23 11:53:09 UTC"
#> [3] "2019-07-19 14:30:06 UTC" "2019-08-01 15:00:17 UTC"

Created on 2020-01-14 by the reprex package (v0.3.0)

Bruno
  • 4,109
  • 1
  • 9
  • 27
  • unfortunately I can't use the function: because I can't install and use the tidyverse > library(tidyverse) Error: package or namespace load failed for ‘tidyverse’ in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]): namespace ‘vctrs’ 0.2.0 is already loaded, but >= 0.2.1 is required > df %>% mutate(r_date_time = Date %>% dmy_hms) Error in df %>% mutate(r_date_time = Date %>% dmy_hms) : could not find function "%>%" – ACLAN Jan 14 '20 at 15:59
  • just lubridate will sove your core problem – Bruno Jan 14 '20 at 16:16