Reading mdy_hms AM/PM off excel into r

Question

I am using dplyr and lubridate.

I am using read_excel to export a data.frame into R from excel. In Excel, I have a column that consists of mdy_hms AM or PM. In R, my code consists of:

df$dateTimeEtc

And this prints out as an example:

"2017-03-07 11:10:37 UTC" "2017-03-22 10:04:42 UTC" "2017-03-08 09:36:49 UTC"

However, I have tried to use:

df <- df %>% 
  mutate(dateTimeEtc = mdy_hms(dateTimeEtc))

So that R recognizes these data points in a mdy_hms (not sure what to do to include the AM/PM) format. However, this line of code converts all my data points into NA. Ideally I'd rather R read these data points as ONLY mdy so I can make comparisons with other mdy data points.

Another goal is to find the max and min data points in this column.

Thanks in Advance

Jon Spring Jon Spring · Accepted Answer · 2018-09-08T00:19:54

I would suggest you keep all your timeline data in a date or datetime format as long as possible, to enable calculations and sorting. In my experience, mdy makes more sense as a formatting choice at the last step, since R does not see mdy data as dates at all, but rather as character strings. So you will get unexpected results if you try to calculate anything in mdy or visualize it.

library(dplyr); library(lubridate)
df = data_frame(dateTimeEtc = c("2017-03-07 11:10:37 UTC", 
                    "2017-03-22 18:04:42 UTC", 
                    "2017-03-08 09:36:49 UTC"))

df
#> # A tibble: 3 x 1
#>   dateTimeEtc            
#>   <chr>                  
#> 1 2017-03-07 11:10:37 UTC
#> 2 2017-03-22 18:04:42 UTC
#> 3 2017-03-08 09:36:49 UTC


# Convert character to datetime POSIXct (if not already that format),
#  then make a character string in mdy format and 12 hour clock. 
df2 <- df %>% 
  mutate(dateTimeEtc2 = ymd_hms(dateTimeEtc),
         as_mdy = format(dateTimeEtc2, '%m/%d/%Y %I:%M:%S %p'))
df2
#> # A tibble: 3 x 3
#>   dateTimeEtc             dateTimeEtc2        as_mdy                
#>   <chr>                   <dttm>              <chr>                 
#> 1 2017-03-07 11:10:37 UTC 2017-03-07 11:10:37 03/07/2017 11:10:37 AM
#> 2 2017-03-22 18:04:42 UTC 2017-03-22 18:04:42 03/22/2017 06:04:42 PM
#> 3 2017-03-08 09:36:49 UTC 2017-03-08 09:36:49 03/08/2017 09:36:49 AM

Reading mdy_hms AM/PM off excel into r

1 Answers