<jupyter_start><jupyter_text>Problem Description- The competition is called : **Corporacion Favorita Grocery Sales Forecasting**.- The task is to predict sales in the stores of an Ecuadorian supermarket chain so that they can avoid overstocking.- The given data is a table with the following variables/features: date, store id, item id, sales volume, promotion.- We can see the data as N time series, one per (store, item) combination. Many of these time series are most likely correlated to each other and some sort of dimensional reduction will be most welcome here. - The company also offers some other data sets, such as a list of stores, a time series of daily transactions per store, a list of holidays and events, a list of products by category, and the price of oil, of which a good chunk of the ecuadorian economy is allegedly tied to. These are additional tools to simplify and/or enhance the predictions, and some other external data could also be used in this regard.   Set-up<jupyter_code># DATA MANIPULATION
import numpy as np # linear algebra
import random as rd
import pandas as pd # data processing
import datetime # manipulating date formats
from operator import add # elementwise addition

# VIZUALIZATION
import matplotlib.pyplot as plt # basic plotting
import seaborn # for prettier plots
#import folium # plotting data on interactive maps
%matplotlib inline

# SUPERVISED LEARNING
from sklearn.neural_network import MLPRegressor
from sklearn.ensemble import BaggingRegressor
from sklearn.ensemble import GradientBoostingRegressor
from sklearn.tree import DecisionTreeRegressor
# Reading daily transfers per store
sales = pd.read_csv('../Data/transactions.csv', parse_dates=['date'])

# Reading store list
stores = pd.read_csv('../Data/stores.csv')
stores.type=stores.type.astype('category')

# Reading the holiday and events schedule
holidays=pd.read_csv('../Data/holidays_events.csv', parse_dates=['date'])

# Reading oil
oil=pd.read_csv('../Data/oil_mod.csv', parse_dates=['date'])

# Merge datasets
def merge_sales(sales):
    sales=pd.merge(sales,stores,how='left')
    sales=pd.merge(sales,oil,how='left')
    return sales

sales = merge_sales(sales)<jupyter_output><empty_output><jupyter_text>Feature engineering DateFirst, let's work on creating features derived from the date, which is expected to be an important field as we are working with time series<jupyter_code>def add_date_features(sales):
    sales['year'] = sales['date'].dt.year
    sales['month'] = sales['date'].dt.month
    sales['day'] = sales['date'].dt.day
    sales['week'] = sales['date'].dt.week
    sales['dow'] = sales['date'].dt.dayofweek
    sales['dayofyear'] = sales['date'].dt.dayofyear
    sales['dayoff']=[x in [5,6] for x in sales.dow] ## Weekends

add_date_features(sales)
sales.head()<jupyter_output><empty_output><jupyter_text>Holiday eventsThis events are expected to be correlated with high volume sales<jupyter_code>holidays.head()
# Enable all holidays
def enable_holidays(sales):
    for (d,t,l,n) in zip(holidays.date,holidays.type,holidays.locale,holidays.locale_name):
        if t!='Work Day' and t!='Event':  
            if l=='National':
                sales.loc[sales.date==d,'dayoff']=True
            elif l=='Regional':
                sales.loc[(sales.date==d)&(sales.state==n),'dayoff']=True
            else:
                sales.loc[(sales.date==d)&(sales.city==n),'dayoff']=True
        else:
            sales.loc[(sales.date==d),'dayoff']=False

enable_holidays(sales)
## Some manual verifications
sales.loc[lambda df: df.date=='2015-01-10'].head()
#sales.loc[lambda df: (df.date=='2017-04-13') & (df.city=='Cuenca')].head()
#sales.loc[lambda df: (df.date=='2013-04-01') & (df.state=='Cotopaxi')].head()<jupyter_output><empty_output><jupyter_text>Transactions<jupyter_code># Transformation
def transform_transactions(sales):
    sales['transactions'] = np.log1p(sales.transactions)

transform_transactions(sales)

# Normalized
#sales['transactions3'] = (sales['transactions2'] - sales['transactions2'].mean()) / sales['transactions2'].std()
# Normalize independently 

# Histograms
#plt.figure(figsize=(15,5))
#sales.transactions.hist(ax=plt.subplot(1,3,1))
#sales.transactions2.hist(ax=plt.subplot(1,3,2))
#sales.transactions3.hist(ax=plt.subplot(1,3,3))
# Denormalize
def denormalize_target(sales, target, transform=False):
    target_std = sales['transactions2'].std()
    target_mean = sales['transactions2'].mean()

    out = target * target_std + target_mean
    if transform:
        out = np.expm1(out)
    
    return out
    
#test = denormalize_target(sales, sales.transactions3, True)
#test.hist()<jupyter_output><empty_output><jupyter_text>Both distributions are skewed. But the transformed looks more normal  Categorical featuresUse one-hot encoding for city, state, type.This might create hundreds of features, which could be restricting given the amount of data.<jupyter_code>def encode(df, column) -> pd.DataFrame:
    one_hot = pd.get_dummies(df[column], drop_first=False, prefix=column)
    #return (one_hot - one_hot.mean()) / one_hot.std()
    return one_hot

def encode_categorical_features(sales):
    cat_columns = ['store_nbr','city', 'state', 'type']
    
    for column in cat_columns:
        column_enc = encode(sales, column)
        sales = pd.concat([sales,column_enc], axis=1)
    
    return sales


sales = encode_categorical_features(sales)
print_cols = [c for c in sales.columns if 
             not c.startswith('store_nbr_') and 
             not c.startswith('city_') and 
             not c.startswith('state') and 
             not c.startswith('type_')]<jupyter_output><empty_output><jupyter_text>Lagged features: weekly and annual<jupyter_code>#u_dates = sales.date.unique() # There are no records for some dates (eg: 25-dic)
dates_range =  pd.date_range(sales.date.min(), sales.date.max())
u_stores = sales.store_nbr.unique()
def add_lag_features(sales):
    ## Fill missing rows using a product between the stores and the dates (range min-max)
    sales2 = sales.copy()
    sales2.set_index(["date", "store_nbr"], inplace=True)
    sales2 = sales2.reindex(
        pd.MultiIndex.from_product(
            [dates_range, u_stores],
            names=["date", "store_nbr"]
        )
    )
    sales2.sort_index(inplace=True)
    #some_cols2 = [c for c in some_cols if c!='date' and c!='store_nbr']
    
    ## Lag 7
    sales2['lag_7']=np.nan
    sales2['lag_7']=sales2['transactions'].shift(7*len(u_stores))
    print(sales2[['transactions','lag_7']].corr())
    
    ## Lag 14
    #sales2['lag_14']=np.nan
    #sales2['lag_14']=sales2['transactions'].shift(14*len(u_stores))
    #print(sales2[['transactions','lag_14']].corr())
    #It did not reduce error metric
    
    ## Lag 364
    sales2['lag_annual']= np.nan
    sales2['lag_annual']= sales2['transactions'].shift(364*len(u_stores)).values
    print(sales2[['transactions','lag_annual']].corr())

    #sales2['lag_annual']= \
    #    (1 *   sales2['transactions2'].shift(364*len(u_stores)).values + 
    #     1.5 * sales2['transactions2'].shift(365*len(u_stores)).values + 
    #     1 *sales2['transactions2'].shift(366*len(u_stores)).values)/3.5
    # It was not better than (364 shift)

    ## Lag 364*2
    #sales2['lag_annual_']= np.nan
    #sales2['lag_annual_']= sales2['transactions'].shift(364*2*len(u_stores)).values
    #print(sales2[['transactions','lag_annual_']].corr())

    # Delete temporal df
    sales = sales2.reset_index()
    del sales2
    return sales


sales = add_lag_features(sales)<jupyter_output>transactions     lag_7
transactions      1.000000  0.959216
lag_7             0.959216  1.000000
              transactions  lag_annual
transactions      1.000000    0.961503
lag_annual        0.961503    1.000000<jupyter_text>Prediction  Drop nan and sort dataThere is lots of them because of the creation of the lag features<jupyter_code>def clean_data_for_prediction(df):
    # Drop
    print('Dropping nan rows...')
    print("Before: ", df.shape)
    df.dropna(inplace=True)
    print("After: ", df.shape)
    
    # Sort
    print('Sorting')
    df.sort_values(['store_nbr', 'date'], ascending=[True, True], inplace=True)
    df = df.reindex()
    
    return df

sales = clean_data_for_prediction(sales)<jupyter_output>Dropping nan rows...
Before:  (91152, 114)
After:  (63254, 114)
Sorting<jupyter_text>Splitting data<jupyter_code>cols = [c for c in sales if c not in ['date','store_nbr','type','city','state',
                                   'transactions','transactions','transactions3',
                                   'prediction']]

X1 = sales.loc[(sales.date<'2017-08-01') & (sales.date>='2016-01-01')].copy()
X2 = sales.loc[sales.date>='2017-08-01'].copy()
print(X1.shape)
print(X2.shape)

target_column = 'transactions' 
y1 = X1[target_column].values
y2 = X2[target_column].values<jupyter_output>(28918, 114)
(793, 114)<jupyter_text>PCA<jupyter_code>from sklearn import decomposition

pca = decomposition.PCA(n_components=50)
pca.fit(X1[cols])
X1 = pca.transform(X1[cols])
X2 = pca.transform(X2[cols])
print(X1.shape)
print(X2.shape)<jupyter_output>(28918, 50)
(793, 50)<jupyter_text>Regressors<jupyter_code>from sklearn import metrics

np.random.seed(1122)

number_regressors_to_test = 3
for method in range(1, number_regressors_to_test+1):
    print('\nmethod = ', method)
    
    if (method==1):
        print('Multilayer perceptron (MLP) neural network 01')
        str_method = 'MLP model01'    
        r = MLPRegressor(hidden_layer_sizes=(3,), max_iter=100)
    if (method==2):
        print('Bagging Regressor 01')
        str_method = 'BaggingRegressor01'
        r = BaggingRegressor(DecisionTreeRegressor(max_depth=6,max_features=0.85))        

    if (method==3):
        np.random.seed(1122)
        print('GradientBoosting 01')
        str_method = 'GradientBoosting01'
        r = GradientBoostingRegressor(n_estimators=85, max_depth=6, learning_rate = 0.01, 
                                       verbose=0, warm_start=True,
                                       subsample= 0.87, max_features = 0.8)        

    r.fit(X1, y1)
    yh2 = r.predict(X2)
    m = metrics.mean_squared_error(y2, yh2)**0.5

    print("Error: %f" % (m))<jupyter_output>method =  1
Multilayer perceptron (MLP) neural network 01
Error: 0.080096

method =  2
Bagging Regressor 01
Error: 0.111530

method =  3
GradientBoosting 01
Error: 0.238459
{'fraction_non_alphanumeric': 0.12985518365781146, 'fraction_numerical': 0.034142131006042006, 'mean_word_length': 4.001438848920864, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '348477', 'license': 'FTL', 'n_tokens_mistral': 3765, 'n_tokens_neox': 3459, 'n_words': 795, 'path': 'notebooks/Predict_transactions_with_PCA.ipynb', 'repo_name': 'kalyanb29/Corporation-Favorita', 'size': '10427'}