ETL_initial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://github.com/nutritionix/library-python\n",
    "https://github.com/leetrout/python-nutritionix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The code below documents the building of the initial data tables through API requests to the nutrionix API, which we are using the free \"hacker\" plan for. It also includes methods to make new queries to the API, get those results, convert them into data frames, and append them to the original results. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### API Limits:\n",
    "* /v2/natural/...\n",
    "    * 200 per day\n",
    "* /v2/items\n",
    "    * 50 per day "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "headers = {\n",
    "    'x-app-id': \"677b211d\", \n",
    "    'x-app-key': \"742d50f2d169f8cc88df795d499f9ff9\",\n",
    "    'Content-Type': 'application/json'\n",
    "          }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# example of accessing exercise endpoint\n",
    "exercise = \"https://trackapi.nutritionix.com/v2/natural/exercise\"\n",
    "query1 = {\"query\": \"running\"}\n",
    "response1 = requests.post(exercise, headers=headers, json=query1)\n",
    "running = json.loads(response1.content.decode(\"utf-8\"))\n",
    "\n",
    "# using exercise information to calculate calories for user\n",
    "running_cals = running['exercises'][0]['nf_calories']\n",
    "running_time = running['exercises'][0]['duration_min']\n",
    "def calc_cals(user_time):\n",
    "    return running_cals/running_time*user_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# example of accessing nutrients endpoint\n",
    "nutrients = \"https://trackapi.nutritionix.com/v2/natural/nutrients\"\n",
    "query2 = {\"query\": \"pizza\"}\n",
    "response2 = requests.post(nutrients, headers=headers, json=query2)\n",
    "pizza = json.loads(response2.content.decode(\"utf-8\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'food_name': 'Big Mac', 'serving_unit': 'burger', 'nix_brand_id': '513fbc1283aa2dc80c000053', 'brand_name_item_name': \"McDonald's Big Mac\", 'serving_qty': 1, 'nf_calories': 540, 'photo': {'thumb': 'https://d2eawub7utcl6.cloudfront.net/images/nix-apple-grey.png', 'highres': None, 'is_user_uploaded': False}, 'brand_name': \"McDonald's\", 'region': 1, 'brand_type': 1, 'nix_item_id': '513fc9e73fe3ffd40300109f', 'locale': 'en_US'}\n",
      "540\n"
     ]
    }
   ],
   "source": [
    "# example of accessing instant endpoint\n",
    "\"\"\"\n",
    "THIS IS THE MAIN FOOD ENDPOINT WE WILL BE USING\n",
    "\n",
    "Each api call will search the string inputted in the query and return two lists, \n",
    "a \"common\" list and a \"branded\" list of foods. \n",
    "Each list will contain 20 of the top search items for that inputted query string. \n",
    "\n",
    "In total, we can get 40 results for one API call to the instant endpoint.\n",
    "\n",
    "Furthermore, for each of those 20 \"common\" results, we will need to do an additional\n",
    "call to the nutrients endpoint to get the calorie amount for that food.\n",
    "\n",
    "This is not necessary for branded foods, as they will have calories already included .\n",
    "\"\"\"\n",
    "instant = \"https://trackapi.nutritionix.com/v2/search/instant\"\n",
    "query3 = {\"query\": \"big mac\"}\n",
    "query = {\"time\": \"0100\", 'age': '20'}\n",
    "response3 = requests.post(instant, headers=headers, json=query3)\n",
    "big_mac = json.loads(response3.content.decode(\"utf-8\"))\n",
    "\n",
    "# getting calories of branded food from result of instant endpoint\n",
    "print(big_mac['branded'][0])\n",
    "big_mac_cals = big_mac['branded'][0]['nf_calories']\n",
    "print(big_mac_cals)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'foods': [{'food_name': 'big mac',\n",
       "   'brand_name': None,\n",
       "   'serving_qty': 1,\n",
       "   'serving_unit': 'burger',\n",
       "   'serving_weight_grams': 212,\n",
       "   'nf_calories': 540,\n",
       "   'nf_total_fat': 28,\n",
       "   'nf_saturated_fat': 10,\n",
       "   'nf_cholesterol': 80,\n",
       "   'nf_sodium': 950,\n",
       "   'nf_total_carbohydrate': 45,\n",
       "   'nf_dietary_fiber': 3,\n",
       "   'nf_sugars': 9,\n",
       "   'nf_protein': 25,\n",
       "   'nf_potassium': None,\n",
       "   'nf_p': None,\n",
       "   'full_nutrients': [{'attr_id': 203, 'value': 25},\n",
       "    {'attr_id': 204, 'value': 28},\n",
       "    {'attr_id': 205, 'value': 45},\n",
       "    {'attr_id': 208, 'value': 540},\n",
       "    {'attr_id': 269, 'value': 9},\n",
       "    {'attr_id': 291, 'value': 3},\n",
       "    {'attr_id': 301, 'value': 100},\n",
       "    {'attr_id': 303, 'value': 4.5},\n",
       "    {'attr_id': 307, 'value': 950},\n",
       "    {'attr_id': 318, 'value': 500},\n",
       "    {'attr_id': 401, 'value': 1.2},\n",
       "    {'attr_id': 601, 'value': 80},\n",
       "    {'attr_id': 605, 'value': 1},\n",
       "    {'attr_id': 606, 'value': 10}],\n",
       "   'nix_brand_name': \"McDonald's\",\n",
       "   'nix_brand_id': '513fbc1283aa2dc80c000053',\n",
       "   'nix_item_name': 'Big Mac',\n",
       "   'nix_item_id': '513fc9e73fe3ffd40300109f',\n",
       "   'upc': None,\n",
       "   'consumed_at': '2019-12-09T06:31:38+00:00',\n",
       "   'metadata': {'is_raw_food': False},\n",
       "   'source': 1,\n",
       "   'ndb_no': 21237,\n",
       "   'tags': {'item': 'mcdonalds big mac',\n",
       "    'measure': None,\n",
       "    'quantity': '1.0',\n",
       "    'food_group': None,\n",
       "    'tag_id': 2583},\n",
       "   'alt_measures': [],\n",
       "   'lat': None,\n",
       "   'lng': None,\n",
       "   'meal_type': 5,\n",
       "   'photo': {'thumb': 'https://d2xdmhkmkbyw75.cloudfront.net/2583_thumb.jpg',\n",
       "    'highres': 'https://d2xdmhkmkbyw75.cloudfront.net/2583_highres.jpg',\n",
       "    'is_user_uploaded': False},\n",
       "   'sub_recipe': None}]}"
      ]
     },
     "execution_count": 221,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# example getting calories of common foods from result of instant endpoint, using nutrients endpoint\n",
    "big_mac_food_name = big_mac['common'][0]['food_name']\n",
    "query4 = {\"query\": big_mac_food_name}\n",
    "response4 = requests.post(nutrients, headers=headers, json=query4)\n",
    "big_mac_common4 = json.loads(response4.content.decode(\"utf-8\"))\n",
    "big_mac_common4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'message': '\"value\" must contain at least one of [upc, nix_item_id]',\n",
       " 'id': 'ba37df55-51a3-4ae4-a95e-0b1badfedc1e'}"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting nutrition information for branded foods using item endpoint, but it's not working right now\n",
    "big_mac_nix_id = big_mac['branded'][0]['nix_item_id']\n",
    "big_mac_nix_id\n",
    "item = \"https://trackapi.nutritionix.com/v2/search/item\"\n",
    "query4 = {\"nix_item_id\":'513fc9e73fe3ffd40300109f'}\n",
    "response4 = requests.get(item, headers=headers, json=query4)\n",
    "big_mac_info = json.loads(response4.content.decode(\"utf-8\"))\n",
    "big_mac_info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Other attempts of pulling data with larger text queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "query5 = {\"query\": \"big mac pizza caesar salad skittles cheesecake\"}\n",
    "response5 = requests.post(instant, headers=headers, json=query5)\n",
    "multiple = json.loads(response5.content.decode(\"utf-8\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "158\n"
     ]
    }
   ],
   "source": [
    "foods = \"asparagus apples alfalfa acorn squash almond arugala artichoke applesauce ahi tuna albacore Apple juice Avocado roll Bruscetta bacon black beans bagels baked beans BBQ bison barley beer bisque bluefish bread broccoli buritto babaganoosh Cabbage cake carrots carne asada celery cheese chicken catfish chips chocolate chowder clams coffee cookies corn cupcakes crab curry cereal chimichanga dates dips duck dumplings donuts eggs enchilada eggrolls English muffins edimame eel sushi fajita falafel fish franks fondu French toast French dip Garlic ginger gnocchi goose granola grapes green beans Guancamole gumbo grits Graham crackers ham halibut hamburger cheeseburgers bacon cheeseburgers honey huenos rancheros hash browns hot dogs haiku roll hummus ice cream Irish stew Indian food Italian bread jambalaya jelly jam jerky jalapeño kale kabobs ketchup kiwi kidney beans kingfish lobster Lamb Linguine Lasagna Meatballs Moose Milk Milkshake Noodles Ostrich Pizza Pepperoni Porter Pancakes Quesadilla Quiche Reuben Spinach Spaghetti Tater tots Toast Venison Waffles Wine Walnuts Yogurt Ziti Zucchini soup\".lower()\n",
    "print(len(foods.split(\" \")))\n",
    "foods_query = {\"query\": foods}\n",
    "foods_response = requests.post(instant, headers=headers, json=foods_query)\n",
    "foods = json.loads(foods_response.content.decode(\"utf-8\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing dictionary to a json file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Writing a dictionary into a json file\n",
    "with open(\"foods.json\", 'w') as file:\n",
    "    json.dump(foods,file)\n",
    "with open(\"big_mac.json\", 'w') as file:\n",
    "    json.dump(big_mac,file)\n",
    "with open(\"multiple.json\", 'w') as file:\n",
    "    json.dump(multiple,file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Building Tables from JSON API results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for \"branded\" foods, keep the fields: ['food_name', 'brand_name_item_name', 'brand_name', 'serving_unit', 'serving_qty', 'nf_calories']\n",
    "brand_cols = ['food_name', 'brand_name_item_name', 'brand_name', 'serving_unit', 'serving_qty', 'nf_calories']\n",
    "def build_branded_table(result):\n",
    "    brand_cols = ['food_name', 'brand_name_item_name', 'brand_name', 'serving_unit', 'serving_qty', 'nf_calories']\n",
    "    table = []\n",
    "    for i in range(len(result['branded'])):\n",
    "        row = []\n",
    "        for col in brand_cols:\n",
    "            row.append(result['branded'][i][col])\n",
    "        table.append(row)\n",
    "    branded = pd.DataFrame(table, columns = brand_cols)\n",
    "    branded.drop_duplicates(subset = 'food_name', keep=False, inplace=True)\n",
    "    return branded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Rewriting this code so that it can take in a huge list of search results, where each search result \n",
    "is a dictionary with 'common' and 'branded', and within each of those is a list of individual\n",
    "results, from which i can pull a \"food_name\". \n",
    "\n",
    "Ultimately, the goal is to iterate through all lists and pull out every single \"food_name\" in \n",
    "every search result, and put them into a string separated by a comma and a space, like \", \". \n",
    "\n",
    "However, as I am designing this method, I want it to primarily work for the typical case of\n",
    "building a table from the results of a user's query. Which means on a regular basis it would\n",
    "take in just a list of results as would be typical of the value of the 'common' key in a single\n",
    "api query result. \n",
    "\n",
    "Externally, I'll need to write a bigger function that uses this as a helper function that\n",
    "only inputs a list of individual food results\n",
    "\"\"\"\n",
    "# for \"common\" foods, keep the fields: ['food_name', 'tag_name', 'serving_unit', 'serving_qty']\n",
    "\n",
    "\"\"\"\n",
    "Input: a list of individual food results, each of which is in dictionary format.\n",
    "If coming from a single API instant query, then simply input query_response['common'].\n",
    "Else, reformat data to be in a full list form.\n",
    "\"\"\"\n",
    "def build_common_table(result):\n",
    "    common_cols = ['food_name', 'tag_name','serving_unit', 'serving_qty', 'nf_calories']\n",
    "    common_fields = ['food_name', 'tag_name','serving_unit', 'serving_qty']\n",
    "    table = []\n",
    "    food_names = []\n",
    "    for i in range(len(result)): \n",
    "        result_keys = result[i].keys()\n",
    "        row = []\n",
    "        for col in common_fields:\n",
    "            if col in result_keys:\n",
    "                row.append(result[i][col])\n",
    "            else:\n",
    "                row.append(\"?\")\n",
    "        table.append(row)\n",
    "        food_names.append(result[i]['food_name'])\n",
    "        \n",
    "    table = np.array(table)\n",
    "    calories = common_api_get_cals(food_names)\n",
    "    print(table.shape)\n",
    "    print(calories.shape)\n",
    "    \n",
    "    full_table = np.concatenate((table, calories.T), axis=1)\n",
    "    common = pd.DataFrame(full_table, columns = common_cols)\n",
    "    return common"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The reason I make the below edit to common_api_get_calls and return the calories in a dictionary format rather than simply a list of calories is because I'm running into this error where each input result is unique, but the nutrients API does not have an output for every single input, thus meaning that I am only getting 2188 calorie numbers, when in fact I inputted 2223 different types of foods. However, due to the nature of the API input query and how it is just one string with each item separated by \", \" there's no way to distinguish which result row returned something meaningful and which ones didn't, at least in the current form, so I needed to edit this method to return a dictionary. That way, a new list of calories can be built such that if the calorie count for a particular food didn't come back, we just have a \"?\" there. \n",
    "\n",
    "This can be dealth with later down the line, in enforcing the schema to not take in rows that do not have calorie counts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {},
   "outputs": [],
   "source": [
    "def common_api_get_cals(food_names_list):\n",
    "    nutrients = \"https://trackapi.nutritionix.com/v2/natural/nutrients\"\n",
    "    food_names_string = \", \".join(food_names_list)\n",
    "    query = {'query': food_names_string}\n",
    "    response = requests.post(nutrients, headers=headers, json=query)\n",
    "    response_json = json.loads(response.content.decode(\"utf-8\"))\n",
    "    calories_dict = dict()\n",
    "    for item in response_json['foods']:\n",
    "        calories_dict[item['food_name']] = item['nf_calories']\n",
    "#     calories = np.array([[item['nf_calories'] for item in response_json['foods']]])\n",
    "    calories = np.array([[calories_dict[name] if name in calories_dict else \"?\" for name in food_names_list]])\n",
    "    return calories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>food_name</th>\n",
       "      <th>tag_name</th>\n",
       "      <th>serving_unit</th>\n",
       "      <th>serving_qty</th>\n",
       "      <th>nf_calories</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>apple</td>\n",
       "      <td>apple</td>\n",
       "      <td>medium (3\" dia)</td>\n",
       "      <td>1</td>\n",
       "      <td>94.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>apples</td>\n",
       "      <td>apple</td>\n",
       "      <td>medium (3\" dia)</td>\n",
       "      <td>1</td>\n",
       "      <td>94.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>apple pie</td>\n",
       "      <td>Apple pie</td>\n",
       "      <td>piece (1/8 of 9\" dia)</td>\n",
       "      <td>1</td>\n",
       "      <td>296.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>applepear</td>\n",
       "      <td>Asian pear</td>\n",
       "      <td>fruit 2-1/4\" high x 2-1/2\" dia</td>\n",
       "      <td>1</td>\n",
       "      <td>51.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>appletini</td>\n",
       "      <td>appletini</td>\n",
       "      <td>cocktail</td>\n",
       "      <td>1</td>\n",
       "      <td>149.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   food_name    tag_name                    serving_unit serving_qty  \\\n",
       "0      apple       apple                 medium (3\" dia)           1   \n",
       "1     apples       apple                 medium (3\" dia)           1   \n",
       "2  apple pie   Apple pie           piece (1/8 of 9\" dia)           1   \n",
       "3  applepear  Asian pear  fruit 2-1/4\" high x 2-1/2\" dia           1   \n",
       "4  appletini   appletini                        cocktail           1   \n",
       "\n",
       "  nf_calories  \n",
       "0       94.64  \n",
       "1       94.64  \n",
       "2      296.25  \n",
       "3       51.24  \n",
       "4      149.11  "
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# just checking if my build_common_table works\n",
    "trial_table = build_common_table(food_list_jsons[12]['common'])\n",
    "trial_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "ONLY USED TO BUILD THE ORIGINAL TABLES, NOT USED AT A LATER TIME, DEC 7, 2019\n",
    "THIS CODE IS DEPRECATED\n",
    "\n",
    "Currently I've made three different queries to the instant endpoint, I don't want to waste that data so\n",
    "I'm just going to build those tables now and append them to form two large dataframes, common and branded,\n",
    "and then I will do more comprehensive querying and build more items into the tables.\n",
    "\n",
    "all_queries = [big_mac,multiple,foods]\n",
    "\"\"\"\n",
    "def build_temp_curr_tables():\n",
    "    big_mac_common = build_common_table(big_mac)\n",
    "    big_mac_branded = build_branded_table(big_mac)\n",
    "    multiple_common = build_common_table(multiple)\n",
    "    multiple_branded = build_branded_table(multiple)\n",
    "    foods_common = build_common_table(foods)\n",
    "    foods_branded = build_branded_table(foods)\n",
    "    \n",
    "    common = big_mac_common.append(multiple_common,ignore_index=True).append(foods_common,ignore_index=True)\n",
    "    branded = big_mac_branded.append(multiple_branded,ignore_index=True).append(foods_branded,ignore_index=True)\n",
    "    return common, branded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_to_curr_tables(new_common,new_branded):\n",
    "    og_common = pd.read_csv(\"common.csv\")\n",
    "    full_common = og_common.append(new_common,ignore_index=True)\n",
    "    full_common.to_csv(\"common.csv\",index=False)\n",
    "    print(\"common.csv updated\")\n",
    "    \n",
    "    og_branded = pd.read_csv(\"branded.csv\")\n",
    "    full_branded = og_branded.append(new_branded,ignore_index=True)\n",
    "    full_branded.to_csv(\"branded.csv\",index=False)\n",
    "    print(\"branded.csv updated\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sun, Dec 8, 2019, getting new data by making query for each search item in list \"foods\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "foods = \"asparagus apples alfalfa acorn squash almond arugala artichoke applesauce ahi tuna albacore Apple juice Avocado roll Bruscetta bacon black beans bagels baked beans BBQ bison barley beer bisque bluefish bread broccoli buritto babaganoosh Cabbage cake carrots carne asada celery cheese chicken catfish chips chocolate chowder clams coffee cookies corn cupcakes crab curry cereal chimichanga dates dips duck dumplings donuts eggs enchilada eggrolls English muffins edimame eel sushi fajita falafel fish franks fondu French toast French dip Garlic ginger gnocchi goose granola grapes green beans Guancamole gumbo grits Graham crackers ham halibut hamburger cheeseburgers bacon cheeseburgers honey huenos rancheros hash browns hot dogs haiku roll hummus ice cream Irish stew Indian food Italian bread jambalaya jelly jam jerky jalapeño kale kabobs ketchup kiwi kidney beans kingfish lobster Lamb Linguine Lasagna Meatballs Moose Milk Milkshake Noodles Ostrich Pizza Pepperoni Porter Pancakes Quesadilla Quiche Reuben Spinach Spaghetti Tater tots Toast Venison Waffles Wine Walnuts Yogurt Ziti Zucchini soup\".lower()\n",
    "food_list = foods.split(\" \")\n",
    "\n",
    "def search_foods(food_list):\n",
    "    instant = \"https://trackapi.nutritionix.com/v2/search/instant\"\n",
    "    headers = {\n",
    "    'x-app-id': \"677b211d\", \n",
    "    'x-app-key': \"742d50f2d169f8cc88df795d499f9ff9\",\n",
    "    'Content-Type': 'application/json'\n",
    "          }\n",
    "    search_results = []\n",
    "    for food_item in food_list:\n",
    "        search_query = {'query': food_item}\n",
    "        response = requests.post(instant, headers=headers, json=search_query)\n",
    "        response_json = json.loads(response.content.decode(\"utf-8\"))\n",
    "        search_results.append(response_json)\n",
    "    return search_results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "food_list_jsons = search_foods(food_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [],
   "source": [
    "import functools\n",
    "\n",
    "def build_branded_table_from_list_of_jsons(jsons_list):\n",
    "    df_results = []\n",
    "    for json_result in jsons_list:\n",
    "        df_results.append(build_branded_table(json_result))\n",
    "    full_table = functools.reduce(lambda x,y:x.append(y,ignore_index=True), df_results)\n",
    "    # drop duplicate rows\n",
    "    full_table.drop_duplicates(subset='food_name',keep=False, inplace=True)\n",
    "    return full_table    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2398\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>food_name</th>\n",
       "      <th>brand_name_item_name</th>\n",
       "      <th>brand_name</th>\n",
       "      <th>serving_unit</th>\n",
       "      <th>serving_qty</th>\n",
       "      <th>nf_calories</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fresh Asparagus</td>\n",
       "      <td>Giorgio Fresh Asparagus</td>\n",
       "      <td>Giorgio</td>\n",
       "      <td>oz</td>\n",
       "      <td>3.00</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Asparagus</td>\n",
       "      <td>Fresh 1 Asparagus</td>\n",
       "      <td>Fresh 1</td>\n",
       "      <td>kilogram</td>\n",
       "      <td>1.00</td>\n",
       "      <td>191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Asparagus &amp; Pea</td>\n",
       "      <td>Saladworks Asparagus &amp; Pea</td>\n",
       "      <td>Saladworks</td>\n",
       "      <td>oz</td>\n",
       "      <td>1.00</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Asparagus Spears</td>\n",
       "      <td>Great Value Asparagus Spears</td>\n",
       "      <td>Great Value</td>\n",
       "      <td>cup</td>\n",
       "      <td>0.75</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Asparagus Medley</td>\n",
       "      <td>Safeway Farms Asparagus Medley</td>\n",
       "      <td>Safeway Farms</td>\n",
       "      <td>cup</td>\n",
       "      <td>1.00</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          food_name            brand_name_item_name     brand_name  \\\n",
       "0   Fresh Asparagus         Giorgio Fresh Asparagus        Giorgio   \n",
       "1         Asparagus               Fresh 1 Asparagus        Fresh 1   \n",
       "2   Asparagus & Pea      Saladworks Asparagus & Pea     Saladworks   \n",
       "3  Asparagus Spears    Great Value Asparagus Spears    Great Value   \n",
       "4  Asparagus Medley  Safeway Farms Asparagus Medley  Safeway Farms   \n",
       "\n",
       "  serving_unit  serving_qty  nf_calories  \n",
       "0           oz         3.00         20.0  \n",
       "1     kilogram         1.00        191.0  \n",
       "2           oz         1.00         40.0  \n",
       "3          cup         0.75         20.0  \n",
       "4          cup         1.00         20.0  "
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_branded_table = build_branded_table_from_list_of_jsons(food_list_jsons)\n",
    "print(len(new_branded_table))\n",
    "new_branded_table.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to utilize my build_common_table method, I need to input a single list of all the individual food result dictionaries. Currently, food_list_jsons is a list of 155 items, each of which is a result of a particular query. Each of these 155 result items is a dictionary of two keys, one of which is 'common'. After accessing the value of that 'common' key, we get a list of individual food results. So I need to extract those."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_common_table_from_list_of_jsons(jsons_list):\n",
    "    food_results = [item for sublist in jsons_list for item in sublist['common']]\n",
    "    new_list = []\n",
    "    # below is to eliminate duplicates\n",
    "    for d in food_results:\n",
    "        if d not in new_list:\n",
    "            new_list.append(d)\n",
    "            \n",
    "    table = build_common_table(new_list)\n",
    "    return table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2223, 4)\n",
      "(1, 2223)\n",
      "2223\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>food_name</th>\n",
       "      <th>tag_name</th>\n",
       "      <th>serving_unit</th>\n",
       "      <th>serving_qty</th>\n",
       "      <th>nf_calories</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>asparagus</td>\n",
       "      <td>asparagus</td>\n",
       "      <td>spears</td>\n",
       "      <td>5</td>\n",
       "      <td>16.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>asparagus dip</td>\n",
       "      <td>asparagus dip</td>\n",
       "      <td>cup</td>\n",
       "      <td>1</td>\n",
       "      <td>622.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>asparagus soup</td>\n",
       "      <td>cream of asparagus soup</td>\n",
       "      <td>cup (8 fl oz)</td>\n",
       "      <td>1</td>\n",
       "      <td>161.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>asparagus stalk</td>\n",
       "      <td>asparagus stalks</td>\n",
       "      <td>spears</td>\n",
       "      <td>5</td>\n",
       "      <td>16.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>asparagus pizza</td>\n",
       "      <td>white asparagus pizza</td>\n",
       "      <td>medium slice</td>\n",
       "      <td>1</td>\n",
       "      <td>306.91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         food_name                 tag_name   serving_unit serving_qty  \\\n",
       "0        asparagus                asparagus         spears           5   \n",
       "1    asparagus dip            asparagus dip            cup           1   \n",
       "2   asparagus soup  cream of asparagus soup  cup (8 fl oz)           1   \n",
       "3  asparagus stalk         asparagus stalks         spears           5   \n",
       "4  asparagus pizza    white asparagus pizza   medium slice           1   \n",
       "\n",
       "  nf_calories  \n",
       "0        16.5  \n",
       "1      622.06  \n",
       "2       161.2  \n",
       "3        16.5  \n",
       "4      306.91  "
      ]
     },
     "execution_count": 240,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_common_table = build_common_table_from_list_of_jsons(food_list_jsons)\n",
    "print(len(new_common_table))\n",
    "new_common_table.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sun, Dec 8, 2019  \n",
    "Both the new common and the new branded table have been built, so I can now combine it with the previous stuff I had. All my new methods ensure no duplicates, but the old data that I'm adding onto definitely will still have duplicates. So we'll definitely need to ensure in creating our sql tables that\n",
    "1) There are no duplicate rows\n",
    "2) There are no rows that have a \"?\" in the calories column"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "add_to_curr_tables(new_common_table,new_branded_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting Exercise Data\n",
    "\n",
    "Users have the option either \n",
    "1. simply inputting an exercise\n",
    "2. adding extra information\n",
    "    * sex\n",
    "    * weight_kg\n",
    "    * height_cm\n",
    "    * age\n",
    "    \n",
    "Taking action 1 will always attempt to query our SQL database before it resorts to using the API. Taking action 2 will always use the API and will never store that data in the SQL database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {},
   "outputs": [],
   "source": [
    "# example of accessing exercise endpoint\n",
    "\"\"\"\n",
    "Takes in a string query like \"running\", or several queries together in one string separated by \", \"\n",
    "like \"running, tennis, hiking\"\n",
    "\"\"\"\n",
    "def get_exercise(exercise_input, gender=None, weight_kg=None,height_cm=None,age=None):\n",
    "    exercise = \"https://trackapi.nutritionix.com/v2/natural/exercise\"\n",
    "    if gender is not None and weight_kg is not None and height_cm is not None and age is not None:\n",
    "        query = {\"query\": exercise_input, 'gender':gender,'weight_kg':weight_kg,'height_cm':height_cm,'age':age}\n",
    "    else:\n",
    "        query = {\"query\": exercise_input}\n",
    "    response = requests.post(exercise, headers=headers, json=query)\n",
    "    results = json.loads(response.content.decode(\"utf-8\"))\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 367,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_baseline_exercises():\n",
    "    gym_exercises = \"Squat,Leg press,Lunge,Deadlift,Leg extension,Leg curl,Standing calf raise,Seated calf raise,Hip adductor,Bench press,Chest fly,Push-up,Pull-down,Pull-up,Bent-over row,Upright row,Shoulder press,Shoulder fly,Lateral raise,Shoulder shrug,Pushdown,Triceps extension,Biceps curl,Crunch,Russian twist,Leg raise,Back extension\"\n",
    "    gym_exercises = gym_exercises.lower().split(\",\")\n",
    "    gym_exercises = \", \".join(gym_exercises)\n",
    "    sports = \"Archery Badminton Cricket Bowling Boxing Curling Tennis Skateboarding Surfing Hockey Figure skating Yoga Fencing Fitness Gymnastics Karate Volleyball Weightlifting Basketball Baseball Rugby Wrestling High jumping Hang gliding Car racing Cycling Running Table tennis Fishing Judo Climbing Billiards Pool Shooting Horse racing Horseback riding Golf Football Soccer American football Swimming\"\n",
    "    sports = sports.lower().split(\" \")\n",
    "    remove = ['figure', 'skating','high', 'jumping','hang','gliding','car','racing','table','tennis','horse','racing','horseback','riding','american','football']\n",
    "    for sprt in remove:\n",
    "        sports.remove(sprt)\n",
    "    add = [\"figure skating\",'high jumping', 'hang gliding', 'table tennis', 'horseback riding', 'american football']\n",
    "    for sprt in add:\n",
    "        sports.append(sprt)\n",
    "    sports = \", \".join(sports)\n",
    "    dance = 'waltz,tango,cha cha,rumba,samba,mambo,quickstep,jive,flamenco,swing,tap,salsa,capoiera,ballet,ballroom'\n",
    "    dance = dance.split(',')\n",
    "    dance = \", \".join(dance)\n",
    "    other = ['walk','run','climb','rock climb','hiking','speed walking','jogging']\n",
    "    other = \", \".join(other)\n",
    "    exercises = gym_exercises+\", \"+sports+\", \"+dance+\", \"+other\n",
    "    return get_exercise(exercises)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "baseline_exercises = get_baseline_exercises()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 387,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we want to keep the fields 'name', 'duration_min', 'met', 'nf_calories'\n",
    "def build_exercise_table(exercises):\n",
    "    table = []\n",
    "    exercise_fields = ['name','duration_min','met','nf_calories']\n",
    "    for i in range(len(exercises['exercises'])):\n",
    "        row = []\n",
    "        for col in exercise_fields:\n",
    "            row.append(exercises['exercises'][i][col])\n",
    "        table.append(row)\n",
    "    exercise_table = pd.DataFrame(table, columns = exercise_fields)\n",
    "    return exercise_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 388,
   "metadata": {},
   "outputs": [],
   "source": [
    "exercise_table = build_exercise_table(baseline_exercises)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 390,
   "metadata": {},
   "outputs": [],
   "source": [
    "exercise_table.to_csv('exercises.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}