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	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	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	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	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	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	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	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	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	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	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	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	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

AymanHidanAymanHidan

Ikea TRO

Total Range Overview¶

Hands on!¶

Loading our data:¶

The data at a glance:¶

Numerical analysis and visualization¶

Categorical analysis and visualization¶

Relationship between the columns?¶