Profile picture

Ikea TRO

Last updated: August 29th, 20202020-08-29Project preview

Total Range Overview

In this example we'll be analyzing Ikea's total range products.

purple-divider

Hands on!

In [3]:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

%matplotlib inline

green-divider

Loading our data:

In [17]:
df = pd.read_excel('data/TRO.xlsx')

green-divider

The data at a glance:

In [19]:
df.head()
Out[19]:
HFB PA Item No. Item Description Available In Store Replenishment Code Range Code Service Level Sales Start Date End Date Sale ... MPQ Lead Time Lead Time Deviation Actual Inventory Sales Space Capacity Sales Space Type Maximum Delivery Quantity Full Pallet Mark Delivery Type Unit Volume
0 15 1521 15134 SVALKA WHI WNE GLS 25 CL CLEAR GLASS 6-P 1 10 O 2 2016-10-01 2021-02-01 ... 125 4.6 1.4 161.0 138 Flexible 0 M M 0.00655
1 18 1832 64662 OMSORG N SHOEHORN ASSORTED COLOURS 1 77 A 5 2015-04-01 2018-10-01 ... 1 0.0 0.0 0.0 26 Flexible 0 M M 0.00036
2 11 1111 71262 OXEL N BED BSE SD CVR 140X200 WHITE 1 7 A 5 2015-04-01 2017-08-01 ... 7 2.6 0.3 0.0 8 Flexible 0 M M 0.00223
3 16 1645 81591 MONGSTAD MIRROR 94X190 BLACK-BROWN 1 10 O 5 2015-04-01 2020-08-01 ... 11 2.9 0.1 1.0 13 Non Flexible 0 M M 0.12575
4 2 216 94348 EKBY JARPEN N SHELF 79X19 BLACK-BROWN 1 77 A 5 2015-04-01 2019-04-01 ... 87 0.0 0.0 0.0 60 Flexible 0 M M 0.00455

5 rows × 26 columns

In [20]:
df.shape
Out[20]:
(5195, 26)
In [22]:
df.info
Out[22]:
<bound method DataFrame.info of       HFB    PA  Item No.                                   Item Description  \
0      15  1521     15134           SVALKA WHI WNE GLS 25 CL CLEAR GLASS 6-P   
1      18  1832     64662                 OMSORG N SHOEHORN ASSORTED COLOURS   
2      11  1111     71262                OXEL N BED BSE SD CVR 140X200 WHITE   
3      16  1645     81591                 MONGSTAD MIRROR 94X190 BLACK-BROWN   
4       2   216     94348              EKBY JARPEN N SHELF 79X19 BLACK-BROWN   
...   ...   ...       ...                                                ...   
5190   18  1811  90465066  FJALLA N stor box w lid 25x36x20 lgrn/flower patt   
5191   18  1813  90465165      ANILINARE deco box s2 green gold-colour/metal   
5192   12  1231  90466594              LJUV cushion cvr 50x50 yellow/printed   
5193   15  1543  90466607                   LJUV place mat 35x45 red/printed   
5194   13  1311  90467107                    LJUV rug high pile 133x195 pink   

      Available In Store  Replenishment Code Range Code  Service Level  \
0                      1                  10          O              2   
1                      1                  77          A              5   
2                      1                   7          A              5   
3                      1                  10          O              5   
4                      1                  77          A              5   
...                  ...                 ...        ...            ...   
5190                   1                  10          O              5   
5191                   1                  10          O              5   
5192                   1                   5          O              4   
5193                   1                   5          O              5   
5194                   1                   5          O              5   

     Sales Start Date End Date Sale  ...  MPQ  Lead Time  Lead Time Deviation  \
0          2016-10-01    2021-02-01  ...  125        4.6                  1.4   
1          2015-04-01    2018-10-01  ...    1        0.0                  0.0   
2          2015-04-01    2017-08-01  ...    7        2.6                  0.3   
3          2015-04-01    2020-08-01  ...   11        2.9                  0.1   
4          2015-04-01    2019-04-01  ...   87        0.0                  0.0   
...               ...           ...  ...  ...        ...                  ...   
5190       2020-02-01    2020-10-01  ...   56        3.0                  0.1   
5191       2020-02-01    2020-10-01  ...    9        5.7                  0.3   
5192       2020-02-01    2020-10-01  ...   24        5.7                  0.3   
5193       2020-02-01    2020-10-01  ...   80        5.7                  0.3   
5194       2020-02-01    2020-10-01  ...    8        5.7                  0.3   

      Actual Inventory Sales Space Capacity  Sales Space Type  \
0                161.0                  138          Flexible   
1                  0.0                   26          Flexible   
2                  0.0                    8          Flexible   
3                  1.0                   13      Non Flexible   
4                  0.0                   60          Flexible   
...                ...                  ...               ...   
5190              44.0                   30          Flexible   
5191               0.0                   61          Flexible   
5192              84.0                   48          Flexible   
5193             135.0                   80          Flexible   
5194               5.0                    8          Flexible   

      Maximum Delivery Quantity  Full Pallet Mark  Delivery Type  Unit Volume  
0                             0                 M              M      0.00655  
1                             0                 M              M      0.00036  
2                             0                 M              M      0.00223  
3                             0                 M              M      0.12575  
4                             0                 M              M      0.00455  
...                         ...               ...            ...          ...  
5190                          0                 M              M      0.00603  
5191                          0                 M              M      0.00403  
5192                          0                 M              M      0.00069  
5193                          0                 M              M      0.00025  
5194                          0                 M              M      0.04436  

[5195 rows x 26 columns]>
In [23]:
df.describe()
Out[23]:
HFB PA Item No. Available In Store Replenishment Code Service Level AWS EWS Safety Stock (in weeks) Safety Stock (to DSP) PQ MPQ Lead Time Lead Time Deviation Actual Inventory Sales Space Capacity Maximum Delivery Quantity Unit Volume
count 5195.000000 5195.000000 5.195000e+03 5195.0 5195.000000 5195.000000 5195.000000 5195.000000 5195.00000 5195.000000 5195.000000 5195.000000 5195.000000 5195.000000 5195.000000 5195.000000 5195.000000 5195.000000
mean 10.149567 1040.731088 4.407653e+07 1.0 16.919538 4.521078 4.855819 4.608878 15.37150 16.995765 429.695861 30.512608 3.425890 0.177883 58.173080 44.135900 0.004427 0.030175
std 5.474673 549.678376 2.855404e+07 0.0 23.377889 0.917511 17.778906 15.766602 40.28193 78.246680 817.989540 87.641894 2.622673 0.187945 241.609534 128.472692 0.100918 0.116377
min 1.000000 111.000000 1.513400e+04 1.0 1.000000 1.000000 0.000000 0.008700 0.00000 0.000000 1.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
25% 7.000000 711.000000 2.031787e+07 1.0 7.000000 4.000000 0.050000 0.217500 2.17000 2.000000 40.000000 2.000000 0.000000 0.000000 0.000000 6.000000 0.000000 0.001370
50% 10.000000 1081.000000 4.043152e+07 1.0 7.000000 5.000000 0.580000 0.669900 6.05000 3.000000 144.000000 12.000000 3.000000 0.100000 5.000000 14.000000 0.000000 0.004330
75% 15.000000 1543.000000 7.030757e+07 1.0 10.000000 5.000000 2.860000 2.801700 14.59500 9.000000 449.000000 28.000000 5.700000 0.300000 30.000000 36.000000 0.000000 0.014890
max 70.000000 7015.000000 9.046711e+07 1.0 77.000000 5.000000 473.100000 302.150000 908.59000 2000.000000 14400.000000 2280.000000 15.600000 3.700000 5965.000000 2691.000000 6.000000 2.121820

green-divider

Numerical analysis and visualization

We'll analyze the AWS (Average Weekly sales) column:

In [26]:
df['AWS'].describe()
Out[26]:
count    5195.000000
mean        4.855819
std        17.778906
min         0.000000
25%         0.050000
50%         0.580000
75%         2.860000
max       473.100000
Name: AWS, dtype: float64
In [27]:
df['AWS'].mean()
Out[27]:
4.855819254360458
In [28]:
df['AWS'].median()
Out[28]:
0.58
In [29]:
df['AWS'].plot(kind='box', vert=False, figsize=(14,6))
Out[29]:
<matplotlib.axes._subplots.AxesSubplot at 0x7fda3a8d16a0>
In [30]:
df['AWS'].plot(kind='density', figsize=(14,6)) # kde
Out[30]:
<matplotlib.axes._subplots.AxesSubplot at 0x7fda3a8cbb50>
In [31]:
ax = df['AWS'].plot(kind='density', figsize=(14,6)) # kde
ax.axvline(df['AWS'].mean(), color='red')
ax.axvline(df['AWS'].median(), color='green')
Out[31]:
<matplotlib.lines.Line2D at 0x7fda310522b0>
In [39]:
ax = df['AWS'].plot(kind='hist', figsize=(14,6))
ax.set_ylabel('Number of Articles')
ax.set_xlabel('Average Weekly Sales')
Out[39]:
Text(0.5, 0, 'Average Weekly Sales')

green-divider

Categorical analysis and visualization

We'll analyze the `HFB' (Home furnishing businesses) column:

In [41]:
df.head()
Out[41]:
HFB PA Item No. Item Description Available In Store Replenishment Code Range Code Service Level Sales Start Date End Date Sale ... MPQ Lead Time Lead Time Deviation Actual Inventory Sales Space Capacity Sales Space Type Maximum Delivery Quantity Full Pallet Mark Delivery Type Unit Volume
0 15 1521 15134 SVALKA WHI WNE GLS 25 CL CLEAR GLASS 6-P 1 10 O 2 2016-10-01 2021-02-01 ... 125 4.6 1.4 161.0 138 Flexible 0 M M 0.00655
1 18 1832 64662 OMSORG N SHOEHORN ASSORTED COLOURS 1 77 A 5 2015-04-01 2018-10-01 ... 1 0.0 0.0 0.0 26 Flexible 0 M M 0.00036
2 11 1111 71262 OXEL N BED BSE SD CVR 140X200 WHITE 1 7 A 5 2015-04-01 2017-08-01 ... 7 2.6 0.3 0.0 8 Flexible 0 M M 0.00223
3 16 1645 81591 MONGSTAD MIRROR 94X190 BLACK-BROWN 1 10 O 5 2015-04-01 2020-08-01 ... 11 2.9 0.1 1.0 13 Non Flexible 0 M M 0.12575
4 2 216 94348 EKBY JARPEN N SHELF 79X19 BLACK-BROWN 1 77 A 5 2015-04-01 2019-04-01 ... 87 0.0 0.0 0.0 60 Flexible 0 M M 0.00455

5 rows × 26 columns

In [42]:
df['HFB'].value_counts()
Out[42]:
16    738
7     587
1     440
11    432
10    417
18    403
15    338
4     324
12    297
9     277
2     208
14    150
6     137
13    105
17     88
8      86
3      83
5      81
70      3
20      1
Name: HFB, dtype: int64
In [43]:
df['HFB'].value_counts().plot(kind='pie', figsize=(6,6))
Out[43]:
<matplotlib.axes._subplots.AxesSubplot at 0x7fda2ec7bc40>
In [44]:
ax = df['HFB'].value_counts().plot(kind='bar', figsize=(14,6))
ax.set_ylabel('Number of articles')
Out[44]:
Text(0, 0.5, 'Number of articles')

green-divider

Relationship between the columns?

Can we find any significant relationship?

In [45]:
corr = df.corr()

corr
Out[45]:
HFB PA Item No. Available In Store Replenishment Code Service Level AWS EWS Safety Stock (in weeks) Safety Stock (to DSP) PQ MPQ Lead Time Lead Time Deviation Actual Inventory Sales Space Capacity Maximum Delivery Quantity Unit Volume
HFB 1.000000 0.999611 -0.033542 NaN 0.019161 0.029288 0.093495 0.104406 0.106713 0.057478 0.246975 0.127808 0.019142 -0.001275 0.106766 0.120747 -0.011653 -0.265174
PA 0.999611 1.000000 -0.032797 NaN 0.018922 0.030361 0.094499 0.105473 0.107082 0.058070 0.249395 0.129122 0.018178 -0.001743 0.107352 0.121518 -0.011703 -0.267383
Item No. -0.033542 -0.032797 1.000000 NaN -0.018275 -0.024678 0.005762 0.013136 -0.013897 -0.007731 -0.016753 -0.009219 -0.000350 -0.007076 -0.012635 0.004916 -0.004438 -0.009069
Available In Store NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Replenishment Code 0.019161 0.018922 -0.018275 NaN 1.000000 0.105224 -0.101575 0.005403 -0.151899 -0.078815 0.005241 -0.023870 -0.117651 -0.071948 -0.089763 -0.035269 -0.014456 0.013461
Service Level 0.029288 0.030361 -0.024678 NaN 0.105224 1.000000 -0.101989 -0.089762 -0.007193 -0.098001 0.040456 -0.022010 -0.303732 -0.218083 -0.077516 -0.059176 -0.045713 0.009599
AWS 0.093495 0.094499 0.005762 NaN -0.101575 -0.101989 1.000000 0.901397 -0.049934 0.815185 0.119652 0.348905 0.052240 0.023160 0.476469 0.673022 0.124770 -0.057979
EWS 0.104406 0.105473 0.013136 NaN 0.005403 -0.089762 0.901397 1.000000 -0.077282 0.724358 0.126556 0.337332 0.020492 0.004827 0.429480 0.662471 0.124653 -0.060519
Safety Stock (in weeks) 0.106713 0.107082 -0.013897 NaN -0.151899 -0.007193 -0.049934 -0.077282 1.000000 0.004422 0.156187 0.141652 0.022715 0.014103 0.016501 0.133298 -0.009922 -0.044616
Safety Stock (to DSP) 0.057478 0.058070 -0.007731 NaN -0.078815 -0.098001 0.815185 0.724358 0.004422 1.000000 0.115285 0.389096 0.015587 -0.004429 0.524366 0.683879 0.148462 -0.043534
PQ 0.246975 0.249395 -0.016753 NaN 0.005241 0.040456 0.119652 0.126556 0.156187 0.115285 1.000000 0.256357 0.014056 0.010104 0.160985 0.197145 0.003739 -0.128938
MPQ 0.127808 0.129122 -0.009219 NaN -0.023870 -0.022010 0.348905 0.337332 0.141652 0.389096 0.256357 1.000000 -0.041468 -0.033496 0.339790 0.633358 0.021294 -0.074098
Lead Time 0.019142 0.018178 -0.000350 NaN -0.117651 -0.303732 0.052240 0.020492 0.022715 0.015587 0.014056 -0.041468 1.000000 0.766165 0.051105 0.007717 -0.013745 0.013144
Lead Time Deviation -0.001275 -0.001743 -0.007076 NaN -0.071948 -0.218083 0.023160 0.004827 0.014103 -0.004429 0.010104 -0.033496 0.766165 1.000000 0.029092 -0.006639 0.001103 0.049688
Actual Inventory 0.106766 0.107352 -0.012635 NaN -0.089763 -0.077516 0.476469 0.429480 0.016501 0.524366 0.160985 0.339790 0.051105 0.029092 1.000000 0.561667 0.089582 -0.051915
Sales Space Capacity 0.120747 0.121518 0.004916 NaN -0.035269 -0.059176 0.673022 0.662471 0.133298 0.683879 0.197145 0.633358 0.007717 -0.006639 0.561667 1.000000 0.114178 -0.071013
Maximum Delivery Quantity -0.011653 -0.011703 -0.004438 NaN -0.014456 -0.045713 0.124770 0.124653 -0.009922 0.148462 0.003739 0.021294 -0.013745 0.001103 0.089582 0.114178 1.000000 0.005639
Unit Volume -0.265174 -0.267383 -0.009069 NaN 0.013461 0.009599 -0.057979 -0.060519 -0.044616 -0.043534 -0.128938 -0.074098 0.013144 0.049688 -0.051915 -0.071013 0.005639 1.000000
In [46]:
fig = plt.figure(figsize=(8,8))
plt.matshow(corr, cmap='RdBu', fignum=fig.number)
plt.xticks(range(len(corr.columns)), corr.columns, rotation='vertical');
plt.yticks(range(len(corr.columns)), corr.columns);
In [52]:
df.plot(kind='scatter', x='AWS', y='Unit Volume', figsize=(6,6))
Out[52]:
<matplotlib.axes._subplots.AxesSubplot at 0x7fda2e9a0df0>
In [50]:
df.plot(kind='scatter', x='AWS', y='Sales Space Capacity', figsize=(6,6))
Out[50]:
<matplotlib.axes._subplots.AxesSubplot at 0x7fda2ea00af0>
In [56]:
ax = df[['Sales Space Capacity', 'HFB']].boxplot(by='HFB', figsize=(10,6))
ax.set_ylabel('Sales Space Capacity')
Out[56]:
Text(0, 0.5, 'Sales Space Capacity')
In [59]:
boxplot_cols = ['HFB', 'Service Level', 'Lead Time', 'PQ', 'Actual Inventory', 'Sales Space Capacity']

df[boxplot_cols].plot(kind='box', subplots=True, layout=(2,3), figsize=(14,8))
Out[59]:
HFB                        AxesSubplot(0.125,0.536818;0.227941x0.343182)
Service Level           AxesSubplot(0.398529,0.536818;0.227941x0.343182)
Lead Time               AxesSubplot(0.672059,0.536818;0.227941x0.343182)
PQ                            AxesSubplot(0.125,0.125;0.227941x0.343182)
Actual Inventory           AxesSubplot(0.398529,0.125;0.227941x0.343182)
Sales Space Capacity       AxesSubplot(0.672059,0.125;0.227941x0.343182)
dtype: object

green-divider

Notebooks AI
Notebooks AI Profile20060