Tabular Playground Series: September 2022 Sales Forecasting

Problem Description:

For this challenge, you will be **predicting a full year worth of sales for 4 items from two competing stores located in six different countries**. This dataset is completely fictional, but contains many effects you see in real-world data, e.g., weekend and holiday effect, seasonality, etc. You are given the challenging task of predicting book sales during the year 2021.

Good luck!

Files

  • train.csv - the training set, which includes the sales data for each date-country-store-item combination.
  • test.csv - the test set; your task is to predict the corresponding item sales for each date-country-store-item combination. Note the Public leaderboard is scored on the first quarter of the test year, and the Private on the remaining.
  • sample_submission.csv - a sample submission file in the correct format

Import Libraries

¶

In [1]:
import pandas as pd
import numpy as np

import seaborn as sns
import matplotlib.pyplot as plt

from sklearn.preprocessing import StandardScaler
from sklearn.pipeline import make_pipeline
from sklearn.linear_model import Ridge

# Forecast
from sklearn.model_selection import GroupKFold
from sklearn.linear_model import Ridge
from sklearn.metrics      import r2_score

EDA

¶

Data Description¶

In [2]:
tr_dat = pd.read_csv("../input/tabular-playground-series-sep-2022/train.csv", parse_dates=["date"])
ts_dat = pd.read_csv("../input/tabular-playground-series-sep-2022/test.csv",  parse_dates=["date"])

print("Data Description: \n")

print("countries: ", tr_dat['country'].unique())
print("stores:    ", tr_dat['store'].unique())
print("products:  ", tr_dat['product'].unique())
print("\nyears in data:", tr_dat['date'].dt.year.unique())

Data Description: 

countries:  ['Belgium' 'France' 'Germany' 'Italy' 'Poland' 'Spain']
stores:     ['KaggleMart' 'KaggleRama']
products:   ['Kaggle Advanced Techniques' 'Kaggle Getting Started'
 'Kaggle Recipe Book' 'Kaggle for Kids: One Smart Goose']

years in data: [2017 2018 2019 2020]

Distribution by Country and Product¶

In [3]:
from matplotlib import ticker

by_country = tr_dat.groupby(['country']).sum()['num_sold'].reset_index().sort_values(by='num_sold')
by_product = tr_dat.groupby(['product']).sum()['num_sold'].reset_index().sort_values(by='num_sold')
by_country['percent'] = np.round(by_country['num_sold']/by_country['num_sold'].sum() * 100, 2)
by_product['percent'] = np.round(by_product['num_sold']/by_product['num_sold'].sum() * 100, 2)

### create bar plots
f,ax=plt.subplots(ncols=2, figsize=(15,5))
sns.barplot(data = by_country, x='country', y='num_sold', ax=ax[0])
sns.barplot(data = by_product, x='product', y='num_sold', ax=ax[1])
ax[1].set_xticklabels(ax[1].get_xticklabels(), rotation=15, ha='right')

ax[0].set_title("Total sales by country in the period 2017-2020", fontweight='bold', fontsize=12)
ax[1].set_title("Total sales by product in the period 2017-2020", fontweight='bold', fontsize=12)

#### define ba labels
ax[0].bar_label(ax[0].containers[0],
                labels=[f"{el}%" for el in by_country['percent'].values], 
                label_type='center', color='white', fontweight='bold')

ax[1].bar_label(ax[1].containers[0],
                labels=[f"{el}%" for el in by_product['percent'].values], 
                label_type='center', color='white', fontweight='bold')

plt.show()

From 2017-2020:

  • A great ammount of sales were achieve from Germany and Belgium.
  • 'Kaggle for Kids: One Smart Goose' was the best seller.
In [4]:
tr_dat['Year'] = tr_dat['date'].dt.strftime('%Y')

data_by_country_year = tr_dat.groupby(['country','Year']).sum()['num_sold'].reset_index()# .sort_values(by=['country','Year'])
data_by_product_year = tr_dat.groupby(['product','Year']).sum()['num_sold'].reset_index()

f,ax=plt.subplots(ncols=2, figsize=(15,5))
sns.lineplot(data=data_by_country_year, x='Year', y='num_sold', hue='country', ax=ax[0])
sns.lineplot(data=data_by_product_year, x='Year', y='num_sold', hue='product', ax=ax[1])

ax[0].yaxis.set_major_locator(ticker.MultipleLocator(1e5))
ax[1].yaxis.set_major_locator(ticker.MultipleLocator(1e5))

ax[0].set_title("Total sales by country per year", fontweight='bold', fontsize=12)
ax[1].set_title("Total sales by product per year", fontweight='bold', fontsize=12)

ax[0].grid(True); ax[1].grid(True)
  • Each product sales tendecy is similar to other products.
  • At 2020, there was a strong change of tendency, all countries produce similar sales, even Poland which experiment the most drastic change in sales.
In [6]:
temp  = tr_dat.groupby(['country','date']).sum()['num_sold'].reset_index()
temp3 = tr_dat.groupby(['date']).sum()['num_sold'].reset_index()['num_sold']
temp3 = np.concatenate([temp3,temp3,temp3,temp3,temp3,temp3])

temp['ratio'] = temp['num_sold']/temp3

f,ax=plt.subplots(figsize=(15,5))
ax.set_title('Ratio of Daily Sales per country', fontweight='bold', fontsize=15)
sns.lineplot(data=temp, x='date', y='ratio', hue='country')

plt.show()
  • Ratio of sales per country in each store are similar, but, since January 2020, the rate of all countries change to be in similar proportions.
  • This ratio also containes holidays, an extra effects, then is not suitable to obtain the mean an used to the next year.

Sales by store¶

In [8]:
data_by_store = tr_dat.groupby(['store','country']).sum()['num_sold'].reset_index().sort_values(by=['country','store'])
ratios_store  = (tr_dat.groupby(['country','store']).sum()['num_sold']/tr_dat.groupby(['country']).sum()['num_sold']).reset_index().sort_values(by=['country','store'])
data_by_store['ratio'] = ratios_store['num_sold'].values


f,ax = plt.subplots(figsize=(10,5))
ax.set_title("Total Sales by store", fontweight='bold', fontsize=15)
sns.barplot(data=data_by_store, x='country', y='num_sold', hue='store',ax=ax)

for i,store in enumerate(data_by_store['store'].unique()):
    ax.bar_label(ax.containers[i], 
                 labels=[f'{round(el*100,1):.1f}%' for el in data_by_store[data_by_store['store']==store]['ratio'].values], 
                 label_type='center', color='white', fontweight='bold')
  • The ratio of sales per store is the same in all countries.

Sales by product¶

In [9]:
f,ax=plt.subplots(figsize=(15,5), ncols=2, nrows=2)
for cnt, p in enumerate(tr_dat['product'].unique()):
    ax[cnt//2][cnt%2].set_title(p)
    tr_dat[(tr_dat['product']==p)].groupby(['date','country']).sum()['num_sold'].unstack(level=1).plot(kind='line', ax=ax[cnt//2][cnt%2], lw=1)
    ax[cnt//2][cnt%2].legend(loc='best')
    
plt.tight_layout()

The behaviour of the sales is summarized in total sales per product time series.

  • Each product total sales has proportional tendecy in each country.
  • High rate of sales at last period
  • Seasonal Sales, high peaks observed at new eve.
  • The last period shows that a drastic sales tendency (COVID19 lockdown?), similar sales are expected independently of the country for each product.

There is a drastic change of the tendecy at the begining of 2020.

As stated here. The percentage of which products sold by date is cyclical.

In [11]:
data_product = (tr_dat.groupby(["date",'product']).sum()['num_sold']/tr_dat.groupby(["date"]).sum()['num_sold']).reset_index()

f,ax=plt.subplots(figsize=(15,5))
ax.set_title("Ratio of Daily Sales per product", fontweight='bold', fontsize=15)
sns.lineplot(data=data_product, x='date', y='num_sold', hue='product')
ax.vlines(x=[np.datetime64(y) for y in ['2017','2018','2019','2020','2021']], ymin=0.13, ymax=0.38, color='black', linestyles='dotted')

plt.show()
  • Ratios of sales are mantained over time

This can suggests a multiplicative decomposition of the time series sales.

In [13]:
temp1        = tr_dat.groupby(['date']).sum()['num_sold'].reset_index()
temp1['woy'] = temp1['date'].dt.isocalendar().week
temp1['dow'] = temp1['date'].dt.dayofweek
temp1['doy'] = temp1['date'].dt.dayofyear

f,ax=plt.subplots(figsize=(15,4))
period_ind = (temp1['date']>='2020-03-01')&(temp1['date']<'2020-06-01')

ax.set_title("Total Sales per day", fontweight='bold', fontsize=15)
sns.lineplot(data=temp1, x='date', y='num_sold', ax=ax, color='darkred', label='Total sales per day')
sns.lineplot(data=temp1[period_ind], x='date', y='num_sold', ax=ax, color='navy', label='covid19 news')
plt.show()
  • Total Daily sales during 2020 were affected by a global crisis (COVID lockdown), this is not expected to occur annually, the specific period from '2020-03-01' to '2020-06-01' may be ignored for model training.
  • There is a tendency for peaks during the week, this can be used to refine the model, for example, more sales are expected on Friday than on Monday.

Holiday Sales Modeling¶

In [15]:
## Holiday sales modeling
#from statsmodels.tsa.filters.hp_filter import hpfilter

t1 = temp1[temp1['date'].dt.year==2017]
t2 = temp1[temp1['date'].dt.year==2018]
t3 = temp1[temp1['date'].dt.year==2019]
t4 = temp1[(temp1['date'].dt.year==2020)] #&~((temp1['date']>='2020-03-01')&(temp1['date']<'2020-06-01'))]


# to compare years it is necessary to move day-of-year until weekends overlap
t4_val = t4['num_sold'].values
t3_val = t3['num_sold'].values
t2_val = np.concatenate([t2['num_sold'].values[1:],t3_val[-1:]])
t1_val = np.concatenate([t1['num_sold'].values[2:],t3_val[-2:]])

# apply mean normalization
t1_val = (t1_val-t1_val.mean())/t1_val.std()
t2_val = (t2_val-t2_val.mean())/t2_val.std()
t3_val = (t3_val-t3_val.mean())/t3_val.std()
t4_val = (t4_val-t4_val.mean())/t4_val.std()
t5 = (t1_val+t2_val+t3_val)/3  # mean of non leap years

# representation
f,ax=plt.subplots(figsize=(15,4))

ax.plot(t1_val,color='red', label='2017 normalized data', lw=0.8)
ax.plot(t2_val,color='gold' , label='2018 normalized data', lw=0.8)
ax.plot(t3_val,color='blue', label='2019 normalized data', lw=0.8)
ax.plot(t4['doy'].values,t4_val,color='goldenrod', label='2020 normalized data', lw=0.8)


rep_dates = [1,2,3,4,5,
             119,120,121,122,123,124,125,126,127,128,129,
             224,225,226,227,228,229,230,
             304,305,306,307,308,309,310,
             360,361,362,363,364,365]

ax.vlines(rep_dates,ymin=-1,ymax=9,color='green',label='apparent representative dates', lw=0.5)
ax.plot(t5, lw=2,color='black', label='Avg normalized data')

#sm_factor = hpfilter(t5, lamb=50)
#ax.plot(sm_factor[1], lw=2,color='darkred', label='hpfilter')

ax.set_title('Normalized Total Daily Sales by year', fontweight='bold', fontsize=15)
ax.set_xlabel('day of the year')
ax.grid()
ax.set_ylim([-1.5, 5])
ax.legend(loc='best')

plt.tight_layout()
plt.show()

Holidays¶

In [17]:
from holidays import Belgium, France, Germany, Italy, Poland, Spain
from datetime import datetime

def obtain_hollidays(country):
    temp = pd.to_datetime(pd.Series(country(years=[2020]).keys())).dt.dayofyear.values
    temp = temp - (temp >=29)
    temp = np.concatenate([pd.to_datetime(pd.Series(Belgium(years=[2017,2018,2019,2020,2021]).keys())).dt.dayofyear.values, temp])
    t_dir = {el:0 for el in temp}
    
    for el in temp:
        t_dir[el] = t_dir[el]+1
        
    return [el[0] for el in t_dir.items() if el[1]>=4]

print('Holidays:\n')
print('Belgium: ',obtain_hollidays(Belgium))
print('France:  ',obtain_hollidays(France))
print('Germany: ',obtain_hollidays(Germany))
print('Italy:   ',obtain_hollidays(Italy))
print('Poland:  ',obtain_hollidays(Poland))
print('Spain:   ',obtain_hollidays(Spain))

Holidays:

Belgium:  [1, 121, 202, 227, 305, 315, 359]
France:   [1, 121, 202, 227, 305, 315, 359]
Germany:  [1, 121, 202, 227, 305, 315, 359]
Italy:    [1, 121, 202, 227, 305, 315, 359]
Poland:   [1, 121, 202, 227, 305, 315, 359]
Spain:    [1, 121, 202, 227, 305, 315, 359]
  • Z-score-normalized total daily sales by year show a holiday effect. Manually selected days in the graph above show this.
  • Also, the most important hollidays per country were obtained.

**This effect can be included in feature engineering using the radial basis function around selected dates.**.

Intraweek sales tendency¶

In [18]:
# select complete weeks available only
t_2017 = temp1[temp1['date'].dt.year==2017][1:]
t_2018 = temp1[temp1['date'].dt.year==2018][:-1]
t_2019 = temp1[temp1['date'].dt.year==2019][:-2]
t_2020 = temp1[(temp1['date'].dt.year==2020)&~((temp1['date']>='2020-03-01')&(temp1['date']<'2020-06-01'))]

def tend_week(t_data):
    tp = t_data.groupby(['dow']).mean()['num_sold']
    tp = (tp - tp.mean())/tp.std()
    return tp.values


f,ax=plt.subplots(figsize=(10,8), ncols=2, nrows=2)

avg_tend = []
for n,data in enumerate([t_2017, t_2018, t_2019, t_2020]):
    avg_tend.append(tend_week(data))
    for i in range(2,50):
        t1 = data[data['woy']==i].set_index('dow')
        # mean normalization
        t1 = (t1['num_sold']-t1['num_sold'].mean())/t1['num_sold'].std()
        
        t1.plot(ax=ax[n//2][n%2], marker='o', label=i)
        
        ax[n//2][n%2].set_title(n+2017)
        ax[n//2][n%2].set_ylim([-1.5, 2.0])
    
    ax[n//2][n%2].plot(tend_week(data), color='black', lw=4)

f.suptitle('Normalized Data for intra-week sales', fontsize=16)
plt.tight_layout()

Normalized data for intra-week sales shows that there is a cyclic sales tendency for each day of the week (dow). This effect can be used during feature enginering. This is defined:

  • dow <= 3 => weight of 0 (from Monday to Thursday low sales are expected)
  • dow == 4 => weight of 1 (by Friday, there are more sales)
  • dow == 5 => weight of 2 (by Saturday, there are more sales than Friday)
  • dow == 6 => weight of 3 (by Sunday, there are more sales than Saturday)

Propotions of Sales per Country¶

In [19]:
#grouped country weight calculation
country_df = tr_dat.groupby(["date","country"])["num_sold"].sum().reset_index()

country_weight_df = country_df.pivot(index="date", columns="country", values="num_sold")
country_weight_df = country_weight_df.apply(lambda x: x/x.sum(),axis=1)    # normalized to [0 --1] axis=1 rows
country_weight_df = country_weight_df.stack().rename("country_w").reset_index()

########################
temp = country_weight_df.pivot(index='date', columns='country', values='country_w').reset_index()
temp['year']  = temp['date'].dt.year

# correction to dramatic change at 1st Jan 2020
ref = ['Belgium','France','Germany','Italy','Poland','Spain']
temp.loc[temp['date']=='2020-01-01',ref] = temp.loc[temp['date']=='2020-01-02',ref].values

means = temp.groupby('year').mean() ; stds  = temp.groupby('year').std()
for year in [2017, 2018, 2019, 2020]:
    temp.loc[temp['year']==year,ref] -=means.loc[year].values
    temp.loc[temp['year']==year,ref] /=stds.loc[year].values

colors =['gold','yellow','orange','darkred']
country_rate = {}

f, ax = plt.subplots(figsize=(15,12), ncols=2, nrows=3)
for n_c, country in enumerate(ref):
    avg_rate = 0
    for year, g in temp.groupby("year"):
        if year==2020: g = g[g['date']!='2020-02-29']
        t1 = g[country].values
        ax[n_c//2][n_c%2].plot(t1, label=year, color=colors[year-2017], lw=0.8)
        avg_rate +=t1
    
    country_rate[country] = avg_rate/4
    ax[n_c//2][n_c%2].plot(avg_rate/4, label='avg', color='navy', lw=1.8)
    ax[n_c//2][n_c%2].set_ylim([-5,5]); ax[n_c//2][n_c%2].set_title(country)
    ax[n_c//2][n_c%2].legend()

f.suptitle('Normalized Data for product rate per country', fontsize=16)
plt.tight_layout() ; plt.show()

Comparing the z-score normalized daily sales tendency by country, similarities can be found. Notice that there is a certain sales tendecy encoded along the years per country, the averaged proportion of sales can be useful to define a forecast model.

Forecast model

¶

From EDA's insights, the target (daily sales by country, store and product) can be expresed as a multiplicative decomposition. The following equation is proposed:

yc,s,pt=TtRptmsrctytc,s,p=TtRtpmsrtc

where:

  • ∑Rpt=1∑Rtp=1, ∑ms=1∑ms=1
  • TtTt is the seasonal yearly trend of total number of daily sales, at a give day tt.
  • RptRtp is the cylic product sales ratio of a given product pp at a given day tt.
  • msms is the proportion of sales at a given store ss.
  • rctrtc is the proportion of sales for a specific country cc at a given day tt.
  • yc,s,ptytc,s,p is the total number of sales of a product pp from a country cc, store ss at a given day tt.

Since RptRtp and rctrtc are periodic and msms is fixed, the forecasting problem can be simplified to just predict TT total number of daily sales for 2021.

Model for Total Daily Sales T¶

In [20]:
#remove covid dates
train_nocovid_df = tr_dat.groupby(['date']).sum()['num_sold'].reset_index()
train_nocovid_df = train_nocovid_df.loc[~((train_nocovid_df["date"]>="2020-03-01") & (train_nocovid_df["date"]<"2020-06-01"))]

total_tr_df = train_nocovid_df
#get the dates to forecast for # just a complex way to obtain the data from the test file
ts_dates = ts_dat.groupby(["date"])["row_id"].first().reset_index().drop(columns="row_id")
test_all_df_dates = ts_dates[['date']]
In [21]:
# Holiday 'day-of-year' selected
#                    (amplitude, day-of-year)
radial_basis_info = [(20,  1),   (200, 121), (50, 191),
                     (150, 202), (400, 227), (1000, 315), 
                     (800, 359), (10,  365)]
#[1,121,202,227,305,315,359,365]

def feature_engineer(df):
    new_df = df.copy()
    # add non-linear features for modelling
    new_df["month"]   = new_df["date"].dt.month
    new_df["month_sin"] = np.sin(new_df['month'] * (2 * np.pi / 12))
    
    # weighted weeks days [1-3]:0, [4]:1, [5]:2, [6,7]:3
    new_df["day_of_week"] = df["date"].dt.dayofweek
    new_df["day_of_week"] = new_df["day_of_week"].apply(lambda x: 0 if x<=3 else (1 if x==4 else (2 if x==5 else (3))))
    
    new_df["day_of_year"] = df["date"].dt.dayofyear
    #account for leap year, remove the change in numeration due to 29th Feb 2020
    new_df["day_of_year"] = new_df.apply(lambda x: x["day_of_year"]-1 if (x["date"] > pd.Timestamp("2020-02-29") and x["date"] < pd.Timestamp("2021-01-01"))  else x["day_of_year"], axis=1)
    
    # radial basis function for selected dates
    for el in radial_basis_info:
        amplt=el[0]; day=el[1]
        new_df[f'x_{day}'] = new_df['day_of_year'].apply(lambda x: np.exp((x-day)**2 /(-2*amplt)))
    
    new_df["year"] = df["date"].dt.year
    new_df = new_df.drop(columns=["date","month","day_of_year"])
    
    # convert categorical into indicative variables (onehot version)
    new_df = pd.get_dummies(new_df, columns = ["day_of_week"], drop_first=True)
    
    return new_df
                                                                   
train_all_df = feature_engineer(total_tr_df) 
test_all_df  = feature_engineer(ts_dates)
In [23]:
X = train_all_df.drop(columns="num_sold")
y = train_all_df["num_sold"]
X_test = test_all_df

preds_lst = []
kf = GroupKFold(n_splits=4)

for fold, (train_idx, val_idx) in enumerate(kf.split(X, groups=X.year)):
    model = Ridge(alpha=0.5, solver='sag')    
    model = make_pipeline(StandardScaler(), model)
    model.fit(X.iloc[train_idx], y.iloc[train_idx])
    preds_lst.append(model.predict(X_test))
    
    #sc =  model.score(X.iloc[val_idx], y.iloc[val_idx])
    #print(fold, X.iloc[val_idx]['year'].unique(), sc)

preds_df = pd.DataFrame(np.column_stack(preds_lst), columns = ["2017", "2018", "2019", "2020"])

#average predictions from kfold    
preds_df['num_sold'] = preds_df.sum(axis=1)/len(preds_lst)
test_all_df_dates["num_sold"] = preds_df['num_sold']
In [24]:
f,ax=plt.subplots(figsize=(15,8), nrows=2)

t1 = preds_df['num_sold'].values
# mean normalization
comp1 = (t1-t1.mean())/t1.std()

ax[0].plot(t1,label='total sales predicted',color='darkred')

# overlap weekend peaks
ax[1].plot(np.linspace(-3,361,365),t5, label='tendency', color='black', lw=2)
ax[1].plot(comp1, label='total sales predicted', color='navy', lw=1)

ax[0].grid()                  ; ax[1].grid()
ax[0].set_ylim([9000, 15000]) ; ax[1].set_ylim([-1.5, 6])
ax[0].set_xlim([-10, 370])    ; ax[1].set_xlim([-10, 370])
ax[0].legend(loc='best')      ; ax[1].legend(loc='best')
ax[0].set_title('Predicted Sales for 2021', fontsize=15, fontweight='bold')
ax[1].set_title('Normalized Predicted Sales vs expected tendecy', fontsize=15, fontweight='bold')

plt.legend() ; plt.show()
  • The normalized tendency for the 2021 forcasted sales has the same tendency of previous years.

  • A Rigde model with K-Fold validation was sufficient. The Linear Model is capable of this thanks to the encoding non-linear features:

    • sinusoidal month (sin(month)) for cyclic effects
    • holiday effects, radial basis functions to simulation sales tendency around holidays.
    • intra-week tendency, weights according day of the week.

Cyclic sales ratios per product R¶

In [25]:
#product ratios per day
#grouped for ratio calculation
product_df = tr_dat.groupby(["date","product"])["num_sold"].sum().reset_index()

product_ratio_df = product_df.pivot(index="date", columns="product", values="num_sold")
product_ratio_df = product_ratio_df.apply(lambda x: x/x.sum(),axis=1)                      # normalized to [0 --1] axis=1 rows
product_ratio_df = product_ratio_df.stack().rename("ratios").reset_index()

seas_ratio = product_ratio_df[(product_ratio_df["date"].dt.year==2017)|
                              (product_ratio_df["date"].dt.year==2019)].copy()

seas_ratio["mm-dd"] = seas_ratio["date"].dt.strftime('%m-%d')
seas_ratio = seas_ratio.drop(columns="date")
seas_ratio = seas_ratio.groupby(['mm-dd','product']).mean().reset_index()

############ Two year cycle
f,ax=plt.subplots(figsize=(15,5))
sns.lineplot(data=product_ratio_df, x='date', y='ratios', hue='product',ax=ax)
ax.vlines(['2017','2019','2021'], ymin=0.1, ymax=0.4, linestyle='dashed')

plt.show()

Empirical observation indicates that there is a two year cycle for the sales ratio of 'Kaggle for Kids: One Smart Goose', to e this cycle as yearly the mean of them is used.

TtRptTtRtp Total product sales per day calculation¶

In [26]:
test_sub_df = pd.merge(ts_dat, test_all_df_dates, how="left")

# distribute ratios R_t
test_sub_df["mm-dd"]  = test_sub_df["date"].dt.strftime('%m-%d')
test_product_ratio_df = pd.merge(test_sub_df, seas_ratio, how="left", on = ["mm-dd","product"])

#test_product_ratio_df.head()  # seasonal tendency included

Proporion of sales per store msms¶

In [27]:
#ratio sold by store
store_weights = tr_dat.groupby("store")["num_sold"].sum()/tr_dat["num_sold"].sum()
store_weights.reset_index()
Out[27]:
store num_sold
0 KaggleMart 0.742515
1 KaggleRama 0.257485

Propotion of sales per Country and day rctrtc¶

In [28]:
country_weight_avg_df = pd.DataFrame(country_rate)
country_weight_avg_df['mm-dd'] = ts_dates['date'].dt.strftime('%m-%d')
#country_weight_avg_df['mm-dd'] = country_weight_avg_df['date']

country_weight_avg_df.loc[:,ref] *= stds.loc[2020].values
country_weight_avg_df.loc[:,ref] += means.loc[2020].values

country_weight_avg_df = country_weight_avg_df.melt(id_vars=["mm-dd"], var_name="country", value_name="country_w_avg")
#country_weight_avg_df.head()

Estimate daily sales by country, store and product¶

In [29]:
final = pd.merge(test_product_ratio_df, country_weight_avg_df[['country','country_w_avg','mm-dd']], how="left", on = ["mm-dd","country"])
final['num_sold_avg']   = final['num_sold'] * final['ratios'] * final['country_w_avg']

for store in store_weights.index:
    ind = final["store"] == store
    final.loc[ind, "num_sold_avg"]   = final.loc[ind, "num_sold_avg"]   * store_weights[store]


#output = pd.DataFrame({'row_id':   final['row_id'],
#                       'num_sold': final['num_sold_avg'].round()})

#output.to_csv('submission_v8.csv', index=False)

Forecasted sales analysis

¶

In [30]:
final = final[['date','country','store','product','num_sold_avg']].copy()
final['Year'] = 2021
In [31]:
temp = tr_dat.groupby(['country','Year'])['num_sold'].sum().reset_index()
forecast_temp = (final.groupby(['country','Year'])['num_sold_avg']
                 .sum().reset_index()
                 .rename(columns={"num_sold_avg": "num_sold"}))

temp = pd.concat([temp,forecast_temp], ignore_index=True).sort_values(by=['country','Year'])

temp1 = tr_dat.groupby(['product','Year'])['num_sold'].sum().reset_index()
forecast_temp1 = (final.groupby(['product','Year'])['num_sold_avg']
                 .sum().reset_index()
                 .rename(columns={"num_sold_avg": "num_sold"}))

temp1 = pd.concat([temp1,forecast_temp1], ignore_index=True).sort_values(by=['product','Year'])
temp['Year'] = temp['Year'].astype("int")
temp1['Year'] = temp1['Year'].astype("int")

f,ax=plt.subplots(ncols=2, figsize=(10,5))
ax[0].set_title("Total sales by country per year", fontsize=15, fontweight='bold')
ax[1].set_title("Total sales by product per year", fontsize=15, fontweight='bold')

#ax[0].xaxis.set_major_locator(ticker.MultipleLocator(1))
#ax[1].xaxis.set_major_locator(ticker.MultipleLocator(1))

sns.lineplot(data=temp, x='Year', y='num_sold', hue='country', marker='o', ax=ax[0])
sns.lineplot(data=temp1, x='Year', y='num_sold', hue='product', marker='o', ax=ax[1])
plt.show()
In [32]:
print(temp[temp['Year']==2021].set_index('country')['num_sold']/temp[temp['Year']==2020]['num_sold'].values - 1)
print("*"*50)
print(temp1[temp1['Year']==2021].set_index('product')['num_sold']/temp1[temp1['Year']==2020]['num_sold'].values - 1)

country
Belgium    0.023211
France     0.023638
Germany    0.023505
Italy      0.024031
Poland     0.025639
Spain      0.024320
Name: num_sold, dtype: float64
**************************************************
product
Kaggle Advanced Techniques          0.050964
Kaggle Getting Started              0.038923
Kaggle Recipe Book                  0.037137
Kaggle for Kids: One Smart Goose   -0.017946
Name: num_sold, dtype: float64

By year 2021:

  • The total sales are expected to increase a 2% per country.
  • The total sales are expected to increase at least 3% per product except fot 'Kaggle for Kids' that seems to decrease at 1%.