4.6 - Combining and Exporting Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2b227beb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "974980ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create path variable\n",
    "\n",
    "path = r'/Users/davidcarpenter/Documents/Instacart Basket Analysis'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3ceaf41",
   "metadata": {},
   "source": [
    "## Question 3 - In a new notebook, import the orders_products_combined dataframe from the pickle file you just saved."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "29d1398d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import the orders_products_combined.pkl file\n",
    "\n",
    "df_ords_combined = pd.read_pickle(os.path.join(path, '02 Data', 'Prepared Data', 'orders_products_combined.pkl'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c874f91",
   "metadata": {},
   "source": [
    "## Question 4 - Check the shape of the imported dataframe (it should be the same as the one you exported—always check!)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5c8f367c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(32434489, 12)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ords_combined.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d8d715be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import the ex4-5-products.csv file\n",
    "\n",
    "df_prods_cleaned_wrangled_deduped = pd.read_csv(os.path.join(path, '02 Data', 'Prepared Data', 'ex4-5-products.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "892c73f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(49693, 6)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_prods_cleaned_wrangled_deduped.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20cc2bd9",
   "metadata": {},
   "source": [
    "## Question 5 - Determine a suitable way to combine the orders_products_combined dataframe with your products data set. Make sure you’re using your wrangled, cleaned, and deduped products data set stored in your “Prepared Data” folder from the previous Exercise’s task."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "12659453",
   "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>Unnamed: 0</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",
       "      <th>product_id</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\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",
       "      <td>196</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\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",
       "      <td>14084</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\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",
       "      <td>12427</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\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",
       "      <td>26088</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\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",
       "      <td>26405</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  order_id  user_id eval_set  order_number  order_dow  \\\n",
       "0           0   2539329        1    prior             1          2   \n",
       "1           0   2539329        1    prior             1          2   \n",
       "2           0   2539329        1    prior             1          2   \n",
       "3           0   2539329        1    prior             1          2   \n",
       "4           0   2539329        1    prior             1          2   \n",
       "\n",
       "   order_hour_of_day  days_since_prior_order  product_id  add_to_cart_order  \\\n",
       "0                  8                     NaN         196                  1   \n",
       "1                  8                     NaN       14084                  2   \n",
       "2                  8                     NaN       12427                  3   \n",
       "3                  8                     NaN       26088                  4   \n",
       "4                  8                     NaN       26405                  5   \n",
       "\n",
       "   reordered _merge  \n",
       "0          0   both  \n",
       "1          0   both  \n",
       "2          0   both  \n",
       "3          0   both  \n",
       "4          0   both  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ords_combined.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "64fa3cef",
   "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>Unnamed: 0</th>\n",
       "      <th>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>prices</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>All-Seasons Salt</td>\n",
       "      <td>104</td>\n",
       "      <td>13</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Robust Golden Unsweetened Oolong Tea</td>\n",
       "      <td>94</td>\n",
       "      <td>7</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Smart Ones Classic Favorites Mini Rigatoni Wit...</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Green Chile Anytime Sauce</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  product_id                                       product_name  \\\n",
       "0           0           1                         Chocolate Sandwich Cookies   \n",
       "1           1           2                                   All-Seasons Salt   \n",
       "2           2           3               Robust Golden Unsweetened Oolong Tea   \n",
       "3           3           4  Smart Ones Classic Favorites Mini Rigatoni Wit...   \n",
       "4           4           5                          Green Chile Anytime Sauce   \n",
       "\n",
       "   aisle_id  department_id  prices  \n",
       "0        61             19     5.8  \n",
       "1       104             13     9.3  \n",
       "2        94              7     4.5  \n",
       "3        38              1    10.5  \n",
       "4         5             13     4.3  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_prods_cleaned_wrangled_deduped.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "7c2368d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "ords_prods_merge = df_prods_cleaned_wrangled_deduped.merge(df_ords_combined, on = 'product_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "faf97823",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(32434212, 17)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "9bcd28a8",
   "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>Unnamed: 0_x</th>\n",
       "      <th>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>prices</th>\n",
       "      <th>Unnamed: 0_y</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",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1987</td>\n",
       "      <td>3139998</td>\n",
       "      <td>138</td>\n",
       "      <td>prior</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1989</td>\n",
       "      <td>1977647</td>\n",
       "      <td>138</td>\n",
       "      <td>prior</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>11433</td>\n",
       "      <td>389851</td>\n",
       "      <td>709</td>\n",
       "      <td>prior</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>12198</td>\n",
       "      <td>652770</td>\n",
       "      <td>764</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>12200</td>\n",
       "      <td>1813452</td>\n",
       "      <td>764</td>\n",
       "      <td>prior</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0_x  product_id                product_name  aisle_id  \\\n",
       "0             0           1  Chocolate Sandwich Cookies        61   \n",
       "1             0           1  Chocolate Sandwich Cookies        61   \n",
       "2             0           1  Chocolate Sandwich Cookies        61   \n",
       "3             0           1  Chocolate Sandwich Cookies        61   \n",
       "4             0           1  Chocolate Sandwich Cookies        61   \n",
       "\n",
       "   department_id  prices  Unnamed: 0_y  order_id  user_id eval_set  \\\n",
       "0             19     5.8          1987   3139998      138    prior   \n",
       "1             19     5.8          1989   1977647      138    prior   \n",
       "2             19     5.8         11433    389851      709    prior   \n",
       "3             19     5.8         12198    652770      764    prior   \n",
       "4             19     5.8         12200   1813452      764    prior   \n",
       "\n",
       "   order_number  order_dow  order_hour_of_day  days_since_prior_order  \\\n",
       "0            28          6                 11                     3.0   \n",
       "1            30          6                 17                    20.0   \n",
       "2             2          0                 21                     6.0   \n",
       "3             1          3                 13                     NaN   \n",
       "4             3          4                 17                     9.0   \n",
       "\n",
       "   add_to_cart_order  reordered _merge  \n",
       "0                  5          0   both  \n",
       "1                  1          1   both  \n",
       "2                 20          0   both  \n",
       "3                 10          0   both  \n",
       "4                 11          1   both  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e75a79c7",
   "metadata": {},
   "source": [
    "## Question 6 - Confirm the results of the merge using the merge flag."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "b9449d99",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "_merge\n",
       "both          32434212\n",
       "left_only            0\n",
       "right_only           0\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge['_merge'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b60059d",
   "metadata": {},
   "source": [
    "## Question 7 - Export the newly created dataframe as ords_prods_merge in a suitable format (taking into consideration the size)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "ceb28a23",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export to pickle file, result is about 4GB\n",
    "\n",
    "ords_prods_merge.to_pickle(os.path.join(path, '02 Data','Prepared Data', 'ex4-6-orders_products_merged.pkl'))"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}