Parse "m/%d/%Y %H:%M" form of datetime in R

Question

Given a dataframe as follows:

df <- structure(list(time = structure(1:10, .Label = c("1/1/2001 0:00", 
"1/1/2001 1:00", "1/1/2001 2:00", "1/1/2001 3:00", "1/1/2001 4:00", 
"1/1/2001 5:00", "1/1/2001 6:00", "1/1/2001 7:00", "1/1/2001 8:00", 
"1/1/2001 9:00"), class = "factor"), values = c(19.94, 19.94, 
12.92, 15.08, 12.02, 12.02, 12.02, 12.92, 12.92, 8.96)), class = "data.frame", row.names = c(NA, 
-10L))

For time column, it represent: m/%d/%Y %H:%M, but when I parse them with following code, both of code return time to NA.

df$time <- strptime(df$time, format="m/%d/%Y %H:%M")
df$time <- as.Date(df$time, format="%m/%d/%Y %H:%M")
df$time

Out:

[1] NA NA NA NA NA NA NA NA NA NA NA

Finally, I need to split time column into columns of year, month, day, hour and minutes.

Someone could help? Thanks.

score 3 · Accepted Answer · answered Jul 10 '20 at 11:33

3

You just forgot a % in the beginning of your format string, this should work:

strptime(df$time, format="%m/%d/%Y %H:%M")

answered Jul 10 '20 at 11:33

snaut

2,261
18
37

Thanks, any ideas how to split week of day from it? – ah bon Jul 10 '20 at 11:35
1

Either use Format with just `"%a"` as format or use the `wday` function from the `lubridate` package. – snaut Jul 20 '20 at 15:52

Ronak Shah · Answer 2 · 2020-07-10T14:21:28.000

There are functions in lubridate which allows to extract the values you want from POSIXct objects.

library(dplyr)
library(lubridate)

df %>%
  mutate(time = mdy_hm(time), 
         year = year(time), 
         month = month(time), 
         day = day(time), 
         hour = hour(time), 
         minute = minute(time), 
         weekday = wday(time))


#                  time values year month day hour minute weekday
#1  2001-01-01 00:00:00  19.94 2001     1   1    0      0       2
#2  2001-01-01 01:00:00  19.94 2001     1   1    1      0       2
#3  2001-01-01 02:00:00  12.92 2001     1   1    2      0       2
#4  2001-01-01 03:00:00  15.08 2001     1   1    3      0       2
#5  2001-01-01 04:00:00  12.02 2001     1   1    4      0       2
#6  2001-01-01 05:00:00  12.02 2001     1   1    5      0       2
#7  2001-01-01 06:00:00  12.02 2001     1   1    6      0       2
#8  2001-01-01 07:00:00  12.92 2001     1   1    7      0       2
#9  2001-01-01 08:00:00  12.92 2001     1   1    8      0       2
#10 2001-01-01 09:00:00   8.96 2001     1   1    9      0       2

@ahbon You can use `wday` function for it. See updated answer. — Ronak Shah, Jul 10 '20 at 14:21

score 0 · Answer 3 · answered Jul 10 '20 at 11:25

0

The following code works:

df$time <- lubridate::mdy_hm(df$time)

df %>%
  separate(time, into = c('year', 'month', 'day', 'hour'))

answered Jul 10 '20 at 11:25

ah bon

9,293
12
65
148

Parse "m/%d/%Y %H:%M" form of datetime in R

3 Answers3