{"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"},{"sourceId":7168996,"sourceType":"datasetVersion","datasetId":4141778},{"sourceId":7194619,"sourceType":"datasetVersion","datasetId":4160714}],"dockerImageVersionId":30587,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# <center> Favorita dataset preparation","metadata":{}},{"cell_type":"code","source":"!pip install py7zr","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:29:23.459316Z","iopub.execute_input":"2024-04-21T18:29:23.459729Z","iopub.status.idle":"2024-04-21T18:29:38.526142Z","shell.execute_reply.started":"2024-04-21T18:29:23.459695Z","shell.execute_reply":"2024-04-21T18:29:38.525043Z"},"trusted":true},"execution_count":3,"outputs":[{"name":"stdout","text":"Collecting py7zr\n  Obtaining dependency information for py7zr from https://files.pythonhosted.org/packages/e7/38/783a9591807e1e5fd9cd92ce6e0fe49a4ba5a84fed0f552599881017faa6/py7zr-0.21.0-py3-none-any.whl.metadata\n  Downloading py7zr-0.21.0-py3-none-any.whl.metadata (17 kB)\nRequirement already satisfied: texttable in /opt/conda/lib/python3.10/site-packages (from py7zr) (1.7.0)\nCollecting pycryptodomex>=3.16.0 (from py7zr)\n  Obtaining dependency information for pycryptodomex>=3.16.0 from https://files.pythonhosted.org/packages/20/7a/3162173af8597f0399b45c6aaa4939ccae908476fdf1b3a3cc30631fc9fb/pycryptodomex-3.20.0-cp35-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata\n  Downloading pycryptodomex-3.20.0-cp35-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (3.4 kB)\nCollecting pyzstd>=0.15.9 (from py7zr)\n  Obtaining dependency information for pyzstd>=0.15.9 from https://files.pythonhosted.org/packages/17/d1/dd4b5af664edd46a671a5275edc92b218f1e0b121107575d9e822b984186/pyzstd-0.15.10-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata\n  Downloading pyzstd-0.15.10-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (7.9 kB)\nCollecting pyppmd<1.2.0,>=1.1.0 (from py7zr)\n  Obtaining dependency information for pyppmd<1.2.0,>=1.1.0 from https://files.pythonhosted.org/packages/09/76/61db4268a439cfba8736b14130d928d199633fab2360a2c5043332a427d2/pyppmd-1.1.0-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata\n  Downloading pyppmd-1.1.0-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (5.7 kB)\nCollecting pybcj<1.1.0,>=1.0.0 (from py7zr)\n  Obtaining dependency information for pybcj<1.1.0,>=1.0.0 from https://files.pythonhosted.org/packages/09/70/8b6a6cc2a5721f67f629bdc17875c0d603d57f360a19b099a7b4de19383d/pybcj-1.0.2-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata\n  Downloading pybcj-1.0.2-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (4.0 kB)\nCollecting multivolumefile>=0.2.3 (from py7zr)\n  Obtaining dependency information for multivolumefile>=0.2.3 from https://files.pythonhosted.org/packages/22/31/ec5f46fd4c83185b806aa9c736e228cb780f13990a9cf4da0beb70025fcc/multivolumefile-0.2.3-py3-none-any.whl.metadata\n  Downloading multivolumefile-0.2.3-py3-none-any.whl.metadata (6.3 kB)\nCollecting inflate64<1.1.0,>=1.0.0 (from py7zr)\n  Obtaining dependency information for inflate64<1.1.0,>=1.0.0 from https://files.pythonhosted.org/packages/b8/f4/e387a50f5027194eac4f9712d57b97e3e1a012402eaae98bcf1ebe8a97d1/inflate64-1.0.0-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata\n  Downloading inflate64-1.0.0-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (4.0 kB)\nCollecting brotli>=1.1.0 (from py7zr)\n  Obtaining dependency information for brotli>=1.1.0 from https://files.pythonhosted.org/packages/d5/00/40f760cc27007912b327fe15bf6bfd8eaecbe451687f72a8abc587d503b3/Brotli-1.1.0-cp310-cp310-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl.metadata\n  Downloading Brotli-1.1.0-cp310-cp310-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl.metadata (5.5 kB)\nRequirement already satisfied: psutil in /opt/conda/lib/python3.10/site-packages (from py7zr) (5.9.3)\nDownloading py7zr-0.21.0-py3-none-any.whl (67 kB)\n\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m67.6/67.6 kB\u001b[0m \u001b[31m3.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n\u001b[?25hDownloading Brotli-1.1.0-cp310-cp310-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (3.0 MB)\n\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m3.0/3.0 MB\u001b[0m \u001b[31m35.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m00:01\u001b[0m00:01\u001b[0m\n\u001b[?25hDownloading inflate64-1.0.0-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (93 kB)\n\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m93.1/93.1 kB\u001b[0m \u001b[31m10.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n\u001b[?25hDownloading multivolumefile-0.2.3-py3-none-any.whl (17 kB)\nDownloading pybcj-1.0.2-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (49 kB)\n\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m49.7/49.7 kB\u001b[0m \u001b[31m6.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n\u001b[?25hDownloading pycryptodomex-3.20.0-cp35-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (2.1 MB)\n\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.1/2.1 MB\u001b[0m \u001b[31m58.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m:00:01\u001b[0m\n\u001b[?25hDownloading pyppmd-1.1.0-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (138 kB)\n\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m138.9/138.9 kB\u001b[0m \u001b[31m16.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n\u001b[?25hDownloading pyzstd-0.15.10-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (411 kB)\n\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m411.2/411.2 kB\u001b[0m \u001b[31m40.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n\u001b[?25hInstalling collected packages: brotli, pyzstd, pyppmd, pycryptodomex, pybcj, multivolumefile, inflate64, py7zr\nSuccessfully installed brotli-1.1.0 inflate64-1.0.0 multivolumefile-0.2.3 py7zr-0.21.0 pybcj-1.0.2 pycryptodomex-3.20.0 pyppmd-1.1.0 pyzstd-0.15.10\n","output_type":"stream"}]},{"cell_type":"code","source":"!pip install pyspark","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:29:38.528268Z","iopub.execute_input":"2024-04-21T18:29:38.528643Z","iopub.status.idle":"2024-04-21T18:30:25.521367Z","shell.execute_reply.started":"2024-04-21T18:29:38.528609Z","shell.execute_reply":"2024-04-21T18:30:25.519932Z"},"trusted":true},"execution_count":4,"outputs":[{"name":"stdout","text":"Collecting pyspark\n  Downloading pyspark-3.5.1.tar.gz (317.0 MB)\n\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m317.0/317.0 MB\u001b[0m \u001b[31m3.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m00:01\u001b[0m00:01\u001b[0m\n\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25ldone\n\u001b[?25hRequirement already satisfied: py4j==0.10.9.7 in /opt/conda/lib/python3.10/site-packages (from pyspark) (0.10.9.7)\nBuilding wheels for collected packages: pyspark\n  Building wheel for pyspark (setup.py) ... \u001b[?25ldone\n\u001b[?25h  Created wheel for pyspark: filename=pyspark-3.5.1-py2.py3-none-any.whl size=317488491 sha256=ab4f34cedfe9e110b3efa4417e8da0cd3d9fc2e405ddddd9a117bfd740bce970\n  Stored in directory: /root/.cache/pip/wheels/80/1d/60/2c256ed38dddce2fdd93be545214a63e02fbd8d74fb0b7f3a6\nSuccessfully built pyspark\nInstalling collected packages: pyspark\nSuccessfully installed pyspark-3.5.1\n","output_type":"stream"}]},{"cell_type":"code","source":"import sys\nimport os\nimport gc","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:30:25.523283Z","iopub.execute_input":"2024-04-21T18:30:25.523715Z","iopub.status.idle":"2024-04-21T18:30:25.531091Z","shell.execute_reply.started":"2024-04-21T18:30:25.523676Z","shell.execute_reply":"2024-04-21T18:30:25.530151Z"},"trusted":true},"execution_count":5,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:30:25.533804Z","iopub.execute_input":"2024-04-21T18:30:25.53433Z","iopub.status.idle":"2024-04-21T18:30:25.911974Z","shell.execute_reply.started":"2024-04-21T18:30:25.534299Z","shell.execute_reply":"2024-04-21T18:30:25.910969Z"},"trusted":true},"execution_count":6,"outputs":[]},{"cell_type":"code","source":"import pyspark\nimport pyspark.sql.functions as F\nimport pyspark.sql.types as T\nfrom pyspark.sql.window import Window as W","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:37:33.908514Z","iopub.execute_input":"2024-04-21T18:37:33.908956Z","iopub.status.idle":"2024-04-21T18:37:33.914318Z","shell.execute_reply.started":"2024-04-21T18:37:33.908921Z","shell.execute_reply":"2024-04-21T18:37:33.913204Z"},"trusted":true},"execution_count":19,"outputs":[]},{"cell_type":"markdown","source":"starting spark session","metadata":{}},{"cell_type":"code","source":"os.environ['PYSPARK_PYTHON'] = sys.executable\nos.environ['PYSPARK_DRIVER_PYTHON'] = sys.executable\n\nnumber_cores = 8\nmemory_gb = 24\nconf = (\n    pyspark\n    .SparkConf()\n    .setMaster(f'local[{number_cores}]')\n    .set('spark.driver.memory', f'{memory_gb}g')\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:30:25.984111Z","iopub.execute_input":"2024-04-21T18:30:25.984407Z","iopub.status.idle":"2024-04-21T18:30:25.990831Z","shell.execute_reply.started":"2024-04-21T18:30:25.98438Z","shell.execute_reply":"2024-04-21T18:30:25.989631Z"},"trusted":true},"execution_count":8,"outputs":[]},{"cell_type":"code","source":"spark = pyspark.sql.SparkSession.builder.appName('favorita_dataset').getOrCreate()\nspark","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:30:25.992179Z","iopub.execute_input":"2024-04-21T18:30:25.992597Z","iopub.status.idle":"2024-04-21T18:30:32.293992Z","shell.execute_reply.started":"2024-04-21T18:30:25.992535Z","shell.execute_reply":"2024-04-21T18:30:32.292546Z"},"trusted":true},"execution_count":9,"outputs":[{"name":"stderr","text":"Setting default log level to \"WARN\".\nTo adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n24/04/21 18:30:29 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n","output_type":"stream"},{"execution_count":9,"output_type":"execute_result","data":{"text/plain":"<pyspark.sql.session.SparkSession at 0x7ae948e43640>","text/html":"\n            <div>\n                <p><b>SparkSession - in-memory</b></p>\n                \n        <div>\n            <p><b>SparkContext</b></p>\n\n            <p><a href=\"http://3b496dcc6d44:4040\">Spark UI</a></p>\n\n            <dl>\n              <dt>Version</dt>\n                <dd><code>v3.5.1</code></dd>\n              <dt>Master</dt>\n                <dd><code>local[*]</code></dd>\n              <dt>AppName</dt>\n                <dd><code>favorita_dataset</code></dd>\n            </dl>\n        </div>\n        \n            </div>\n        "},"metadata":{}}]},{"cell_type":"code","source":"import py7zr\nfrom subprocess import check_output\n\nbase_dir = '/kaggle/input/favorita-grocery-sales-forecasting'\n\nfor dirname, _, filenames in os.walk(base_dir):\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\"))","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:30:32.2958Z","iopub.execute_input":"2024-04-21T18:30:32.300139Z","iopub.status.idle":"2024-04-21T18:31:58.320622Z","shell.execute_reply.started":"2024-04-21T18:30:32.300098Z","shell.execute_reply":"2024-04-21T18:31:58.319475Z"},"trusted":true},"execution_count":10,"outputs":[{"name":"stdout","text":"holidays_events.csv\nitems.csv\noil.csv\nsample_submission.csv\nstores.csv\ntest.csv\ntrain.csv\ntransactions.csv\n\n","output_type":"stream"}]},{"cell_type":"code","source":"data = spark.read.csv('/kaggle/working/train.csv', header=True)\ndata = data.withColumn('unique_id', F.concat(F.col('store_nbr'), F.lit('_'), F.col('item_nbr')))\ndata.show()","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:31:58.322123Z","iopub.execute_input":"2024-04-21T18:31:58.322552Z","iopub.status.idle":"2024-04-21T18:32:03.594444Z","shell.execute_reply.started":"2024-04-21T18:31:58.322515Z","shell.execute_reply":"2024-04-21T18:32:03.593265Z"},"trusted":true},"execution_count":11,"outputs":[{"name":"stdout","text":"+---+----------+---------+--------+----------+-----------+---------+\n| id|      date|store_nbr|item_nbr|unit_sales|onpromotion|unique_id|\n+---+----------+---------+--------+----------+-----------+---------+\n|  0|2013-01-01|       25|  103665|       7.0|       NULL|25_103665|\n|  1|2013-01-01|       25|  105574|       1.0|       NULL|25_105574|\n|  2|2013-01-01|       25|  105575|       2.0|       NULL|25_105575|\n|  3|2013-01-01|       25|  108079|       1.0|       NULL|25_108079|\n|  4|2013-01-01|       25|  108701|       1.0|       NULL|25_108701|\n|  5|2013-01-01|       25|  108786|       3.0|       NULL|25_108786|\n|  6|2013-01-01|       25|  108797|       1.0|       NULL|25_108797|\n|  7|2013-01-01|       25|  108952|       1.0|       NULL|25_108952|\n|  8|2013-01-01|       25|  111397|      13.0|       NULL|25_111397|\n|  9|2013-01-01|       25|  114790|       3.0|       NULL|25_114790|\n| 10|2013-01-01|       25|  114800|       1.0|       NULL|25_114800|\n| 11|2013-01-01|       25|  115267|       1.0|       NULL|25_115267|\n| 12|2013-01-01|       25|  115611|       1.0|       NULL|25_115611|\n| 13|2013-01-01|       25|  115693|       1.0|       NULL|25_115693|\n| 14|2013-01-01|       25|  115720|       5.0|       NULL|25_115720|\n| 15|2013-01-01|       25|  115850|       1.0|       NULL|25_115850|\n| 16|2013-01-01|       25|  115891|       6.0|       NULL|25_115891|\n| 17|2013-01-01|       25|  115892|      10.0|       NULL|25_115892|\n| 18|2013-01-01|       25|  115894|       5.0|       NULL|25_115894|\n| 19|2013-01-01|       25|  119024|       1.0|       NULL|25_119024|\n+---+----------+---------+--------+----------+-----------+---------+\nonly showing top 20 rows\n\n","output_type":"stream"}]},{"cell_type":"code","source":"data.agg(F.min('date'), F.max('date')).show()","metadata":{"execution":{"iopub.status.busy":"2024-04-20T20:11:20.648417Z","iopub.execute_input":"2024-04-20T20:11:20.648902Z","iopub.status.idle":"2024-04-20T20:12:55.377823Z","shell.execute_reply.started":"2024-04-20T20:11:20.648855Z","shell.execute_reply":"2024-04-20T20:12:55.376657Z"},"trusted":true},"execution_count":14,"outputs":[{"name":"stderr","text":"[Stage 2:=======================================================> (37 + 1) / 38]\r","output_type":"stream"},{"name":"stdout","text":"+----------+----------+\n| min(date)| max(date)|\n+----------+----------+\n|2013-01-01|2017-08-15|\n+----------+----------+\n\n","output_type":"stream"},{"name":"stderr","text":"                                                                                \r","output_type":"stream"}]},{"cell_type":"code","source":"mon_data = (\n    data\n    .withColumn(\n        'mon',\n        F.last_day('date')\n    )\n    .filter(\n        F.col('mon') < '2017-01-01'\n    )\n    .groupBy('unique_id', 'mon')\n    .agg(\n        F.sum('unit_sales').alias('unit_sales')\n    )\n)\n\nmon_data","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:32:03.600009Z","iopub.execute_input":"2024-04-21T18:32:03.600419Z","iopub.status.idle":"2024-04-21T18:32:03.731724Z","shell.execute_reply.started":"2024-04-21T18:32:03.600382Z","shell.execute_reply":"2024-04-21T18:32:03.730175Z"},"trusted":true},"execution_count":12,"outputs":[{"execution_count":12,"output_type":"execute_result","data":{"text/plain":"DataFrame[unique_id: string, mon: date, unit_sales: double]"},"metadata":{}}]},{"cell_type":"code","source":"ids = (\n    data.select('store_nbr').distinct()\n    .crossJoin(\n        data.select('item_nbr').distinct().sample(0.01, seed=21)\n    )\n    .withColumn(\n        'unique_id',\n        F.concat(F.col('store_nbr'), F.lit('_'), F.col('item_nbr'))\n    )\n)\n\nids","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:32:03.735347Z","iopub.execute_input":"2024-04-21T18:32:03.735673Z","iopub.status.idle":"2024-04-21T18:32:03.82584Z","shell.execute_reply.started":"2024-04-21T18:32:03.735646Z","shell.execute_reply":"2024-04-21T18:32:03.824952Z"},"trusted":true},"execution_count":13,"outputs":[{"execution_count":13,"output_type":"execute_result","data":{"text/plain":"DataFrame[store_nbr: string, item_nbr: string, unique_id: string]"},"metadata":{}}]},{"cell_type":"code","source":"dates = spark.createDataFrame(\n    pd.DataFrame({'date': pd.date_range('2013-01-01', '2017-01-01', freq='M')}, dtype='str')\n)\ndates = dates.withColumn('mon', F.last_day('date'))\ndates.show(20)","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:32:03.829194Z","iopub.execute_input":"2024-04-21T18:32:03.829512Z","iopub.status.idle":"2024-04-21T18:32:05.776341Z","shell.execute_reply.started":"2024-04-21T18:32:03.829464Z","shell.execute_reply":"2024-04-21T18:32:05.775307Z"},"trusted":true},"execution_count":14,"outputs":[{"name":"stderr","text":"                                                                                \r","output_type":"stream"},{"name":"stdout","text":"+----------+----------+\n|      date|       mon|\n+----------+----------+\n|2013-01-31|2013-01-31|\n|2013-02-28|2013-02-28|\n|2013-03-31|2013-03-31|\n|2013-04-30|2013-04-30|\n|2013-05-31|2013-05-31|\n|2013-06-30|2013-06-30|\n|2013-07-31|2013-07-31|\n|2013-08-31|2013-08-31|\n|2013-09-30|2013-09-30|\n|2013-10-31|2013-10-31|\n|2013-11-30|2013-11-30|\n|2013-12-31|2013-12-31|\n|2014-01-31|2014-01-31|\n|2014-02-28|2014-02-28|\n|2014-03-31|2014-03-31|\n|2014-04-30|2014-04-30|\n|2014-05-31|2014-05-31|\n|2014-06-30|2014-06-30|\n|2014-07-31|2014-07-31|\n|2014-08-31|2014-08-31|\n+----------+----------+\nonly showing top 20 rows\n\n","output_type":"stream"}]},{"cell_type":"code","source":"data = data.drop('id', 'store_nbr', 'item_nbr')","metadata":{"execution":{"iopub.status.busy":"2024-04-20T20:18:49.389765Z","iopub.execute_input":"2024-04-20T20:18:49.390175Z","iopub.status.idle":"2024-04-20T20:18:49.406615Z","shell.execute_reply.started":"2024-04-20T20:18:49.390143Z","shell.execute_reply":"2024-04-20T20:18:49.4054Z"},"trusted":true},"execution_count":18,"outputs":[]},{"cell_type":"code","source":"dataset = ids.crossJoin(dates.select('mon'))\ndataset","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:32:05.777501Z","iopub.execute_input":"2024-04-21T18:32:05.778544Z","iopub.status.idle":"2024-04-21T18:32:05.827524Z","shell.execute_reply.started":"2024-04-21T18:32:05.778506Z","shell.execute_reply":"2024-04-21T18:32:05.826557Z"},"trusted":true},"execution_count":15,"outputs":[{"execution_count":15,"output_type":"execute_result","data":{"text/plain":"DataFrame[store_nbr: string, item_nbr: string, unique_id: string, mon: date]"},"metadata":{}}]},{"cell_type":"code","source":"dataset = dataset.join(mon_data, ['unique_id', 'mon'], 'left')\ndataset","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:32:05.829342Z","iopub.execute_input":"2024-04-21T18:32:05.829757Z","iopub.status.idle":"2024-04-21T18:32:05.88994Z","shell.execute_reply.started":"2024-04-21T18:32:05.829719Z","shell.execute_reply":"2024-04-21T18:32:05.888906Z"},"trusted":true},"execution_count":16,"outputs":[{"execution_count":16,"output_type":"execute_result","data":{"text/plain":"DataFrame[unique_id: string, mon: date, store_nbr: string, item_nbr: string, unit_sales: double]"},"metadata":{}}]},{"cell_type":"code","source":"active_ids = (\n    dataset\n    .groupBy('unique_id')\n    .agg(\n        F.sum('unit_sales').alias('unit_sales')\n    )\n    .where(\n        F.col('unit_sales') > 0\n    )\n    .select('unique_id')\n)\n\ndataset = (\n    active_ids\n    .join(\n        dataset, \n        on=['unique_id'], \n        how='left'\n    )\n    .cache()\n)\ndataset.count()","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:32:05.891824Z","iopub.execute_input":"2024-04-21T18:32:05.892239Z","iopub.status.idle":"2024-04-21T18:37:17.31362Z","shell.execute_reply.started":"2024-04-21T18:32:05.892198Z","shell.execute_reply":"2024-04-21T18:37:17.311695Z"},"trusted":true},"execution_count":17,"outputs":[{"name":"stderr","text":"24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n24/04/21 18:36:59 WARN RowBasedKeyValueBatch: Calling spill() on RowBasedKeyValueBatch. Will not spill but return 0.\n                                                                                \r","output_type":"stream"},{"execution_count":17,"output_type":"execute_result","data":{"text/plain":"75552"},"metadata":{}}]},{"cell_type":"code","source":"48*1574","metadata":{"execution":{"iopub.status.busy":"2024-04-21T17:40:24.212469Z","iopub.execute_input":"2024-04-21T17:40:24.213577Z","iopub.status.idle":"2024-04-21T17:40:24.220153Z","shell.execute_reply.started":"2024-04-21T17:40:24.213537Z","shell.execute_reply":"2024-04-21T17:40:24.219082Z"},"trusted":true},"execution_count":22,"outputs":[{"execution_count":22,"output_type":"execute_result","data":{"text/plain":"75552"},"metadata":{}}]},{"cell_type":"code","source":"dataset.select('mon').distinct().count()","metadata":{"execution":{"iopub.status.busy":"2024-04-21T17:38:11.752303Z","iopub.execute_input":"2024-04-21T17:38:11.754077Z","iopub.status.idle":"2024-04-21T17:38:15.851749Z","shell.execute_reply.started":"2024-04-21T17:38:11.754031Z","shell.execute_reply":"2024-04-21T17:38:15.850717Z"},"trusted":true},"execution_count":16,"outputs":[{"name":"stderr","text":"                                                                                \r","output_type":"stream"},{"execution_count":16,"output_type":"execute_result","data":{"text/plain":"48"},"metadata":{}}]},{"cell_type":"code","source":"dataset.select('unique_id').distinct().count()","metadata":{"execution":{"iopub.status.busy":"2024-04-21T17:38:15.853819Z","iopub.execute_input":"2024-04-21T17:38:15.854236Z","iopub.status.idle":"2024-04-21T17:38:18.485898Z","shell.execute_reply.started":"2024-04-21T17:38:15.854196Z","shell.execute_reply":"2024-04-21T17:38:18.482588Z"},"trusted":true},"execution_count":17,"outputs":[{"name":"stderr","text":"                                                                                \r","output_type":"stream"},{"execution_count":17,"output_type":"execute_result","data":{"text/plain":"1574"},"metadata":{}}]},{"cell_type":"code","source":"(\n    dataset\n    .select(\n        'unique_id',\n        'mon',\n        'unit_sales',\n        F.month('mon').alias('month')\n    )\n    .withColumn(\n        'activity',\n        F.sum('unit_sales').over(\n            W.partitionBy('unique_id')\n            .orderBy('mon')\n            .rowsBetween(-12, W.currentRow)\n        )\n    )\n    .withColumn(\n        'activity_mask',\n        F.when(\n            F.col('activity') > 0, \n            True\n        )\n        .otherwise(False)\n    )\n    .orderBy('unique_id', 'mon')\n    .write\n    .mode('overwrite')\n    .parquet('favorita_mon_test_sample')\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:37:52.57517Z","iopub.execute_input":"2024-04-21T18:37:52.575622Z","iopub.status.idle":"2024-04-21T18:38:00.262297Z","shell.execute_reply.started":"2024-04-21T18:37:52.575588Z","shell.execute_reply":"2024-04-21T18:38:00.26117Z"},"trusted":true},"execution_count":21,"outputs":[{"name":"stderr","text":"                                                                                \r","output_type":"stream"}]},{"cell_type":"code","source":"48*1574csv","metadata":{"execution":{"iopub.status.busy":"2024-04-20T21:10:45.601557Z","iopub.execute_input":"2024-04-20T21:10:45.60202Z","iopub.status.idle":"2024-04-20T21:10:45.610178Z","shell.execute_reply.started":"2024-04-20T21:10:45.601981Z","shell.execute_reply":"2024-04-20T21:10:45.608894Z"},"trusted":true},"execution_count":27,"outputs":[{"execution_count":27,"output_type":"execute_result","data":{"text/plain":"75552"},"metadata":{}}]},{"cell_type":"code","source":"8833*366","metadata":{"execution":{"iopub.status.busy":"2023-12-15T22:03:52.997855Z","iopub.execute_input":"2023-12-15T22:03:52.998327Z","iopub.status.idle":"2023-12-15T22:03:53.014219Z","shell.execute_reply.started":"2023-12-15T22:03:52.998283Z","shell.execute_reply":"2023-12-15T22:03:53.012514Z"},"trusted":true},"execution_count":33,"outputs":[{"execution_count":33,"output_type":"execute_result","data":{"text/plain":"3232878"},"metadata":{}}]},{"cell_type":"code","source":"dataset = dataset.fillna(False, subset=['onpromotion'])\ndataset","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"transactions = spark.read.csv('/kaggle/working/transactions.csv', header=True)\ntransactions = (\n    transactions\n    .select(\n        'date', \n        'store_nbr', \n        F.col('transactions').cast(T.LongType())\n    )\n    .withColumn(\n        'mon',\n        F.last_day('date')\n    )\n    .groupBy(\n        'mon',\n        'store_nbr'\n    )\n    .agg(\n        F.sum('transactions').alias('transactions')\n    )\n)\ntransactions","metadata":{"execution":{"iopub.status.busy":"2024-04-20T21:44:36.98592Z","iopub.status.idle":"2024-04-20T21:44:36.99017Z","shell.execute_reply.started":"2024-04-20T21:44:36.989773Z","shell.execute_reply":"2024-04-20T21:44:36.989812Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset = dataset.join(transactions, on=['mon', 'store_nbr'], how='left')","metadata":{"execution":{"iopub.status.busy":"2024-04-20T21:44:36.996593Z","iopub.status.idle":"2024-04-20T21:44:37.001173Z","shell.execute_reply.started":"2024-04-20T21:44:37.000689Z","shell.execute_reply":"2024-04-20T21:44:37.000735Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset = dataset.fillna(0, subset=['transactions'])","metadata":{"execution":{"iopub.status.busy":"2024-04-20T21:44:37.005961Z","iopub.status.idle":"2024-04-20T21:44:37.007337Z","shell.execute_reply.started":"2024-04-20T21:44:37.006958Z","shell.execute_reply":"2024-04-20T21:44:37.006994Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset = dataset.fillna(0, subset=[col for col, dtype in dataset.dtypes if dtype != 'string'])","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"items = spark.read.csv('/kaggle/working/items.csv', header=True)\nitems ","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset = dataset.join(items, on='item_nbr', how='left')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stores = spark.read.csv('/kaggle/working/stores.csv', header=True)\nstores ","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset = dataset.join(stores, on='store_nbr', how='left')\ndataset","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"oil = spark.read.csv('/kaggle/working/oil.csv', header=True)\noil.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"window_spec = Window.orderBy('date')\noil = oil.withColumn('oil_price', F.when(F.col('dcoilwtico').isNull(), F.lag('dcoilwtico', 1).over(window_spec)).otherwise(F.col('dcoilwtico')))\noil = oil.withColumn('oil_price', F.when(F.col('dcoilwtico').isNull(), F.lag('dcoilwtico', 2).over(window_spec)).otherwise(F.col('dcoilwtico')))\noil = oil.withColumn('oil_price', F.when(F.col('dcoilwtico').isNull(), F.lag('dcoilwtico', 3).over(window_spec)).otherwise(F.col('dcoilwtico')))\noil = oil.withColumn('oil_price', F.when(F.col('dcoilwtico').isNull(), F.lag('dcoilwtico', 4).over(window_spec)).otherwise(F.col('dcoilwtico')))\noil = oil.withColumn('oil_price', F.when(F.col('dcoilwtico').isNull(), F.lag('dcoilwtico', 5).over(window_spec)).otherwise(F.col('dcoilwtico')))\noil = oil.withColumn('oil_price', F.when(F.col('dcoilwtico').isNull(), F.lag('dcoilwtico', 6).over(window_spec)).otherwise(F.col('dcoilwtico')))\noil = oil.withColumn('oil_price', F.when(F.col('dcoilwtico').isNull(), F.lag('dcoilwtico', 7).over(window_spec)).otherwise(F.col('dcoilwtico')))\noil = oil.withColumn('oil_price', F.when(F.col('dcoilwtico').isNull(), F.lead('dcoilwtico', 1).over(window_spec)).otherwise(F.col('dcoilwtico')))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"oil.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset = dataset.join(oil.select('date', 'oil_price'), on='date', how='left')\ndataset","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset = dataset.select(\n    F.col('date'),\n    F.col('store_nbr'),\n    F.col('item_nbr'),\n    F.col('unique_id'),\n    F.col('unit_sales').cast(T.FloatType()),\n    F.col('onpromotion').cast(T.BooleanType()),\n    F.col('transactions'),\n    F.col('family'),\n    F.col('class'),\n    F.col('perishable').cast(T.BooleanType()),\n    F.col('city'),\n    F.col('state'),\n    F.col('type'),\n    F.col('cluster'),\n    F.col('oil_price').cast(T.FloatType())\n)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset = (\n    dataset\n    .withColumn('day', F.day('date'))\n    .withColumn('month', F.month('date'))\n    .withColumn('year', F.year('date'))\n    .withColumn('day_of_year', F.dayofyear('date'))\n    .withColumn('day_of_weel', F.dayofweek('date'))\n)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"holidays = spark.read.csv('/kaggle/working/holidays_events.csv', header=True)\nholidays ","metadata":{"execution":{"iopub.status.busy":"2023-12-15T22:07:12.241873Z","iopub.execute_input":"2023-12-15T22:07:12.242341Z","iopub.status.idle":"2023-12-15T22:07:12.413694Z","shell.execute_reply.started":"2023-12-15T22:07:12.242304Z","shell.execute_reply":"2023-12-15T22:07:12.412807Z"},"trusted":true},"execution_count":51,"outputs":[{"execution_count":51,"output_type":"execute_result","data":{"text/plain":"DataFrame[date: string, type: string, locale: string, locale_name: string, description: string, transferred: string]"},"metadata":{}}]},{"cell_type":"code","source":"holidays.select('type').distinct().show()","metadata":{"execution":{"iopub.status.busy":"2023-12-15T22:08:47.943479Z","iopub.execute_input":"2023-12-15T22:08:47.94436Z","iopub.status.idle":"2023-12-15T22:08:48.134743Z","shell.execute_reply.started":"2023-12-15T22:08:47.944317Z","shell.execute_reply":"2023-12-15T22:08:48.133516Z"},"trusted":true},"execution_count":53,"outputs":[{"name":"stdout","text":"+----------+\n|      type|\n+----------+\n|     Event|\n|   Holiday|\n|  Transfer|\n|    Bridge|\n|Additional|\n|  Work Day|\n+----------+\n\n","output_type":"stream"}]},{"cell_type":"code","source":"dataset","metadata":{"execution":{"iopub.status.busy":"2024-04-20T21:11:18.446031Z","iopub.execute_input":"2024-04-20T21:11:18.446483Z","iopub.status.idle":"2024-04-20T21:11:18.459652Z","shell.execute_reply.started":"2024-04-20T21:11:18.446447Z","shell.execute_reply":"2024-04-20T21:11:18.458625Z"},"trusted":true},"execution_count":32,"outputs":[{"execution_count":32,"output_type":"execute_result","data":{"text/plain":"DataFrame[mon: date, store_nbr: string, unique_id: string, item_nbr: string, unit_sales: double, date: string, transactions: bigint]"},"metadata":{}}]},{"cell_type":"code","source":"(\n    dataset\n    .select(\n        'unique_id',\n        'mon',\n        'unit_sales',\n        # 'transactions',\n        F.month('mon').alias('month')\n    )\n    # .fillna(0, subset=['transactions', 'unit_sales'])\n    .orderBy('unique_id', 'mon')\n    .write\n    .parquet('sample')\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-20T21:53:15.637655Z","iopub.execute_input":"2024-04-20T21:53:15.642135Z","iopub.status.idle":"2024-04-20T21:53:21.161924Z","shell.execute_reply.started":"2024-04-20T21:53:15.642086Z","shell.execute_reply":"2024-04-20T21:53:21.160782Z"},"trusted":true},"execution_count":16,"outputs":[{"name":"stderr","text":"                                                                                \r","output_type":"stream"}]},{"cell_type":"code","source":"dataset.groupBy('unique_id', 'mon').agg(F.sum('unit_sales')).count()","metadata":{"execution":{"iopub.status.busy":"2024-04-20T21:41:08.029999Z","iopub.execute_input":"2024-04-20T21:41:08.030775Z","iopub.status.idle":"2024-04-20T21:41:10.87019Z","shell.execute_reply.started":"2024-04-20T21:41:08.030734Z","shell.execute_reply":"2024-04-20T21:41:10.868871Z"},"trusted":true},"execution_count":40,"outputs":[{"name":"stderr","text":"                                                                                \r","output_type":"stream"},{"execution_count":40,"output_type":"execute_result","data":{"text/plain":"75552"},"metadata":{}}]},{"cell_type":"code","source":"1574*48","metadata":{"execution":{"iopub.status.busy":"2024-04-20T21:40:11.944205Z","iopub.execute_input":"2024-04-20T21:40:11.944634Z","iopub.status.idle":"2024-04-20T21:40:11.953425Z","shell.execute_reply.started":"2024-04-20T21:40:11.944601Z","shell.execute_reply":"2024-04-20T21:40:11.951725Z"},"trusted":true},"execution_count":37,"outputs":[{"execution_count":37,"output_type":"execute_result","data":{"text/plain":"75552"},"metadata":{}}]},{"cell_type":"code","source":"!zip -r sample.zip /kaggle/working/favorita_mon_test_sample/","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:49:43.510005Z","iopub.execute_input":"2024-04-21T18:49:43.511173Z","iopub.status.idle":"2024-04-21T18:49:44.539773Z","shell.execute_reply.started":"2024-04-21T18:49:43.511129Z","shell.execute_reply":"2024-04-21T18:49:44.538554Z"},"trusted":true},"execution_count":22,"outputs":[{"name":"stdout","text":"  adding: kaggle/working/favorita_mon_test_sample/ (stored 0%)\n  adding: kaggle/working/favorita_mon_test_sample/_SUCCESS (stored 0%)\n  adding: kaggle/working/favorita_mon_test_sample/._SUCCESS.crc (stored 0%)\n  adding: kaggle/working/favorita_mon_test_sample/.part-00000-71fa9069-ac0e-4cb7-b46a-6c1759362c49-c000.snappy.parquet.crc (stored 0%)\n  adding: kaggle/working/favorita_mon_test_sample/part-00000-71fa9069-ac0e-4cb7-b46a-6c1759362c49-c000.snappy.parquet (deflated 13%)\n","output_type":"stream"}]},{"cell_type":"code","source":"from IPython.display import FileLink\nFileLink(r'sample.zip')","metadata":{"execution":{"iopub.status.busy":"2024-04-21T18:49:45.764397Z","iopub.execute_input":"2024-04-21T18:49:45.765564Z","iopub.status.idle":"2024-04-21T18:49:45.772991Z","shell.execute_reply.started":"2024-04-21T18:49:45.765512Z","shell.execute_reply":"2024-04-21T18:49:45.771928Z"},"trusted":true},"execution_count":23,"outputs":[{"execution_count":23,"output_type":"execute_result","data":{"text/plain":"/kaggle/working/sample.zip","text/html":"<a href='sample.zip' target='_blank'>sample.zip</a><br>"},"metadata":{}}]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}