{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Importing the Packages","metadata":{}},{"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport seaborn as sns\nimport matplotlib.pyplot as plt\n\nimport plotly.express as px\nfrom plotly.subplots import make_subplots\nimport plotly.figure_factory as ff\nfrom plotly.offline import init_notebook_mode, iplot, plot\nimport plotly.graph_objs as go\n\n\nimport statsmodels.api as sm\nimport warnings\nfrom scipy import stats\nfrom itertools import product\nfrom sklearn.metrics import mean_squared_log_error\nfrom sklearn.metrics import mean_absolute_error,mean_squared_error\nfrom sklearn.linear_model import LinearRegression, Ridge, Lasso\nfrom sklearn.ensemble import RandomForestRegressor\nfrom sklearn.model_selection import TimeSeriesSplit, cross_val_score, cross_validate\nfrom sklearn.preprocessing import StandardScaler, MinMaxScaler\n\nimport warnings\nwarnings.filterwarnings('ignore')","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:41.646383Z","iopub.execute_input":"2021-11-13T13:40:41.646664Z","iopub.status.idle":"2021-11-13T13:40:46.505167Z","shell.execute_reply.started":"2021-11-13T13:40:41.646635Z","shell.execute_reply":"2021-11-13T13:40:46.504077Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <p><font size=\"10\" color=\"#8214c7\">Loading the data","metadata":{}},{"cell_type":"markdown","source":"<p><font size=\"3\" color=\"#8214c7\" style=\"Comic Sans MS;\">\n    \n**Train Data** The training data, comprising time series of features store_nbr, family, and onpromotion as well as the target sales. store_nbr identifies the store at which the products are sold. family identifies the type of product sold. sales gives the total sales for a product family at a particular store at a given date. Fractional values are possible since products can be sold in fractional units (1.5 kg of cheese, for instance, as opposed to 1 bag of chips). onpromotion gives the total number of items in a product family that were being promoted at a store at a given date.","metadata":{}},{"cell_type":"code","source":"train = pd.read_csv('../input/store-sales-time-series-forecasting/train.csv')\ntest = pd.read_csv('../input/store-sales-time-series-forecasting/test.csv')","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:46.506823Z","iopub.execute_input":"2021-11-13T13:40:46.507065Z","iopub.status.idle":"2021-11-13T13:40:49.766339Z","shell.execute_reply.started":"2021-11-13T13:40:46.507036Z","shell.execute_reply":"2021-11-13T13:40:49.765308Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:49.767549Z","iopub.execute_input":"2021-11-13T13:40:49.767814Z","iopub.status.idle":"2021-11-13T13:40:49.796653Z","shell.execute_reply.started":"2021-11-13T13:40:49.767781Z","shell.execute_reply":"2021-11-13T13:40:49.795788Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<p><font size=\"3\" color=\"#8214c7\" style=\"Comic Sans MS;\">\n    \n**Stores data** gives some information about stores such as city, state, type, cluster.\n\n**Transaction data** is highly correlated with train's sales column. You can understand the sales patterns of the stores.\n\n***Holidays and events data is a meta data***. This data is quite valuable to understand past sales, trend and seasonality components. However, it needs to be arranged. You are going to find a comprehensive data manipulation for this data. That part will be one of the most important chapter in this notebook.\n</font></p>\n","metadata":{}},{"cell_type":"code","source":"#Loading other data oil,holidays,transaction,stores\noil_df = pd.read_csv('../input/store-sales-time-series-forecasting/oil.csv')\nholidays_df = pd.read_csv('../input/store-sales-time-series-forecasting/holidays_events.csv')\nstores_df = pd.read_csv('../input/store-sales-time-series-forecasting/stores.csv')\ntrans_df = pd.read_csv('../input/store-sales-time-series-forecasting/transactions.csv',parse_dates=['date'])","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:49.798831Z","iopub.execute_input":"2021-11-13T13:40:49.799138Z","iopub.status.idle":"2021-11-13T13:40:49.911663Z","shell.execute_reply.started":"2021-11-13T13:40:49.799103Z","shell.execute_reply":"2021-11-13T13:40:49.910786Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#shape of train/test data\ntrain.shape,test.shape","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:49.91308Z","iopub.execute_input":"2021-11-13T13:40:49.913317Z","iopub.status.idle":"2021-11-13T13:40:49.918399Z","shell.execute_reply.started":"2021-11-13T13:40:49.913289Z","shell.execute_reply":"2021-11-13T13:40:49.917841Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Column names in the Training Dataset are:\\n\",train.columns)","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:49.919614Z","iopub.execute_input":"2021-11-13T13:40:49.919962Z","iopub.status.idle":"2021-11-13T13:40:49.930088Z","shell.execute_reply.started":"2021-11-13T13:40:49.919928Z","shell.execute_reply":"2021-11-13T13:40:49.929231Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.info()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:49.932013Z","iopub.execute_input":"2021-11-13T13:40:49.932407Z","iopub.status.idle":"2021-11-13T13:40:49.951763Z","shell.execute_reply.started":"2021-11-13T13:40:49.932361Z","shell.execute_reply":"2021-11-13T13:40:49.950871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.describe().style.set_properties(**{\"background-color\": \"#c69be0\",\"color\": \"black\", \"border-color\": \"black\"})","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:28.956807Z","iopub.execute_input":"2021-11-13T13:41:28.957084Z","iopub.status.idle":"2021-11-13T13:41:29.675722Z","shell.execute_reply.started":"2021-11-13T13:41:28.957054Z","shell.execute_reply":"2021-11-13T13:41:29.675123Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <p><font size=\"10\" color=\"#8214c7\">Basic Exploration \n","metadata":{}},{"cell_type":"markdown","source":"# <p><font size=\"6\" color=\"#8214c7\">Oil Data\n<p><font size=\"3\" color=\"#8214c7\" style=\"Comic Sans MS;\">\nDaily oil price. Includes values during both the train and test data timeframes. (Ecuador is an oil-dependent country and it's economical health is highly vulnerable to shocks in oil prices.)","metadata":{}},{"cell_type":"code","source":"oil_df.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:50.415047Z","iopub.execute_input":"2021-11-13T13:40:50.415266Z","iopub.status.idle":"2021-11-13T13:40:50.424759Z","shell.execute_reply.started":"2021-11-13T13:40:50.415238Z","shell.execute_reply":"2021-11-13T13:40:50.423907Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = px.line(oil_df, x='date', y=\"dcoilwtico\")\nfig.update_layout(title = \"Oil by Date\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:50.428023Z","iopub.execute_input":"2021-11-13T13:40:50.428242Z","iopub.status.idle":"2021-11-13T13:40:51.437615Z","shell.execute_reply.started":"2021-11-13T13:40:50.428215Z","shell.execute_reply":"2021-11-13T13:40:51.436755Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"oil_df[\"date\"] = pd.to_datetime(oil_df.date)\n# Resample\noil_df = oil_df.set_index(\"date\").dcoilwtico.resample(\"D\").sum().reset_index()\n# Interpolate\noil_df[\"dcoilwtico\"] = np.where(oil_df[\"dcoilwtico\"] == 0, np.nan, oil_df[\"dcoilwtico\"])\noil_df[\"dcoilwtico_interpolated\"] =oil_df.dcoilwtico.interpolate()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:51.43901Z","iopub.execute_input":"2021-11-13T13:40:51.439235Z","iopub.status.idle":"2021-11-13T13:40:51.463638Z","shell.execute_reply.started":"2021-11-13T13:40:51.439208Z","shell.execute_reply":"2021-11-13T13:40:51.463002Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Plot\np = oil_df.melt(id_vars=['date']+list(oil_df.keys()[5:]), var_name='Legend')\npx.line(p.sort_values([\"Legend\", \"date\"], ascending = [False, True]), x='date', y='value', color='Legend',title = \"Daily Oil Price interpolated\" )","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:51.464704Z","iopub.execute_input":"2021-11-13T13:40:51.465093Z","iopub.status.idle":"2021-11-13T13:40:51.67726Z","shell.execute_reply.started":"2021-11-13T13:40:51.465062Z","shell.execute_reply":"2021-11-13T13:40:51.664983Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Holiday's Data\n","metadata":{}},{"cell_type":"code","source":"print(holidays_df.info())\nholidays_df.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:51.678506Z","iopub.execute_input":"2021-11-13T13:40:51.679021Z","iopub.status.idle":"2021-11-13T13:40:51.699569Z","shell.execute_reply.started":"2021-11-13T13:40:51.678975Z","shell.execute_reply":"2021-11-13T13:40:51.698607Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"holidays_df['date'] = pd.to_datetime(holidays_df['date'])\nholidays_df['day_holiday'] = holidays_df['date'].dt.day_name()\nholidays_df['month_holiday'] = holidays_df['date'].dt.month\nholidays_df['year_holiday'] = holidays_df['date'].dt.year","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:51.700997Z","iopub.execute_input":"2021-11-13T13:40:51.701815Z","iopub.status.idle":"2021-11-13T13:40:51.715109Z","shell.execute_reply.started":"2021-11-13T13:40:51.701765Z","shell.execute_reply":"2021-11-13T13:40:51.714441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"holidays_df['day_holiday'].value_counts().plot.bar(figsize=(15,8),cmap = 'nipy_spectral')\nplt.title('Counts of Holidays',fontsize = 26)","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:51.716094Z","iopub.execute_input":"2021-11-13T13:40:51.716778Z","iopub.status.idle":"2021-11-13T13:40:52.022028Z","shell.execute_reply.started":"2021-11-13T13:40:51.716722Z","shell.execute_reply":"2021-11-13T13:40:52.021155Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = px.pie(holidays_df,names='type', color_discrete_sequence=px.colors.sequential.RdBu,template=\"plotly_dark\")\n\nfig.update_layout(title = \"Holidays Type\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:52.023764Z","iopub.execute_input":"2021-11-13T13:40:52.024379Z","iopub.status.idle":"2021-11-13T13:40:52.115333Z","shell.execute_reply.started":"2021-11-13T13:40:52.024331Z","shell.execute_reply":"2021-11-13T13:40:52.114254Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"holidays_df['locale'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:52.116507Z","iopub.execute_input":"2021-11-13T13:40:52.116762Z","iopub.status.idle":"2021-11-13T13:40:52.125291Z","shell.execute_reply.started":"2021-11-13T13:40:52.116709Z","shell.execute_reply":"2021-11-13T13:40:52.124403Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"f,ax=plt.subplots(1,2,figsize=(10,5))\nholidays_df['transferred'].value_counts().plot.pie(explode=[0,0.1],autopct='%1.1f%%',ax=ax[0],shadow=True,cmap ='tab20c')\nax[0].set_title('Transferred Holidays Pie chart')\nax[0].set_ylabel('')\nsns.countplot('transferred',data=holidays_df,ax=ax[1],palette='Set2_r')\nax[1].set_title('Transferred Holidays bar chart')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:52.126769Z","iopub.execute_input":"2021-11-13T13:40:52.12741Z","iopub.status.idle":"2021-11-13T13:40:52.44151Z","shell.execute_reply.started":"2021-11-13T13:40:52.127373Z","shell.execute_reply":"2021-11-13T13:40:52.440651Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(8,5))\nsns.countplot(data=holidays_df,x='type',hue='transferred',palette = 'YlOrBr_r')\nplt.legend(['Not Transferred', 'Transferred'])\nplt.xticks(rotation=45)\nplt.title('Transferred Holidays')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:52.443156Z","iopub.execute_input":"2021-11-13T13:40:52.443651Z","iopub.status.idle":"2021-11-13T13:40:52.725379Z","shell.execute_reply.started":"2021-11-13T13:40:52.443604Z","shell.execute_reply":"2021-11-13T13:40:52.724744Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Word Cloud ","metadata":{}},{"cell_type":"code","source":"from wordcloud import WordCloud\nwordcloud = WordCloud(\n                          background_color='black',\n                          max_font_size=50, \n                         ).generate(str(holidays_df['description']))\n\nprint(wordcloud)\nplt.figure(figsize=(15,7))\nplt.title('Word Cloud for Holidays description',fontsize=25)\nplt.imshow(wordcloud)\nplt.axis('off')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:52.726853Z","iopub.execute_input":"2021-11-13T13:40:52.72735Z","iopub.status.idle":"2021-11-13T13:40:53.105234Z","shell.execute_reply.started":"2021-11-13T13:40:52.727305Z","shell.execute_reply":"2021-11-13T13:40:53.103888Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Store Data**","metadata":{}},{"cell_type":"code","source":"stores_df.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:53.107048Z","iopub.execute_input":"2021-11-13T13:40:53.107371Z","iopub.status.idle":"2021-11-13T13:40:53.128348Z","shell.execute_reply.started":"2021-11-13T13:40:53.107328Z","shell.execute_reply":"2021-11-13T13:40:53.127078Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nfig = px.histogram(stores_df, x=\"city\",color='type',template=\"plotly_dark\")\nfig.update_layout(title = \"Citys by Store Type\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:53.131553Z","iopub.execute_input":"2021-11-13T13:40:53.134457Z","iopub.status.idle":"2021-11-13T13:40:53.254777Z","shell.execute_reply.started":"2021-11-13T13:40:53.1344Z","shell.execute_reply":"2021-11-13T13:40:53.253797Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Transactions data ","metadata":{}},{"cell_type":"code","source":"trans_df.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:53.256283Z","iopub.execute_input":"2021-11-13T13:40:53.256614Z","iopub.status.idle":"2021-11-13T13:40:53.268593Z","shell.execute_reply.started":"2021-11-13T13:40:53.256571Z","shell.execute_reply":"2021-11-13T13:40:53.267526Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trans_df.info()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:53.270061Z","iopub.execute_input":"2021-11-13T13:40:53.270319Z","iopub.status.idle":"2021-11-13T13:40:53.290843Z","shell.execute_reply.started":"2021-11-13T13:40:53.270289Z","shell.execute_reply":"2021-11-13T13:40:53.289513Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = px.histogram(trans_df, x=\"transactions\",template=\"plotly_dark\")\nfig.update_layout(title = \"Transaction Distribution\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:53.292866Z","iopub.execute_input":"2021-11-13T13:40:53.293378Z","iopub.status.idle":"2021-11-13T13:40:53.853798Z","shell.execute_reply.started":"2021-11-13T13:40:53.29332Z","shell.execute_reply":"2021-11-13T13:40:53.85264Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"copy_df= trans_df.copy()\ncopy_df[\"year\"] = copy_df.date.dt.year\ncopy_df[\"dayofweek\"] = copy_df.date.dt.dayofweek+1\ncopy_df = copy_df.groupby([\"year\", \"dayofweek\"]).transactions.mean().reset_index()\npx.line(copy_df, x=\"dayofweek\", y=\"transactions\" , color = \"year\", title = \"Transactions\")","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:53.855378Z","iopub.execute_input":"2021-11-13T13:40:53.855773Z","iopub.status.idle":"2021-11-13T13:40:53.989704Z","shell.execute_reply.started":"2021-11-13T13:40:53.855708Z","shell.execute_reply":"2021-11-13T13:40:53.988899Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:53.991003Z","iopub.execute_input":"2021-11-13T13:40:53.991249Z","iopub.status.idle":"2021-11-13T13:40:54.005084Z","shell.execute_reply.started":"2021-11-13T13:40:53.991219Z","shell.execute_reply":"2021-11-13T13:40:54.003948Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Timeline feature\ndef time_feature(df):\n    df['date'] = pd.to_datetime(df['date'])\n    df['dayofweek'] = df['date'].dt.dayofweek\n    df['quarter'] = df['date'].dt.quarter\n    df['month'] = df['date'].dt.month\n    df['year'] = df['date'].dt.year\n    df['dayofyear'] = df['date'].dt.dayofyear\n    df['dayofmonth'] = df['date'].dt.day\n    return df","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:54.010754Z","iopub.execute_input":"2021-11-13T13:40:54.011596Z","iopub.status.idle":"2021-11-13T13:40:54.018926Z","shell.execute_reply.started":"2021-11-13T13:40:54.011547Z","shell.execute_reply":"2021-11-13T13:40:54.017929Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"time_feature(train)\ntime_feature(test)","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:54.020617Z","iopub.execute_input":"2021-11-13T13:40:54.020951Z","iopub.status.idle":"2021-11-13T13:40:56.327488Z","shell.execute_reply.started":"2021-11-13T13:40:54.020905Z","shell.execute_reply":"2021-11-13T13:40:56.326801Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# THANKS TO https://www.kaggle.com/shivamb/store-sales-forecasting-exploration\ndef hbar(col):\n    temp = train.groupby(col).agg({\"sales\" : \"mean\"}).reset_index()\n    temp = temp.sort_values(col, ascending = False)\n    c = {\n        'y' : list(temp['sales']), \n        'x' : list(temp[col]),\n        'title' : \"Average sales by \"+col\n    }\n    trace = go.Bar(y=c['y'], x=c['x'], orientation=\"v\", marker=dict(color=\"#edb705\"))\n    return trace \n\n    layout = go.Layout(title=c['title'], \n                           paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)',\n                           xaxis_title=\"\", yaxis_title=\"\", width=650)\n    fig = go.Figure([trace], layout=layout)\n    fig.update_xaxes(tickangle=45, tickfont=dict(color='crimson'))\n    fig.update_yaxes(tickangle=0, tickfont=dict(color='crimson'))\n    fig.show()\n    \ntrace1 = hbar('dayofweek') \ntrace2 = hbar('dayofmonth') \ntrace3 = hbar('dayofyear') \ntrace4 = hbar('month') \ntrace5 = hbar('quarter') \ntrace6 = hbar('year') \n\ntitles = ['Day of Week', 'Day of Month', 'Day of Year', 'Month', 'Quarter', 'Year']\ntitles = ['Avg Sales by ' + _ for _ in titles]\nfig = make_subplots(rows=3, cols=2, subplot_titles = titles)\n\nfig.add_trace(trace1, row=1, col=1)\nfig.add_trace(trace2, row=1, col=2)\nfig.add_trace(trace3, row=2, col=1)\nfig.add_trace(trace4, row=2, col=2)\nfig.add_trace(trace5, row=3, col=1)\nfig.add_trace(trace6, row=3, col=2)\n\nfig.update_layout(height=1200, paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)', showlegend = False)\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:56.328518Z","iopub.execute_input":"2021-11-13T13:40:56.329261Z","iopub.status.idle":"2021-11-13T13:40:56.809324Z","shell.execute_reply.started":"2021-11-13T13:40:56.329212Z","shell.execute_reply":"2021-11-13T13:40:56.808432Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#\n# data \ndf_2013 = train[train['year']==2013][['month','sales']]\ndf_2013 = df_2013.groupby('month').agg({\"sales\" : \"mean\"}).reset_index().rename(columns={'sales':'s13'})\ndf_2014 = train[train['year']==2014][['month','sales']]\ndf_2014 = df_2014.groupby('month').agg({\"sales\" : \"mean\"}).reset_index().rename(columns={'sales':'s14'})\ndf_2015 = train[train['year']==2015][['month','sales']]\ndf_2015 = df_2015.groupby('month').agg({\"sales\" : \"mean\"}).reset_index().rename(columns={'sales':'s15'})\ndf_2016 = train[train['year']==2016][['month','sales']]\ndf_2016 = df_2016.groupby('month').agg({\"sales\" : \"mean\"}).reset_index().rename(columns={'sales':'s16'})\ndf_2017 = train[train['year']==2017][['month','sales']]\ndf_2017 = df_2017.groupby('month').agg({\"sales\" : \"mean\"}).reset_index()\ndf_2017_no = pd.DataFrame({'month': [9,10,11,12], 'sales':[0,0,0,0]})\ndf_2017 = df_2017.append(df_2017_no).rename(columns={'sales':'s17'})\ndf_year = df_2013.merge(df_2014,on='month').merge(df_2015,on='month').merge(df_2016,on='month').merge(df_2017,on='month')\n\n# top levels\ntop_labels = ['2013', '2014', '2015', '2016', '2017']\n\ncolors = ['#97c20a', '#a9cf30',\n          '#badb51', '#d0eb7a',\n          '#e3f2b1']\n\n# X axis value \ndf_year = df_year[['s13','s14','s15','s16','s17']].replace(np.nan,0)\nx_data = df_year.values\n\n# y axis value (Month)\ndf_2013['month'] =['1M','2M','3M','4M','5M','6M','7M','8M','9M','10M','11M','12M']\ny_data = df_2013['month'].tolist()\n\nfig = go.Figure()\nfor i in range(0, len(x_data[0])):\n    for xd, yd in zip(x_data, y_data):\n        fig.add_trace(go.Bar(\n            x=[xd[i]], y=[yd],\n            orientation='h',\n            marker=dict(\n                color=colors[i],\n                line=dict(color='rgb(248, 248, 249)', width=1)\n            )\n        ))\n\nfig.update_layout(title='Avg Sales for each Year',\n    xaxis=dict(showgrid=False, \n               zeroline=False, domain=[0.15, 1]),\n    yaxis=dict(showgrid=False, showline=False,\n               showticklabels=False, zeroline=False),\n    barmode='stack', #barnorm='percent',\n    plot_bgcolor='#fff', paper_bgcolor='#fff',\n    margin=dict(l=0, r=50, t=100, b=10),\n    showlegend=False, \n)\n\nannotations = []\nfor yd, xd in zip(y_data, x_data):\n    # labeling the y-axis\n    annotations.append(dict(xref='paper', yref='y',\n                            x=0.14, y=yd,\n                            xanchor='right',\n                            text=str(yd),\n                            font=dict(family='Arial', size=14,\n                                      color='rgb(67, 67, 67)'),\n                            showarrow=False, align='right'))\n    # labeling the first Likert scale (on the top)\n    if yd == y_data[-1]:\n        annotations.append(dict(xref='x', yref='paper',\n                                x=xd[0] / 2, y=1.1,\n                                text=top_labels[0],\n                                font=dict(family='Arial', size=14,\n                                          color='rgb(67, 67, 67)'),\n                          showarrow=False))\n    space = xd[0]\n    for i in range(1, len(xd)):\n            # labeling the Likert scale\n            if yd == y_data[-1]:\n                annotations.append(dict(xref='x', yref='paper',\n                                        x=space + (xd[i]/2), y=1.1,\n                                        text=top_labels[i],\n                                        font=dict(family='Arial', size=14,\n                                                  color='rgb(67, 67, 67)'),\n                                        showarrow=False))\n            space += xd[i]\nfig.update_layout(\n    annotations=annotations)\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:56.811328Z","iopub.execute_input":"2021-11-13T13:40:56.811948Z","iopub.status.idle":"2021-11-13T13:40:57.390744Z","shell.execute_reply.started":"2021-11-13T13:40:56.811889Z","shell.execute_reply":"2021-11-13T13:40:57.389908Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"agg = train.groupby('date').agg({\"sales\" : \"mean\"}).reset_index()\nfig = px.line(agg, x='date', y=['sales'])\nfig.update_layout(title = \"Average Sales by Date\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:57.391955Z","iopub.execute_input":"2021-11-13T13:40:57.3922Z","iopub.status.idle":"2021-11-13T13:40:57.576765Z","shell.execute_reply.started":"2021-11-13T13:40:57.392169Z","shell.execute_reply":"2021-11-13T13:40:57.575973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:57.578022Z","iopub.execute_input":"2021-11-13T13:40:57.578626Z","iopub.status.idle":"2021-11-13T13:40:57.594867Z","shell.execute_reply.started":"2021-11-13T13:40:57.578585Z","shell.execute_reply":"2021-11-13T13:40:57.59388Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#oil_df.columns\n#Index(['date', 'dcoilwtico', 'dcoilwtico_interpolated'], dtype='object')\n#holidays_df.columns\n#Index(['date', 'type', 'locale', 'locale_name', 'description', 'transferred','day_holiday', 'month_holiday', 'year_holiday'],dtype='object')\n#trans_df.columns\n#Index(['date', 'store_nbr', 'transactions'], dtype='object')\n#stores_df.columns\n#Index(['store_nbr', 'city', 'state', 'type', 'cluster'], dtype='object')","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:57.596228Z","iopub.execute_input":"2021-11-13T13:40:57.596473Z","iopub.status.idle":"2021-11-13T13:40:57.601562Z","shell.execute_reply.started":"2021-11-13T13:40:57.596445Z","shell.execute_reply":"2021-11-13T13:40:57.600486Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## combine datasets\ntrain1 = train.merge(oil_df, on = 'date', how='left')\ntrain1 = train1.merge(holidays_df, on = 'date', how='left')\ntrain1 = train1.merge(stores_df, on = 'store_nbr', how='left')\ntrain1 = train1.merge(trans_df, on = ['date', 'store_nbr'], how='left')\ntrain1 = train1.rename(columns = {\"type_x\" : \"holiday_type\", \"type_y\" : \"store_type\"})\n\ntest1 = test.merge(oil_df, on = 'date', how='left')\ntest1 = test1.merge(holidays_df, on = 'date', how='left')\ntest1 = test1.merge(stores_df, on = 'store_nbr', how='left')\ntest1 = test1.merge(trans_df, on = ['date', 'store_nbr'], how='left')\ntest1 = test1.rename(columns = {\"type_x\" : \"holiday_type\", \"type_y\" : \"store_type\"})\n\ntrain1.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:40:57.602788Z","iopub.execute_input":"2021-11-13T13:40:57.602997Z","iopub.status.idle":"2021-11-13T13:41:02.985634Z","shell.execute_reply.started":"2021-11-13T13:40:57.602971Z","shell.execute_reply":"2021-11-13T13:41:02.984716Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Function to calculate missing values by column# Funct \ndef missing_data(df):\n        # Total missing values\n        mis_val = df.isnull().sum()\n        \n        # Percentage of missing values\n        mis_val_percent = 100 * df.isnull().sum() / len(df)\n        \n        # Make a table with the results\n        mis_val_table = pd.concat([mis_val, mis_val_percent], axis=1)\n        \n        # Rename the columns\n        mis_val_table_ren_columns = mis_val_table.rename(\n        columns = {0 : 'Missing Values', 1 : '% of Total Values'})\n        \n        # Sort the table by percentage of missing descending\n        mis_val_table_ren_columns = mis_val_table_ren_columns[\n            mis_val_table_ren_columns.iloc[:,1] != 0].sort_values(\n        '% of Total Values', ascending=False).round(1)\n        \n        # Print some summary information\n        print (\"Your selected dataframe has \" + str(df.shape[1]) + \" columns.\\n\"      \n            \"There are \" + str(mis_val_table_ren_columns.shape[0]) +\n              \" columns that have missing values.\")\n        \n        # Return the dataframe with missing information\n        return mis_val_table_ren_columns","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:02.987194Z","iopub.execute_input":"2021-11-13T13:41:02.987665Z","iopub.status.idle":"2021-11-13T13:41:02.995382Z","shell.execute_reply.started":"2021-11-13T13:41:02.987617Z","shell.execute_reply":"2021-11-13T13:41:02.99448Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"missing_value = missing_data(train1)\nmissing_value.head(20)","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:02.996499Z","iopub.execute_input":"2021-11-13T13:41:02.997023Z","iopub.status.idle":"2021-11-13T13:41:08.022839Z","shell.execute_reply.started":"2021-11-13T13:41:02.996989Z","shell.execute_reply":"2021-11-13T13:41:08.02217Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_missing = missing_data(test1)\ntest_missing.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:08.023956Z","iopub.execute_input":"2021-11-13T13:41:08.024372Z","iopub.status.idle":"2021-11-13T13:41:08.086586Z","shell.execute_reply.started":"2021-11-13T13:41:08.024341Z","shell.execute_reply":"2021-11-13T13:41:08.08594Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Data Wranggling\n#train1.isnull().any()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:08.087612Z","iopub.execute_input":"2021-11-13T13:41:08.0883Z","iopub.status.idle":"2021-11-13T13:41:08.091273Z","shell.execute_reply.started":"2021-11-13T13:41:08.088268Z","shell.execute_reply":"2021-11-13T13:41:08.090612Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"agg = train1.groupby([\"year\", \"store_type\"]).agg({\"sales\"  :\"mean\", \"transactions\" : \"mean\"}).reset_index()\nfig = px.box(agg, y=\"sales\", facet_col=\"store_type\", color=\"store_type\",\n             boxmode=\"overlay\", points='all')\nfig.update_layout(title = \"Average Sales Distribution by Store Type\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:08.092384Z","iopub.execute_input":"2021-11-13T13:41:08.092796Z","iopub.status.idle":"2021-11-13T13:41:08.631597Z","shell.execute_reply.started":"2021-11-13T13:41:08.092747Z","shell.execute_reply":"2021-11-13T13:41:08.630782Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"agg = train1.groupby([\"year\", \"state\"]).agg({\"sales\"  :\"mean\", \"transactions\" : \"mean\"}).reset_index()\nfig = px.box(agg, y=\"sales\", facet_col=\"state\", color=\"state\",\n             boxmode=\"overlay\", points='all')\nfig.update_layout(title = \"Average Sales Distribution by State\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:08.632942Z","iopub.execute_input":"2021-11-13T13:41:08.633197Z","iopub.status.idle":"2021-11-13T13:41:09.375682Z","shell.execute_reply.started":"2021-11-13T13:41:08.633163Z","shell.execute_reply":"2021-11-13T13:41:09.374863Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"agg = train1.groupby([\"year\", \"day_holiday\"]).agg({\"sales\"  :\"mean\", \"transactions\" : \"mean\"}).reset_index()\nfig = px.box(agg, y=\"sales\", facet_col=\"day_holiday\", color=\"day_holiday\",\n             boxmode=\"overlay\", points='all')\nfig.update_layout(title = \"Average Sales Distribution by Holiday day's\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:09.376885Z","iopub.execute_input":"2021-11-13T13:41:09.377137Z","iopub.status.idle":"2021-11-13T13:41:09.950773Z","shell.execute_reply.started":"2021-11-13T13:41:09.377108Z","shell.execute_reply":"2021-11-13T13:41:09.949816Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"agg = train1.groupby([\"year\", \"holiday_type\"]).agg({\"sales\"  :\"mean\", \"transactions\" : \"mean\"}).reset_index()\nfig = px.box(agg, y=\"sales\", facet_col=\"holiday_type\", color=\"holiday_type\",\n             boxmode=\"overlay\", points='all')\nfig.update_layout(title = \"Average Sales Distribution by Holiday Type\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:09.952117Z","iopub.execute_input":"2021-11-13T13:41:09.952814Z","iopub.status.idle":"2021-11-13T13:41:10.48866Z","shell.execute_reply.started":"2021-11-13T13:41:09.952774Z","shell.execute_reply":"2021-11-13T13:41:10.487761Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# data\ndf_st_sa = train1.groupby('store_type').agg({\"sales\" : \"mean\"}).reset_index().sort_values(by='sales', ascending=False)\ndf_fa_sa = train1.groupby('family').agg({\"sales\" : \"mean\"}).reset_index().sort_values(by='sales', ascending=False)[:10]\ndf_cl_sa = train1.groupby('cluster').agg({\"sales\" : \"mean\"}).reset_index() \n# chart color\ndf_fa_sa['color'] = '#32cf76'\ndf_fa_sa['color'][2:] = '#a5d993'\ndf_cl_sa['color'] = '#c8d984'\n\n# chart\nfig = make_subplots(rows=2, cols=2, \n                    specs=[[{\"type\": \"bar\"}, {\"type\": \"pie\"}],\n                           [{\"colspan\": 2}, None]],\n                    column_widths=[0.7, 0.3], vertical_spacing=0, horizontal_spacing=0.02,\n                    subplot_titles=(\"Top 10 Highest Product Sales\", \"Highest Sales in Stores\", \"Clusters Vs Sales\"))\n\nfig.add_trace(go.Bar(x=df_fa_sa['sales'], y=df_fa_sa['family'], marker=dict(color= df_fa_sa['color']),\n                     name='Family', orientation='h'), \n                     row=1, col=1)\nfig.add_trace(go.Pie(values=df_st_sa['sales'], labels=df_st_sa['store_type'], name='Store type',\n                     marker=dict(colors=['#68992f','#80b346','#9ac963','#c5e0a6','#e1edd3']), hole=0.7,\n                     hoverinfo='label+percent+value', textinfo='label'), \n                    row=1, col=2)\nfig.add_trace(go.Bar(x=df_cl_sa['cluster'], y=df_cl_sa['sales'], \n                     marker=dict(color= df_cl_sa['color']), name='Cluster'), \n                     row=2, col=1)\n\n# styling\nfig.update_yaxes(showgrid=False, ticksuffix=' ', categoryorder='total ascending', row=1, col=1)\nfig.update_xaxes(visible=False, row=1, col=1)\nfig.update_xaxes(tickmode = 'array', tickvals=df_cl_sa.cluster, ticktext=[i for i in range(1,17)], row=2, col=1)\nfig.update_yaxes(visible=False, row=2, col=1)\nfig.update_layout(height=500, bargap=0.2,\n                  margin=dict(b=0,r=20,l=20), xaxis=dict(tickmode='linear'),\n                  title_text=\"Average Sales Analysis\",\n                  template=\"plotly_white\",\n                  title_font=dict(size=25, color='#8a8d93', family=\"Lato, sans-serif\"),\n                  font=dict(color='#8a8d93'),\n                  hoverlabel=dict(bgcolor=\"#f2f2f2\", font_size=13, font_family=\"Lato, sans-serif\"),\n                  showlegend=False)\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:41:10.489916Z","iopub.execute_input":"2021-11-13T13:41:10.490178Z","iopub.status.idle":"2021-11-13T13:41:11.224164Z","shell.execute_reply.started":"2021-11-13T13:41:10.490148Z","shell.execute_reply":"2021-11-13T13:41:11.223193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def vbar(col):\n    temp = train1.groupby(col).agg({\"sales\" : \"mean\"}).reset_index()\n    temp = temp.sort_values('sales', ascending = False)\n    c = {\n        'x' : list(temp['sales'])[:15][::-1], \n        'y' : list(temp[col])[:15][::-1],\n        'title' : \"Average sales by \"+col\n    }\n    trace = go.Bar(y=[str(_) + \"    \" for _ in c['y']], x=c['x'], orientation=\"h\", marker=dict(color=\"#c69be0\"))\n    return trace \n\n    layout = go.Layout(title=c['title'], \n                           paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)',\n                           xaxis_title=\"\", yaxis_title=\"\", width=650)\n    fig = go.Figure([trace], layout=layout)\n    fig.update_xaxes(tickangle=45, tickfont=dict(color='crimson'))\n    fig.update_yaxes(tickangle=0, tickfont=dict(color='crimson'))\n    fig.show()\n    \ntrace1 = vbar('family') \ntrace2 = vbar('store_type') \ntrace3 = vbar('state') \ntrace4 = vbar('city')\n\ntitles = ['Store Family', 'Store Type', 'State', 'City']\ntitles = ['Top ' + _ + \" by Average Sales\" for _ in titles]\nfig = make_subplots(rows=2, cols=2, subplot_titles = titles)\n\nfig.add_trace(trace1, row=1, col=1)\nfig.add_trace(trace2, row=1, col=2)\nfig.add_trace(trace3, row=2, col=1)\nfig.add_trace(trace4, row=2, col=2)\n\nfig.update_layout(height=800, paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)', showlegend = False)\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-13T13:42:19.417867Z","iopub.execute_input":"2021-11-13T13:42:19.418187Z","iopub.status.idle":"2021-11-13T13:42:20.600141Z","shell.execute_reply.started":"2021-11-13T13:42:19.41815Z","shell.execute_reply":"2021-11-13T13:42:20.599242Z"},"trusted":true},"execution_count":null,"outputs":[]}]}