{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"!pip install py7zr\nimport py7zr","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import py7zr\nfrom subprocess import check_output\n\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        archive = py7zr.SevenZipFile(os.path.join(dirname, filename), mode='r')\n        archive.extractall(path=\"/kaggle/working\")\n        archive.close()\n\nprint(check_output([\"ls\", \"../working\"]).decode(\"utf8\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train = pd.read_csv(\"../working/train.csv\")\n#test = pd.read_csv(\"../working/test.csv\")\n#sample_sub = pd.read_csv(\"../working/sample_submission.csv\")\nstores = pd.read_csv(\"../working/stores.csv\")\nitems = pd.read_csv(\"../working/items.csv\")\n#transactions = pd.read_csv(\"../working/transactions.csv\")\noil = pd.read_csv(\"../working/oil.csv\")\nholiday = pd.read_csv(\"../working/holidays_events.csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Basic EDA"},{"metadata":{"trusted":true},"cell_type":"code","source":"print(\"Shape of train:\" , train.shape)\nprint(\"Shape of test:\" , test.shape)\nprint(\"Shape of stores:\" , stores.shape)\nprint(\"Shape of items:\" , items.shape)\nprint(\"Shape of transactions:\" , transactions.shape)\nprint(\"Shape of oil:\" , oil.shape)\nprint(\"Shape of holiday:\" , holiday.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(train.info(),\"\\n\")\nprint(test.info(),\"\\n\")\nprint(stores.info(),\"\\n\")\nprint(items.info(),\"\\n\")\nprint(transactions.info(),\"\\n\")\nprint(oil.info(),\"\\n\")\nprint(holiday.info())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 1) Train Set"},{"metadata":{"trusted":true},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.tail()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"stores.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Converting date column to datetime type to reduce the memory usage\nprint(\"size before:\", train[\"date\"].memory_usage(deep=True) * 1e-6)\ntrain[\"date\"] = pd.to_datetime(train[\"date\"])\nprint(\"size after: \", train[\"date\"].memory_usage(deep=True) * 1e-6)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 1) Filtering out stores where city = 'Daule','Quito','Santo Domingo'\n### 2) Filtering out stores where family = 'Dairy','Bread/Bakery'\n### NOTE:Doing mannual filtering because not able to apply join or any other technique on such a big file due to CPU and RAM restrictions."},{"metadata":{"trusted":true},"cell_type":"code","source":"#The below code can be used with a better CPU and RAM access\n#merged_df = train.merge(stores, on='store_nbr', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"store_number = (stores.loc[(stores['city']=='Daule') | (stores['city']=='Quito') | (stores['city']=='Santo Domingo')])['store_nbr'].tolist()\nprint(\"Stores which are present in these 3 citites:\",\"\\n\",store_number)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"item_number = (items.loc[(items['family']=='BREAD/BAKERY') | (items['family']=='DAIRY')])['item_nbr'].tolist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset = train[train['store_nbr'].isin(store_number) & train['item_nbr'].isin(item_number)]\nprint(train_subset.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Left join on \"Train & Stores\",\"Train & Items\", \"Train & Oil\" and \"Train & Holiday\". \n### Note: Here we are using the train set for LEFT JOIN after applying given filters so that it will consume lesser amount of memory."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Left Join - Train & Stores\ntrain_subset = pd.merge(train_subset, stores, on = 'store_nbr', how = 'left')\ntrain_subset.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Left Join - Train & Items \ntrain_subset = pd.merge(train_subset, items, on = 'item_nbr', how = 'left')\ntrain_subset.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Left Join - Train & Oil\ntrain_subset = pd.merge(train_subset, oil, on = 'date', how = 'left')\ntrain_subset.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### ASSUMPTION 1: Dates having type 'Additional','Bridge','Event',and 'Transfer' are considered to be a holiday. Hence there will be only two types of day categories available - a) Work Day and b) Holiday "},{"metadata":{"trusted":true},"cell_type":"code","source":"holiday['type'] = holiday['type'].replace(['Additional','Bridge','Event','Transfer'], 'Holiday')\nmask = (holiday['transferred'] == True)\nholiday['type'][mask] = 'Work Day'\nprint(holiday['type'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Left Join - Train & Holiday\ntrain_subset = pd.merge(train_subset, holiday, on = 'date', how = 'left')\ntrain_subset = train_subset.drop(['locale', 'locale_name','description','transferred'], axis=1)\ntrain_subset = train_subset.rename(columns={\"type_y\": \"day_type\", \"type_x\": \"type\",\"dcoilwtico\":\"oil_price\"})\ntrain_subset.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Handling Missing Values in Train Set"},{"metadata":{},"cell_type":"markdown","source":""},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset.isnull().sum().sort_values(ascending=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### ASSUMPTION 2: Dates which are not present in the holiday dataset are considered to be Work Day. "},{"metadata":{"trusted":true},"cell_type":"code","source":"# Replacing NA values in day_type column with Work Day\ntrain_subset['day_type'] = train_subset['day_type'].fillna(\"Work Day\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Replace missing values in Oil_Price\ntrain_subset['oil_price'] = train_subset[\"oil_price\"].fillna(axis = 0,method = 'ffill')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### ASSUMPTION 3: Considering the missing values in onpromotion field as \"Not Mentioned\". Hence there will be 3 categories available in this field - 'True', 'False', and 'Not Mentioned'."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Creating a new category in onpromotion column, where NA values are replaced with \"Not Mentioned\"\ntrain_subset['onpromotion'] = train_subset['onpromotion'].fillna(\"Not Mentioned\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Removing the datasets which are not required anymore to clear some space."},{"metadata":{"trusted":true},"cell_type":"code","source":"#del oil\n#del holiday\n#del items\n#del stores","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Adding additional fields (for EDA purpose) - Month & Year using Date column"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset[\"date\"] = pd.to_datetime(train_subset[\"date\"])\ntrain_subset['Month'] = train_subset['date'].dt.strftime('%B')\ntrain_subset['Year'] = train_subset['date'].dt.strftime('%Y')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Detailed EDA"},{"metadata":{"trusted":true},"cell_type":"code","source":"import altair as alt","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 1) Yearly Transactions"},{"metadata":{"trusted":true},"cell_type":"code","source":"year_df = train_subset['Year'].value_counts().to_frame().reset_index().rename(columns={'index':'Year','Year':'count'}).sort_values(by = 'Year')\nprint(year_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"bars = alt.Chart(year_df).mark_bar(color=\"purple\").encode(\n    x='Year',\n    y='count',\n    tooltip=[alt.Tooltip('count:Q')]\n    \n)\n\ntext = bars.mark_text(\n    align='center',\n    baseline='middle',\n    dy=-7 ,\n    size=15,\n).encode(text='count')\n\n(bars + text).properties(\n    width=400,\n    height=400,\n    title=\"Yearly Transactions\")\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Interpretation: The retail store has seen a constant annual growth rate of around 20% in all the years except for 2013-2014 where the growth was 40%."},{"metadata":{},"cell_type":"markdown","source":"### 2) Monthly Transactions over 4 years"},{"metadata":{"trusted":true},"cell_type":"code","source":"month_df = train_subset.groupby(['Month','Year']).size().reset_index().rename(columns={0:'count'})\nmonth_df['Year'] = month_df['Year'].astype('category')\nmonth_df['Month'] = month_df['Month'].astype('category')\nmonth_df['Month_Year'] = month_df['Month'].astype(str)+\"-\"+month_df['Year'].astype(str)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"bars=alt.Chart(month_df).mark_bar().encode(\n    x='count',\n    y='Month',\n    color=alt.Color('Year',title='Year'),\n    tooltip=[alt.Tooltip('Month_Year:N'),\n             alt.Tooltip('Month:N'),\n             alt.Tooltip('count:Q'),\n            ]\n    \n).properties(\n    width=550,\n    height=400,\n    title=\"Monthly Transactions over 4 years -  (Hover over each segment of Bar to understand distribution)\")\n\ntext = alt.Chart(month_df).mark_text(dx=-20, dy=3, color='white').encode(\n    x=alt.X('count', stack='zero'),\n    y=alt.Y('Month',title=\"Month\"),\n    detail='Month_Year',\n    text=alt.Text('count'))\n\nbars+text\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Interpretation: Every year, the sales starts from a low point and as we progress to the second half of the year, sales starts to increase which can be seen as a monthly seasonality. And we can also see that Month on Month sales for all the months are increasing every year which is a good sign for the retail store. "},{"metadata":{},"cell_type":"markdown","source":"### 3) How many stores are there in a city ?"},{"metadata":{"trusted":true},"cell_type":"code","source":"city_store_df = train_subset[['store_nbr','city']].drop_duplicates().groupby('city').size().to_frame().reset_index().rename(columns={0:'count'})\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"bars=alt.Chart(city_store_df).mark_bar(color=\"darkorange\").encode(\n    x='count',\n    y=alt.Y('city', sort='-x'),\n     tooltip=[alt.Tooltip('count:Q')]\n)\n\ntext = bars.mark_text(\n    align='center',\n    baseline='middle',\n    dx=7 ,\n    size=12,\n).encode(\n    text='count')\n\n(bars + text).properties(\n    width=600,\n    height=200,\n    title=\"Stores in each city\")\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Interpretation: Quito has the maximum number of stores while Santo Domingo and Daule seem to be  new markets for the retail store with only 3 and 1 stores respectively.  "},{"metadata":{},"cell_type":"markdown","source":"### 4) Footfall Comparison in each store"},{"metadata":{"trusted":true,"collapsed":true},"cell_type":"code","source":"store_df=train_subset['store_nbr'].value_counts().to_frame().reset_index().rename(columns={'index':'Store_No','store_nbr':'total'}).sort_values(by = 'Store_No')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"alt.Chart(store_df).transform_joinaggregate(\n    TotalTime='sum(total)',\n).transform_calculate(\n    Percent_Of_Total_Transactions=\"datum.total / datum.TotalTime\"\n).mark_bar(color=\"maroon\").encode(\n    alt.X('Percent_Of_Total_Transactions:Q', axis=alt.Axis(format='.0%')),\n    y='Store_No:N',\n    tooltip=[alt.Tooltip('Store_No:N'),\n             alt.Tooltip('Percent_Of_Total_Transactions:Q')]\n).properties(height=500,width=300,title=\"Distribution of footfalls in each store-(Hover over each segment of Bar to understand distribution)\")\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Interpretation: Out of all 22 stores, Store 10, Store 16, Store 17, Store 20 and Store 21 are amongst the bottom 5 stores in terms of customers footfall."},{"metadata":{},"cell_type":"markdown","source":"### 5) Correlation between Oil Prices and Unit Sales"},{"metadata":{"trusted":true},"cell_type":"code","source":"oil_and_sales= train_subset[['date','oil_price','unit_sales']]\nd = {'oil_price':'oil_price', 'unit_sales':'total_sales'}\noil_and_sales = oil_and_sales.groupby('date').agg({'oil_price':'mean', 'unit_sales':'sum'}).rename(columns=d)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"alt.Chart(oil_and_sales.sample(500)).mark_circle().encode(\n    alt.X('oil_price',title=\"Oil Price\"),\n    alt.Y('total_sales',title=\"Total_Sales\"),\n     tooltip=[alt.Tooltip('oil_price'),\n            alt.Tooltip('total_sales')]\n    \n).configure_mark(color='green').properties(width=600,height=400,title=\"Oil Price vs Total Sales\")\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Interpretation: When oil prices are in the range of 30-60, total unit sold per day is not varying too much, but when oil price is increasing beyound 60 the total unit sold per day reduces by around 10k to 15k. This shows that the citizens of Ecuador face an impact of oil prices and their buying behaviour also changes when the oil prices increase beyond 60."},{"metadata":{},"cell_type":"markdown","source":"### 6) Product family demand comparison"},{"metadata":{"trusted":true},"cell_type":"code","source":"family_df = train_subset[['family','unit_sales']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"domain = ['BREAD/BAKERY', 'DAIRY']\nrange_ = ['green','orange']\nalt.Chart(family_df.sample(5000)).mark_boxplot().encode(\n    alt.X('family:O',sort=['BREAD/BAKERY','DAIRY']),\n    alt.Y('unit_sales:Q',title=\"Unit Sales under each product family\"),\n    color=alt.Color('family', scale=alt.Scale(domain=domain, range=range_)),\n    tooltip=[alt.Tooltip('family:O'),\n            alt.Tooltip('unit_sales')]\n).configure_mark().properties(width=600,height=400,title=\"Unit Sales vs Product Family (Five point summary)\")\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Interpretation: The units sold for both the product categories (i.e. Bread/bakery and Dairy) are almost similar and therefore their median, Quartile1 and Quartile 2 are approximately same. The only difference is in their Max value of Units sold and according to the visualised figure, we can observe that bread/bakery product has more units sold. We can also observe that the outliers for dairy products have more variation compared to bread/bakery product."},{"metadata":{},"cell_type":"markdown","source":"### 7) Citywise Sales of each product family"},{"metadata":{"trusted":true},"cell_type":"code","source":"city_df = train_subset[['city','family','unit_sales']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"alt.Chart(city_df.sample(5000)).mark_rect().encode(\n    x='city',\n    y='family',\n\n    color=alt.Color('unit_sales',scale=alt.Scale(type='log',scheme='reds')),\n    tooltip=['city','family','unit_sales']\n).properties(width=600,height=400,title=\"Citywise Avg Unit Sales for each product family - Hover over each block to see the numbers\")\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Interpretation: Based on the random sample taken for this visualisation from our dataset, we observe that people staying in city Quito are consuming bread/bakery products the most as compared to the other two cities. In Santo Domingo, people prefer dairy products over bread/bakery products and similar is the case with people staying in city Quale.\n#### Note: The results of this visualisation may vary in every attempt to run the code as I am picking up 5000 random samples."},{"metadata":{},"cell_type":"markdown","source":"### Forecasting"},{"metadata":{"trusted":true},"cell_type":"code","source":"!pip install sklearn\nimport xgboost as xgb\nimport random\nfrom sklearn.model_selection import train_test_split","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Only use this when you have removed all the files after EDA for space issues. \n# train_subset = pd.read_csv('./mycsvfile.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 1) Adding day and quarter fields using date field"},{"metadata":{"trusted":true},"cell_type":"code","source":"#train_subset['date'] = pd.to_datetime(train_subset['date'],format = '%Y-%m-%d')\ntrain_subset['day'] = train_subset['date'].dt.day\ntrain_subset['quarter'] = train_subset['date'].dt.quarter\ntrain_subset['month'] = train_subset['date'].dt.month\ntrain_subset['year'] = train_subset['date'].dt.year","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 2) Dropping columns which are correlated and hence will not be used"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset = train_subset.drop(['city','state','perishable','type','cluster','class','date','Month','Year'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset['onpromotion'] = train_subset['onpromotion'].replace(True,1)\ntrain_subset['onpromotion'] = train_subset['onpromotion'].replace(False,0)\ntrain_subset['onpromotion'] = train_subset['onpromotion'].replace('Not Mentioned',2)\ntrain_subset['family'] = train_subset['family'].replace('BREAD/BAKERY',0)\ntrain_subset['family'] = train_subset['family'].replace('DAIRY',1)\ntrain_subset['day_type'] = train_subset['day_type'].replace('Holiday',0)\ntrain_subset['day_type'] = train_subset['day_type'].replace('Work Day',1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_subset.to_csv('train_subset')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#train_subset['onpromotion'] = train_subset['onpromotion'].astype(int)\n# train1\ntrain1 = train_subset.drop(['unit_sales'], axis = 1)\nprint(train1.head())\n\n# train2\ntrain2 = train_subset.drop(['id','store_nbr','item_nbr','onpromotion', 'family','oil_price','day_type','month','year','day','quarter'], axis = 1)\nprint(train2.head())\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Xg_train, Xg_valid = train_test_split(train1, test_size=0.20, random_state=10)\nYg_train, Yg_valid = train_test_split(train2, test_size=0.20, random_state=10)\nfeatures1 = list(train1.columns.values)\nfeatures2 = list(train2.columns.values)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(features1)\nprint(features2)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dtrain = xgb.DMatrix(Xg_train[features1],Yg_train[features2])\ndvalid = xgb.DMatrix(Xg_valid[features1],Yg_valid[features2])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from sklearn.metrics import mean_squared_error","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#def rmspe(y, yhat):\n    #return np.sqrt(np.mean((yhat / y-1) ** 2))\ndef rmspe(y,yhat):\n    return","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import math\nfrom sklearn.preprocessing import minmax_scale","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"\n#A function to calculate Root Mean Squared Logarithmic Error (RMSLE)\ndef rmsle(y, y_pred):\n    assert len(y) == len(y_pred)\n    terms_to_sum = minmax_scale(np.sqrt(np.mean([(math.log(y_pred[i] + 1) - math.log(y[i] + 1)) ** 2.0 for i,pred in enumerate(y_pred)])),feature_range=(0,1))\n    return out_put","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def rmspe_xg(yhat, y):\n    y = np.expm1(y.get_label())\n    y1 = np.expm1(yhat)\n    return \"rmspe\", rmspe(y, yhat)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"params = {\"objective\": \"reg:linear\",\n          \"booster\" : \"gbtree\",\n          \"eta\": 0.3,\n          \"max_depth\": 10,\n          \"subsample\": 0.9,\n          \"colsample_bytree\": 0.7,\n          \"silent\": 1,\n          \"seed\": 1301\n          }\nnum_boost_round = 15\nwatchlist = [(dtrain, 'train'), (dvalid, 'eval')]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gbm = xgb.train(params, dtrain, num_boost_round, evals = watchlist,\n  early_stopping_rounds = 5, feval = rmspe_xg, verbose_eval = True)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"yhat = gbm.predict(xgb.DMatrix(Xg_valid[features1]))\nerror = rmspe(Yg_valid.unit_sales.values, np.expm1(yhat))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Prepare Test set for Forecasting\n### Note: All the assumptions taken on Train Set are same for Test Set as well."},{"metadata":{"trusted":true},"cell_type":"code","source":"#test = pd.read_csv('test.csv')\n#del test","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Filtering out data based on city and family\ntest_subset = test[test['store_nbr'].isin(store_number) & test['item_nbr'].isin(item_number)]\nprint(test_subset.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Left Join - Test & Stores\ntest_subset = pd.merge(test_subset, stores, on = 'store_nbr', how = 'left')\ntest_subset.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Left Join - Test & Items \ntest_subset = pd.merge(test_subset, items, on = 'item_nbr', how = 'left')\ntest_subset.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Left Join - Test & Oil\ntest_subset = pd.merge(test_subset, oil, on = 'date', how = 'left')\ntest_subset.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Left Join - Test & Holiday\ntest_subset = pd.merge(test_subset, holiday, on = 'date', how = 'left')\ntest_subset = test_subset.drop(['locale', 'locale_name','description','transferred'], axis=1)\ntest_subset = test_subset.rename(columns={\"type_y\": \"day_type\", \"type_x\": \"type\",\"dcoilwtico\":\"oil_price\"})\ntest_subset.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Handling Missing Values in Test Set"},{"metadata":{"trusted":true},"cell_type":"code","source":"test_subset.isnull().sum().sort_values(ascending=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Replacing NA values in day_type column with Work Day\ntest_subset['day_type'] = test_subset['day_type'].fillna(\"Work Day\")\n# Replace missing values in Oil_Price\ntest_subset['oil_price'] = test_subset[\"oil_price\"].fillna(axis = 0,method = 'ffill')\n# Creating a new category in onpromotion column, where NA values are replaced with \"Not Mentioned\"\ntest_subset['onpromotion'] = test_subset['onpromotion'].fillna(\"Not Mentioned\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_subset[\"date\"] = pd.to_datetime(test_subset[\"date\"])\ntest_subset['Day'] = test_subset['date'].dt.strftime('%d')\ntest_subset['Month'] = test_subset['date'].dt.strftime('%m')\ntest_subset['Year'] = test_subset['date'].dt.strftime('%Y')\ntest_subset['quarter'] = test_subset['date'].dt.quarter\ntest_subset.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Keeping only those columns which are used in Train set"},{"metadata":{"trusted":true},"cell_type":"code","source":"test_subset = test_subset.drop(['date','city','state','perishable','type','cluster','class'], axis=1)\ntest_subset.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Converting Categorical variables into Numerical variables."},{"metadata":{"trusted":true},"cell_type":"code","source":"test_subset['onpromotion'] = test_subset['onpromotion'].replace(True,1)\ntest_subset['onpromotion'] = test_subset['onpromotion'].replace('False',0)\ntest_subset['onpromotion'] = test_subset['onpromotion'].replace('Not Mentioned',2)\ntest_subset['family'] = test_subset['family'].replace('BREAD/BAKERY',0)\ntest_subset['family'] = test_subset['family'].replace('DAIRY',1)\ntest_subset['day_type'] = test_subset['day_type'].replace('Holiday',0)\ntest_subset['day_type'] = test_subset['day_type'].replace('Work Day',1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#test_subset['onpromotion'] = test_subset['onpromotion'].replace(0.0,'0')\n#test_subset['onpromotion'] = test_subset['onpromotion'].replace(1.0,'1')\n#test_subset['onpromotion'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Converting Object type into int\ntest_subset['onpromotion'] = test_subset['onpromotion'].astype(int)\ntest_subset['day'] = test_subset['Day'].astype(int)\ntest_subset['month'] = test_subset['Month'].astype(int)\ntest_subset['year'] = test_subset['Year'].astype(int)\ntest_subset.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Saving the file in the kernel\n#test_subset.to_csv('test_subset.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_dmatrix = xgb.DMatrix(test_subset[features1])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_prediction = gbm.predict(test_dmatrix)\nprint(\"Predictions\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"result = pd.DataFrame({\"id\": test_subset[\"id\"], 'unit_sales': np.expm1(test_prediction)})\nresult.to_csv(\"final_submission.csv\", index=False)\nprint(\"Submitted the final output file in Kaggle's kernel\")","execution_count":null,"outputs":[]}],"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":4,"nbformat_minor":4}