How to identify columns by pattern and convert it to datetime using R?

user_10829044 0
Viewed 198

I have a data frame called data like as shown below

 structure(list(NRIC_ID = c(1234L, 4567L, 1234L, 3578L, 2468L), 
    ADMIT_DATE = structure(c(2L, 3L, 4L, 5L, 1L), .Label = c("11/11/2011", 
    "2/12/2016", "3/11/2019", "5/7/2018", "7/7/2014"), class = "factor"), 
    test_date = structure(c(3L, 5L, 2L, 1L, 4L), .Label = c(" 2014-17-11", 
    "10/8/2013", "11/2/2012", "12/12/2012", "12/2/2014"), class = "factor"), 
    test2_DATE = structure(c(2L, 3L, 4L, 5L, 1L), .Label = c("11/11/2011", 
    "2/12/2016", "3/11/2019", "5/7/2018", "7/7/2014"), class = "factor")), class = "data.frame", row.names = c(NA, 
-5L))

Currently when I get the datatypes of the columns using the below code

(sapply(data, class))

I get the output as Integer, Date, and character.

Now, I would like to identify all the columns that contain DATE or date in their column name using pattern matching and convert them to the Date data type

I tried the below code for pattern matching

data %>%
  select(contains("date")) %>%  #how to ignore case here
  as.Date()

When I do this, I get the below error

Error in as.Date.default: do not know how to convert '.' to class "Date"

can help me to fix this?

tibble tidyr dplyr dataframe r
3 Answers

Use mutate if you want to operate on the columns.

library(dplyr)
df %>% 
  mutate(across(contains('date', ignore.case = TRUE), 
          lubridate::parse_date_time, c('dmY', 'mdY', 'Ydm')))

#  NRIC_ID ADMIT_DATE  test_date test2_DATE
#1    1234 2016-12-02 2012-02-11 2016-12-02
#2    4567 2019-11-03 2014-02-12 2019-11-03
#3    1234 2018-07-05 2013-08-10 2018-07-05
#4    3578 2014-07-07 2014-11-17 2014-07-07
#5    2468 2011-11-11 2012-12-12 2011-11-11
user_39629140

using data.table and solution @Ronak Shah

library(data.table)
library(lubridate)

dt <- as.data.table(df)
cols_date <- grep(pattern = "date", x = names(dt), ignore.case = T, value = T)
dt[, lapply(.SD, parse_date_time, c('dmY', 'mdY', 'Ydm')), .SDcols = cols_date]

#>    ADMIT_DATE  test_date test2_DATE
#> 1: 2016-02-12 2012-02-11 2016-12-02
#> 2: 2019-03-11 2014-02-12 2019-11-03
#> 3: 2018-05-07 2013-08-10 2018-07-05
#> 4: 2014-07-07 2014-11-17 2014-07-07
#> 5: 2011-11-11 2012-12-12 2011-11-11

Created on 2020-12-23 by the reprex package (v0.3.0)

user_120254830

We can use anydate from anytime

library(dplyr)
library(anytime)
addFormats("%Y-%d-%m")
df %>%
    mutate(across(ends_with('DATE'), anydate))

-output

#   NRIC_ID ADMIT_DATE  test_date test2_DATE
#1    1234 2016-02-12 2012-11-02 2016-02-12
#2    4567 2019-03-11 2014-12-02 2019-03-11
#3    1234 2018-05-07 2013-10-08 2018-05-07
#4    3578 2014-07-07 2014-11-17 2014-07-07
#5    2468 2011-11-11 2012-12-12 2011-11-11
user_37322710
Related