prepare_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prepare Recipe data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:23.828652Z",
     "iopub.status.busy": "2024-08-06T10:10:23.828351Z",
     "iopub.status.idle": "2024-08-06T10:10:37.160999Z",
     "shell.execute_reply": "2024-08-06T10:10:37.160685Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import constants\n",
    "import json\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.163640Z",
     "iopub.status.busy": "2024-08-06T10:10:37.163485Z",
     "iopub.status.idle": "2024-08-06T10:10:37.166217Z",
     "shell.execute_reply": "2024-08-06T10:10:37.165976Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PosixPath('/Users/shubham/Desktop/shubham/upliance')"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "constants.ROOT_DIR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.181990Z",
     "iopub.status.busy": "2024-08-06T10:10:37.181861Z",
     "iopub.status.idle": "2024-08-06T10:10:37.187555Z",
     "shell.execute_reply": "2024-08-06T10:10:37.187332Z"
    }
   },
   "outputs": [],
   "source": [
    "# load data\n",
    "with open(constants.ROOT_DIR / \"recipe_category.json\", 'r') as f:\n",
    "    recipe_cat_dict = json.load(f)\n",
    "\n",
    "with open(constants.ROOT_DIR / \"recipe_ingredients.json\", 'r') as f:\n",
    "    recipe_ingredients_dict = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.188918Z",
     "iopub.status.busy": "2024-08-06T10:10:37.188823Z",
     "iopub.status.idle": "2024-08-06T10:10:37.199732Z",
     "shell.execute_reply": "2024-08-06T10:10:37.199532Z"
    }
   },
   "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>category_name</th>\n",
       "      <th>is_active</th>\n",
       "      <th>id</th>\n",
       "      <th>rank</th>\n",
       "      <th>Recipe_Categories_Map</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Homely Recipes</td>\n",
       "      <td>True</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'Recipe_ID': '5c7c1c8a-582c-48f7-9739-bcca75...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>North Indian</td>\n",
       "      <td>True</td>\n",
       "      <td>120</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'Recipe_ID': '18d527d2-19ac-46f4-a0ff-8d68b2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Healthy Recipes</td>\n",
       "      <td>True</td>\n",
       "      <td>119</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'Recipe_ID': 'bM2PYm6kCCo6EmuPNQ11'}, {'Reci...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fusion Food</td>\n",
       "      <td>True</td>\n",
       "      <td>118</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'Recipe_ID': 'dfc05dd1-3466-42a5-ac4d-063603...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>South Indian</td>\n",
       "      <td>True</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'Recipe_ID': '5756b48b-dc97-44d0-b699-bad97d...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     category_name  is_active   id  rank  \\\n",
       "0   Homely Recipes       True   69     0   \n",
       "1     North Indian       True  120     0   \n",
       "2  Healthy Recipes       True  119     0   \n",
       "3      Fusion Food       True  118     0   \n",
       "4     South Indian       True  117     0   \n",
       "\n",
       "                               Recipe_Categories_Map  \n",
       "0  [{'Recipe_ID': '5c7c1c8a-582c-48f7-9739-bcca75...  \n",
       "1  [{'Recipe_ID': '18d527d2-19ac-46f4-a0ff-8d68b2...  \n",
       "2  [{'Recipe_ID': 'bM2PYm6kCCo6EmuPNQ11'}, {'Reci...  \n",
       "3  [{'Recipe_ID': 'dfc05dd1-3466-42a5-ac4d-063603...  \n",
       "4  [{'Recipe_ID': '5756b48b-dc97-44d0-b699-bad97d...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "category_df = pd.json_normalize(recipe_cat_dict['data']['recipe_categories'])\n",
    "category_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.200951Z",
     "iopub.status.busy": "2024-08-06T10:10:37.200876Z",
     "iopub.status.idle": "2024-08-06T10:10:37.206403Z",
     "shell.execute_reply": "2024-08-06T10:10:37.206189Z"
    }
   },
   "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>category_id</th>\n",
       "      <th>recipe_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>69</td>\n",
       "      <td>5c7c1c8a-582c-48f7-9739-bcca7511ac7b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>69</td>\n",
       "      <td>98061e99-3fd6-486e-a332-1b2b42eff358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>69</td>\n",
       "      <td>99c9143a-489e-4e2c-afdd-a6ae8975e760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>69</td>\n",
       "      <td>3bf5afd4-4f93-4ade-8ceb-caea8c1faeee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>69</td>\n",
       "      <td>b82a3f5e-2c77-4256-bb02-a60d1cde1754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   category_id                             recipe_id\n",
       "0           69  5c7c1c8a-582c-48f7-9739-bcca7511ac7b\n",
       "0           69  98061e99-3fd6-486e-a332-1b2b42eff358\n",
       "0           69  99c9143a-489e-4e2c-afdd-a6ae8975e760\n",
       "0           69  3bf5afd4-4f93-4ade-8ceb-caea8c1faeee\n",
       "0           69  b82a3f5e-2c77-4256-bb02-a60d1cde1754"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Category Recipe map\n",
    "category_recipe_map = (\n",
    "    category_df\n",
    "    # dropping Recommende for you category\n",
    "    .loc[lambda df_: df_['category_name'] != 'Recommended For You']\n",
    "    [['id', 'Recipe_Categories_Map']]\n",
    "    .explode('Recipe_Categories_Map')\n",
    "    .loc\n",
    "    [lambda df_: ~df_['Recipe_Categories_Map'].isnull()]\n",
    "    .assign(recipe_id = lambda df_: df_['Recipe_Categories_Map'].map(lambda x: x['Recipe_ID']))\n",
    "    .drop(['Recipe_Categories_Map'], axis=1)\n",
    "    .rename(columns={\"id\": \"category_id\"})\n",
    ")\n",
    "category_recipe_map.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.207650Z",
     "iopub.status.busy": "2024-08-06T10:10:37.207572Z",
     "iopub.status.idle": "2024-08-06T10:10:37.210598Z",
     "shell.execute_reply": "2024-08-06T10:10:37.210415Z"
    }
   },
   "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>category_name</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Homely Recipes</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>North Indian</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Healthy Recipes</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fusion Food</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>South Indian</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     category_name   id\n",
       "0   Homely Recipes   69\n",
       "1     North Indian  120\n",
       "2  Healthy Recipes  119\n",
       "3      Fusion Food  118\n",
       "4     South Indian  117"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Category\n",
    "category_df = (\n",
    "    category_df\n",
    "    .drop(['is_active', 'rank', 'Recipe_Categories_Map'], axis=1)\n",
    "    # deleting recommended for you catefory\n",
    "    .loc[lambda df_: df_['category_name'] != 'Recommended For You']\n",
    ")\n",
    "category_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.211803Z",
     "iopub.status.busy": "2024-08-06T10:10:37.211729Z",
     "iopub.status.idle": "2024-08-06T10:10:37.219039Z",
     "shell.execute_reply": "2024-08-06T10:10:37.218832Z"
    }
   },
   "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>name</th>\n",
       "      <th>cuisine</th>\n",
       "      <th>hidden_tags</th>\n",
       "      <th>diet</th>\n",
       "      <th>description</th>\n",
       "      <th>meal_type</th>\n",
       "      <th>active</th>\n",
       "      <th>is_public</th>\n",
       "      <th>created_source</th>\n",
       "      <th>Recipe_Variations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>Zucchini Curry</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>Zucchini curry is a vegetarian dish that featu...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>recipeAdmin</td>\n",
       "      <td>[{'description': 'Zucchini curry is a vegetari...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b7f22d40-8a95-4877-8d50-0f493c61738a</td>\n",
       "      <td>Gujarati Kadhi</td>\n",
       "      <td>[Indian, Gujarati]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Gujarati kadhi is a sweet tangy yogurt-based d...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>recipeAdmin</td>\n",
       "      <td>[{'description': 'Gujarati kadhi is a sweet ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e</td>\n",
       "      <td>Sabudana Khichdi</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Sabudana Khichdi is a popular Indian dish made...</td>\n",
       "      <td>[Breakfast]</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>recipeAdmin</td>\n",
       "      <td>[{'description': 'Sabudana Khichdi is a popula...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f5e954c2-821a-4541-a3b0-d126baa02ac5</td>\n",
       "      <td>Beetroot Palya</td>\n",
       "      <td>[Indian, South Indian]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>This recipe is prepared using beetroot, grated...</td>\n",
       "      <td>[Main Course Dry]</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>recipeAdmin</td>\n",
       "      <td>[{'description': 'This recipe is prepared usin...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>f7b53d60-5c24-4512-bba5-90af1f27f8ce</td>\n",
       "      <td>Jain Red Sauce Pasta</td>\n",
       "      <td>[Italian]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Red sauce pasta on autopilot. Lightly flavoure...</td>\n",
       "      <td>[One Pot Meal]</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>recipeAdmin</td>\n",
       "      <td>[{'description': 'Red sauce pasta on autopilot...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                   name  \\\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae         Zucchini Curry   \n",
       "1  b7f22d40-8a95-4877-8d50-0f493c61738a         Gujarati Kadhi   \n",
       "2  a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e       Sabudana Khichdi   \n",
       "3  f5e954c2-821a-4541-a3b0-d126baa02ac5         Beetroot Palya   \n",
       "4  f7b53d60-5c24-4512-bba5-90af1f27f8ce  Jain Red Sauce Pasta    \n",
       "\n",
       "                  cuisine hidden_tags     diet  \\\n",
       "0                [Indian]          []  [Vegan]   \n",
       "1      [Indian, Gujarati]          []    [Veg]   \n",
       "2                [Indian]          []    [Veg]   \n",
       "3  [Indian, South Indian]          []  [Vegan]   \n",
       "4               [Italian]          []    [Veg]   \n",
       "\n",
       "                                         description            meal_type  \\\n",
       "0  Zucchini curry is a vegetarian dish that featu...  [Main Course Gravy]   \n",
       "1  Gujarati kadhi is a sweet tangy yogurt-based d...  [Main Course Gravy]   \n",
       "2  Sabudana Khichdi is a popular Indian dish made...          [Breakfast]   \n",
       "3  This recipe is prepared using beetroot, grated...    [Main Course Dry]   \n",
       "4  Red sauce pasta on autopilot. Lightly flavoure...       [One Pot Meal]   \n",
       "\n",
       "   active  is_public created_source  \\\n",
       "0    True       True    recipeAdmin   \n",
       "1    True       True    recipeAdmin   \n",
       "2    True       True    recipeAdmin   \n",
       "3    True       True    recipeAdmin   \n",
       "4    True       True    recipeAdmin   \n",
       "\n",
       "                                   Recipe_Variations  \n",
       "0  [{'description': 'Zucchini curry is a vegetari...  \n",
       "1  [{'description': 'Gujarati kadhi is a sweet ta...  \n",
       "2  [{'description': 'Sabudana Khichdi is a popula...  \n",
       "3  [{'description': 'This recipe is prepared usin...  \n",
       "4  [{'description': 'Red sauce pasta on autopilot...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipe_df = pd.json_normalize(recipe_ingredients_dict['data']['Recipes_E3'])\n",
    "recipe_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.220177Z",
     "iopub.status.busy": "2024-08-06T10:10:37.220104Z",
     "iopub.status.idle": "2024-08-06T10:10:37.227122Z",
     "shell.execute_reply": "2024-08-06T10:10:37.226919Z"
    }
   },
   "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>name</th>\n",
       "      <th>cuisine</th>\n",
       "      <th>diet</th>\n",
       "      <th>description</th>\n",
       "      <th>meal_type</th>\n",
       "      <th>image_url</th>\n",
       "      <th>cooking_time</th>\n",
       "      <th>prep_time</th>\n",
       "      <th>ingredients</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>Zucchini Curry</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>Zucchini curry is a vegetarian dish that featu...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>66.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[YZJypF7J7qoqJ5JcuzXs, jVF1elDY6pZ1zZfoWO9K, k...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b7f22d40-8a95-4877-8d50-0f493c61738a</td>\n",
       "      <td>Gujarati Kadhi</td>\n",
       "      <td>[Indian, Gujarati]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Gujarati kadhi is a sweet tangy yogurt-based d...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[SSkQlAri5V5Ip377WXqQ, k01MJnr4IWnpM9vUG9Cp, 4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e</td>\n",
       "      <td>Sabudana Khichdi</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Sabudana Khichdi is a popular Indian dish made...</td>\n",
       "      <td>[Breakfast]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>58.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>[b8301341-efcd-425c-86f0-e282415f4a9b, OqJKyZC...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f5e954c2-821a-4541-a3b0-d126baa02ac5</td>\n",
       "      <td>Beetroot Palya</td>\n",
       "      <td>[Indian, South Indian]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>This recipe is prepared using beetroot, grated...</td>\n",
       "      <td>[Main Course Dry]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[jVF1elDY6pZ1zZfoWO9K, 1Ky6vVG6LIdXgQ77KpUf, k...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>f7b53d60-5c24-4512-bba5-90af1f27f8ce</td>\n",
       "      <td>Jain Red Sauce Pasta</td>\n",
       "      <td>[Italian]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Red sauce pasta on autopilot. Lightly flavoure...</td>\n",
       "      <td>[One Pot Meal]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[cJvpPYfSg5s1aEaEzPv8, 1Ky6vVG6LIdXgQ77KpUf, L...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                   name  \\\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae         Zucchini Curry   \n",
       "1  b7f22d40-8a95-4877-8d50-0f493c61738a         Gujarati Kadhi   \n",
       "2  a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e       Sabudana Khichdi   \n",
       "3  f5e954c2-821a-4541-a3b0-d126baa02ac5         Beetroot Palya   \n",
       "4  f7b53d60-5c24-4512-bba5-90af1f27f8ce  Jain Red Sauce Pasta    \n",
       "\n",
       "                  cuisine     diet  \\\n",
       "0                [Indian]  [Vegan]   \n",
       "1      [Indian, Gujarati]    [Veg]   \n",
       "2                [Indian]    [Veg]   \n",
       "3  [Indian, South Indian]  [Vegan]   \n",
       "4               [Italian]    [Veg]   \n",
       "\n",
       "                                         description            meal_type  \\\n",
       "0  Zucchini curry is a vegetarian dish that featu...  [Main Course Gravy]   \n",
       "1  Gujarati kadhi is a sweet tangy yogurt-based d...  [Main Course Gravy]   \n",
       "2  Sabudana Khichdi is a popular Indian dish made...          [Breakfast]   \n",
       "3  This recipe is prepared using beetroot, grated...    [Main Course Dry]   \n",
       "4  Red sauce pasta on autopilot. Lightly flavoure...       [One Pot Meal]   \n",
       "\n",
       "                                           image_url  cooking_time  prep_time  \\\n",
       "0  https://res.cloudinary.com/dgakjrw8j/image/upl...          66.0        0.0   \n",
       "1  https://res.cloudinary.com/dgakjrw8j/image/upl...          36.0        0.0   \n",
       "2  https://res.cloudinary.com/dgakjrw8j/image/upl...          58.0      240.0   \n",
       "3  https://res.cloudinary.com/dgakjrw8j/image/upl...          31.0        0.0   \n",
       "4  https://res.cloudinary.com/dgakjrw8j/image/upl...          46.0        0.0   \n",
       "\n",
       "                                         ingredients  \n",
       "0  [YZJypF7J7qoqJ5JcuzXs, jVF1elDY6pZ1zZfoWO9K, k...  \n",
       "1  [SSkQlAri5V5Ip377WXqQ, k01MJnr4IWnpM9vUG9Cp, 4...  \n",
       "2  [b8301341-efcd-425c-86f0-e282415f4a9b, OqJKyZC...  \n",
       "3  [jVF1elDY6pZ1zZfoWO9K, 1Ky6vVG6LIdXgQ77KpUf, k...  \n",
       "4  [cJvpPYfSg5s1aEaEzPv8, 1Ky6vVG6LIdXgQ77KpUf, L...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Recipe\n",
    "recipe_df = (\n",
    "    recipe_df\n",
    "    .assign(Recipe_Variations= lambda df_: df_['Recipe_Variations'].map(lambda x: x[0]))\n",
    "    .assign(\n",
    "        image_url = lambda df_: df_['Recipe_Variations'].map(lambda x: x['image_url']),\n",
    "        cooking_time = lambda df_: df_['Recipe_Variations'].map(lambda x: x['cooking_time']),\n",
    "        prep_time = lambda df_: df_['Recipe_Variations'].map(lambda x: x['prep_time']),\n",
    "        ingredients = lambda df_: df_['Recipe_Variations'].map(lambda x: x['ingredients'])\n",
    "    )\n",
    "    .drop(['hidden_tags', 'active', 'is_public', 'created_source', 'Recipe_Variations',], axis=1)\n",
    ")\n",
    "recipe_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.228346Z",
     "iopub.status.busy": "2024-08-06T10:10:37.228270Z",
     "iopub.status.idle": "2024-08-06T10:10:37.232127Z",
     "shell.execute_reply": "2024-08-06T10:10:37.231922Z"
    }
   },
   "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>ingredients_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>YZJypF7J7qoqJ5JcuzXs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>jVF1elDY6pZ1zZfoWO9K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>k01MJnr4IWnpM9vUG9Cp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>n7nLaGcBeHJg54aNs7eb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>Jvrv1829TmalCfkvRg7z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              recipe_id        ingredients_id\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae  YZJypF7J7qoqJ5JcuzXs\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae  jVF1elDY6pZ1zZfoWO9K\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae  k01MJnr4IWnpM9vUG9Cp\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae  n7nLaGcBeHJg54aNs7eb\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae  Jvrv1829TmalCfkvRg7z"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Recipe Ingredients map\n",
    "recipe_ingredients_df = (\n",
    "    recipe_df[['id', 'ingredients']]\n",
    "    .rename(columns={'id': \"recipe_id\", \"ingredients\": \"ingredients_id\"})\n",
    "    .explode('ingredients_id')\n",
    ")\n",
    "recipe_ingredients_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.233363Z",
     "iopub.status.busy": "2024-08-06T10:10:37.233282Z",
     "iopub.status.idle": "2024-08-06T10:10:37.237983Z",
     "shell.execute_reply": "2024-08-06T10:10:37.237769Z"
    }
   },
   "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>name</th>\n",
       "      <th>cuisine</th>\n",
       "      <th>diet</th>\n",
       "      <th>description</th>\n",
       "      <th>meal_type</th>\n",
       "      <th>image_url</th>\n",
       "      <th>cooking_time</th>\n",
       "      <th>prep_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>Zucchini Curry</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>Zucchini curry is a vegetarian dish that featu...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>66.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b7f22d40-8a95-4877-8d50-0f493c61738a</td>\n",
       "      <td>Gujarati Kadhi</td>\n",
       "      <td>[Indian, Gujarati]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Gujarati kadhi is a sweet tangy yogurt-based d...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e</td>\n",
       "      <td>Sabudana Khichdi</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Sabudana Khichdi is a popular Indian dish made...</td>\n",
       "      <td>[Breakfast]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>58.0</td>\n",
       "      <td>240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f5e954c2-821a-4541-a3b0-d126baa02ac5</td>\n",
       "      <td>Beetroot Palya</td>\n",
       "      <td>[Indian, South Indian]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>This recipe is prepared using beetroot, grated...</td>\n",
       "      <td>[Main Course Dry]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>f7b53d60-5c24-4512-bba5-90af1f27f8ce</td>\n",
       "      <td>Jain Red Sauce Pasta</td>\n",
       "      <td>[Italian]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Red sauce pasta on autopilot. Lightly flavoure...</td>\n",
       "      <td>[One Pot Meal]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                   name  \\\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae         Zucchini Curry   \n",
       "1  b7f22d40-8a95-4877-8d50-0f493c61738a         Gujarati Kadhi   \n",
       "2  a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e       Sabudana Khichdi   \n",
       "3  f5e954c2-821a-4541-a3b0-d126baa02ac5         Beetroot Palya   \n",
       "4  f7b53d60-5c24-4512-bba5-90af1f27f8ce  Jain Red Sauce Pasta    \n",
       "\n",
       "                  cuisine     diet  \\\n",
       "0                [Indian]  [Vegan]   \n",
       "1      [Indian, Gujarati]    [Veg]   \n",
       "2                [Indian]    [Veg]   \n",
       "3  [Indian, South Indian]  [Vegan]   \n",
       "4               [Italian]    [Veg]   \n",
       "\n",
       "                                         description            meal_type  \\\n",
       "0  Zucchini curry is a vegetarian dish that featu...  [Main Course Gravy]   \n",
       "1  Gujarati kadhi is a sweet tangy yogurt-based d...  [Main Course Gravy]   \n",
       "2  Sabudana Khichdi is a popular Indian dish made...          [Breakfast]   \n",
       "3  This recipe is prepared using beetroot, grated...    [Main Course Dry]   \n",
       "4  Red sauce pasta on autopilot. Lightly flavoure...       [One Pot Meal]   \n",
       "\n",
       "                                           image_url  cooking_time  prep_time  \n",
       "0  https://res.cloudinary.com/dgakjrw8j/image/upl...          66.0        0.0  \n",
       "1  https://res.cloudinary.com/dgakjrw8j/image/upl...          36.0        0.0  \n",
       "2  https://res.cloudinary.com/dgakjrw8j/image/upl...          58.0      240.0  \n",
       "3  https://res.cloudinary.com/dgakjrw8j/image/upl...          31.0        0.0  \n",
       "4  https://res.cloudinary.com/dgakjrw8j/image/upl...          46.0        0.0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dropping ingredients column from recipe_df\n",
    "recipe_df = recipe_df.drop(['ingredients'], axis=1)\n",
    "recipe_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.239189Z",
     "iopub.status.busy": "2024-08-06T10:10:37.239115Z",
     "iopub.status.idle": "2024-08-06T10:10:37.244966Z",
     "shell.execute_reply": "2024-08-06T10:10:37.244765Z"
    }
   },
   "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>displayName</th>\n",
       "      <th>allergens</th>\n",
       "      <th>_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rice Cakes</td>\n",
       "      <td>[]</td>\n",
       "      <td>78a1dd37-5ea4-473d-bea9-60e73fa74af9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fish Cake</td>\n",
       "      <td>[]</td>\n",
       "      <td>4c3c805b-6169-4e84-b2c0-67dee660e97e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>new ingredient</td>\n",
       "      <td>[]</td>\n",
       "      <td>55c89473-b57f-4394-b65b-f7edd817ffd8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shredded Cabbage</td>\n",
       "      <td>[]</td>\n",
       "      <td>9b5533eb-46b8-4ad7-8296-ea4ee3619f53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tomato Puree</td>\n",
       "      <td>[]</td>\n",
       "      <td>a1d7c043-7dd9-449c-9f97-ab7b0a372f46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        displayName allergens                                   _id\n",
       "0        Rice Cakes        []  78a1dd37-5ea4-473d-bea9-60e73fa74af9\n",
       "1         Fish Cake        []  4c3c805b-6169-4e84-b2c0-67dee660e97e\n",
       "2    new ingredient        []  55c89473-b57f-4394-b65b-f7edd817ffd8\n",
       "3  Shredded Cabbage        []  9b5533eb-46b8-4ad7-8296-ea4ee3619f53\n",
       "4      Tomato Puree        []  a1d7c043-7dd9-449c-9f97-ab7b0a372f46"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ingredients_df = pd.json_normalize(recipe_ingredients_dict['data']['Ingredients_DB'])\n",
    "ingredients_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.246134Z",
     "iopub.status.busy": "2024-08-06T10:10:37.246061Z",
     "iopub.status.idle": "2024-08-06T10:10:37.248942Z",
     "shell.execute_reply": "2024-08-06T10:10:37.248734Z"
    }
   },
   "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>name</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rice Cakes</td>\n",
       "      <td>78a1dd37-5ea4-473d-bea9-60e73fa74af9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fish Cake</td>\n",
       "      <td>4c3c805b-6169-4e84-b2c0-67dee660e97e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>new ingredient</td>\n",
       "      <td>55c89473-b57f-4394-b65b-f7edd817ffd8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shredded Cabbage</td>\n",
       "      <td>9b5533eb-46b8-4ad7-8296-ea4ee3619f53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tomato Puree</td>\n",
       "      <td>a1d7c043-7dd9-449c-9f97-ab7b0a372f46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name                                    id\n",
       "0        Rice Cakes  78a1dd37-5ea4-473d-bea9-60e73fa74af9\n",
       "1         Fish Cake  4c3c805b-6169-4e84-b2c0-67dee660e97e\n",
       "2    new ingredient  55c89473-b57f-4394-b65b-f7edd817ffd8\n",
       "3  Shredded Cabbage  9b5533eb-46b8-4ad7-8296-ea4ee3619f53\n",
       "4      Tomato Puree  a1d7c043-7dd9-449c-9f97-ab7b0a372f46"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ingredients\n",
    "ingredients_df = ingredients_df.drop(['allergens'], axis=1).rename(columns={'displayName': \"name\", \"_id\": \"id\"})\n",
    "ingredients_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.250135Z",
     "iopub.status.busy": "2024-08-06T10:10:37.250058Z",
     "iopub.status.idle": "2024-08-06T10:10:37.269964Z",
     "shell.execute_reply": "2024-08-06T10:10:37.269742Z"
    }
   },
   "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>name</th>\n",
       "      <th>cuisine</th>\n",
       "      <th>diet</th>\n",
       "      <th>description</th>\n",
       "      <th>meal_type</th>\n",
       "      <th>image_url</th>\n",
       "      <th>cooking_time</th>\n",
       "      <th>prep_time</th>\n",
       "      <th>category_names</th>\n",
       "      <th>ingredients_names</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>Zucchini Curry</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>Zucchini curry is a vegetarian dish that featu...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>66.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[Perfect With Chapati, Fusion Recipes]</td>\n",
       "      <td>[Zucchini, Refined Oil, Jeera, Onion, Garlic, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b7f22d40-8a95-4877-8d50-0f493c61738a</td>\n",
       "      <td>Gujarati Kadhi</td>\n",
       "      <td>[Indian, Gujarati]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Gujarati kadhi is a sweet tangy yogurt-based d...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[Vegetarian Delights, Kem Cho Gujarat!]</td>\n",
       "      <td>[Mustard Seeds, Jeera, Hing, Dried Red Chilli,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e</td>\n",
       "      <td>Sabudana Khichdi</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Sabudana Khichdi is a popular Indian dish made...</td>\n",
       "      <td>[Breakfast]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>58.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>[Breakfasts]</td>\n",
       "      <td>[Medium Tapioca Pearls, Water, Peanuts (Raw), ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f5e954c2-821a-4541-a3b0-d126baa02ac5</td>\n",
       "      <td>Beetroot Palya</td>\n",
       "      <td>[Indian, South Indian]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>This recipe is prepared using beetroot, grated...</td>\n",
       "      <td>[Main Course Dry]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[Homely Recipes, Side Dish Specialities]</td>\n",
       "      <td>[Refined Oil, Green Chilli, Jeera, Mustard See...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>f7b53d60-5c24-4512-bba5-90af1f27f8ce</td>\n",
       "      <td>Jain Red Sauce Pasta</td>\n",
       "      <td>[Italian]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Red sauce pasta on autopilot. Lightly flavoure...</td>\n",
       "      <td>[One Pot Meal]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[Jain Friendly Meals]</td>\n",
       "      <td>[Parsley, Green Chilli, Pomace Olive Oil, Toma...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                   name  \\\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae         Zucchini Curry   \n",
       "1  b7f22d40-8a95-4877-8d50-0f493c61738a         Gujarati Kadhi   \n",
       "2  a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e       Sabudana Khichdi   \n",
       "3  f5e954c2-821a-4541-a3b0-d126baa02ac5         Beetroot Palya   \n",
       "4  f7b53d60-5c24-4512-bba5-90af1f27f8ce  Jain Red Sauce Pasta    \n",
       "\n",
       "                  cuisine     diet  \\\n",
       "0                [Indian]  [Vegan]   \n",
       "1      [Indian, Gujarati]    [Veg]   \n",
       "2                [Indian]    [Veg]   \n",
       "3  [Indian, South Indian]  [Vegan]   \n",
       "4               [Italian]    [Veg]   \n",
       "\n",
       "                                         description            meal_type  \\\n",
       "0  Zucchini curry is a vegetarian dish that featu...  [Main Course Gravy]   \n",
       "1  Gujarati kadhi is a sweet tangy yogurt-based d...  [Main Course Gravy]   \n",
       "2  Sabudana Khichdi is a popular Indian dish made...          [Breakfast]   \n",
       "3  This recipe is prepared using beetroot, grated...    [Main Course Dry]   \n",
       "4  Red sauce pasta on autopilot. Lightly flavoure...       [One Pot Meal]   \n",
       "\n",
       "                                           image_url  cooking_time  prep_time  \\\n",
       "0  https://res.cloudinary.com/dgakjrw8j/image/upl...          66.0        0.0   \n",
       "1  https://res.cloudinary.com/dgakjrw8j/image/upl...          36.0        0.0   \n",
       "2  https://res.cloudinary.com/dgakjrw8j/image/upl...          58.0      240.0   \n",
       "3  https://res.cloudinary.com/dgakjrw8j/image/upl...          31.0        0.0   \n",
       "4  https://res.cloudinary.com/dgakjrw8j/image/upl...          46.0        0.0   \n",
       "\n",
       "                             category_names  \\\n",
       "0    [Perfect With Chapati, Fusion Recipes]   \n",
       "1   [Vegetarian Delights, Kem Cho Gujarat!]   \n",
       "2                              [Breakfasts]   \n",
       "3  [Homely Recipes, Side Dish Specialities]   \n",
       "4                     [Jain Friendly Meals]   \n",
       "\n",
       "                                   ingredients_names  \n",
       "0  [Zucchini, Refined Oil, Jeera, Onion, Garlic, ...  \n",
       "1  [Mustard Seeds, Jeera, Hing, Dried Red Chilli,...  \n",
       "2  [Medium Tapioca Pearls, Water, Peanuts (Raw), ...  \n",
       "3  [Refined Oil, Green Chilli, Jeera, Mustard See...  \n",
       "4  [Parsley, Green Chilli, Pomace Olive Oil, Toma...  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding ingredients names list and category names list in recipe_df\n",
    "recipe_category_name_map = (\n",
    "    category_recipe_map\n",
    "    .merge(category_df, left_on='category_id', right_on='id', how='inner')\n",
    "    .groupby('recipe_id', as_index=False)\n",
    "    .agg(**{\"category_names\": (\"category_name\", list)})\n",
    ")\n",
    "\n",
    "recipe_ingredients_name_map = (\n",
    "    recipe_ingredients_df\n",
    "    .merge(ingredients_df, left_on='ingredients_id', right_on='id', how='inner')\n",
    "    .groupby('recipe_id', as_index=False)\n",
    "    .agg(**{\"ingredients_names\": (\"name\", list)})\n",
    ")\n",
    "\n",
    "recipe_df = (\n",
    "    recipe_df\n",
    "    .merge(recipe_category_name_map, left_on='id', right_on='recipe_id', how='left')\n",
    "    .drop(['recipe_id'], axis=1)\n",
    "    .merge(recipe_ingredients_name_map, left_on='id', right_on='recipe_id', how='left')\n",
    "    .drop(['recipe_id'], axis=1)\n",
    "    # filling with empty list wherever ingredients name or category names is missing\n",
    "    .assign(\n",
    "        category_names = lambda df_: df_['category_names'].map(lambda x: [] if pd.isnull(x) is True else x),\n",
    "        ingredients_names = lambda df_: df_['ingredients_names'].map(lambda x: [] if pd.isnull(x) is True else x)\n",
    "    )\n",
    ")\n",
    "\n",
    "recipe_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.271379Z",
     "iopub.status.busy": "2024-08-06T10:10:37.271303Z",
     "iopub.status.idle": "2024-08-06T10:10:37.273544Z",
     "shell.execute_reply": "2024-08-06T10:10:37.273302Z"
    }
   },
   "outputs": [],
   "source": [
    "def make_recipe_document(row):\n",
    "    # id\tname\tcuisine\tdiet\tdescription\tmeal_type\timage_url\tcooking_time\tprep_time\tingredients\n",
    "    # print(row)\n",
    "    name: str =  row['name']\n",
    "    cuisine: list[str] = row['cuisine']\n",
    "    diet: list[str] = row['diet']\n",
    "    description: str = row['description']\n",
    "    meal_type: list[str] = row['meal_type']\n",
    "    category: list[str] = row['category_names']\n",
    "    ingredients: list[str] = row['ingredients_names']\n",
    "    document = (\n",
    "        f\"Recipe name is {name}.\\n\"\n",
    "        f\"{description}\\n\"\n",
    "        f\"Cuisine is {', '.join(cuisine)}.\\n\"\n",
    "        f\"Diet is {', '.join(diet)}.\\n\"\n",
    "        f\"Meal type is {', '.join(meal_type)}.\\n\"\n",
    "        f\"Food Categories are {', '.join(category)}.\\n\"\n",
    "        f\"Ingredients are {', '.join(ingredients)}.\"\n",
    "    )\n",
    "    return document"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.274889Z",
     "iopub.status.busy": "2024-08-06T10:10:37.274802Z",
     "iopub.status.idle": "2024-08-06T10:10:37.285894Z",
     "shell.execute_reply": "2024-08-06T10:10:37.285602Z"
    }
   },
   "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>name</th>\n",
       "      <th>cuisine</th>\n",
       "      <th>diet</th>\n",
       "      <th>description</th>\n",
       "      <th>meal_type</th>\n",
       "      <th>image_url</th>\n",
       "      <th>cooking_time</th>\n",
       "      <th>prep_time</th>\n",
       "      <th>category_names</th>\n",
       "      <th>ingredients_names</th>\n",
       "      <th>document</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54087d1b-6dab-403b-93eb-c33162f10aae</td>\n",
       "      <td>Zucchini Curry</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>Zucchini curry is a vegetarian dish that featu...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>66.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[Perfect With Chapati, Fusion Recipes]</td>\n",
       "      <td>[Zucchini, Refined Oil, Jeera, Onion, Garlic, ...</td>\n",
       "      <td>Recipe name is Zucchini Curry.\\nZucchini curry...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b7f22d40-8a95-4877-8d50-0f493c61738a</td>\n",
       "      <td>Gujarati Kadhi</td>\n",
       "      <td>[Indian, Gujarati]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Gujarati kadhi is a sweet tangy yogurt-based d...</td>\n",
       "      <td>[Main Course Gravy]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[Vegetarian Delights, Kem Cho Gujarat!]</td>\n",
       "      <td>[Mustard Seeds, Jeera, Hing, Dried Red Chilli,...</td>\n",
       "      <td>Recipe name is Gujarati Kadhi.\\nGujarati kadhi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e</td>\n",
       "      <td>Sabudana Khichdi</td>\n",
       "      <td>[Indian]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Sabudana Khichdi is a popular Indian dish made...</td>\n",
       "      <td>[Breakfast]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>58.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>[Breakfasts]</td>\n",
       "      <td>[Medium Tapioca Pearls, Water, Peanuts (Raw), ...</td>\n",
       "      <td>Recipe name is Sabudana Khichdi.\\nSabudana Khi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f5e954c2-821a-4541-a3b0-d126baa02ac5</td>\n",
       "      <td>Beetroot Palya</td>\n",
       "      <td>[Indian, South Indian]</td>\n",
       "      <td>[Vegan]</td>\n",
       "      <td>This recipe is prepared using beetroot, grated...</td>\n",
       "      <td>[Main Course Dry]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[Homely Recipes, Side Dish Specialities]</td>\n",
       "      <td>[Refined Oil, Green Chilli, Jeera, Mustard See...</td>\n",
       "      <td>Recipe name is Beetroot Palya.\\nThis recipe is...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>f7b53d60-5c24-4512-bba5-90af1f27f8ce</td>\n",
       "      <td>Jain Red Sauce Pasta</td>\n",
       "      <td>[Italian]</td>\n",
       "      <td>[Veg]</td>\n",
       "      <td>Red sauce pasta on autopilot. Lightly flavoure...</td>\n",
       "      <td>[One Pot Meal]</td>\n",
       "      <td>https://res.cloudinary.com/dgakjrw8j/image/upl...</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[Jain Friendly Meals]</td>\n",
       "      <td>[Parsley, Green Chilli, Pomace Olive Oil, Toma...</td>\n",
       "      <td>Recipe name is Jain Red Sauce Pasta .\\nRed sau...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                   name  \\\n",
       "0  54087d1b-6dab-403b-93eb-c33162f10aae         Zucchini Curry   \n",
       "1  b7f22d40-8a95-4877-8d50-0f493c61738a         Gujarati Kadhi   \n",
       "2  a15df9da-6efd-4bf1-ba6e-0f5fa0fc6a4e       Sabudana Khichdi   \n",
       "3  f5e954c2-821a-4541-a3b0-d126baa02ac5         Beetroot Palya   \n",
       "4  f7b53d60-5c24-4512-bba5-90af1f27f8ce  Jain Red Sauce Pasta    \n",
       "\n",
       "                  cuisine     diet  \\\n",
       "0                [Indian]  [Vegan]   \n",
       "1      [Indian, Gujarati]    [Veg]   \n",
       "2                [Indian]    [Veg]   \n",
       "3  [Indian, South Indian]  [Vegan]   \n",
       "4               [Italian]    [Veg]   \n",
       "\n",
       "                                         description            meal_type  \\\n",
       "0  Zucchini curry is a vegetarian dish that featu...  [Main Course Gravy]   \n",
       "1  Gujarati kadhi is a sweet tangy yogurt-based d...  [Main Course Gravy]   \n",
       "2  Sabudana Khichdi is a popular Indian dish made...          [Breakfast]   \n",
       "3  This recipe is prepared using beetroot, grated...    [Main Course Dry]   \n",
       "4  Red sauce pasta on autopilot. Lightly flavoure...       [One Pot Meal]   \n",
       "\n",
       "                                           image_url  cooking_time  prep_time  \\\n",
       "0  https://res.cloudinary.com/dgakjrw8j/image/upl...          66.0        0.0   \n",
       "1  https://res.cloudinary.com/dgakjrw8j/image/upl...          36.0        0.0   \n",
       "2  https://res.cloudinary.com/dgakjrw8j/image/upl...          58.0      240.0   \n",
       "3  https://res.cloudinary.com/dgakjrw8j/image/upl...          31.0        0.0   \n",
       "4  https://res.cloudinary.com/dgakjrw8j/image/upl...          46.0        0.0   \n",
       "\n",
       "                             category_names  \\\n",
       "0    [Perfect With Chapati, Fusion Recipes]   \n",
       "1   [Vegetarian Delights, Kem Cho Gujarat!]   \n",
       "2                              [Breakfasts]   \n",
       "3  [Homely Recipes, Side Dish Specialities]   \n",
       "4                     [Jain Friendly Meals]   \n",
       "\n",
       "                                   ingredients_names  \\\n",
       "0  [Zucchini, Refined Oil, Jeera, Onion, Garlic, ...   \n",
       "1  [Mustard Seeds, Jeera, Hing, Dried Red Chilli,...   \n",
       "2  [Medium Tapioca Pearls, Water, Peanuts (Raw), ...   \n",
       "3  [Refined Oil, Green Chilli, Jeera, Mustard See...   \n",
       "4  [Parsley, Green Chilli, Pomace Olive Oil, Toma...   \n",
       "\n",
       "                                            document  \n",
       "0  Recipe name is Zucchini Curry.\\nZucchini curry...  \n",
       "1  Recipe name is Gujarati Kadhi.\\nGujarati kadhi...  \n",
       "2  Recipe name is Sabudana Khichdi.\\nSabudana Khi...  \n",
       "3  Recipe name is Beetroot Palya.\\nThis recipe is...  \n",
       "4  Recipe name is Jain Red Sauce Pasta .\\nRed sau...  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating document column for recipe\n",
    "recipe_df['document'] = recipe_df.apply(make_recipe_document, axis=1)\n",
    "recipe_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.287159Z",
     "iopub.status.busy": "2024-08-06T10:10:37.287080Z",
     "iopub.status.idle": "2024-08-06T10:10:37.288867Z",
     "shell.execute_reply": "2024-08-06T10:10:37.288667Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Recipe name is Zucchini Curry.\n",
      "Zucchini curry is a vegetarian dish that features zucchini as the main ingredient. The curry typically includes a blend of spices and seasonings, such as turmeric, cumin, and coriander, along with garlic, onions, and tomatoes. The addition of coconut milk makes it creamy. Pair with Rice/Roti or Appam\n",
      "Cuisine is Indian.\n",
      "Diet is Vegan.\n",
      "Meal type is Main Course Gravy.\n",
      "Food Categories are Perfect With Chapati, Fusion Recipes.\n",
      "Ingredients are Zucchini, Refined Oil, Jeera, Onion, Garlic, Green Chilli, Basil, Dried Basil, Salt, Coriander Powder, Kashmiri Mirch, Garam Masala, Tomato, Water, Coconut Milk,  Coriander Leaves.\n"
     ]
    }
   ],
   "source": [
    "print(recipe_df['document'][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-06T10:10:37.290038Z",
     "iopub.status.busy": "2024-08-06T10:10:37.289963Z",
     "iopub.status.idle": "2024-08-06T10:10:39.052064Z",
     "shell.execute_reply": "2024-08-06T10:10:39.051521Z"
    }
   },
   "outputs": [],
   "source": [
    "recipe_df.to_parquet(constants.recipe_file)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}