Forecasting von hierarischen und gruppierte Daten

Beispiel Sales

Author

Jürgen Mayer

Published

April 12, 2024

Setup

Im folgenden werden Forecasts von gruppierten oder hierarischen Saleszahlen erzeugt. Das entsprechende R script wird exportiert und kann dann weiterverwendet werden.

[1] "Database: "
[1] "Predifo"
[1] "file: "
[1] "Sales3"

Aufbereitung und Filterung der Daten

Namen werden für die Standardisierung vereinheitlicht:

  1. date

  2. category category_2

  3. value

Filespezifische Anpassung

`summarise()` has grouped output by 'category', 'category_2'. You can override
using the `.groups` argument.
`summarise()` has grouped output by 'category'. You can override using the
`.groups` argument.
# A tibble: 20 × 2
# Groups:   category [5]
   category category_2
      <int>      <int>
 1      200          1
 2      200          2
 3      200          3
 4      200         NA
 5      210          1
 6      210          2
 7      210          3
 8      210         NA
 9      220          1
10      220          2
11      220          3
12      220         NA
13      230          1
14      230          2
15      230          3
16      230         NA
17      300          1
18      300          2
19      300          3
20      300         NA
# A tibble: 20 × 3
# Groups:   category, category_2 [20]
   category category_2     n
      <int>      <int> <int>
 1      200          1   236
 2      200          2   209
 3      200          3   236
 4      200         NA     6
 5      210          1   228
 6      210          2   214
 7      210          3   227
 8      210         NA    12
 9      220          1   215
10      220          2   215
11      220          3   215
12      220         NA    13
13      230          1   221
14      230          2   196
15      230          3   221
16      230         NA    13
17      300          1   210
18      300          2   191
19      300          3   209
20      300         NA    30

category_2 ist teilweise NA –> entweder ersetzen durch 4 oder löschen.

# A tibble: 15 × 3
# Groups:   category, category_2 [15]
   category category_2     n
      <int>      <int> <int>
 1      200          1   236
 2      200          2   209
 3      200          3   236
 4      210          1   228
 5      210          2   214
 6      210          3   227
 7      220          1   215
 8      220          2   215
 9      220          3   215
10      230          1   221
11      230          2   196
12      230          3   221
13      300          1   210
14      300          2   191
15      300          3   209

Daten gruppieren und visualisieren

Man beachte die unterschiedlichen Skalierungen der Y-Achsen.

Loading required namespace: crayon
# A tsibble: 5,343 x 4 [1M]
# Key:       category, category_2 [24]
   Datum_ym category     category_2   value
      <mth> <int*>       <int*>       <dbl>
 1 2002 Apr <aggregated> <aggregated>   600
 2 2002 May <aggregated> <aggregated>   882
 3 2002 Jun <aggregated> <aggregated>  1294
 4 2002 Jul <aggregated> <aggregated>  4190
 5 2002 Aug <aggregated> <aggregated>   721
 6 2002 Sep <aggregated> <aggregated> 10970
 7 2002 Oct <aggregated> <aggregated>  7124
 8 2002 Nov <aggregated> <aggregated>  7771
 9 2002 Dec <aggregated> <aggregated>  3472
10 2003 Jan <aggregated> <aggregated>  9208
# ℹ 5,333 more rows

Forecasting

method:

  • ols: ordinary least squares
  • wls_var: weighted least squares
  • wls_struct: structural scaling
  • mint_cov: sample covariance
  • mint_shrink: shrinked covariance

Warning: The future dataset is incomplete, incomplete out-of-sample data will be treated as missing. 
1 observation is missing at 2021 Dec
Accuracy Forecast
.model rmse mase
base 917.86 1.26
bu 917.86 1.26
mint 927.45 1.26
ols 943.83 1.91 
Warning: The future dataset is incomplete, incomplete out-of-sample data will be treated as missing. 
1 observation is missing at 2021 Dec
# A tibble: 60 × 6
   .model category category_2 .type   rmse   mase
   <chr>  <int*>   <int*>     <chr>  <dbl>  <dbl>
 1 base   200      1          Test   171.  1.03  
 2 base   200      2          Test   547.  2.00  
 3 base   200      3          Test   765.  0.865 
 4 base   210      1          Test   576.  1.22  
 5 base   210      2          Test    67.9 0.371 
 6 base   210      3          Test   357.  0.578 
 7 base   220      1          Test   214.  1.45  
 8 base   220      2          Test    10.4 0.0152
 9 base   220      3          Test  1941.  0.711 
10 base   230      1          Test  1772.  1.96  
# ℹ 50 more rows
Accuracy Forecast
.model category category_2 .type rmse mase
base 200 1 Test 170.67 1.03
base 200 2 Test 547.42 2.00
base 200 3 Test 764.74 0.87
base 210 1 Test 575.77 1.22
base 210 2 Test 67.92 0.37
base 210 3 Test 357.27 0.58
base 220 1 Test 213.64 1.45
base 220 2 Test 10.41 0.02
base 220 3 Test 1940.95 0.71
base 230 1 Test 1772.13 1.96
base 230 2 Test 14.01 0.95
base 230 3 Test 558.07 2.52
base 300 1 Test 4056.16 2.00
base 300 2 Test 993.65 1.88
base 300 3 Test 1725.10 1.32
bu 200 1 Test 170.67 1.03
bu 200 2 Test 547.42 2.00
bu 200 3 Test 764.74 0.87
bu 210 1 Test 575.77 1.22
bu 210 2 Test 67.92 0.37
bu 210 3 Test 357.27 0.58
bu 220 1 Test 213.64 1.45
bu 220 2 Test 10.41 0.02
bu 220 3 Test 1940.95 0.71
bu 230 1 Test 1772.13 1.96
bu 230 2 Test 14.01 0.95
bu 230 3 Test 558.07 2.52
bu 300 1 Test 4056.16 2.00
bu 300 2 Test 993.65 1.88
bu 300 3 Test 1725.10 1.32
mint 200 1 Test 167.71 1.01
mint 200 2 Test 545.76 1.98
mint 200 3 Test 810.67 0.95
mint 210 1 Test 573.69 1.22
mint 210 2 Test 57.60 0.31
mint 210 3 Test 356.75 0.58
mint 220 1 Test 212.78 1.44
mint 220 2 Test 35.23 0.05
mint 220 3 Test 2122.69 0.82
mint 230 1 Test 1812.62 2.06
mint 230 2 Test 14.01 0.96
mint 230 3 Test 560.92 2.53
mint 300 1 Test 4080.54 1.98
mint 300 2 Test 973.89 1.85
mint 300 3 Test 1586.87 1.19
ols 200 1 Test 233.34 1.34
ols 200 2 Test 579.76 3.17
ols 200 3 Test 759.45 0.84
ols 210 1 Test 588.34 1.24
ols 210 2 Test 49.66 0.24
ols 210 3 Test 414.65 0.74
ols 220 1 Test 278.83 1.64
ols 220 2 Test 132.90 0.21
ols 220 3 Test 1923.16 0.71
ols 230 1 Test 1784.82 1.96
ols 230 2 Test 115.49 7.51
ols 230 3 Test 700.56 3.31
ols 300 1 Test 4060.95 2.00
ols 300 2 Test 984.49 2.50
ols 300 3 Test 1550.98 1.18 
# A fable: 1,152 x 6 [1M]
# Key:     category, category_2, .model [48]
   category category_2 .model Datum_ym         value .mean
   <int*>   <int*>     <chr>     <mth>        <dist> <dbl>
 1 200      1          ets    2021 Dec N(517, 15953)  517.
 2 200      1          ets    2022 Jan N(619, 23071)  619.
 3 200      1          ets    2022 Feb N(598, 21645)  598.
 4 200      1          ets    2022 Mar N(599, 21890)  599.
 5 200      1          ets    2022 Apr N(518, 16424)  518.
 6 200      1          ets    2022 May N(492, 14929)  492.
 7 200      1          ets    2022 Jun N(785, 38245)  785.
 8 200      1          ets    2022 Jul N(707, 31251)  707.
 9 200      1          ets    2022 Aug N(551, 19057)  551.
10 200      1          ets    2022 Sep N(566, 20242)  566.
# ℹ 1,142 more rows

Training

Genauigkeit

Warning: The future dataset is incomplete, incomplete out-of-sample data will be treated as missing. 
1 observation is missing at 2021 Dec
Accuracy Forecast
category category_2 .model .type RMSE MAE MAPE MASE RMSSE
200 1 base Test 170.67 139.59 35.00 1.03 0.62
200 1 bu Test 170.67 139.59 35.00 1.03 0.62
200 1 mint Test 167.71 137.61 34.02 1.01 0.61
200 1 ols Test 233.34 182.39 32.59 1.34 0.85
200 2 base Test 547.42 183.86 112.45 2.00 1.65
200 2 bu Test 547.42 183.86 112.45 2.00 1.65
200 2 mint Test 545.76 182.21 107.53 1.98 1.64
200 2 ols Test 579.76 292.38 462.24 3.17 1.74
200 3 base Test 764.74 595.02 35.49 0.87 0.48
200 3 bu Test 764.74 595.02 35.49 0.87 0.48
200 3 mint Test 810.67 655.59 37.11 0.95 0.51
200 3 ols Test 759.45 577.65 36.28 0.84 0.48
200 base Test 1028.44 864.74 36.80 1.03 0.59
200 bu Test 983.14 792.42 35.48 0.95 0.57
200 mint Test 981.67 811.99 35.56 0.97 0.57
200 ols Test 1039.93 878.01 36.49 1.05 0.60
210 1 base Test 575.77 444.66 104.85 1.22 1.22
210 1 bu Test 575.77 444.66 104.85 1.22 1.22
210 1 mint Test 573.69 443.26 102.80 1.22 1.21
210 1 ols Test 588.34 450.25 80.29 1.24 1.24
210 2 base Test 67.92 63.36 355.83 0.37 0.16
210 2 bu Test 67.92 63.36 355.83 0.37 0.16
210 2 mint Test 57.60 52.47 287.84 0.31 0.13
210 2 ols Test 49.66 41.44 194.48 0.24 0.11
210 3 base Test 357.27 248.10 29.64 0.58 0.59
210 3 bu Test 357.27 248.10 29.64 0.58 0.59
210 3 mint Test 356.75 246.84 29.54 0.58 0.59
210 3 ols Test 414.65 318.47 39.36 0.74 0.68
210 base Test 683.13 584.07 43.59 0.74 0.66
210 bu Test 691.45 602.13 44.01 0.77 0.66
210 mint Test 684.32 588.13 42.82 0.75 0.66
210 ols Test 707.83 593.88 41.66 0.76 0.68
220 1 base Test 213.64 166.47 37.98 1.45 1.41
220 1 bu Test 213.64 166.47 37.98 1.45 1.41
220 1 mint Test 212.78 165.61 37.68 1.44 1.41
220 1 ols Test 278.83 188.89 32.29 1.64 1.85
220 2 base Test 10.41 9.08 Inf 0.02 0.01
220 2 bu Test 10.41 9.08 Inf 0.02 0.01
220 2 mint Test 35.23 29.44 Inf 0.05 0.02
220 2 ols Test 132.90 122.51 Inf 0.21 0.09
220 3 base Test 1940.95 1531.00 38.91 0.71 0.43
220 3 bu Test 1940.95 1531.00 38.91 0.71 0.43
220 3 mint Test 2122.69 1774.99 47.61 0.82 0.47
220 3 ols Test 1923.16 1536.31 40.17 0.71 0.43
220 base Test 2135.99 1624.00 34.33 0.66 0.44
220 bu Test 2008.43 1542.81 33.94 0.63 0.42
220 mint Test 2205.53 1801.98 41.22 0.74 0.46
220 ols Test 2180.52 1675.25 35.83 0.68 0.45
230 1 base Test 1772.13 1370.52 80.22 1.96 1.05
230 1 bu Test 1772.13 1370.52 80.22 1.96 1.05
230 1 mint Test 1812.62 1442.02 91.13 2.06 1.08
230 1 ols Test 1784.82 1374.28 80.62 1.96 1.06
230 2 base Test 14.01 11.60 83.90 0.95 0.72
230 2 bu Test 14.01 11.60 83.90 0.95 0.72
230 2 mint Test 14.01 11.64 83.03 0.96 0.72
230 2 ols Test 115.49 91.41 514.57 7.51 5.95
230 3 base Test 558.07 430.12 32.84 2.52 2.22
230 3 bu Test 558.07 430.12 32.84 2.52 2.22
230 3 mint Test 560.92 431.84 32.98 2.53 2.23
230 3 ols Test 700.56 565.00 44.00 3.31 2.79
230 base Test 2065.64 1665.56 58.49 2.13 1.21
230 bu Test 1855.63 1494.27 48.80 1.91 1.09
230 mint Test 1941.22 1573.63 53.40 2.01 1.14
230 ols Test 1980.98 1590.99 54.00 2.03 1.16
300 1 base Test 4056.16 3165.68 94.01 2.00 1.54
300 1 bu Test 4056.16 3165.68 94.01 2.00 1.54
300 1 mint Test 4080.54 3130.50 89.88 1.98 1.54
300 1 ols Test 4060.95 3164.90 91.55 2.00 1.54
300 2 base Test 993.65 453.69 64.58 1.88 0.84
300 2 bu Test 993.65 453.69 64.58 1.88 0.84
300 2 mint Test 973.89 446.91 67.14 1.85 0.83
300 2 ols Test 984.49 604.38 222.55 2.50 0.84
300 3 base Test 1725.10 1509.43 27.16 1.32 0.70
300 3 bu Test 1725.10 1509.43 27.16 1.32 0.70
300 3 mint Test 1586.87 1360.56 25.10 1.19 0.64
300 3 ols Test 1550.98 1351.99 24.93 1.18 0.63
300 base Test 5018.68 4003.58 35.13 1.64 1.32
300 bu Test 5170.17 4159.17 36.71 1.70 1.36
300 mint Test 5058.02 4096.28 35.53 1.68 1.33
300 ols Test 5049.66 4082.23 35.61 1.67 1.32
1 base Test 4596.88 3863.83 44.14 2.06 1.58
1 bu Test 4739.74 4047.91 47.28 2.16 1.62
1 mint Test 4710.45 4008.09 46.56 2.14 1.61
1 ols Test 4808.83 4090.50 44.70 2.18 1.65
2 base Test 1028.97 547.11 139.01 0.66 0.56
2 bu Test 1035.44 545.02 131.75 0.66 0.56
2 mint Test 1036.32 548.49 105.95 0.67 0.56
2 ols Test 1241.20 933.32 288.14 1.13 0.67
3 base Test 3533.28 2839.46 23.43 0.81 0.60
3 bu Test 3954.65 3390.99 25.53 0.97 0.67
3 mint Test 3886.42 3350.61 25.17 0.96 0.66
3 ols Test 3632.13 3035.59 23.95 0.87 0.62
base Test 8315.66 7057.24 30.07 1.47 1.19
bu Test 7929.37 6869.55 30.42 1.43 1.14
mint Test 7935.16 6776.70 29.77 1.41 1.14
ols Test 7922.62 6733.29 29.39 1.40 1.13

Interpretation: Sind die MAPE-Werte verwendbar?

Export

Die generierten Daten werden exportiert.

Möglichkeiten:

  • lokales file

  • Speichern in SQL Server

exp_fc_h wurde exportier