How to change a selection of a POSIXct vector from YDM to YMD in R?

user_13398064 0
Viewed 192

I have a large dataset with POSIXct datetimes in a YMD_HMS format. I however came to realize that some dates were entered in the database in a YDM_HMS format instead of YMD_HMS. This is visible in the density-plot below. datetimes

The observations run in march and april, but somehow the first of April through the 12th of April have been wrongly added, and are therefore spread across the year. Obviously I want to change tose YDM's into YMD's, but I fail...

A reproducible example is this df:

df <- data.frame(id = c(1:10), datetime = ymd_hms("2020-03-01 00:02:15",
                                                     "2020-03-25 01:03:33",
                                                     "2020-03-14 03:45:55",
                                                     "2020-01-04 05:37:27",
                                                     "2020-01-04 06:23:38",
                                                     "2020-04-12 09:09:43",
                                                     "2020-04-21 12:44:47",
                                                     "2020-02-04 17:38:52",
                                                     "2020-04-24 19:44:21",
                                                     "2020-04-28 21:49:34",
                                                     tz = "UTC"))

I made an extra vector containing the dates, so I can target the faulty observations easily:

df$date <- as.Date(df$datetime)

Then I thought I could easily use an ifelse(). The condition is the selection of dates which are wrong, the TRUE action is to cooerce it into character and then into ydm_hms, the FALSE action is to maintain datetime. This fails...

df <- df %>%
   ifelse(date %in% c("2020-01-04", "2020-02-04"), ydm_hms(as.character(datetime), tz = "UTC"), datetime)
Error in ifelse(., date %in% c("2020-01-04", "2020-02-04"), ydm_hms(as.character(datetime),  : 
  unused argument (datetime)

Adding mutate() like this df <- df %>% mutate(datetime = ifelse(date %in% c("2020-01-04", "2020-02-04"), ydm_hms(as.character(datetime), tz = "UTC"), datetime)) doesn't maintain a POSIXct format for the datetime-vector:

> df
   id   datetime       date
1   1 1583020935 2020-03-01
2   2 1585098213 2020-03-25
3   3 1584157555 2020-03-14
4   4 1578116247 2020-01-04
5   5 1578119018 2020-01-04
6   6 1586682583 2020-04-12
7   7 1587473087 2020-04-21
8   8 1580837932 2020-02-04
9   9 1587757461 2020-04-24
10 10 1588110574 2020-04-28

Question: how do I get this output:

   id            datetime
1   1 2020-03-01 00:02:15
2   2 2020-03-25 01:03:33
3   3 2020-03-14 03:45:55
4   4 2020-04-01 05:37:27
5   5 2020-04-01 06:23:38
6   6 2020-04-12 09:09:43
7   7 2020-04-21 12:44:47
8   8 2020-04-02 17:38:52
9   9 2020-04-24 19:44:21
10 10 2020-04-28 21:49:34
r date
2 Answers

You can get the index for the rows where date is one of c("2020-01-04", "2020-02-04") and change those dates with ydm_hms.

df$date <- as.Date(df$datetime)
inds <- df$date %in% as.Date(c("2020-01-04", "2020-02-04"))
df$datetime[inds] <- lubridate::ydm_hms(df$datetime[inds])

df
#   id            datetime       date
#1   1 2020-03-01 00:02:15 2020-03-01
#2   2 2020-03-25 01:03:33 2020-03-25
#3   3 2020-03-14 03:45:55 2020-03-14
#4   4 2020-04-01 05:37:27 2020-01-04
#5   5 2020-04-01 06:23:38 2020-01-04
#6   6 2020-04-12 09:09:43 2020-04-12
#7   7 2020-04-21 12:44:47 2020-04-21
#8   8 2020-04-02 17:38:52 2020-02-04
#9   9 2020-04-24 19:44:21 2020-04-24
#10 10 2020-04-28 21:49:34 2020-04-28
user_39629140

Instead of ifelse, we can use replace

library(dplyr)
df <- df %>%
    mutate(datetime = replace(datetime, date %in% as.Date(c("2020-01-04", "2020-02-04")),
      ydm_hms(as.character(datetime[date %in% as.Date(c("2020-01-04", "2020-02-04"))]), tz = "UTC") ))

df
#   id            datetime       date
#1   1 2020-03-01 00:02:15 2020-03-01
#2   2 2020-03-25 01:03:33 2020-03-25
#3   3 2020-03-14 03:45:55 2020-03-14
#4   4 2020-04-01 05:37:27 2020-01-04
#5   5 2020-04-01 06:23:38 2020-01-04
#6   6 2020-04-12 09:09:43 2020-04-12
#7   7 2020-04-21 12:44:47 2020-04-21
#8   8 2020-04-02 17:38:52 2020-02-04
#9   9 2020-04-24 19:44:21 2020-04-24
#10 10 2020-04-28 21:49:34 2020-04-28

Or another option with rowwise

df %>% 
   rowwise %>%
   mutate(datetime = if(date %in% 
         as.Date(c("2020-01-04", "2020-02-04"))) ydm_hms(datetime, tz = "UTC") else datetime)

Or with case_when after interchanging the month, day with str_replace

library(stringr)
df %>% 
    mutate(datetime = ymd_hms(case_when(date %in% 
      as.Date(c("2020-01-04", "2020-02-04")) ~ 
           str_replace(datetime, '-(\\d{2})-(\\d{2})', '-\\2-\\1'), TRUE ~ as.character(datetime))))
#  id            datetime       date
#1   1 2020-03-01 00:02:15 2020-03-01
#2   2 2020-03-25 01:03:33 2020-03-25
#3   3 2020-03-14 03:45:55 2020-03-14
#4   4 2020-04-01 05:37:27 2020-01-04
#5   5 2020-04-01 06:23:38 2020-01-04
#6   6 2020-04-12 09:09:43 2020-04-12
#7   7 2020-04-21 12:44:47 2020-04-21
#8   8 2020-04-02 17:38:52 2020-02-04
#9   9 2020-04-24 19:44:21 2020-04-24
#10 10 2020-04-28 21:49:34 2020-04-28

NOTE: All three solutions work

user_37322710
Related