rformatposixct

Assign date order (mm/dd/yyyy) to a date time character in R

I have loaded datalogger csv files. Here I include a fragment. The date_time column is ordered as month/day/year and hour:min:sec:

date_time<-c("6/5/22 10:22:01 AM", "6/5/22 10:23:01 AM", "6/5/22 10:24:01 AM", "6/5/22 10:25:01 AM", "6/5/22 10:26:01 AM", "6/5/22 10:27:01 AM",
 "6/5/22 10:28:01 AM", "6/5/22 10:29:01 AM", "6/5/22 10:30:01 AM", "6/5/22 10:31:01 AM", "6/5/22 10:32:01 AM", "6/5/22 10:33:01 AM")
temp<-c(21.296, 22.047, 22.798, 22.422, 22.172, 22.673, 22.360, 22.110, 21.922, 21.797, 21.672, 21.609)

df<-data.frame(date_time,temp)
df

         date_time   temp
1  6/5/22 10:22:01 AM 21.296
2  6/5/22 10:23:01 AM 22.047
3  6/5/22 10:24:01 AM 22.798
4  6/5/22 10:25:01 AM 22.422
5  6/5/22 10:26:01 AM 22.172
6  6/5/22 10:27:01 AM 22.673
7  6/5/22 10:28:01 AM 22.360
8  6/5/22 10:29:01 AM 22.110
9  6/5/22 10:30:01 AM 21.922
10 6/5/22 10:31:01 AM 21.797
11 6/5/22 10:32:01 AM 21.672
12 6/5/22 10:33:01 AM 21.609

The date_time column appears as a character,

str(df)
'data.frame':   12 obs. of  2 variables:
 $ date_time: chr  "6/5/22 10:22:01 AM" "6/5/22 10:23:01 AM" "6/5/22 10:24:01 AM" "6/5/22 10:25:01 AM" ...
 $ temp     : num  21.3 22 22.8 22.4 22.2 ...

but I want it to be a posixct variable, so I applied the as.POSIXct function:

df$date_time<-as.POSIXct(df$date_time)


 df
             date_time   temp
1  0006-05-22 10:22:01 21.296
2  0006-05-22 10:23:01 22.047
3  0006-05-22 10:24:01 22.798
4  0006-05-22 10:25:01 22.422
5  0006-05-22 10:26:01 22.172
6  0006-05-22 10:27:01 22.673
7  0006-05-22 10:28:01 22.360
8  0006-05-22 10:29:01 22.110
9  0006-05-22 10:30:01 21.922
10 0006-05-22 10:31:01 21.797
11 0006-05-22 10:32:01 21.672
12 0006-05-22 10:33:01 21.609

However, it puts the days as years and vice-versa. By specifying the format, it does not work:

df$date_time<-as.POSIXct(df$date_time,format="%dd-%mm-%YYYY %H:%M:%S")
df

             date_time   temp
1  0006-05-22 10:22:01 21.296
2  0006-05-22 10:23:01 22.047
3  0006-05-22 10:24:01 22.798
4  0006-05-22 10:25:01 22.422
5  0006-05-22 10:26:01 22.172
6  0006-05-22 10:27:01 22.673
7  0006-05-22 10:28:01 22.360
8  0006-05-22 10:29:01 22.110
9  0006-05-22 10:30:01 21.922
10 0006-05-22 10:31:01 21.797
11 0006-05-22 10:32:01 21.672
12 0006-05-22 10:33:01 21.609

How can assign correctly the order of date (mm/dd/yyyy)? Any advice would be helpful

Thanks in advance!

Solution

  • Two options:

    1- while using as.POSIXct() you didn't use the correct format

    as.POSIXct(df$date_time, format = "%m/%d/%y %H:%M:%S")

    2- Use the lubridate package, it has many useful fuctions to handle date time objects

    ubridate::mdy_hms(df$date_time)