3 - Data Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f7e91bf3-5d22-4364-b270-97228f8f9067",
   "metadata": {},
   "source": [
    "## Part III - Data Preprocessing\n",
    "\n",
    "University of San Diego - MS Applied AI\n",
    "\n",
    "AAI-540 Team 5\n",
    "\n",
    "October 21, 2024"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23e8a144-506b-4e55-a84f-c521ba07a6f8",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Set up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a5d9b15d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\u001b[33m\n",
      "\u001b[0m\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\u001b[33m\n",
      "\u001b[0m\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\u001b[33m\n",
      "\u001b[0m\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\u001b[33m\n",
      "\u001b[0msagemaker.config INFO - Not applying SDK defaults from location: /etc/xdg/sagemaker/config.yaml\n",
      "sagemaker.config INFO - Not applying SDK defaults from location: /root/.config/sagemaker/config.yaml\n",
      "Stored 's3_datalake_path_csv' (str)\n",
      "Stored 'local_data_path_csv' (str)\n",
      "Stored 's3_datalake_path_parquet' (str)\n"
     ]
    }
   ],
   "source": [
    "# setup environment\n",
    "%run 0-Environment_Setup.ipynb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2fd8f1d1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "aai540finalprojectdb\n"
     ]
    }
   ],
   "source": [
    "# check database name\n",
    "print(database_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4498c8b7-1eaa-48ce-bf88-0ffc680feea2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create the database\n",
    "wr.catalog.create_database(database_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "489072f5-45ec-4932-9749-d0c52fb09521",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Load data\n",
    "holidays_df = wr.s3.read_csv(s3_datalake_path_csv + \"/holidays_events.csv\")\n",
    "oil_df = wr.s3.read_csv(s3_datalake_path_csv + \"/oil.csv\")\n",
    "transactions_df = wr.s3.read_csv(s3_datalake_path_csv + \"/transactions.csv\")\n",
    "stores_df = wr.s3.read_csv(s3_datalake_path_csv + \"/stores.csv\")\n",
    "sales_df = wr.s3.read_csv(s3_datalake_path_csv + \"/train.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6908456-db63-4c59-b440-e63a3a4a4e46",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Clean Holidays and save to Parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "153dab5a-ee07-406a-b79d-6cbfaf85751e",
   "metadata": {
    "tags": []
   },
   "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>date</th>\n",
       "      <th>type</th>\n",
       "      <th>locale</th>\n",
       "      <th>locale_name</th>\n",
       "      <th>description</th>\n",
       "      <th>transferred</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-03-02</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Manta</td>\n",
       "      <td>Fundacion de Manta</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-04-01</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Regional</td>\n",
       "      <td>Cotopaxi</td>\n",
       "      <td>Provincializacion de Cotopaxi</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-04-12</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Cuenca</td>\n",
       "      <td>Fundacion de Cuenca</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-04-14</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Libertad</td>\n",
       "      <td>Cantonizacion de Libertad</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-04-21</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Riobamba</td>\n",
       "      <td>Cantonizacion de Riobamba</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date     type    locale locale_name                    description  \\\n",
       "0  2012-03-02  Holiday     Local       Manta             Fundacion de Manta   \n",
       "1  2012-04-01  Holiday  Regional    Cotopaxi  Provincializacion de Cotopaxi   \n",
       "2  2012-04-12  Holiday     Local      Cuenca            Fundacion de Cuenca   \n",
       "3  2012-04-14  Holiday     Local    Libertad      Cantonizacion de Libertad   \n",
       "4  2012-04-21  Holiday     Local    Riobamba      Cantonizacion de Riobamba   \n",
       "\n",
       "   transferred  \n",
       "0        False  \n",
       "1        False  \n",
       "2        False  \n",
       "3        False  \n",
       "4        False  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 350 entries, 0 to 349\n",
      "Data columns (total 6 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   date         350 non-null    object\n",
      " 1   type         350 non-null    object\n",
      " 2   locale       350 non-null    object\n",
      " 3   locale_name  350 non-null    object\n",
      " 4   description  350 non-null    object\n",
      " 5   transferred  350 non-null    bool  \n",
      "dtypes: bool(1), object(5)\n",
      "memory usage: 14.1+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check that the data loaded correctly\n",
    "display(holidays_df.head())\n",
    "holidays_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f30651ff-6535-4033-8352-2b523e68ba78",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Convert dates to datetime (excluding timestamp)\n",
    "holidays_df['date'] = pd.to_datetime(holidays_df['date']).dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "89152362-d3d4-4c24-bbfa-c83fefaa8cbc",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'paths': ['s3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/holidays/holidaysf3720312463c4643ab1213d899efee80.snappy.parquet'],\n",
       " 'partitions_values': {}}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write cleaned data to Parquet\n",
    "wr.s3.to_parquet(\n",
    "    holidays_df, \n",
    "    path=f\"{s3_datalake_path_parquet}/holidays\", \n",
    "    dataset=True,\n",
    "    database=database_name,\n",
    "    table=\"holidays\",\n",
    "    filename_prefix=\"holidays\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "462a633a-9905-4e06-be9c-e58f8a2e0bef",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/3797932140.py:4: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df_show = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>tab_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>holidays</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tab_name\n",
       "0  holidays"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that the table was created\n",
    "statement = \"SHOW TABLES in {}\".format(database_name)\n",
    "\n",
    "df_show = pd.read_sql(statement, conn)\n",
    "df_show.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8f557da2-17ba-4b5d-8528-28f13df4f983",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/1244320953.py:11: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>date</th>\n",
       "      <th>type</th>\n",
       "      <th>locale</th>\n",
       "      <th>locale_name</th>\n",
       "      <th>description</th>\n",
       "      <th>transferred</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-03-02</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Manta</td>\n",
       "      <td>Fundacion de Manta</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-04-01</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Regional</td>\n",
       "      <td>Cotopaxi</td>\n",
       "      <td>Provincializacion de Cotopaxi</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-04-12</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Cuenca</td>\n",
       "      <td>Fundacion de Cuenca</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-04-14</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Libertad</td>\n",
       "      <td>Cantonizacion de Libertad</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-04-21</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Riobamba</td>\n",
       "      <td>Cantonizacion de Riobamba</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date     type    locale locale_name                    description  \\\n",
       "0  2012-03-02  Holiday     Local       Manta             Fundacion de Manta   \n",
       "1  2012-04-01  Holiday  Regional    Cotopaxi  Provincializacion de Cotopaxi   \n",
       "2  2012-04-12  Holiday     Local      Cuenca            Fundacion de Cuenca   \n",
       "3  2012-04-14  Holiday     Local    Libertad      Cantonizacion de Libertad   \n",
       "4  2012-04-21  Holiday     Local    Riobamba      Cantonizacion de Riobamba   \n",
       "\n",
       "   transferred  \n",
       "0        False  \n",
       "1        False  \n",
       "2        False  \n",
       "3        False  \n",
       "4        False  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that data from the table loads correctly\n",
    "table_name = \"holidays\"\n",
    "\n",
    "# Define SQL query\n",
    "statement = \"\"\"SELECT * FROM {}.{}\n",
    "    LIMIT 5\"\"\".format(\n",
    "    database_name, table_name\n",
    ")\n",
    "\n",
    "# Display the results\n",
    "df = pd.read_sql(statement, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "792f3b66-0da6-418b-94c7-f32a8db3fb3e",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Clean Oil and save to Parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "409ac812-adb2-47c9-97b0-ff731b83440a",
   "metadata": {
    "tags": []
   },
   "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>date</th>\n",
       "      <th>dcoilwtico</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>93.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>92.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>93.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>93.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  dcoilwtico\n",
       "0  2013-01-01         NaN\n",
       "1  2013-01-02       93.14\n",
       "2  2013-01-03       92.97\n",
       "3  2013-01-04       93.12\n",
       "4  2013-01-07       93.20"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1218 entries, 0 to 1217\n",
      "Data columns (total 2 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   date        1218 non-null   object \n",
      " 1   dcoilwtico  1175 non-null   float64\n",
      "dtypes: float64(1), object(1)\n",
      "memory usage: 19.2+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check that the data loaded correctly\n",
    "display(oil_df.head())\n",
    "oil_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3121f9bb-b269-4d4e-b8e9-74060d2bf934",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Convert dates to datetime (excluding timestamp)\n",
    "oil_df['date'] = pd.to_datetime(oil_df['date']).dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "61083094-38b3-432c-bea2-ef209f1a342e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1218 entries, 0 to 1217\n",
      "Data columns (total 2 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   date        1218 non-null   object \n",
      " 1   dcoilwtico  1218 non-null   float64\n",
      "dtypes: float64(1), object(1)\n",
      "memory usage: 19.2+ KB\n"
     ]
    }
   ],
   "source": [
    "# Impute the missing values for oil prices by backfilling them\n",
    "oil_df['dcoilwtico'] = oil_df['dcoilwtico'].bfill()\n",
    "oil_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6a8ff421-6841-42a9-9c94-6120e842d78d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'paths': ['s3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/oil/oil905fe25401ae4d8ea70ff440c0e214fe.snappy.parquet'],\n",
       " 'partitions_values': {}}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write cleaned data to Parquet\n",
    "wr.s3.to_parquet(\n",
    "    oil_df, \n",
    "    path=f\"{s3_datalake_path_parquet}/oil\", \n",
    "    dataset=True,\n",
    "    database=database_name,\n",
    "    table=\"oil\",\n",
    "    filename_prefix=\"oil\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "410faec8-0788-4678-b679-4c44a8936080",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/3797932140.py:4: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df_show = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>tab_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>holidays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>oil</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tab_name\n",
       "0  holidays\n",
       "1       oil"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that the table was created\n",
    "statement = \"SHOW TABLES in {}\".format(database_name)\n",
    "\n",
    "df_show = pd.read_sql(statement, conn)\n",
    "df_show.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8969d7a6-6f7d-49cb-ada5-d7d129555b33",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/3206027839.py:11: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>date</th>\n",
       "      <th>dcoilwtico</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>93.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>93.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>92.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>93.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>93.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  dcoilwtico\n",
       "0  2013-01-01       93.14\n",
       "1  2013-01-02       93.14\n",
       "2  2013-01-03       92.97\n",
       "3  2013-01-04       93.12\n",
       "4  2013-01-07       93.20"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that data from the table loads correctly\n",
    "table_name = \"oil\"\n",
    "\n",
    "# Define SQL query\n",
    "statement = \"\"\"SELECT * FROM {}.{}\n",
    "    LIMIT 5\"\"\".format(\n",
    "    database_name, table_name\n",
    ")\n",
    "\n",
    "# Display the results\n",
    "df = pd.read_sql(statement, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "665f0f14-7271-4a77-98cc-4d305f45ba36",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Clean Transactions and save to Parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "fef285da-b83a-4bcf-9a80-28d5cadd3618",
   "metadata": {
    "tags": []
   },
   "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>date</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>1</td>\n",
       "      <td>2111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>2358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>3</td>\n",
       "      <td>3487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>4</td>\n",
       "      <td>1922</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  store_nbr  transactions\n",
       "0  2013-01-01         25           770\n",
       "1  2013-01-02          1          2111\n",
       "2  2013-01-02          2          2358\n",
       "3  2013-01-02          3          3487\n",
       "4  2013-01-02          4          1922"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 83488 entries, 0 to 83487\n",
      "Data columns (total 3 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   date          83488 non-null  object\n",
      " 1   store_nbr     83488 non-null  int64 \n",
      " 2   transactions  83488 non-null  int64 \n",
      "dtypes: int64(2), object(1)\n",
      "memory usage: 1.9+ MB\n"
     ]
    }
   ],
   "source": [
    "# Check that the data loaded correctly\n",
    "display(transactions_df.head())\n",
    "transactions_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c9de14ad-7333-44eb-8fc1-53dd69cd3fc1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Convert dates to datetime (excluding timestamp)\n",
    "transactions_df['date'] = pd.to_datetime(transactions_df['date']).dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9bdbe84f-9475-4714-b890-746ff97000f4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'paths': ['s3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/transactions/transactionsf3dee95bd1074a23bcf64c31363f73d9.snappy.parquet'],\n",
       " 'partitions_values': {}}"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write cleaned data to Parquet\n",
    "wr.s3.to_parquet(\n",
    "    transactions_df, \n",
    "    path=f\"{s3_datalake_path_parquet}/transactions\", \n",
    "    dataset=True,\n",
    "    database=database_name,\n",
    "    table=\"transactions\",\n",
    "    filename_prefix=\"transactions\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "1b7932c2-3c96-4ad2-b8f4-a597e4c8ee43",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/3797932140.py:4: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df_show = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>tab_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>holidays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>oil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>transactions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       tab_name\n",
       "0      holidays\n",
       "1           oil\n",
       "2  transactions"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that the table was created\n",
    "statement = \"SHOW TABLES in {}\".format(database_name)\n",
    "\n",
    "df_show = pd.read_sql(statement, conn)\n",
    "df_show.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "b0633219-c920-4ecb-9501-f3cce6eb0e55",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/1481457548.py:11: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>date</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>1</td>\n",
       "      <td>2111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>2358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>3</td>\n",
       "      <td>3487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>4</td>\n",
       "      <td>1922</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  store_nbr  transactions\n",
       "0  2013-01-01         25           770\n",
       "1  2013-01-02          1          2111\n",
       "2  2013-01-02          2          2358\n",
       "3  2013-01-02          3          3487\n",
       "4  2013-01-02          4          1922"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that data from the table loads correctly\n",
    "table_name = \"transactions\"\n",
    "\n",
    "# Define SQL query\n",
    "statement = \"\"\"SELECT * FROM {}.{}\n",
    "    LIMIT 5\"\"\".format(\n",
    "    database_name, table_name\n",
    ")\n",
    "\n",
    "# Display the results\n",
    "df = pd.read_sql(statement, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6423997-1cd6-4f7e-9154-a9cae095269f",
   "metadata": {},
   "source": [
    "#### Clean Stores and save to Parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "45ab9bb9-863a-47df-9e4e-f54c50514ce8",
   "metadata": {
    "tags": []
   },
   "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>store_nbr</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>type</th>\n",
       "      <th>cluster</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Santo Domingo</td>\n",
       "      <td>Santo Domingo de los Tsachilas</td>\n",
       "      <td>D</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_nbr           city                           state type  cluster\n",
       "0          1          Quito                       Pichincha    D       13\n",
       "1          2          Quito                       Pichincha    D       13\n",
       "2          3          Quito                       Pichincha    D        8\n",
       "3          4          Quito                       Pichincha    D        9\n",
       "4          5  Santo Domingo  Santo Domingo de los Tsachilas    D        4"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 54 entries, 0 to 53\n",
      "Data columns (total 5 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   store_nbr  54 non-null     int64 \n",
      " 1   city       54 non-null     object\n",
      " 2   state      54 non-null     object\n",
      " 3   type       54 non-null     object\n",
      " 4   cluster    54 non-null     int64 \n",
      "dtypes: int64(2), object(3)\n",
      "memory usage: 2.2+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check that the data loaded correctly\n",
    "display(stores_df.head())\n",
    "stores_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "606779cc-77cd-4366-9779-560a43c70125",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'paths': ['s3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/stores/stores46f4dd094b104634b868641f12ad40a4.snappy.parquet'],\n",
       " 'partitions_values': {}}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write cleaned data to Parquet\n",
    "wr.s3.to_parquet(\n",
    "    stores_df, \n",
    "    path=f\"{s3_datalake_path_parquet}/stores\", \n",
    "    dataset=True,\n",
    "    database=database_name,\n",
    "    table=\"stores\",\n",
    "    filename_prefix=\"stores\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f9badb71-5c27-4179-81a6-1c187c59f09f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/3797932140.py:4: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df_show = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>tab_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>holidays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>oil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stores</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>transactions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       tab_name\n",
       "0      holidays\n",
       "1           oil\n",
       "2        stores\n",
       "3  transactions"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that the table was created\n",
    "statement = \"SHOW TABLES in {}\".format(database_name)\n",
    "\n",
    "df_show = pd.read_sql(statement, conn)\n",
    "df_show.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0243ed8b-3e93-4315-b538-365871d148eb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/3250605361.py:11: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>store_nbr</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>type</th>\n",
       "      <th>cluster</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Santo Domingo</td>\n",
       "      <td>Santo Domingo de los Tsachilas</td>\n",
       "      <td>D</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_nbr           city                           state type  cluster\n",
       "0          1          Quito                       Pichincha    D       13\n",
       "1          2          Quito                       Pichincha    D       13\n",
       "2          3          Quito                       Pichincha    D        8\n",
       "3          4          Quito                       Pichincha    D        9\n",
       "4          5  Santo Domingo  Santo Domingo de los Tsachilas    D        4"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that data from the table loads correctly\n",
    "table_name = \"stores\"\n",
    "\n",
    "# Define SQL query\n",
    "statement = \"\"\"SELECT * FROM {}.{}\n",
    "    LIMIT 5\"\"\".format(\n",
    "    database_name, table_name\n",
    ")\n",
    "\n",
    "# Display the results\n",
    "df = pd.read_sql(statement, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9334c74e-347e-4d67-bca9-5d571105d97e",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Clean Sales and save to Parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "8b4a4d42-5ac9-45d1-8c17-d475558d7e7f",
   "metadata": {
    "tags": []
   },
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>family</th>\n",
       "      <th>sales</th>\n",
       "      <th>onpromotion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>AUTOMOTIVE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>BABY CARE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>BEAUTY</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>BEVERAGES</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>BOOKS</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id        date  store_nbr      family  sales  onpromotion\n",
       "0   0  2013-01-01          1  AUTOMOTIVE    0.0            0\n",
       "1   1  2013-01-01          1   BABY CARE    0.0            0\n",
       "2   2  2013-01-01          1      BEAUTY    0.0            0\n",
       "3   3  2013-01-01          1   BEVERAGES    0.0            0\n",
       "4   4  2013-01-01          1       BOOKS    0.0            0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3000888 entries, 0 to 3000887\n",
      "Data columns (total 6 columns):\n",
      " #   Column       Dtype  \n",
      "---  ------       -----  \n",
      " 0   id           int64  \n",
      " 1   date         object \n",
      " 2   store_nbr    int64  \n",
      " 3   family       object \n",
      " 4   sales        float64\n",
      " 5   onpromotion  int64  \n",
      "dtypes: float64(1), int64(3), object(2)\n",
      "memory usage: 137.4+ MB\n"
     ]
    }
   ],
   "source": [
    "# Check that the data loaded correctly\n",
    "display(sales_df.head())\n",
    "sales_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "323a9d0b-127a-4540-948c-9250687b516a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Convert dates to datetime\n",
    "sales_df['date'] = pd.to_datetime(sales_df['date'])\n",
    "\n",
    "# Extract year for partitioning\n",
    "sales_df['year'] = sales_df['date'].dt.year\n",
    "\n",
    "# Convert datetime to date (excluding timestamp)\n",
    "sales_df['date'] = pd.to_datetime(sales_df['date']).dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4ddc3662-9465-48ed-a73e-1d2b3ad2d135",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id             False\n",
       "date           False\n",
       "store_nbr      False\n",
       "family         False\n",
       "sales          False\n",
       "onpromotion    False\n",
       "year           False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for missing values\n",
    "sales_df.isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "9cfe7c4d-30aa-428e-80bd-3f4374a0d7b6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Drop the id column\n",
    "sales_df.drop(columns=['id'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "f8668e1c-da95-4ef0-a310-0d5e40166769",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3000888 entries, 0 to 3000887\n",
      "Data columns (total 6 columns):\n",
      " #   Column       Dtype  \n",
      "---  ------       -----  \n",
      " 0   date         object \n",
      " 1   store_nbr    int64  \n",
      " 2   family       object \n",
      " 3   sales        float64\n",
      " 4   onpromotion  int64  \n",
      " 5   year         int32  \n",
      "dtypes: float64(1), int32(1), int64(2), object(2)\n",
      "memory usage: 125.9+ MB\n"
     ]
    }
   ],
   "source": [
    "sales_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "f090ae74-1c74-4a0a-be66-fdb40686a118",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'paths': ['s3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2013/sales3cc39bf6c144469597af94a564c9ba15.snappy.parquet',\n",
       "  's3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2014/sales3cc39bf6c144469597af94a564c9ba15.snappy.parquet',\n",
       "  's3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2015/sales3cc39bf6c144469597af94a564c9ba15.snappy.parquet',\n",
       "  's3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2016/sales3cc39bf6c144469597af94a564c9ba15.snappy.parquet',\n",
       "  's3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2017/sales3cc39bf6c144469597af94a564c9ba15.snappy.parquet'],\n",
       " 'partitions_values': {'s3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2013/': ['2013'],\n",
       "  's3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2014/': ['2014'],\n",
       "  's3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2015/': ['2015'],\n",
       "  's3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2016/': ['2016'],\n",
       "  's3://sagemaker-us-east-1-342408968837/store-sales-forecasting/parquet/sales/year=2017/': ['2017']}}"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write cleaned data to Parquet\n",
    "wr.s3.to_parquet(\n",
    "    sales_df, \n",
    "    path=f\"{s3_datalake_path_parquet}/sales\", \n",
    "    dataset=True,\n",
    "    database=database_name,\n",
    "    table=\"sales\",\n",
    "    partition_cols=[\"year\"],\n",
    "    filename_prefix=\"sales\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "3d408df6-96af-48f9-91d1-7f3d0ba2d918",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/3797932140.py:4: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df_show = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>tab_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>holidays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>oil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>stores</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>transactions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       tab_name\n",
       "0      holidays\n",
       "1           oil\n",
       "2         sales\n",
       "3        stores\n",
       "4  transactions"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that the table was created\n",
    "statement = \"SHOW TABLES in {}\".format(database_name)\n",
    "\n",
    "df_show = pd.read_sql(statement, conn)\n",
    "df_show.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "80203437-bdea-4458-b2aa-948a1f949422",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_316/3860274799.py:11: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql(statement, conn)\n"
     ]
    },
    {
     "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>date</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>family</th>\n",
       "      <th>sales</th>\n",
       "      <th>onpromotion</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>AUTOMOTIVE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>BABY CARE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>BEAUTY</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>BEVERAGES</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>BOOKS</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  store_nbr      family  sales  onpromotion  year\n",
       "0  2014-01-01          1  AUTOMOTIVE    0.0            0  2014\n",
       "1  2014-01-01          1   BABY CARE    0.0            0  2014\n",
       "2  2014-01-01          1      BEAUTY    0.0            0  2014\n",
       "3  2014-01-01          1   BEVERAGES    0.0            0  2014\n",
       "4  2014-01-01          1       BOOKS    0.0            0  2014"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that data from the table loads correctly\n",
    "table_name = \"sales\"\n",
    "\n",
    "# Define SQL query\n",
    "statement = \"\"\"SELECT * FROM {}.{}\n",
    "    LIMIT 5\"\"\".format(\n",
    "    database_name, table_name\n",
    ")\n",
    "\n",
    "# Display the results\n",
    "df = pd.read_sql(statement, conn)\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "availableInstances": [
   {
    "_defaultOrder": 0,
    "_isFastLaunch": true,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 4,
    "name": "ml.t3.medium",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 1,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.t3.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 2,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.t3.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 3,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.t3.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 4,
    "_isFastLaunch": true,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.m5.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 5,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.m5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 6,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.m5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 7,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.m5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 8,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.m5.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 9,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.m5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 10,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.m5.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 11,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.m5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 12,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.m5d.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 13,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.m5d.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 14,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.m5d.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 15,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.m5d.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 16,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.m5d.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 17,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.m5d.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 18,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.m5d.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 19,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.m5d.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 20,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": true,
    "memoryGiB": 0,
    "name": "ml.geospatial.interactive",
    "supportedImageNames": [
     "sagemaker-geospatial-v1-0"
    ],
    "vcpuNum": 0
   },
   {
    "_defaultOrder": 21,
    "_isFastLaunch": true,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 4,
    "name": "ml.c5.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 22,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.c5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 23,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.c5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 24,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.c5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 25,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 72,
    "name": "ml.c5.9xlarge",
    "vcpuNum": 36
   },
   {
    "_defaultOrder": 26,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 96,
    "name": "ml.c5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 27,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 144,
    "name": "ml.c5.18xlarge",
    "vcpuNum": 72
   },
   {
    "_defaultOrder": 28,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.c5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 29,
    "_isFastLaunch": true,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.g4dn.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 30,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.g4dn.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 31,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.g4dn.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 32,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.g4dn.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 33,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.g4dn.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 34,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.g4dn.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 35,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 61,
    "name": "ml.p3.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 36,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 244,
    "name": "ml.p3.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 37,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 488,
    "name": "ml.p3.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 38,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 768,
    "name": "ml.p3dn.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 39,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.r5.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 40,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.r5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 41,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.r5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 42,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.r5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 43,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.r5.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 44,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.r5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 45,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 512,
    "name": "ml.r5.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 46,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 768,
    "name": "ml.r5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 47,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.g5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 48,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.g5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 49,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.g5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 50,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.g5.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 51,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.g5.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 52,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.g5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 53,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.g5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 54,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 768,
    "name": "ml.g5.48xlarge",
    "vcpuNum": 192
   },
   {
    "_defaultOrder": 55,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 1152,
    "name": "ml.p4d.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 56,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 1152,
    "name": "ml.p4de.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 57,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.trn1.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 58,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 512,
    "name": "ml.trn1.32xlarge",
    "vcpuNum": 128
   },
   {
    "_defaultOrder": 59,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 512,
    "name": "ml.trn1n.32xlarge",
    "vcpuNum": 128
   }
  ],
  "kernelspec": {
   "display_name": "Python 3 (Data Science 3.0)",
   "language": "python",
   "name": "python3__SAGEMAKER_INTERNAL__arn:aws:sagemaker:us-east-1:081325390199:image/sagemaker-data-science-310-v1"
  },
  "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}