src/request_and_save_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting data from the API and saving it into the PostgreSQL DB\n",
    "I'm using the public API from the U.S. Department of Agriculture (https://fdc.nal.usda.gov/api-guide.html). The API documentation can be found [here](https://app.swaggerhub.com/apis/fdcnal/food-data_central_api/1.0.0). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import psycopg2 as ps\n",
    "from sqlalchemy import create_engine\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading API secret key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = open(\"../secrets/FoodData_central_key.txt\", \"r\")\n",
    "key = f.read()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function for parsing the incoming JSON into a dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_response(response):\n",
    "    item_page_list = []\n",
    "    for item in response[\"foods\"]:\n",
    "        choco = {\n",
    "            \"id\": item.get(\"fdcId\"),\n",
    "            \"description\": item.get(\"description\"),\n",
    "            \"brandOwner\": item.get(\"brandOwner\"),\n",
    "            \"brandName\": item.get(\"brandName\"),\n",
    "            \"ingredients\": item.get(\"ingredients\"),\n",
    "            \"marketCountry\": item.get(\"marketCountry\"),\n",
    "            \"dataSource\": item.get(\"dataSource\"),\n",
    "            \"packageWeight\": item.get(\"packageWeight\"),\n",
    "            \"servingSizeUnit\": item.get(\"servingSizeUnit\"),\n",
    "            \"servingSize\": item.get(\"servingSize\"), \n",
    "            \"score\": item.get(\"score\"),\n",
    "            \"Protein\": None,\n",
    "            \"Protein_unit\": None,\n",
    "            \"fat\": None,\n",
    "            \"fat_unit\": None,\n",
    "            \"Carbohydrate\": None,\n",
    "            \"Carbohydrate_unit\": None,\n",
    "            \"Energy\": None,\n",
    "            \"Energy_unit\": None,\n",
    "            \"Fiber\": None,\n",
    "            \"Fiber_unit\": None,\n",
    "            \"Calcium\": None,\n",
    "            \"Calcium_unit\": None,\n",
    "            \"Iron\": None,\n",
    "            \"Iron_unit\": None,\n",
    "            \"Vitamin_D\": None,\n",
    "            \"Vitamin_D_unit\": None,\n",
    "            \"Sugars_added\": None,\n",
    "            \"Sugars_added_unit\": None,\n",
    "            \"Cholesterol\": None,\n",
    "            \"Cholesterol_unit\": None\n",
    "        }\n",
    "        \n",
    "        # the following attributes are a little tricky. \n",
    "        # They are not always given and you have to search for them       \n",
    "        for nutrient in item['foodNutrients']:\n",
    "            if \"Protein\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"Protein\"] = nutrient[\"value\"]\n",
    "                choco[\"Protein_unit\"] = nutrient[\"unitName\"]\n",
    "            elif \"fat\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"fat\"] = nutrient[\"value\"]\n",
    "                choco[\"fat_unit\"] = nutrient[\"unitName\"]\n",
    "            elif \"Carbohydrate\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"Carbohydrate\"] = nutrient[\"value\"]\n",
    "                choco[\"Carbohydrate_unit\"] = nutrient[\"unitName\"]\n",
    "            elif \"Energy\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"Energy\"] = nutrient[\"value\"]\n",
    "                choco[\"Energy_unit\"] = nutrient[\"unitName\"]\n",
    "            elif \"Fiber\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"Fiber\"] = nutrient[\"value\"]\n",
    "                choco[\"Fiber_unit\"] = nutrient[\"unitName\"]\n",
    "            elif \"Calcium\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"Calcium\"] = nutrient[\"value\"]\n",
    "                choco[\"Calcium_unit\"] = nutrient[\"unitName\"]\n",
    "            elif \"Iron\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"Iron\"] = nutrient[\"value\"]\n",
    "                choco[\"Iron_unit\"] = nutrient[\"unitName\"]\n",
    "            elif \"Vitamin D\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"Vitamin_D\"] = nutrient[\"value\"]\n",
    "                choco[\"Vitamin_D_unit\"] = nutrient[\"unitName\"]\n",
    "            elif \"Sugars\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"Sugars_added\"] = nutrient[\"value\"]\n",
    "                choco[\"Sugars_added_unit\"] = nutrient[\"unitName\"]\n",
    "            elif \"Cholesterol\" in nutrient[\"nutrientName\"]:\n",
    "                choco[\"Cholesterol\"] = nutrient[\"value\"]\n",
    "                choco[\"Cholesterol_unit\"] = nutrient[\"unitName\"]\n",
    "        \n",
    "        item_page_list.append(choco)\n",
    "    return item_page_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to request data from the API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def request_data(url, endpoint, params):\n",
    "    return requests.get(url=url + endpoint, params=params).json()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Executing the request and saving everything in a list\n",
    "We are looking for chocolate, which is a branded food (no foundation food). But there are many more products that include chocolate, that's why we narrow the search on the food category equals 'chocolate'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://api.nal.usda.gov/fdc/v1/\"\n",
    "endpoint = \"foods/search\"\n",
    "params = {\n",
    "\"api_key\": key,\n",
    "\"query\": \"foodCategory:Chocolate\",\n",
    "\"pageSize\": \"200\",\n",
    "\"dataType\": \"Branded\",\n",
    "\"pageNumber\": 1\n",
    "}\n",
    "\n",
    "total_list = []\n",
    "for page in range(1, request_data(url, endpoint, params)['totalPages']+1):\n",
    "    # use paging\n",
    "    params[\"pageNumber\"] = page\n",
    "    response = request_data(url, endpoint, params)\n",
    "    if page==2:\n",
    "        f = open(\"..\\\\data\\\\response_foods.json\", \"w\")\n",
    "        json.dump(response['foods'][0], f)\n",
    "    total_list.extend(parse_response(response))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving the output into a Pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>description</th>\n",
       "      <th>brandOwner</th>\n",
       "      <th>brandName</th>\n",
       "      <th>ingredients</th>\n",
       "      <th>marketCountry</th>\n",
       "      <th>dataSource</th>\n",
       "      <th>packageWeight</th>\n",
       "      <th>servingSizeUnit</th>\n",
       "      <th>servingSize</th>\n",
       "      <th>...</th>\n",
       "      <th>Calcium</th>\n",
       "      <th>Calcium_unit</th>\n",
       "      <th>Iron</th>\n",
       "      <th>Iron_unit</th>\n",
       "      <th>Vitamin_D</th>\n",
       "      <th>Vitamin_D_unit</th>\n",
       "      <th>Sugars_added</th>\n",
       "      <th>Sugars_added_unit</th>\n",
       "      <th>Cholesterol</th>\n",
       "      <th>Cholesterol_unit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1914156</td>\n",
       "      <td>SILKY SMOOTH TRUFFLE HEARTS , MILK CHOCOLATE</td>\n",
       "      <td>Mars Chocolate North America LLC</td>\n",
       "      <td>DOVE</td>\n",
       "      <td>MILK CHOCOLATE (SUGAR, COCOA BUTTER, CHOCOLATE...</td>\n",
       "      <td>United States</td>\n",
       "      <td>LI</td>\n",
       "      <td>3.04 oz/86.2 g</td>\n",
       "      <td>g</td>\n",
       "      <td>43.0</td>\n",
       "      <td>...</td>\n",
       "      <td>140.0</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.84</td>\n",
       "      <td>MG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>48.8</td>\n",
       "      <td>G</td>\n",
       "      <td>23.0</td>\n",
       "      <td>MG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2171978</td>\n",
       "      <td>&amp; SEA SALT CARAMEL CHOCOLATE SWIRL, DARK &amp; MIL...</td>\n",
       "      <td>Mars Chocolate North America LLC</td>\n",
       "      <td>DOVE</td>\n",
       "      <td>SEMI SWEET CHOCOLATE (SUGAR, CHOCOLATE, CHOCOL...</td>\n",
       "      <td>United States</td>\n",
       "      <td>LI</td>\n",
       "      <td>22.70 OZ/643.5 g</td>\n",
       "      <td>g</td>\n",
       "      <td>31.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MG</td>\n",
       "      <td>2.58</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.0</td>\n",
       "      <td>IU</td>\n",
       "      <td>45.2</td>\n",
       "      <td>G</td>\n",
       "      <td>16.0</td>\n",
       "      <td>MG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2154148</td>\n",
       "      <td>100% CACAO RAW HONEY, CACAO</td>\n",
       "      <td>Vosges, Ltd.</td>\n",
       "      <td>VOSGES</td>\n",
       "      <td>UNSWEETENED CHOCOLATE PROCESSED WITH ALKALI, R...</td>\n",
       "      <td>United States</td>\n",
       "      <td>LI</td>\n",
       "      <td>3 oz/85 g</td>\n",
       "      <td>g</td>\n",
       "      <td>28.0</td>\n",
       "      <td>...</td>\n",
       "      <td>36.0</td>\n",
       "      <td>MG</td>\n",
       "      <td>16.80</td>\n",
       "      <td>MG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>21.4</td>\n",
       "      <td>G</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986920</td>\n",
       "      <td>100% COCOA UNSWEETENED CHOCOLATE</td>\n",
       "      <td>Lindt &amp; Sprungli (Schweiz) AG</td>\n",
       "      <td>LINDT</td>\n",
       "      <td>CHOCOLATE, COCOA POWDER PROCESSED WITH ALKALI,...</td>\n",
       "      <td>United States</td>\n",
       "      <td>LI</td>\n",
       "      <td>1.7 oz/50 g</td>\n",
       "      <td>g</td>\n",
       "      <td>50.0</td>\n",
       "      <td>...</td>\n",
       "      <td>82.0</td>\n",
       "      <td>MG</td>\n",
       "      <td>8.00</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.0</td>\n",
       "      <td>IU</td>\n",
       "      <td>0.0</td>\n",
       "      <td>G</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1096787</td>\n",
       "      <td>100% MARANON CACAO WITH PISTACHIOS &amp; DATES, PI...</td>\n",
       "      <td>WILLIE'SCACAO LTD</td>\n",
       "      <td>None</td>\n",
       "      <td>COCOA MASS*, DATES 28%, ALMONDS, PISTACHIO 7%,...</td>\n",
       "      <td>United States</td>\n",
       "      <td>LI</td>\n",
       "      <td>None</td>\n",
       "      <td>g</td>\n",
       "      <td>25.0</td>\n",
       "      <td>...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>MG</td>\n",
       "      <td>12.00</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.0</td>\n",
       "      <td>IU</td>\n",
       "      <td>20.0</td>\n",
       "      <td>G</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                        description  \\\n",
       "0  1914156       SILKY SMOOTH TRUFFLE HEARTS , MILK CHOCOLATE   \n",
       "1  2171978  & SEA SALT CARAMEL CHOCOLATE SWIRL, DARK & MIL...   \n",
       "2  2154148                        100% CACAO RAW HONEY, CACAO   \n",
       "3  1986920                   100% COCOA UNSWEETENED CHOCOLATE   \n",
       "4  1096787  100% MARANON CACAO WITH PISTACHIOS & DATES, PI...   \n",
       "\n",
       "                         brandOwner brandName  \\\n",
       "0  Mars Chocolate North America LLC      DOVE   \n",
       "1  Mars Chocolate North America LLC      DOVE   \n",
       "2                      Vosges, Ltd.    VOSGES   \n",
       "3     Lindt & Sprungli (Schweiz) AG     LINDT   \n",
       "4                 WILLIE'SCACAO LTD      None   \n",
       "\n",
       "                                         ingredients  marketCountry  \\\n",
       "0  MILK CHOCOLATE (SUGAR, COCOA BUTTER, CHOCOLATE...  United States   \n",
       "1  SEMI SWEET CHOCOLATE (SUGAR, CHOCOLATE, CHOCOL...  United States   \n",
       "2  UNSWEETENED CHOCOLATE PROCESSED WITH ALKALI, R...  United States   \n",
       "3  CHOCOLATE, COCOA POWDER PROCESSED WITH ALKALI,...  United States   \n",
       "4  COCOA MASS*, DATES 28%, ALMONDS, PISTACHIO 7%,...  United States   \n",
       "\n",
       "  dataSource     packageWeight servingSizeUnit  servingSize  ...  Calcium  \\\n",
       "0         LI    3.04 oz/86.2 g               g         43.0  ...    140.0   \n",
       "1         LI  22.70 OZ/643.5 g               g         31.0  ...      0.0   \n",
       "2         LI         3 oz/85 g               g         28.0  ...     36.0   \n",
       "3         LI       1.7 oz/50 g               g         50.0  ...     82.0   \n",
       "4         LI              None               g         25.0  ...    100.0   \n",
       "\n",
       "   Calcium_unit   Iron  Iron_unit Vitamin_D  Vitamin_D_unit Sugars_added  \\\n",
       "0            MG   0.84         MG       NaN            None         48.8   \n",
       "1            MG   2.58         MG       0.0              IU         45.2   \n",
       "2            MG  16.80         MG       NaN            None         21.4   \n",
       "3            MG   8.00         MG       0.0              IU          0.0   \n",
       "4            MG  12.00         MG       0.0              IU         20.0   \n",
       "\n",
       "   Sugars_added_unit Cholesterol  Cholesterol_unit  \n",
       "0                  G        23.0                MG  \n",
       "1                  G        16.0                MG  \n",
       "2                  G         0.0                MG  \n",
       "3                  G         0.0                MG  \n",
       "4                  G         0.0                MG  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df = pd.DataFrame(total_list)\n",
    "data_df.to_csv(\"..\\data\\\\api_raw_data.csv\", index=False)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inserting data into database\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "384"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine = create_engine(\"postgresql://postgres:admin@localhost:5432/clusterdb\")\n",
    "data_df.to_sql(name=\"chocolate\", con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Appendix\n",
    "This is function for connecting to postgreSQL with `psycopg2` and creating a new tables based on the given Pandas dataframe. It is not used here because the Pandas method `to_csv` does everything fo you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([dtype('int64'), dtype('O'), dtype('float64')], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check column data types in the dataframe\n",
    "data_df.dtypes.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_table(data):\n",
    "    # translation of pandas datatypes in SQL data types\n",
    "    replacements = {\n",
    "            'object': 'varchar',\n",
    "            'float64': 'float',\n",
    "            'int64': 'integer'\n",
    "        }\n",
    "    # build 'create table' command\n",
    "    column_com = \"\"\", \"\"\".join(\"{} {}\".format(c, d) for (c, d) in zip(data.columns, data.dtypes.replace(replacements)))\n",
    "    create_table_com = \"\"\"Create Table if not exists chocolate (\"\"\" + column_com + \"\"\");\"\"\"\n",
    "    # Connect to database\n",
    "    db_conn = None\n",
    "    try:\n",
    "        conn_string = \"host='localhost' dbname='clusterdb' user='postgres' password='admin' port=5432\"\n",
    "        db_conn = ps.connect(conn_string)\n",
    "        \n",
    "        # create table\n",
    "        db_cursor = db_conn.cursor()\n",
    "        db_cursor.execute(create_table_com)\n",
    "        \n",
    "        # commit and close connection\n",
    "        db_conn.commit()\n",
    "        db_cursor.close()\n",
    "        print(\"table created\")\n",
    "    except Exception as error:\n",
    "        print(error)\n",
    "    finally:\n",
    "        if db_conn is not None:\n",
    "            db_conn.close()\n",
    "        \n",
    "    return "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "7bc9ded3f4b263abbfa32ad63e4c4a6c716168e50d440895d45bc1204a767ac3"
  },
  "kernelspec": {
   "display_name": "Python 3.9.10",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}