mongo_load.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "departments_df = pd.read_csv(\"data/departments.csv\")\n",
    "aisles_df = pd.read_csv(\"data/aisles.csv\")\n",
    "orders_df = pd.read_csv(\"data/orders.csv\")\n",
    "products_df = pd.read_csv(\"data/products.csv\")\n",
    "order_products_prior_df = pd.read_csv(\"data/order_products__prior.csv\")\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### About the following table:\n",
    "\n",
    "This table has been randomly generated where each user_id is related to a name, location (Address, City and State) , Pin-Code and email address."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "random_us_addresses_df = pd.read_csv(\"data/random_us_addresses.csv\", header= None)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to check for NaN values in each column for a table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def checking_NaN(table):\n",
    "    for column in table.columns:\n",
    "        null_values = table[f\"{column}\"].isnull().sum()\n",
    "        print(f\"{column} - {null_values}\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking departments_df for any violations in data and cleaning it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>department_id</th>\n",
       "      <th>department</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>frozen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>produce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>alcohol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>international</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>pets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>dry goods pasta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>bulk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>personal care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>meat seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>pantry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>breakfast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>canned goods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>dairy eggs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>household</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>babies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>snacks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>deli</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>missing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    department_id       department\n",
       "0               1           frozen\n",
       "1               2            other\n",
       "2               3           bakery\n",
       "3               4          produce\n",
       "4               5          alcohol\n",
       "5               6    international\n",
       "6               7        beverages\n",
       "7               8             pets\n",
       "8               9  dry goods pasta\n",
       "9              10             bulk\n",
       "10             11    personal care\n",
       "11             12     meat seafood\n",
       "12             13           pantry\n",
       "13             14        breakfast\n",
       "14             15     canned goods\n",
       "15             16       dairy eggs\n",
       "16             17        household\n",
       "17             18           babies\n",
       "18             19           snacks\n",
       "19             20             deli\n",
       "20             21          missing"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "departments_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking aisles_df for any violations in data and cleaning it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>aisle_id</th>\n",
       "      <th>aisle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>prepared soups salads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>specialty cheeses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>energy granola bars</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>instant foods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>marinades meat preparation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>130</td>\n",
       "      <td>hot cereal pancake mixes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>131</td>\n",
       "      <td>dry pasta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>132</td>\n",
       "      <td>beauty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>133</td>\n",
       "      <td>muscles joints pain relief</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>134</td>\n",
       "      <td>specialty wines champagnes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     aisle_id                       aisle\n",
       "0           1       prepared soups salads\n",
       "1           2           specialty cheeses\n",
       "2           3         energy granola bars\n",
       "3           4               instant foods\n",
       "4           5  marinades meat preparation\n",
       "..        ...                         ...\n",
       "129       130    hot cereal pancake mixes\n",
       "130       131                   dry pasta\n",
       "131       132                      beauty\n",
       "132       133  muscles joints pain relief\n",
       "133       134  specialty wines champagnes\n",
       "\n",
       "[134 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aisles_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking if any duplicate ids or aisles are present"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>aisle_id</th>\n",
       "      <th>aisle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [aisle_id, aisle]\n",
       "Index: []"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aisles_df[aisles_df.duplicated(subset=[\"aisle_id\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>aisle_id</th>\n",
       "      <th>aisle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [aisle_id, aisle]\n",
       "Index: []"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aisles_df[aisles_df.duplicated(subset=[\"aisle\"])]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking if any aisle_id or aisle is null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "aisle_id - 0\n",
      "aisle - 0\n"
     ]
    }
   ],
   "source": [
    "checking_NaN(aisles_df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking orders_df for any violations in data and cleaning it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>eval_set</th>\n",
       "      <th>order_number</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2539329</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2398795</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>473747</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2254736</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>431534</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421078</th>\n",
       "      <td>2266710</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421079</th>\n",
       "      <td>1854736</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421080</th>\n",
       "      <td>626363</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421081</th>\n",
       "      <td>2977660</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421082</th>\n",
       "      <td>272231</td>\n",
       "      <td>206209</td>\n",
       "      <td>train</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3421083 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         order_id  user_id eval_set  order_number  order_dow  \\\n",
       "0         2539329        1    prior             1          2   \n",
       "1         2398795        1    prior             2          3   \n",
       "2          473747        1    prior             3          3   \n",
       "3         2254736        1    prior             4          4   \n",
       "4          431534        1    prior             5          4   \n",
       "...           ...      ...      ...           ...        ...   \n",
       "3421078   2266710   206209    prior            10          5   \n",
       "3421079   1854736   206209    prior            11          4   \n",
       "3421080    626363   206209    prior            12          1   \n",
       "3421081   2977660   206209    prior            13          1   \n",
       "3421082    272231   206209    train            14          6   \n",
       "\n",
       "         order_hour_of_day  days_since_prior_order  \n",
       "0                        8                     NaN  \n",
       "1                        7                    15.0  \n",
       "2                       12                    21.0  \n",
       "3                        7                    29.0  \n",
       "4                       15                    28.0  \n",
       "...                    ...                     ...  \n",
       "3421078                 18                    29.0  \n",
       "3421079                 10                    30.0  \n",
       "3421080                 12                    18.0  \n",
       "3421081                 12                     7.0  \n",
       "3421082                 14                    30.0  \n",
       "\n",
       "[3421083 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking for NaN Values in each column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "order_id - 0\n",
      "user_id - 0\n",
      "eval_set - 0\n",
      "order_number - 0\n",
      "order_dow - 0\n",
      "order_hour_of_day - 0\n",
      "days_since_prior_order - 206209\n"
     ]
    }
   ],
   "source": [
    "checking_NaN(orders_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_df = orders_df.replace(np.nan,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>eval_set</th>\n",
       "      <th>order_number</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2539329</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2398795</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>473747</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2254736</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>431534</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421078</th>\n",
       "      <td>2266710</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421079</th>\n",
       "      <td>1854736</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421080</th>\n",
       "      <td>626363</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421081</th>\n",
       "      <td>2977660</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421082</th>\n",
       "      <td>272231</td>\n",
       "      <td>206209</td>\n",
       "      <td>train</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3421083 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         order_id  user_id eval_set  order_number  order_dow  \\\n",
       "0         2539329        1    prior             1          2   \n",
       "1         2398795        1    prior             2          3   \n",
       "2          473747        1    prior             3          3   \n",
       "3         2254736        1    prior             4          4   \n",
       "4          431534        1    prior             5          4   \n",
       "...           ...      ...      ...           ...        ...   \n",
       "3421078   2266710   206209    prior            10          5   \n",
       "3421079   1854736   206209    prior            11          4   \n",
       "3421080    626363   206209    prior            12          1   \n",
       "3421081   2977660   206209    prior            13          1   \n",
       "3421082    272231   206209    train            14          6   \n",
       "\n",
       "         order_hour_of_day  days_since_prior_order  \n",
       "0                        8                     0.0  \n",
       "1                        7                    15.0  \n",
       "2                       12                    21.0  \n",
       "3                        7                    29.0  \n",
       "4                       15                    28.0  \n",
       "...                    ...                     ...  \n",
       "3421078                 18                    29.0  \n",
       "3421079                 10                    30.0  \n",
       "3421080                 12                    18.0  \n",
       "3421081                 12                     7.0  \n",
       "3421082                 14                    30.0  \n",
       "\n",
       "[3421083 rows x 7 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_df = orders_df.astype({\"days_since_prior_order\":int})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_df.days_since_prior_order = orders_df.days_since_prior_order.replace(0,\"\\\\N\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>eval_set</th>\n",
       "      <th>order_number</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2539329</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2398795</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>473747</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2254736</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>431534</td>\n",
       "      <td>1</td>\n",
       "      <td>prior</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421078</th>\n",
       "      <td>2266710</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421079</th>\n",
       "      <td>1854736</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421080</th>\n",
       "      <td>626363</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421081</th>\n",
       "      <td>2977660</td>\n",
       "      <td>206209</td>\n",
       "      <td>prior</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421082</th>\n",
       "      <td>272231</td>\n",
       "      <td>206209</td>\n",
       "      <td>train</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3421083 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         order_id  user_id eval_set  order_number  order_dow  \\\n",
       "0         2539329        1    prior             1          2   \n",
       "1         2398795        1    prior             2          3   \n",
       "2          473747        1    prior             3          3   \n",
       "3         2254736        1    prior             4          4   \n",
       "4          431534        1    prior             5          4   \n",
       "...           ...      ...      ...           ...        ...   \n",
       "3421078   2266710   206209    prior            10          5   \n",
       "3421079   1854736   206209    prior            11          4   \n",
       "3421080    626363   206209    prior            12          1   \n",
       "3421081   2977660   206209    prior            13          1   \n",
       "3421082    272231   206209    train            14          6   \n",
       "\n",
       "         order_hour_of_day days_since_prior_order  \n",
       "0                        8                     \\N  \n",
       "1                        7                     15  \n",
       "2                       12                     21  \n",
       "3                        7                     29  \n",
       "4                       15                     28  \n",
       "...                    ...                    ...  \n",
       "3421078                 18                     29  \n",
       "3421079                 10                     30  \n",
       "3421080                 12                     18  \n",
       "3421081                 12                      7  \n",
       "3421082                 14                     30  \n",
       "\n",
       "[3421083 rows x 7 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check if combined (order_id, user_id) value is duplicated. This is done since they will be made the primary keys for this table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>eval_set</th>\n",
       "      <th>order_number</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [order_id, user_id, eval_set, order_number, order_dow, order_hour_of_day, days_since_prior_order]\n",
       "Index: []"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_df[orders_df.duplicated(subset=[\"order_id\", \"user_id\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleaning_orders_df():\n",
    "    # Dropping eval set column\n",
    "    orders_df.drop(columns=[\"eval_set\"], inplace=True)\n",
    "    # Making all the nan values in days_since_prior_order replace with 0\n",
    "    # orders_df.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaning_orders_df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2539329</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2398795</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>473747</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2254736</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>431534</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421078</th>\n",
       "      <td>2266710</td>\n",
       "      <td>206209</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421079</th>\n",
       "      <td>1854736</td>\n",
       "      <td>206209</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421080</th>\n",
       "      <td>626363</td>\n",
       "      <td>206209</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421081</th>\n",
       "      <td>2977660</td>\n",
       "      <td>206209</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421082</th>\n",
       "      <td>272231</td>\n",
       "      <td>206209</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3421083 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         order_id  user_id  order_number  order_dow  order_hour_of_day  \\\n",
       "0         2539329        1             1          2                  8   \n",
       "1         2398795        1             2          3                  7   \n",
       "2          473747        1             3          3                 12   \n",
       "3         2254736        1             4          4                  7   \n",
       "4          431534        1             5          4                 15   \n",
       "...           ...      ...           ...        ...                ...   \n",
       "3421078   2266710   206209            10          5                 18   \n",
       "3421079   1854736   206209            11          4                 10   \n",
       "3421080    626363   206209            12          1                 12   \n",
       "3421081   2977660   206209            13          1                 12   \n",
       "3421082    272231   206209            14          6                 14   \n",
       "\n",
       "        days_since_prior_order  \n",
       "0                           \\N  \n",
       "1                           15  \n",
       "2                           21  \n",
       "3                           29  \n",
       "4                           28  \n",
       "...                        ...  \n",
       "3421078                     29  \n",
       "3421079                     30  \n",
       "3421080                     18  \n",
       "3421081                      7  \n",
       "3421082                     30  \n",
       "\n",
       "[3421083 rows x 6 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking products_df for any violations in data and cleaning it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>All-Seasons Salt</td>\n",
       "      <td>104</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Robust Golden Unsweetened Oolong Tea</td>\n",
       "      <td>94</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Smart Ones Classic Favorites Mini Rigatoni Wit...</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Green Chile Anytime Sauce</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49683</th>\n",
       "      <td>49684</td>\n",
       "      <td>Vodka, Triple Distilled, Twist of Vanilla</td>\n",
       "      <td>124</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49684</th>\n",
       "      <td>49685</td>\n",
       "      <td>En Croute Roast Hazelnut Cranberry</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49685</th>\n",
       "      <td>49686</td>\n",
       "      <td>Artisan Baguette</td>\n",
       "      <td>112</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49686</th>\n",
       "      <td>49687</td>\n",
       "      <td>Smartblend Healthy Metabolism Dry Cat Food</td>\n",
       "      <td>41</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49687</th>\n",
       "      <td>49688</td>\n",
       "      <td>Fresh Foaming Cleanser</td>\n",
       "      <td>73</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49688 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       product_id                                       product_name  \\\n",
       "0               1                         Chocolate Sandwich Cookies   \n",
       "1               2                                   All-Seasons Salt   \n",
       "2               3               Robust Golden Unsweetened Oolong Tea   \n",
       "3               4  Smart Ones Classic Favorites Mini Rigatoni Wit...   \n",
       "4               5                          Green Chile Anytime Sauce   \n",
       "...           ...                                                ...   \n",
       "49683       49684          Vodka, Triple Distilled, Twist of Vanilla   \n",
       "49684       49685                 En Croute Roast Hazelnut Cranberry   \n",
       "49685       49686                                   Artisan Baguette   \n",
       "49686       49687         Smartblend Healthy Metabolism Dry Cat Food   \n",
       "49687       49688                             Fresh Foaming Cleanser   \n",
       "\n",
       "       aisle_id  department_id  \n",
       "0            61             19  \n",
       "1           104             13  \n",
       "2            94              7  \n",
       "3            38              1  \n",
       "4             5             13  \n",
       "...         ...            ...  \n",
       "49683       124              5  \n",
       "49684        42              1  \n",
       "49685       112              3  \n",
       "49686        41              8  \n",
       "49687        73             11  \n",
       "\n",
       "[49688 rows x 4 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking for NaN values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "product_id - 0\n",
      "product_name - 0\n",
      "aisle_id - 0\n",
      "department_id - 0\n"
     ]
    }
   ],
   "source": [
    "checking_NaN(products_df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking for duplicate values in product_id as it will be the primary key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [product_id, product_name, aisle_id, department_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products_df[products_df.duplicated(subset=[\"product_id\"])]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mapping department_df to product_df (department_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "products_df['department_id'] = products_df.department_id.map(departments_df.set_index('department_id').department)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>snacks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>All-Seasons Salt</td>\n",
       "      <td>104</td>\n",
       "      <td>pantry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Robust Golden Unsweetened Oolong Tea</td>\n",
       "      <td>94</td>\n",
       "      <td>beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Smart Ones Classic Favorites Mini Rigatoni Wit...</td>\n",
       "      <td>38</td>\n",
       "      <td>frozen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Green Chile Anytime Sauce</td>\n",
       "      <td>5</td>\n",
       "      <td>pantry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49683</th>\n",
       "      <td>49684</td>\n",
       "      <td>Vodka, Triple Distilled, Twist of Vanilla</td>\n",
       "      <td>124</td>\n",
       "      <td>alcohol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49684</th>\n",
       "      <td>49685</td>\n",
       "      <td>En Croute Roast Hazelnut Cranberry</td>\n",
       "      <td>42</td>\n",
       "      <td>frozen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49685</th>\n",
       "      <td>49686</td>\n",
       "      <td>Artisan Baguette</td>\n",
       "      <td>112</td>\n",
       "      <td>bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49686</th>\n",
       "      <td>49687</td>\n",
       "      <td>Smartblend Healthy Metabolism Dry Cat Food</td>\n",
       "      <td>41</td>\n",
       "      <td>pets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49687</th>\n",
       "      <td>49688</td>\n",
       "      <td>Fresh Foaming Cleanser</td>\n",
       "      <td>73</td>\n",
       "      <td>personal care</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49688 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       product_id                                       product_name  \\\n",
       "0               1                         Chocolate Sandwich Cookies   \n",
       "1               2                                   All-Seasons Salt   \n",
       "2               3               Robust Golden Unsweetened Oolong Tea   \n",
       "3               4  Smart Ones Classic Favorites Mini Rigatoni Wit...   \n",
       "4               5                          Green Chile Anytime Sauce   \n",
       "...           ...                                                ...   \n",
       "49683       49684          Vodka, Triple Distilled, Twist of Vanilla   \n",
       "49684       49685                 En Croute Roast Hazelnut Cranberry   \n",
       "49685       49686                                   Artisan Baguette   \n",
       "49686       49687         Smartblend Healthy Metabolism Dry Cat Food   \n",
       "49687       49688                             Fresh Foaming Cleanser   \n",
       "\n",
       "       aisle_id  department_id  \n",
       "0            61         snacks  \n",
       "1           104         pantry  \n",
       "2            94      beverages  \n",
       "3            38         frozen  \n",
       "4             5         pantry  \n",
       "...         ...            ...  \n",
       "49683       124        alcohol  \n",
       "49684        42         frozen  \n",
       "49685       112         bakery  \n",
       "49686        41           pets  \n",
       "49687        73  personal care  \n",
       "\n",
       "[49688 rows x 4 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mapping aisle_df to product_df (aisle_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "products_df['aisle_id'] = products_df.aisle_id.map(aisles_df.set_index('aisle_id').aisle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>cookies cakes</td>\n",
       "      <td>snacks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>All-Seasons Salt</td>\n",
       "      <td>spices seasonings</td>\n",
       "      <td>pantry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Robust Golden Unsweetened Oolong Tea</td>\n",
       "      <td>tea</td>\n",
       "      <td>beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Smart Ones Classic Favorites Mini Rigatoni Wit...</td>\n",
       "      <td>frozen meals</td>\n",
       "      <td>frozen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Green Chile Anytime Sauce</td>\n",
       "      <td>marinades meat preparation</td>\n",
       "      <td>pantry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49683</th>\n",
       "      <td>49684</td>\n",
       "      <td>Vodka, Triple Distilled, Twist of Vanilla</td>\n",
       "      <td>spirits</td>\n",
       "      <td>alcohol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49684</th>\n",
       "      <td>49685</td>\n",
       "      <td>En Croute Roast Hazelnut Cranberry</td>\n",
       "      <td>frozen vegan vegetarian</td>\n",
       "      <td>frozen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49685</th>\n",
       "      <td>49686</td>\n",
       "      <td>Artisan Baguette</td>\n",
       "      <td>bread</td>\n",
       "      <td>bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49686</th>\n",
       "      <td>49687</td>\n",
       "      <td>Smartblend Healthy Metabolism Dry Cat Food</td>\n",
       "      <td>cat food care</td>\n",
       "      <td>pets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49687</th>\n",
       "      <td>49688</td>\n",
       "      <td>Fresh Foaming Cleanser</td>\n",
       "      <td>facial care</td>\n",
       "      <td>personal care</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49688 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       product_id                                       product_name  \\\n",
       "0               1                         Chocolate Sandwich Cookies   \n",
       "1               2                                   All-Seasons Salt   \n",
       "2               3               Robust Golden Unsweetened Oolong Tea   \n",
       "3               4  Smart Ones Classic Favorites Mini Rigatoni Wit...   \n",
       "4               5                          Green Chile Anytime Sauce   \n",
       "...           ...                                                ...   \n",
       "49683       49684          Vodka, Triple Distilled, Twist of Vanilla   \n",
       "49684       49685                 En Croute Roast Hazelnut Cranberry   \n",
       "49685       49686                                   Artisan Baguette   \n",
       "49686       49687         Smartblend Healthy Metabolism Dry Cat Food   \n",
       "49687       49688                             Fresh Foaming Cleanser   \n",
       "\n",
       "                         aisle_id  department_id  \n",
       "0                   cookies cakes         snacks  \n",
       "1               spices seasonings         pantry  \n",
       "2                             tea      beverages  \n",
       "3                    frozen meals         frozen  \n",
       "4      marinades meat preparation         pantry  \n",
       "...                           ...            ...  \n",
       "49683                     spirits        alcohol  \n",
       "49684     frozen vegan vegetarian         frozen  \n",
       "49685                       bread         bakery  \n",
       "49686               cat food care           pets  \n",
       "49687                 facial care  personal care  \n",
       "\n",
       "[49688 rows x 4 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking order_products_prior_df for any violations in data and cleaning it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>33120</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>28985</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>9327</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>45918</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>30035</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434484</th>\n",
       "      <td>3421083</td>\n",
       "      <td>39678</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434485</th>\n",
       "      <td>3421083</td>\n",
       "      <td>11352</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434486</th>\n",
       "      <td>3421083</td>\n",
       "      <td>4600</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434487</th>\n",
       "      <td>3421083</td>\n",
       "      <td>24852</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434488</th>\n",
       "      <td>3421083</td>\n",
       "      <td>5020</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32434489 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          order_id  product_id  add_to_cart_order  reordered\n",
       "0                2       33120                  1          1\n",
       "1                2       28985                  2          1\n",
       "2                2        9327                  3          0\n",
       "3                2       45918                  4          1\n",
       "4                2       30035                  5          0\n",
       "...            ...         ...                ...        ...\n",
       "32434484   3421083       39678                  6          1\n",
       "32434485   3421083       11352                  7          0\n",
       "32434486   3421083        4600                  8          0\n",
       "32434487   3421083       24852                  9          1\n",
       "32434488   3421083        5020                 10          1\n",
       "\n",
       "[32434489 rows x 4 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_products_prior_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [order_id, product_id, add_to_cart_order, reordered]\n",
       "Index: []"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_products_prior_df[order_products_prior_df.duplicated(subset=[\"order_id\", \"product_id\"])]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking for NaN values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "order_id - 0\n",
      "product_id - 0\n",
      "add_to_cart_order - 0\n",
      "reordered - 0\n"
     ]
    }
   ],
   "source": [
    "checking_NaN(order_products_prior_df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking random_us_addresses_df for any violations in data and cleaning it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Kelly Norman</td>\n",
       "      <td>530 Kevin Plain</td>\n",
       "      <td>Beckstad</td>\n",
       "      <td>DC</td>\n",
       "      <td>10028</td>\n",
       "      <td>kellynorman530@gordon-carey.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Susan Hernandez</td>\n",
       "      <td>3210 Stuart Union</td>\n",
       "      <td>Port Spencer</td>\n",
       "      <td>VA</td>\n",
       "      <td>88043</td>\n",
       "      <td>susanhernandez3210@ayala.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>David Roach</td>\n",
       "      <td>0687 Matthew Circles</td>\n",
       "      <td>Mallorybury</td>\n",
       "      <td>PW</td>\n",
       "      <td>85734</td>\n",
       "      <td>davidroach0687@chang.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Brandon Brown</td>\n",
       "      <td>71413 Dickson Hills Apt. 762</td>\n",
       "      <td>Glendachester</td>\n",
       "      <td>PA</td>\n",
       "      <td>61726</td>\n",
       "      <td>brandonbrown71413@hanna-cruz.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Joshua French</td>\n",
       "      <td>2949 Samantha Wall Suite 216</td>\n",
       "      <td>New Davidview</td>\n",
       "      <td>WI</td>\n",
       "      <td>18740</td>\n",
       "      <td>joshuafrench2949@sanchez.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999995</th>\n",
       "      <td>3999995</td>\n",
       "      <td>Pamela Waller</td>\n",
       "      <td>02886 Sara Cove Suite 663</td>\n",
       "      <td>Smithland</td>\n",
       "      <td>NH</td>\n",
       "      <td>9812</td>\n",
       "      <td>pamelawaller02886@serrano-duke.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999996</th>\n",
       "      <td>3999996</td>\n",
       "      <td>Jeffrey Lamb</td>\n",
       "      <td>1849 Erin Divide</td>\n",
       "      <td>Port Kelliville</td>\n",
       "      <td>SC</td>\n",
       "      <td>58072</td>\n",
       "      <td>jeffreylamb1849@dillon.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999997</th>\n",
       "      <td>3999997</td>\n",
       "      <td>Crystal Walsh</td>\n",
       "      <td>02038 Jason Alley</td>\n",
       "      <td>Steventown</td>\n",
       "      <td>WI</td>\n",
       "      <td>29788</td>\n",
       "      <td>crystalwalsh02038@jennings.biz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999998</th>\n",
       "      <td>3999998</td>\n",
       "      <td>Joe Davidson</td>\n",
       "      <td>055 Jordan Pass</td>\n",
       "      <td>Guzmanfurt</td>\n",
       "      <td>SC</td>\n",
       "      <td>25399</td>\n",
       "      <td>joedavidson055@parker-schmitt.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999999</th>\n",
       "      <td>3999999</td>\n",
       "      <td>Yvette Webb</td>\n",
       "      <td>643 Kayla Junction Suite 782</td>\n",
       "      <td>North Brian</td>\n",
       "      <td>GA</td>\n",
       "      <td>27195</td>\n",
       "      <td>yvettewebb643@davis.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4000000 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               0                1                             2  \\\n",
       "0              0     Kelly Norman               530 Kevin Plain   \n",
       "1              1  Susan Hernandez             3210 Stuart Union   \n",
       "2              2      David Roach          0687 Matthew Circles   \n",
       "3              3    Brandon Brown  71413 Dickson Hills Apt. 762   \n",
       "4              4    Joshua French  2949 Samantha Wall Suite 216   \n",
       "...          ...              ...                           ...   \n",
       "3999995  3999995    Pamela Waller     02886 Sara Cove Suite 663   \n",
       "3999996  3999996     Jeffrey Lamb              1849 Erin Divide   \n",
       "3999997  3999997    Crystal Walsh             02038 Jason Alley   \n",
       "3999998  3999998     Joe Davidson               055 Jordan Pass   \n",
       "3999999  3999999      Yvette Webb  643 Kayla Junction Suite 782   \n",
       "\n",
       "                       3   4      5                                   6  \n",
       "0               Beckstad  DC  10028     kellynorman530@gordon-carey.com  \n",
       "1           Port Spencer  VA  88043        susanhernandez3210@ayala.com  \n",
       "2            Mallorybury  PW  85734            davidroach0687@chang.com  \n",
       "3          Glendachester  PA  61726    brandonbrown71413@hanna-cruz.com  \n",
       "4          New Davidview  WI  18740        joshuafrench2949@sanchez.com  \n",
       "...                  ...  ..    ...                                 ...  \n",
       "3999995        Smithland  NH   9812  pamelawaller02886@serrano-duke.org  \n",
       "3999996  Port Kelliville  SC  58072          jeffreylamb1849@dillon.com  \n",
       "3999997       Steventown  WI  29788      crystalwalsh02038@jennings.biz  \n",
       "3999998       Guzmanfurt  SC  25399   joedavidson055@parker-schmitt.com  \n",
       "3999999      North Brian  GA  27195             yvettewebb643@davis.com  \n",
       "\n",
       "[4000000 rows x 7 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "random_us_addresses_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Naming the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "random_us_addresses_df.rename(columns= {\n",
    "    0: \"user_id\",\n",
    "    1: \"user_name\",\n",
    "    2: \"user_address\",\n",
    "    3: \"city\",\n",
    "    4: \"state\",\n",
    "    5: \"pincode\",\n",
    "    6: \"user_email\",\n",
    "}, inplace= True )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>user_id</th>\n",
       "      <th>user_name</th>\n",
       "      <th>user_address</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>pincode</th>\n",
       "      <th>user_email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Kelly Norman</td>\n",
       "      <td>530 Kevin Plain</td>\n",
       "      <td>Beckstad</td>\n",
       "      <td>DC</td>\n",
       "      <td>10028</td>\n",
       "      <td>kellynorman530@gordon-carey.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Susan Hernandez</td>\n",
       "      <td>3210 Stuart Union</td>\n",
       "      <td>Port Spencer</td>\n",
       "      <td>VA</td>\n",
       "      <td>88043</td>\n",
       "      <td>susanhernandez3210@ayala.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>David Roach</td>\n",
       "      <td>0687 Matthew Circles</td>\n",
       "      <td>Mallorybury</td>\n",
       "      <td>PW</td>\n",
       "      <td>85734</td>\n",
       "      <td>davidroach0687@chang.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Brandon Brown</td>\n",
       "      <td>71413 Dickson Hills Apt. 762</td>\n",
       "      <td>Glendachester</td>\n",
       "      <td>PA</td>\n",
       "      <td>61726</td>\n",
       "      <td>brandonbrown71413@hanna-cruz.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Joshua French</td>\n",
       "      <td>2949 Samantha Wall Suite 216</td>\n",
       "      <td>New Davidview</td>\n",
       "      <td>WI</td>\n",
       "      <td>18740</td>\n",
       "      <td>joshuafrench2949@sanchez.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999995</th>\n",
       "      <td>3999995</td>\n",
       "      <td>Pamela Waller</td>\n",
       "      <td>02886 Sara Cove Suite 663</td>\n",
       "      <td>Smithland</td>\n",
       "      <td>NH</td>\n",
       "      <td>9812</td>\n",
       "      <td>pamelawaller02886@serrano-duke.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999996</th>\n",
       "      <td>3999996</td>\n",
       "      <td>Jeffrey Lamb</td>\n",
       "      <td>1849 Erin Divide</td>\n",
       "      <td>Port Kelliville</td>\n",
       "      <td>SC</td>\n",
       "      <td>58072</td>\n",
       "      <td>jeffreylamb1849@dillon.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999997</th>\n",
       "      <td>3999997</td>\n",
       "      <td>Crystal Walsh</td>\n",
       "      <td>02038 Jason Alley</td>\n",
       "      <td>Steventown</td>\n",
       "      <td>WI</td>\n",
       "      <td>29788</td>\n",
       "      <td>crystalwalsh02038@jennings.biz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999998</th>\n",
       "      <td>3999998</td>\n",
       "      <td>Joe Davidson</td>\n",
       "      <td>055 Jordan Pass</td>\n",
       "      <td>Guzmanfurt</td>\n",
       "      <td>SC</td>\n",
       "      <td>25399</td>\n",
       "      <td>joedavidson055@parker-schmitt.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999999</th>\n",
       "      <td>3999999</td>\n",
       "      <td>Yvette Webb</td>\n",
       "      <td>643 Kayla Junction Suite 782</td>\n",
       "      <td>North Brian</td>\n",
       "      <td>GA</td>\n",
       "      <td>27195</td>\n",
       "      <td>yvettewebb643@davis.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4000000 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id        user_name                  user_address  \\\n",
       "0              0     Kelly Norman               530 Kevin Plain   \n",
       "1              1  Susan Hernandez             3210 Stuart Union   \n",
       "2              2      David Roach          0687 Matthew Circles   \n",
       "3              3    Brandon Brown  71413 Dickson Hills Apt. 762   \n",
       "4              4    Joshua French  2949 Samantha Wall Suite 216   \n",
       "...          ...              ...                           ...   \n",
       "3999995  3999995    Pamela Waller     02886 Sara Cove Suite 663   \n",
       "3999996  3999996     Jeffrey Lamb              1849 Erin Divide   \n",
       "3999997  3999997    Crystal Walsh             02038 Jason Alley   \n",
       "3999998  3999998     Joe Davidson               055 Jordan Pass   \n",
       "3999999  3999999      Yvette Webb  643 Kayla Junction Suite 782   \n",
       "\n",
       "                    city state  pincode                          user_email  \n",
       "0               Beckstad    DC    10028     kellynorman530@gordon-carey.com  \n",
       "1           Port Spencer    VA    88043        susanhernandez3210@ayala.com  \n",
       "2            Mallorybury    PW    85734            davidroach0687@chang.com  \n",
       "3          Glendachester    PA    61726    brandonbrown71413@hanna-cruz.com  \n",
       "4          New Davidview    WI    18740        joshuafrench2949@sanchez.com  \n",
       "...                  ...   ...      ...                                 ...  \n",
       "3999995        Smithland    NH     9812  pamelawaller02886@serrano-duke.org  \n",
       "3999996  Port Kelliville    SC    58072          jeffreylamb1849@dillon.com  \n",
       "3999997       Steventown    WI    29788      crystalwalsh02038@jennings.biz  \n",
       "3999998       Guzmanfurt    SC    25399   joedavidson055@parker-schmitt.com  \n",
       "3999999      North Brian    GA    27195             yvettewebb643@davis.com  \n",
       "\n",
       "[4000000 rows x 7 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "random_us_addresses_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "user_id - 0\n",
      "user_name - 0\n",
      "user_address - 0\n",
      "city - 0\n",
      "state - 0\n",
      "pincode - 0\n",
      "user_email - 0\n"
     ]
    }
   ],
   "source": [
    "checking_NaN(random_us_addresses_df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Making order collection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def datetime(x, y):\n",
    "    return {\"order_dow\": x, \"order_hour_of_day\": y}"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding datetime column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_df[\"datetime\"] = orders_df.apply(lambda row: datetime(row[\"order_dow\"], row[\"order_hour_of_day\"]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_df.drop(columns={\"order_dow\", \"order_hour_of_day\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2539329</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>\\N</td>\n",
       "      <td>{'order_dow': 2, 'order_hour_of_day': 8}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2398795</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>{'order_dow': 3, 'order_hour_of_day': 7}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>473747</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>21</td>\n",
       "      <td>{'order_dow': 3, 'order_hour_of_day': 12}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2254736</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>{'order_dow': 4, 'order_hour_of_day': 7}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>431534</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "      <td>{'order_dow': 4, 'order_hour_of_day': 15}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421078</th>\n",
       "      <td>2266710</td>\n",
       "      <td>206209</td>\n",
       "      <td>10</td>\n",
       "      <td>29</td>\n",
       "      <td>{'order_dow': 5, 'order_hour_of_day': 18}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421079</th>\n",
       "      <td>1854736</td>\n",
       "      <td>206209</td>\n",
       "      <td>11</td>\n",
       "      <td>30</td>\n",
       "      <td>{'order_dow': 4, 'order_hour_of_day': 10}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421080</th>\n",
       "      <td>626363</td>\n",
       "      <td>206209</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>{'order_dow': 1, 'order_hour_of_day': 12}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421081</th>\n",
       "      <td>2977660</td>\n",
       "      <td>206209</td>\n",
       "      <td>13</td>\n",
       "      <td>7</td>\n",
       "      <td>{'order_dow': 1, 'order_hour_of_day': 12}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421082</th>\n",
       "      <td>272231</td>\n",
       "      <td>206209</td>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "      <td>{'order_dow': 6, 'order_hour_of_day': 14}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3421083 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         order_id  user_id  order_number days_since_prior_order  \\\n",
       "0         2539329        1             1                     \\N   \n",
       "1         2398795        1             2                     15   \n",
       "2          473747        1             3                     21   \n",
       "3         2254736        1             4                     29   \n",
       "4          431534        1             5                     28   \n",
       "...           ...      ...           ...                    ...   \n",
       "3421078   2266710   206209            10                     29   \n",
       "3421079   1854736   206209            11                     30   \n",
       "3421080    626363   206209            12                     18   \n",
       "3421081   2977660   206209            13                      7   \n",
       "3421082    272231   206209            14                     30   \n",
       "\n",
       "                                          datetime  \n",
       "0         {'order_dow': 2, 'order_hour_of_day': 8}  \n",
       "1         {'order_dow': 3, 'order_hour_of_day': 7}  \n",
       "2        {'order_dow': 3, 'order_hour_of_day': 12}  \n",
       "3         {'order_dow': 4, 'order_hour_of_day': 7}  \n",
       "4        {'order_dow': 4, 'order_hour_of_day': 15}  \n",
       "...                                            ...  \n",
       "3421078  {'order_dow': 5, 'order_hour_of_day': 18}  \n",
       "3421079  {'order_dow': 4, 'order_hour_of_day': 10}  \n",
       "3421080  {'order_dow': 1, 'order_hour_of_day': 12}  \n",
       "3421081  {'order_dow': 1, 'order_hour_of_day': 12}  \n",
       "3421082  {'order_dow': 6, 'order_hour_of_day': 14}  \n",
       "\n",
       "[3421083 rows x 5 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add products column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>33120</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>28985</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>9327</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>45918</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>30035</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434484</th>\n",
       "      <td>3421083</td>\n",
       "      <td>39678</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434485</th>\n",
       "      <td>3421083</td>\n",
       "      <td>11352</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434486</th>\n",
       "      <td>3421083</td>\n",
       "      <td>4600</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434487</th>\n",
       "      <td>3421083</td>\n",
       "      <td>24852</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32434488</th>\n",
       "      <td>3421083</td>\n",
       "      <td>5020</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32434489 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          order_id  product_id  add_to_cart_order  reordered\n",
       "0                2       33120                  1          1\n",
       "1                2       28985                  2          1\n",
       "2                2        9327                  3          0\n",
       "3                2       45918                  4          1\n",
       "4                2       30035                  5          0\n",
       "...            ...         ...                ...        ...\n",
       "32434484   3421083       39678                  6          1\n",
       "32434485   3421083       11352                  7          0\n",
       "32434486   3421083        4600                  8          0\n",
       "32434487   3421083       24852                  9          1\n",
       "32434488   3421083        5020                 10          1\n",
       "\n",
       "[32434489 rows x 4 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_products_prior_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "def products(x, y):\n",
    "    return {\"product_id\": x, \"reordered\": y}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "order_products_prior_df[\"products\"] = order_products_prior_df.apply(lambda x: products(x[\"product_id\"], x[\"reordered\"]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_series= order_products_prior_df.groupby('order_id').apply(lambda x: x['products'].tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_id\n",
       "2          [{'product_id': 33120, 'reordered': 1}, {'prod...\n",
       "3          [{'product_id': 33754, 'reordered': 1}, {'prod...\n",
       "4          [{'product_id': 46842, 'reordered': 0}, {'prod...\n",
       "5          [{'product_id': 13176, 'reordered': 1}, {'prod...\n",
       "6          [{'product_id': 40462, 'reordered': 0}, {'prod...\n",
       "                                 ...                        \n",
       "3421079              [{'product_id': 30136, 'reordered': 0}]\n",
       "3421080    [{'product_id': 27845, 'reordered': 1}, {'prod...\n",
       "3421081    [{'product_id': 38185, 'reordered': 0}, {'prod...\n",
       "3421082    [{'product_id': 17279, 'reordered': 1}, {'prod...\n",
       "3421083    [{'product_id': 7854, 'reordered': 0}, {'produ...\n",
       "Length: 3214874, dtype: object"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_df = grouped_series.to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_df.rename(columns={0: \"products\"}, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mapping to order_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_df[\"products\"] = orders_df.order_id.map(grouped_df.set_index('order_id').products)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>datetime</th>\n",
       "      <th>products</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2539329</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>\\N</td>\n",
       "      <td>{'order_dow': 2, 'order_hour_of_day': 8}</td>\n",
       "      <td>[{'product_id': 196, 'reordered': 0}, {'produc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2398795</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>{'order_dow': 3, 'order_hour_of_day': 7}</td>\n",
       "      <td>[{'product_id': 196, 'reordered': 1}, {'produc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>473747</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>21</td>\n",
       "      <td>{'order_dow': 3, 'order_hour_of_day': 12}</td>\n",
       "      <td>[{'product_id': 196, 'reordered': 1}, {'produc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2254736</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>{'order_dow': 4, 'order_hour_of_day': 7}</td>\n",
       "      <td>[{'product_id': 196, 'reordered': 1}, {'produc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>431534</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "      <td>{'order_dow': 4, 'order_hour_of_day': 15}</td>\n",
       "      <td>[{'product_id': 196, 'reordered': 1}, {'produc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421078</th>\n",
       "      <td>2266710</td>\n",
       "      <td>206209</td>\n",
       "      <td>10</td>\n",
       "      <td>29</td>\n",
       "      <td>{'order_dow': 5, 'order_hour_of_day': 18}</td>\n",
       "      <td>[{'product_id': 9405, 'reordered': 1}, {'produ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421079</th>\n",
       "      <td>1854736</td>\n",
       "      <td>206209</td>\n",
       "      <td>11</td>\n",
       "      <td>30</td>\n",
       "      <td>{'order_dow': 4, 'order_hour_of_day': 10}</td>\n",
       "      <td>[{'product_id': 6846, 'reordered': 1}, {'produ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421080</th>\n",
       "      <td>626363</td>\n",
       "      <td>206209</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>{'order_dow': 1, 'order_hour_of_day': 12}</td>\n",
       "      <td>[{'product_id': 6846, 'reordered': 1}, {'produ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421081</th>\n",
       "      <td>2977660</td>\n",
       "      <td>206209</td>\n",
       "      <td>13</td>\n",
       "      <td>7</td>\n",
       "      <td>{'order_dow': 1, 'order_hour_of_day': 12}</td>\n",
       "      <td>[{'product_id': 24852, 'reordered': 1}, {'prod...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421082</th>\n",
       "      <td>272231</td>\n",
       "      <td>206209</td>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "      <td>{'order_dow': 6, 'order_hour_of_day': 14}</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3421083 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         order_id  user_id  order_number days_since_prior_order  \\\n",
       "0         2539329        1             1                     \\N   \n",
       "1         2398795        1             2                     15   \n",
       "2          473747        1             3                     21   \n",
       "3         2254736        1             4                     29   \n",
       "4          431534        1             5                     28   \n",
       "...           ...      ...           ...                    ...   \n",
       "3421078   2266710   206209            10                     29   \n",
       "3421079   1854736   206209            11                     30   \n",
       "3421080    626363   206209            12                     18   \n",
       "3421081   2977660   206209            13                      7   \n",
       "3421082    272231   206209            14                     30   \n",
       "\n",
       "                                          datetime  \\\n",
       "0         {'order_dow': 2, 'order_hour_of_day': 8}   \n",
       "1         {'order_dow': 3, 'order_hour_of_day': 7}   \n",
       "2        {'order_dow': 3, 'order_hour_of_day': 12}   \n",
       "3         {'order_dow': 4, 'order_hour_of_day': 7}   \n",
       "4        {'order_dow': 4, 'order_hour_of_day': 15}   \n",
       "...                                            ...   \n",
       "3421078  {'order_dow': 5, 'order_hour_of_day': 18}   \n",
       "3421079  {'order_dow': 4, 'order_hour_of_day': 10}   \n",
       "3421080  {'order_dow': 1, 'order_hour_of_day': 12}   \n",
       "3421081  {'order_dow': 1, 'order_hour_of_day': 12}   \n",
       "3421082  {'order_dow': 6, 'order_hour_of_day': 14}   \n",
       "\n",
       "                                                  products  \n",
       "0        [{'product_id': 196, 'reordered': 0}, {'produc...  \n",
       "1        [{'product_id': 196, 'reordered': 1}, {'produc...  \n",
       "2        [{'product_id': 196, 'reordered': 1}, {'produc...  \n",
       "3        [{'product_id': 196, 'reordered': 1}, {'produc...  \n",
       "4        [{'product_id': 196, 'reordered': 1}, {'produc...  \n",
       "...                                                    ...  \n",
       "3421078  [{'product_id': 9405, 'reordered': 1}, {'produ...  \n",
       "3421079  [{'product_id': 6846, 'reordered': 1}, {'produ...  \n",
       "3421080  [{'product_id': 6846, 'reordered': 1}, {'produ...  \n",
       "3421081  [{'product_id': 24852, 'reordered': 1}, {'prod...  \n",
       "3421082                                                NaN  \n",
       "\n",
       "[3421083 rows x 6 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_df.products = orders_df.products.astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_id                   int64\n",
       "user_id                    int64\n",
       "order_number               int64\n",
       "days_since_prior_order    object\n",
       "datetime                  object\n",
       "products                  object\n",
       "dtype: object"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_df.dtypes"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Renaming columns to match Mongodb schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "products_df.rename(columns={\"product_id\": \"_id\", \"product_name\":\"name\", \"aisle_id\":\"aisle\", \"department_id\":\"department\"}, inplace=True)\n",
    "random_us_addresses_df.rename(columns={\"user_id\":\"_id\", \"user_address\":\"address\"}, inplace=True)\n",
    "orders_df.rename(columns={\"order_id\": \"_id\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "products_df.replace(\"\\\\N\", None, inplace=True)\n",
    "random_us_addresses_df.replace(\"\\\\N\", None, inplace=True)\n",
    "orders_df.replace(\"\\\\N\", None, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "products_dict = products_df.to_dict(orient='records')\n",
    "users_dict = random_us_addresses_df.to_dict(orient='records')\n",
    "orders_dict = orders_df.to_dict(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pymongo import MongoClient\n",
    "def connect_to_mongodb():\n",
    "    client = MongoClient(\"mongodb://localhost:27017\")\n",
    "    mydb = client[\"instacart\"]\n",
    "    orders = mydb[\"Orders\"]\n",
    "    products = mydb[\"Products\"]\n",
    "    users = mydb[\"Users\"]\n",
    "    return orders, products, users\n",
    "\n",
    "orders, products, users = connect_to_mongodb()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_data(data):\n",
    "    processed_data = []\n",
    "    for documents in data:\n",
    "        doc = {k: v for k, v in documents.items() if v is not None}\n",
    "        processed_data.append(doc)\n",
    "    return processed_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "products_data = process_data(products_dict)\n",
    "orders_data = process_data(orders_dict)\n",
    "users_data = process_data(users_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': 2539329,\n",
       " 'user_id': 1,\n",
       " 'order_number': 1,\n",
       " 'datetime': {'order_dow': 2, 'order_hour_of_day': 8},\n",
       " 'products': \"[{'product_id': 196, 'reordered': 0}, {'product_id': 14084, 'reordered': 0}, {'product_id': 12427, 'reordered': 0}, {'product_id': 26088, 'reordered': 0}, {'product_id': 26405, 'reordered': 0}]\"}"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "products.delete_many({})\n",
    "products.insert_many(products_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users.delete_many({})\n",
    "users.insert_many(users_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders.delete_many({})\n",
    "orders.insert_many(orders_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.10"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}