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.
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")