After uploading datetime data from a csv file, I can see the datetime with am/pm but it is in a character format and thus cannot add a regression line. When trying to convert to an integer using stprtime I create a new column that is now an integer, however it has lost the am/pm information. How can I retain this information?
Sample data "wpplot":
Date Irrigation Rep ID WP
6/29/17 12:40 PM Reduced 1 11B -14.3
6/29/17 12:50 PM Reduced 1 11B -14.4
6/29/17 1:00 AM Reduced 1 11B -14.5
6/29/17 1:10 AM Reduced 1 11B -14.5
6/29/17 1:20 AM Reduced 1 11B -14.5
6/29/17 1:30 AM Reduced 1 11B -14.5
6/29/17 1:40 AM Reduced 1 11B -14.5
6/29/17 1:50 AM Reduced 1 11B -14.5
6/29/17 2:00 AM Reduced 1 11B -14.5
6/29/17 2:10 AM Reduced 1 11B -14.5
6/29/17 2:20 AM Reduced 1 11B -14.5
6/29/17 2:30 AM Reduced 1 11B -14.5
Code:
attach(wpplot)
wpplot$datefinish<-strptime(wpplot$Date, format = "%m/%d/%y %H:%M %p")
UPDATE:
In using the strptime I am not able to discern between am/pm. The locale is set to English_United States (OS is Windows).
> Sys.getlocale("LC_TIME")
[1] "English_United States.1252"
>
> Sys.setlocale("LC_TIME", "English")
[1] "English_United States.1252"
wpplot$datefinish<-strptime(wpplot$Date, format = "%m/%d/%y %I:%M %p")
> datefinish
[1] "2017-06-29 12:40:00 PDT" "2017-06-29 12:50:00 PDT"
"2017-06-29 01:00:00 PDT" "2017-06-29 01:10:00 PDT"
The am/pm format only works when it is written in lower-case, not in AM/PM like in your example. Also note that I changed %H to %I as indicated in ?strptime.
The below did the trick for me.
wpplot$Date <- tolower(wpplot$Date)
wpplot$datefinish <- strptime(wpplot$Date, format = "%m/%d/%y %I:%M %p")
In the documentation, however, it also states that the AM/PM behaviour is locale-specific:
Locale-specific conversions to and from character strings are used where appropriate and available. This affects the names of the days and months, the AM/PM indicator (if used) and the separators in formats such as %x and %X, via the setting of the LC_TIME locale category.
We can change the times a bit in order to be sure we understand the am/pm concept correctly (is 12:40 AM in the afternoon or just past midnight?).
wpplot$Date[1] <- "6/29/17 02:40 pm" # This is definitely 14:40 29th of June
# CORRECT:
strptime(wpplot$Date, format = "%m/%d/%y %I:%M %p")
# [1] "2017-06-29 14:40:00 CEST" "2017-06-29 12:50:00 CEST"
# [3] "2017-06-29 01:00:00 CEST" "2017-06-29 01:10:00 CEST"
# [5] "2017-06-29 01:20:00 CEST"
Note that I used format = "%m/%d/%y %I:%M %p" and not format = "%m/%d/%y %H:%M %p":
# WRONG
strptime(wpplot$Date, format = "%m/%d/%y %H:%M %p")
# [1] "2017-06-29 02:40:00 CEST" "2017-06-29 12:50:00 CEST"
# [3] "2017-06-29 01:00:00 CEST" "2017-06-29 01:10:00 CEST"
# [5] "2017-06-29 01:20:00 CEST"