Convert character column to date column in lubridate

Question

I have a dataframe looking like:

# A tibble: 10 x 4
      id incoming_date expiry_date end_date
   <dbl> <chr>         <chr>       <chr>   
 1     1 11.17.18      10.1.19     03.1.19 
 2    11 03.4.19       NA          03.20.19
 3     2 03.17.19      02.1.20     05.7.19 
 4     2 05.7.19       NA          06.15.19
 5     4 06.11.19      05.1.21     06.22.19
 6     1 06.12.19      04.1.21     NA      
 7     2 06.12.19      04.1.21     NA      
 8    13 11.16.18      06.1.19     02.20.19
 9     7 02.19.19      12.1.21     3.23.19 
10     1 03.19.19      01.1.21     09.10.19

reproducible:

library(tidyverse)
library(lubridate) 

df <-  as.tibble(structure(list(id = c(1, 11, 2, 2, 4, 1, 2, 13, 7, 1), incoming_date = c("11.17.18", 
    "03.4.19", "03.17.19", "05.7.19", "06.11.19", "06.12.19", "06.12.19", 
    "11.16.18", "02.19.19", "03.19.19"), expiry_date = c("10.1.19", 
    NA, "02.1.20", NA, "05.1.21", "04.1.21", "04.1.21", "06.1.19", 
    "12.1.21", "01.1.21"), end_date = c("03.1.19", "03.20.19", "05.7.19", 
    "06.15.19", "06.22.19", NA, NA, "02.20.19", "3.23.19", "09.10.19"
    )), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"
    )))

I want to convert the columns from character to date format, as follows:

      id incoming_date expiry_date end_date  
   <dbl> <date>        <date>      <date>    
 1     1 2018-11-17    2019-10-01  2019-03-01
 2    11 2019-03-04    NA          2019-03-20
 3     2 2019-03-17    2020-02-01  2019-05-07
 4     2 2019-05-07    NA          2019-06-15
 5     4 2019-06-11    2021-05-01  2019-06-22
 6     1 2019-06-12    2021-04-01  NA        
 7     2 2019-06-12    2021-04-01  NA        
 8    13 2018-11-16    2019-06-01  2019-02-20
 9     7 2019-02-19    2021-12-01  2019-03-23
10     1 2019-03-19    2021-01-01  2019-09-10

What I tried to do - starting from the incoming_date column - is:

df %>%
  mutate(incoming_date_parsed = strptime(as.POSIXct(incoming_date), "%m.%d.%Y"))

throwing the error:

Error in as.POSIXlt.character(x, tz, ...) : 
  character string is not in a standard unambiguous format

In logical order, what I would do is:

Convert the column from character to datetime
Parse the date following in the format YYYY-MM-dd

score 0 · Answer 1 · answered Oct 29 '19 at 10:46

You're really close! There's no reason to try to convert to posxict first and then date in your example. And you would use a lower case 'y' for YY format years.



df <- df %>%
        mutate(incoming_date_parsed = as.Date(incoming_date, "%m.%d.%y"))


> head(df$incoming_date_parsed)
[1] "2018-11-17" "2019-03-04" "2019-03-17" "2019-05-07" "2019-06-11" "2019-06-12"

Convert character column to date column in lubridate

1 Answers1