Convert character month name to date time object

Question

I must be missing something simple.

I have a data.frame of various date formats and I'm using lubridate which works great with everything except month names by themselves. I can't get the month names to convert to date time objects.

> head(dates)
    From         To
1       June     August
2    January   December
3 05/01/2013 10/30/2013
4       July   November
5 06/17/2013 10/14/2013
6 05/04/2013 11/23/2013

Trying to change June into date time object:

> as_date(dates[1,1])
Error in charToDate(x) : 
  character string is not in a standard unambiguous format

> as_date("June")
Error in charToDate(x) : 
  character string is not in a standard unambiguous format

• The actual year and day do not matter. I only need the month. zx8754 suggested using dummy day and year.

Solution 1

Using a custom function:

# dummy data
df1 <- read.table(text = "
From         To
1       June     August
2    January   December
3 05/01/2013 10/30/2013
4       July   November
5 06/17/2013 10/14/2013
6 05/04/2013 11/23/2013", header = TRUE, as.is = TRUE)

# custom function
myFun <- function(x, dummyDay = "01", dummyYear = "2013"){
  require(lubridate)

  x <- ifelse(substr(x, 1, 3) %in% month.abb,
              paste(match(substr(x, 1, 3), month.abb),
                    dummyDay,
                    dummyYear, sep = "/"), x)
  #return date
  mdy(x)
}

res <- data.frame(lapply(df1, myFun))

res
#         From         To
# 1 2013-06-01 2013-08-01
# 2 2013-01-01 2013-12-01
# 3 2013-05-01 2013-10-30
# 4 2013-07-01 2013-11-01
# 5 2013-06-17 2013-10-14
# 6 2013-05-04 2013-11-23

Solution 2

The following is a crude example of how you could achieve that.

Given that dummy values are fine:

match(dates[1, 1], month.abb)

The above would return you, given that we had Dec in dates[1. 1]:

12

To generate the returned value above along with dummy number in a date format, I tried:

tmp = paste(match(dates[1, 1], month.abb), "2013", sep="/")

which gives us:

12/2013

and then lastly:

result = paste("01", tmp, sep="/")

which returns:

01/12/2013

I am sure there are more flexible approaches than this; but this is just an idea, which I just tried.

Solution 3

lubridate can handle converting the name or abbreviation of a month to its number when it's paired with the rest of the information needed to make a proper date, i.e. a day and year. For example:

lubridate::mdy("August/01/2013", "08/01/2013", "Aug/01/2013")
#> [1] "2013-08-01" "2013-08-01" "2013-08-01"

You can utilize that to write a function that appends "/01/2013" to any month names (I threw in abbreviations as well to be safe). Then apply that to all your date columns (dplyr::mutate_all is just one way to do that).

name_to_date <- function(x) {
  lubridate::mdy(ifelse(x %in% c(month.name, month.abb), paste0(x, "/01/2013"), x))
}

dplyr::mutate_all(dates, name_to_date)
#>         From         To
#> 1 2013-06-01 2013-08-01
#> 2 2013-01-01 2013-12-01
#> 3 2013-05-01 2013-10-30
#> 4 2013-07-01 2013-11-01
#> 5 2013-06-17 2013-10-14
#> 6 2013-05-04 2013-11-23

Comments

[David Arenburg]

Nice function. I think you could just do df1[] <- lapply(df1, myFun) in order to avoid duplicating data sets. I'm also wondering what meaning these dates have and how OP is going to differ between a real June first and some dummy one. Also, what happens if From will have November and To will have August? If you ask me, I would just close this whole thing as "unclear"

[zx8754]

@DavidArenburg OP can use the function however they wish, I prefer to keep raw input as raw.

[David Arenburg]

Have no idea what keep raw input as raw means, but ok I guess

[zx8754]

Hehe, you have just broke down my function into pieces and gave a great explanation.

[Unheilig]

@zx8754 Thanks; your method is definitely more flexible than mine. :-)

[Chris]

@zx8754 This is a little off topic, but why do you use substr(x,1,3). I don't understand why 1 and 3 evaluates to True. Why does 1 and 2 and it evaluate it False?

[zx8754]

@Chris because month.abb gives 3 letter month names, like "Jan, Feb, Mar,..., Dec". So I am saying if first 3 letters match month.abb names then convert to number using match.