Rossman Data Preprocessing

In [13]:

# Structures data library
from fastai.fastai.structured import *

# Columnar data library
from fastai.fastai.column_data import *

from IPython.display import HTML

In [2]:

PATH = 'data/rossman/'

In [6]:

!kaggle competitions download -c rossmann-store-sales --path='{PATH}'

sample_submission.csv.zip: Downloaded 55KB of 55KB
store.csv.zip: Downloaded 8KB of 8KB
test.csv.zip: Downloaded 143KB of 143KB
train.csv.zip: Downloaded 6MB of 6MB

In [7]:

!ls -l {PATH}

total 12016
-rw-r--r--  1 lex  staff    56579 16 Apr 17:13 sample_submission.csv.zip
-rw-r--r--  1 lex  staff     8529 16 Apr 17:13 store.csv.zip
-rw-r--r--  1 lex  staff   146686 16 Apr 17:13 test.csv.zip
-rw-r--r--  1 lex  staff  5931096 16 Apr 17:13 train.csv.zip

In [8]:

for file in os.listdir(PATH):
    if not file.endswith('zip'):
        continue
        
    !unzip -q -d {PATH} {PATH}{file}

In [9]:

!wget http://files.fast.ai/part2/lesson14/rossmann.tgz --directory-prefix={PATH}

--2018-04-16 17:13:45--  http://files.fast.ai/part2/lesson14/rossmann.tgz
Resolving files.fast.ai... 67.205.15.147
Connecting to files.fast.ai|67.205.15.147|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7730448 (7.4M) [text/plain]
Saving to: ‘data/rossman/rossmann.tgz’

rossmann.tgz        100%[===================>]   7.37M  2.03MB/s    in 5.3s    

2018-04-16 17:13:51 (1.39 MB/s) - ‘data/rossman/rossmann.tgz’ saved [7730448/7730448]

In [10]:

!tar -zxvf {PATH}rossmann.tgz -C {PATH}

x googletrend.csv
x sample_submission.csv
x state_names.csv
x store.csv
x store_states.csv
x test.csv
x train.csv
x weather.csv

Feature Space:
- train.csv: Training set provided by competition
- store.csv: List of stores
- store_states.csv: mapping of store to the German state they are in
- state_names.csv: List of German state names
- googletrend.csv: trend of certain google keywords over time, found by users to correlate well with given data.
- weather.csv: weather
- test.csv: testing set

In [11]:

table_names = [
    'train', 'store', 'store_states', 'state_names',
    'googletrend', 'weather', 'test'
]

Load all the csvs as a list of dataframes:

In [12]:

tables = [pd.read_csv(f'{PATH}{fname}.csv', low_memory=False) for fname in table_names]

Contents of the tables:

In [14]:

for t in tables: display(t.head())

	Store	DayOfWeek	Date	Sales	Customers	Open	Promo	SchoolHoliday
0	1	5	2015-07-31	5263	555	1	1	1
1	2	5	2015-07-31	6064	625	1	1	1
2	3	5	2015-07-31	8314	821	1	1	1
3	4	5	2015-07-31	13995	1498	1	1	1
4	5	5	2015-07-31	4822	559	1	1	1

	Store	StoreType	Assortment	CompetitionDistance	CompetitionOpenSinceMonth	CompetitionOpenSinceYear	Promo2	Promo2SinceWeek	Promo2SinceYear	PromoInterval
0	1	c	a	1270.0	9.0	2008.0	0	NaN	NaN	NaN
1	2	a	a	570.0	11.0	2007.0	1	13.0	2010.0	Jan,Apr,Jul,Oct
2	3	a	a	14130.0	12.0	2006.0	1	14.0	2011.0	Jan,Apr,Jul,Oct
3	4	c	c	620.0	9.0	2009.0	0	NaN	NaN	NaN
4	5	a	a	29910.0	4.0	2015.0	0	NaN	NaN	NaN

	Store	State
0	1	HE
1	2	TH
2	3	NW
3	4	BE
4	5	SN

	StateName	State
0	BadenWuerttemberg	BW
1	Bayern	BY
2	Berlin	BE
3	Brandenburg	BB
4	Bremen	HB

	file	week	trend
0	Rossmann_DE_SN	2012-12-02 - 2012-12-08	96
1	Rossmann_DE_SN	2012-12-09 - 2012-12-15	95
2	Rossmann_DE_SN	2012-12-16 - 2012-12-22	91
3	Rossmann_DE_SN	2012-12-23 - 2012-12-29	48
4	Rossmann_DE_SN	2012-12-30 - 2013-01-05	67

	file	Date	Max_TemperatureC	Mean_TemperatureC	Min_TemperatureC	Dew_PointC	MeanDew_PointC	Min_DewpointC	Max_Humidity	Mean_Humidity	...	Max_VisibilityKm	Mean_VisibilityKm	Min_VisibilitykM	Max_Wind_SpeedKm_h	Mean_Wind_SpeedKm_h	Max_Gust_SpeedKm_h	Precipitationmm	CloudCover	Events	WindDirDegrees
0	NordrheinWestfalen	2013-01-01	8	4	2	7	5	1	94	87	...	31.0	12.0	4.0	39	26	58.0	5.08	6.0	Rain	215
1	NordrheinWestfalen	2013-01-02	7	4	1	5	3	2	93	85	...	31.0	14.0	10.0	24	16	NaN	0.00	6.0	Rain	225
2	NordrheinWestfalen	2013-01-03	11	8	6	10	8	4	100	93	...	31.0	8.0	2.0	26	21	NaN	1.02	7.0	Rain	240
3	NordrheinWestfalen	2013-01-04	9	9	8	9	9	8	100	94	...	11.0	5.0	2.0	23	14	NaN	0.25	7.0	Rain	263
4	NordrheinWestfalen	2013-01-05	8	8	7	8	7	6	100	94	...	10.0	6.0	3.0	16	10	NaN	0.00	7.0	Rain	268

5 rows × 24 columns

	Id	Store	DayOfWeek	Date	Open	Promo
0	1	1	4	2015-09-17	1.0	1
1	2	3	4	2015-09-17	1.0	1
2	3	7	4	2015-09-17	1.0	1
3	4	8	4	2015-09-17	1.0	1
4	5	9	4	2015-09-17	1.0	1

View summarised agg infomation for each table:

In [15]:

for t in tables: display(DataFrameSummary(t).summary())

	Store	DayOfWeek	Date	Sales	Customers	Open	Promo	StateHoliday	SchoolHoliday
count	1.01721e+06	1.01721e+06	NaN	1.01721e+06	1.01721e+06	1.01721e+06	1.01721e+06	NaN	1.01721e+06
mean	558.43	3.99834	NaN	5773.82	633.146	0.830107	0.381515	NaN	0.178647
std	321.909	1.99739	NaN	3849.93	464.412	0.375539	0.485759	NaN	0.383056
min	1	1	NaN	0	0	0	0	NaN	0
25%	280	2	NaN	3727	405	1	0	NaN	0
50%	558	4	NaN	5744	609	1	0	NaN	0
75%	838	6	NaN	7856	837	1	1	NaN	0
max	1115	7	NaN	41551	7388	1	1	NaN	1
counts	1017209	1017209	1017209	1017209	1017209	1017209	1017209	1017209	1017209
uniques	1115	7	942	21734	4086	2	2	4	2
missing	0	0	0	0	0	0	0	0	0
missing_perc	0%	0%	0%	0%	0%	0%	0%	0%	0%
types	numeric	numeric	categorical	numeric	numeric	bool	bool	categorical	bool

	Store	StoreType	Assortment	CompetitionDistance	CompetitionOpenSinceMonth	CompetitionOpenSinceYear	Promo2	Promo2SinceWeek	Promo2SinceYear	PromoInterval
count	1115	NaN	NaN	1112	761	761	1115	571	571	NaN
mean	558	NaN	NaN	5404.9	7.2247	2008.67	0.512108	23.5954	2011.76	NaN
std	322.017	NaN	NaN	7663.17	3.21235	6.19598	0.500078	14.142	1.67494	NaN
min	1	NaN	NaN	20	1	1900	0	1	2009	NaN
25%	279.5	NaN	NaN	717.5	4	2006	0	13	2011	NaN
50%	558	NaN	NaN	2325	8	2010	1	22	2012	NaN
75%	836.5	NaN	NaN	6882.5	10	2013	1	37	2013	NaN
max	1115	NaN	NaN	75860	12	2015	1	50	2015	NaN
counts	1115	1115	1115	1112	761	761	1115	571	571	571
uniques	1115	4	3	654	12	23	2	24	7	3
missing	0	0	0	3	354	354	0	544	544	544
missing_perc	0%	0%	0%	0.27%	31.75%	31.75%	0%	48.79%	48.79%	48.79%
types	numeric	categorical	categorical	numeric	numeric	numeric	bool	numeric	numeric	categorical

	Store	State
count	1115	NaN
mean	558	NaN
std	322.017	NaN
min	1	NaN
25%	279.5	NaN
50%	558	NaN
75%	836.5	NaN
max	1115	NaN
counts	1115	1115
uniques	1115	12
missing	0	0
missing_perc	0%	0%
types	numeric	categorical

	StateName	State
count	16	16
unique	16	16
top	NordrheinWestfalen	SL
freq	1	1
counts	16	16
uniques	16	16
missing	0	0
missing_perc	0%	0%
types	unique	unique

	file	week	trend
count	NaN	NaN	2072
mean	NaN	NaN	63.8142
std	NaN	NaN	12.6502
min	NaN	NaN	0
25%	NaN	NaN	55
50%	NaN	NaN	64
75%	NaN	NaN	72
max	NaN	NaN	100
counts	2072	2072	2072
uniques	14	148	68
missing	0	0	0
missing_perc	0%	0%	0%
types	categorical	categorical	numeric

	file	Date	Max_TemperatureC	Mean_TemperatureC	Min_TemperatureC	Dew_PointC	MeanDew_PointC	Min_DewpointC	Max_Humidity	Mean_Humidity	...	Max_VisibilityKm	Mean_VisibilityKm	Min_VisibilitykM	Max_Wind_SpeedKm_h	Mean_Wind_SpeedKm_h	Max_Gust_SpeedKm_h	Precipitationmm	CloudCover	Events	WindDirDegrees
count	NaN	NaN	15840	15840	15840	15840	15840	15840	15840	15840	...	15459	15459	15459	15840	15840	3604	15840	14667	NaN	15840
mean	NaN	NaN	14.6441	10.389	6.19899	8.58782	6.20581	3.62614	93.6596	74.2829	...	24.0576	12.2398	7.02516	22.7666	11.9722	48.8643	0.831718	5.55131	NaN	175.897
std	NaN	NaN	8.64601	7.37926	6.52639	6.24478	6.08677	6.12839	7.67853	13.4866	...	8.9768	5.06794	4.9806	8.98862	5.87284	13.027	2.51351	1.68771	NaN	101.589
min	NaN	NaN	-11	-13	-15	-14	-15	-73	44	30	...	0	0	0	3	2	21	0	0	NaN	-1
25%	NaN	NaN	8	4	1	4	2	-1	90.75	65	...	14	10	3	16	8	39	0	5	NaN	80
50%	NaN	NaN	15	11	7	9	7	4	94	76	...	31	11	7	21	11	48	0	6	NaN	202
75%	NaN	NaN	21	16	11	13	11	8	100	85	...	31	14	10	27	14	55	0.25	7	NaN	256
max	NaN	NaN	39	31	24	25	20	19	100	100	...	31	31	31	101	53	111	58.93	8	NaN	360
counts	15840	15840	15840	15840	15840	15840	15840	15840	15840	15840	...	15459	15459	15459	15840	15840	3604	15840	14667	11889	15840
uniques	16	990	51	45	40	40	36	40	53	71	...	24	32	24	44	29	47	41	9	21	362
missing	0	0	0	0	0	0	0	0	0	0	...	381	381	381	0	0	12236	0	1173	3951	0
missing_perc	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	...	2.41%	2.41%	2.41%	0%	0%	77.25%	0%	7.41%	24.94%	0%
types	categorical	categorical	numeric	numeric	numeric	numeric	numeric	numeric	numeric	numeric	...	numeric	numeric	numeric	numeric	numeric	numeric	numeric	numeric	categorical	numeric

13 rows × 24 columns

	Id	Store	DayOfWeek	Date	Open	Promo	StateHoliday	SchoolHoliday
count	41088	41088	41088	NaN	41077	41088	NaN	41088
mean	20544.5	555.9	3.97917	NaN	0.854322	0.395833	NaN	0.443487
std	11861.2	320.274	2.01548	NaN	0.352787	0.489035	NaN	0.496802
min	1	1	1	NaN	0	0	NaN	0
25%	10272.8	279.75	2	NaN	1	0	NaN	0
50%	20544.5	553.5	4	NaN	1	0	NaN	0
75%	30816.2	832.25	6	NaN	1	1	NaN	1
max	41088	1115	7	NaN	1	1	NaN	1
counts	41088	41088	41088	41088	41077	41088	41088	41088
uniques	41088	856	7	48	2	2	2	2
missing	0	0	0	0	11	0	0	0
missing_perc	0%	0%	0%	0%	0.03%	0%	0%	0%
types	numeric	numeric	numeric	categorical	bool	bool	bool	bool

Data cleaning / feature engineering

Since it's a structured data problem, you still need to perform cleaning and feature engineering, even though it's going to use a neural network.

In [16]:

train, store, store_states, state_names, googletrend, weather, test = tables

In [17]:

len(train), len(test)

Out[17]:

(1017209, 41088)

1. Convert state holidays into boolean

using calculations on pandas fields, we can convert state holidays into boolean values:

In [18]:

train.StateHoliday.head()

Out[18]:

0    0
1    0
2    0
3    0
4    0
Name: StateHoliday, dtype: object

In [19]:

train.StateHoliday = train.StateHoliday != '0'
test.StateHoliday = test.StateHoliday != '0'

In [20]:

train.StateHoliday.head()

Out[20]:

0    False
1    False
2    False
3    False
4    False
Name: StateHoliday, dtype: bool

2. Join tables

Perform a left outer join of right on the left argument using given fields.
Pandas performs joins using the merge method.
- suffixes describes the naming convention for dupe fields.
  - In below example, leaving dupe fields on the left untouched and append _y to the right:

In [21]:

def join_df(left, right, left_on, right_on=None, suffix='_y'):
    if right_on is None: right_on = left_on
    return left.merge(
        right, how='left', left_on=left_on, right_on=right_on,
        suffixes=('', suffix))

Join weather/state names:

In [22]:

weather = join_df(weather, state_names, 'file', 'StateName')

In [23]:

weather.head()

Out[23]:

	file	Date	Max_TemperatureC	Mean_TemperatureC	Min_TemperatureC	Dew_PointC	MeanDew_PointC	Min_DewpointC	Max_Humidity	Mean_Humidity	...	Min_VisibilitykM	Max_Wind_SpeedKm_h	Mean_Wind_SpeedKm_h	Max_Gust_SpeedKm_h	Precipitationmm	CloudCover	Events	WindDirDegrees	StateName	State
0	NordrheinWestfalen	2013-01-01	8	4	2	7	5	1	94	87	...	4.0	39	26	58.0	5.08	6.0	Rain	215	NordrheinWestfalen	NW
1	NordrheinWestfalen	2013-01-02	7	4	1	5	3	2	93	85	...	10.0	24	16	NaN	0.00	6.0	Rain	225	NordrheinWestfalen	NW
2	NordrheinWestfalen	2013-01-03	11	8	6	10	8	4	100	93	...	2.0	26	21	NaN	1.02	7.0	Rain	240	NordrheinWestfalen	NW
3	NordrheinWestfalen	2013-01-04	9	9	8	9	9	8	100	94	...	2.0	23	14	NaN	0.25	7.0	Rain	263	NordrheinWestfalen	NW
4	NordrheinWestfalen	2013-01-05	8	8	7	8	7	6	100	94	...	3.0	16	10	NaN	0.00	7.0	Rain	268	NordrheinWestfalen	NW

5 rows × 26 columns

3. Ensure formatting is consistent

Can add new columns to a Dataframe by just defining them.
Want to extract dates and state names, then change state name NI to match result of the data: HB,NI.
- Using .loc[rows, cols] we can select a list of rows and columns from the dataset, and use to replace.

In [24]:

googletrend.head()

Out[24]:

	file	week	trend
0	Rossmann_DE_SN	2012-12-02 - 2012-12-08	96
1	Rossmann_DE_SN	2012-12-09 - 2012-12-15	95
2	Rossmann_DE_SN	2012-12-16 - 2012-12-22	91
3	Rossmann_DE_SN	2012-12-23 - 2012-12-29	48
4	Rossmann_DE_SN	2012-12-30 - 2013-01-05	67

In [25]:

googletrend['Date'] = googletrend.week.str.split(' - ', expand=True)[0]
googletrend['State'] = googletrend.file.str.split('_', expand=True)[2]

In [26]:

googletrend.head()

Out[26]:

	file	week	trend	Date	State
0	Rossmann_DE_SN	2012-12-02 - 2012-12-08	96	2012-12-02	SN
1	Rossmann_DE_SN	2012-12-09 - 2012-12-15	95	2012-12-09	SN
2	Rossmann_DE_SN	2012-12-16 - 2012-12-22	91	2012-12-16	SN
3	Rossmann_DE_SN	2012-12-23 - 2012-12-29	48	2012-12-23	SN
4	Rossmann_DE_SN	2012-12-30 - 2013-01-05	67	2012-12-30	SN

In [27]:

googletrend.loc[googletrend.State=='NI', "State"].head()

Out[27]:

1184    NI
1185    NI
1186    NI
1187    NI
1188    NI
Name: State, dtype: object

In [28]:

googletrend.loc[googletrend.State=='HB,NI', "State"].head()

Out[28]:

Series([], Name: State, dtype: object)

In [29]:

googletrend.loc[googletrend.State=='NI', "State"] = 'HB,NI'

In [30]:

googletrend.loc[googletrend.State=='NI', "State"].head()

Out[30]:

Series([], Name: State, dtype: object)

In [31]:

googletrend.loc[googletrend.State=='HB,NI', "State"].head()

Out[31]:

1184    HB,NI
1185    HB,NI
1186    HB,NI
1187    HB,NI
1188    HB,NI
Name: State, dtype: object

4. Extract date fields from datetime to use categorically

Should always consider this feature extraction step.
- Without adding these additional fields, you can't capture trend/cyclical behaviour as a function of time.

In [32]:

add_datepart

Out[32]:

<function fastai.fastai.structured.add_datepart(df, fldname, drop=True)>

In [33]:

googletrend.head()

Out[33]:

	file	week	trend	Date	State
0	Rossmann_DE_SN	2012-12-02 - 2012-12-08	96	2012-12-02	SN
1	Rossmann_DE_SN	2012-12-09 - 2012-12-15	95	2012-12-09	SN
2	Rossmann_DE_SN	2012-12-16 - 2012-12-22	91	2012-12-16	SN
3	Rossmann_DE_SN	2012-12-23 - 2012-12-29	48	2012-12-23	SN
4	Rossmann_DE_SN	2012-12-30 - 2013-01-05	67	2012-12-30	SN

In [34]:

add_datepart(weather, "Date", drop=False)
add_datepart(googletrend, "Date", drop=False)
add_datepart(train, "Date", drop=False)
add_datepart(test, "Date", drop=False)

In [35]:

googletrend.head()

Out[35]:

	file	week	trend	Date	State	Year	Month	Week	Day	Dayofweek	Dayofyear	Is_month_end	Is_month_start	Is_quarter_end	Is_quarter_start	Is_year_end	Is_year_start	Elapsed
0	Rossmann_DE_SN	2012-12-02 - 2012-12-08	96	2012-12-02	SN	2012	12	48	2	6	337	False	False	False	False	False	False	1354406400
1	Rossmann_DE_SN	2012-12-09 - 2012-12-15	95	2012-12-09	SN	2012	12	49	9	6	344	False	False	False	False	False	False	1355011200
2	Rossmann_DE_SN	2012-12-16 - 2012-12-22	91	2012-12-16	SN	2012	12	50	16	6	351	False	False	False	False	False	False	1355616000
3	Rossmann_DE_SN	2012-12-23 - 2012-12-29	48	2012-12-23	SN	2012	12	51	23	6	358	False	False	False	False	False	False	1356220800
4	Rossmann_DE_SN	2012-12-30 - 2013-01-05	67	2012-12-30	SN	2012	12	52	30	6	365	False	False	False	False	False	False	1356825600

5. Pull out the Google trends category just for Germany

In [36]:

trend_de = googletrend[googletrend.file == 'Rossmann_DE']

6. Join all data into a single DF

Now do an outer join on all data.
- For outer joins, every time the joining field on the left table doesn't have a corresponding value on the right, you get Null values for all right table fields.
- You could do a Null check post join to check this.

In [37]:

store = join_df(store, store_states, "Store")
len(store[store.State.isnull()])

Out[37]:

In [38]:

joined = join_df(train, store, "Store")
joined_test = join_df(test, store, "Store")
len(joined[joined.StoreType.isnull()]),len(joined_test[joined_test.StoreType.isnull()])

Out[38]:

(0, 0)

In [39]:

joined = join_df(joined, googletrend, ["State","Year", "Week"])
joined_test = join_df(joined_test, googletrend, ["State","Year", "Week"])
len(joined[joined.trend.isnull()]),len(joined_test[joined_test.trend.isnull()])

Out[39]:

(0, 0)

In [40]:

joined = joined.merge(trend_de, 'left', ["Year", "Week"], suffixes=('', '_DE'))
joined_test = joined_test.merge(trend_de, 'left', ["Year", "Week"], suffixes=('', '_DE'))
len(joined[joined.trend_DE.isnull()]),len(joined_test[joined_test.trend_DE.isnull()])

Out[40]:

(0, 0)

In [41]:

joined = join_df(joined, weather, ["State","Date"])
joined_test = join_df(joined_test, weather, ["State","Date"])
len(joined[joined.Mean_TemperatureC.isnull()]),len(joined_test[joined_test.Mean_TemperatureC.isnull()])

Out[41]:

(0, 0)

In [42]:

for df in (joined, joined_test):
    for c in df.columns:
        if c.endswith('_y'):
            if c in df.columns: df.drop(c, inplace=True, axis=1)

7. Fill in missing values

Replace NAs with a "signal value", which is a value that doesn't otherwise appear in the data.

In [43]:

for df in (joined, joined_test):
    df['CompetitionOpenSinceYear'] = df.CompetitionOpenSinceYear.fillna(1900).astype(np.int32)
    df['CompetitionOpenSinceMonth'] = df.CompetitionOpenSinceMonth.fillna(1).astype(np.int32)
    df['Promo2SinceYear'] = df.Promo2SinceYear.fillna(1900).astype(np.int32)
    df['Promo2SinceWeek'] = df.Promo2SinceWeek.fillna(1).astype(np.int32)

In [48]:

df[df['Promo2SinceYear'] == 1900].head()

Out[48]:

	Id	Store	DayOfWeek	Date	Open	Promo	StateHoliday	Year	Month	...	Mean_VisibilityKm	Min_VisibilitykM	Max_Wind_SpeedKm_h	Mean_Wind_SpeedKm_h	Max_Gust_SpeedKm_h	Precipitationmm	CloudCover	Events	WindDirDegrees	StateName
0	1	1	4	2015-09-17	1.0	1	False	2015	9	...	12.0	7.0	29	14	NaN	7.11	6.0	Rain	191	Hessen
2	3	7	4	2015-09-17	1.0	1	False	2015	9	...	10.0	2.0	29	14	45.0	0.76	5.0	Rain	209	SchleswigHolstein
3	4	8	4	2015-09-17	1.0	1	False	2015	9	...	10.0	2.0	29	14	45.0	0.76	5.0	Rain	209	SchleswigHolstein
4	5	9	4	2015-09-17	1.0	1	False	2015	9	...	12.0	8.0	37	26	53.0	3.05	6.0	Rain	187	NordrheinWestfalen
5	6	10	4	2015-09-17	1.0	1	False	2015	9	...	10.0	8.0	37	29	58.0	3.05	6.0	Rain	201	Niedersachsen

5 rows × 73 columns

8. Extract features

Extract CompetitionOpenSince and CompetitionDaysOpen and replace bad data.

In [49]:

for df in (joined,joined_test):
    df["CompetitionOpenSince"] = pd.to_datetime(dict(year=df.CompetitionOpenSinceYear, 
                                                     month=df.CompetitionOpenSinceMonth, day=15))
    df["CompetitionDaysOpen"] = df.Date.subtract(df.CompetitionOpenSince).dt.days

In [50]:

for df in (joined,joined_test):
    df.loc[df.CompetitionDaysOpen<0, "CompetitionDaysOpen"] = 0
    df.loc[df.CompetitionOpenSinceYear<1990, "CompetitionDaysOpen"] = 0

Add CompetitionMonthsOpen limiting max to 2 years to limit number of categories:

In [51]:

for df in (joined,joined_test):
    df["CompetitionMonthsOpen"] = df["CompetitionDaysOpen"]//30
    df.loc[df.CompetitionMonthsOpen>24, "CompetitionMonthsOpen"] = 24
joined.CompetitionMonthsOpen.unique()

Out[51]:

array([24,  3, 19,  9,  0, 16, 17,  7, 15, 22, 11, 13,  2, 23, 12,  4, 10,  1, 14, 20,  8, 18,  6, 21,  5])

Same process for Promo dates:

In [52]:

for df in (joined,joined_test):
    df["Promo2Since"] = pd.to_datetime(df.apply(lambda x: Week(
        x.Promo2SinceYear, x.Promo2SinceWeek).monday(), axis=1).astype(pd.datetime))
    df["Promo2Days"] = df.Date.subtract(df["Promo2Since"]).dt.days

In [53]:

for df in (joined,joined_test):
    df.loc[df.Promo2Days<0, "Promo2Days"] = 0
    df.loc[df.Promo2SinceYear<1990, "Promo2Days"] = 0
    df["Promo2Weeks"] = df["Promo2Days"]//7
    df.loc[df.Promo2Weeks<0, "Promo2Weeks"] = 0
    df.loc[df.Promo2Weeks>25, "Promo2Weeks"] = 25
    df.Promo2Weeks.unique()

Save data prepared so far:

In [54]:

joined.to_feather(f'{PATH}joined')
joined_test.to_feather(f'{PATH}joined_test')

In [55]:

!ls -lh {PATH}join*

-rw-r--r--  1 lex  staff   561M 16 Apr 17:42 data/rossman/joined
-rw-r--r--  1 lex  staff    22M 16 Apr 17:42 data/rossman/joined_test

9. Durations

It's common when working with time series data to extract data that explains relationships across rows and columns like:
- Running averages
- Time until next event
- Time since last event
Fast.ai created a class to deal with this sort of data.
Create a function get_elaspsed which performs cumulative counting across a sorted dataframe.
- Field fld is monitored and tracks time since last occurance of the field.
  - When field is seen again, set tracking time to zero.

In [96]:

def get_elapsed(fld, pre, df=df):
    day1 = np.timedelta64(1, 'D')
    last_date = np.datetime64()
    last_store = 0
    res = []
    
    for s,v,d in zip(df.Store.values,df[fld].values, df.Date.values):
        if s != last_store:
            last_date = np.datetime64()
            last_store = s
        if v:
            last_date = d
        res.append(((d-last_date).astype('timedelta64[D]') / day1))
    df[pre+fld] = res

In [57]:

columns = ['Date', 'Store', 'Promo', 'StateHoliday', 'SchoolHoliday']

In [59]:

df = train[columns]

School holiday example:
- Sort by Store, then Date, then call add_elapsed('SchoolHoliday', 'After'), which applies to each row with School Holiday:
  - Adds datframe with days since seeing a School Holiday.
  - In other direction, it's days until a holiday.

In [61]:

df.head()

Out[61]:

	Date	Store	Promo	StateHoliday	SchoolHoliday
0	2015-07-31	1	1	False	1
1	2015-07-31	2	1	False	1
2	2015-07-31	3	1	False	1
3	2015-07-31	4	1	False	1
4	2015-07-31	5	1	False	1

In [62]:

fld = 'SchoolHoliday'
df = df.sort_values(['Store', 'Date'])
get_elapsed(fld, 'After')
df = df.sort_values(['Store', 'Date'], ascending=[True, False])
get_elapsed(fld, 'Before')

In [67]:

df[df['SchoolHoliday'] == 0].head()

Out[67]:

	Date	Store	StateHoliday	AfterSchoolHoliday	BeforeSchoolHoliday	AfterStateHoliday	BeforeStateHoliday	AfterPromo	BeforePromo
5575	2015-07-26	1	False	107.0	-1.0	52.0	NaN	9.0	-1.0
6690	2015-07-25	1	False	106.0	-2.0	51.0	NaN	8.0	-2.0
7805	2015-07-24	1	False	105.0	-3.0	50.0	NaN	7.0	-3.0
8920	2015-07-23	1	False	104.0	-4.0	49.0	NaN	6.0	-4.0
10035	2015-07-22	1	False	103.0	-5.0	48.0	NaN	5.0	-5.0

Do it for 2 more fields:

In [64]:

fld = 'StateHoliday'
df = df.sort_values(['Store', 'Date'])
get_elapsed(fld, 'After')
df = df.sort_values(['Store', 'Date'], ascending=[True, False])
get_elapsed(fld, 'Before')

In [65]:

fld = 'Promo'
df = df.sort_values(['Store', 'Date'])
get_elapsed(fld, 'After')
df = df.sort_values(['Store', 'Date'], ascending=[True, False])
get_elapsed(fld, 'Before')

set active index to Date:

In [69]:

df = df.set_index("Date")

Set null values to elapsed field calculations:

In [70]:

columns = ['SchoolHoliday', 'StateHoliday', 'Promo']

In [71]:

for o in ['Before', 'After']:
    for p in columns:
        a = o + p
        df[a] = df[a].fillna(0).astype(int)

In [72]:

df.head()

Out[72]:

	Store	Promo	StateHoliday	SchoolHoliday	AfterSchoolHoliday	BeforeSchoolHoliday	AfterStateHoliday	BeforeStateHoliday	AfterPromo	BeforePromo
Date
2015-07-31	1	1	False	1	0	0	57	0	0	0
2015-07-30	1	1	False	1	0	0	56	0	0	0
2015-07-29	1	1	False	1	0	0	55	0	0	0
2015-07-28	1	1	False	1	0	0	54	0	0	0
2015-07-27	1	1	False	1	0	0	53	0	0	0

Use window functions in pandas to calculate rolling quantities.
1. Start by sorting by date (sort_index()).
2. Count the number of events of interest (sum()) defined in columns in the following week (rolling())
3. Grouped by Store (groupby()).
4. Do the same in the opposite direction.

In [73]:

bwd = df[['Store'] + columns].sort_index().groupby("Store").rolling(7, min_periods=1).sum()

In [74]:

fwd = df[['Store'] + columns]\
    .sort_index(ascending=False)\
    .groupby("Store")\
    .rolling(7, min_periods=1).sum()

In [75]:

bwd.head()

Out[75]:

		Store	SchoolHoliday	StateHoliday	Promo
Store	Date
1	2013-01-01	1.0	1.0	1.0	0.0
	2013-01-02	2.0	2.0	1.0	0.0
	2013-01-03	3.0	3.0	1.0	0.0
	2013-01-04	4.0	4.0	1.0	0.0
	2013-01-05	5.0	5.0	1.0	0.0

In [76]:

fwd.head()

Out[76]:

		Store	SchoolHoliday	StateHoliday	Promo
Store	Date
1	2015-07-31	1.0	1.0	0.0	1.0
	2015-07-30	2.0	2.0	0.0	2.0
	2015-07-29	3.0	3.0	0.0	3.0
	2015-07-28	4.0	4.0	0.0	4.0
	2015-07-27	5.0	5.0	0.0	5.0

Next we want to drop the Store indices grouped together in the window function. Often in Pandas, there is an option to do this in place. This is time and memory efficient when working with large datasets.

In [77]:

bwd.drop('Store',1,inplace=True)
bwd.reset_index(inplace=True)

In [78]:

fwd.drop('Store',1,inplace=True)
fwd.reset_index(inplace=True)

In [79]:

df.reset_index(inplace=True)

In [80]:

df = df.merge(bwd, 'left', ['Date', 'Store'], suffixes=['', '_bw'])
df = df.merge(fwd, 'left', ['Date', 'Store'], suffixes=['', '_fw'])

In [82]:

df.drop(columns,1,inplace=True)

In [83]:

df.head()

Out[83]:

	Date	Store	AfterStateHoliday	SchoolHoliday_bw	Promo_bw	SchoolHoliday_fw	Promo_fw
0	2015-07-31	1	57	5.0	5.0	1.0	1.0
1	2015-07-30	1	56	4.0	4.0	2.0	2.0
2	2015-07-29	1	55	3.0	3.0	3.0	3.0
3	2015-07-28	1	54	2.0	2.0	4.0	4.0
4	2015-07-27	1	53	1.0	1.0	5.0	5.0

Backup table:

In [84]:

df.to_feather(f'{PATH}df')

In [85]:

df = pd.read_feather(f'{PATH}df')

In [86]:

df["Date"] = pd.to_datetime(df.Date)

In [87]:

df.columns

Out[87]:

Index(['Date', 'Store', 'AfterSchoolHoliday', 'BeforeSchoolHoliday',
       'AfterStateHoliday', 'BeforeStateHoliday', 'AfterPromo', 'BeforePromo',
       'SchoolHoliday_bw', 'StateHoliday_bw', 'Promo_bw', 'SchoolHoliday_fw',
       'StateHoliday_fw', 'Promo_fw'],
      dtype='object')

In [88]:

joined = join_df(joined, df, ['Store', 'Date'])

The original author on Kaggle removed all instances of 0 sales (if it was closed, for example).
- Might have cost them a higher standing because there could be some good information in predicted higher sales after a day of being closed.

In [89]:

joined = joined[joined.Sales!=0]

Back it up.

In [90]:

joined.reset_index(inplace=True)

In [91]:

joined.to_feather(f'{PATH}joined')

Now to do the same for the test set:

In [93]:

test_df.head()

Out[93]:

	Date	Store	Promo	StateHoliday	SchoolHoliday
40232	2015-08-01	1	0	False	1
39376	2015-08-02	1	0	False	1
38520	2015-08-03	1	1	False	1
37664	2015-08-04	1	1	False	1
36808	2015-08-05	1	1	False	1

In [99]:

columns = ['Date', 'Store', 'Promo', 'StateHoliday', 'SchoolHoliday']
test_df = test[columns]

fld = 'SchoolHoliday'
test_df = test_df.sort_values(['Store', 'Date'])
get_elapsed(fld, 'After', df=test_df)
test_df = test_df.sort_values(['Store', 'Date'], ascending=[True, False])
get_elapsed(fld, 'Before', df=test_df)

fld = 'StateHoliday'
test_df = test_df.sort_values(['Store', 'Date'])
get_elapsed(fld, 'After', df=test_df)
test_df = test_df.sort_values(['Store', 'Date'], ascending=[True, False])
get_elapsed(fld, 'Before', df=test_df)

fld = 'Promo'
test_df = test_df.sort_values(['Store', 'Date'])
get_elapsed(fld, 'After', df=test_df)
test_df = test_df.sort_values(['Store', 'Date'], ascending=[True, False])
get_elapsed(fld, 'Before', df=test_df)

test_df = test_df.set_index("Date")

columns = ['SchoolHoliday', 'StateHoliday', 'Promo']

for o in ['Before', 'After']:
    for p in columns:
        a = o + p
        test_df[a] = test_df[a].fillna(0).astype(int)
        
bwd = test_df[['Store'] + columns].sort_index().groupby("Store").rolling(7, min_periods=1).sum()
fwd = test_df[['Store'] + columns].sort_index(ascending=False).groupby("Store").rolling(7, min_periods=1).sum()
    
bwd.drop('Store',1,inplace=True)
bwd.reset_index(inplace=True)

fwd.drop('Store',1,inplace=True)
fwd.reset_index(inplace=True)

test_df.reset_index(inplace=True)

test_df = test_df.merge(bwd, 'left', ['Date', 'Store'], suffixes=['', '_bw'])
test_df = test_df.merge(fwd, 'left', ['Date', 'Store'], suffixes=['', '_fw'])

test_df.drop(columns,1,inplace=True)

test_df.to_feather(f'{PATH}test_df')
test_df = pd.read_feather(f'{PATH}test_df')

test_df["Date"] = pd.to_datetime(test_df.Date)

joined_test = join_df(joined_test, test_df, ['Store', 'Date'])

joined_test.reset_index(inplace=True)

joined_test.to_feather(f'{PATH}joined_test')

Fairly typical set of steps for dealing with structured data.

Fast.ai: Deep Learning 2018 - Lesson 4 (Rossman Data Preprocessing)