JSON_to_dataframe.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "accelerator": "GPU",
    "colab": {
      "name": "JSON_to_dataframe.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "znjyvLWqH09V"
      },
      "source": [
        "## **Python code to convert scraped JSON dataset to pandas dataframe for efficient processing for classification and recommendation**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k_9Fg20DIFN6"
      },
      "source": [
        "***Created by Gaurav Lodhi***"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3FNsdeTNWXqo"
      },
      "source": [
        "import pandas as pd\n",
        "import json\n",
        "import re\n",
        "import numpy as np"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Nm9gAJpsXQ46"
      },
      "source": [
        "with open('bbc_food_recipe.json') as json_file: \n",
        "    data = json.load(json_file) "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 353
        },
        "id": "Iejx9j5wXWz_",
        "outputId": "0d53591c-1d70-4f50-f7b5-1e0265f9248c"
      },
      "source": [
        "print(len(data))\n",
        "i=0\n",
        "recipe1=(list(data[1].values())[0])\n",
        "print(recipe1)\n",
        "d={}\n",
        "for i in range(len(data)):\n",
        "  for x in list(data[i].values())[0]:\n",
        "    key=list(x.keys())[0]\n",
        "    if key not in d.keys():\n",
        "      d[key]=[]\n",
        "    d[key].append(list(x.values())[0])\n",
        "df=pd.DataFrame.from_dict(d)\n",
        "df.head(5)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "1340\n",
            "[{'Title': 'Two Minute Breakfast Smoothie'}, {'URL': 'https://www.bbcgoodfood.com/recipes/two-minute-breakfast-smoothie'}, {'Meal': 'Breakfast'}, {'Rating': ['5 out of 5.', '56']}, {'Prepartion Time': 2}, {'Cooking Time': None}, {'Serves': '2'}, {'Calories': '156'}, {'Fat': '3g'}, {'Cholesterol': '2g'}, {'Carbs': '25g'}, {'Fiber': '2g'}, {'Protein': '4g'}, {'Ingredients': ['1  banana', '1 tbsp  porridge oats', '80g  soft fruit  (whatever you have – strawberries, blueberries, and mango all work well)', '150ml  milk', '1 tsp  honey', '1 tsp  vanilla extract']}, {'Cooking instructions': ['Put all the ingredients in a blender and whizz for 1 min until smooth.', 'Pour the banana oat smoothie into two glasses to serve.']}]\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "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>Title</th>\n",
              "      <th>URL</th>\n",
              "      <th>Meal</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Prepartion Time</th>\n",
              "      <th>Cooking Time</th>\n",
              "      <th>Serves</th>\n",
              "      <th>Calories</th>\n",
              "      <th>Fat</th>\n",
              "      <th>Cholesterol</th>\n",
              "      <th>Carbs</th>\n",
              "      <th>Fiber</th>\n",
              "      <th>Protein</th>\n",
              "      <th>Ingredients</th>\n",
              "      <th>Cooking instructions</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Breakfast Egg Wraps</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/breakfast-...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>[4 out of 5., 16]</td>\n",
              "      <td>5</td>\n",
              "      <td>7.0</td>\n",
              "      <td>4</td>\n",
              "      <td>429</td>\n",
              "      <td>20g</td>\n",
              "      <td>4g</td>\n",
              "      <td>31g</td>\n",
              "      <td>6g</td>\n",
              "      <td>28g</td>\n",
              "      <td>[500g  pack closed cup mushrooms, 4 tsp  cold ...</td>\n",
              "      <td>[Thickly slice half the pack of mushrooms. Hea...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Two Minute Breakfast Smoothie</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/two-minute...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>[5 out of 5., 56]</td>\n",
              "      <td>2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2</td>\n",
              "      <td>156</td>\n",
              "      <td>3g</td>\n",
              "      <td>2g</td>\n",
              "      <td>25g</td>\n",
              "      <td>2g</td>\n",
              "      <td>4g</td>\n",
              "      <td>[1  banana, 1 tbsp  porridge oats, 80g  soft f...</td>\n",
              "      <td>[Put all the ingredients in a blender and whiz...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Breakfast Burrito</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/breakfast-...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>[5 out of 5., 25]</td>\n",
              "      <td>5</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1</td>\n",
              "      <td>366</td>\n",
              "      <td>21g</td>\n",
              "      <td>4g</td>\n",
              "      <td>26g</td>\n",
              "      <td>5g</td>\n",
              "      <td>16g</td>\n",
              "      <td>[1 tsp  chipotle paste, 1  egg, 1 tsp  rapesee...</td>\n",
              "      <td>[Whisk the chipotle paste with the egg and som...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Breakfast Naans</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/breakfast-...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>[5 out of 5., 13]</td>\n",
              "      <td>5</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2</td>\n",
              "      <td>503</td>\n",
              "      <td>30g</td>\n",
              "      <td>7g</td>\n",
              "      <td>35g</td>\n",
              "      <td>5g</td>\n",
              "      <td>20g</td>\n",
              "      <td>[1 tbsp  vegetable or sunflower oil, 2  eggs, ...</td>\n",
              "      <td>[Heat oven to 200C/180C fan/gas 6. Heat the oi...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Raspberry Almond Oat Breakfast Cookies</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/raspberry-...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>[4 out of 5., 12]</td>\n",
              "      <td>10</td>\n",
              "      <td>15.0</td>\n",
              "      <td>12 cookies</td>\n",
              "      <td>86</td>\n",
              "      <td>3g</td>\n",
              "      <td>0.3g</td>\n",
              "      <td>13g</td>\n",
              "      <td>2g</td>\n",
              "      <td>2g</td>\n",
              "      <td>[2  ripe  bananas  , mashed, 150g  porridge oa...</td>\n",
              "      <td>[Heat the oven to 200C/180C fan/gas 4 and line...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    Title  \\\n",
              "0                     Breakfast Egg Wraps   \n",
              "1           Two Minute Breakfast Smoothie   \n",
              "2                       Breakfast Burrito   \n",
              "3                         Breakfast Naans   \n",
              "4  Raspberry Almond Oat Breakfast Cookies   \n",
              "\n",
              "                                                 URL       Meal  \\\n",
              "0  https://www.bbcgoodfood.com/recipes/breakfast-...  Breakfast   \n",
              "1  https://www.bbcgoodfood.com/recipes/two-minute...  Breakfast   \n",
              "2  https://www.bbcgoodfood.com/recipes/breakfast-...  Breakfast   \n",
              "3  https://www.bbcgoodfood.com/recipes/breakfast-...  Breakfast   \n",
              "4  https://www.bbcgoodfood.com/recipes/raspberry-...  Breakfast   \n",
              "\n",
              "              Rating  Prepartion Time  Cooking Time      Serves Calories  Fat  \\\n",
              "0  [4 out of 5., 16]                5           7.0           4      429  20g   \n",
              "1  [5 out of 5., 56]                2           NaN           2      156   3g   \n",
              "2  [5 out of 5., 25]                5          10.0           1      366  21g   \n",
              "3  [5 out of 5., 13]                5           5.0           2      503  30g   \n",
              "4  [4 out of 5., 12]               10          15.0  12 cookies       86   3g   \n",
              "\n",
              "  Cholesterol Carbs Fiber Protein  \\\n",
              "0          4g   31g    6g     28g   \n",
              "1          2g   25g    2g      4g   \n",
              "2          4g   26g    5g     16g   \n",
              "3          7g   35g    5g     20g   \n",
              "4        0.3g   13g    2g      2g   \n",
              "\n",
              "                                         Ingredients  \\\n",
              "0  [500g  pack closed cup mushrooms, 4 tsp  cold ...   \n",
              "1  [1  banana, 1 tbsp  porridge oats, 80g  soft f...   \n",
              "2  [1 tsp  chipotle paste, 1  egg, 1 tsp  rapesee...   \n",
              "3  [1 tbsp  vegetable or sunflower oil, 2  eggs, ...   \n",
              "4  [2  ripe  bananas  , mashed, 150g  porridge oa...   \n",
              "\n",
              "                                Cooking instructions  \n",
              "0  [Thickly slice half the pack of mushrooms. Hea...  \n",
              "1  [Put all the ingredients in a blender and whiz...  \n",
              "2  [Whisk the chipotle paste with the egg and som...  \n",
              "3  [Heat oven to 200C/180C fan/gas 6. Heat the oi...  \n",
              "4  [Heat the oven to 200C/180C fan/gas 4 and line...  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8aZpxIOiq2t7"
      },
      "source": [
        "df.to_csv(\"bbc_food_recipe.csv\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2HMFgVVtmXay"
      },
      "source": [
        "with open('spark_people_food_recipe.json') as json_file: \n",
        "    data = json.load(json_file) "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MW_pF6GLHtVt",
        "outputId": "830549f1-fb6b-4ab5-cc6b-971a85fef0e3"
      },
      "source": [
        "print(len(data))\n",
        "i=0\n",
        "recipe1=(list(data[1].values())[0])\n",
        "print(recipe1)\n",
        "d={}\n",
        "for i in range(len(data)):\n",
        "  for x in list(data[i].values())[0]:\n",
        "    key=list(x.keys())[0]\n",
        "    if key not in d.keys():\n",
        "      d[key]=[]\n",
        "    d[key].append(list(x.values())[0])\n",
        "df=pd.DataFrame.from_dict(d)\n",
        "df.head(5)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "8445\n",
            "[{'Title': 'Crockpot Yogurt'}, {'URL': 'https://recipes.sparkpeople.com/recipe-detail.asp?recipe=452682'}, {'Meal': 'breakfast'}, {'Rating': ['4.7 out of 5.', '3']}, {'Prepartion Time': ' 5'}, {'Cooking Time': ' 840'}, {'Serves': ' 9'}, {'Calories': '127.9'}, {'Fat': '4.7 g'}, {'Cholesterol': '19.8 mg'}, {'Carbs': '12.5 g'}, {'Fiber': '0.0 g'}, {'Protein': '8.7 g'}, {'Ingredients': ['8 cups 2% milk', '1/2 cup nonfat powdered milk', '1/2 cup whole milk yogurt w/live cultures']}, {'Cooking instructions': ['Turn your crockpot to low', '  Add the milk and heat in crockpot for 2 1/2 hours', '  Next, unplug the crockpot and let everything sit for 3 hours', '  Remove 2 cups of the milk to a bowl and whisk in the milk powder and the yogurt', '  Return to the crockpot and stir to combine', '  Cover crockpot completely with a bath towel or two and let sit for 8 hours']}]\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "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>Title</th>\n",
              "      <th>URL</th>\n",
              "      <th>Meal</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Prepartion Time</th>\n",
              "      <th>Cooking Time</th>\n",
              "      <th>Serves</th>\n",
              "      <th>Calories</th>\n",
              "      <th>Fat</th>\n",
              "      <th>Cholesterol</th>\n",
              "      <th>Carbs</th>\n",
              "      <th>Fiber</th>\n",
              "      <th>Protein</th>\n",
              "      <th>Ingredients</th>\n",
              "      <th>Cooking instructions</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Breakfast caserole</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>breakfast</td>\n",
              "      <td>[4.5 out of 5., 2]</td>\n",
              "      <td>None</td>\n",
              "      <td>45</td>\n",
              "      <td>6</td>\n",
              "      <td>440.9</td>\n",
              "      <td>33.7 g</td>\n",
              "      <td>432.3 mg</td>\n",
              "      <td>2.6 g</td>\n",
              "      <td>0.6 g</td>\n",
              "      <td>27.7 g</td>\n",
              "      <td>[2.0 tbsp Butter, salted, 1.0 cup, shredded Ch...</td>\n",
              "      <td>[bake at 350 for 45 minutesServing Size: 6 3x8...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Crockpot Yogurt</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>breakfast</td>\n",
              "      <td>[4.7 out of 5., 3]</td>\n",
              "      <td>5</td>\n",
              "      <td>840</td>\n",
              "      <td>9</td>\n",
              "      <td>127.9</td>\n",
              "      <td>4.7 g</td>\n",
              "      <td>19.8 mg</td>\n",
              "      <td>12.5 g</td>\n",
              "      <td>0.0 g</td>\n",
              "      <td>8.7 g</td>\n",
              "      <td>[8 cups 2% milk, 1/2 cup nonfat powdered milk,...</td>\n",
              "      <td>[Turn your crockpot to low,   Add the milk and...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Scrambled Eggs with Black Beans and Salsa</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>breakfast</td>\n",
              "      <td>[4.5 out of 5., 13]</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>275.9</td>\n",
              "      <td>10.6 g</td>\n",
              "      <td>427.3 mg</td>\n",
              "      <td>26.2 g</td>\n",
              "      <td>6.7 g</td>\n",
              "      <td>19.7 g</td>\n",
              "      <td>[1/3 cup black beans (canned), drained and rin...</td>\n",
              "      <td>[1,  Put green pepper and beans into a nonstic...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Flaxseed Morning Glory Muffins</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>breakfast</td>\n",
              "      <td>[3.7 out of 5., 3]</td>\n",
              "      <td>15</td>\n",
              "      <td>25</td>\n",
              "      <td>12</td>\n",
              "      <td>127.5</td>\n",
              "      <td>3.8 g</td>\n",
              "      <td>0.1 mg</td>\n",
              "      <td>22.9 g</td>\n",
              "      <td>6.4 g</td>\n",
              "      <td>4.3 g</td>\n",
              "      <td>[1 cup fiber one cereal, 2/3 cup skim milk, 1 ...</td>\n",
              "      <td>[Heat oven to 375,  place paper baking cup in ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Strawberry Yogurt Breakfast Split</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>breakfast</td>\n",
              "      <td>[3.3 out of 5., 4]</td>\n",
              "      <td>2</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>250.7</td>\n",
              "      <td>4.9 g</td>\n",
              "      <td>2.5 mg</td>\n",
              "      <td>46.8 g</td>\n",
              "      <td>7.0 g</td>\n",
              "      <td>9.2 g</td>\n",
              "      <td>[1 medium banana, 1 cup strawberries, fresh or...</td>\n",
              "      <td>[Peel and split banana, place halves in a serv...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                       Title  \\\n",
              "0                         Breakfast caserole   \n",
              "1                            Crockpot Yogurt   \n",
              "2  Scrambled Eggs with Black Beans and Salsa   \n",
              "3             Flaxseed Morning Glory Muffins   \n",
              "4          Strawberry Yogurt Breakfast Split   \n",
              "\n",
              "                                                 URL       Meal  \\\n",
              "0  https://recipes.sparkpeople.com/recipe-detail....  breakfast   \n",
              "1  https://recipes.sparkpeople.com/recipe-detail....  breakfast   \n",
              "2  https://recipes.sparkpeople.com/recipe-detail....  breakfast   \n",
              "3  https://recipes.sparkpeople.com/recipe-detail....  breakfast   \n",
              "4  https://recipes.sparkpeople.com/recipe-detail....  breakfast   \n",
              "\n",
              "                Rating Prepartion Time Cooking Time Serves Calories     Fat  \\\n",
              "0   [4.5 out of 5., 2]            None           45      6    440.9  33.7 g   \n",
              "1   [4.7 out of 5., 3]               5          840      9    127.9   4.7 g   \n",
              "2  [4.5 out of 5., 13]            None         None      1    275.9  10.6 g   \n",
              "3   [3.7 out of 5., 3]              15           25     12    127.5   3.8 g   \n",
              "4   [3.3 out of 5., 4]               2            2      1    250.7   4.9 g   \n",
              "\n",
              "  Cholesterol   Carbs  Fiber Protein  \\\n",
              "0    432.3 mg   2.6 g  0.6 g  27.7 g   \n",
              "1     19.8 mg  12.5 g  0.0 g   8.7 g   \n",
              "2    427.3 mg  26.2 g  6.7 g  19.7 g   \n",
              "3      0.1 mg  22.9 g  6.4 g   4.3 g   \n",
              "4      2.5 mg  46.8 g  7.0 g   9.2 g   \n",
              "\n",
              "                                         Ingredients  \\\n",
              "0  [2.0 tbsp Butter, salted, 1.0 cup, shredded Ch...   \n",
              "1  [8 cups 2% milk, 1/2 cup nonfat powdered milk,...   \n",
              "2  [1/3 cup black beans (canned), drained and rin...   \n",
              "3  [1 cup fiber one cereal, 2/3 cup skim milk, 1 ...   \n",
              "4  [1 medium banana, 1 cup strawberries, fresh or...   \n",
              "\n",
              "                                Cooking instructions  \n",
              "0  [bake at 350 for 45 minutesServing Size: 6 3x8...  \n",
              "1  [Turn your crockpot to low,   Add the milk and...  \n",
              "2  [1,  Put green pepper and beans into a nonstic...  \n",
              "3  [Heat oven to 375,  place paper baking cup in ...  \n",
              "4  [Peel and split banana, place halves in a serv...  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aA9vAcFOHtVu"
      },
      "source": [
        "df.to_csv(\"spark_people_food_recipe.csv\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "RMUR-PZTHtVu",
        "outputId": "d9f94af6-eb92-44b4-dac0-a2878572f1fa"
      },
      "source": [
        "df_1 = pd.read_csv(\"bbc_food_recipe.csv\")\n",
        "df_1.rename(columns={ df_1.columns[0]: \"Recipe ID\" }, inplace = True)\n",
        "df_1[\"Recipe ID\"]+=1\n",
        "ratings=df_1[\"Rating\"].tolist()\n",
        "norm_ratings_list=[]\n",
        "num_of_reviews_list=[]\n",
        "for rating in ratings:\n",
        "    norm_rating=re.sub(\"out of \",\"\",rating.strip(\"[\").split(\",\")[0]).split()[0].strip(\"'\")\n",
        "    if \"None\" not in norm_rating:\n",
        "        norm_rating=round(float(norm_rating)/5,2)\n",
        "    else:\n",
        "        norm_rating=None\n",
        "    norm_ratings_list.append(norm_rating)\n",
        "    num_of_reviews=int(re.sub(\"'\",\"\",re.sub(\"reviews\",\"\",rating.strip(\"[\").split(\",\")[1]).strip(\"]\").strip()))\n",
        "    num_of_reviews_list.append(num_of_reviews)\n",
        "max_reviews=max(num_of_reviews_list)\n",
        "num_of_reviews_list=[num_of_reviews/max_reviews for num_of_reviews in num_of_reviews_list]\n",
        "rating_score_list=[]\n",
        "for i in range(len(norm_ratings_list)):\n",
        "    if str(norm_ratings_list[i])!=\"None\":\n",
        "        rating_score_list.append(round(0.8*norm_ratings_list[i]+0.2*num_of_reviews_list[i],2))\n",
        "    else:\n",
        "        rating_score_list.append(None)\n",
        "df_1=df_1.fillna(\"0\")\n",
        "df_1[\"Rating Score\"]=rating_score_list\n",
        "df_1.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>URL</th>\n",
              "      <th>Meal</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Prepartion Time</th>\n",
              "      <th>Cooking Time</th>\n",
              "      <th>Serves</th>\n",
              "      <th>Calories</th>\n",
              "      <th>Fat</th>\n",
              "      <th>Cholesterol</th>\n",
              "      <th>Carbs</th>\n",
              "      <th>Fiber</th>\n",
              "      <th>Protein</th>\n",
              "      <th>Ingredients</th>\n",
              "      <th>Cooking instructions</th>\n",
              "      <th>Rating Score</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>Breakfast Egg Wraps</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/breakfast-...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['4 out of 5.', '16']</td>\n",
              "      <td>5</td>\n",
              "      <td>7</td>\n",
              "      <td>4</td>\n",
              "      <td>429</td>\n",
              "      <td>20g</td>\n",
              "      <td>4g</td>\n",
              "      <td>31g</td>\n",
              "      <td>6g</td>\n",
              "      <td>28g</td>\n",
              "      <td>['500g  pack closed cup mushrooms', '4 tsp  co...</td>\n",
              "      <td>['Thickly slice half the pack of mushrooms. He...</td>\n",
              "      <td>0.65</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>Two Minute Breakfast Smoothie</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/two-minute...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['5 out of 5.', '56']</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "      <td>156</td>\n",
              "      <td>3g</td>\n",
              "      <td>2g</td>\n",
              "      <td>25g</td>\n",
              "      <td>2g</td>\n",
              "      <td>4g</td>\n",
              "      <td>['1  banana', '1 tbsp  porridge oats', '80g  s...</td>\n",
              "      <td>['Put all the ingredients in a blender and whi...</td>\n",
              "      <td>0.82</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>Breakfast Burrito</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/breakfast-...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['5 out of 5.', '25']</td>\n",
              "      <td>5</td>\n",
              "      <td>10</td>\n",
              "      <td>1</td>\n",
              "      <td>366</td>\n",
              "      <td>21g</td>\n",
              "      <td>4g</td>\n",
              "      <td>26g</td>\n",
              "      <td>5g</td>\n",
              "      <td>16g</td>\n",
              "      <td>['1 tsp  chipotle paste', '1  egg', '1 tsp  ra...</td>\n",
              "      <td>['Whisk the chipotle paste with the egg and so...</td>\n",
              "      <td>0.81</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>Breakfast Naans</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/breakfast-...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['5 out of 5.', '13']</td>\n",
              "      <td>5</td>\n",
              "      <td>5</td>\n",
              "      <td>2</td>\n",
              "      <td>503</td>\n",
              "      <td>30g</td>\n",
              "      <td>7g</td>\n",
              "      <td>35g</td>\n",
              "      <td>5g</td>\n",
              "      <td>20g</td>\n",
              "      <td>['1 tbsp  vegetable or sunflower oil', '2  egg...</td>\n",
              "      <td>['Heat oven to 200C/180C fan/gas 6. Heat the o...</td>\n",
              "      <td>0.80</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>Raspberry Almond Oat Breakfast Cookies</td>\n",
              "      <td>https://www.bbcgoodfood.com/recipes/raspberry-...</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['4 out of 5.', '12']</td>\n",
              "      <td>10</td>\n",
              "      <td>15</td>\n",
              "      <td>12 cookies</td>\n",
              "      <td>86</td>\n",
              "      <td>3g</td>\n",
              "      <td>0.3g</td>\n",
              "      <td>13g</td>\n",
              "      <td>2g</td>\n",
              "      <td>2g</td>\n",
              "      <td>['2  ripe  bananas  , mashed', '150g  porridge...</td>\n",
              "      <td>['Heat the oven to 200C/180C fan/gas 4 and lin...</td>\n",
              "      <td>0.64</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Recipe ID                                   Title  \\\n",
              "0          1                     Breakfast Egg Wraps   \n",
              "1          2           Two Minute Breakfast Smoothie   \n",
              "2          3                       Breakfast Burrito   \n",
              "3          4                         Breakfast Naans   \n",
              "4          5  Raspberry Almond Oat Breakfast Cookies   \n",
              "\n",
              "                                                 URL       Meal  \\\n",
              "0  https://www.bbcgoodfood.com/recipes/breakfast-...  Breakfast   \n",
              "1  https://www.bbcgoodfood.com/recipes/two-minute...  Breakfast   \n",
              "2  https://www.bbcgoodfood.com/recipes/breakfast-...  Breakfast   \n",
              "3  https://www.bbcgoodfood.com/recipes/breakfast-...  Breakfast   \n",
              "4  https://www.bbcgoodfood.com/recipes/raspberry-...  Breakfast   \n",
              "\n",
              "                  Rating  Prepartion Time Cooking Time      Serves  Calories  \\\n",
              "0  ['4 out of 5.', '16']                5            7           4       429   \n",
              "1  ['5 out of 5.', '56']                2            0           2       156   \n",
              "2  ['5 out of 5.', '25']                5           10           1       366   \n",
              "3  ['5 out of 5.', '13']                5            5           2       503   \n",
              "4  ['4 out of 5.', '12']               10           15  12 cookies        86   \n",
              "\n",
              "   Fat Cholesterol Carbs Fiber Protein  \\\n",
              "0  20g          4g   31g    6g     28g   \n",
              "1   3g          2g   25g    2g      4g   \n",
              "2  21g          4g   26g    5g     16g   \n",
              "3  30g          7g   35g    5g     20g   \n",
              "4   3g        0.3g   13g    2g      2g   \n",
              "\n",
              "                                         Ingredients  \\\n",
              "0  ['500g  pack closed cup mushrooms', '4 tsp  co...   \n",
              "1  ['1  banana', '1 tbsp  porridge oats', '80g  s...   \n",
              "2  ['1 tsp  chipotle paste', '1  egg', '1 tsp  ra...   \n",
              "3  ['1 tbsp  vegetable or sunflower oil', '2  egg...   \n",
              "4  ['2  ripe  bananas  , mashed', '150g  porridge...   \n",
              "\n",
              "                                Cooking instructions  Rating Score  \n",
              "0  ['Thickly slice half the pack of mushrooms. He...          0.65  \n",
              "1  ['Put all the ingredients in a blender and whi...          0.82  \n",
              "2  ['Whisk the chipotle paste with the egg and so...          0.81  \n",
              "3  ['Heat oven to 200C/180C fan/gas 6. Heat the o...          0.80  \n",
              "4  ['Heat the oven to 200C/180C fan/gas 4 and lin...          0.64  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FOHRJlxXHtVv",
        "outputId": "6f6808eb-2ec0-463d-cf2c-9f8e7686691e"
      },
      "source": [
        "df_2 = pd.read_csv(\"spark_people_food_recipe.csv\")\n",
        "df_2.rename(columns={ df_2.columns[0]: \"Recipe ID\" }, inplace = True)\n",
        "df_2[\"Recipe ID\"]=df_2[\"Recipe ID\"]+len(df_1)+1\n",
        "df_2.loc[df_2['Meal'] == \"breakfast\", 'Meal'] = \"Breakfast\"\n",
        "df_2.loc[df_2['Meal'] == \"lunch\", 'Meal'] = \"Lunch\"\n",
        "df_2.loc[df_2['Meal'] == \"dinner\", 'Meal'] = \"Dinner\"\n",
        "ratings=df_2[\"Rating\"].tolist()\n",
        "norm_ratings_list=[]\n",
        "num_of_reviews_list=[]\n",
        "for rating in ratings:\n",
        "    norm_rating=re.sub(\"out of \",\"\",rating.strip(\"[\").split(\",\")[0]).split()[0].strip(\"'\")\n",
        "    if \"None\" not in norm_rating:\n",
        "        norm_rating=round(float(norm_rating)/5,2)\n",
        "    else:\n",
        "        norm_rating=None\n",
        "    norm_ratings_list.append(norm_rating)\n",
        "    norm_num_of_reviews=re.sub(\"'\",\"\",re.sub(\"reviews\",\"\",rating.strip(\"[\").split(\",\")[1]).strip(\"]\").strip())\n",
        "    if \"None\" not in norm_num_of_reviews:\n",
        "        num_of_reviews=int(norm_num_of_reviews)\n",
        "    else:\n",
        "        num_of_reviews=None\n",
        "    num_of_reviews_list.append(num_of_reviews)\n",
        "\n",
        "max_reviews=np.nanmax(np.array(num_of_reviews_list, dtype=np.float64))\n",
        "num_of_reviews_list=[num_of_reviews/max_reviews if num_of_reviews is not None else None for num_of_reviews in num_of_reviews_list]\n",
        "\n",
        "rating_score_list=[]\n",
        "for i in range(len(norm_ratings_list)):\n",
        "    if str(norm_ratings_list[i])!=\"None\":\n",
        "        rating_score_list.append(round(0.8*norm_ratings_list[i]+0.2*num_of_reviews_list[i],2))\n",
        "    else:\n",
        "        rating_score_list.append(None)\n",
        "\n",
        "max_rating=np.nanmax(np.array(rating_score_list, dtype=np.float64))\n",
        "rating_score_list=[round(rating_score/max_rating,2) if rating_score is not None else None for rating_score in rating_score_list]\n",
        "\n",
        "df_2[\"Cholesterol\"]=round(df_2[df_2.Cholesterol.str.match(\".*mg\")==True][\"Cholesterol\"].str.strip('mg').str.strip().str.replace(',', '').astype(float)/1000,3)\n",
        "df_2[\"Cholesterol\"]=df_2[\"Cholesterol\"].astype(str)+\" g\"\n",
        "\n",
        "\n",
        "df_2=df_2.fillna(\"0\")\n",
        "df_2[\"Rating Score\"]=rating_score_list\n",
        "df_2.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>URL</th>\n",
              "      <th>Meal</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Prepartion Time</th>\n",
              "      <th>Cooking Time</th>\n",
              "      <th>Serves</th>\n",
              "      <th>Calories</th>\n",
              "      <th>Fat</th>\n",
              "      <th>Cholesterol</th>\n",
              "      <th>Carbs</th>\n",
              "      <th>Fiber</th>\n",
              "      <th>Protein</th>\n",
              "      <th>Ingredients</th>\n",
              "      <th>Cooking instructions</th>\n",
              "      <th>Rating Score</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1341</td>\n",
              "      <td>Breakfast caserole</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['4.5 out of 5.', '2']</td>\n",
              "      <td>0</td>\n",
              "      <td>45</td>\n",
              "      <td>6</td>\n",
              "      <td>440.9</td>\n",
              "      <td>33.7 g</td>\n",
              "      <td>0.432 g</td>\n",
              "      <td>2.6 g</td>\n",
              "      <td>0.6 g</td>\n",
              "      <td>27.7 g</td>\n",
              "      <td>['2.0 tbsp Butter, salted', '1.0 cup, shredded...</td>\n",
              "      <td>['bake at 350 for 45 minutesServing Size: 6 3x...</td>\n",
              "      <td>0.81</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1342</td>\n",
              "      <td>Crockpot Yogurt</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['4.7 out of 5.', '3']</td>\n",
              "      <td>5</td>\n",
              "      <td>840</td>\n",
              "      <td>9</td>\n",
              "      <td>127.9</td>\n",
              "      <td>4.7 g</td>\n",
              "      <td>0.02 g</td>\n",
              "      <td>12.5 g</td>\n",
              "      <td>0.0 g</td>\n",
              "      <td>8.7 g</td>\n",
              "      <td>['8 cups 2% milk', '1/2 cup nonfat powdered mi...</td>\n",
              "      <td>['Turn your crockpot to low', '  Add the milk ...</td>\n",
              "      <td>0.84</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1343</td>\n",
              "      <td>Scrambled Eggs with Black Beans and Salsa</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['4.5 out of 5.', '13']</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>275.9</td>\n",
              "      <td>10.6 g</td>\n",
              "      <td>0.427 g</td>\n",
              "      <td>26.2 g</td>\n",
              "      <td>6.7 g</td>\n",
              "      <td>19.7 g</td>\n",
              "      <td>['1/3 cup black beans (canned), drained and ri...</td>\n",
              "      <td>['1', ' Put green pepper and beans into a nons...</td>\n",
              "      <td>0.81</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1344</td>\n",
              "      <td>Flaxseed Morning Glory Muffins</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['3.7 out of 5.', '3']</td>\n",
              "      <td>15</td>\n",
              "      <td>25</td>\n",
              "      <td>12</td>\n",
              "      <td>127.5</td>\n",
              "      <td>3.8 g</td>\n",
              "      <td>0.0 g</td>\n",
              "      <td>22.9 g</td>\n",
              "      <td>6.4 g</td>\n",
              "      <td>4.3 g</td>\n",
              "      <td>['1 cup fiber one cereal', '2/3 cup skim milk'...</td>\n",
              "      <td>['Heat oven to 375', ' place paper baking cup ...</td>\n",
              "      <td>0.66</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1345</td>\n",
              "      <td>Strawberry Yogurt Breakfast Split</td>\n",
              "      <td>https://recipes.sparkpeople.com/recipe-detail....</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td>['3.3 out of 5.', '4']</td>\n",
              "      <td>2</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>250.7</td>\n",
              "      <td>4.9 g</td>\n",
              "      <td>0.002 g</td>\n",
              "      <td>46.8 g</td>\n",
              "      <td>7.0 g</td>\n",
              "      <td>9.2 g</td>\n",
              "      <td>['1 medium banana', '1 cup strawberries, fresh...</td>\n",
              "      <td>['Peel and split banana, place halves in a ser...</td>\n",
              "      <td>0.60</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Recipe ID                                      Title  \\\n",
              "0       1341                         Breakfast caserole   \n",
              "1       1342                            Crockpot Yogurt   \n",
              "2       1343  Scrambled Eggs with Black Beans and Salsa   \n",
              "3       1344             Flaxseed Morning Glory Muffins   \n",
              "4       1345          Strawberry Yogurt Breakfast Split   \n",
              "\n",
              "                                                 URL       Meal  \\\n",
              "0  https://recipes.sparkpeople.com/recipe-detail....  Breakfast   \n",
              "1  https://recipes.sparkpeople.com/recipe-detail....  Breakfast   \n",
              "2  https://recipes.sparkpeople.com/recipe-detail....  Breakfast   \n",
              "3  https://recipes.sparkpeople.com/recipe-detail....  Breakfast   \n",
              "4  https://recipes.sparkpeople.com/recipe-detail....  Breakfast   \n",
              "\n",
              "                    Rating Prepartion Time Cooking Time  Serves Calories  \\\n",
              "0   ['4.5 out of 5.', '2']               0           45       6    440.9   \n",
              "1   ['4.7 out of 5.', '3']               5          840       9    127.9   \n",
              "2  ['4.5 out of 5.', '13']               0            0       1    275.9   \n",
              "3   ['3.7 out of 5.', '3']              15           25      12    127.5   \n",
              "4   ['3.3 out of 5.', '4']               2            2       1    250.7   \n",
              "\n",
              "      Fat Cholesterol   Carbs  Fiber Protein  \\\n",
              "0  33.7 g     0.432 g   2.6 g  0.6 g  27.7 g   \n",
              "1   4.7 g      0.02 g  12.5 g  0.0 g   8.7 g   \n",
              "2  10.6 g     0.427 g  26.2 g  6.7 g  19.7 g   \n",
              "3   3.8 g       0.0 g  22.9 g  6.4 g   4.3 g   \n",
              "4   4.9 g     0.002 g  46.8 g  7.0 g   9.2 g   \n",
              "\n",
              "                                         Ingredients  \\\n",
              "0  ['2.0 tbsp Butter, salted', '1.0 cup, shredded...   \n",
              "1  ['8 cups 2% milk', '1/2 cup nonfat powdered mi...   \n",
              "2  ['1/3 cup black beans (canned), drained and ri...   \n",
              "3  ['1 cup fiber one cereal', '2/3 cup skim milk'...   \n",
              "4  ['1 medium banana', '1 cup strawberries, fresh...   \n",
              "\n",
              "                                Cooking instructions  Rating Score  \n",
              "0  ['bake at 350 for 45 minutesServing Size: 6 3x...          0.81  \n",
              "1  ['Turn your crockpot to low', '  Add the milk ...          0.84  \n",
              "2  ['1', ' Put green pepper and beans into a nons...          0.81  \n",
              "3  ['Heat oven to 375', ' place paper baking cup ...          0.66  \n",
              "4  ['Peel and split banana, place halves in a ser...          0.60  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2hIiguzPHtVv"
      },
      "source": [
        "df=df_1.append(df_2, ignore_index=True)\n",
        "df.rename(columns={ df.columns[5]: \"Preperation Time\" }, inplace = True)\n",
        "df[['Preperation Time', 'Cooking Time']] = df[['Preperation Time', 'Cooking Time']].apply(pd.to_numeric)\n",
        "\n",
        "df[\"Fat\"]=df.Fat.str.strip('g').str.strip()\n",
        "df[\"Cholesterol\"]=df.Cholesterol.str.strip('g').str.strip()\n",
        "df[\"Carbs\"]=df.Carbs.str.strip('g').str.strip()\n",
        "df[\"Fiber\"]=df.Fiber.str.strip('g').str.strip()\n",
        "df[\"Protein\"]=df.Protein.str.strip('g').str.strip()\n",
        "\n",
        "recipes=[]\n",
        "generic_ingredients_list=[]\n",
        "with open('train.json','r') as f: \n",
        " recipes = json.load(f)\n",
        "for recipe in recipes: \n",
        "   temp_basket=[item.lower() for item in recipe['ingredients']] \n",
        "   generic_ingredients_list+=temp_basket \n",
        "generic_ingredients_list=list(set(generic_ingredients_list))\n",
        "\n",
        "ingredient_lists=list(df[\"Ingredients\"])\n",
        "lookup_list=[]\n",
        "for i,ingredient_list in enumerate(ingredient_lists):\n",
        "    temp_list=[]\n",
        "    ingredients=re.sub(\"'\",\"\",ingredient_list.strip('[').strip(']')).split(\",\")\n",
        "    for ingredient in ingredients:\n",
        "        ingredient_search_list=ingredient.split()\n",
        "        ingredient_search_list=[ing.lower() for ing in ingredient_search_list]\n",
        "        for generic_ingredient in generic_ingredients_list:\n",
        "            if generic_ingredient in ingredient_search_list:\n",
        "                temp_list.append(generic_ingredient)\n",
        "                break\n",
        "    temp_list=list(set(temp_list))\n",
        "    lookup_list.append(temp_list)\n",
        "\n",
        "df[\"Lookup Ingredients\"]=lookup_list\n",
        "\n",
        "df.to_csv(\"recipes.csv\",index=False)"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}