/!\\ Be aware that this is not the most efficient way to join the columns as it will consume a lot of memory and the resulting csv will be very large (more on this below). You may want to take that code and adapt it so that `train.csv` get read by chunks
"], "cell_type": "markdown", "metadata": {"collapsed": true, "_cell_guid": "431ff1ca-2acc-46b8-8a35-b0040fc8ad47", "_uuid": "2ec23a2f454e13c0030264f06785b7af26ad60ea"}}, {"source": ["## 1. Open & view the data"], "cell_type": "markdown", "metadata": {"_cell_guid": "da927899-5ada-4f4a-9be2-5b07791e9b0e", "_uuid": "598f69bee72ea4305f5a645ec11dd41978f56e1f"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "5011933a-5ca0-417f-a3ec-55eba11a765d", "_uuid": "0b48f7e3f486ef5ef902227afc875fa6382e46a3"}, "execution_count": null, "source": ["import sys\n", "import os\n", "import gc\n", "import numpy as np\n", "import pandas as pd\n", "from tqdm import tqdm\n", "from IPython.display import display\n", "import datetime\n", "\n", "%matplotlib inline\n", "%config InlineBackend.figure_format = 'retina'"]}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "00b058f5-46e4-45c9-aaca-26c3b32267b2", "_uuid": "bf48346ebe463a2ef2d7627f3ebe65a5179298c0"}, "execution_count": null, "source": ["path = \"../input/\"\n", "files = [\"train.csv\", \"test.csv\", \"transactions.csv\" , \"stores.csv\", \"oil.csv\", \"items.csv\", \"holidays_events.csv\"]\n", "datasets_path = [path + f for f in files]\n", "print('# File sizes')\n", "for f in datasets_path:\n", " print(f.ljust(30) + str(round(os.path.getsize(f) / 1000000, 2)) + 'MB')"]}, {"source": ["Notice we use a `chunksize` to not load all the dataset on Kaggle virtual machine. You may want to set it to `None` on your machine.\n", "\n", "We define the type for each tables because by default pandas loads the csv with float64/int64 columns which will doubles the size of our resulting csv.\n", "\n", "This notebook assumes you have enough memory to handle all the operations/creation of the different datasets we will have. A better alternative could be to load a chunk of the csv file then iteratively store the chunk in the resulting csv. It's a hard thing to do in a notebook but you can take most of the code here to achieve this goal.\n", "\n", "**The training set has 125 497 040 entries and the the testing set has 3 370 464 entries in total.**\n", "\n", "To run this notebook on all the data you will need to have at least 7gb of free RAM as well as 1.2gb of free disk space
"], "cell_type": "markdown", "metadata": {"_cell_guid": "7915f179-2493-4185-aa5a-f65de0906b25", "_uuid": "5778e186f8ba6b6061df204f27af39479abf2fae"}}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "8816604b-c6f1-4f75-b30f-c551edb37525", "_uuid": "7d65ee61b0188d689bbaf798534f428fa2ea8cbf"}, "execution_count": null, "source": ["%%time\n", "\n", "# This will take all the data from the test set but not the train set\n", "# It's ~15x less data from the train set \n", "chunksize = 8_366_470\n", "\n", "data_df = {}\n", "for file, path in tqdm(zip(files, datasets_path), total=len(files)):\n", " name = file.split(\".\")[0]\n", " if name == 'train' or name == 'test':\n", " data_df[name] = pd.read_csv(path, dtype={\"date\": np.str,\n", " \"id\": np.int32,\n", " \"item_nbr\": np.int32,\n", " \"onpromotion\": np.object,\n", " \"store_nbr\": np.int8,\n", " \"unit_sales\": np.float32},\n", " parse_dates=[\"date\"], chunksize=chunksize, \n", " low_memory=False)\n", " if chunksize:\n", " data_df[name] = data_df[name].get_chunk()\n", " data_df[name][\"onpromotion\"].fillna(False, inplace=True)\n", " data_df[name][\"onpromotion\"].map({\"True\": True, \"False\": False})\n", " data_df[name][\"onpromotion\"].astype(bool)\n", " elif name == 'transactions':\n", " data_df[name] = pd.read_csv(path, dtype={\"date\": np.str,\n", " \"store_nbr\": np.int32,\n", " \"transactions\": np.int32},\n", " parse_dates=[\"date\"])\n", " elif name == 'stores':\n", " data_df[name] = pd.read_csv(path, dtype={\"store_nbr\": np.int8,\n", " \"city\": np.str,\n", " \"state\": np.str,\n", " \"type\": np.str,\n", " \"cluster\": np.int32})\n", " elif name == 'oil':\n", " data_df[name] = pd.read_csv(path, dtype={\"date\": np.str,\n", " \"dcoilwtico\": np.float32},\n", " parse_dates=[\"date\"])\n", " elif name == 'oil':\n", " data_df[name] = pd.read_csv(path, dtype={\"date\": np.str,\n", " \"dcoilwtico\": np.float32},\n", " parse_dates=[\"date\"])\n", " elif name == 'items':\n", " data_df[name] = pd.read_csv(path, dtype={\"item_nbr\": np.int32,\n", " \"family\": np.str,\n", " \"class\": np.str,\n", " \"perishable\": np.bool})\n", " elif name == 'holidays_events':\n", " data_df[name] = pd.read_csv(path, dtype={\"date\": np.str,\n", " \"type\": np.str,\n", " \"locale\": np.str,\n", " \"locale_name\": np.str,\n", " \"description\": np.str,\n", " \"transferred\": np.bool},\n", " parse_dates=[\"date\"])\n", " else:\n", " data_df[name] = pd.read_csv(path)"]}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "fe974cae-658d-4d0a-bda5-63cd76edf17a", "_uuid": "ca2e1cfbd4b11d782117dde825cc9a92bea6bea7"}, "execution_count": null, "source": ["for k, df in data_df.items():\n", " print(k)\n", " display(df.head())"]}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "9bf3151d-991d-441e-abe1-5232de645d2c", "_uuid": "32390a11a43b25f1e0d7b67b967b93dd78d27398"}, "execution_count": null, "source": ["train_df = data_df[\"train\"]\n", "test_df = data_df[\"test\"]\n", "oil_df = data_df[\"oil\"]\n", "oil_df[\"date\"] = pd.to_datetime(oil_df[\"date\"])\n", "items_df = data_df[\"items\"]\n", "stores_df = data_df[\"stores\"]\n", "transactions_df = data_df[\"transactions\"]\n", "transactions_df[\"date\"] = pd.to_datetime(transactions_df[\"date\"])\n", "holidays_df = data_df[\"holidays_events\"]\n", "holidays_df[\"date\"] = pd.to_datetime(holidays_df[\"date\"])\n", "\n", "print(\"Train set date range: {} to {}\".format(train_df[\"date\"].min(), train_df[\"date\"].max()))\n", "print(\"Test set date range: {} to {}\".format(test_df[\"date\"].min(), test_df[\"date\"].max()))"]}, {"source": ["## 2. Merge train/test"], "cell_type": "markdown", "metadata": {"_cell_guid": "c855020f-2cf7-4c02-8fef-237075316f56", "_uuid": "6b75b03c3c297c7aea3835f5135585b0bf8d3647"}}, {"source": ["Now lets merge train and test sets, so all operations can be operated onto 1 Dataframe, we can split them back later as we know the train set range from **2013-01-01 to 2017-08-15** and the test set from **2017-08-16 to 2017-08-31**."], "cell_type": "markdown", "metadata": {"_cell_guid": "22438efd-5acc-4cc2-b26b-6acb9295b504", "_uuid": "c0c2028770101bde9f337795df898792fe85f339"}}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "224eb22b-8d94-4ba5-ba54-7f49b425ae16", "_uuid": "934be8ae432532a94bb0487925571977e38de743"}, "execution_count": null, "source": ["train_df['onpromotion'].fillna(False, inplace=True)\n", "test_df['onpromotion'].fillna(False, inplace=True)\n", "\n", "train_date_range = [train_df['date'].min(), train_df['date'].max()]\n", "test_date_range = [test_df['date'].min(), test_df['date'].max()]\n", "\n", "merged_df = train_df.append(test_df)\n", "origin_merge_len = len(merged_df)\n", "origin_train_len = len(train_df)\n", "origin_test_len = len(test_df)\n", "\n", "assert len(merged_df) == len(train_df) + len(test_df)\n", "print(\"Merged df size: {}\".format(len(merged_df)))\n", "del train_df\n", "del test_df\n", "gc.collect()\n", "\n", "display(merged_df.tail())\n", "display(merged_df.dtypes)\n", "print(f\"Train set range:{train_date_range}\\nTest set range:{test_date_range}\")"]}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "3e01d55b-a611-47b5-b651-f204b8081d19", "_uuid": "aba7810e02110fad2ee1d16ff9839033dd42af52"}, "execution_count": null, "source": ["display(merged_df.isnull().sum().sort_index() / len(merged_df))"]}, {"source": ["We can see there are no missing values in the train/test sets as the missing values from `unit_sales` are the ones from the test set."], "cell_type": "markdown", "metadata": {"_cell_guid": "15d1e505-a6f9-4ff5-98c6-fd7363f201cc", "_uuid": "4c6147944a9ed31c85fd2696f5f543c5413fcb85"}}, {"source": ["Now lets merge all the datasets"], "cell_type": "markdown", "metadata": {"_cell_guid": "af8dc9ef-89ad-4a4f-a570-f6322520bcb7", "_uuid": "63eeba1018eacca572b79c1bdb0a1b3c0a5f8353"}}, {"source": ["## 3. Merge each table into our train/test dataframe"], "cell_type": "markdown", "metadata": {"_cell_guid": "e457108e-ba31-4e25-bd47-5524be966156", "_uuid": "5a7efa20d7724e09512778626ad28b2c7853c935"}}, {"source": ["### 3.1 Transactions\n", "\n", "From Kaggle:\n", "> The count of sales transactions for each date, store_nbr combination. Only included for the training data timeframe.\n", "\n", "/!\\ There is no dates with transactions for the test set
"], "cell_type": "markdown", "metadata": {"_cell_guid": "204a1bcf-33db-4cec-9af7-b3828ba93c32", "_uuid": "b1075722f9d29b1552234cc75fd8cc9f49b3228e"}}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "f74c14bc-1978-4a0e-b172-f4d3811b0317", "_uuid": "ca78d27649c30d0e2b93c0db29f5829a6bbcd698"}, "execution_count": null, "source": ["merged_df = merged_df.merge(transactions_df, on=[\"date\", \"store_nbr\"], how='left')\n", "merged_df.head()\n", "print(transactions_df['date'].max())"]}, {"source": ["We merge on [left](http://www.datacarpentry.org/python-ecology-lesson/04-merging-data/) because we want to preserve all the rows of the test set which don't have any couple `[\"date\", \"store_nbr\"]` for the test set"], "cell_type": "markdown", "metadata": {"_cell_guid": "787e76c2-59c4-440d-909b-75756c91d680", "_uuid": "bd5fc6a6f4ce6356529f67aa9b60b7b63b23a9da"}}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "75907330-e07d-48b8-99bf-3ba41ae30aeb", "_uuid": "feb3675e1b94089b4208eaa2f65bd75df7be158c"}, "execution_count": null, "source": ["print(len(merged_df))\n", "display(merged_df.head())\n", "display(merged_df.tail())"]}, {"source": ["### 3.2 Stores\n", "From Kaggle:\n", "> Store metadata, including `city`, `state`, `type`, and `cluster`. `cluster` is a grouping of similar stores."], "cell_type": "markdown", "metadata": {"_cell_guid": "b47f1e10-7f96-4eb9-8587-1380782d28da", "_uuid": "087a8bb1defde5a3a8bf53e16b2303d17fa5fff0"}}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "e8c2a0c1-eb0a-4364-a9de-ddbb4c5d865c", "_uuid": "dfbc8c627b2d30691e2d9c5821e20f6c1d51eae9"}, "execution_count": null, "source": ["stores_df.columns = ['store_' + col if col != \"store_nbr\" else \"store_nbr\" for col in stores_df.columns]\n", "stores_df.head(3)"]}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "613d1536-e476-407f-8039-16079c988333", "_uuid": "3ae3e8ce622da57e6b93e22e10b613776e624329"}, "execution_count": null, "source": ["merged_df = merged_df.merge(stores_df, on=[\"store_nbr\"])"]}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "86eeca8a-5e53-4564-8a65-13c04b84d1a2", "_uuid": "fe264ef0eb80d7bdb9c3eaed906191fa4032af6e"}, "execution_count": null, "source": ["print(len(merged_df))\n", "display(merged_df.head())\n", "display(merged_df.tail())"]}, {"source": ["### 3.3 Oil\n", "\n", "The oild index exists for both the train and test sets but the oil index is not available for every date of the merged dataset"], "cell_type": "markdown", "metadata": {"_cell_guid": "1c9db0e5-b7e6-4bea-aa91-3afb4fa165b9", "_uuid": "b1ac4e42b30e0fbdcbd328f66c0ef8b5a64fedd9"}}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "7e5c2cd6-9542-4261-9532-90a9ca78cb36", "_uuid": "724c8db20542cbe2ca6c1408a7d570b1d06b3b06"}, "execution_count": null, "source": ["oil_df.tail(3)"]}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "bd6c8c90-b735-4c2f-9231-5fc104641825", "_uuid": "f47ea99dfb03c7abc1d7383b0ff547ce9350657d"}, "execution_count": null, "source": ["merged_df = merged_df.merge(oil_df, on=[\"date\"], how='left')"]}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "2722f979-3910-4192-8fe8-48217a0adf50", "_uuid": "6bc57bc691c0718fe23dba0d618c15402c1b9046"}, "execution_count": null, "source": ["assert len(merged_df) == origin_merge_len\n", "display(merged_df.head())\n", "display(merged_df.tail())"]}, {"source": ["### 3.4 Items\n", "\n", "From Kaggle:\n", "> Item metadata, including family, class, and perishable.\n", ">\n", "> NOTE: Items marked as perishable have a score weight of 1.25; otherwise, the weight is 1.0."], "cell_type": "markdown", "metadata": {"_cell_guid": "e90d860c-8628-4760-8a74-3a82d53ece3f", "_uuid": "b6d00b708a8feb836f51abf2ee862d088934d44e"}}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "10c3f422-9a58-4442-b1ed-e36d87011929", "_uuid": "156b63d54178e9b76eca158610552b50a6f7570a"}, "execution_count": null, "source": ["items_df.columns = ['item_' + col if col != \"item_nbr\" else \"item_nbr\" for col in items_df.columns]\n", "items_df.head(3)"]}, {"outputs": [], "cell_type": "code", "metadata": {"_cell_guid": "d5028740-93ec-4d13-b5b5-0532e58d84a2", "_uuid": "283c9849f99e4a5131cfa04230952de72a1eee20"}, "execution_count": null, "source": ["merged_df = merged_df.merge(items_df, on=[\"item_nbr\"])"]}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "7c226378-13c6-4229-b44c-9e3513de5a01", "_uuid": "9010e890bb6b83393a6c5c8f084dd9b2f0e1251c"}, "execution_count": null, "source": ["assert len(merged_df) == origin_merge_len\n", "display(merged_df.head())\n", "display(merged_df.tail())"]}, {"source": ["### 3.5 Holidays events\n", "There are duplicate dates in the `date` column, we need to fix this before merging `holidays_df` into `merged_df`.
"], "cell_type": "markdown", "metadata": {"_cell_guid": "a96b69c9-965e-49f8-a62b-432301009ebc", "_uuid": "79c17d6adb95eaae3199f0344954a8116141e061"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "177c243e-4d81-4241-ae35-535a2c6077d8", "_uuid": "e2c4551436df547fe4ef065559cf8714b537cd93"}, "execution_count": null, "source": ["print(f\"# of entries: {len(holidays_df)}\")\n", "print(holidays_df.nunique())\n", "holidays_df[holidays_df['date'].duplicated(keep=False)].head()"]}, {"source": ["We can see that these duplications are due to the fact that some holidays of the same day can be different depending on the `locale_name`.So here we need to be careful to not only merge the datasets on the `date` but also on the `locale_name` where the `store` of a given entry is located.
\n", "\n", "To make things easier here we won't try to map `store_city` or `store_state` from `merged_df` to the `locale_name` of `holidays_df`. We'll just get rid of every rows which `locale_name` is not `Ecuador` and check if we still have duplicates."], "cell_type": "markdown", "metadata": {"_cell_guid": "aeef62af-062e-45fd-8c08-59657155d0ac", "_uuid": "e153a61d4efbc934296a230834bedc48818c169f"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "bcf43c6f-68bf-4d41-9e72-1a816cf385b6", "_uuid": "1dd5bfa67f5997bf51d0ed402ba35a1e36bd2d54"}, "execution_count": null, "source": ["holidays_df = holidays_df[holidays_df[\"locale_name\"] == \"Ecuador\"]\n", "print(f\"# of entries: {len(holidays_df)}\")\n", "print(holidays_df.nunique())\n", "holidays_df[holidays_df['date'].duplicated(keep=False)].head()"]}, {"source": ["Not yet! We still have duplicates, so lets just remove the duplicates now"], "cell_type": "markdown", "metadata": {"_cell_guid": "19276853-ae1f-4d6c-8017-58a14ba4ab4c", "_uuid": "c4ac848b317a3630a6844f00821332a3ecc4f9b5"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "e2f072cf-69a5-442d-a344-60cff69d4077", "_uuid": "440b39f630e0f53d92daea7f8fba936e4bf74343"}, "execution_count": null, "source": ["holidays_df = holidays_df[np.invert(holidays_df['date'].duplicated())]\n", "print(f\"# of entries: {len(holidays_df)}\")\n", "print(holidays_df.nunique())\n", "holidays_df.head()"]}, {"source": ["Great we're done with the duplicates, now we can now continue drop `locale` and `locale_name` as they all have the same value."], "cell_type": "markdown", "metadata": {"_cell_guid": "8076b80d-1b57-44bc-97cb-d4e945547603", "_uuid": "93c69fe181266df5294df73e25109c52eb85dfcb"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "d3b8ed7b-140f-46ee-9a60-b50cd21b93fe", "_uuid": "8c83e4ec63b54152dd8d61a6bc094aba6d0aeb7e"}, "execution_count": null, "source": ["holidays_df = holidays_df.drop([\"locale\", \"locale_name\"], axis=1)"]}, {"source": ["Now to the merge."], "cell_type": "markdown", "metadata": {"_cell_guid": "3e7226fb-1209-4a6d-b317-03963b5813b7", "_uuid": "da1b7c2694cec2a71ade430526b942268821473d"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "4df9e44e-ebef-4931-8bef-b083c836d63f", "_uuid": "f2de846815a3b5a35a86364f2166f0be51e0be21"}, "execution_count": null, "source": ["holidays_df.columns = ['holiday_' + col if col != \"date\" else \"date\" for col in holidays_df.columns]\n", "holidays_df.head(3)"]}, {"source": ["Here we'll do something a bit different. According to Kaggle:\n", "> A holiday that is `transferred` officially falls on that calendar day, but was moved to another date by the government. A transferred day is more like a normal day than a holiday. To find the day that it was actually celebrated, look for the corresponding row where `type` is `Transfer`. For example, the holiday Independencia de Guayaquil was transferred from 2012-10-09 to 2012-10-12, which means it was celebrated on 2012-10-12. Days that are type Bridge are extra days that are added to a holiday (e.g., to extend the break across a long weekend). These are frequently made up by the type Work Day which is a day not normally scheduled for work (e.g., Saturday) that is meant to payback the Bridge.\n", "\n", "So basically we are only interested in the day when the holiday event actually happened. Before merging this table to our `merged_df` we can get rid of the rows where `transferred` is `True` and then get rid of the column itself."], "cell_type": "markdown", "metadata": {"_cell_guid": "4de0d070-a1c9-44ac-84f6-7cdf8e3fac75", "_uuid": "a1164f37bc7767b91d472c9be4a36d1985c285cd"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "86ce9821-438f-404f-acb2-5c38ed7f8035", "_uuid": "cb48f86912a90192abf48ea7c64e85d978e08290"}, "execution_count": null, "source": ["holidays_df = holidays_df[holidays_df[\"holiday_transferred\"] != True]\n", "holidays_df = holidays_df.drop([\"holiday_transferred\"], axis=1)\n", "holidays_df.head()"]}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "f123e1cb-7ace-4435-b7c2-9e52e7952416", "_uuid": "cb914366dcf81fc22a57f754a8bb40af5cd7831b"}, "execution_count": null, "source": ["merged_df = merged_df.merge(holidays_df, on=\"date\", how='left')"]}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "2b1e9b2d-1ae6-4a53-9cd2-49f440199fb4", "_uuid": "659c3efad83d326074a3019d24ac525bcaa4b8d9"}, "execution_count": null, "source": ["assert len(merged_df) == origin_merge_len\n", "display(merged_df.head())\n", "display(merged_df.tail())"]}, {"source": ["## Final datasets output & checkup"], "cell_type": "markdown", "metadata": {"_cell_guid": "666124f4-f3a2-42eb-b3b4-a45fb44d07d8", "_uuid": "ec9ccf3f347473ec65f18624b0b4a374f847bce6"}}, {"source": ["Lets take a look at what our final dataset looks like"], "cell_type": "markdown", "metadata": {"_cell_guid": "49977181-8109-493a-ae7e-40bbaae7d229", "_uuid": "e8ee1827d858a45a8592340c12ed4847cc64d138"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "8ad7c48d-39bc-40ca-bdc4-f26ef3e798a0", "_uuid": "e09ca198d1ff8ce93b0ba3589ef330043f2fe212"}, "execution_count": null, "source": ["print(len(merged_df))\n", "display(merged_df.dtypes)\n", "merged_df.head()"]}, {"source": ["Now lets define our `id` as index"], "cell_type": "markdown", "metadata": {"_cell_guid": "b5df64e2-dca3-4aa9-b1bb-62c9492ade0f", "_uuid": "3914f707a74597dc435f4c7fb179e0a4f8e4bc09"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "d8784942-35cc-4fc0-a6d0-86e74074ed81", "_uuid": "89a2acdd1902ee9c817f6be9c73b43799bd2d2db"}, "execution_count": null, "source": ["merged_df.set_index(\"id\", inplace=True)"]}, {"source": ["Do we still have the same number of entry as before we started the merging operations?"], "cell_type": "markdown", "metadata": {"_cell_guid": "46d7e2e2-9ce3-4d84-b2cd-1b455e7c08f0", "_uuid": "78046387d9ad4362b441756b623f85410143ed7b"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "3d65a390-131b-4a33-a7f9-97efeac04cc3", "_uuid": "233f8b03e94bc2e3c0c5230d64b1dfdc54fab9df"}, "execution_count": null, "source": ["assert len(merged_df) == origin_merge_len"]}, {"source": ["Count the missing values for each set on the whole"], "cell_type": "markdown", "metadata": {"_cell_guid": "f271b0d5-1814-4109-b5ff-19a9690ad80b", "_uuid": "25c8cdba852c2eeb29b3c5cdb55067fc343bcdec"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "425c87c0-7b70-44b8-ba37-ff3be507690c", "_uuid": "67d372abcb92afcf90b46c4359954b8f40a8a98f"}, "execution_count": null, "source": ["display(merged_df.isnull().sum().sort_index() / len(merged_df))"]}, {"source": ["Looks legit!"], "cell_type": "markdown", "metadata": {"_cell_guid": "577d1cc6-f8ae-41cc-8a6b-111f6df22a50", "_uuid": "3639e0dfa74f96ec3528674105c03e5c0f518c24"}}, {"source": ["Now we can get rid of columns we know are completely useless such as `item_nbr`, `store_nbr` which are just identifiers and does not hold relevant informations."], "cell_type": "markdown", "metadata": {"_cell_guid": "6f01f103-c68a-4611-9388-b2f5471cba16", "_uuid": "9ca8ed0b243d5948fe4bd8224a32bd099f32b31f"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "e96c9d01-b0f1-47db-b8cd-6b326a29d2d6", "_uuid": "b7d0863a60ca43b6de54fa56be41e593b1ae5199"}, "execution_count": null, "source": ["merged_df = merged_df.drop([\"item_nbr\", \"store_nbr\"], axis=1)"]}, {"source": ["Now lets split back to test/train splits"], "cell_type": "markdown", "metadata": {"_cell_guid": "0bcd8ab8-ed2e-4424-a552-f3eefaa43e72", "_uuid": "66d85eebbfa00f640b55b540f0c54a457964ddd7"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "ec8b6b7f-9d16-465b-a215-cac29bafe571", "_uuid": "be2808906a2a1284ff9396388e17451e3d007924"}, "execution_count": null, "source": ["train_df = merged_df[(merged_df['date'] >= train_date_range[0]) & (merged_df['date'] <= train_date_range[1])]\n", "train_df = train_df.sort_index()\n", "test_df = merged_df[(merged_df['date'] >= test_date_range[0]) & (merged_df['date'] <= test_date_range[1])]\n", "test_df = test_df.sort_index()\n", "print(f\"train_df range: {train_df['date'].min()} to {train_df['date'].max()}\")\n", "print(f\"test_df range: {test_df['date'].min()} to {test_df['date'].max()}\")"]}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "2909a940-7735-4aec-9e80-ede11793f911", "_uuid": "8967d09de760ea228b9d0e30362650a31c2caf66"}, "execution_count": null, "source": ["assert origin_train_len == len(train_df)\n", "assert origin_test_len == len(test_df)\n", "display(train_df.head(3))\n", "display(test_df.head(3))"]}, {"source": ["And finally save them as csv:"], "cell_type": "markdown", "metadata": {"_cell_guid": "23d80cef-63d6-471e-866a-dd1d135e1192", "_uuid": "3206f85e44dc90cbeb09269134a8a5232aad1ae8"}}, {"outputs": [], "cell_type": "code", "metadata": {"collapsed": true, "_cell_guid": "d9671e68-4bd6-4201-9f07-65fd6fb8a68d", "_uuid": "d978243a4eb096b4f780f8dd615de6fe4cd2f638"}, "execution_count": null, "source": ["# train_df.to_csv(\"train_joined.csv\")\n", "# test_df.to_csv(\"test_joined.csv\")\n", "print(\"Operation finished!\")"]}, {"source": ["Don't forget to shutdown your kernel now to clear up your memory
"], "cell_type": "markdown", "metadata": {"_cell_guid": "eb35a6fe-22d1-4c31-aeed-f84ad8a2e4a2", "_uuid": "ae278f43a81d1a91ec41790425ba863f7c10a530"}}, {"source": ["Now we can do some feature engineering and transform our data to a format usable by our machine learning models. \n", "We could also exploit this information from kaggle to scrape useful informations from the net to add to our dataset:\n", "> Wages in the public sector are paid every two weeks on the 15 th and on the last day of the month. Supermarket sales could be affected by this.\n", "\n", "So maybe every 15/16/17th the sales get higher a bit?\n", "But we'll leave this here for now :)\n", "\n", "Happy hacking!"], "cell_type": "markdown", "metadata": {"_cell_guid": "8fed98df-b5fb-469d-8f78-9a513ef4c9c5", "_uuid": "a1c43ac00f9ace044d8309e23cc2fa1d9c3a9b2e"}}], "metadata": {"kernelspec": {"name": "python3", "display_name": "Python 3", "language": "python"}, "language_info": {"codemirror_mode": {"name": "ipython", "version": 3}, "mimetype": "text/x-python", "version": "3.6.3", "name": "python", "file_extension": ".py", "pygments_lexer": "ipython3", "nbconvert_exporter": "python"}}, "nbformat": 4}