1_prophet_data_selection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2a63ac9f",
   "metadata": {},
   "source": [
    "# Data Selection \n",
    "1. Select only the columns we need for running `prophet`.\n",
    "2. Select sales data including and after 2018/10/01, in order to accommodate constraints on computational resources and time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fbafd422",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "from src.data_helpers import load_lookup_tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cc1d2e6d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(99, 99)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dept_lookup = pd.read_csv(\"data/dept_lookup.txt\", sep=\"|\")\n",
    "\n",
    "# Making sure that np.int16 can represent the largest DEPARTMENT_ID\n",
    "dept_lookup.DEPARTMENT_ID.max(), np.int16(dept_lookup.DEPARTMENT_ID.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c4815d84",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7899.0, 7899)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store_lookup = pd.read_csv(\"data/store_lookup.txt\", sep=\"|\")\n",
    "\n",
    "# Making sure that np.int16 can represent the largest LOC_ID\n",
    "store_lookup.LOC_ID.max(), np.int16(store_lookup.LOC_ID.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a5dea339",
   "metadata": {},
   "outputs": [],
   "source": [
    "bm_sales_col_dtypes = {\n",
    "    \"LOC_ID\": np.int16,\n",
    "    \"DEPARTMENT_ID\": np.int16,\n",
    "    \"ACTUAL_SALES\": np.float16\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "08df1b32",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 9min 9s, sys: 34.5 s, total: 9min 44s\n",
      "Wall time: 9min 39s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "bm_sales_df = []\n",
    "\n",
    "bm_sales_csviter = pd.read_csv(\n",
    "    \"data/day_bm_loc_dep_sales_201801_202102.txt.gz\",\n",
    "    sep=\"|\",\n",
    "    dtype=bm_sales_col_dtypes,\n",
    "    usecols=[\"DAY_ID\", \"LOC_ID\", \"DEPARTMENT_ID\", \"ACTUAL_SALES\"],\n",
    "    parse_dates=[\"DAY_ID\"],\n",
    "    chunksize=10000,\n",
    ")\n",
    "\n",
    "\n",
    "for chunk in bm_sales_csviter:\n",
    "    bm_sales_df.append(chunk.query(\"`DAY_ID` >= '2018-10-01'\"))\n",
    "    \n",
    "bm_sales_df = pd.concat(bm_sales_df, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9bcf967e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DAY_ID</th>\n",
       "      <th>LOC_ID</th>\n",
       "      <th>DEPARTMENT_ID</th>\n",
       "      <th>ACTUAL_SALES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-12-03</td>\n",
       "      <td>2768</td>\n",
       "      <td>13</td>\n",
       "      <td>135.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-12-09</td>\n",
       "      <td>3811</td>\n",
       "      <td>20</td>\n",
       "      <td>33.031250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-04-28</td>\n",
       "      <td>739</td>\n",
       "      <td>21</td>\n",
       "      <td>23.984375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-11-26</td>\n",
       "      <td>1179</td>\n",
       "      <td>50</td>\n",
       "      <td>15.882812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-06-25</td>\n",
       "      <td>2433</td>\n",
       "      <td>58</td>\n",
       "      <td>13.992188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-05-02</td>\n",
       "      <td>2402</td>\n",
       "      <td>21</td>\n",
       "      <td>29.984375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-12-21</td>\n",
       "      <td>917</td>\n",
       "      <td>79</td>\n",
       "      <td>5.988281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-04-29</td>\n",
       "      <td>201</td>\n",
       "      <td>11</td>\n",
       "      <td>33.968750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-01-19</td>\n",
       "      <td>1929</td>\n",
       "      <td>82</td>\n",
       "      <td>20.984375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019-04-18</td>\n",
       "      <td>725</td>\n",
       "      <td>22</td>\n",
       "      <td>16.984375</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      DAY_ID  LOC_ID  DEPARTMENT_ID  ACTUAL_SALES\n",
       "0 2020-12-03    2768             13    135.500000\n",
       "1 2018-12-09    3811             20     33.031250\n",
       "2 2019-04-28     739             21     23.984375\n",
       "3 2019-11-26    1179             50     15.882812\n",
       "4 2019-06-25    2433             58     13.992188\n",
       "5 2020-05-02    2402             21     29.984375\n",
       "6 2020-12-21     917             79      5.988281\n",
       "7 2020-04-29     201             11     33.968750\n",
       "8 2019-01-19    1929             82     20.984375\n",
       "9 2019-04-18     725             22     16.984375"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bm_sales_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ebd9b479",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2198.322072"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now we have a 2GB dataframe\n",
    "bm_sales_df.memory_usage().sum() / 1e6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ee02dfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Persist to disk for later \n",
    "bm_sales_df.to_pickle(\"data/bm_sales_df.pickle\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "810392a2",
   "metadata": {},
   "source": [
    "## Save each department as separate files \n",
    "\n",
    "Formatted for `prophet` usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b945d27e",
   "metadata": {},
   "outputs": [],
   "source": [
    "bm_sales_df = pd.read_pickle(\"data/bm_sales_df.pickle\")\n",
    "\n",
    "loc_table, dept_table = load_lookup_tables(\n",
    "    os.path.join(\"data\", \"store_lookup.txt\"),\n",
    "    os.path.join(\"data\", \"dept_lookup.txt\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e9229299",
   "metadata": {},
   "outputs": [],
   "source": [
    "for dept_id in dept_table.DEPARTMENT_ID.unique():\n",
    "    # print(dept_id, dept_table.query(\"`DEPARTMENT_ID` == @dept_id\").iloc[0, 1])\n",
    "    (\n",
    "        bm_sales_df\n",
    "        .query(\"`DEPARTMENT_ID` == @dept_id\")\n",
    "        .astype({\"ACTUAL_SALES\": np.float32})  # upcast, otherwise overflow \n",
    "        .groupby(\"DAY_ID\")[\"ACTUAL_SALES\"].sum()\n",
    "        .reset_index()\n",
    "        .rename(columns={\"DAY_ID\": \"ds\", \"ACTUAL_SALES\": \"y\"})\n",
    "        .to_pickle(os.path.join(\"data\", \"per_dept_daily_sales\", f\"dept_{dept_id}_daily.pickle\"))\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84acfe16",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}