Find date columns in R. Date column have different date-formats. Replace all date format to dd-mmm-yyyy

Question

Creating a function that identifies only date columns in a data frame. And change their format to DD-MMM_YYYY(01-Jan-2020)

 data <- data.frame(Name = c("A","B","C"),
                        Col2 = c("01-01-2020","01-Feb-2020","31/05/2020"),
                col3 = c("05-Feb-2020","13/April/2020","2020-09-17"), 
                        Col4 = c("XYZ","REW","RRRR"),
                        col5 = c("01-Mar-2020","01/01/2019","2020-01-12")) 

func <- function(data) { }

Expected output

enter image description here

NOTE : The function needs to identify which columns are date and change its format to yyyy-mm-dd

Please always ensure your data is copy pastable. – NelsonGon Apr 28 '20 at 16:40 — NelsonGon, Apr 28 '20 at 16:40

NelsonGon · Accepted Answer · 2020-04-28T17:37:28.060

2

Using the tidyverse(see Note):

    date_parser <- function(column){
   if(any(grepl("[-]",column))){

 format(parse_date_time(column,orders = c("dmy","dby","ymd")),"%d-%b-%Y")
     }

   else column
 }
   df %>% 
   mutate_at(-1,~ stringr::str_replace_all(.,"\\/","-")) %>% 
  purrr::map_dfr(~date_parser(.))
# A tibble: 3 x 5
  Name  Col2        col3        Col4  col5       
  <chr> <chr>       <chr>       <chr> <chr>      
1 A     01-Jan-2020 05-Feb-2020 XYZ   01-Mar-2020
2 B     01-Feb-2020 13-Apr-2020 REW   01-Jan-2019
3 C     31-May-2020 17-Sep-2020 RRRR  12-Jan-2020

NOTE:

This is not an actual date, you can convert it again with lubridate although I doubt dttm supports mixed dates. Hence as far as I know, you'll still resort to characters.

edited Apr 28 '20 at 17:37

answered Apr 28 '20 at 17:18

NelsonGon

13,015
7
27
57

The result should be in 'dd-mmm-yyyy' format. (eg 01-Jan-2020) – Questions Ask Apr 28 '20 at 17:28
1

It Worked! Thanks – Questions Ask Apr 28 '20 at 18:05
When the input file is in '.csv' format, the above code works fine. However, when the input file is in '.xlsx' format the above code produces NA inside of dates. Any Idea? – Questions Ask May 01 '20 at 08:29
It might be hard for me to tell. Which function are you using to read in the data and what does the data look like? – NelsonGon May 01 '20 at 08:46
That got resolved! Thanks. I have some other columns in my dataset that contain "-" symbol. So now those are also getting affected by the above function – Questions Ask May 01 '20 at 15:19
Could you ask a new question with that data and link it to this and explain the issue there?! It's kinda hard for me to know all possible scenarios without the data. – NelsonGon May 01 '20 at 15:47
https://stackoverflow.com/questions/61546592/error-in-replacing-data-in-r-by-identifying-date-columns – Questions Ask May 01 '20 at 16:31
I have asked here – Questions Ask May 01 '20 at 16:31

Find date columns in R. Date column have different date-formats. Replace all date format to dd-mmm-yyyy

1 Answers1