0

I have a data set which has the date time column but the format is muddled up and whats difficult is that the column also has NA values

date_time <- c("11/7/2016 14:11", "12-11-2016 2:00:54", "NA", "12/7/2016 22:03")

I need the NA values to be ignored, the seconds to be inserted and date format to be unified.

Using gsub(pattern ='-', replacement='/', date_test) i am able to fix the date format

When I apply any date time format using as.Date or as.POSIX, it results in NA values as some time is without seconds. The table already has NA values so I lose values.

I need to either round up time to minutes or insert dummy seconds before formatting it as date time it gives me NA values

Thanks

akrun
  • 874,273
  • 37
  • 540
  • 662
Kaps
  • 1

1 Answers1

0

Based on the input, if we have multiple formats, one option is parse_date_time from lubridate

library(lubridate)
parse_date_time(date_time, c("%m/%d/%Y %H:%M:%S", "%m-%d-%y %H:%M"))
#[1] "2016-11-07 14:11:00 UTC" "2016-12-11 02:00:54 UTC"
#[3] NA                        "2016-12-07 22:03:00 UTC"
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thanks but i am still getting some error as i omitted another format value, date_time <- c("11/7/2016 14:11", "12-11-2016 2:00:54", "NA", "12/7/2016 22:03", "14/07/2016 03:15:43", "13-12-2016 5:05"). I used the variation of your suggested solution to parse_date_time and included additional formats but it does not work. – Kaps Jun 05 '18 at 05:55
  • @Kaps It is based on your example – akrun Jun 05 '18 at 05:55
  • I am really thankful for your help, yes it works for the initial example. Please could you help with the revised example i included in my comment above. Why this does not work parse_date_time(date_time, c("%m/%d/%Y %H:%M:%S", "%m-%d-%y %H:%M", "%m/%d/%Y %H:%M", "%m-%d-%Y %H:%M:%S")) – Kaps Jun 06 '18 at 01:24
  • I think the `%m-%d-%y %H:%M` should be `%Y` based on the example – akrun Jun 06 '18 at 02:44