Instacart_prelim_eda.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "10d3c8a7",
   "metadata": {},
   "source": [
    "# Unpacking Instacart: A Deep Dive into North American Grocery E-Commerce Behavior\n",
    "\n",
    "## Preliminary Data Exploration Analysis\n",
    "\n",
    "The **main aim** is to get a basic feel of the data and understand the relationships inside a dataset before its uploading to SQL.\n",
    "\n",
    "**Tasks:**\n",
    "* Check for missing values and duplicates.\n",
    "* Check dimensions of the dataset.\n",
    "* Understand the type of variables present."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf31edc7",
   "metadata": {},
   "source": [
    "### Import Statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0b578850",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "541450f3",
   "metadata": {},
   "source": [
    "### Read the CSV files into a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2fc57f4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_aisles = pd.read_csv('data/aisles.csv')\n",
    "df_departments = pd.read_csv('data/departments.csv')\n",
    "df_orders_prior = pd.read_csv('data/order_products__prior.csv')\n",
    "df_orders_train = pd.read_csv('data/order_products__train.csv')\n",
    "df_orders = pd.read_csv('data/orders.csv')\n",
    "df_products = pd.read_csv('data/products.csv')\n",
    "df_sample_submission = pd.read_csv('data/sample_submission.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "127a40db",
   "metadata": {},
   "source": [
    "### Check for Missing Values and Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b5d79ffa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_aisles has duplicates: False\n",
      "df_aisles has NaN values: False\n",
      "df_departments has duplicates: False\n",
      "df_departments has NaN values: False\n",
      "df_products has duplicates: False\n",
      "df_products has NaN values: False\n",
      "df_orders has duplicates: False\n",
      "df_orders has NaN values: True\n",
      "df_orders_prior has duplicates: False\n",
      "df_orders_prior has NaN values: False\n",
      "df_orders_train has duplicates: False\n",
      "df_orders_train has NaN values: False\n",
      "df_sample_submission has duplicates: False\n",
      "df_sample_submission has NaN values: False\n"
     ]
    }
   ],
   "source": [
    "dataframes = [df_aisles, df_departments, df_products, df_orders, df_orders_prior, df_orders_train, df_sample_submission]\n",
    "\n",
    "for df in dataframes:\n",
    "    df_name = [name for name, obj in locals().items() if obj is df][0]\n",
    "    print(f'{df_name} has duplicates: {df.duplicated().values.any()}')\n",
    "    print(f'{df_name} has NaN values: {df.isna().values.any()}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55ca7b8f",
   "metadata": {},
   "source": [
    "* There are no duplicates in all dataframes.\n",
    "* However, `df_orders` dataframe has missing values values. This needs to be investigated further."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c95ab318",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "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",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Identifying column(s) with missing values\n",
    "df_orders.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c6981b08",
   "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",
       "    <tr>\n",
       "      <th>eval_set</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>prior</th>\n",
       "      <td>3214874</td>\n",
       "      <td>3214874</td>\n",
       "      <td>3214874</td>\n",
       "      <td>3214874</td>\n",
       "      <td>3214874</td>\n",
       "      <td>3008665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test</th>\n",
       "      <td>75000</td>\n",
       "      <td>75000</td>\n",
       "      <td>75000</td>\n",
       "      <td>75000</td>\n",
       "      <td>75000</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>train</th>\n",
       "      <td>131209</td>\n",
       "      <td>131209</td>\n",
       "      <td>131209</td>\n",
       "      <td>131209</td>\n",
       "      <td>131209</td>\n",
       "      <td>131209</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          order_id  user_id  order_number  order_dow  order_hour_of_day  \\\n",
       "eval_set                                                                  \n",
       "prior      3214874  3214874       3214874    3214874            3214874   \n",
       "test         75000    75000         75000      75000              75000   \n",
       "train       131209   131209        131209     131209             131209   \n",
       "\n",
       "          days_since_prior_order  \n",
       "eval_set                          \n",
       "prior                    3008665  \n",
       "test                       75000  \n",
       "train                     131209  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_orders.groupby('eval_set').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "581a37c5",
   "metadata": {},
   "source": [
    "* The only column with 206,209 NaN values is `'days_since_prior_order'` in the `'prior'` evaluation set.\n",
    "* NaN values in this column mean the first order by a user and are not connected with any issue.\n",
    "* **75,000 orders** in `'test'` data are reserved for machine learning competition. No product data in this evaluation set is expected."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dde1c76",
   "metadata": {},
   "source": [
    "### Check Dimensions of the Dataset and Data Type of Variables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "737920fe",
   "metadata": {},
   "source": [
    "### DataFrame #1: Products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2053f12f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 49688 entries, 0 to 49687\n",
      "Data columns (total 4 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   product_id     49688 non-null  int64 \n",
      " 1   product_name   49688 non-null  object\n",
      " 2   aisle_id       49688 non-null  int64 \n",
      " 3   department_id  49688 non-null  int64 \n",
      "dtypes: int64(3), object(1)\n",
      "memory usage: 1.5+ MB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                                       product_name  aisle_id  \\\n",
       "0           1                         Chocolate Sandwich Cookies        61   \n",
       "1           2                                   All-Seasons Salt       104   \n",
       "2           3               Robust Golden Unsweetened Oolong Tea        94   \n",
       "3           4  Smart Ones Classic Favorites Mini Rigatoni Wit...        38   \n",
       "4           5                          Green Chile Anytime Sauce         5   \n",
       "\n",
       "   department_id  \n",
       "0             19  \n",
       "1             13  \n",
       "2              7  \n",
       "3              1  \n",
       "4             13  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_products.info())\n",
    "df_products.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b57909e9",
   "metadata": {},
   "source": [
    "* No issues with the data type.\n",
    "* There are **49,688 products** available on the platform."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a481430",
   "metadata": {},
   "source": [
    "### DataFrame #2: Aisles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "eea05d8d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 134 entries, 0 to 133\n",
      "Data columns (total 2 columns):\n",
      " #   Column    Non-Null Count  Dtype \n",
      "---  ------    --------------  ----- \n",
      " 0   aisle_id  134 non-null    int64 \n",
      " 1   aisle     134 non-null    object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 2.2+ KB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\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"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_aisles.info())\n",
    "df_aisles.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0d13dd1",
   "metadata": {},
   "source": [
    "* No issues with the data type.\n",
    "* Products on the platform are organized into **134 aisles**."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd43d373",
   "metadata": {},
   "source": [
    "### DataFrame #3: Departments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dd804df6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 21 entries, 0 to 20\n",
      "Data columns (total 2 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   department_id  21 non-null     int64 \n",
      " 1   department     21 non-null     object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 464.0+ bytes\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </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"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_departments.info())\n",
    "df_departments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "616f5dd0",
   "metadata": {},
   "source": [
    "* No issues with the data type.\n",
    "* Aisles on the platform are grouped into **21 departments**."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d86897f1",
   "metadata": {},
   "source": [
    "### DataFrame #4: Orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7eee680c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3421083 entries, 0 to 3421082\n",
      "Data columns (total 7 columns):\n",
      " #   Column                  Dtype  \n",
      "---  ------                  -----  \n",
      " 0   order_id                int64  \n",
      " 1   user_id                 int64  \n",
      " 2   eval_set                object \n",
      " 3   order_number            int64  \n",
      " 4   order_dow               int64  \n",
      " 5   order_hour_of_day       int64  \n",
      " 6   days_since_prior_order  float64\n",
      "dtypes: float64(1), int64(5), object(1)\n",
      "memory usage: 182.7+ MB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  user_id eval_set  order_number  order_dow  order_hour_of_day  \\\n",
       "0   2539329        1    prior             1          2                  8   \n",
       "1   2398795        1    prior             2          3                  7   \n",
       "2    473747        1    prior             3          3                 12   \n",
       "3   2254736        1    prior             4          4                  7   \n",
       "4    431534        1    prior             5          4                 15   \n",
       "\n",
       "   days_since_prior_order  \n",
       "0                     NaN  \n",
       "1                    15.0  \n",
       "2                    21.0  \n",
       "3                    29.0  \n",
       "4                    28.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_orders.info())\n",
    "df_orders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "62bb6f83",
   "metadata": {
    "scrolled": true
   },
   "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>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",
       "    <tr>\n",
       "      <th>user_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>152340</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185641</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185524</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81678</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70922</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>99</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>13046</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70208</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111440</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31762</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47980</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>206209 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         order_id  eval_set  order_number  order_dow  order_hour_of_day  \\\n",
       "user_id                                                                   \n",
       "152340        100       100           100        100                100   \n",
       "185641        100       100           100        100                100   \n",
       "185524        100       100           100        100                100   \n",
       "81678         100       100           100        100                100   \n",
       "70922         100       100           100        100                100   \n",
       "...           ...       ...           ...        ...                ...   \n",
       "13046           4         4             4          4                  4   \n",
       "70208           4         4             4          4                  4   \n",
       "111440          4         4             4          4                  4   \n",
       "31762           4         4             4          4                  4   \n",
       "47980           4         4             4          4                  4   \n",
       "\n",
       "         days_since_prior_order  \n",
       "user_id                          \n",
       "152340                       99  \n",
       "185641                       99  \n",
       "185524                       99  \n",
       "81678                        99  \n",
       "70922                        99  \n",
       "...                         ...  \n",
       "13046                         3  \n",
       "70208                         3  \n",
       "111440                        3  \n",
       "31762                         3  \n",
       "47980                         3  \n",
       "\n",
       "[206209 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# User count and their orders count\n",
    "df_orders.groupby('user_id').count().sort_values('order_id', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "84efcbe3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 206209 new users. Initial orders are 6.4% of all orders.\n"
     ]
    }
   ],
   "source": [
    "# How many users in the dataset placed their initial order?\n",
    "new_users = df_orders.isna().days_since_prior_order.sum()\n",
    "\n",
    "# What proportion of the total orders are initial orders\n",
    "nu_share = new_users / df_orders.days_since_prior_order.count() * 100\n",
    "print(f'There are {new_users} new users. Initial orders are {nu_share:.2}% of all orders.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "aa542c4a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2, 3, 4, 1, 5, 0, 6])"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Unique values in `order_dow` column\n",
    "df_orders.order_dow.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cc56157",
   "metadata": {},
   "source": [
    "* No issues with the data type.\n",
    "* This file tells to which set (prior, train, test) an order belongs (based on `'eval_set'` column) and is supposed to be a central file in the dataset.\n",
    "* **3,421,083 orders** were placed on the platform.\n",
    "* Initial orders constitute 6.4% of the total orders.\n",
    "* The dataset is anonymized (`'user_id'` only) and contains grocery orders from **206,209 Instacart users**.\n",
    "    * Previously, in the `'days_since_prior_order'` column of the 'prior' evaluation set 206,209 NaN values were identified. Notably, this number matches the count of unique users. This supports the idea that these missing values represent first-time orders and there is no issue with the data.\n",
    "    * Additionally, it appears that this dataset portrays sales data about users from the start of their shopping history on the platform, wherein the new user count precisely matches the total user count of 206,209.\n",
    "* For each user, **between 4 and 100 of their orders** are provided.\n",
    "* `'order_dow'` is the day of week the order was placed on. It is represented by a number from 0 to 6. A week in the US starts from Sunday, that's why I have a hypothesis that 0 stands for Sunday, 1 for Monday, etc.\n",
    "* `'order_number'`: the order sequence number for this user (1 = first, n = nth).\n",
    "* There is no information about products sold in each order."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa75fb5e",
   "metadata": {},
   "source": [
    "### DataFrame #5: Orders Prior"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5719d8d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32434489 entries, 0 to 32434488\n",
      "Data columns (total 4 columns):\n",
      " #   Column             Dtype\n",
      "---  ------             -----\n",
      " 0   order_id           int64\n",
      " 1   product_id         int64\n",
      " 2   add_to_cart_order  int64\n",
      " 3   reordered          int64\n",
      "dtypes: int64(4)\n",
      "memory usage: 989.8 MB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\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"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_orders_prior.info())\n",
    "df_orders_prior.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c613ce7e",
   "metadata": {},
   "source": [
    "* No issues with the data type.\n",
    "* This file contains **previous order** contents for all customers and **specifies which products** were purchased in each order (there is a reference to `product_id`).\n",
    "* `'reordered'` indicates that the customer has a previous order that contains the product:\n",
    "    * 1 if this product has been ordered by this user in the past,\n",
    "    * 0 otherwise.\n",
    "* Memory usage is nearly 1GB; consider this when merging with other data frames."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b56048b4",
   "metadata": {},
   "source": [
    "### DataFrame #6: Orders Train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "aa7b27fa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1384617 entries, 0 to 1384616\n",
      "Data columns (total 4 columns):\n",
      " #   Column             Non-Null Count    Dtype\n",
      "---  ------             --------------    -----\n",
      " 0   order_id           1384617 non-null  int64\n",
      " 1   product_id         1384617 non-null  int64\n",
      " 2   add_to_cart_order  1384617 non-null  int64\n",
      " 3   reordered          1384617 non-null  int64\n",
      "dtypes: int64(4)\n",
      "memory usage: 42.3 MB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>1</td>\n",
       "      <td>49302</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>11109</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10246</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>49683</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>43633</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  product_id  add_to_cart_order  reordered\n",
       "0         1       49302                  1          1\n",
       "1         1       11109                  2          1\n",
       "2         1       10246                  3          0\n",
       "3         1       49683                  4          0\n",
       "4         1       43633                  5          1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_orders_train.info())\n",
    "df_orders_train.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4308b55",
   "metadata": {},
   "source": [
    "* No issues with the data type.\n",
    "* This file identifies **the last order for 131,209 users** (= 206,209 - 75,000) and **specifies which products** were purchased in each order (there is a reference to `product_id`).\n",
    "* `'reordered'` indicates that the customer has a previous order that contains the product:\n",
    "    * 1 if this product has been ordered by this user in the past,\n",
    "    * 0 otherwise."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78c8538d",
   "metadata": {},
   "source": [
    "### DataFrame #7: Orders Test (Sample Submission)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "53362246",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 75000 entries, 0 to 74999\n",
      "Data columns (total 2 columns):\n",
      " #   Column    Non-Null Count  Dtype \n",
      "---  ------    --------------  ----- \n",
      " 0   order_id  75000 non-null  int64 \n",
      " 1   products  75000 non-null  object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 1.1+ MB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>products</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17</td>\n",
       "      <td>39276 29259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>34</td>\n",
       "      <td>39276 29259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>137</td>\n",
       "      <td>39276 29259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>182</td>\n",
       "      <td>39276 29259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>257</td>\n",
       "      <td>39276 29259</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id     products\n",
       "0        17  39276 29259\n",
       "1        34  39276 29259\n",
       "2       137  39276 29259\n",
       "3       182  39276 29259\n",
       "4       257  39276 29259"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_sample_submission.info())\n",
    "df_sample_submission.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "918b3299",
   "metadata": {},
   "source": [
    "* No issues with the data type.\n",
    "* This file identifies **the last order for 75,000 users** and represents the 'test' set for machine learning competition.\n",
    "* `'products'` column contains an example of a space-delimited list of product_ids for that order. Thus, for further data analysis purporses this file will be out of scope."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56dffb2d",
   "metadata": {},
   "source": [
    "## Findings & Conclusions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2441d531",
   "metadata": {},
   "source": [
    "#### Dataset structure\n",
    "* The dataset is a **relational set of 7 files** describing customers' orders over time.\n",
    "* Three of them (`departments.csv`, `aisles.csv`, `products.csv`) contain product related information only.\n",
    "* The other four contain information about users' orders:\n",
    "    * `orders.csv` is the central file with an information about all orders made on the platform, but without product specifics.\n",
    "        * Note: Memory usage of this file is nearly 1GB; consider this when merging with other data frames.\n",
    "    * `order_products__prior.csv` and `order_products__prior.csv` specifies which products were purchased in each order.\n",
    "    * `sample_submission.csv` contains test data (order IDs) reserved for machine learning competitions. This file identifies **the last order for 75,000 users**. For further data analysis purporses this file will be **out of scope**.\n",
    "    * `order_products__train.csv` and `sample_submission.csv` (test) identify the last order for every user.\n",
    "\n",
    "#### Data cleaning is not needed:\n",
    "* no data type issues were identified,\n",
    "* no duplicated values,\n",
    "* no unexpected missing values,\n",
    "* data in all files looks logically neat.\n",
    "\n",
    "#### Dataset dimensions overview\n",
    "\n",
    "* **Orders & users**:\n",
    "    * The dataset is anonymized and contains a sample of over **3 million (3,421,083) grocery orders** from more than **200,000 (206,209) Instacart users**.\n",
    "    * For each user, provided **between 4 and 100 of their orders**, with the sequence of products purchased in each order.\n",
    "* **Product hierarchy**:\n",
    "    * There are **49,688 products** available on the platform.\n",
    "    * They are organized into **134 aisles** and **21 departments**.\n",
    "\n",
    "* **Other**: \n",
    "    * The day of week and hour of day the order was placed, and a relative measure of time between orders, are provided.\n",
    "    * The day of week when the order was placed on is represented by a number from 0 to 6. There is a hypothesis that 0 stands for Sunday, 1 for Monday, etc. (based on the USA calendar tradition)."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}