Format date as Year/Quarter

Question

I have the following dataframe:

Data <- data.frame(
  date = c("2001-01-01", "2001-02-01", "2001-03-01", "2001-04-01", "2001-05-01", "2001-06-01"),
  qtr = c("NA", "NA","NA","NA","NA","NA")
)

I want to fill Data$qtr with Year/Quater - f.e. 01/01 (I need this format!).

I wrote a function:

fun <- function(x) { 
  if(x == "2001-01-01" | x == "2001-02-01" | x == "2001-03-01") y <- "01/01"
  if(x == "2001-04-01" | x == "2001-05-01" | x == "2001-06-01") y <- "01/02"
  return(y)
}
n$qtr <- sapply(n$date, fun)

But it does not work. I always get the error message:

Error in FUN(X[[1L]], ...) : Object 'y' not found

Why?

Answer 1

I have been loving the lubridate package for working with dates. Super slick. The quarter function finds the quarter (of course) and then just pair that with the year.

library(lubridate)
Data <- Data %>%
  mutate(qtr = paste0(substring(year(date),2,4),"/0",quarter(date))) 

If you are not familiar with the %>% the first line basically says "use data frame called data" and the second line says "mutate (or add) a column called qtr"

Answer 2

yq=function(x,prefix="%Y",combine="Q") paste0(ifelse(is.null(prefix),"",format(x,"%Y")),floor(as.numeric(format(x,"%m"))/3-1e-3)+1,sep=combine)

this gives the flexibility of returning any format back that has the quarter in it

no need for chron or zoo

as for your example

yq(as.Date("2013-04-30"),prefix="%y",combine="/0")
> [1] "13/02"

Answer 3

I made a similar format using quarters() and sub() in R:

  Data$qtr <- paste(format(Data$date, "%y/"), 0, 
              sub( "Q", "", quarters(Data$date) ), sep = "")

Hope this helps!

Answer 4

Another (longer) way of doing it using if statements is this:

month <- as.numeric(format(date, format = "%m"))[1]

if (month < 4) {
    quarter <- paste( format(date, format = "%Y")[1], "Q1", sep="-")
} else if (month > 3 & month < 7) {
    quarter <- paste( format(date, format = "%Y")[1], "Q2", sep="-")            
} else if (month > 6 & month < 10) {
    quarter <- paste( format(date, format = "%Y")[1], "Q3", sep="-")
} else if (month > 9) {
    quarter <- paste( format(date, format = "%Y")[1], "Q4", sep="-")
}

Returns a string in the format:

> quarter
[1] "2001-Q1"

Then you could extend that using a loop.

Answer 5

Using base functions:

Data$date <- as.Date(Data$date)
Data$qtr <- paste(format(Data$date, "%y"), 
                  sprintf("%02i", (as.POSIXlt(Data$date)$mon) %/% 3L + 1L), 
                  sep="/")

#         date   qtr
# 1 2001-01-01 01/01
# 2 2001-02-01 01/01
# 3 2001-03-01 01/01
# 4 2001-04-01 01/02
# 5 2001-05-01 01/02
# 6 2001-06-01 01/02

Answer 6

You need to explicilty Vectorize your function:

fun_v <- Vectorize(fun, "x")
fun_v(Data$date)
#[1] "01/01" "01/01" "01/01" "01/02" "01/02" "01/02"

However, when it comes to more or less standard tasks (such as datetime manipulations), there's always a solution already available:

library(zoo)
yq <- as.yearqtr(Data$date, format = "%Y-%m-%d")
yq
#[1] "2001 Q1" "2001 Q1" "2001 Q1" "2001 Q2" "2001 Q2" "2001 Q2"

To convert to your specific format, use

format(yq, format = "%y/0%q")
#[1] "01/01" "01/01" "01/01" "01/02" "01/02" "01/02"