Navigate
Courses Tutorials Practice Pricing Tools Upgrade

Reconciliation with fabletools

Today let's see how to make forecasts at every level of a hierarchy add up, using four functions from the fabletools package in R.

Say you forecast monthly retail turnover in Australia. There are three states, New South Wales, Victoria and Queensland, and three industries in each state: food retailing, household goods retailing, and clothing, footwear and personal accessory retailing. That gives 9 series of monthly turnover, in millions of Australian dollars, from January 2010 to December 2018.

Those 9 series roll up. The three industries in a state add up to that state's turnover, and the three states add up to a Total. So besides the 9 series you also have 3 State series and 1 Total series, 13 series in all.

The workflow that produces forecasts for all 13 is four function calls, in this order.

The diagram lists the four calls in the order the lesson runs them on the retail data.

How aggregate_key() declares the hierarchy

The retail data comes with the tsibbledata package as aus_retail, and it is a tsibble. A tsibble is a data frame for time series: one column is the time index (here Month), and the key columns (here State and Industry) say which series each row belongs to.

The code below reads it with tsibbledata::aus_retail, keeps the 3 states and 3 industries, from January 2010 onward, and prints the first 4 rows. The blocks on this page share one R session, so run them in order.

RInteractive R
# Build the retail turnover series for 3 states and 3 industries library(tsibble) library(tsibbledata) library(dplyr) library(fable) library(fabletools) retail <- tsibbledata::aus_retail |> filter( State %in% c("New South Wales", "Victoria", "Queensland"), Industry %in% c( "Food retailing", "Household goods retailing", "Clothing, footwear and personal accessory retailing" ), Month >= yearmonth("2010 Jan") ) |> select(Month, State, Industry, Turnover) print(retail, n = 4) #> # A tsibble: 972 x 4 [1M] #> # Key: State, Industry [9] #> Month State Industry Turnover #> <mth> <chr> <chr> <dbl> #> 1 2010 Jan New South Wales Clothing, footwear and personal accessory r… 536. #> 2 2010 Feb New South Wales Clothing, footwear and personal accessory r… 427. #> 3 2010 Mar New South Wales Clothing, footwear and personal accessory r… 494. #> 4 2010 Apr New South Wales Clothing, footwear and personal accessory r… 508. #> # ℹ 968 more rows

  

  





There are 972 rows, which is 9 series with 108 months each. The [1M] in the header means one row per month, and Key: State, Industry [9] says the data holds 9 series.



aggregate_key() adds the summed series. The formula State / Industry says Industry sits inside State, so every industry series has exactly one parent, its state. That one-parent rule is what makes this a hierarchy. And Turnover = sum(Turnover) says each new series is the sum of its children's turnover in every month.




  

    
RInteractive R

    
# Add the 3 State series and the Total series to the 9 State-by-industry series
sales <- retail |>
  aggregate_key(State / Industry, Turnover = sum(Turnover))

print(sales, n = 4)
#> # A tsibble: 1,404 x 4 [1M]
#> # Key:       State, Industry [13]
#>      Month State        Industry     Turnover
#>      <mth> <chr*>       <chr*>          <dbl>
#> 1 2010 Jan <aggregated> <aggregated>   10124.
#> 2 2010 Feb <aggregated> <aggregated>    8810 
#> 3 2010 Mar <aggregated> <aggregated>    9737.
#> 4 2010 Apr <aggregated> <aggregated>    9545.
#> # ℹ 1,400 more rows
n_keys(sales)
#> [1] 13

    

      
      
    

    

  

  





The output has 1,404 rows, which is 13 series times 108 months, and n_keys() counts the 13 series. In the new rows the key holds <aggregated>, a label that means the series is a sum over that key. The star in <chr*> marks a key column that can hold this label.



The 9 State-and-industry series are the bottom level. Each series in the hierarchy, from the Total down to the bottom level, is called a node.



The Total is the node where both keys are aggregated. is_aggregated() is TRUE when a key holds that label, so filtering on it for State and for Industry picks the Total out.




  

    
RInteractive R

    
# Pick out the Total series, where both keys are aggregated
sales_total <- sales |>
  filter(is_aggregated(State), is_aggregated(Industry))

print(sales_total, n = 3)
#> # A tsibble: 108 x 4 [1M]
#> # Key:       State, Industry [1]
#>      Month State        Industry     Turnover
#>      <mth> <chr*>       <chr*>          <dbl>
#> 1 2010 Jan <aggregated> <aggregated>   10124.
#> 2 2010 Feb <aggregated> <aggregated>    8810 
#> 3 2010 Mar <aggregated> <aggregated>    9737.
#> # ℹ 105 more rows

    

      
      
    

    

  

  





The Total has 108 rows, one per month. Its turnover is the sum of these 9 series only, so it is not the national retail figure.



There is one other way to declare the structure. Writing State * Industry crosses the two keys instead of nesting them, so each industry is also summed across all 3 states. Every bottom series then has two parents, its state and its industry, and a structure like that is called grouped rather than a hierarchy.




  

    
RInteractive R

    
# Count the series when the two keys cross instead of nest
crossed <- retail |>
  aggregate_key(State * Industry, Turnover = sum(Turnover))

n_keys(crossed)
#> [1] 16

    

      
      
    

    

  

  





The crossed version has 16 series against 13 for the nested one: the 9 bottom series, 3 State series, 3 Industry series and the Total. This lesson keeps the nested hierarchy.