{"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":"**This is attemp to use regression to find the sales for future dates.**\n\n------- Kindly **Up-Vote** if you like. Really appreaciated ----------------\n\nBelwo is the case where a simple feature engineering along with regression models to predict teh sales for teh store on future dates.","metadata":{}},{"cell_type":"markdown","source":"First Step as always read all the data files and get the Dataframe, so we can analyse and create a feature lists","metadata":{}},{"cell_type":"code","source":"%matplotlib inline\n\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:50:52.877772Z","iopub.execute_input":"2022-07-29T05:50:52.878760Z","iopub.status.idle":"2022-07-29T05:50:52.907657Z","shell.execute_reply.started":"2022-07-29T05:50:52.878664Z","shell.execute_reply":"2022-07-29T05:50:52.906415Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def converttonumber(col, df):\n    colvalues = df[col].value_counts()\n    colvaluelist = colvalues.index\n    colvaluesseries = pd.Series(colvaluelist)\n    \n    for ivalue in range(0,len(colvaluesseries)):\n        df[col].replace(colvaluesseries[ivalue], colvaluesseries[colvaluesseries == colvaluesseries[ivalue]].index[0], inplace=True)\n    return df","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:50:56.938159Z","iopub.execute_input":"2022-07-29T05:50:56.938571Z","iopub.status.idle":"2022-07-29T05:50:56.944272Z","shell.execute_reply.started":"2022-07-29T05:50:56.938541Z","shell.execute_reply":"2022-07-29T05:50:56.943517Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"holidaysdf = pd.read_csv('../input/store-sales-time-series-forecasting/holidays_events.csv')\noildf = pd.read_csv('../input/store-sales-time-series-forecasting/oil.csv')\nstoredf = pd.read_csv('../input/store-sales-time-series-forecasting/stores.csv')\ntraindf = pd.read_csv('../input/store-sales-time-series-forecasting/train.csv')\ntestdf = pd.read_csv('../input/store-sales-time-series-forecasting/test.csv')\ntransactionsdf = pd.read_csv('../input/store-sales-time-series-forecasting/transactions.csv')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:51:02.478160Z","iopub.execute_input":"2022-07-29T05:51:02.479325Z","iopub.status.idle":"2022-07-29T05:51:05.922162Z","shell.execute_reply.started":"2022-07-29T05:51:02.479280Z","shell.execute_reply":"2022-07-29T05:51:05.921072Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Since the data is plit into multple files and dataframe its best to combine it and then review the features","metadata":{}},{"cell_type":"code","source":"mastertraindf = pd.merge(traindf, holidaysdf, on='date', how='left')\nmastertraindf = pd.merge(mastertraindf, oildf, on='date', how='left')\nmastertraindf = pd.merge(mastertraindf, storedf, on='store_nbr', how='left')\nmastertraindf = pd.merge(mastertraindf, transactionsdf, on=['store_nbr', 'date'], how='left')\nmastertraindf.rename(columns={'type_x':'holidaytype', 'type_y':'storetype'}, inplace=True)\nmastertraindf['holidaytype'].fillna('No', inplace=True)\n#mastertraindf.drop(['locale','locale_name','description'], inplace=True, axis=1)\nmastertraindf['test'] = 0\nmastertraindf.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:51:10.718063Z","iopub.execute_input":"2022-07-29T05:51:10.718595Z","iopub.status.idle":"2022-07-29T05:51:15.606293Z","shell.execute_reply.started":"2022-07-29T05:51:10.718560Z","shell.execute_reply":"2022-07-29T05:51:15.605406Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now we have a whole dataset with all features lined up. WE will now do the feature selection. So what we going to find.....\n\n- Correlation between different features\n- What feature can be eliminated\n- Find final feature list\n- Fill null values and encoding","metadata":{}},{"cell_type":"code","source":"f = plt.figure(figsize=(10, 10))\nplt.matshow(mastertraindf.corr(), fignum=f.number)\nplt.xticks(range(mastertraindf.select_dtypes(['number']).shape[1]), mastertraindf.select_dtypes(['number']).columns, fontsize=14, rotation=45)\nplt.yticks(range(mastertraindf.select_dtypes(['number']).shape[1]), mastertraindf.select_dtypes(['number']).columns, fontsize=14)\ncb = plt.colorbar()\ncb.ax.tick_params(labelsize=14)\nplt.title('Correlation Matrix', fontsize=16);","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:51:18.628245Z","iopub.execute_input":"2022-07-29T05:51:18.628667Z","iopub.status.idle":"2022-07-29T05:51:20.095538Z","shell.execute_reply.started":"2022-07-29T05:51:18.628630Z","shell.execute_reply":"2022-07-29T05:51:20.094599Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"From Correlation matrix it seems not much correlation exist in between featreus. So we dont have any dependant features.\n\nNow lets check what holiday has impact on the sales and trasnactions. One of the analysis I want to know is does type of holiday makes any dirrenece to sales and transactions. For example does local holiday makes more sale than national. Both are holiday but does locale has any impact\n\nNow based on below it seems that locale has no impact. What I am looking at is lets say if hout of 100 holidat only 10 i.e. 10%, but the sales and trasanctions for that locale is more than 10%. That would mean that for specific locale holiday sales and trasanctions are more compared to others. From below numebrs that is not the case. Numbers are in propotion irrespective of which holiday it is.","metadata":{}},{"cell_type":"code","source":"(mastertraindf[['locale', 'sales', 'transactions']].dropna().groupby(by='locale').sum()).join(mastertraindf[['locale', 'sales', 'transactions']].dropna().groupby(by='locale').count(), on='locale', how='inner', lsuffix='_sum', rsuffix='_count')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:51:26.968400Z","iopub.execute_input":"2022-07-29T05:51:26.969400Z","iopub.status.idle":"2022-07-29T05:51:27.722186Z","shell.execute_reply.started":"2022-07-29T05:51:26.969337Z","shell.execute_reply":"2022-07-29T05:51:27.721071Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Same analysis for tranfered as well","metadata":{}},{"cell_type":"code","source":"(mastertraindf[['transferred', 'sales', 'transactions']].dropna().groupby(by='transferred').sum()).join(mastertraindf[['transferred', 'sales', 'transactions']].dropna().groupby(by='transferred').count(), on='transferred', how='inner', lsuffix='_sum', rsuffix='_count')","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:51:32.057386Z","iopub.execute_input":"2022-07-29T05:51:32.057716Z","iopub.status.idle":"2022-07-29T05:51:32.693804Z","shell.execute_reply.started":"2022-07-29T05:51:32.057689Z","shell.execute_reply":"2022-07-29T05:51:32.692206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"If you calculate the above in terms of percentage then we dont see any significant influance these values have on sales and trasnactions. Which means it only matters if its holiday or not, it does not matter which holiday it is.\n\nSo from above we can remove the descriptive columns of holiday \"locale, description, transfered, locale_name\"\n\nAlso fom by view City and State will also not impact as store number will suffice as a store identity. So we can drop \"city, State\"","metadata":{}},{"cell_type":"code","source":"mastertraindf.drop(['locale', 'description', 'transferred', 'locale_name'], inplace=True, axis=1)\nmastertraindf.drop(['city', 'state'], inplace=True, axis=1)\nmastertraindf.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:51:37.167945Z","iopub.execute_input":"2022-07-29T05:51:37.168335Z","iopub.status.idle":"2022-07-29T05:51:37.673031Z","shell.execute_reply.started":"2022-07-29T05:51:37.168299Z","shell.execute_reply":"2022-07-29T05:51:37.671978Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"mastertraindf['dcoilwtico'].fillna(mastertraindf['dcoilwtico'].mean(), inplace=True)\nmastertraindf['holidaytype'].fillna('No', inplace=True)\nmastertraindf['transactions'].fillna(0, inplace=True)\nmastertraindf = converttonumber('holidaytype', mastertraindf)\nmastertraindf = converttonumber('family', mastertraindf)\nmastertraindf = converttonumber('storetype', mastertraindf)\nmastertraindf['date'] = mastertraindf['date'].str.replace('-','')\nmastertraindf.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:51:48.347793Z","iopub.execute_input":"2022-07-29T05:51:48.348444Z","iopub.status.idle":"2022-07-29T05:54:00.800263Z","shell.execute_reply.started":"2022-07-29T05:51:48.348410Z","shell.execute_reply":"2022-07-29T05:54:00.799079Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"I hhink we got the master feature list, so lets create similar test dataset\n\nSo now lets preapre the test dataset","metadata":{}},{"cell_type":"code","source":"mastertestdf = pd.merge(testdf, holidaysdf, on='date', how='left')\nmastertestdf = pd.merge(mastertestdf, oildf, on='date', how='left')\nmastertestdf = pd.merge(mastertestdf, storedf, on='store_nbr', how='left')\nmastertestdf = pd.merge(mastertestdf, transactionsdf, on=['store_nbr', 'date'], how='left')\nmastertestdf.rename(columns={'type_x':'holidaytype', 'type_y':'storetype'}, inplace=True)\nmastertestdf.drop(['locale','locale_name','description'], inplace=True, axis=1)\nmastertestdf['test'] = 1\n\n## Feature setup\nmastertestdf['dcoilwtico'].fillna(mastertestdf['dcoilwtico'].mean(), inplace=True)\nmastertestdf['holidaytype'].fillna('No', inplace=True)\nmastertestdf['transactions'].fillna(0, inplace=True)\nmastertestdf = converttonumber('holidaytype', mastertraindf)\nmastertestdf = converttonumber('family', mastertraindf)\nmastertestdf = converttonumber('storetype', mastertraindf)\nmastertestdf['date'] = mastertestdf['date'].str.replace('-','')\nmastertestdf.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:54:08.359908Z","iopub.execute_input":"2022-07-29T05:54:08.360309Z","iopub.status.idle":"2022-07-29T05:54:10.789834Z","shell.execute_reply.started":"2022-07-29T05:54:08.360274Z","shell.execute_reply":"2022-07-29T05:54:10.789047Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.model_selection import train_test_split\nfrom sklearn import linear_model\nfrom sklearn.linear_model import LinearRegression\nfrom sklearn.preprocessing import PolynomialFeatures\n\nX = mastertraindf[['id', 'date', 'store_nbr', 'family', 'onpromotion', 'holidaytype', 'dcoilwtico', 'storetype', 'cluster', 'transactions']]\ny = mastertraindf['sales']\nX_submission = mastertestdf[['id', 'date', 'store_nbr', 'family', 'onpromotion', 'holidaytype', 'dcoilwtico', 'storetype', 'cluster', 'transactions']]\ny_submission = mastertestdf['sales']\n\nX_train,X_test,y_train,y_test = train_test_split(X,y,test_size = 0.2)","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:54:32.570856Z","iopub.execute_input":"2022-07-29T05:54:32.571226Z","iopub.status.idle":"2022-07-29T05:54:34.671099Z","shell.execute_reply.started":"2022-07-29T05:54:32.571197Z","shell.execute_reply":"2022-07-29T05:54:34.669650Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#poly = PolynomialFeatures(degree = 3)\n#X_poly = poly.fit_transform(X_train)\n#X_test_poly = poly.fit_transform(X_test)\n \n#poly.fit(X_poly, y_train)\n#lin = LinearRegression()\n#lin.fit(X_poly, y_train)\n\n#lin.score(X_test_poly, y_test)\n","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:54:53.809594Z","iopub.execute_input":"2022-07-29T05:54:53.809952Z","iopub.status.idle":"2022-07-29T05:54:53.814491Z","shell.execute_reply.started":"2022-07-29T05:54:53.809921Z","shell.execute_reply":"2022-07-29T05:54:53.813591Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.ensemble import RandomForestRegressor\nRFR = RandomForestRegressor(n_estimators = 10)\nRFR.fit(X_train,y_train)\nRFR.score(X_test,y_test)","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:55:06.760502Z","iopub.execute_input":"2022-07-29T05:55:06.760989Z","iopub.status.idle":"2022-07-29T05:58:36.681834Z","shell.execute_reply.started":"2022-07-29T05:55:06.760949Z","shell.execute_reply":"2022-07-29T05:58:36.680324Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"y_pred = pd.DataFrame(RFR.predict(X_submission), index=y_submission.index, columns=['sales']).clip(0.0)\ny_pred.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:58:51.761719Z","iopub.execute_input":"2022-07-29T05:58:51.762671Z","iopub.status.idle":"2022-07-29T05:59:10.663829Z","shell.execute_reply.started":"2022-07-29T05:58:51.762632Z","shell.execute_reply":"2022-07-29T05:59:10.662561Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_submission = pd.read_csv('../input/store-sales-time-series-forecasting/sample_submission.csv')\nsample_submission['sales'] = y_pred['sales']\nsample_submission.to_csv('submission.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-07-29T05:59:45.333436Z","iopub.execute_input":"2022-07-29T05:59:45.333864Z","iopub.status.idle":"2022-07-29T05:59:45.540951Z","shell.execute_reply.started":"2022-07-29T05:59:45.333830Z","shell.execute_reply":"2022-07-29T05:59:45.539794Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}