DB_Processing.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: swifter in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (1.3.5)\n",
      "Requirement already satisfied: cloudpickle>=0.2.2 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from swifter) (2.2.1)\n",
      "Requirement already satisfied: pandas>=1.0.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from swifter) (1.5.3)\n",
      "Requirement already satisfied: psutil>=5.6.6 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from swifter) (5.9.5)\n",
      "Requirement already satisfied: parso>0.4.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from swifter) (0.8.3)\n",
      "Requirement already satisfied: bleach>=3.1.1 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from swifter) (4.1.0)\n",
      "Requirement already satisfied: ipywidgets>=7.0.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from swifter) (8.0.4)\n",
      "Requirement already satisfied: tqdm>=4.33.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from swifter) (4.65.0)\n",
      "Requirement already satisfied: dask[dataframe]>=2.10.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from swifter) (2023.4.1)\n",
      "Requirement already satisfied: webencodings in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from bleach>=3.1.1->swifter) (0.5.1)\n",
      "Requirement already satisfied: packaging in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from bleach>=3.1.1->swifter) (23.1)\n",
      "Requirement already satisfied: six>=1.9.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from bleach>=3.1.1->swifter) (1.16.0)\n",
      "Requirement already satisfied: pyyaml>=5.3.1 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from dask[dataframe]>=2.10.0->swifter) (6.0)\n",
      "Requirement already satisfied: click>=8.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from dask[dataframe]>=2.10.0->swifter) (8.1.3)\n",
      "Requirement already satisfied: fsspec>=2021.09.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from dask[dataframe]>=2.10.0->swifter) (2023.4.0)\n",
      "Requirement already satisfied: partd>=1.2.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from dask[dataframe]>=2.10.0->swifter) (1.2.0)\n",
      "Requirement already satisfied: importlib-metadata>=4.13.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from dask[dataframe]>=2.10.0->swifter) (6.6.0)\n",
      "Requirement already satisfied: toolz>=0.10.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from dask[dataframe]>=2.10.0->swifter) (0.12.0)\n",
      "Requirement already satisfied: numpy>=1.21 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from dask[dataframe]>=2.10.0->swifter) (1.24.3)\n",
      "Requirement already satisfied: ipython>=6.1.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipywidgets>=7.0.0->swifter) (8.12.0)\n",
      "Requirement already satisfied: traitlets>=4.3.1 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipywidgets>=7.0.0->swifter) (5.7.1)\n",
      "Requirement already satisfied: widgetsnbextension~=4.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipywidgets>=7.0.0->swifter) (4.0.5)\n",
      "Requirement already satisfied: ipykernel>=4.5.1 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipywidgets>=7.0.0->swifter) (6.19.2)\n",
      "Requirement already satisfied: jupyterlab-widgets~=3.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipywidgets>=7.0.0->swifter) (3.0.5)\n",
      "Requirement already satisfied: python-dateutil>=2.8.1 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from pandas>=1.0.0->swifter) (2.8.2)\n",
      "Requirement already satisfied: pytz>=2020.1 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from pandas>=1.0.0->swifter) (2023.3)\n",
      "Requirement already satisfied: zipp>=0.5 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from importlib-metadata>=4.13.0->dask[dataframe]>=2.10.0->swifter) (3.11.0)\n",
      "Requirement already satisfied: comm>=0.1.1 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (0.1.2)\n",
      "Requirement already satisfied: jupyter-client>=6.1.12 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (8.1.0)\n",
      "Requirement already satisfied: matplotlib-inline>=0.1 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (0.1.6)\n",
      "Requirement already satisfied: debugpy>=1.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (1.5.1)\n",
      "Requirement already satisfied: appnope in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (0.1.2)\n",
      "Requirement already satisfied: pyzmq>=17 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (25.0.2)\n",
      "Requirement already satisfied: tornado>=6.1 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (6.2)\n",
      "Requirement already satisfied: nest-asyncio in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (1.5.6)\n",
      "Requirement already satisfied: typing-extensions in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (4.5.0)\n",
      "Requirement already satisfied: pexpect>4.3 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (4.8.0)\n",
      "Requirement already satisfied: pickleshare in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (0.7.5)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.37,<3.1.0,>=3.0.30 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (3.0.36)\n",
      "Requirement already satisfied: backcall in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (0.2.0)\n",
      "Requirement already satisfied: decorator in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (5.1.1)\n",
      "Requirement already satisfied: jedi>=0.16 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (0.18.1)\n",
      "Requirement already satisfied: stack-data in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (0.2.0)\n",
      "Requirement already satisfied: pygments>=2.4.0 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (2.15.1)\n",
      "Requirement already satisfied: locket in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from partd>=1.2.0->dask[dataframe]>=2.10.0->swifter) (1.0.0)\n",
      "Requirement already satisfied: jupyter-core!=5.0.*,>=4.12 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from jupyter-client>=6.1.12->ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (5.3.0)\n",
      "Requirement already satisfied: ptyprocess>=0.5 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from pexpect>4.3->ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (0.7.0)\n",
      "Requirement already satisfied: wcwidth in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from prompt-toolkit!=3.0.37,<3.1.0,>=3.0.30->ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (0.2.5)\n",
      "Requirement already satisfied: executing in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from stack-data->ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (0.8.3)\n",
      "Requirement already satisfied: asttokens in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from stack-data->ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (2.0.5)\n",
      "Requirement already satisfied: pure-eval in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from stack-data->ipython>=6.1.0->ipywidgets>=7.0.0->swifter) (0.2.2)\n",
      "Requirement already satisfied: platformdirs>=2.5 in /Users/jamesmarcogliese/opt/anaconda3/lib/python3.9/site-packages (from jupyter-core!=5.0.*,>=4.12->jupyter-client>=6.1.12->ipykernel>=4.5.1->ipywidgets>=7.0.0->swifter) (3.5.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install swifter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import swifter\n",
    "import numpy as np"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CulinaryDB"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read Culinary DB Data\n",
    "The database consists of 4 csv (comma separated values) files, provided as a bundle in a .zip file.\n",
    "\n",
    "> https://cosylab.iiitd.edu.in/culinarydb/\n",
    "\n",
    "* **01_Recipe_Details.csv**: Details of all recipes in the database. Structure: A unique 'Recipe ID', 'Recipe Title', 'Source' (from which it was fetched), and the 'Regional Cuisine' to which it belongs.\n",
    "\n",
    "* **02_Ingredients.csv**: List of all ingredients present in the database. Structure: 'Name of Aliased Ingredient', its 'Synonyms', a unique 'Entity ID' and the 'Category' to which the ingredient belongs.\n",
    "   Note 1: Synonyms were used to alias ingredients from recipes.\n",
    "   Note 2: Certain ingredients' names have been suffixed & preffixed with \"#\" qualifier. This has done to prevent them from participating in the aliasing process as they've been included in/as one of the 'Compound Ingredients'.\n",
    "   Note 3: Certain ingredients' names have \"=\" wildcard. It signifies a possible space-character in its position.\n",
    "\n",
    "* **03_Compound_Ingredients.csv**: Description of \"Compound Ingredients\". Structure: name of the 'Compound Ingredient', its 'Synonyms', a unique 'Entity ID', 'Components' that constitute a compound ingredient and the 'Category' of each compound ingredient.\n",
    "\n",
    "* **04_Recipe-Ingredients_Aliases.csv**: Enumerates the mapping of each ingredient of each recipe to one of the entities in our database. Structure: A 'Recipe ID', 'Original Ingredient Name' (as in the recipe), 'Aliased Ingredient Name (from FlavorDB or Compound Ingredient)' and its 'Entity ID'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "recipe_details_path = \"CulinaryDB/01_Recipe_Details.csv\"\n",
    "ingredients_path = \"CulinaryDB/02_Ingredients.csv\"\n",
    "compound_ingredients_path = \"CulinaryDB/03_Compound_Ingredients.csv\"\n",
    "recipe_ingredients_aliases_path = \"CulinaryDB/04_Recipe-Ingredients_Aliases.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Recipe ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Source</th>\n",
       "      <th>Cuisine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>aachar aaloo</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>aadu lassan keri nu athanu</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>aaloo kofta</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>aaloo tamatar subzi</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Recipe ID                         Title       Source              Cuisine\n",
       "0          1  5 spice vegetable fried rice  TARLA_DALAL  Indian Subcontinent\n",
       "1          2                  aachar aaloo  TARLA_DALAL  Indian Subcontinent\n",
       "2          3    aadu lassan keri nu athanu  TARLA_DALAL  Indian Subcontinent\n",
       "3          4                   aaloo kofta  TARLA_DALAL  Indian Subcontinent\n",
       "4          5           aaloo tamatar subzi  TARLA_DALAL  Indian Subcontinent"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipe_details_df = pd.read_csv(recipe_details_path)\n",
    "recipe_details_df.head()"
   ]
  },
  {
   "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>Aliased Ingredient Name</th>\n",
       "      <th>Ingredient Synonyms</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Egg</td>\n",
       "      <td>egg</td>\n",
       "      <td>0</td>\n",
       "      <td>Meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bread</td>\n",
       "      <td>bread; bun</td>\n",
       "      <td>2</td>\n",
       "      <td>Bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rye Bread</td>\n",
       "      <td>bread-rye</td>\n",
       "      <td>3</td>\n",
       "      <td>Bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wheaten Bread</td>\n",
       "      <td>bread-wheaten</td>\n",
       "      <td>4</td>\n",
       "      <td>Bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>White Bread</td>\n",
       "      <td>bread-white; baguette</td>\n",
       "      <td>5</td>\n",
       "      <td>Bakery</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Aliased Ingredient Name    Ingredient Synonyms  Entity ID Category\n",
       "0                     Egg                    egg          0     Meat\n",
       "1                   Bread             bread; bun          2   Bakery\n",
       "2               Rye Bread              bread-rye          3   Bakery\n",
       "3           Wheaten Bread          bread-wheaten          4   Bakery\n",
       "4             White Bread  bread-white; baguette          5   Bakery"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ingredients_df = pd.read_csv(ingredients_path)\n",
    "ingredients_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Compound Ingredient Name</th>\n",
       "      <th>Compound Ingredient Synonyms</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>Contituent Ingredients</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Garam Masala</td>\n",
       "      <td>garam masala</td>\n",
       "      <td>2000</td>\n",
       "      <td>black pepper, mace, cinnamon, clove, cardamom,...</td>\n",
       "      <td>Spice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ginger Garlic Paste</td>\n",
       "      <td>ginger garlic paste</td>\n",
       "      <td>2001</td>\n",
       "      <td>ginger, garlic</td>\n",
       "      <td>Spice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Coriander Cumin Seed Powder</td>\n",
       "      <td>coriander cumin seed powder</td>\n",
       "      <td>2002</td>\n",
       "      <td>coriander, cumin</td>\n",
       "      <td>Spice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chaat Masala</td>\n",
       "      <td>chaat masala</td>\n",
       "      <td>2003</td>\n",
       "      <td>asafoetida, mango, black salt, cayenne, garlic...</td>\n",
       "      <td>Spice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sambar Powder</td>\n",
       "      <td>sambar powder</td>\n",
       "      <td>2004</td>\n",
       "      <td>pigeon pea, coriander, chickpea, cumin, black ...</td>\n",
       "      <td>Spice</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Compound Ingredient Name Compound Ingredient Synonyms  Entity ID  \\\n",
       "0                 Garam Masala                 garam masala       2000   \n",
       "1          Ginger Garlic Paste          ginger garlic paste       2001   \n",
       "2  Coriander Cumin Seed Powder  coriander cumin seed powder       2002   \n",
       "3                 Chaat Masala                 chaat masala       2003   \n",
       "4                Sambar Powder                sambar powder       2004   \n",
       "\n",
       "                              Contituent Ingredients Category  \n",
       "0  black pepper, mace, cinnamon, clove, cardamom,...    Spice  \n",
       "1                                     ginger, garlic    Spice  \n",
       "2                                   coriander, cumin    Spice  \n",
       "3  asafoetida, mango, black salt, cayenne, garlic...    Spice  \n",
       "4  pigeon pea, coriander, chickpea, cumin, black ...    Spice  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compound_ingredients_df = pd.read_csv(compound_ingredients_path)\n",
    "compound_ingredients_df = compound_ingredients_df.rename({'entity_id': 'Entity ID'}, axis=1)\n",
    "compound_ingredients_df.head()"
   ]
  },
  {
   "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>Recipe ID</th>\n",
       "      <th>Original Ingredient Name</th>\n",
       "      <th>Aliased Ingredient Name</th>\n",
       "      <th>Entity ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>capsicum</td>\n",
       "      <td>capsicum</td>\n",
       "      <td>362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>green bell pepper</td>\n",
       "      <td>pepper bell</td>\n",
       "      <td>362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>soy sauce</td>\n",
       "      <td>soy sauce</td>\n",
       "      <td>291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>sunflower oil</td>\n",
       "      <td>sunflower</td>\n",
       "      <td>426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>buttermilk</td>\n",
       "      <td>buttermilk</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Recipe ID Original Ingredient Name Aliased Ingredient Name  Entity ID\n",
       "0          1                 capsicum               capsicum         362\n",
       "1          1        green bell pepper            pepper bell         362\n",
       "2          1                soy sauce              soy sauce         291\n",
       "3          1            sunflower oil              sunflower         426\n",
       "4          2               buttermilk             buttermilk          61"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipe_ingredients_aliases_df = pd.read_csv(recipe_ingredients_aliases_path)\n",
    "recipe_ingredients_aliases_df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Preprocessing"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Remove Misc Cuisines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipe_details_df['Cuisine'].unique().tolist()\n",
    "recipe_details_df = recipe_details_df[~recipe_details_df['Cuisine'].str.startswith('Misc.:')]\n",
    "len(recipe_details_df['Cuisine'].unique().tolist())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Denormalize CulinaryDB tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>Recipe ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Source</th>\n",
       "      <th>Cuisine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>aachar aaloo</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>aadu lassan keri nu athanu</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>aaloo kofta</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>aaloo tamatar subzi</td>\n",
       "      <td>TARLA_DALAL</td>\n",
       "      <td>Indian Subcontinent</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>45767</th>\n",
       "      <td>45768</td>\n",
       "      <td>Strawberries &amp; Cream Frappé</td>\n",
       "      <td>EPICURIOUS</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45768</th>\n",
       "      <td>45769</td>\n",
       "      <td>Chocolate Whiskey Bundt Cake</td>\n",
       "      <td>EPICURIOUS</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45769</th>\n",
       "      <td>45770</td>\n",
       "      <td>Wild Rice with Butternut Squash, Leeks, and Corn</td>\n",
       "      <td>EPICURIOUS</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45770</th>\n",
       "      <td>45771</td>\n",
       "      <td>Fruit Crumble</td>\n",
       "      <td>EPICURIOUS</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45771</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>EPICURIOUS</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>45565 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Recipe ID                                             Title  \\\n",
       "0              1                      5 spice vegetable fried rice   \n",
       "1              2                                      aachar aaloo   \n",
       "2              3                        aadu lassan keri nu athanu   \n",
       "3              4                                       aaloo kofta   \n",
       "4              5                               aaloo tamatar subzi   \n",
       "...          ...                                               ...   \n",
       "45767      45768                       Strawberries & Cream Frappé   \n",
       "45768      45769                      Chocolate Whiskey Bundt Cake   \n",
       "45769      45770  Wild Rice with Butternut Squash, Leeks, and Corn   \n",
       "45770      45771                                     Fruit Crumble   \n",
       "45771      45772                       Enlightened Chicken Pot Pie   \n",
       "\n",
       "            Source              Cuisine  \n",
       "0      TARLA_DALAL  Indian Subcontinent  \n",
       "1      TARLA_DALAL  Indian Subcontinent  \n",
       "2      TARLA_DALAL  Indian Subcontinent  \n",
       "3      TARLA_DALAL  Indian Subcontinent  \n",
       "4      TARLA_DALAL  Indian Subcontinent  \n",
       "...            ...                  ...  \n",
       "45767   EPICURIOUS                  USA  \n",
       "45768   EPICURIOUS                  USA  \n",
       "45769   EPICURIOUS                  USA  \n",
       "45770   EPICURIOUS                  USA  \n",
       "45771   EPICURIOUS                  USA  \n",
       "\n",
       "[45565 rows x 4 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipe_details_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>Recipe ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Cuisine</th>\n",
       "      <th>Aliased Ingredient Name</th>\n",
       "      <th>Entity ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>capsicum</td>\n",
       "      <td>362.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>pepper bell</td>\n",
       "      <td>362.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>soy sauce</td>\n",
       "      <td>291.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>sunflower</td>\n",
       "      <td>426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>aachar aaloo</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>buttermilk</td>\n",
       "      <td>61.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454161</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>salt</td>\n",
       "      <td>778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454162</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>squash yellow</td>\n",
       "      <td>493.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454163</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>thyme</td>\n",
       "      <td>269.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454164</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>worcestershire sauce</td>\n",
       "      <td>2031.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454165</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>zucchini</td>\n",
       "      <td>969.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>454166 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Recipe ID                         Title              Cuisine  \\\n",
       "0               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "1               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "2               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "3               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "4               2                  aachar aaloo  Indian Subcontinent   \n",
       "...           ...                           ...                  ...   \n",
       "454161      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454162      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454163      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454164      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454165      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "\n",
       "       Aliased Ingredient Name  Entity ID  \n",
       "0                    capsicum       362.0  \n",
       "1                 pepper bell       362.0  \n",
       "2                   soy sauce       291.0  \n",
       "3                   sunflower       426.0  \n",
       "4                  buttermilk        61.0  \n",
       "...                        ...        ...  \n",
       "454161                   salt       778.0  \n",
       "454162          squash yellow       493.0  \n",
       "454163                  thyme       269.0  \n",
       "454164   worcestershire sauce      2031.0  \n",
       "454165               zucchini       969.0  \n",
       "\n",
       "[454166 rows x 5 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_recipe_df = recipe_details_df.merge(recipe_ingredients_aliases_df, on='Recipe ID', how='left').drop([\"Source\", \"Original Ingredient Name\"], axis=1)\n",
    "#master_recipe_df[master_recipe_df['Entity ID'] == 362.0]\n",
    "master_recipe_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>Recipe ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Cuisine</th>\n",
       "      <th>Aliased Ingredient Name_x</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>capsicum</td>\n",
       "      <td>362.0</td>\n",
       "      <td>Vegetable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>pepper bell</td>\n",
       "      <td>362.0</td>\n",
       "      <td>Vegetable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>soy sauce</td>\n",
       "      <td>291.0</td>\n",
       "      <td>Plant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>sunflower</td>\n",
       "      <td>426.0</td>\n",
       "      <td>Flower</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>aachar aaloo</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>buttermilk</td>\n",
       "      <td>61.0</td>\n",
       "      <td>Dairy</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>454161</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>salt</td>\n",
       "      <td>778.0</td>\n",
       "      <td>Additive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454162</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>squash yellow</td>\n",
       "      <td>493.0</td>\n",
       "      <td>Fruit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454163</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>thyme</td>\n",
       "      <td>269.0</td>\n",
       "      <td>Herb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454164</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>worcestershire sauce</td>\n",
       "      <td>2031.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454165</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>zucchini</td>\n",
       "      <td>969.0</td>\n",
       "      <td>Vegetable</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>454166 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Recipe ID                         Title              Cuisine  \\\n",
       "0               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "1               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "2               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "3               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "4               2                  aachar aaloo  Indian Subcontinent   \n",
       "...           ...                           ...                  ...   \n",
       "454161      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454162      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454163      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454164      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454165      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "\n",
       "       Aliased Ingredient Name_x  Entity ID   Category  \n",
       "0                      capsicum       362.0  Vegetable  \n",
       "1                   pepper bell       362.0  Vegetable  \n",
       "2                     soy sauce       291.0      Plant  \n",
       "3                     sunflower       426.0     Flower  \n",
       "4                    buttermilk        61.0      Dairy  \n",
       "...                          ...        ...        ...  \n",
       "454161                     salt       778.0   Additive  \n",
       "454162            squash yellow       493.0      Fruit  \n",
       "454163                    thyme       269.0       Herb  \n",
       "454164     worcestershire sauce      2031.0        NaN  \n",
       "454165                 zucchini       969.0  Vegetable  \n",
       "\n",
       "[454166 rows x 6 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_recipe_df = master_recipe_df.merge(ingredients_df, on='Entity ID', how='left').drop([\"Aliased Ingredient Name_y\", \"Ingredient Synonyms\"], axis=1)\n",
    "master_recipe_df"
   ]
  },
  {
   "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>Recipe ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Cuisine</th>\n",
       "      <th>Aliased Ingredient Name</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>capsicum</td>\n",
       "      <td>362</td>\n",
       "      <td>Vegetable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>pepper bell</td>\n",
       "      <td>362</td>\n",
       "      <td>Vegetable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>soy sauce</td>\n",
       "      <td>291</td>\n",
       "      <td>Plant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>sunflower</td>\n",
       "      <td>426</td>\n",
       "      <td>Flower</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>aachar aaloo</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>buttermilk</td>\n",
       "      <td>61</td>\n",
       "      <td>Dairy</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>454161</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>salt</td>\n",
       "      <td>778</td>\n",
       "      <td>Additive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454162</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>squash yellow</td>\n",
       "      <td>493</td>\n",
       "      <td>Fruit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454163</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>thyme</td>\n",
       "      <td>269</td>\n",
       "      <td>Herb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454164</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>worcestershire sauce</td>\n",
       "      <td>2031</td>\n",
       "      <td>Dish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454165</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>zucchini</td>\n",
       "      <td>969</td>\n",
       "      <td>Vegetable</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>454166 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Recipe ID                         Title              Cuisine  \\\n",
       "0               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "1               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "2               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "3               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "4               2                  aachar aaloo  Indian Subcontinent   \n",
       "...           ...                           ...                  ...   \n",
       "454161      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454162      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454163      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454164      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454165      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "\n",
       "       Aliased Ingredient Name  Entity ID   Category  \n",
       "0                    capsicum         362  Vegetable  \n",
       "1                 pepper bell         362  Vegetable  \n",
       "2                   soy sauce         291      Plant  \n",
       "3                   sunflower         426     Flower  \n",
       "4                  buttermilk          61      Dairy  \n",
       "...                        ...        ...        ...  \n",
       "454161                   salt         778   Additive  \n",
       "454162          squash yellow         493      Fruit  \n",
       "454163                  thyme         269       Herb  \n",
       "454164   worcestershire sauce        2031       Dish  \n",
       "454165               zucchini         969  Vegetable  \n",
       "\n",
       "[454166 rows x 6 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge 'master_recipe_df' with 'compound_ingredients_df' on 'Entity ID' using a left join and drop unnecessary columns.\n",
    "master_recipe_df = master_recipe_df.merge(compound_ingredients_df, on='Entity ID', how='left').drop([\"Compound Ingredient Name\", \"Compound Ingredient Synonyms\", \"Contituent Ingredients\"], axis=1)\n",
    "\n",
    "# Combine the 'Category_x' and 'Category_y' columns into a new 'Category' column, filling missing values from 'Category_x'.\n",
    "master_recipe_df[\"Category\"] = np.where(master_recipe_df[\"Category_x\"].isnull(), master_recipe_df[\"Category_y\"], master_recipe_df[\"Category_x\"])\n",
    "\n",
    "# Drop the original 'Category_x' and 'Category_y' columns, as they are no longer needed.\n",
    "master_recipe_df.drop([\"Category_x\", \"Category_y\"], axis=1, inplace=True)\n",
    "\n",
    "# Rename the 'Aliased Ingredient Name_x' column to 'Aliased Ingredient Name'.\n",
    "master_recipe_df = master_recipe_df.rename({'Aliased Ingredient Name_x': 'Aliased Ingredient Name'}, axis=1)\n",
    "\n",
    "# Convert the 'Entity ID' column to Int64 type to handle nullable integer values.\n",
    "master_recipe_df['Entity ID'] = master_recipe_df['Entity ID'].astype('Int64')"
   ]
  },
  {
   "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>Recipe ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Cuisine</th>\n",
       "      <th>Aliased Ingredient Name</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6954</th>\n",
       "      <td>1087</td>\n",
       "      <td>khus ka sherbat</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100682</th>\n",
       "      <td>11281</td>\n",
       "      <td>Italian Beef for Sandwiches</td>\n",
       "      <td>Italy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149791</th>\n",
       "      <td>16626</td>\n",
       "      <td>Tennessee Tea</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170994</th>\n",
       "      <td>18909</td>\n",
       "      <td>prodtest</td>\n",
       "      <td>Africa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207804</th>\n",
       "      <td>21919</td>\n",
       "      <td>Decorating with Fondant</td>\n",
       "      <td>France</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226300</th>\n",
       "      <td>23711</td>\n",
       "      <td>Crispy Pepperoni Chips</td>\n",
       "      <td>Italy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250296</th>\n",
       "      <td>25949</td>\n",
       "      <td>Recipes are temporarily unavailable. Please ch...</td>\n",
       "      <td>British Isles</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252963</th>\n",
       "      <td>26187</td>\n",
       "      <td>Calimocho</td>\n",
       "      <td>Spain</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261220</th>\n",
       "      <td>26914</td>\n",
       "      <td>Watermelon Ice Cubes</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272695</th>\n",
       "      <td>27956</td>\n",
       "      <td>Chili Rubbed Rotisserie Prime Rib</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>274473</th>\n",
       "      <td>28123</td>\n",
       "      <td>Big Al's Boiled Crawfish</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275204</th>\n",
       "      <td>28198</td>\n",
       "      <td>Creamed Corn Succotash with Cotija</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277846</th>\n",
       "      <td>28448</td>\n",
       "      <td>Maple-Mustard-Mixed Pepper Glazed Salmon with ...</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282586</th>\n",
       "      <td>28876</td>\n",
       "      <td>Microwave Steamed Baby Bok Choy</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295443</th>\n",
       "      <td>30047</td>\n",
       "      <td>Zinfandel Poached Pears</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308727</th>\n",
       "      <td>31284</td>\n",
       "      <td>Sorbet Shots</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330292</th>\n",
       "      <td>33292</td>\n",
       "      <td>Lollipop Pots</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345543</th>\n",
       "      <td>34668</td>\n",
       "      <td>Jalapeno Bites</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353583</th>\n",
       "      <td>35384</td>\n",
       "      <td>Phrik Phon Khua (Toasted-Chile Powder)</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365146</th>\n",
       "      <td>36455</td>\n",
       "      <td>Steamed Japanese Rice</td>\n",
       "      <td>Japan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366071</th>\n",
       "      <td>36560</td>\n",
       "      <td>To Cook Dried Udon Noodles</td>\n",
       "      <td>Japan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390049</th>\n",
       "      <td>39059</td>\n",
       "      <td>Beaujolais Kir</td>\n",
       "      <td>France</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451269</th>\n",
       "      <td>45478</td>\n",
       "      <td>Havarti and Roasted Garlic on Ciabatta</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Recipe ID                                              Title  \\\n",
       "6954         1087                                    khus ka sherbat   \n",
       "100682      11281                        Italian Beef for Sandwiches   \n",
       "149791      16626                                      Tennessee Tea   \n",
       "170994      18909                                           prodtest   \n",
       "207804      21919                            Decorating with Fondant   \n",
       "226300      23711                             Crispy Pepperoni Chips   \n",
       "250296      25949  Recipes are temporarily unavailable. Please ch...   \n",
       "252963      26187                                          Calimocho   \n",
       "261220      26914                               Watermelon Ice Cubes   \n",
       "272695      27956                  Chili Rubbed Rotisserie Prime Rib   \n",
       "274473      28123                           Big Al's Boiled Crawfish   \n",
       "275204      28198                 Creamed Corn Succotash with Cotija   \n",
       "277846      28448  Maple-Mustard-Mixed Pepper Glazed Salmon with ...   \n",
       "282586      28876                    Microwave Steamed Baby Bok Choy   \n",
       "295443      30047                            Zinfandel Poached Pears   \n",
       "308727      31284                                       Sorbet Shots   \n",
       "330292      33292                                      Lollipop Pots   \n",
       "345543      34668                                     Jalapeno Bites   \n",
       "353583      35384             Phrik Phon Khua (Toasted-Chile Powder)   \n",
       "365146      36455                              Steamed Japanese Rice   \n",
       "366071      36560                         To Cook Dried Udon Noodles   \n",
       "390049      39059                                     Beaujolais Kir   \n",
       "451269      45478             Havarti and Roasted Garlic on Ciabatta   \n",
       "\n",
       "                    Cuisine Aliased Ingredient Name  Entity ID Category  \n",
       "6954    Indian Subcontinent                     NaN       <NA>      NaN  \n",
       "100682                Italy                     NaN       <NA>      NaN  \n",
       "149791                  USA                     NaN       <NA>      NaN  \n",
       "170994               Africa                     NaN       <NA>      NaN  \n",
       "207804               France                     NaN       <NA>      NaN  \n",
       "226300                Italy                     NaN       <NA>      NaN  \n",
       "250296        British Isles                     NaN       <NA>      NaN  \n",
       "252963                Spain                     NaN       <NA>      NaN  \n",
       "261220                  USA                     NaN       <NA>      NaN  \n",
       "272695                  USA                     NaN       <NA>      NaN  \n",
       "274473                  USA                     NaN       <NA>      NaN  \n",
       "275204                  USA                     NaN       <NA>      NaN  \n",
       "277846                  USA                     NaN       <NA>      NaN  \n",
       "282586                  USA                     NaN       <NA>      NaN  \n",
       "295443                  USA                     NaN       <NA>      NaN  \n",
       "308727                  USA                     NaN       <NA>      NaN  \n",
       "330292                  USA                     NaN       <NA>      NaN  \n",
       "345543                  USA                     NaN       <NA>      NaN  \n",
       "353583             Thailand                     NaN       <NA>      NaN  \n",
       "365146                Japan                     NaN       <NA>      NaN  \n",
       "366071                Japan                     NaN       <NA>      NaN  \n",
       "390049               France                     NaN       <NA>      NaN  \n",
       "451269                  USA                     NaN       <NA>      NaN  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_recipe_df[master_recipe_df.isna().any(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>Recipe ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Cuisine</th>\n",
       "      <th>Aliased Ingredient Name</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>capsicum</td>\n",
       "      <td>362</td>\n",
       "      <td>Vegetable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>pepper bell</td>\n",
       "      <td>362</td>\n",
       "      <td>Vegetable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>soy sauce</td>\n",
       "      <td>291</td>\n",
       "      <td>Plant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>sunflower</td>\n",
       "      <td>426</td>\n",
       "      <td>Flower</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>aachar aaloo</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>buttermilk</td>\n",
       "      <td>61</td>\n",
       "      <td>Dairy</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>454161</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>salt</td>\n",
       "      <td>778</td>\n",
       "      <td>Additive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454162</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>squash yellow</td>\n",
       "      <td>493</td>\n",
       "      <td>Fruit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454163</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>thyme</td>\n",
       "      <td>269</td>\n",
       "      <td>Herb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454164</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>worcestershire sauce</td>\n",
       "      <td>2031</td>\n",
       "      <td>Dish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454165</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>zucchini</td>\n",
       "      <td>969</td>\n",
       "      <td>Vegetable</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>454143 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Recipe ID                         Title              Cuisine  \\\n",
       "0               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "1               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "2               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "3               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "4               2                  aachar aaloo  Indian Subcontinent   \n",
       "...           ...                           ...                  ...   \n",
       "454161      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454162      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454163      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454164      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454165      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "\n",
       "       Aliased Ingredient Name  Entity ID   Category  \n",
       "0                    capsicum         362  Vegetable  \n",
       "1                 pepper bell         362  Vegetable  \n",
       "2                   soy sauce         291      Plant  \n",
       "3                   sunflower         426     Flower  \n",
       "4                  buttermilk          61      Dairy  \n",
       "...                        ...        ...        ...  \n",
       "454161                   salt         778   Additive  \n",
       "454162          squash yellow         493      Fruit  \n",
       "454163                  thyme         269       Herb  \n",
       "454164   worcestershire sauce        2031       Dish  \n",
       "454165               zucchini         969  Vegetable  \n",
       "\n",
       "[454143 rows x 6 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_recipe_df = master_recipe_df.dropna()\n",
    "master_recipe_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/tk/3mpzsdc17flggsprn40wq3380000gn/T/ipykernel_75335/3043333264.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  master_recipe_df[\"Aliased Ingredient Name\"] = master_recipe_df[\"Aliased Ingredient Name\"].str.strip()\n"
     ]
    }
   ],
   "source": [
    "master_recipe_df[\"Aliased Ingredient Name\"] = master_recipe_df[\"Aliased Ingredient Name\"].str.strip()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1825116"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipe_ingredients_aliases_df.size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2724858"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_recipe_df.size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_recipe_df.isnull().values.any()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## FlavorDB"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Acquiring Data\n",
    "Scraping the data from FlavorDB\n",
    "> https://vchoo.github.io/\n",
    "\n",
    "The general steps to data scraping are:\n",
    "\n",
    "1. Download the JSON files.\n",
    "2. Process the JSON data.\n",
    "3. Clean the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for basic data science\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import math\n",
    "import os\n",
    "import json"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Download JSON files"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "import os\n",
    "import requests\n",
    "import json\n",
    "\n",
    "# Get the current working directory\n",
    "cwd_dir = os.getcwd()\n",
    "\n",
    "# Create a directory called \"FlavorDB\" within the current working directory\n",
    "db_dir = os.path.join(cwd_dir, \"FlavorDB\")\n",
    "\n",
    "# Check if the \"FlavorDB\" directory exists, and if not, create it\n",
    "if not os.path.exists(db_dir):\n",
    "    os.makedirs(db_dir)\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    # Loop through a range of 1000 entities (indexed from 0 to 999)\n",
    "    for index in range(1000):\n",
    "        try:\n",
    "            # Compose the URL to fetch data for the current entity index\n",
    "            url = \"https://cosylab.iiitd.edu.in/flavordb2/entities_json?id=%d\" % index\n",
    "\n",
    "            # Send a GET request to the URL and parse the response as JSON\n",
    "            data = requests.get(url).json()\n",
    "\n",
    "            # Convert the JSON data to a string for writing to a file\n",
    "            obj = json.dumps(data)\n",
    "\n",
    "            # Extract the entity name from the JSON data\n",
    "            name_key = \"entity_alias_readable\"\n",
    "            name = data[name_key]\n",
    "\n",
    "            # Print the progress of downloading data for the current entity\n",
    "            print(\"Downloading %s data\" % name)\n",
    "\n",
    "            # Create a file name using the current index and save the JSON data to that file\n",
    "            file_name = os.path.join(db_dir, \"%d.json\" % index)\n",
    "\n",
    "            with open(file_name, 'w') as f:\n",
    "                json.dump(data, f, indent=4, sort_keys=True)\n",
    "\n",
    "            index += 1  # Move to the next entity index\n",
    "        except:\n",
    "            # If there is an exception (e.g., the entity doesn't exist), continue to the next index\n",
    "            continue\n",
    "\n",
    "    print(\"Done\")\n",
    "\n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Process JSON data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# the names of the \"columns\" in the raw JSON objects\n",
    "def flavordb_entity_cols():\n",
    "    return ['entity_id', 'entity_alias_readable', 'entity_alias_synonyms','natural_source_name', 'category_readable', 'molecules']\n",
    "\n",
    "\n",
    "# what we want to rename the JSON object \"columns\" to\n",
    "def flavordb_df_cols():\n",
    "    return ['entity id', 'alias', 'synonyms','scientific name', 'category', 'molecules']\n",
    "\n",
    "\n",
    "# \"subcolumns\" in the \"molecules\" column that we are interested in\n",
    "def molecules_df_cols():\n",
    "    return ['pubchem id', 'common name', 'flavor profile', 'cas id']   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "\n",
    "# Translates the JSON file at the specified dir into a dictionary.\n",
    "def get_flavordb_entity(x):\n",
    "    # Construct the file path for the JSON file based on the input 'x'.\n",
    "    file_path = os.path.join('FlavorDB', str(x) + '.json')\n",
    "    \n",
    "    # Check if the file exists.\n",
    "    if os.path.isfile(file_path):\n",
    "        # If the file exists, open it and load its contents as a dictionary.\n",
    "        with open(file_path, \"r\") as json_file:\n",
    "            return json.load(json_file)\n",
    "    else:\n",
    "        # If the file does not exist, return None to indicate that no data was found.\n",
    "        return None\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_flavordb_dataframes(flavor_df, molecules_df):\n",
    "    \"\"\"\n",
    "    Helps ensure consistent intra-column typing and converts all strings to lowercase.\n",
    "\n",
    "    Args:\n",
    "        flavor_df (DataFrame): DataFrame containing flavor data.\n",
    "        molecules_df (DataFrame): DataFrame containing molecules data.\n",
    "\n",
    "    Returns:\n",
    "        list: A list of two DataFrames, each grouped by specific columns.\n",
    "    \"\"\"\n",
    "\n",
    "    strtype = type('')  # Define the type for strings\n",
    "    settype = type(set())  # Define the type for sets\n",
    "\n",
    "    # Convert 'alias', 'scientific name', and 'category' columns to lowercase strings\n",
    "    for column in ['alias', 'scientific name', 'category']:\n",
    "        flavor_df[column] = [elem.strip().lower() if isinstance(elem, strtype) else '' for elem in flavor_df[column]]\n",
    "\n",
    "    # Convert 'synonyms' column to a set of lowercase strings\n",
    "    def map_to_synonyms_set(elem):\n",
    "        \"\"\"\n",
    "        Converts an element to a set of lowercase strings.\n",
    "\n",
    "        Args:\n",
    "            elem: Element to be converted.\n",
    "\n",
    "        Returns:\n",
    "            set: A set of lowercase strings.\n",
    "        \"\"\"\n",
    "        if isinstance(elem, settype):\n",
    "            return elem\n",
    "        elif isinstance(elem, strtype):\n",
    "            if elem == '':\n",
    "                return set()\n",
    "            # If it's a string of a set, convert it to a set\n",
    "            if elem[0] == '{' and elem[-1] == '}':\n",
    "                return eval(elem)\n",
    "            else:\n",
    "                # Else, it's probably directly from the source\n",
    "                return set(elem.strip().lower().split(', '))\n",
    "        else:\n",
    "            return set()\n",
    "\n",
    "    flavor_df['synonyms'] = [map_to_synonyms_set(elem) for elem in flavor_df['synonyms']]\n",
    "\n",
    "    # Convert 'flavor profile' column in molecules_df to a set of lowercase strings\n",
    "    molecules_df['flavor profile'] = [set([x.strip().lower() for x in elem]) for elem in molecules_df['flavor profile']]\n",
    "\n",
    "    # Group by 'entity id' and 'pubchem id', respectively, and keep the first row of each group\n",
    "    cleaned_flavor_df = flavor_df.groupby('entity id').first().reset_index()\n",
    "    cleaned_molecules_df = molecules_df.groupby('pubchem id').first().reset_index()\n",
    "\n",
    "    return [cleaned_flavor_df, cleaned_molecules_df]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def get_flavordb_dataframes():\n",
    "    \"\"\"\n",
    "    Read JSON data, convert it to DataFrames, and clean them.\n",
    "    \n",
    "    Returns DataFrames for both foods and molecules, as well as missing JSON entries.\n",
    "    \"\"\"\n",
    "    \n",
    "    # Initialize an empty list to store data from Flavordb JSON\n",
    "    flavordb_data = []\n",
    "    \n",
    "    # Initialize an empty dictionary to store molecules' data\n",
    "    molecules_dict = {}\n",
    "    \n",
    "    # Get the columns for the Flavordb entities\n",
    "    flavordb_cols = flavordb_entity_cols()\n",
    "\n",
    "    # Loop through Flavordb entities from 0 to 999 (a total of 1000 entries)\n",
    "    for i in range(0, 1000):\n",
    "        try:\n",
    "            # Get the Flavordb entity for the current index\n",
    "            fdbe = get_flavordb_entity(i)\n",
    "            \n",
    "            # If the entity is None (missing entry), continue to the next iteration\n",
    "            if fdbe is None:\n",
    "                continue\n",
    "            \n",
    "            # Extract specific columns from the Flavordb entity and store them in a list\n",
    "            flavordb_series = [fdbe[k] for k in flavordb_cols[:-1]]\n",
    "            flavordb_series.append(set([m['pubchem_id'] for m in fdbe['molecules']]))\n",
    "            flavordb_data.append(flavordb_series)\n",
    "\n",
    "            # Process and store molecule data in the dictionary\n",
    "            for m in fdbe['molecules']:\n",
    "                if m['pubchem_id'] not in molecules_dict:\n",
    "                    molecules_dict[m['pubchem_id']] = [m['common_name'], set(m['flavor_profile'].split('@')), set(m['cas_id'].split('@'))]\n",
    "        except Exception:\n",
    "            # Handle any exceptions during JSON retrieval (broad exception handling, better to specify the exception)\n",
    "            continue\n",
    "\n",
    "    # Create a DataFrame from the Flavordb data\n",
    "    flavordb_df = pd.DataFrame(flavordb_data, columns=flavordb_df_cols())\n",
    "    \n",
    "    # Create a DataFrame from the molecules dictionary\n",
    "    molecules_df = pd.DataFrame([[k, v[0], v[1], v[2]] for k, v in molecules_dict.items()], columns=molecules_df_cols())\n",
    "\n",
    "    # Clean the Flavordb and molecules DataFrames\n",
    "    flavordb_df, molecules_df = clean_flavordb_dataframes(flavordb_df, molecules_df)\n",
    "\n",
    "    # Return the cleaned DataFrames\n",
    "    return [flavordb_df, molecules_df]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "flavor_df, molecules_df = get_flavordb_dataframes()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>entity id</th>\n",
       "      <th>alias</th>\n",
       "      <th>synonyms</th>\n",
       "      <th>scientific name</th>\n",
       "      <th>category</th>\n",
       "      <th>molecules</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>egg</td>\n",
       "      <td>{egg}</td>\n",
       "      <td>chicken</td>\n",
       "      <td>animal product</td>\n",
       "      <td>{6274, 5311110, 644104, 9609, 18827, 527, 1960...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>bakery products</td>\n",
       "      <td>{bakery products}</td>\n",
       "      <td>poacceae</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{27457, 7976, 31252, 26808, 22201, 26331}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>bread</td>\n",
       "      <td>{bread}</td>\n",
       "      <td>poacceae</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{1031, 1032, 644104, 527, 8723, 31260, 15394, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>rye bread</td>\n",
       "      <td>{rye bread}</td>\n",
       "      <td>rye</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{644104, 7824, 643731, 8468, 1049, 5372954, 80...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>wheaten bread</td>\n",
       "      <td>{soda farls, soda scones}</td>\n",
       "      <td>wheat</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{5365891, 6915, 1146, 12170, 8082, 31251, 7958...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   entity id            alias                   synonyms scientific name  \\\n",
       "0          0              egg                      {egg}         chicken   \n",
       "1          1  bakery products          {bakery products}        poacceae   \n",
       "2          2            bread                    {bread}        poacceae   \n",
       "3          3        rye bread                {rye bread}             rye   \n",
       "4          4    wheaten bread  {soda farls, soda scones}           wheat   \n",
       "\n",
       "         category                                          molecules  \n",
       "0  animal product  {6274, 5311110, 644104, 9609, 18827, 527, 1960...  \n",
       "1          bakery          {27457, 7976, 31252, 26808, 22201, 26331}  \n",
       "2          bakery  {1031, 1032, 644104, 527, 8723, 31260, 15394, ...  \n",
       "3          bakery  {644104, 7824, 643731, 8468, 1049, 5372954, 80...  \n",
       "4          bakery  {5365891, 6915, 1146, 12170, 8082, 31251, 7958...  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flavor_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(936, 6)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flavor_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>pubchem id</th>\n",
       "      <th>common name</th>\n",
       "      <th>flavor profile</th>\n",
       "      <th>cas id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1-Aminopropan-2-ol</td>\n",
       "      <td>{fishy}</td>\n",
       "      <td>{2799-17-9, 78-96-6, 2799-16-8}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49</td>\n",
       "      <td>3-Methyl-2-oxobutanoic acid</td>\n",
       "      <td>{fruity}</td>\n",
       "      <td>{759-05-7}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58</td>\n",
       "      <td>2-oxobutanoic acid</td>\n",
       "      <td>{creamy, lactonic, brown, sweet, caramel}</td>\n",
       "      <td>{600-18-0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70</td>\n",
       "      <td>4-Methyl-2-oxovaleric acid</td>\n",
       "      <td>{fruity}</td>\n",
       "      <td>{816-66-0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>72</td>\n",
       "      <td>3,4-Dihydroxybenzoic Acid</td>\n",
       "      <td>{phenolic, mild, balsamic}</td>\n",
       "      <td>{99-50-3}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pubchem id                  common name  \\\n",
       "0           4           1-Aminopropan-2-ol   \n",
       "1          49  3-Methyl-2-oxobutanoic acid   \n",
       "2          58           2-oxobutanoic acid   \n",
       "3          70   4-Methyl-2-oxovaleric acid   \n",
       "4          72    3,4-Dihydroxybenzoic Acid   \n",
       "\n",
       "                              flavor profile                           cas id  \n",
       "0                                    {fishy}  {2799-17-9, 78-96-6, 2799-16-8}  \n",
       "1                                   {fruity}                       {759-05-7}  \n",
       "2  {creamy, lactonic, brown, sweet, caramel}                       {600-18-0}  \n",
       "3                                   {fruity}                       {816-66-0}  \n",
       "4                 {phenolic, mild, balsamic}                        {99-50-3}  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "molecules_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1788, 4)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "molecules_df.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Alignment between master_recipe_df & flavors_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ID which ingredients are missing from FlavorDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of ingredients in master_recipe_df: 695\n",
      "Number of ingredients in flavors_df: 936\n",
      "Missing ingredient IDs: 113\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>Aliased Ingredient Name</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>flour</td>\n",
       "      <td>2056</td>\n",
       "      <td>9294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>vegetable oil</td>\n",
       "      <td>2089</td>\n",
       "      <td>5584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>lemon juice</td>\n",
       "      <td>984</td>\n",
       "      <td>4036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>cayenne</td>\n",
       "      <td>992</td>\n",
       "      <td>3709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>salt pepper</td>\n",
       "      <td>2022</td>\n",
       "      <td>3129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>baking powder</td>\n",
       "      <td>996</td>\n",
       "      <td>2237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>mayonnaise</td>\n",
       "      <td>2021</td>\n",
       "      <td>1546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>baking soda</td>\n",
       "      <td>996</td>\n",
       "      <td>1387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>tomato paste</td>\n",
       "      <td>2064</td>\n",
       "      <td>1158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>worcestershire sauce</td>\n",
       "      <td>2031</td>\n",
       "      <td>1148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>yeast</td>\n",
       "      <td>993</td>\n",
       "      <td>911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>green chile</td>\n",
       "      <td>992</td>\n",
       "      <td>725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>ketchup</td>\n",
       "      <td>2037</td>\n",
       "      <td>713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>garam masala</td>\n",
       "      <td>2000</td>\n",
       "      <td>690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>coconut milk</td>\n",
       "      <td>981</td>\n",
       "      <td>678</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Aliased Ingredient Name  Entity ID  count\n",
       "50                    flour       2056   9294\n",
       "137           vegetable oil       2089   5584\n",
       "81              lemon juice        984   4036\n",
       "22                  cayenne        992   3709\n",
       "116             salt pepper       2022   3129\n",
       "9             baking powder        996   2237\n",
       "86               mayonnaise       2021   1546\n",
       "10              baking soda        996   1387\n",
       "132            tomato paste       2064   1158\n",
       "142    worcestershire sauce       2031   1148\n",
       "143                   yeast        993    911\n",
       "59              green chile        992    725\n",
       "79                  ketchup       2037    713\n",
       "53             garam masala       2000    690\n",
       "29             coconut milk        981    678"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Printing the number of unique ingredients in the master_recipe_df\n",
    "print(\"Number of ingredients in master_recipe_df: %s\" % len(master_recipe_df['Entity ID'].unique()))\n",
    "\n",
    "# Printing the number of unique ingredients in the flavors_df\n",
    "print(\"Number of ingredients in flavors_df: %s\" % len(flavor_df['entity id'].unique()))\n",
    "\n",
    "# Finding missing ingredient IDs (present in master_recipe_df but not in flavors_df)\n",
    "missing_ingredients_ids = list(set(master_recipe_df['Entity ID'].unique()) - set(flavor_df['entity id'].unique()))\n",
    "\n",
    "# Printing the number of missing ingredient IDs\n",
    "print(\"Missing ingredient IDs: %s\" % len(missing_ingredients_ids))\n",
    "\n",
    "# Grouping and counting missing ingredient names in master_recipe_df, sorting in descending order, and displaying top 15 missing ingredients\n",
    "result_df = master_recipe_df[master_recipe_df['Entity ID'].isin(missing_ingredients_ids)].groupby([\"Aliased Ingredient Name\", \"Entity ID\"])[\"Aliased Ingredient Name\"].count().reset_index(name='count').sort_values(['count'], ascending=False)[:15]\n",
    "print(result_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_ingredients_df = master_recipe_df[master_recipe_df['Entity ID'].isin(missing_ingredients_ids)][[\"Entity ID\",\"Aliased Ingredient Name\"]].drop_duplicates().reset_index(drop=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the FlavorNetwork dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read ingredient information from 'ingr_info.tsv' file into a DataFrame.\n",
    "# Use tab ('\\t') as the separator and consider the first row as the header.\n",
    "ingr_info_df = pd.read_csv('FlavorNetwork/ingr_info.tsv', sep='\\t', header=0)\n",
    "\n",
    "# Clean up the \"ingredient name\" column by replacing underscores ('_') with spaces (' ').\n",
    "ingr_info_df[\"ingredient name\"] = ingr_info_df[\"ingredient name\"].str.replace(r'_', ' ')\n",
    "\n",
    "# Read ingredient composition data from 'ingr_comp.tsv' file into another DataFrame.\n",
    "# Use tab ('\\t') as the separator and consider the first row as the header.\n",
    "ingr_comp_df = pd.read_csv('FlavorNetwork/ingr_comp.tsv', sep='\\t', header=0)\n",
    "\n",
    "# Read component information from 'comp_info.tsv' file into a third DataFrame.\n",
    "# Use tab ('\\t') as the separator and consider the first row as the header.\n",
    "comp_info_df = pd.read_csv('FlavorNetwork/comp_info.tsv', sep='\\t', header=0)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Break apart the molecules dataset to get the cas ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1390,
   "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>pubchem id</th>\n",
       "      <th>common name</th>\n",
       "      <th>flavor profile</th>\n",
       "      <th>cas id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1-Aminopropan-2-ol</td>\n",
       "      <td>{fishy}</td>\n",
       "      <td>78-96-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1-Aminopropan-2-ol</td>\n",
       "      <td>{fishy}</td>\n",
       "      <td>2799-16-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1-Aminopropan-2-ol</td>\n",
       "      <td>{fishy}</td>\n",
       "      <td>2799-17-9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49</td>\n",
       "      <td>3-Methyl-2-oxobutanoic acid</td>\n",
       "      <td>{fruity}</td>\n",
       "      <td>759-05-7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58</td>\n",
       "      <td>2-oxobutanoic acid</td>\n",
       "      <td>{caramel, brown, sweet, lactonic, creamy}</td>\n",
       "      <td>600-18-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>1783</th>\n",
       "      <td>76319594</td>\n",
       "      <td>(-)-alpha-Copaene</td>\n",
       "      <td>{}</td>\n",
       "      <td>3856-25-5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1784</th>\n",
       "      <td>91865027</td>\n",
       "      <td>UNII-S32G48306Y</td>\n",
       "      <td>{}</td>\n",
       "      <td>26184-88-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1785</th>\n",
       "      <td>92043553</td>\n",
       "      <td>Jasmine Oil</td>\n",
       "      <td>{}</td>\n",
       "      <td>8022-96-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1786</th>\n",
       "      <td>92043681</td>\n",
       "      <td>Glycerides, palm-oil mono- and di-, hydrogenat...</td>\n",
       "      <td>{}</td>\n",
       "      <td>91052-70-9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1787</th>\n",
       "      <td>118855872</td>\n",
       "      <td>Glycerides, palm-oil mono- and di-, hydrogenat...</td>\n",
       "      <td>{}</td>\n",
       "      <td>91052-68-5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2693 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      pubchem id                                        common name  \\\n",
       "0              4                                 1-Aminopropan-2-ol   \n",
       "0              4                                 1-Aminopropan-2-ol   \n",
       "0              4                                 1-Aminopropan-2-ol   \n",
       "1             49                        3-Methyl-2-oxobutanoic acid   \n",
       "2             58                                 2-oxobutanoic acid   \n",
       "...          ...                                                ...   \n",
       "1783    76319594                                  (-)-alpha-Copaene   \n",
       "1784    91865027                                    UNII-S32G48306Y   \n",
       "1785    92043553                                        Jasmine Oil   \n",
       "1786    92043681  Glycerides, palm-oil mono- and di-, hydrogenat...   \n",
       "1787   118855872  Glycerides, palm-oil mono- and di-, hydrogenat...   \n",
       "\n",
       "                                 flavor profile      cas id  \n",
       "0                                       {fishy}     78-96-6  \n",
       "0                                       {fishy}   2799-16-8  \n",
       "0                                       {fishy}   2799-17-9  \n",
       "1                                      {fruity}    759-05-7  \n",
       "2     {caramel, brown, sweet, lactonic, creamy}    600-18-0  \n",
       "...                                         ...         ...  \n",
       "1783                                         {}   3856-25-5  \n",
       "1784                                         {}  26184-88-3  \n",
       "1785                                         {}   8022-96-6  \n",
       "1786                                         {}  91052-70-9  \n",
       "1787                                         {}  91052-68-5  \n",
       "\n",
       "[2693 rows x 4 columns]"
      ]
     },
     "execution_count": 1390,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "molecules_tmp_df = molecules_df.copy()\n",
    "molecules_tmp_df['cas id'] = molecules_tmp_df['cas id'].apply(list)\n",
    "molecules_tmp_df = molecules_tmp_df.explode('cas id')\n",
    "molecules_tmp_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Denormalize the Flavor Network dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1391,
   "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># id</th>\n",
       "      <th>ingredient name</th>\n",
       "      <th>category</th>\n",
       "      <th>compound id</th>\n",
       "      <th>Compound name</th>\n",
       "      <th>CAS number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>magnolia tripetala</td>\n",
       "      <td>flower</td>\n",
       "      <td>995</td>\n",
       "      <td>diacetyl</td>\n",
       "      <td>431-03-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>calyptranthes parriculata</td>\n",
       "      <td>plant</td>\n",
       "      <td>921</td>\n",
       "      <td>citral_(neral)</td>\n",
       "      <td>5392-40-5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>chamaecyparis pisifera oil</td>\n",
       "      <td>plant derivative</td>\n",
       "      <td>1018</td>\n",
       "      <td>undecanoic_acid</td>\n",
       "      <td>112-37-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>mackerel</td>\n",
       "      <td>fish/seafood</td>\n",
       "      <td>272</td>\n",
       "      <td>2,4-nonadienal</td>\n",
       "      <td>6750-03-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>mackerel</td>\n",
       "      <td>fish/seafood</td>\n",
       "      <td>275</td>\n",
       "      <td>lauric_acid</td>\n",
       "      <td>143-07-7</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>36781</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>1102</td>\n",
       "      <td>2-heptanol</td>\n",
       "      <td>543-49-7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36782</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>268</td>\n",
       "      <td>isoamyl_hexanoate</td>\n",
       "      <td>2198-61-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36783</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>821</td>\n",
       "      <td>isoamyl_butyrate</td>\n",
       "      <td>106-27-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36784</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>554</td>\n",
       "      <td>n-nonanal</td>\n",
       "      <td>124-19-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36785</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>556</td>\n",
       "      <td>2,5-dimethylpyrazine</td>\n",
       "      <td>123-32-0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>36786 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       # id             ingredient name          category  compound id  \\\n",
       "0         0          magnolia tripetala            flower          995   \n",
       "1         1   calyptranthes parriculata             plant          921   \n",
       "2         2  chamaecyparis pisifera oil  plant derivative         1018   \n",
       "3         3                    mackerel      fish/seafood          272   \n",
       "4         3                    mackerel      fish/seafood          275   \n",
       "...     ...                         ...               ...          ...   \n",
       "36781  1529              munster cheese             dairy         1102   \n",
       "36782  1529              munster cheese             dairy          268   \n",
       "36783  1529              munster cheese             dairy          821   \n",
       "36784  1529              munster cheese             dairy          554   \n",
       "36785  1529              munster cheese             dairy          556   \n",
       "\n",
       "              Compound name CAS number  \n",
       "0                  diacetyl   431-03-8  \n",
       "1            citral_(neral)  5392-40-5  \n",
       "2           undecanoic_acid   112-37-8  \n",
       "3            2,4-nonadienal  6750-03-4  \n",
       "4               lauric_acid   143-07-7  \n",
       "...                     ...        ...  \n",
       "36781            2-heptanol   543-49-7  \n",
       "36782     isoamyl_hexanoate  2198-61-0  \n",
       "36783      isoamyl_butyrate   106-27-4  \n",
       "36784             n-nonanal   124-19-6  \n",
       "36785  2,5-dimethylpyrazine   123-32-0  \n",
       "\n",
       "[36786 rows x 6 columns]"
      ]
     },
     "execution_count": 1391,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge the 'ingr_info_df' DataFrame with 'ingr_comp_df' DataFrame using the 'id' and '# ingredient id' columns as the keys,\n",
    "# keeping only the left rows. Then, drop the '# ingredient id' column from the resulting DataFrame.\n",
    "ingr_info_df = ingr_info_df.merge(ingr_comp_df, left_on='# id', right_on=\"# ingredient id\", how='left').drop(columns=['# ingredient id'])\n",
    "\n",
    "# Convert the 'compound id' column to nullable integers (Int64).\n",
    "ingr_info_df['compound id'] = ingr_info_df['compound id'].astype('Int64')\n",
    "\n",
    "# Convert the '# id' column in 'comp_info_df' to nullable integers (Int64).\n",
    "comp_info_df['# id'] = comp_info_df['# id'].astype('Int64')\n",
    "\n",
    "# Merge the 'ingr_info_df' DataFrame with 'comp_info_df' DataFrame using the 'compound id' and '# id' columns as the keys,\n",
    "# keeping only the left rows. Then, drop the '# id_y' column from the resulting DataFrame and rename '# id_x' to '# id'.\n",
    "ingr_info_df = ingr_info_df.merge(comp_info_df, left_on=\"compound id\", right_on='# id', how='left').drop(columns=['# id_y']).rename(columns={'# id_x': '# id'})\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge molecules_df onto it to get the pubchem id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1392,
   "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># id</th>\n",
       "      <th>ingredient name</th>\n",
       "      <th>category</th>\n",
       "      <th>compound id</th>\n",
       "      <th>Compound name</th>\n",
       "      <th>CAS number</th>\n",
       "      <th>pubchem id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>magnolia tripetala</td>\n",
       "      <td>flower</td>\n",
       "      <td>995</td>\n",
       "      <td>diacetyl</td>\n",
       "      <td>431-03-8</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>calyptranthes parriculata</td>\n",
       "      <td>plant</td>\n",
       "      <td>921</td>\n",
       "      <td>citral_(neral)</td>\n",
       "      <td>5392-40-5</td>\n",
       "      <td>638011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>calyptranthes parriculata</td>\n",
       "      <td>plant</td>\n",
       "      <td>921</td>\n",
       "      <td>citral_(neral)</td>\n",
       "      <td>5392-40-5</td>\n",
       "      <td>643779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>chamaecyparis pisifera oil</td>\n",
       "      <td>plant derivative</td>\n",
       "      <td>1018</td>\n",
       "      <td>undecanoic_acid</td>\n",
       "      <td>112-37-8</td>\n",
       "      <td>8180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>mackerel</td>\n",
       "      <td>fish/seafood</td>\n",
       "      <td>272</td>\n",
       "      <td>2,4-nonadienal</td>\n",
       "      <td>6750-03-4</td>\n",
       "      <td>5283339</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>38738</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>1102</td>\n",
       "      <td>2-heptanol</td>\n",
       "      <td>543-49-7</td>\n",
       "      <td>10976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38739</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>268</td>\n",
       "      <td>isoamyl_hexanoate</td>\n",
       "      <td>2198-61-0</td>\n",
       "      <td>16617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38740</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>821</td>\n",
       "      <td>isoamyl_butyrate</td>\n",
       "      <td>106-27-4</td>\n",
       "      <td>7795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38741</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>554</td>\n",
       "      <td>n-nonanal</td>\n",
       "      <td>124-19-6</td>\n",
       "      <td>31289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38742</th>\n",
       "      <td>1529</td>\n",
       "      <td>munster cheese</td>\n",
       "      <td>dairy</td>\n",
       "      <td>556</td>\n",
       "      <td>2,5-dimethylpyrazine</td>\n",
       "      <td>123-32-0</td>\n",
       "      <td>31252</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>38743 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       # id             ingredient name          category  compound id  \\\n",
       "0         0          magnolia tripetala            flower          995   \n",
       "1         1   calyptranthes parriculata             plant          921   \n",
       "2         1   calyptranthes parriculata             plant          921   \n",
       "3         2  chamaecyparis pisifera oil  plant derivative         1018   \n",
       "4         3                    mackerel      fish/seafood          272   \n",
       "...     ...                         ...               ...          ...   \n",
       "38738  1529              munster cheese             dairy         1102   \n",
       "38739  1529              munster cheese             dairy          268   \n",
       "38740  1529              munster cheese             dairy          821   \n",
       "38741  1529              munster cheese             dairy          554   \n",
       "38742  1529              munster cheese             dairy          556   \n",
       "\n",
       "              Compound name CAS number  pubchem id  \n",
       "0                  diacetyl   431-03-8         650  \n",
       "1            citral_(neral)  5392-40-5      638011  \n",
       "2            citral_(neral)  5392-40-5      643779  \n",
       "3           undecanoic_acid   112-37-8        8180  \n",
       "4            2,4-nonadienal  6750-03-4     5283339  \n",
       "...                     ...        ...         ...  \n",
       "38738            2-heptanol   543-49-7       10976  \n",
       "38739     isoamyl_hexanoate  2198-61-0       16617  \n",
       "38740      isoamyl_butyrate   106-27-4        7795  \n",
       "38741             n-nonanal   124-19-6       31289  \n",
       "38742  2,5-dimethylpyrazine   123-32-0       31252  \n",
       "\n",
       "[38743 rows x 7 columns]"
      ]
     },
     "execution_count": 1392,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ingr_info_df = ingr_info_df.merge(molecules_tmp_df[[\"pubchem id\",\"cas id\"]], left_on=\"CAS number\", right_on=\"cas id\", how='left').drop(columns=['cas id'])\n",
    "ingr_info_df['pubchem id'] = ingr_info_df['pubchem id'].astype('Int64')\n",
    "ingr_info_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Enrich the missing_ingredients_df with the ids of the molecules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1393,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to get the category of an ingredient from the ingr_info_df DataFrame\n",
    "def apply_category(ingredient_name):\n",
    "    try:\n",
    "        # Filter ingr_info_df by ingredient name and get the category\n",
    "        cat_name = ingr_info_df[ingr_info_df[\"ingredient name\"] == ingredient_name][\"category\"].iloc[0]\n",
    "        # Map some specific categories to simplified categories\n",
    "        if cat_name == \"fish/seafood\":\n",
    "            return \"seafood\"\n",
    "        elif cat_name == \"beverage alcoholic\":\n",
    "            return \"beverage alcoholic\"\n",
    "        else:\n",
    "            return cat_name\n",
    "    except:\n",
    "        # Return None if there's an error or if the ingredient is not found\n",
    "        return None\n",
    "\n",
    "# Function to get the set of pubchem ids associated with an ingredient from the ingr_info_df DataFrame\n",
    "def apply_molecules(ingredient_name):\n",
    "    # Filter ingr_info_df by ingredient name and drop rows with missing pubchem ids\n",
    "    filtered_df = ingr_info_df[ingr_info_df['ingredient name'] == ingredient_name].dropna(subset=['pubchem id'])\n",
    "    # Get the set of unique pubchem ids\n",
    "    pubchem_ids = set(filtered_df['pubchem id'])\n",
    "    return pubchem_ids\n",
    "\n",
    "# Get a list of missing ingredient names that exist in both missing_ingredients_df and ingr_info_df\n",
    "missing_ingredients_names = list(set(missing_ingredients_df[\"Aliased Ingredient Name\"].unique()).intersection(set(ingr_info_df[\"ingredient name\"].unique())))\n",
    "\n",
    "# Filter missing_ingredients_df to only include rows with missing ingredient names\n",
    "missing_ingredients_df = missing_ingredients_df[missing_ingredients_df[\"Aliased Ingredient Name\"].isin(missing_ingredients_names)]\n",
    "\n",
    "# Apply the apply_category function to get the category for each missing ingredient\n",
    "missing_ingredients_df[\"category\"] = missing_ingredients_df[\"Aliased Ingredient Name\"].apply(apply_category)\n",
    "\n",
    "# Apply lambda function to get the set of synonyms for each missing ingredient\n",
    "missing_ingredients_df[\"synonyms\"] = missing_ingredients_df[\"Aliased Ingredient Name\"].apply(lambda x: set(ingr_info_df[ingr_info_df[\"ingredient name\"] == x][\"ingredient name\"]))\n",
    "\n",
    "# Apply the apply_molecules function to get the set of molecules (pubchem ids) for each missing ingredient\n",
    "missing_ingredients_df[\"molecules\"] = missing_ingredients_df[\"Aliased Ingredient Name\"].apply(apply_molecules)\n",
    "\n",
    "# Fill the \"scientific name\" column with the same values as the \"Aliased Ingredient Name\" column\n",
    "missing_ingredients_df[\"scientific name\"] = missing_ingredients_df[\"Aliased Ingredient Name\"]\n",
    "\n",
    "# Rename the columns to have more readable names\n",
    "missing_ingredients_df.rename(columns={\"Entity ID\": \"entity id\", \"Aliased Ingredient Name\": \"alias\", \"synonyms\": \"synonyms\", \"category\": \"category\", \"molecules\": \"molecules\", \"scientific name\": \"scientific name\"}, inplace=True)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add the complete missing_ingredients_df to flavor_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1394,
   "metadata": {},
   "outputs": [],
   "source": [
    "flavor_df = flavor_df.append(missing_ingredients_df[[\"entity id\",\"alias\",\"synonyms\",\"category\",\"molecules\",\"scientific name\"]]).reset_index(drop=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1395,
   "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>entity id</th>\n",
       "      <th>alias</th>\n",
       "      <th>synonyms</th>\n",
       "      <th>scientific name</th>\n",
       "      <th>category</th>\n",
       "      <th>molecules</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>936</th>\n",
       "      <td>992</td>\n",
       "      <td>cayenne</td>\n",
       "      <td>{cayenne}</td>\n",
       "      <td>cayenne</td>\n",
       "      <td>spice</td>\n",
       "      <td>{5362819, 7559, 102667, 1548943, 7824, 7826, 5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>937</th>\n",
       "      <td>982</td>\n",
       "      <td>coconut oil</td>\n",
       "      <td>{coconut oil}</td>\n",
       "      <td>coconut oil</td>\n",
       "      <td>plant derivative</td>\n",
       "      <td>{13187, 8163, 11622, 12777, 12844, 335, 8175, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>938</th>\n",
       "      <td>993</td>\n",
       "      <td>yeast</td>\n",
       "      <td>{yeast}</td>\n",
       "      <td>yeast</td>\n",
       "      <td>plant</td>\n",
       "      <td>{6202}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>939</th>\n",
       "      <td>2088</td>\n",
       "      <td>crab</td>\n",
       "      <td>{crab}</td>\n",
       "      <td>crab</td>\n",
       "      <td>seafood</td>\n",
       "      <td>{8193, 1146, 18827, 19602, 1049, 8093, 798, 11...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>940</th>\n",
       "      <td>984</td>\n",
       "      <td>lemon juice</td>\n",
       "      <td>{lemon juice}</td>\n",
       "      <td>lemon juice</td>\n",
       "      <td>fruit</td>\n",
       "      <td>{9017, 7909, 62367}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941</th>\n",
       "      <td>2089</td>\n",
       "      <td>vegetable oil</td>\n",
       "      <td>{vegetable oil}</td>\n",
       "      <td>vegetable oil</td>\n",
       "      <td>plant derivative</td>\n",
       "      <td>{7762}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>942</th>\n",
       "      <td>2071</td>\n",
       "      <td>mackerel</td>\n",
       "      <td>{mackerel}</td>\n",
       "      <td>mackerel</td>\n",
       "      <td>seafood</td>\n",
       "      <td>{5283329, 90246, 1031, 5283335, 11527, 1032, 5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>943</th>\n",
       "      <td>980</td>\n",
       "      <td>apple juice</td>\n",
       "      <td>{apple juice}</td>\n",
       "      <td>apple juice</td>\n",
       "      <td>fruit</td>\n",
       "      <td>{6276, 12293, 10886, 135, 1032, 11529, 12294, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>944</th>\n",
       "      <td>1000</td>\n",
       "      <td>gelatin</td>\n",
       "      <td>{gelatin}</td>\n",
       "      <td>gelatin</td>\n",
       "      <td>animal product</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>945</th>\n",
       "      <td>2096</td>\n",
       "      <td>catfish</td>\n",
       "      <td>{catfish}</td>\n",
       "      <td>catfish</td>\n",
       "      <td>seafood</td>\n",
       "      <td>{5283329, 5311110, 1031, 90246, 5283335, 11527...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>946</th>\n",
       "      <td>2097</td>\n",
       "      <td>salmon</td>\n",
       "      <td>{salmon}</td>\n",
       "      <td>salmon</td>\n",
       "      <td>seafood</td>\n",
       "      <td>{5283329, 8194, 1031, 5283335, 1032, 5283339, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>947</th>\n",
       "      <td>2069</td>\n",
       "      <td>tuna</td>\n",
       "      <td>{tuna}</td>\n",
       "      <td>tuna</td>\n",
       "      <td>seafood</td>\n",
       "      <td>{5283329, 8194, 5283335, 1032, 1031, 5283339, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>948</th>\n",
       "      <td>990</td>\n",
       "      <td>lard</td>\n",
       "      <td>{lard}</td>\n",
       "      <td>lard</td>\n",
       "      <td>animal product</td>\n",
       "      <td>{5283356}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>949</th>\n",
       "      <td>2092</td>\n",
       "      <td>lobster</td>\n",
       "      <td>{lobster}</td>\n",
       "      <td>lobster</td>\n",
       "      <td>seafood</td>\n",
       "      <td>{15380, 454, 19310}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>950</th>\n",
       "      <td>2067</td>\n",
       "      <td>whitefish</td>\n",
       "      <td>{whitefish}</td>\n",
       "      <td>whitefish</td>\n",
       "      <td>seafood</td>\n",
       "      <td>{5283329, 5311110, 1031, 90246, 5283335, 11527...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>951</th>\n",
       "      <td>2099</td>\n",
       "      <td>haddock</td>\n",
       "      <td>{haddock}</td>\n",
       "      <td>haddock</td>\n",
       "      <td>seafood</td>\n",
       "      <td>{5283329, 5311110, 1031, 90246, 5283335, 11527...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>952</th>\n",
       "      <td>995</td>\n",
       "      <td>sauerkraut</td>\n",
       "      <td>{sauerkraut}</td>\n",
       "      <td>sauerkraut</td>\n",
       "      <td>vegetable</td>\n",
       "      <td>{6276, 8103, 1032, 9609, 31276, 1068, 8908, 52...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>953</th>\n",
       "      <td>994</td>\n",
       "      <td>tequila</td>\n",
       "      <td>{tequila}</td>\n",
       "      <td>tequila</td>\n",
       "      <td>alcoholic beverage</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>954</th>\n",
       "      <td>2068</td>\n",
       "      <td>seal</td>\n",
       "      <td>{seal}</td>\n",
       "      <td>seal</td>\n",
       "      <td>meat</td>\n",
       "      <td>{7938, 13187, 18827, 5362572, 77837, 5281168, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>955</th>\n",
       "      <td>2086</td>\n",
       "      <td>herring</td>\n",
       "      <td>{herring}</td>\n",
       "      <td>herring</td>\n",
       "      <td>seafood</td>\n",
       "      <td>{5283329, 90246, 5283335, 1032, 11527, 1031, 5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>956</th>\n",
       "      <td>2101</td>\n",
       "      <td>apple sauce</td>\n",
       "      <td>{apple sauce}</td>\n",
       "      <td>apple sauce</td>\n",
       "      <td>plant derivative</td>\n",
       "      <td>{5311110}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     entity id          alias         synonyms scientific name  \\\n",
       "936        992        cayenne        {cayenne}         cayenne   \n",
       "937        982    coconut oil    {coconut oil}     coconut oil   \n",
       "938        993          yeast          {yeast}           yeast   \n",
       "939       2088           crab           {crab}            crab   \n",
       "940        984    lemon juice    {lemon juice}     lemon juice   \n",
       "941       2089  vegetable oil  {vegetable oil}   vegetable oil   \n",
       "942       2071       mackerel       {mackerel}        mackerel   \n",
       "943        980    apple juice    {apple juice}     apple juice   \n",
       "944       1000        gelatin        {gelatin}         gelatin   \n",
       "945       2096        catfish        {catfish}         catfish   \n",
       "946       2097         salmon         {salmon}          salmon   \n",
       "947       2069           tuna           {tuna}            tuna   \n",
       "948        990           lard           {lard}            lard   \n",
       "949       2092        lobster        {lobster}         lobster   \n",
       "950       2067      whitefish      {whitefish}       whitefish   \n",
       "951       2099        haddock        {haddock}         haddock   \n",
       "952        995     sauerkraut     {sauerkraut}      sauerkraut   \n",
       "953        994        tequila        {tequila}         tequila   \n",
       "954       2068           seal           {seal}            seal   \n",
       "955       2086        herring        {herring}         herring   \n",
       "956       2101    apple sauce    {apple sauce}     apple sauce   \n",
       "\n",
       "               category                                          molecules  \n",
       "936               spice  {5362819, 7559, 102667, 1548943, 7824, 7826, 5...  \n",
       "937    plant derivative  {13187, 8163, 11622, 12777, 12844, 335, 8175, ...  \n",
       "938               plant                                             {6202}  \n",
       "939             seafood  {8193, 1146, 18827, 19602, 1049, 8093, 798, 11...  \n",
       "940               fruit                                {9017, 7909, 62367}  \n",
       "941    plant derivative                                             {7762}  \n",
       "942             seafood  {5283329, 90246, 1031, 5283335, 11527, 1032, 5...  \n",
       "943               fruit  {6276, 12293, 10886, 135, 1032, 11529, 12294, ...  \n",
       "944      animal product                                                 {}  \n",
       "945             seafood  {5283329, 5311110, 1031, 90246, 5283335, 11527...  \n",
       "946             seafood  {5283329, 8194, 1031, 5283335, 1032, 5283339, ...  \n",
       "947             seafood  {5283329, 8194, 5283335, 1032, 1031, 5283339, ...  \n",
       "948      animal product                                          {5283356}  \n",
       "949             seafood                                {15380, 454, 19310}  \n",
       "950             seafood  {5283329, 5311110, 1031, 90246, 5283335, 11527...  \n",
       "951             seafood  {5283329, 5311110, 1031, 90246, 5283335, 11527...  \n",
       "952           vegetable  {6276, 8103, 1032, 9609, 31276, 1068, 8908, 52...  \n",
       "953  alcoholic beverage                                                 {}  \n",
       "954                meat  {7938, 13187, 18827, 5362572, 77837, 5281168, ...  \n",
       "955             seafood  {5283329, 90246, 5283335, 1032, 11527, 1031, 5...  \n",
       "956    plant derivative                                          {5311110}  "
      ]
     },
     "execution_count": 1395,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flavor_df[flavor_df[\"entity id\"].isin(missing_ingredients_df[\"entity id\"].unique())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1396,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55"
      ]
     },
     "execution_count": 1396,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(flavor_df[flavor_df[\"entity id\"] == 0][\"molecules\"].to_list()[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1397,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55"
      ]
     },
     "execution_count": 1397,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = list(flavor_df[flavor_df[\"entity id\"] == 0][\"molecules\"].to_list()[0])\n",
    "len(molecules_df[molecules_df[\"pubchem id\"].isin(test)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1398,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"{'vegetable', 'fungus', 'herb', 'beverage', 'fish', 'animal product', 'cereal', 'berry', 'dairy', 'fruit', 'legume', 'bakery', 'fruit-berry', 'seafood', 'fruit citrus', 'maize', 'dish', 'cabbage', 'alcoholic beverage', 'vegetable stem', 'flower', 'seed', 'gourd', 'additive', 'vegetable tuber', 'essential oil', 'plant', 'vegetable root', 'nut', 'vegetable fruit', 'fruit essence', 'beverage caffeinated', 'beverage alcoholic', 'meat', 'plant derivative', 'spice'}\""
      ]
     },
     "execution_count": 1398,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(set(flavor_df['category']))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Feature Engineering"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Group"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\"We still have some more data cleaning to do. What's the difference between a vegetable, fruit, and vegetable fruit? We're mostly interested in the ingredients list, not in finished products like cornbread, so we'll keep only raw ingredients, remove a few outliers, and give them each a food group.\" (Choo, V.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1399,
   "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>index</th>\n",
       "      <th>entity id</th>\n",
       "      <th>alias</th>\n",
       "      <th>synonyms</th>\n",
       "      <th>scientific name</th>\n",
       "      <th>category</th>\n",
       "      <th>molecules</th>\n",
       "      <th>Group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>egg</td>\n",
       "      <td>{egg}</td>\n",
       "      <td>chicken</td>\n",
       "      <td>animal product</td>\n",
       "      <td>{6274, 5311110, 644104, 9609, 18827, 527, 1960...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>bakery products</td>\n",
       "      <td>{bakery products}</td>\n",
       "      <td>poacceae</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{27457, 7976, 31252, 26808, 22201, 26331}</td>\n",
       "      <td>grain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>bread</td>\n",
       "      <td>{bread}</td>\n",
       "      <td>poacceae</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{1031, 1032, 644104, 527, 8723, 31260, 15394, ...</td>\n",
       "      <td>grain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>rye bread</td>\n",
       "      <td>{rye bread}</td>\n",
       "      <td>rye</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{644104, 7824, 643731, 8468, 1049, 5372954, 80...</td>\n",
       "      <td>grain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>wheaten bread</td>\n",
       "      <td>{soda farls, soda scones}</td>\n",
       "      <td>wheat</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{5365891, 6915, 1146, 12170, 8082, 31251, 7958...</td>\n",
       "      <td>grain</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  entity id            alias                   synonyms  \\\n",
       "0      0          0              egg                      {egg}   \n",
       "1      1          1  bakery products          {bakery products}   \n",
       "2      2          2            bread                    {bread}   \n",
       "3      3          3        rye bread                {rye bread}   \n",
       "4      4          4    wheaten bread  {soda farls, soda scones}   \n",
       "\n",
       "  scientific name        category  \\\n",
       "0         chicken  animal product   \n",
       "1        poacceae          bakery   \n",
       "2        poacceae          bakery   \n",
       "3             rye          bakery   \n",
       "4           wheat          bakery   \n",
       "\n",
       "                                           molecules  Group  \n",
       "0  {6274, 5311110, 644104, 9609, 18827, 527, 1960...   None  \n",
       "1          {27457, 7976, 31252, 26808, 22201, 26331}  grain  \n",
       "2  {1031, 1032, 644104, 527, 8723, 31260, 15394, ...  grain  \n",
       "3  {644104, 7824, 643731, 8468, 1049, 5372954, 80...  grain  \n",
       "4  {5365891, 6915, 1146, 12170, 8082, 31251, 7958...  grain  "
      ]
     },
     "execution_count": 1399,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def food_groups():\n",
    "    \"\"\"\n",
    "    Return a set of food groups.\n",
    "    \"\"\"\n",
    "    return set([\n",
    "        'grain', 'vegetable', 'fruit', 'protein',\n",
    "        'dairy', 'fat', 'sugar', 'seasoning',\n",
    "        'beverage', 'alcohol'\n",
    "    ])\n",
    "\n",
    "def get_food_group(food, category):\n",
    "    \"\"\"\n",
    "    Maps each food category to a food group.\n",
    "\n",
    "    The food groups include the main five: grain, vegetable, fruit, protein,\n",
    "    dairy, fat, and sugar. However, they also include others: seasonings,\n",
    "    beverages, alcohol.\n",
    "\n",
    "    Args:\n",
    "        food (str): The specific food item.\n",
    "        category (str): The category of the food item.\n",
    "\n",
    "    Returns:\n",
    "        str or None: The food group to which the food item belongs, or None if it doesn't belong to any group.\n",
    "    \"\"\"\n",
    "    out = None\n",
    "\n",
    "    # Categorize the food item based on its category\n",
    "    if category in ['bakery', 'vegetable tuber', 'cereal']:\n",
    "        out = 'grain'\n",
    "    elif category in [\n",
    "        'flower', 'fungus', 'plant', 'cabbage',\n",
    "        'vegetable fruit', 'herb', 'gourd', 'vegetable'\n",
    "    ]:\n",
    "        out = 'vegetable'\n",
    "    elif category in [\n",
    "        'fruit-berry', 'berry', 'fruit', 'fruit citrus'\n",
    "    ]:\n",
    "        out = 'fruit'\n",
    "    elif category in [\n",
    "        'legume', 'nut', 'seed', 'seafood', 'fish', 'meat'\n",
    "    ]:\n",
    "        out = 'protein'\n",
    "    elif category in ['dairy']:\n",
    "        out = 'dairy'\n",
    "    elif category in [\n",
    "        'fruit essence', 'additive', 'spice', 'essential oil'\n",
    "    ]:\n",
    "        out = 'seasoning'\n",
    "    elif category in ['beverage alcoholic']:\n",
    "        out = 'alcohol'\n",
    "    elif 'beverage' in category:\n",
    "        out = 'beverage'\n",
    "    # Handle special cases within the 'maize' category\n",
    "    elif category == 'maize':\n",
    "        if food in ['corn', 'sweetcorn']:\n",
    "            out = 'vegetable'\n",
    "        elif food in ['cornbread', 'corn grits', 'popcorn']:\n",
    "            out = 'grain'\n",
    "        elif food == 'corn oil':\n",
    "            out = 'fat'\n",
    "    # Handle special cases within the 'plant derivative' category\n",
    "    elif category == 'plant derivative':\n",
    "        if (any(x in food for x in ['sauce', 'vinegar', 'cocoa'])\n",
    "                or food in ['creosote', 'storax']):\n",
    "            out = 'seasoning'\n",
    "        elif 'seed' in food or food == 'peanut butter':\n",
    "            out = 'protein'\n",
    "        elif any([x in food for x in ['butter', 'oil']]):\n",
    "            out = 'fat'\n",
    "        elif food == 'fermented tea':\n",
    "            out = 'beverage'\n",
    "        elif food in ['honey', 'chocolate', 'chocolate spread']:\n",
    "            out = 'sugar'\n",
    "        elif food == 'macaroni':\n",
    "            out = 'grain'\n",
    "        elif food in ['jute', 'tofu']:\n",
    "            out = 'vegetable'\n",
    "        elif food == 'soy yogurt':\n",
    "            out = 'dairy'\n",
    "    # Handle special cases within the 'additive' category\n",
    "    elif category == 'additive':\n",
    "        if 'sugar' in food or food in [\n",
    "            'fruit preserve', 'syrup', 'icing', 'molasses'\n",
    "        ]:\n",
    "            out = 'sugar'\n",
    "        elif 'margarine' in food or food in ['cooking oil', 'shortening']:\n",
    "            out = 'fat'\n",
    "        elif food in ['sauce', 'gelatin dessert', 'spread', 'topping', 'water']:\n",
    "            out = None\n",
    "        elif food == 'stuffing':\n",
    "            out = 'grain'\n",
    "        else:\n",
    "            out = 'seasoning'\n",
    "\n",
    "    # Check for specific food items that belong to the 'fat' group\n",
    "    if (\n",
    "        any([\n",
    "            food == x + ' oil'\n",
    "            for x in ['soybean', 'cooking', 'fish', 'peanut', 'canola', 'corn']\n",
    "        ])\n",
    "        or food in ['butter', 'ghee']\n",
    "        or (' butter' in food and food != 'peanut butter')\n",
    "        or 'margarine' in food\n",
    "    ):\n",
    "        out = 'fat'\n",
    "    # Check for specific food items that belong to the 'sugar' group\n",
    "    elif food in [\n",
    "        'sugar', 'honey', 'molasses', 'agave', 'dulce de leche'\n",
    "    ]:\n",
    "        out = 'sugar'\n",
    "    # Check for specific food items that belong to the 'vegetable' group\n",
    "    elif food in ['irish moss', 'kelp', 'kombu', 'wakame']:\n",
    "        out = 'vegetable'\n",
    "    elif food in ['butternut squash', 'winter squash', 'japanese pumpkin']:\n",
    "        out = 'vegetable'\n",
    "    # Check for specific food items that belong to the 'sugar' group (sweet desserts)\n",
    "    elif food in ['sweet custard', 'candy bar', 'chocolate mousse', 'fudge']:\n",
    "        out = 'sugar'\n",
    "    # Check for specific food items that belong to the 'seasoning' group (cocoa-related items)\n",
    "    elif 'cocoa' in food:\n",
    "        out = 'seasoning'\n",
    "\n",
    "    return out\n",
    "\n",
    "\n",
    "# The DataFrame is assumed to have columns 'alias' (the specific food item) and 'category' (the category of the item).\n",
    "ridf = flavor_df.copy()\n",
    "ridf['Group'] = [\n",
    "    get_food_group(ridf.at[i, 'alias'], ridf.at[i, 'category'])\n",
    "    for i in ridf.index\n",
    "]\n",
    "# Drop any rows where the group is None (i.e., the food item does not belong to any group).\n",
    "# If you want to keep the None values, you can comment this line.\n",
    "# ridf = ridf[[g is not None for g in ridf['group']]]\n",
    "ridf = ridf.reset_index()\n",
    "ridf.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1400,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"{'vegetable', 'fungus', 'herb', 'beverage', 'fish', 'animal product', 'cereal', 'berry', 'dairy', 'fruit', 'legume', 'bakery', 'fruit-berry', 'seafood', 'fruit citrus', 'maize', 'dish', 'cabbage', 'alcoholic beverage', 'vegetable stem', 'flower', 'seed', 'gourd', 'additive', 'vegetable tuber', 'essential oil', 'plant', 'vegetable root', 'nut', 'vegetable fruit', 'fruit essence', 'beverage caffeinated', 'beverage alcoholic', 'meat', 'plant derivative', 'spice'}\""
      ]
     },
     "execution_count": 1400,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(set(ridf['category']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1401,
   "metadata": {},
   "outputs": [],
   "source": [
    "ridf.rename(columns={\"entity id\":\"Entity ID\",\"alias\":\"Aliased Ingredient Name\",\"synonyms\":\"Synonyms\",\"category\":\"Category\",\"molecules\":\"Molecules\",\"scientific name\":\"Scientific Name\"}, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dietary Style\n",
    "\n",
    "Taken from RecipeDB. (Kim, J. Y., Glober, N., & Varshney, L. R. (2019))\n",
    "\n",
    "Rules were created for designating a dietary style based on the ingredient category and/or ingredient(s) used in a recipe. For each dietary style, the rules were formed to specify categories of ingredients that were restricted and the ones mandatory. For a recipe to be associated with a dietary style, it needed to have no ingredient belonging to the dietary style’s list of restricted categories while having at least one or more ingredients belonging to each of the dietary style’s list of mandatory categories. As a precaution, any recipe containing an ingredient under the category ‘bakery’ was assumed to have eggs in it. Also, a recipe was not mapped to any dietary style if it had an ingredient(s) of the ‘dish’ category. \n",
    "\n",
    "Following were the rules used: Vegan (Exclude: meat, eggs, dairy, fish, seafood, dish); Pescetarian (Include: fish or seafood and Exclude: meat, dairy, dish); Lacto-Vegetarian (Include: dairy and Exclude: meat, eggs, fish, seafood, dish); Ovo-Vegetarian (Include: egg and Exclude meat, fish, seafood, dairy, dish); and Ovo-Lacto Vegetarian (Include: egg and dairy and Exclude: meat, fish, seafood, dish)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1402,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Meat',\n",
       " 'Bakery',\n",
       " 'Beverage',\n",
       " 'Beverage Alcoholic',\n",
       " 'Cereal',\n",
       " 'Maize',\n",
       " 'Dairy',\n",
       " 'Essential Oil',\n",
       " 'Fruit',\n",
       " 'Seafood',\n",
       " 'Fish',\n",
       " 'Flower',\n",
       " 'Fungus',\n",
       " 'Herb',\n",
       " 'Dish',\n",
       " 'Nuts & Seed',\n",
       " 'Legume',\n",
       " 'Plant',\n",
       " 'Spice',\n",
       " 'Vegetable',\n",
       " 'Additive']"
      ]
     },
     "execution_count": 1402,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ingredients_df['Category'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1404,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Splitting the ingredients into categories\n",
    "tmp_master_recipe_df = master_recipe_df[[\"Entity ID\", \"Category\"]].drop_duplicates().reset_index(drop=True)\n",
    "\n",
    "tmp_dietary_df = master_recipe_df[['Recipe ID']].drop_duplicates().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1405,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "328b221be1e94d2dba926b0ad0247f56",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Dask Apply:   0%|          | 0/20 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Define a function to apply dietary rules based on the ingredients of a recipe\n",
    "def apply_dietary_rules(recipe_id):\n",
    "    \n",
    "    # Create a set to store unique categories of ingredients in the recipe\n",
    "    recipe_category_set = set()\n",
    "    \n",
    "    # Iterate through each ingredient ID in the recipe\n",
    "    for ingredient_id in recipe_ingredients_aliases_df[recipe_ingredients_aliases_df['Recipe ID'] == recipe_id][\"Entity ID\"]:\n",
    "        # Add the category of the ingredient to the set\n",
    "        recipe_category_set.add(tmp_master_recipe_df[tmp_master_recipe_df[\"Entity ID\"] == ingredient_id].iloc[0][\"Category\"])\n",
    "    \n",
    "    # Check dietary rules based on the categories present in the recipe ingredients\n",
    "    if \"Meat\" not in recipe_category_set and \\\n",
    "            \"Fish\" not in recipe_category_set and \\\n",
    "                \"Seafood\" not in recipe_category_set and \\\n",
    "                    \"Eggs\" not in recipe_category_set and \\\n",
    "                        \"Dairy\" not in recipe_category_set and \\\n",
    "                            \"Dish\" not in recipe_category_set:\n",
    "        return \"Vegan\"\n",
    "    elif \"Meat\" not in recipe_category_set and \\\n",
    "            (\"Fish\" in recipe_category_set or \\\n",
    "                \"Seafood\" in recipe_category_set) and \\\n",
    "                        \"Dairy\" not in recipe_category_set and \\\n",
    "                            \"Dish\" not in recipe_category_set:\n",
    "        return \"Pescetarian\"\n",
    "    elif \"Meat\" not in recipe_category_set and \\\n",
    "            \"Fish\" not in recipe_category_set and \\\n",
    "                \"Seafood\" not in recipe_category_set and \\\n",
    "                    \"Eggs\" not in recipe_category_set and \\\n",
    "                        \"Dairy\" in recipe_category_set and \\\n",
    "                            \"Dish\" not in recipe_category_set:\n",
    "        return \"Lacto-Vegetarian\"\n",
    "    elif \"Meat\" not in recipe_category_set and \\\n",
    "            \"Fish\" not in recipe_category_set and \\\n",
    "                \"Seafood\" not in recipe_category_set and \\\n",
    "                    \"Eggs\" in recipe_category_set and \\\n",
    "                        \"Dairy\" not in recipe_category_set and \\\n",
    "                            \"Dish\" not in recipe_category_set:\n",
    "        return \"Ovo-Vegetarian\"\n",
    "    elif \"Meat\" not in recipe_category_set and \\\n",
    "            \"Fish\" not in recipe_category_set and \\\n",
    "                \"Seafood\" not in recipe_category_set and \\\n",
    "                    \"Eggs\" in recipe_category_set and \\\n",
    "                        \"Dairy\" in recipe_category_set and \\\n",
    "                            \"Dish\" not in recipe_category_set:\n",
    "        return \"Ovo-Lacto Vegetarian\"\n",
    "    elif \"Dish\" not in recipe_category_set:\n",
    "        return \"Non-Vegetarian\"\n",
    "    # If no specific rules match, it falls under \"Other\" category (Optional)\n",
    "    # else:\n",
    "    #     return \"Other\"\n",
    "\n",
    "# Apply the dietary rules to each recipe ID in the DataFrame and create a new column \"Dietary Style\"\n",
    "tmp_dietary_df['Dietary Style'] = tmp_dietary_df['Recipe ID'].swifter.apply(apply_dietary_rules)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1406,
   "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>Recipe ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Cuisine</th>\n",
       "      <th>Aliased Ingredient Name</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>Category</th>\n",
       "      <th>Dietary Style</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>capsicum</td>\n",
       "      <td>362</td>\n",
       "      <td>Vegetable</td>\n",
       "      <td>Vegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>pepper bell</td>\n",
       "      <td>362</td>\n",
       "      <td>Vegetable</td>\n",
       "      <td>Vegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>soy sauce</td>\n",
       "      <td>291</td>\n",
       "      <td>Plant</td>\n",
       "      <td>Vegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>sunflower</td>\n",
       "      <td>426</td>\n",
       "      <td>Flower</td>\n",
       "      <td>Vegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>aachar aaloo</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>buttermilk</td>\n",
       "      <td>61</td>\n",
       "      <td>Dairy</td>\n",
       "      <td>Lacto-Vegetarian</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>454138</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>salt</td>\n",
       "      <td>778</td>\n",
       "      <td>Additive</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454139</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>squash yellow</td>\n",
       "      <td>493</td>\n",
       "      <td>Fruit</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454140</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>thyme</td>\n",
       "      <td>269</td>\n",
       "      <td>Herb</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454141</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>worcestershire sauce</td>\n",
       "      <td>2031</td>\n",
       "      <td>Dish</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454142</th>\n",
       "      <td>45772</td>\n",
       "      <td>Enlightened Chicken Pot Pie</td>\n",
       "      <td>USA</td>\n",
       "      <td>zucchini</td>\n",
       "      <td>969</td>\n",
       "      <td>Vegetable</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>454143 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Recipe ID                         Title              Cuisine  \\\n",
       "0               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "1               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "2               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "3               1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "4               2                  aachar aaloo  Indian Subcontinent   \n",
       "...           ...                           ...                  ...   \n",
       "454138      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454139      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454140      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454141      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "454142      45772   Enlightened Chicken Pot Pie                  USA   \n",
       "\n",
       "       Aliased Ingredient Name  Entity ID   Category     Dietary Style  \n",
       "0                     capsicum        362  Vegetable             Vegan  \n",
       "1                  pepper bell        362  Vegetable             Vegan  \n",
       "2                    soy sauce        291      Plant             Vegan  \n",
       "3                    sunflower        426     Flower             Vegan  \n",
       "4                   buttermilk         61      Dairy  Lacto-Vegetarian  \n",
       "...                        ...        ...        ...               ...  \n",
       "454138                    salt        778   Additive              None  \n",
       "454139           squash yellow        493      Fruit              None  \n",
       "454140                   thyme        269       Herb              None  \n",
       "454141    worcestershire sauce       2031       Dish              None  \n",
       "454142                zucchini        969  Vegetable              None  \n",
       "\n",
       "[454143 rows x 7 columns]"
      ]
     },
     "execution_count": 1406,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_recipe_df = master_recipe_df.merge(tmp_dietary_df, on='Recipe ID', how='left')\n",
    "del tmp_dietary_df\n",
    "master_recipe_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save datasets for analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1411,
   "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>index</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>Aliased Ingredient Name</th>\n",
       "      <th>Synonyms</th>\n",
       "      <th>Scientific Name</th>\n",
       "      <th>Category</th>\n",
       "      <th>Molecules</th>\n",
       "      <th>Group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>egg</td>\n",
       "      <td>{egg}</td>\n",
       "      <td>chicken</td>\n",
       "      <td>animal product</td>\n",
       "      <td>{6274, 5311110, 644104, 9609, 18827, 527, 1960...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>bakery products</td>\n",
       "      <td>{bakery products}</td>\n",
       "      <td>poacceae</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{27457, 7976, 31252, 26808, 22201, 26331}</td>\n",
       "      <td>grain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>bread</td>\n",
       "      <td>{bread}</td>\n",
       "      <td>poacceae</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{1031, 1032, 644104, 527, 8723, 31260, 15394, ...</td>\n",
       "      <td>grain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>rye bread</td>\n",
       "      <td>{rye bread}</td>\n",
       "      <td>rye</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{644104, 7824, 643731, 8468, 1049, 5372954, 80...</td>\n",
       "      <td>grain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>wheaten bread</td>\n",
       "      <td>{soda farls, soda scones}</td>\n",
       "      <td>wheat</td>\n",
       "      <td>bakery</td>\n",
       "      <td>{5365891, 6915, 1146, 12170, 8082, 31251, 7958...</td>\n",
       "      <td>grain</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  Entity ID Aliased Ingredient Name                   Synonyms  \\\n",
       "0      0          0                     egg                      {egg}   \n",
       "1      1          1         bakery products          {bakery products}   \n",
       "2      2          2                   bread                    {bread}   \n",
       "3      3          3               rye bread                {rye bread}   \n",
       "4      4          4           wheaten bread  {soda farls, soda scones}   \n",
       "\n",
       "  Scientific Name        Category  \\\n",
       "0         chicken  animal product   \n",
       "1        poacceae          bakery   \n",
       "2        poacceae          bakery   \n",
       "3             rye          bakery   \n",
       "4           wheat          bakery   \n",
       "\n",
       "                                           Molecules  Group  \n",
       "0  {6274, 5311110, 644104, 9609, 18827, 527, 1960...   None  \n",
       "1          {27457, 7976, 31252, 26808, 22201, 26331}  grain  \n",
       "2  {1031, 1032, 644104, 527, 8723, 31260, 15394, ...  grain  \n",
       "3  {644104, 7824, 643731, 8468, 1049, 5372954, 80...  grain  \n",
       "4  {5365891, 6915, 1146, 12170, 8082, 31251, 7958...  grain  "
      ]
     },
     "execution_count": 1411,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ridf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1407,
   "metadata": {},
   "outputs": [],
   "source": [
    "ridf.to_pickle('ProcessedDBs/flavordb_ridf.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1412,
   "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>Recipe ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Cuisine</th>\n",
       "      <th>Aliased Ingredient Name</th>\n",
       "      <th>Entity ID</th>\n",
       "      <th>Category</th>\n",
       "      <th>Dietary Style</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>capsicum</td>\n",
       "      <td>362</td>\n",
       "      <td>Vegetable</td>\n",
       "      <td>Vegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>pepper bell</td>\n",
       "      <td>362</td>\n",
       "      <td>Vegetable</td>\n",
       "      <td>Vegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>soy sauce</td>\n",
       "      <td>291</td>\n",
       "      <td>Plant</td>\n",
       "      <td>Vegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>5 spice vegetable fried rice</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>sunflower</td>\n",
       "      <td>426</td>\n",
       "      <td>Flower</td>\n",
       "      <td>Vegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>aachar aaloo</td>\n",
       "      <td>Indian Subcontinent</td>\n",
       "      <td>buttermilk</td>\n",
       "      <td>61</td>\n",
       "      <td>Dairy</td>\n",
       "      <td>Lacto-Vegetarian</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Recipe ID                         Title              Cuisine  \\\n",
       "0          1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "1          1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "2          1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "3          1  5 spice vegetable fried rice  Indian Subcontinent   \n",
       "4          2                  aachar aaloo  Indian Subcontinent   \n",
       "\n",
       "  Aliased Ingredient Name  Entity ID   Category     Dietary Style  \n",
       "0                capsicum        362  Vegetable             Vegan  \n",
       "1             pepper bell        362  Vegetable             Vegan  \n",
       "2               soy sauce        291      Plant             Vegan  \n",
       "3               sunflower        426     Flower             Vegan  \n",
       "4              buttermilk         61      Dairy  Lacto-Vegetarian  "
      ]
     },
     "execution_count": 1412,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_recipe_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1408,
   "metadata": {},
   "outputs": [],
   "source": [
    "master_recipe_df.to_pickle('ProcessedDBs/master_recipe_df.pkl')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "3c95b85acbd73f4639c3d13015661b76eb28915a256ecb269f6e93b7a6d771c9"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}