Change date format from YYYYQQ or YYYY to mm/dd/yyyy-CodePudding

I have a column of data with two different formats: yyyyqq and yyyyy. I want to reformat the column to mmddyyyyy.

Whenever I use the following command as.Date(as.character(x), format = "%y") the output is yyyy-12-03. I cannot get any other combination of as.Date to work.

I'm sure this is a simple fix, but how do I do this?

CodePudding user response：

Using the following assumptions:

2021 <- 2021-01-01
2021Q1 <- 2021-01-01
2021Q2 <- 2021-04-01
2021Q3 <- 2021-07-01
2021Q4 <- 2021-10-01

You can use the following:

as.Date(paste(substr(x, 1, 4), 3*as.numeric(max(substr(x, 6, 6),1))-2, "1", sep = "-"))

Edit: You can wrap this in a format(..., "%m%d%Y) but as already said in the comments I would not recommend it.

CodePudding user response：

Here is a function which translates to the first (if frac=0) or last (if frac=1) date of the period. First append a 01 (first of the period) or 04 (last of the period) to the end of the input. That puts them all in yyyyqq format possibly with junk at the end. Then yearqtr will convert to a yearqtr object ignoring any junk. Then convert that to a Date object. as.Date.yearqtr uses the same meaning for frac. Finally format it as a character string in mm/dd/yyyy format.

(One alternative is to replace the format(...) line with chron::as.chron() in which case it will render in the same manner, since the format specified is the default for chron, but be a chron dates object which can be manipulated more conveniently, e.g. it sorts chronologically, than a character string.)

library(zoo)

to_date <- function(x, frac = 1) x |>
  paste0(if (frac == 1) "04" else "01") |>
  as.yearqtr("%Y%q") |>
  as.Date(frac = frac) |>
  format("%m/%d/%Y")

# test data
dd <- data.frame(x = c(2001, 2003, 200202, 200503)) 

transform(dd, first = to_date(x, frac = 0), last = to_date(x, frac = 1))

giving:

       x      first       last
1   2001 01/01/2001 12/31/2001
2   2003 01/01/2003 12/31/2003
3 200202 04/01/2002 06/30/2002
4 200503 07/01/2005 09/30/2005