How To Create a Use Apply (or create a function) To Turn Character Dates Into Dates In R Across Multiple Columns of Dates

user_12975537 0
Viewed 156

So I have an excel sheet full of dates that are in the character form. I can't actually use mdy() or as.Date() to convert the original. I've created a method to convert the dates in one column and I think I need to use the apply() or sapply() function to do convert the rest of the dates in the other columns. Only problem is I don't know how to do that.

While simply using mdy() or as.Date() it will work on the fake data I created it WILL NOT work on my original data. All it spits out are NAs. I can't reproduce what I have been given on the excel sheet perfectly but below I've created some mock data. All I want to do is apply the method to all several columns of my dataframe full of dates.

As of now, my method is that I have been able to separate the character dates into three separate columns and then converted those into dates. I practiced on one column now I need to apply that to the rest of my columns.

Below is an abbreviated version of my data with made-up dates at random and renamed columns

mock_data <- data.frame(
  Death = (c("Jan 23, 2019", "Feb 23, 1998", "June 3, 2003", "Oct 7, 2007", "Feb 28, 2004", "Apr 19, 2014", "Mar 11, 1988", "Sept 30, 2011")),
  Birth = c("May 11, 2010", "Apr 9, 1999", "Aug 30, 1998", "Jan 08, 2003", "Feb 18, 2001", "Nov 25, 2000", "Oct 31, 2009", "Dec 11, 2011"),
  Wedding = c("June 01, 1981", "May 24, 2018", "Feb 25, 2017", "Dec 25, 2011", "Aug 14, 1967", "July 2, 2003", "Nov 30, 2000", "Feb 2, 2002")
  )

This is my four-step code to convert the data and put it into a new data frame

death_data <- data.frame(
  Death_Month = separate(
    separate(mock_data, col = "Death", into = c("Day_Month", "Year"), sep = "\\,"),
    col = "Day_Month",
    into = c("Month", "Day"),
    sep = " ")$Month,
  Death_Day = as.numeric(separate(
    separate(mock_data, col = "Death", into = c("Day_Month", "Year"), sep = "\\,"),
    col = "Day_Month",
    into = c("Montth", "Day"),
    sep = " ")$Day),
  Death_Year = as.numeric(separate(mock_data, col = "Death", into = c("Day_Month", "Year"), sep = "\\,")$Year)
)

death_data$Death_Date <- paste(death_data$Death_Year, death_data$Death_Month, death_data$Death_Day, sep="-") %>% ymd() %>% as.Date()

dates_data <- data.frame(Death = death_data$Death_Date)

dates_data

The final plan will be to cbind() the columns to the other informational columns that are not dates I need from the original dataframe. It's probably not the most efficient or elegant code, but it's the only way I could conceive to get it done. My method works for one columns and this code isn't getting passed on to anyone else.

sapply apply r date function
2 Answers

We could use across in mutate to convert multiple columns. Here, we need to convert all the columns to Date class - and it is easier with mdy from lubridate

library(dplyr)
library(lubridate)
mock_data_out <-  mock_data %>%
      mutate(across(everything(), mdy))
mock_data_out
#      Death      Birth    Wedding
#1 2019-01-23 2010-05-11 1981-06-01
#2 1998-02-23 1999-04-09 2018-05-24
#3 2003-06-03 1998-08-30 2017-02-25
#4 2007-10-07 2003-01-08 2011-12-25
#5 2004-02-28 2001-02-18 1967-08-14
#6 2014-04-19 2000-11-25 2003-07-02
#7 1988-03-11 2009-10-31 2000-11-30
#8 2011-09-30 2011-12-11 2002-02-02

Or in base R with lapply and as.Date

mock_data[] <- lapply(mock_data, as.Date, format = "%b %d, %Y")
user_37322710

A solution using data.table and its IDate format.

library(data.table)
# I modified a little mock_data to change "Sept" to "Sep" so R will recognize it as September

mock_data <- data.frame(
  Death = (c("Jan 23, 2019", "Feb 23, 1998", "June 3, 2003", "Oct 7, 2007", "Feb 28, 2004", "Apr 19, 2014", "Mar 11, 1988", "Sep 30, 2011")),
  Birth = c("May 11, 2010", "Apr 9, 1999", "Aug 30, 1998", "Jan 08, 2003", "Feb 18, 2001", "Nov 25, 2000", "Oct 31, 2009", "Dec 11, 2011"),
  Wedding = c("June 01, 1981", "May 24, 2018", "Feb 25, 2017", "Dec 25, 2011", "Aug 14, 1967", "July 2, 2003", "Nov 30, 2000", "Feb 2, 2002")
)

setDT(mock_data) # converting into a data.table object
#Now lapply-ing and transforming to data.table IDate format
mock_data[,lapply(.SD, function(x) as.IDate(x, format = "%b %d, %Y"))] #.SD is special symbol representing all columns in mock_data 

#output
mock_data
        Death      Birth    Wedding
1: 2019-01-23 2010-05-11 1981-06-01
2: 1998-02-23 1999-04-09 2018-05-24
3: 2003-06-03 1998-08-30 2017-02-25
4: 2007-10-07 2003-01-08 2011-12-25
5: 2004-02-28 2001-02-18 1967-08-14
6: 2014-04-19 2000-11-25 2003-07-02
7: 1988-03-11 2009-10-31 2000-11-30
8: 2011-09-30 2011-12-11 2002-02-02
user_43633150
Related