Most effective way to group data in quarters and fiscal years in R

user_8879634 0
Viewed 7357

I have a large database (POY) with data from 2011 to 2017 which contains a date column. I would need to do two things: make it possible to split by quarters and by fiscal year.
Our fiscal year unfortunately does not run in parallel with calendar years but goes from July to June. Which also means that my Quarter 1 runs from July to September.

I've written some code that seems to work fine but it seems rather lengthy (especially the second part). Does anyone have any advice for this beginner to make it more efficient?

    #Copy of date column and splitting it in 3 columns for year, month and day
    library(tidyr)
    POY$Date2 <- POY$Date
    POY<-separate(POY, Date2, c("year","month","day"), sep = "-", convert=TRUE)


    #Making a quarter variable
    POY$quarter[POY$month<=3] <- "Q3"
    POY$quarter[POY$month>3 & POY$month <=6] <- "Q4"
    POY$quarter[POY$month>6 & POY$month <=9] <- "Q1"
    POY$quarter[POY$month>9 & POY$month <=12] <- "Q2"
    POY$quarter <- as.factor(POY$quarter)

For the Fiscal Year variable: it runs July - June, so:
June'15 should become FY1415
July'15 should become FY1516
Or: Q1 and Q2 in 2015 should become FY1516, while Q3 and Q4 of 2015 are actually FY1415.

    #Making a FY variable 
    for (i in 1:nrow(POY)) {
        if (POY$quarter[i] == "Q1" | POY$quarter[i] == "Q2") {
        year1 <- as.character(POY$year[i])
        year2 <- as.character(POY$year[i] + 1)
      } else {
        year1 <- as.character(POY$year[i]- 1)
        year2 <- as.character(POY$year[i])
      }
      POY$FY[i] <- paste0("FY", substr(year1, start=3, stop=4),         substr(year2, start=3, stop=4))
    }
    POY$FY <- as.factor(POY$FY)
    summary(POY$FY)

Any suggestions? Thank you!

r datetime
3 Answers

I've used a combination of base R, lubridate and dplyr;

# make a blank dataframe with sequential dates ...
df <- data.frame(date = seq (as.Date('2011-07-01'), as.Date('2015-07-01'), by = 'month'))

# similar to original poster, separate year/month/day
df <- 
  df %>% 
  separate(col = date, into = c('yr', 'mnth', 'dy'), sep = '-', convert = TRUE, remove = FALSE)

# extract last 2 digits of year
df$yr_small <- strftime(x = df$date, format = '%y', tz = 'GMT')
df$yr_small <- as.numeric(df$yr_small)

# Use dplyr's "case_when" to categorise quarters
df <- 
  df %>% 
  # make quarters
  mutate(
    quarter = case_when(
      mnth >= 7 & mnth <= 9 ~ 'Q1'
      , mnth >= 10 & mnth <= 12 ~ 'Q2'
      , mnth >= 1 & mnth <= 3 ~ 'Q3'
      , mnth >= 4 & mnth <= 6 ~ 'Q4' ) ) %>% 
  # ... the financial year is
  mutate (
    financial_year = case_when(
      quarter == 'Q1' | quarter == 'Q2' ~ (yr_small + 1)
      , quarter == 'Q3' | quarter == 'Q4' ~ (yr_small) ) )

# final column to make the full financial year start/end
df <- df %>%  mutate (FY = paste('FY',df$financial_year, df$financial_year + 1, sep = '') )

Should give you this:

screenshot

user_55035480

You could use this to replace the for-loop, I think. If you'd supply some data I could test it.

#Making a FY variable
POY$year1 <- as.character(POY$year - 1)
POY$year2 <- as.character(POY$year)

POY$year1[(POY$quarter == "Q1") | (POY$quarter == "Q2")] <-
  as.character(POY$year[(POY$quarter == "Q1") |(POY$quarter == "Q2")])

POY$year2[(POY$quarter == "Q1") | (POY$quarter == "Q2")] <-
  as.character(POY$year[(POY$quarter == "Q1") | (POY$quarter == "Q2")] + 1)

POY$FY <-
  paste0("FY", substr(POY$year1, 3, 4), substr(POY$year2, 3, 4))

POY$FY <- as.factor(POY$FY)
summary(POY$FY)
user_30521480
Related