0

I am working with a dateframe (INPUT) that contains number the of transaction of a product per calendar quarter. The first column (DATE) contains the calendar quarter in this format "2016 Q2". I would like to transform this date into the a financial quarter format such as "2016/17 Q1". The financial year start in the 1st April.

I came up with the following code which does the job, but I was wondering if there is a formula or a neater code that I could use. 

INPUT$FY_Date=character(nrow(INPUT))

for (i in 1:nrow(INPUT)) {
    INPUT$FY_Date[i]= if(substr(INPUT$DATE[i],7,7)==1)  paste(as.numeric(substr(INPUT$DATE[i],1,4))-1,"/",substr(INPUT$DATE[i],3,4)," Q4",sep="") else 

    paste(substr(INPUT$DATE[i],1,4),"/",  formatC(as.numeric(substr(INPUT$DATE[i],3,4))+1,width=2,format="d",flag=0)," Q",as.numeric(substr(INPUT$DATE[i],7,7))-1,sep="")
}

I could not find any previous related posts so I would appreciate any guidance.

nrussell
  • 18,382
  • 4
  • 47
  • 60
FFB
  • 255
  • 2
  • 7
  • Please make sure your examples are complete (including input) and reproducible so anyone else can easily copy it from your question and paste it into their session to run it. Also make sure they are minimal. The data frame is irrelevant here and just distracts from the heart of the question. – G. Grothendieck Apr 12 '17 at 12:15

1 Answers1

2

Using the "yearqtr" class defined in zoo we can do it in two lines of code.

Convert to "yearqtr". The "yearqtr" class uses an internal representation of year + (qtr-1)/4 where qtr is 1, 2, 3 or 4 so adding 3/4 will shift it to the year-end year and fiscal quarter. Then in the final line of code as.integer will extract the year-end year. format function can be used to get the rest where %y means 2 digit year and %q means quarter.

library(zoo)

# test input
yq <- c("2016 Q2", "2016 Q3", "2016 Q4", "2017 Q1")

fyq <- as.yearqtr(yq, format = "%Y Q%q") + 3/4
paste0(as.integer(fyq) - 1, format(fyq, "/%y Q%q"))

giving:

[1] "2016/17 Q1" "2016/17 Q2" "2016/17 Q3" "2016/17 Q4"

Note that if you don't need the specific format shown in the question you could just use format(fyq) in place of the last line or maybe format(fyq, "%Y Q%q").

Update: Minor code improvements.

G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • G. Grothendieck ; I run you code and the following error message appears. Error in UseMethod("quarters") : no applicable method for 'quarters' applied to an object of class "yearqtr" – FFB Apr 12 '17 at 13:11
  • Such an error message could occur, for example, if you forgot to issue the `library(zoo)` command. Make sure that you copy and paste the code in my answer exactly as shown into your session and that you are using the latest version of zoo on CRAN. The line after "giving" is exactly what R output when I ran it. – G. Grothendieck Apr 12 '17 at 13:18
  • do you know which format I should use to have the answer in my question? "2016/17 Q1" "2016/17 Q2" "2016/17 Q3" "2016/17 Q4" – FFB Apr 12 '17 at 13:33
  • I may not be as need as your but I have changed your code to by pass the error message and the formatting issue sprintf("%d/%s %s",as.integer(fyq) - 1, substr(as.integer(fyq),3,4), substr(fyq,6,8)) – FFB Apr 12 '17 at 13:43
  • I have modified the last line so that the output is exactly as in the question. `substr` should not be needed. – G. Grothendieck Apr 12 '17 at 13:50
  • Have made additional improvement which shortens the last line. – G. Grothendieck Apr 12 '17 at 14:18
  • That works better than the previous code! and it bypasses the problem that I was having with the function quarters(). Ta – FFB Apr 12 '17 at 14:28