Sorting Months by Fiscal Year in R: A Simple Solution

Today, I worked with a dataset containing month numbers and corresponding values, and when sorted by month number, it gives the expected 1-12 order. However, this data needed to be sorted using fiscal year calendar (7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6).

I had to go back and think about how to sort months for fiscal years all over again. If you are trying to custom sort months for fiscal years, follow along to see how easy it is.

The magic that happens here is brought to you by the factor() function in R. Factors are a type of data structure in R that will only allow values from a predefined set of options.

This will also work if your months are named instead of using an integer, you will just need to change the levels parameter to match your the format of your month (“January” or “Jan” or “JAN”)

Example Time

I created a data frame with different month numbers in mon_data, and random values in mon_val.

> mon_data = c(7,1,8,2,12,11,10,4,9,6,5,3,2,10,12,8)
> mon_val = c(10,13,12,14,12,11,16,18,20,1,2,4,7,5,10,16)

> dat = data.frame(mon_data, mon_val)
> dat

  mon_data mon_val
         7      10
         1      13
         8      12
         2      14
        12      12
        11      11
        10      16
         4      18
         9      20
         6       1
         5       2
         3       4
         2       7
        10       5
        12      10
         8      16

Using the dplyr package to sort (using the arrange function) this data frame results in the standard, ascending numerical sort:

library(dplyr)

dat |> arrange(mon_data)

 mon_data mon_val
        1      13
        2      14
        2       7
        3       4
        4      18
        5       2
        6       1
        7      10
        8      12
        8      16
        9      20
       10      16
       10       5
       11      11
       12      12
       12      10

Convert to a Factor

To sort the data by months using a fiscal year calendar, convert the mon_data column to a factor() with the levels argument (shown below). Then, when the sort is applied it, almost magically, is sorted by month using the levels specified.

> dat$mon_data = factor(dat$mon_data, levels = c(7,8,9,10,11,12,1,2,3,4,5,6))
> dat |> arrange(mon_data)

 mon_data mon_val
        7      10
        8      12
        8      16
        9      20
       10      16
       10       5
       11      11
       12      12
       12      10
        1      13
        2      14
        2       7
        3       4
        4      18
        5       2
        6       1

Give the Factor Labels

To make the output even more readable, you can map the levels to labels by adding the labels argument. In the output, “7” will be “JUL”.

> dat$mon_data = factor(dat$mon_data, levels = c(7,8,9,10,11,12,1,2,3,4,5,6),
                                      labels = c("JUL", "AUG", "SEP", "OCT", "NOV", "DEC", "JAN", "FEB", "MAR", "APR", "MAY", "JUN"))
> dat |> arrange(mon_data)

  mon_data mon_val
       JUL      10
       AUG      12
       AUG      16
       SEP      20
       OCT      16
       OCT       5
       NOV      11
       DEC      12
       DEC      10
       JAN      13
       FEB      14
       FEB       7
       MAR       4
       APR      18
       MAY       2
       JUN       1

Conclusion

To my knowledge, there isn’t a default way to sort months by fiscal year vs calendar year in R. That said, it can be done very fast using the base R factor() function to order the values in a custom order. This approach not only saves time but also opens up new possibilities for analyzing and visualizing financial data. When it’s important to sort your data by fiscal year months, understanding how to manipulate formats in R becomes super important.