Convert multiple date types in a column to same format

Question

I am converting the variable column into date but only a specific format gets converted while for other I get NA. Is there a way to convert the entire column as date?

my code:
mydata<- mydata %>%
  mutate(mydate=as.Date(variable, format = "%Y.%m.%d"))

mydata

Cat variable    value   mydate
A   5/1/23  0.593406085 <NA>
B   5/2/23  0.53820134  <NA>
C   5/3/23  0.501317568 <NA>
D   5/4/23  0.797812184 <NA>
E   5/5/23  0.444436445 <NA>
G   5/6/23  0.726510674 <NA>
H   5/7/23  0.764232936 <NA>
I   5/8/23  0.659018724 <NA>
J   5/9/23  0.669565494 <NA>
K   5/10/23 0.78342198  <NA>
L   5/11/23 0.437390481 <NA>
A   2023.05.03  0.752062817 5/3/23
B   2023.05.03  0.505276131 5/3/23
C   2023.05.03  0.338695417 5/3/23
D   2023.05.03  0.840394574 5/3/23
E   2023.05.03  0.56846352  5/3/23
G   2023.05.03  0.657647355 5/3/23
H   2023.05.03  0.052378105 5/3/23
I   2023.05.03  0.973788407 5/3/23
J   2023.05.03  0.658600196 5/3/23
K   2023.05.03  0.738847181 5/3/23
L   2023.05.03  0.361230414 5/3/23
A   2023.05.03  0.302676365 5/3/23
B   2023.05.03  0.196976833 5/3/23
C   2023.05.03  0.148042337 5/3/23
D   2023.05.03  0.931336165 5/3/23
E   2023.05.03  0.208053405 5/3/23
G   2023.05.03  0.292979982 5/3/23
H   2023.05.03  0.835556334 5/3/23
I   2023.05.03  0.691765386 5/3/23
J   2023.05.03  0.90168841  5/3/23
K   2023.05.03  0.713476267 5/3/23
L   2023.05.03  0.382385665 5/3/23

See stackoverflow.com/a/52319606/3358272 (dates), stackoverflow.com/a/70304571/3358272 (POSIXct) for examples of how to deal with a variety of data formats. — r2evans, May 03 '23 at 19:13
Possible duplicate https://stackoverflow.com/q/13764514/680068 — zx8754, May 03 '23 at 19:24

score 0 · Answer 1 · answered May 03 '23 at 19:06

Based on the formats showed, it can be done automatically with parse_date

library(dplyr)
library(parsedate)
 mydata %>%
   mutate(mydate = as.Date(parse_date(variable)))

-output

 Cat   variable      value     mydate
1    A     5/1/23 0.59340609 2023-05-01
2    B     5/2/23 0.53820134 2023-05-02
3    C     5/3/23 0.50131757 2023-05-03
4    D     5/4/23 0.79781218 2023-05-04
5    E     5/5/23 0.44443645 2023-05-05
6    G     5/6/23 0.72651067 2023-05-06
7    H     5/7/23 0.76423294 2023-05-07
8    I     5/8/23 0.65901872 2023-05-08
9    J     5/9/23 0.66956549 2023-05-09
10   K    5/10/23 0.78342198 2023-05-10
11   L    5/11/23 0.43739048 2023-05-11
12   A 2023.05.03 0.75206282 2023-05-03
13   B 2023.05.03 0.50527613 2023-05-03
14   C 2023.05.03 0.33869542 2023-05-03
15   D 2023.05.03 0.84039457 2023-05-03
16   E 2023.05.03 0.56846352 2023-05-03
17   G 2023.05.03 0.65764736 2023-05-03
18   H 2023.05.03 0.05237811 2023-05-03
19   I 2023.05.03 0.97378841 2023-05-03
20   J 2023.05.03 0.65860020 2023-05-03
21   K 2023.05.03 0.73884718 2023-05-03
22   L 2023.05.03 0.36123041 2023-05-03
23   A 2023.05.03 0.30267637 2023-05-03
24   B 2023.05.03 0.19697683 2023-05-03
25   C 2023.05.03 0.14804234 2023-05-03
26   D 2023.05.03 0.93133617 2023-05-03
27   E 2023.05.03 0.20805340 2023-05-03
28   G 2023.05.03 0.29297998 2023-05-03
29   H 2023.05.03 0.83555633 2023-05-03
30   I 2023.05.03 0.69176539 2023-05-03
31   J 2023.05.03 0.90168841 2023-05-03
32   K 2023.05.03 0.71347627 2023-05-03
33   L 2023.05.03 0.38238566 2023-05-03

score 0 · Answer 2 · answered May 03 '23 at 19:11

You can try tryFormats with as.Date. rowwise assures that all formats are tested in each row.

library(dplyr)

tibble(variable = c("5/1/23", "2023.05.03")) %>% 
  rowwise() %>% 
  mutate(mydate = as.Date(variable, tryFormats=c("%Y.%m.%d", "%m/%d/%y"))) %>% 
  ungroup()
# A tibble: 2 × 2
  variable   mydate    
  <chr>      <date>    
1 5/1/23     2023-05-01
2 2023.05.03 2023-05-03

Convert multiple date types in a column to same format

2 Answers2