CODEWITHSUNDEEP
rdplyrtidyrlubridatestringr
Ben G
Ben G

Reputation: 4338

Convert a a character column to date with multiple formats

Here's my starting example data from excel (next time, I'm going to teach my client about tidy data): 

date_string  
3/13, 3/17, 3/20  
4/13  
5/12, 5/20

I get pretty close to what I want with this: 

 library(tidyverse)  
 library(stringr)  
    data <- str_split_fixed(data$date_string, ",", 3)%>%  
    as_tibble() %>%  
    gather() %>%
    filter(value != "")

then I'm left with this:

key    value
v1    3/13  
v1    43203  
v1    5/12  
v2    3/17
v2    5/20
v3    3/20

This is good enough that I can do the rest of the formatting and arranging in excel, but the more I can do in R the better, especially as I'll have to do this all again next time I update the final product. I feel like there is a lubridate function to help me with this but mdy and date keep returning errors.

The value I want is the table above but in m/d/y format.

Update

Based on the answer below I added the following. This works, but there's probably a more elegant way to do it:

   data <- str_split_fixed(data$date_string, ",", 3)%>%  
    as_tibble() %>%  
    gather() %>%
    filter(value != "") %>%  
    mutate(value =  
     if_else(
      str_detect(value, "/") == T,
      paste0(value, "/2018"),
      as.character(as_date(as.numeric(value), origin = "1900-01-01")))) %>%        
    mutate(value =
     if_else(
      str_detect(value, "/") == T,
      mdy(value),
      ymd(value)))

I get these warnings, but the data is how I want it: 

1. In as_date(as.numeric(value), origin = "1900-01-01") :  
 NAs introduced by coercion
2. 1 failed to parse.
3. 5 failed to parse.

Not sure how it "failed to parse" when the final "value" column is returned as a date variable . . .

Upvotes: 1

Views: 660

Answers (2)

user5176207
user5176207

Reputation:

Here's a slightly different approach:

library(tidyverse)
library(stringr)
library(lubridate)

data <- c(
"3/13, 3/17, 3/20",
"4/13",
"5/12, 5/20")

df <- tibble(date_string = data) %>% 
  mutate(date_string = str_split(date_string, ", ")) %>% 
  unnest() %>% 
  mutate(date_string = ymd(str_c("2018-", date_string)))
df

Upvotes: 1

byouness
byouness

Reputation: 1782

Here is what you can do:

First, add the year to your dates:

dates <- ("3/13", "4/17", "5/12", "3/17", "5/20", "3/20")
dates <- paste0(dates, "/18")

Second, convert them specifying the format (m/d/y in your case):

  as.Date(dates,  "%m/%d/%y")
  [1] "2018-03-13" "2018-04-17" "2018-05-12" "2018-03-17" "2018-05-20" "2018-03-20"

Upvotes: 1

Related Questions