{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.12","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":7391,"databundleVersionId":44328,"sourceType":"competition"}],"dockerImageVersionId":30822,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"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 matplotlib.pyplot as plt\nimport seaborn as sns\nfrom sklearn.dummy import DummyRegressor\nfrom sklearn.compose import ColumnTransformer\nfrom sklearn.pipeline import Pipeline\nfrom sklearn.preprocessing import StandardScaler, OneHotEncoder\nfrom sklearn.impute import SimpleImputer\nimport lightgbm as lgb\nimport optuna\nfrom sklearn.metrics import mean_squared_error ","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:05:40.791259Z","iopub.execute_input":"2025-01-17T11:05:40.791706Z","iopub.status.idle":"2025-01-17T11:05:45.314808Z","shell.execute_reply.started":"2025-01-17T11:05:40.791673Z","shell.execute_reply":"2025-01-17T11:05:45.313645Z"}},"outputs":[],"execution_count":null},{"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\n\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\npaths = []\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n        paths.append(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","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:05:45.316179Z","iopub.execute_input":"2025-01-17T11:05:45.317042Z","iopub.status.idle":"2025-01-17T11:05:45.325203Z","shell.execute_reply.started":"2025-01-17T11:05:45.317006Z","shell.execute_reply":"2025-01-17T11:05:45.323363Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"!pip install py7zr","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:05:45.326970Z","iopub.execute_input":"2025-01-17T11:05:45.327346Z","iopub.status.idle":"2025-01-17T11:05:53.685577Z","shell.execute_reply.started":"2025-01-17T11:05:45.327288Z","shell.execute_reply":"2025-01-17T11:05:53.684131Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import py7zr\nfor filenames in paths:\n    with py7zr.SevenZipFile(filenames, mode='r') as z_ref:\n        z_ref.extractall(path='/kaggle/working')","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:05:53.687409Z","iopub.execute_input":"2025-01-17T11:05:53.687811Z","iopub.status.idle":"2025-01-17T11:07:22.381537Z","shell.execute_reply.started":"2025-01-17T11:05:53.687779Z","shell.execute_reply":"2025-01-17T11:07:22.379964Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# Corporación Favorita Grocery Sales Forecasting\n\nPlease read data description: https://www.kaggle.com/c/favorita-grocery-sales-forecasting","metadata":{}},{"cell_type":"markdown","source":"## Splitting Data into Training and Test Sets\nThe data you are working with splited into **training** and **test** sets and stored in the following files:\n\n- **`/kaggle/working/train_data.csv`** – the training dataset used for model training.\n- **`/kaggle/working/test_data.csv`** – the test dataset used to evaluate the model's performance.","metadata":{"execution":{"iopub.status.busy":"2025-01-12T12:38:09.662179Z","iopub.execute_input":"2025-01-12T12:38:09.662543Z","iopub.status.idle":"2025-01-12T12:38:09.670455Z","shell.execute_reply.started":"2025-01-12T12:38:09.662506Z","shell.execute_reply":"2025-01-12T12:38:09.668489Z"}}},{"cell_type":"code","source":"%%time\ndata = pd.read_csv(\"/kaggle/working/train.csv\")\ndata[\"date\"] =  pd.to_datetime(data[\"date\"])","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:07:22.382849Z","iopub.execute_input":"2025-01-17T11:07:22.383222Z","iopub.status.idle":"2025-01-17T11:09:27.265876Z","shell.execute_reply.started":"2025-01-17T11:07:22.383193Z","shell.execute_reply":"2025-01-17T11:09:27.264152Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"monthly_counts_data = data['date'].dt.to_period('M').value_counts().sort_index()\n\nplt.figure(figsize=(12, 6))\nmonthly_counts_data.plot(kind='bar', color='skyblue')\nplt.title('Count of Data for Each Month for All data', fontsize=14)\nplt.xlabel('Month', fontsize=12)\nplt.ylabel('Count', fontsize=12)\nplt.xticks(rotation=45)\nplt.grid(axis='y', linestyle='--', alpha=0.7)\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:09:27.267752Z","iopub.execute_input":"2025-01-17T11:09:27.268222Z","iopub.status.idle":"2025-01-17T11:09:35.081937Z","shell.execute_reply.started":"2025-01-17T11:09:27.268182Z","shell.execute_reply":"2025-01-17T11:09:35.080627Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\nTHRESHOLD_TRAIN_DATE = pd.to_datetime(\"2017-07-01\")\nTHRESHOLD_TEST_DATE = pd.to_datetime(\"2017-08-01\")\n\ndata[  data[\"date\"] < THRESHOLD_TRAIN_DATE].to_csv(\"/kaggle/working/train_data.csv\")\ndata[(data[\"date\"] >= THRESHOLD_TRAIN_DATE)&(data[\"date\"] < THRESHOLD_TEST_DATE)].to_csv(\"/kaggle/working/test_data.csv\")\n\n%xdel data","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:09:35.083239Z","iopub.execute_input":"2025-01-17T11:09:35.083654Z","iopub.status.idle":"2025-01-17T11:16:50.152095Z","shell.execute_reply.started":"2025-01-17T11:09:35.083624Z","shell.execute_reply":"2025-01-17T11:16:50.151024Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\ndf = pd.read_csv(\"/kaggle/working/train_data.csv\")\ntest_df = pd.read_csv(\"/kaggle/working/test_data.csv\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:16:50.155025Z","iopub.execute_input":"2025-01-17T11:16:50.155351Z","iopub.status.idle":"2025-01-17T11:18:37.438125Z","shell.execute_reply.started":"2025-01-17T11:16:50.155324Z","shell.execute_reply":"2025-01-17T11:18:37.436982Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df[\"date\"] = pd.to_datetime(df[\"date\"])\ntest_df[\"date\"] = pd.to_datetime(test_df[\"date\"])","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:18:37.440086Z","iopub.execute_input":"2025-01-17T11:18:37.440426Z","iopub.status.idle":"2025-01-17T11:18:49.316853Z","shell.execute_reply.started":"2025-01-17T11:18:37.440397Z","shell.execute_reply":"2025-01-17T11:18:49.315581Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"monthly_counts = df['date'].dt.to_period('M').value_counts().sort_index().reset_index()\nmonthly_counts_test = test_df['date'].dt.to_period('M').value_counts().sort_index().reset_index()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:18:49.317979Z","iopub.execute_input":"2025-01-17T11:18:49.318275Z","iopub.status.idle":"2025-01-17T11:18:58.686413Z","shell.execute_reply.started":"2025-01-17T11:18:49.318251Z","shell.execute_reply":"2025-01-17T11:18:58.684856Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"monthly_counts['type'] = 'Train'\nmonthly_counts_test['type'] = 'Test'\ncombined = pd.concat([monthly_counts, monthly_counts_test])\n\nplt.figure(figsize=(12, 6))\nsns.barplot(data=combined, x='date', y='count', hue='type', palette=['blue', 'red'])\nplt.title('Count of Data for Each Month (Train vs Test)', fontsize=14)\nplt.xlabel('Month', fontsize=12)\nplt.ylabel('Count', fontsize=12)\nplt.xticks(rotation=45)\nplt.legend(title='Dataset')\nplt.grid(axis='y', linestyle='--', alpha=0.7)\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:18:58.687724Z","iopub.execute_input":"2025-01-17T11:18:58.688053Z","iopub.status.idle":"2025-01-17T11:18:59.899524Z","shell.execute_reply.started":"2025-01-17T11:18:58.688021Z","shell.execute_reply":"2025-01-17T11:18:59.898055Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:18:59.900635Z","iopub.execute_input":"2025-01-17T11:18:59.900961Z","iopub.status.idle":"2025-01-17T11:18:59.921197Z","shell.execute_reply.started":"2025-01-17T11:18:59.900934Z","shell.execute_reply":"2025-01-17T11:18:59.919941Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Additional Data\n\n\nTo make more effective use of the data, you also have access to several auxiliary datasets containing contextual information:\n\n#### 1. **`holidays_events.csv`**\n   - **Description**:\n     Contains information about holidays and events that may influence sales.\n   - **Useful Columns**:\n     - `date`: The date of the holiday or event.\n     - `type`: The type of event (national, regional, local).\n     - `locale`: The level of impact (national, regional, local).\n     - `description`: A description of the event.\n   - **How to Use**:\n     - Add a flag indicating whether a date is a holiday to the training and test sets.\n     - Categorize holidays by their `type` and consider their impact on sales.\n     - Use the `locale` column to analyze regional effects.","metadata":{"execution":{"iopub.status.busy":"2024-12-22T19:12:02.31896Z","iopub.execute_input":"2024-12-22T19:12:02.319553Z","iopub.status.idle":"2024-12-22T19:12:02.32548Z","shell.execute_reply.started":"2024-12-22T19:12:02.319516Z","shell.execute_reply":"2024-12-22T19:12:02.323891Z"}}},{"cell_type":"code","source":"holiday_events = pd.read_csv(\"/kaggle/working/holidays_events.csv\")\nprint(holiday_events.shape)\nholiday_events.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:18:59.922653Z","iopub.execute_input":"2025-01-17T11:18:59.923074Z","iopub.status.idle":"2025-01-17T11:18:59.943816Z","shell.execute_reply.started":"2025-01-17T11:18:59.923041Z","shell.execute_reply":"2025-01-17T11:18:59.942424Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### 2. **`items.csv`**\n   - **Description**:\n     Contains information about the items being sold.\n   - **Useful Columns**:\n     - `item_nbr`: Unique identifier for each item.\n     - `family`: The category or family the item belongs to (e.g., \"Beverages\").\n     - `class`: A finer categorization of items.\n     - `perishable`: A binary indicator for whether the item is perishable (1 for perishable, 0 otherwise).\n   - **How to Use**:\n     - Use `family` and `class` to group items and analyze trends across categories.\n     - Include the `perishable` indicator to weight predictions for perishable items.","metadata":{}},{"cell_type":"code","source":"items = pd.read_csv(\"/kaggle/working/items.csv\")\nprint(items.shape)\nitems.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:18:59.945271Z","iopub.execute_input":"2025-01-17T11:18:59.945624Z","iopub.status.idle":"2025-01-17T11:18:59.967837Z","shell.execute_reply.started":"2025-01-17T11:18:59.945584Z","shell.execute_reply":"2025-01-17T11:18:59.966853Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### 3. **`oil.csv`**\n   - **Description**:\n     Contains daily oil price data, which can serve as an external economic indicator.\n   - **Useful Columns**:\n     - `date`: The date corresponding to the oil price.\n     - `dcoilwtico`: The daily oil price.\n   - **How to Use**:\n     - Join this dataset with the training and test sets on the `date` column to include oil price as a feature.\n     - Analyze the correlation between oil price fluctuations and sales trends.","metadata":{}},{"cell_type":"code","source":"oil = pd.read_csv(\"/kaggle/working/oil.csv\")\nprint(oil.shape)\noil.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:18:59.968940Z","iopub.execute_input":"2025-01-17T11:18:59.969415Z","iopub.status.idle":"2025-01-17T11:18:59.988059Z","shell.execute_reply.started":"2025-01-17T11:18:59.969376Z","shell.execute_reply":"2025-01-17T11:18:59.986781Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### 4. **`stores.csv`**\n   - **Description**:\n     Contains information about the stores where items are sold.\n   - **Useful Columns**:\n     - `store_nbr`: Unique identifier for each store.\n     - `city`: The city where the store is located.\n     - `state`: The state where the store is located.\n     - `type`: The store type.\n     - `cluster`: A grouping of similar stores based on sales patterns.\n   - **How to Use**:\n     - Use `city` and `state` for regional analysis of sales trends.\n     - Incorporate `type` and `cluster` to identify patterns and group stores with similar behavior.\n","metadata":{}},{"cell_type":"code","source":"stores = pd.read_csv(\"/kaggle/working/stores.csv\")\nprint(stores.shape)\nstores.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:18:59.989362Z","iopub.execute_input":"2025-01-17T11:18:59.989738Z","iopub.status.idle":"2025-01-17T11:19:00.006775Z","shell.execute_reply.started":"2025-01-17T11:18:59.989694Z","shell.execute_reply":"2025-01-17T11:19:00.005346Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"**[READ DATA DESCRIPTION CAREFULY](https://www.kaggle.com/competitions/favorita-grocery-sales-forecasting) !!!!** \n\n## Exploratory Data Analysis and Model Training\n\n### 1. Baseline Model\n- Consider which model would be suitable as `DummyModel` for this task, then create, train, and use its predictions as a baseline.\n\n### 2. Feature Engineering\n- Generate new features by incorporating data from additional datasets to enhance the model's predictive capabilities.\n\n### 3. Model Training and Optimization\n- Utilize advanced regression models, such as **LGBM Regression** or **CatBoost Regression**, to achieve the best results. Focus on hyperparameter tuning and evaluation metrics to optimize performance. **Use foremention Normalized Weighted Root Mean Squared Logarithmic Error**.  \n\n### Thats All :-)\n","metadata":{}},{"cell_type":"markdown","source":" ## EDA and Feature Engineering","metadata":{}},{"cell_type":"markdown","source":"let's start by just observing what datasets we have.","metadata":{}},{"cell_type":"code","source":"df.info()\nprint('\\n null vals')\ndf.isnull().any()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:00.007934Z","iopub.execute_input":"2025-01-17T11:19:00.008606Z","iopub.status.idle":"2025-01-17T11:19:07.141213Z","shell.execute_reply.started":"2025-01-17T11:19:00.008522Z","shell.execute_reply":"2025-01-17T11:19:07.140281Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"onpromotion may be null.","metadata":{}},{"cell_type":"code","source":"test_df.info()\nprint('\\n null vals')\ntest_df.isnull().any()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:07.142190Z","iopub.execute_input":"2025-01-17T11:19:07.142670Z","iopub.status.idle":"2025-01-17T11:19:07.171828Z","shell.execute_reply.started":"2025-01-17T11:19:07.142642Z","shell.execute_reply":"2025-01-17T11:19:07.170424Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"we have same features , without null values obviously.","metadata":{}},{"cell_type":"code","source":"print('Holidays')\ndisplay(holiday_events.info())\ndisplay(holiday_events.isnull().any())\n\nprint('\\n items')\ndisplay(items.info())\ndisplay(items.isnull().any())\n\nprint('\\n oil')\ndisplay(oil.info())\ndisplay(oil.isnull().any())\n\nprint('\\nstores')\ndisplay(stores.info())\ndisplay(stores.isnull().any())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:07.172939Z","iopub.execute_input":"2025-01-17T11:19:07.173279Z","iopub.status.idle":"2025-01-17T11:19:07.226117Z","shell.execute_reply.started":"2025-01-17T11:19:07.173243Z","shell.execute_reply":"2025-01-17T11:19:07.225192Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"everything fine except oil price(deoilwtco) , for now just remember and after analysis we fill these values.everithing else is without null values , however some values may be not logically zeros.","metadata":{}},{"cell_type":"markdown","source":"back to training data.\nlets analyise data over year and months over some year.\n","metadata":{}},{"cell_type":"code","source":"df[\"date\"].dt.year.value_counts(sort = False).plot.bar()\nplt.show()\ndf2016 = df[df[\"date\"].dt.year == 2016]\ndf2016[\"date\"].dt.month.value_counts(sort = False).plot.bar()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:07.227345Z","iopub.execute_input":"2025-01-17T11:19:07.227749Z","iopub.status.idle":"2025-01-17T11:19:16.437022Z","shell.execute_reply.started":"2025-01-17T11:19:07.227708Z","shell.execute_reply":"2025-01-17T11:19:16.436023Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"overall stores sale numbers follow a trend, for last year we may not have enough samples . and sales may be dependant on time of the year.","metadata":{}},{"cell_type":"code","source":"%xdel df2016","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:16.438155Z","iopub.execute_input":"2025-01-17T11:19:16.438495Z","iopub.status.idle":"2025-01-17T11:19:16.514546Z","shell.execute_reply.started":"2025-01-17T11:19:16.438469Z","shell.execute_reply":"2025-01-17T11:19:16.513429Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"now that we have seen data over the years. I decided that I will be using market data only after 2017-01-01","metadata":{}},{"cell_type":"code","source":"df = df[df[\"date\"] >= pd.to_datetime(\"2017-01-01\")]\ndf.memory_usage().sum()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:16.515819Z","iopub.execute_input":"2025-01-17T11:19:16.516209Z","iopub.status.idle":"2025-01-17T11:19:17.717545Z","shell.execute_reply.started":"2025-01-17T11:19:16.516173Z","shell.execute_reply":"2025-01-17T11:19:17.716411Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"Let's go to store information before considering holidays , because holidays depend on location.","metadata":{}},{"cell_type":"code","source":"import plotly.express as px\nimport plotly.graph_objects as go\nfrom plotly.offline import init_notebook_mode, iplot\n\ninit_notebook_mode(connected=True)\n\nstore_counts = stores.groupby(['state', 'city' ]).size().reset_index(name='store_count')\n\nfig = px.treemap(\n    store_counts,\n    path=['state', 'city' ],  \n    values='store_count',   \n    title='Treemap of Store Counts Across States and Cities',\n    color='store_count',    \n    color_continuous_scale='Viridis' \n)\n\nfig.show(renderer='iframe')\n\n\n\n\n%xdel store_counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:17.724026Z","iopub.execute_input":"2025-01-17T11:19:17.724390Z","iopub.status.idle":"2025-01-17T11:19:20.362513Z","shell.execute_reply.started":"2025-01-17T11:19:17.724362Z","shell.execute_reply":"2025-01-17T11:19:20.361258Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"type_counts = stores['type'].value_counts()\ntype_counts.plot(kind='bar', color='skyblue', edgecolor='black', figsize=(8, 6))\nplt.title('Number of Stores Across Different Types', fontsize=14)\nplt.xlabel('Store Type', fontsize=12)\nplt.ylabel('Number of Stores', fontsize=12)\nplt.show()\n%xdel type_counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:20.364858Z","iopub.execute_input":"2025-01-17T11:19:20.365149Z","iopub.status.idle":"2025-01-17T11:19:20.639265Z","shell.execute_reply.started":"2025-01-17T11:19:20.365125Z","shell.execute_reply":"2025-01-17T11:19:20.637962Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"type_counts = stores['cluster'].value_counts()\ntype_counts.plot(kind='bar', color='skyblue', edgecolor='black', figsize=(8, 6))\nplt.title('Number of Stores Across Different clusters', fontsize=14)\nplt.xlabel('Store cluster', fontsize=12)\nplt.ylabel('Number of Stores', fontsize=12)\nplt.show()\n%xdel type_counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:20.640565Z","iopub.execute_input":"2025-01-17T11:19:20.641008Z","iopub.status.idle":"2025-01-17T11:19:20.902100Z","shell.execute_reply.started":"2025-01-17T11:19:20.640966Z","shell.execute_reply":"2025-01-17T11:19:20.900928Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"let's integrate this data into our main df.","metadata":{}},{"cell_type":"code","source":"df = df.merge(stores , on = 'store_nbr' , how = 'left')\ndisplay(df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:20.903189Z","iopub.execute_input":"2025-01-17T11:19:20.903476Z","iopub.status.idle":"2025-01-17T11:19:23.883528Z","shell.execute_reply.started":"2025-01-17T11:19:20.903453Z","shell.execute_reply":"2025-01-17T11:19:23.882428Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"alright, we can now analyse sales by location and store type/cluster.","metadata":{}},{"cell_type":"code","source":"sales_by_state_city = df.groupby(['state', 'city' ])['unit_sales'].sum().reset_index(name='sales_by_state_city')\n\nfig = px.treemap(\n    sales_by_state_city,\n    path=['state', 'city' ],  \n    values='sales_by_state_city',   \n    title='Treemap of unit sales Across States and Cities',\n    color='sales_by_state_city',    \n    color_continuous_scale='Viridis' \n)\n\nfig.show(renderer='iframe')\n\n%xdel sales_by_state_city","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:23.884710Z","iopub.execute_input":"2025-01-17T11:19:23.885107Z","iopub.status.idle":"2025-01-17T11:19:26.432734Z","shell.execute_reply.started":"2025-01-17T11:19:23.885068Z","shell.execute_reply":"2025-01-17T11:19:26.431775Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"highly informative treemap. sales are quite big in main cities than others.","metadata":{}},{"cell_type":"code","source":"type_counts = df.groupby(['type'])['unit_sales'].sum()\ntype_counts.plot(kind='bar', color='skyblue', edgecolor='black', figsize=(8, 6) )\nplt.title('Number of unit sales Across Different Types', fontsize=14)\nplt.xlabel('Store Type', fontsize=12)\nplt.ylabel('unit sales', fontsize=12)\nplt.show()\n\n\ntype_counts = df.groupby(['cluster'])['unit_sales'].sum()\ntype_counts.plot(kind='bar', color='skyblue', edgecolor='black', figsize=(8, 6) )\nplt.title('Number of unit sales Across Different Clusters', fontsize=14)\nplt.xlabel('Store Cluster', fontsize=12)\nplt.ylabel('unit sales', fontsize=12)\nplt.show()\n\n%xdel type_counts\n\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:26.434014Z","iopub.execute_input":"2025-01-17T11:19:26.434462Z","iopub.status.idle":"2025-01-17T11:19:28.457123Z","shell.execute_reply.started":"2025-01-17T11:19:26.434407Z","shell.execute_reply":"2025-01-17T11:19:28.455880Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"test_df = test_df.merge(stores , on = 'store_nbr' , how = 'left')","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:28.458422Z","iopub.execute_input":"2025-01-17T11:19:28.458851Z","iopub.status.idle":"2025-01-17T11:19:28.854328Z","shell.execute_reply.started":"2025-01-17T11:19:28.458812Z","shell.execute_reply":"2025-01-17T11:19:28.853205Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%xdel stores","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:28.855347Z","iopub.execute_input":"2025-01-17T11:19:28.855619Z","iopub.status.idle":"2025-01-17T11:19:28.860365Z","shell.execute_reply.started":"2025-01-17T11:19:28.855597Z","shell.execute_reply":"2025-01-17T11:19:28.859243Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"alright,let's now move onto holidays.","metadata":{}},{"cell_type":"code","source":"holidays_counts = holiday_events['type'].value_counts()\nholidays_counts.plot(kind='bar', color='skyblue', edgecolor='black', figsize=(8, 6))\nplt.title('Number of different holiday types ', fontsize=14)\nplt.xlabel('type', fontsize=12)\nplt.ylabel('Number', fontsize=12)\nplt.show()\n%xdel holidays_counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:28.861591Z","iopub.execute_input":"2025-01-17T11:19:28.861970Z","iopub.status.idle":"2025-01-17T11:19:29.058115Z","shell.execute_reply.started":"2025-01-17T11:19:28.861930Z","shell.execute_reply":"2025-01-17T11:19:29.056386Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"Let's just leave holiday. others may vary in time.","metadata":{}},{"cell_type":"code","source":"holiday_events['is_holiday'] = holiday_events['type'] == 'Holiday'\nholiday_events.drop(columns=['type'], inplace=True)\nholiday_events = holiday_events[holiday_events['is_holiday']]\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:29.059920Z","iopub.execute_input":"2025-01-17T11:19:29.060405Z","iopub.status.idle":"2025-01-17T11:19:29.069026Z","shell.execute_reply.started":"2025-01-17T11:19:29.060365Z","shell.execute_reply":"2025-01-17T11:19:29.067688Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"display(holiday_events.head(20))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:29.070143Z","iopub.execute_input":"2025-01-17T11:19:29.070518Z","iopub.status.idle":"2025-01-17T11:19:29.098939Z","shell.execute_reply.started":"2025-01-17T11:19:29.070472Z","shell.execute_reply":"2025-01-17T11:19:29.097658Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"holidays_counts = holiday_events['locale'].value_counts()\nholidays_counts.plot(kind='bar', color='skyblue', edgecolor='black', figsize=(8, 6))\nplt.title('Number of different holiday locale types ', fontsize=14)\nplt.xlabel('locale', fontsize=12)\nplt.ylabel('Number', fontsize=12)\nplt.show()\n%xdel holidays_counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:29.100098Z","iopub.execute_input":"2025-01-17T11:19:29.100432Z","iopub.status.idle":"2025-01-17T11:19:29.293419Z","shell.execute_reply.started":"2025-01-17T11:19:29.100406Z","shell.execute_reply":"2025-01-17T11:19:29.292243Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"let's split by national regional and local and add to corresponding rows in df.","metadata":{}},{"cell_type":"code","source":"national_holidays = holiday_events[holiday_events['locale'] == 'National']\nlocal_holidays = holiday_events[holiday_events['locale'] == 'Local']\nregional_holidays = holiday_events[holiday_events['locale'] == 'Regional']\n%xdel holiday_events\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:29.294570Z","iopub.execute_input":"2025-01-17T11:19:29.294920Z","iopub.status.idle":"2025-01-17T11:19:29.302752Z","shell.execute_reply.started":"2025-01-17T11:19:29.294893Z","shell.execute_reply":"2025-01-17T11:19:29.301663Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"print(\"National Holidays Count:\")\nprint(national_holidays['locale'].value_counts())\n\nprint(\"\\nLocal Holidays Count:\")\nprint(local_holidays['locale'].value_counts())\n\nprint(\"\\nRegional Holidays Count:\")\nprint(regional_holidays['locale'].value_counts())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:29.303925Z","iopub.execute_input":"2025-01-17T11:19:29.304494Z","iopub.status.idle":"2025-01-17T11:19:29.328339Z","shell.execute_reply.started":"2025-01-17T11:19:29.304442Z","shell.execute_reply":"2025-01-17T11:19:29.327032Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"I just left national holidays for computability reasons.","metadata":{}},{"cell_type":"code","source":"df['date'] = pd.to_datetime(df['date'])\n\nnational_holidays['date'] = pd.to_datetime(national_holidays['date'])\nregional_holidays['date'] = pd.to_datetime(regional_holidays['date'])\nlocal_holidays['date'] = pd.to_datetime(local_holidays['date'])\n\nregional_holidays_dict = regional_holidays.set_index('date')['locale_name'].to_dict()\nlocal_holidays_dict = local_holidays.set_index('date')['locale_name'].to_dict()\n\ndef merge_holidays(data):\n    data['is_holiday'] = data['date'].isin(national_holidays['date'])\n    \n    data['is_holiday'] |= data['date'].map(regional_holidays_dict) == data['state']\n\n    \n    data['is_holiday'] |= data['date'].map(local_holidays_dict) == data['city']\n\nmerge_holidays(df)\ndisplay(df.head(10))\n\nmerge_holidays(test_df)\ndisplay(test_df.head(10))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:29.329684Z","iopub.execute_input":"2025-01-17T11:19:29.330119Z","iopub.status.idle":"2025-01-17T11:19:35.678491Z","shell.execute_reply.started":"2025-01-17T11:19:29.330076Z","shell.execute_reply":"2025-01-17T11:19:35.677433Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"now let's go to the items.","metadata":{}},{"cell_type":"code","source":"item_family_counts = items['family'].value_counts()\nitem_family_counts.plot(kind='bar', color='skyblue', edgecolor='black', figsize=(8, 6))\nplt.title('number of items in different family', fontsize=14)\nplt.xlabel('family', fontsize=12)\nplt.ylabel('number', fontsize=12)\nplt.show()\n%xdel item_family_counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:35.679414Z","iopub.execute_input":"2025-01-17T11:19:35.679681Z","iopub.status.idle":"2025-01-17T11:19:36.063199Z","shell.execute_reply.started":"2025-01-17T11:19:35.679659Z","shell.execute_reply":"2025-01-17T11:19:36.062137Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = df.merge(items , on = 'item_nbr' , how = 'left')\ndisplay(df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:36.064461Z","iopub.execute_input":"2025-01-17T11:19:36.064903Z","iopub.status.idle":"2025-01-17T11:19:44.884270Z","shell.execute_reply.started":"2025-01-17T11:19:36.064865Z","shell.execute_reply":"2025-01-17T11:19:44.882599Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"test_df = test_df.merge(items , on = 'item_nbr' , how = 'left')\ndisplay(test_df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:44.885576Z","iopub.execute_input":"2025-01-17T11:19:44.885958Z","iopub.status.idle":"2025-01-17T11:19:45.530427Z","shell.execute_reply.started":"2025-01-17T11:19:44.885928Z","shell.execute_reply":"2025-01-17T11:19:45.529283Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"family_counts = df.groupby(['family'])['unit_sales'].sum()\nfamily_counts.plot(kind='bar', color='skyblue', edgecolor='black', figsize=(8, 6) )\nplt.title('Number of unit sales Across Different item family', fontsize=14)\nplt.xlabel('item family ', fontsize=12)\nplt.ylabel('unit sales', fontsize=12)\nplt.show()\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:45.531737Z","iopub.execute_input":"2025-01-17T11:19:45.532182Z","iopub.status.idle":"2025-01-17T11:19:47.315655Z","shell.execute_reply.started":"2025-01-17T11:19:45.532142Z","shell.execute_reply":"2025-01-17T11:19:47.314494Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"we can see that Grocery and Beverages are leading items in sales.","metadata":{}},{"cell_type":"code","source":"%xdel items","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:47.316877Z","iopub.execute_input":"2025-01-17T11:19:47.317191Z","iopub.status.idle":"2025-01-17T11:19:47.322050Z","shell.execute_reply.started":"2025-01-17T11:19:47.317160Z","shell.execute_reply":"2025-01-17T11:19:47.320893Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"Let's go to the oil prices. it contains null values and we will try to fill it.","metadata":{}},{"cell_type":"code","source":"oil['date'] = pd.to_datetime(oil['date']) \noil_2016 = oil[oil['date'].dt.year == 2016]  \n\nplt.figure(figsize=(10, 5))\nplt.plot(oil_2016['date'], oil_2016['dcoilwtico'], marker='o', linestyle='-', label='Oil Price')\nplt.title('Oil Prices in 2016')\nplt.xlabel('Date')\nplt.ylabel('Price')\nplt.legend()\nplt.grid(True)\nplt.xticks(rotation=45)\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:47.323350Z","iopub.execute_input":"2025-01-17T11:19:47.323792Z","iopub.status.idle":"2025-01-17T11:19:47.779094Z","shell.execute_reply.started":"2025-01-17T11:19:47.323753Z","shell.execute_reply":"2025-01-17T11:19:47.777729Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"If we recall 2016 graph for sales , it may affect little on number of sales.","metadata":{}},{"cell_type":"code","source":"%xdel oil_2016","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:47.780409Z","iopub.execute_input":"2025-01-17T11:19:47.780798Z","iopub.status.idle":"2025-01-17T11:19:47.785921Z","shell.execute_reply.started":"2025-01-17T11:19:47.780768Z","shell.execute_reply":"2025-01-17T11:19:47.784594Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"oil[\"dcoilwtico\"].ffill(inplace=True)\nprint(oil.isnull().sum())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:47.786952Z","iopub.execute_input":"2025-01-17T11:19:47.787247Z","iopub.status.idle":"2025-01-17T11:19:47.808225Z","shell.execute_reply.started":"2025-01-17T11:19:47.787221Z","shell.execute_reply":"2025-01-17T11:19:47.806971Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = df.merge(oil, how=\"left\", on=\"date\")\ntest_df = test_df.merge(oil, how=\"left\", on=\"date\")\ndisplay(df.head())\ndisplay(test_df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:47.809867Z","iopub.execute_input":"2025-01-17T11:19:47.810280Z","iopub.status.idle":"2025-01-17T11:19:55.355372Z","shell.execute_reply.started":"2025-01-17T11:19:47.810239Z","shell.execute_reply":"2025-01-17T11:19:55.354507Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df[\"dcoilwtico\"].ffill(inplace=True)\ntest_df[\"dcoilwtico\"].ffill(inplace=True)\ndf[\"dcoilwtico\"].bfill(inplace=True)\ntest_df[\"dcoilwtico\"].bfill(inplace=True)\n\n\ndf[\"unit_sales\"].corr(df[\"dcoilwtico\"])","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:55.356427Z","iopub.execute_input":"2025-01-17T11:19:55.356760Z","iopub.status.idle":"2025-01-17T11:19:55.787049Z","shell.execute_reply.started":"2025-01-17T11:19:55.356735Z","shell.execute_reply":"2025-01-17T11:19:55.785772Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"display(df.isnull().sum())\ndisplay(test_df.isnull().sum())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:19:55.788196Z","iopub.execute_input":"2025-01-17T11:19:55.788522Z","iopub.status.idle":"2025-01-17T11:20:01.057681Z","shell.execute_reply.started":"2025-01-17T11:19:55.788494Z","shell.execute_reply":"2025-01-17T11:20:01.056412Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## preprocessing and training","metadata":{}},{"cell_type":"markdown","source":"we will use dummyreggressor for baseline model.","metadata":{}},{"cell_type":"code","source":"dummy_regressor = DummyRegressor(strategy=\"mean\")\ndummy_regressor.fit(df, df['unit_sales'].values)\n\ndisplay(mean_squared_error(dummy_regressor.predict(test_df) , test_df['unit_sales']))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:20:01.059006Z","iopub.execute_input":"2025-01-17T11:20:01.059432Z","iopub.status.idle":"2025-01-17T11:20:01.121830Z","shell.execute_reply.started":"2025-01-17T11:20:01.059388Z","shell.execute_reply":"2025-01-17T11:20:01.120867Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:20:01.122878Z","iopub.execute_input":"2025-01-17T11:20:01.123257Z","iopub.status.idle":"2025-01-17T11:20:01.139746Z","shell.execute_reply.started":"2025-01-17T11:20:01.123223Z","shell.execute_reply":"2025-01-17T11:20:01.138490Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"from sklearn.preprocessing import LabelEncoder\n\ncategorical_cols = ['store_nbr', 'city', 'state', 'type', 'cluster', 'family', 'is_holiday']\nlabel_encoders = {}\n\nfor col in categorical_cols:\n    le = LabelEncoder()\n    df[col] = le.fit_transform(df[col])\n    label_encoders[col] = le \n    test_df[col] = le.fit_transform(test_df[col])\n\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:20:01.141037Z","iopub.execute_input":"2025-01-17T11:20:01.141479Z","iopub.status.idle":"2025-01-17T11:20:16.960029Z","shell.execute_reply.started":"2025-01-17T11:20:01.141440Z","shell.execute_reply":"2025-01-17T11:20:16.958903Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['date'] = pd.to_datetime(df['date']) \ndf['year'] = df['date'].dt.year\ndf['month'] = df['date'].dt.month\ndf['day'] = df['date'].dt.day\ndf['day_of_week'] = df['date'].dt.dayofweek\n\ntest_df['date'] = pd.to_datetime(test_df['date']) \ntest_df['year'] = test_df['date'].dt.year\ntest_df['month'] = test_df['date'].dt.month\ntest_df['day'] = test_df['date'].dt.day\ntest_df['day_of_week'] = test_df['date'].dt.dayofweek","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:20:16.961120Z","iopub.execute_input":"2025-01-17T11:20:16.961557Z","iopub.status.idle":"2025-01-17T11:20:19.655581Z","shell.execute_reply.started":"2025-01-17T11:20:16.961480Z","shell.execute_reply":"2025-01-17T11:20:19.654506Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = df.drop(['id', 'Unnamed: 0', 'date'], axis=1)\ntest_df = test_df.drop(['id', 'Unnamed: 0', 'date'], axis=1)\n\ndf['onpromotion'] = df['onpromotion'].astype(bool)\n\n\nX_train = df.drop(['unit_sales'], axis=1)  \nY_train = df['unit_sales']  \n\nX_test = test_df.drop(['unit_sales'], axis=1) \nY_test = test_df['unit_sales']  \n\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:20:19.656683Z","iopub.execute_input":"2025-01-17T11:20:19.657114Z","iopub.status.idle":"2025-01-17T11:20:22.690887Z","shell.execute_reply.started":"2025-01-17T11:20:19.657021Z","shell.execute_reply":"2025-01-17T11:20:22.689680Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%xdel df \n    \n%xdel test_df","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:20:22.692020Z","iopub.execute_input":"2025-01-17T11:20:22.692441Z","iopub.status.idle":"2025-01-17T11:20:22.704381Z","shell.execute_reply.started":"2025-01-17T11:20:22.692412Z","shell.execute_reply":"2025-01-17T11:20:22.703122Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"\ntrain_data = lgb.Dataset(X_train, label=Y_train)\ntest_data = lgb.Dataset(X_test, label=Y_test, reference=train_data)\n\n\nparams = {\n    'objective': 'regression',\n    'metric': 'rmse',\n    'boosting_type': 'gbdt',\n    'num_leaves': 31,\n    'learning_rate': 0.05,\n    'feature_fraction': 0.9\n}\n\nlgbm_model = lgb.train(\n    params, \n    train_data, \n    num_boost_round=1000,\n    valid_sets=[train_data, test_data]\n)\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:20:58.743157Z","iopub.execute_input":"2025-01-17T11:20:58.743603Z","iopub.status.idle":"2025-01-17T11:29:45.362498Z","shell.execute_reply.started":"2025-01-17T11:20:58.743570Z","shell.execute_reply":"2025-01-17T11:29:45.361416Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"Y_pred = lgbm_model.predict(X_test, num_iteration=lgbm_model.best_iteration)\nrmse = np.sqrt(mean_squared_error(Y_test,Y_pred))  \nprint(f\"RMSE: {rmse}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-01-17T11:31:12.926279Z","iopub.execute_input":"2025-01-17T11:31:12.926739Z","iopub.status.idle":"2025-01-17T11:32:33.218542Z","shell.execute_reply.started":"2025-01-17T11:31:12.926706Z","shell.execute_reply":"2025-01-17T11:32:33.217431Z"}},"outputs":[],"execution_count":null}]}