Food combinations - Optimization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Optimization of food combinations\n",
    "This Jupyter Notebook aims at finding optimal combinations of foods given a subset of foods and a subset of nutrients to optimize on. Each nutrient has 3 categories of intake: adequate, moderate- and severe-risk associated. The weighted error is increased when falling in a unhealthy intake (too much or not enough)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For now this algorithm does not work properly. It finds combinations that are suboptimal and cannot maximize the number of nutrient that fall into 'adequate intake'. It seems the algorithm falls in the corners of this 30-dimension 'eggholder' by frequently finding 0 or 2100 kcal for some foods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "from scipy import linalg, optimize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import nutritional values of foods for 100 kcal\n",
    "nut_calc = pd.read_excel('Nutrition Calculator.xlsx', sheet_name='Nutrition Calculator (p100kcal)',\n",
    "                         skiprows=(0,1,3), index_col=0)\n",
    "# import nutritional needs for humans\n",
    "nut_needs = pd.read_excel('Nutrition Calculator.xlsx', sheet_name='Human Nutritional Needs',\n",
    "                          nrows=5, skiprows=(0,2), index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "nut_calc = nut_calc.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "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>Protein</th>\n",
       "      <th>Fat total</th>\n",
       "      <th>Saturated fats</th>\n",
       "      <th>Trans fats</th>\n",
       "      <th>Carbohydrates</th>\n",
       "      <th>Fiber</th>\n",
       "      <th>Sugars</th>\n",
       "      <th>Vitamin A</th>\n",
       "      <th>Vitamin E</th>\n",
       "      <th>Vitamin D</th>\n",
       "      <th>...</th>\n",
       "      <th>Lysine</th>\n",
       "      <th>Methionine</th>\n",
       "      <th>Cysteine</th>\n",
       "      <th>Phenylalanine</th>\n",
       "      <th>Tyrosine</th>\n",
       "      <th>Threonine</th>\n",
       "      <th>Tryptophan</th>\n",
       "      <th>Valine</th>\n",
       "      <th>Omega-3 (ALA)</th>\n",
       "      <th>Omega-6 (LA)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lower (severe risk)</th>\n",
       "      <td>46.00</td>\n",
       "      <td>35.00</td>\n",
       "      <td>1.000000e-99</td>\n",
       "      <td>1.000000e-99</td>\n",
       "      <td>5.000000e+01</td>\n",
       "      <td>2.500000e+01</td>\n",
       "      <td>1.000000e-99</td>\n",
       "      <td>5.000000e+02</td>\n",
       "      <td>1.000000e+04</td>\n",
       "      <td>3.8</td>\n",
       "      <td>...</td>\n",
       "      <td>1.860000e+00</td>\n",
       "      <td>6.200000e-01</td>\n",
       "      <td>2.480000e-01</td>\n",
       "      <td>9.300000e-01</td>\n",
       "      <td>2.480000e-01</td>\n",
       "      <td>9.300000e-01</td>\n",
       "      <td>2.480000e-01</td>\n",
       "      <td>1.612000e+00</td>\n",
       "      <td>1.350000e+00</td>\n",
       "      <td>1.000000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lower (moderate risk)</th>\n",
       "      <td>51.00</td>\n",
       "      <td>46.67</td>\n",
       "      <td>1.000000e-99</td>\n",
       "      <td>1.000000e-99</td>\n",
       "      <td>1.300000e+02</td>\n",
       "      <td>2.500000e+01</td>\n",
       "      <td>1.000000e-99</td>\n",
       "      <td>7.000000e+02</td>\n",
       "      <td>1.500000e+04</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.860000e+00</td>\n",
       "      <td>6.200000e-01</td>\n",
       "      <td>2.480000e-01</td>\n",
       "      <td>9.300000e-01</td>\n",
       "      <td>2.480000e-01</td>\n",
       "      <td>9.300000e-01</td>\n",
       "      <td>2.480000e-01</td>\n",
       "      <td>1.612000e+00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>1.450000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Higher (safe)</th>\n",
       "      <td>78.75</td>\n",
       "      <td>70.00</td>\n",
       "      <td>2.333000e+01</td>\n",
       "      <td>2.300000e+00</td>\n",
       "      <td>4.000000e+02</td>\n",
       "      <td>7.000000e+01</td>\n",
       "      <td>5.250000e+01</td>\n",
       "      <td>3.000000e+03</td>\n",
       "      <td>3.000000e+05</td>\n",
       "      <td>250.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.440000e+00</td>\n",
       "      <td>2.480000e+00</td>\n",
       "      <td>9.920000e-01</td>\n",
       "      <td>3.720000e+00</td>\n",
       "      <td>9.920000e-01</td>\n",
       "      <td>3.720000e+00</td>\n",
       "      <td>9.920000e-01</td>\n",
       "      <td>6.448000e+00</td>\n",
       "      <td>2.800000e+00</td>\n",
       "      <td>1.867000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Higher (moderate risk)</th>\n",
       "      <td>140.00</td>\n",
       "      <td>81.67</td>\n",
       "      <td>2.333000e+01</td>\n",
       "      <td>2.300000e+00</td>\n",
       "      <td>5.000000e+02</td>\n",
       "      <td>5.000000e+02</td>\n",
       "      <td>1.312500e+02</td>\n",
       "      <td>8.000000e+03</td>\n",
       "      <td>1.000000e+06</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>4.670000e+00</td>\n",
       "      <td>2.333000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Higher (severe risk)</th>\n",
       "      <td>400.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>2.000000e+02</td>\n",
       "      <td>2.000000e+02</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>15000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "      <td>1.000000e+99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Protein  Fat total  Saturated fats    Trans fats  \\\n",
       "Lower (severe risk)       46.00      35.00    1.000000e-99  1.000000e-99   \n",
       "Lower (moderate risk)     51.00      46.67    1.000000e-99  1.000000e-99   \n",
       "Higher (safe)             78.75      70.00    2.333000e+01  2.300000e+00   \n",
       "Higher (moderate risk)   140.00      81.67    2.333000e+01  2.300000e+00   \n",
       "Higher (severe risk)     400.00     200.00    2.000000e+02  2.000000e+02   \n",
       "\n",
       "                        Carbohydrates         Fiber        Sugars  \\\n",
       "Lower (severe risk)      5.000000e+01  2.500000e+01  1.000000e-99   \n",
       "Lower (moderate risk)    1.300000e+02  2.500000e+01  1.000000e-99   \n",
       "Higher (safe)            4.000000e+02  7.000000e+01  5.250000e+01   \n",
       "Higher (moderate risk)   5.000000e+02  5.000000e+02  1.312500e+02   \n",
       "Higher (severe risk)     1.000000e+99  1.000000e+99  1.000000e+99   \n",
       "\n",
       "                           Vitamin A     Vitamin E  Vitamin D  ...  \\\n",
       "Lower (severe risk)     5.000000e+02  1.000000e+04        3.8  ...   \n",
       "Lower (moderate risk)   7.000000e+02  1.500000e+04       15.0  ...   \n",
       "Higher (safe)           3.000000e+03  3.000000e+05      250.0  ...   \n",
       "Higher (moderate risk)  8.000000e+03  1.000000e+06     1925.0  ...   \n",
       "Higher (severe risk)    1.000000e+99  1.000000e+99    15000.0  ...   \n",
       "\n",
       "                              Lysine    Methionine      Cysteine  \\\n",
       "Lower (severe risk)     1.860000e+00  6.200000e-01  2.480000e-01   \n",
       "Lower (moderate risk)   1.860000e+00  6.200000e-01  2.480000e-01   \n",
       "Higher (safe)           7.440000e+00  2.480000e+00  9.920000e-01   \n",
       "Higher (moderate risk)  1.000000e+99  1.000000e+99  1.000000e+99   \n",
       "Higher (severe risk)    1.000000e+99  1.000000e+99  1.000000e+99   \n",
       "\n",
       "                        Phenylalanine      Tyrosine     Threonine  \\\n",
       "Lower (severe risk)      9.300000e-01  2.480000e-01  9.300000e-01   \n",
       "Lower (moderate risk)    9.300000e-01  2.480000e-01  9.300000e-01   \n",
       "Higher (safe)            3.720000e+00  9.920000e-01  3.720000e+00   \n",
       "Higher (moderate risk)   1.000000e+99  1.000000e+99  1.000000e+99   \n",
       "Higher (severe risk)     1.000000e+99  1.000000e+99  1.000000e+99   \n",
       "\n",
       "                          Tryptophan        Valine  Omega-3 (ALA)  \\\n",
       "Lower (severe risk)     2.480000e-01  1.612000e+00   1.350000e+00   \n",
       "Lower (moderate risk)   2.480000e-01  1.612000e+00   2.000000e+00   \n",
       "Higher (safe)           9.920000e-01  6.448000e+00   2.800000e+00   \n",
       "Higher (moderate risk)  1.000000e+99  1.000000e+99   4.670000e+00   \n",
       "Higher (severe risk)    1.000000e+99  1.000000e+99   1.000000e+99   \n",
       "\n",
       "                        Omega-6 (LA)  \n",
       "Lower (severe risk)     1.000000e+01  \n",
       "Lower (moderate risk)   1.450000e+01  \n",
       "Higher (safe)           1.867000e+01  \n",
       "Higher (moderate risk)  2.333000e+01  \n",
       "Higher (severe risk)    1.000000e+99  \n",
       "\n",
       "[5 rows x 45 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nut_needs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "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>Notes</th>\n",
       "      <th>Source</th>\n",
       "      <th>Energy</th>\n",
       "      <th>Protein digestibility</th>\n",
       "      <th>Protein</th>\n",
       "      <th>Fat total</th>\n",
       "      <th>Saturated fats</th>\n",
       "      <th>Trans fats</th>\n",
       "      <th>Carbohydrates</th>\n",
       "      <th>Fiber</th>\n",
       "      <th>...</th>\n",
       "      <th>Lysine</th>\n",
       "      <th>Methionine</th>\n",
       "      <th>Cysteine</th>\n",
       "      <th>Phenylalanine</th>\n",
       "      <th>Tyrosine</th>\n",
       "      <th>Threonine</th>\n",
       "      <th>Tryptophan</th>\n",
       "      <th>Valine</th>\n",
       "      <th>Omega-3 (ALA)</th>\n",
       "      <th>Omega-6 (LA)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Food</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Fish, tuna, fresh, skipjack, raw</th>\n",
       "      <td>0</td>\n",
       "      <td>https://fdc.nal.usda.gov/fdc-app.html#/food-de...</td>\n",
       "      <td>103.000000</td>\n",
       "      <td>0.900</td>\n",
       "      <td>21.359223</td>\n",
       "      <td>0.980583</td>\n",
       "      <td>0.318447</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.961165</td>\n",
       "      <td>0.632039</td>\n",
       "      <td>0.229126</td>\n",
       "      <td>0.833981</td>\n",
       "      <td>0.721359</td>\n",
       "      <td>0.935922</td>\n",
       "      <td>0.238835</td>\n",
       "      <td>1.100000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.015534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cattle (meat, mechanically separated)</th>\n",
       "      <td>mechanically separated, raw</td>\n",
       "      <td>https://fdc.nal.usda.gov/fdc-app.html#/food-de...</td>\n",
       "      <td>276.000000</td>\n",
       "      <td>0.950</td>\n",
       "      <td>5.423913</td>\n",
       "      <td>8.521739</td>\n",
       "      <td>4.268116</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.420652</td>\n",
       "      <td>0.154348</td>\n",
       "      <td>0.083333</td>\n",
       "      <td>0.233333</td>\n",
       "      <td>0.138043</td>\n",
       "      <td>0.171739</td>\n",
       "      <td>0.060870</td>\n",
       "      <td>0.331522</td>\n",
       "      <td>0.061594</td>\n",
       "      <td>0.213768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beef, variety meats and by-products, liver, raw</th>\n",
       "      <td>0</td>\n",
       "      <td>https://fdc.nal.usda.gov/fdc-app.html#/food-de...</td>\n",
       "      <td>135.000000</td>\n",
       "      <td>0.950</td>\n",
       "      <td>15.081481</td>\n",
       "      <td>2.688889</td>\n",
       "      <td>0.913333</td>\n",
       "      <td>0.125926</td>\n",
       "      <td>2.881481</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.190370</td>\n",
       "      <td>0.402222</td>\n",
       "      <td>0.278519</td>\n",
       "      <td>0.802963</td>\n",
       "      <td>0.597778</td>\n",
       "      <td>0.643704</td>\n",
       "      <td>0.194815</td>\n",
       "      <td>0.933333</td>\n",
       "      <td>0.005185</td>\n",
       "      <td>0.221481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chicken, broilers or fryers, meat and skin and giblets and neck, raw</th>\n",
       "      <td>0</td>\n",
       "      <td>https://fdc.nal.usda.gov/fdc-app.html#/food-de...</td>\n",
       "      <td>213.000000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>8.605634</td>\n",
       "      <td>6.962441</td>\n",
       "      <td>1.990610</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.061033</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.690141</td>\n",
       "      <td>0.226291</td>\n",
       "      <td>0.115493</td>\n",
       "      <td>0.337089</td>\n",
       "      <td>0.275117</td>\n",
       "      <td>0.356808</td>\n",
       "      <td>0.095305</td>\n",
       "      <td>0.419249</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.328638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mushrooms, shiitake, raw</th>\n",
       "      <td>0</td>\n",
       "      <td>https://fdc.nal.usda.gov/fdc-app.html#/food-de...</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>0.690</td>\n",
       "      <td>6.588235</td>\n",
       "      <td>1.441176</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>19.970588</td>\n",
       "      <td>7.352941</td>\n",
       "      <td>...</td>\n",
       "      <td>0.394118</td>\n",
       "      <td>0.097059</td>\n",
       "      <td>0.064706</td>\n",
       "      <td>0.326471</td>\n",
       "      <td>0.229412</td>\n",
       "      <td>0.394118</td>\n",
       "      <td>0.032353</td>\n",
       "      <td>0.426471</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beef, tripe, cooked, simmered</th>\n",
       "      <td>0</td>\n",
       "      <td>https://fdc.nal.usda.gov/fdc-app.html#/food-de...</td>\n",
       "      <td>94.000000</td>\n",
       "      <td>0.971</td>\n",
       "      <td>12.446809</td>\n",
       "      <td>4.308511</td>\n",
       "      <td>1.446809</td>\n",
       "      <td>0.212766</td>\n",
       "      <td>2.117021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.724404</td>\n",
       "      <td>0.296856</td>\n",
       "      <td>0.125090</td>\n",
       "      <td>0.413234</td>\n",
       "      <td>0.465511</td>\n",
       "      <td>0.470489</td>\n",
       "      <td>0.118245</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.008511</td>\n",
       "      <td>0.145745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cattle (organs)</th>\n",
       "      <td>calculated from individual organ nutritional v...</td>\n",
       "      <td>0</td>\n",
       "      <td>139.808421</td>\n",
       "      <td>0.950</td>\n",
       "      <td>14.722854</td>\n",
       "      <td>3.494903</td>\n",
       "      <td>1.369257</td>\n",
       "      <td>0.144928</td>\n",
       "      <td>1.504420</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.033903</td>\n",
       "      <td>0.363285</td>\n",
       "      <td>0.127949</td>\n",
       "      <td>0.387572</td>\n",
       "      <td>0.329675</td>\n",
       "      <td>0.606136</td>\n",
       "      <td>0.173915</td>\n",
       "      <td>0.180805</td>\n",
       "      <td>0.012212</td>\n",
       "      <td>0.175925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lignocellulosic sugar</th>\n",
       "      <td>sugar from cellulose</td>\n",
       "      <td>0</td>\n",
       "      <td>400.000000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bread (whole-wheat)</th>\n",
       "      <td>0</td>\n",
       "      <td>https://fdc.nal.usda.gov/fdc-app.html#/food-de...</td>\n",
       "      <td>278.000000</td>\n",
       "      <td>0.970</td>\n",
       "      <td>3.021583</td>\n",
       "      <td>1.942446</td>\n",
       "      <td>0.286331</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>18.489209</td>\n",
       "      <td>2.158273</td>\n",
       "      <td>...</td>\n",
       "      <td>0.087770</td>\n",
       "      <td>0.048921</td>\n",
       "      <td>0.066906</td>\n",
       "      <td>0.144964</td>\n",
       "      <td>0.090647</td>\n",
       "      <td>0.089209</td>\n",
       "      <td>0.043885</td>\n",
       "      <td>0.134892</td>\n",
       "      <td>0.114029</td>\n",
       "      <td>0.942446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sugar (beets)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>400.000000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                Notes  \\\n",
       "Food                                                                                                    \n",
       "Fish, tuna, fresh, skipjack, raw                                                                    0   \n",
       "Cattle (meat, mechanically separated)                                     mechanically separated, raw   \n",
       "Beef, variety meats and by-products, liver, raw                                                     0   \n",
       "Chicken, broilers or fryers, meat and skin and ...                                                  0   \n",
       "Mushrooms, shiitake, raw                                                                            0   \n",
       "...                                                                                               ...   \n",
       "Beef, tripe, cooked, simmered                                                                       0   \n",
       "Cattle (organs)                                     calculated from individual organ nutritional v...   \n",
       "Lignocellulosic sugar                                                            sugar from cellulose   \n",
       "Bread (whole-wheat)                                                                                 0   \n",
       "Sugar (beets)                                                                                       0   \n",
       "\n",
       "                                                                                               Source  \\\n",
       "Food                                                                                                    \n",
       "Fish, tuna, fresh, skipjack, raw                    https://fdc.nal.usda.gov/fdc-app.html#/food-de...   \n",
       "Cattle (meat, mechanically separated)               https://fdc.nal.usda.gov/fdc-app.html#/food-de...   \n",
       "Beef, variety meats and by-products, liver, raw     https://fdc.nal.usda.gov/fdc-app.html#/food-de...   \n",
       "Chicken, broilers or fryers, meat and skin and ...  https://fdc.nal.usda.gov/fdc-app.html#/food-de...   \n",
       "Mushrooms, shiitake, raw                            https://fdc.nal.usda.gov/fdc-app.html#/food-de...   \n",
       "...                                                                                               ...   \n",
       "Beef, tripe, cooked, simmered                       https://fdc.nal.usda.gov/fdc-app.html#/food-de...   \n",
       "Cattle (organs)                                                                                     0   \n",
       "Lignocellulosic sugar                                                                               0   \n",
       "Bread (whole-wheat)                                 https://fdc.nal.usda.gov/fdc-app.html#/food-de...   \n",
       "Sugar (beets)                                                                                       0   \n",
       "\n",
       "                                                        Energy  \\\n",
       "Food                                                             \n",
       "Fish, tuna, fresh, skipjack, raw                    103.000000   \n",
       "Cattle (meat, mechanically separated)               276.000000   \n",
       "Beef, variety meats and by-products, liver, raw     135.000000   \n",
       "Chicken, broilers or fryers, meat and skin and ...  213.000000   \n",
       "Mushrooms, shiitake, raw                             34.000000   \n",
       "...                                                        ...   \n",
       "Beef, tripe, cooked, simmered                        94.000000   \n",
       "Cattle (organs)                                     139.808421   \n",
       "Lignocellulosic sugar                               400.000000   \n",
       "Bread (whole-wheat)                                 278.000000   \n",
       "Sugar (beets)                                       400.000000   \n",
       "\n",
       "                                                    Protein digestibility  \\\n",
       "Food                                                                        \n",
       "Fish, tuna, fresh, skipjack, raw                                    0.900   \n",
       "Cattle (meat, mechanically separated)                               0.950   \n",
       "Beef, variety meats and by-products, liver, raw                     0.950   \n",
       "Chicken, broilers or fryers, meat and skin and ...                  1.000   \n",
       "Mushrooms, shiitake, raw                                            0.690   \n",
       "...                                                                   ...   \n",
       "Beef, tripe, cooked, simmered                                       0.971   \n",
       "Cattle (organs)                                                     0.950   \n",
       "Lignocellulosic sugar                                               0.000   \n",
       "Bread (whole-wheat)                                                 0.970   \n",
       "Sugar (beets)                                                       0.000   \n",
       "\n",
       "                                                      Protein  Fat total  \\\n",
       "Food                                                                       \n",
       "Fish, tuna, fresh, skipjack, raw                    21.359223   0.980583   \n",
       "Cattle (meat, mechanically separated)                5.423913   8.521739   \n",
       "Beef, variety meats and by-products, liver, raw     15.081481   2.688889   \n",
       "Chicken, broilers or fryers, meat and skin and ...   8.605634   6.962441   \n",
       "Mushrooms, shiitake, raw                             6.588235   1.441176   \n",
       "...                                                       ...        ...   \n",
       "Beef, tripe, cooked, simmered                       12.446809   4.308511   \n",
       "Cattle (organs)                                     14.722854   3.494903   \n",
       "Lignocellulosic sugar                                0.000000   0.000000   \n",
       "Bread (whole-wheat)                                  3.021583   1.942446   \n",
       "Sugar (beets)                                        0.000000   0.000000   \n",
       "\n",
       "                                                    Saturated fats  \\\n",
       "Food                                                                 \n",
       "Fish, tuna, fresh, skipjack, raw                          0.318447   \n",
       "Cattle (meat, mechanically separated)                     4.268116   \n",
       "Beef, variety meats and by-products, liver, raw           0.913333   \n",
       "Chicken, broilers or fryers, meat and skin and ...        1.990610   \n",
       "Mushrooms, shiitake, raw                                  0.000000   \n",
       "...                                                            ...   \n",
       "Beef, tripe, cooked, simmered                             1.446809   \n",
       "Cattle (organs)                                           1.369257   \n",
       "Lignocellulosic sugar                                     0.000000   \n",
       "Bread (whole-wheat)                                       0.286331   \n",
       "Sugar (beets)                                             0.000000   \n",
       "\n",
       "                                                    Trans fats  Carbohydrates  \\\n",
       "Food                                                                            \n",
       "Fish, tuna, fresh, skipjack, raw                      0.000000       0.000000   \n",
       "Cattle (meat, mechanically separated)                 0.000000       0.000000   \n",
       "Beef, variety meats and by-products, liver, raw       0.125926       2.881481   \n",
       "Chicken, broilers or fryers, meat and skin and ...    0.000000       0.061033   \n",
       "Mushrooms, shiitake, raw                              0.000000      19.970588   \n",
       "...                                                        ...            ...   \n",
       "Beef, tripe, cooked, simmered                         0.212766       2.117021   \n",
       "Cattle (organs)                                       0.144928       1.504420   \n",
       "Lignocellulosic sugar                                 0.000000      25.000000   \n",
       "Bread (whole-wheat)                                   0.000000      18.489209   \n",
       "Sugar (beets)                                         0.000000      25.000000   \n",
       "\n",
       "                                                       Fiber  ...    Lysine  \\\n",
       "Food                                                          ...             \n",
       "Fish, tuna, fresh, skipjack, raw                    0.000000  ...  1.961165   \n",
       "Cattle (meat, mechanically separated)               0.000000  ...  0.420652   \n",
       "Beef, variety meats and by-products, liver, raw     0.000000  ...  1.190370   \n",
       "Chicken, broilers or fryers, meat and skin and ...  0.000000  ...  0.690141   \n",
       "Mushrooms, shiitake, raw                            7.352941  ...  0.394118   \n",
       "...                                                      ...  ...       ...   \n",
       "Beef, tripe, cooked, simmered                       0.000000  ...  0.724404   \n",
       "Cattle (organs)                                     0.000000  ...  1.033903   \n",
       "Lignocellulosic sugar                               0.000000  ...  0.000000   \n",
       "Bread (whole-wheat)                                 2.158273  ...  0.087770   \n",
       "Sugar (beets)                                       0.000000  ...  0.000000   \n",
       "\n",
       "                                                    Methionine  Cysteine  \\\n",
       "Food                                                                       \n",
       "Fish, tuna, fresh, skipjack, raw                      0.632039  0.229126   \n",
       "Cattle (meat, mechanically separated)                 0.154348  0.083333   \n",
       "Beef, variety meats and by-products, liver, raw       0.402222  0.278519   \n",
       "Chicken, broilers or fryers, meat and skin and ...    0.226291  0.115493   \n",
       "Mushrooms, shiitake, raw                              0.097059  0.064706   \n",
       "...                                                        ...       ...   \n",
       "Beef, tripe, cooked, simmered                         0.296856  0.125090   \n",
       "Cattle (organs)                                       0.363285  0.127949   \n",
       "Lignocellulosic sugar                                 0.000000  0.000000   \n",
       "Bread (whole-wheat)                                   0.048921  0.066906   \n",
       "Sugar (beets)                                         0.000000  0.000000   \n",
       "\n",
       "                                                    Phenylalanine  Tyrosine  \\\n",
       "Food                                                                          \n",
       "Fish, tuna, fresh, skipjack, raw                         0.833981  0.721359   \n",
       "Cattle (meat, mechanically separated)                    0.233333  0.138043   \n",
       "Beef, variety meats and by-products, liver, raw          0.802963  0.597778   \n",
       "Chicken, broilers or fryers, meat and skin and ...       0.337089  0.275117   \n",
       "Mushrooms, shiitake, raw                                 0.326471  0.229412   \n",
       "...                                                           ...       ...   \n",
       "Beef, tripe, cooked, simmered                            0.413234  0.465511   \n",
       "Cattle (organs)                                          0.387572  0.329675   \n",
       "Lignocellulosic sugar                                    0.000000  0.000000   \n",
       "Bread (whole-wheat)                                      0.144964  0.090647   \n",
       "Sugar (beets)                                            0.000000  0.000000   \n",
       "\n",
       "                                                    Threonine  Tryptophan  \\\n",
       "Food                                                                        \n",
       "Fish, tuna, fresh, skipjack, raw                     0.935922    0.238835   \n",
       "Cattle (meat, mechanically separated)                0.171739    0.060870   \n",
       "Beef, variety meats and by-products, liver, raw      0.643704    0.194815   \n",
       "Chicken, broilers or fryers, meat and skin and ...   0.356808    0.095305   \n",
       "Mushrooms, shiitake, raw                             0.394118    0.032353   \n",
       "...                                                       ...         ...   \n",
       "Beef, tripe, cooked, simmered                        0.470489    0.118245   \n",
       "Cattle (organs)                                      0.606136    0.173915   \n",
       "Lignocellulosic sugar                                0.000000    0.000000   \n",
       "Bread (whole-wheat)                                  0.089209    0.043885   \n",
       "Sugar (beets)                                        0.000000    0.000000   \n",
       "\n",
       "                                                      Valine  Omega-3 (ALA)  \\\n",
       "Food                                                                          \n",
       "Fish, tuna, fresh, skipjack, raw                    1.100000       0.000000   \n",
       "Cattle (meat, mechanically separated)               0.331522       0.061594   \n",
       "Beef, variety meats and by-products, liver, raw     0.933333       0.005185   \n",
       "Chicken, broilers or fryers, meat and skin and ...  0.419249       0.000000   \n",
       "Mushrooms, shiitake, raw                            0.426471       0.000000   \n",
       "...                                                      ...            ...   \n",
       "Beef, tripe, cooked, simmered                       0.000000       0.008511   \n",
       "Cattle (organs)                                     0.180805       0.012212   \n",
       "Lignocellulosic sugar                               0.000000       0.000000   \n",
       "Bread (whole-wheat)                                 0.134892       0.114029   \n",
       "Sugar (beets)                                       0.000000       0.000000   \n",
       "\n",
       "                                                    Omega-6 (LA)  \n",
       "Food                                                              \n",
       "Fish, tuna, fresh, skipjack, raw                        0.015534  \n",
       "Cattle (meat, mechanically separated)                   0.213768  \n",
       "Beef, variety meats and by-products, liver, raw         0.221481  \n",
       "Chicken, broilers or fryers, meat and skin and ...      1.328638  \n",
       "Mushrooms, shiitake, raw                                0.000000  \n",
       "...                                                          ...  \n",
       "Beef, tripe, cooked, simmered                           0.145745  \n",
       "Cattle (organs)                                         0.175925  \n",
       "Lignocellulosic sugar                                   0.000000  \n",
       "Bread (whole-wheat)                                     0.942446  \n",
       "Sugar (beets)                                           0.000000  \n",
       "\n",
       "[61 rows x 49 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nut_calc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trying to combine foods to reach avg of adequate intake.\n",
    "With\n",
    "\n",
    "`food_subset=['Beef, variety meats and by-products, mechanically separated beef, raw',\n",
    "             'Milk, whole, 3.25% milkfat, without added vitamin A and vitamin D',\n",
    "            'Rice, white, long-grain, regular, raw, unenriched',\n",
    "            'Corn, sweet, yellow, raw','Barley, hulled','Anchovy, canned','Soy flour, full-fat, raw',\n",
    "            'Soybeans, mature seeds, raw', 'Beef, organ meal, cooked', 'Bread, whole-wheat']\n",
    "`\n",
    "\n",
    "We should find something like `[100, 100, 250, 300, 200, 250, 400, 150, 100, 250]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def f(x, target): # function giving normalized error, target is the middle of the adequate intake range\n",
    "    return (target-x)/target\n",
    "\n",
    "def weighted_errors(val, nut_needs_subset, nut, target):\n",
    "    # get all thresholds\n",
    "    lower_severe = nut_needs_subset.at['Lower (severe risk)', nut]\n",
    "    lower_moderate = nut_needs_subset.at['Lower (moderate risk)', nut]\n",
    "    higher_safe = nut_needs_subset.at['Higher (safe)', nut]\n",
    "    higher_moderate = nut_needs_subset.at['Higher (moderate risk)', nut]\n",
    "    higher_severe = nut_needs_subset.at['Higher (severe risk)', nut]\n",
    "    # put differentiated weight depending on intake category, should be continuous\n",
    "    if val>higher_severe:\n",
    "        y = np.exp(f(val, target)) - np.exp(f(higher_severe, target)) + f(higher_severe, target)**4 - f(higher_moderate, target)**4 + f(higher_moderate, target)**2 - f(higher_safe, target)**2\n",
    "    elif val>higher_moderate:\n",
    "        y = f(val, target)**4 - f(higher_moderate, target)**4 + f(higher_moderate, target)**2 - f(higher_safe, target)**2\n",
    "    elif val>higher_safe:\n",
    "        y = f(val, target)**2 - f(higher_safe, target)**2\n",
    "    elif (val>= lower_moderate) and (val<=higher_safe):\n",
    "        y = 0\n",
    "    elif val<lower_severe:\n",
    "        y = f(val, target)**4 - f(lower_severe, target)**4 + f(lower_severe, target)**2 - f(lower_moderate, target)**2\n",
    "    elif val<lower_moderate:\n",
    "        y = f(val, target)**2 - f(lower_moderate, target)**2\n",
    "    return y\n",
    "\n",
    "\n",
    "def intake_cat(X, nut_needs, nut_subset, nut_calc, food_subset):# gives the sum of weighted errors for each nutrient\n",
    "    nut_calc_subset=nut_calc.loc[food_subset]\n",
    "    nut_calc_subset=np.array(nut_calc_subset[nut_subset])\n",
    "    nut_needs_subset = nut_needs[nut_subset]\n",
    "    #target = 1.1*np.array(nut_needs_subset.loc['Lower (moderate risk)']\n",
    "    target = (np.array(nut_needs_subset.loc['Lower (moderate risk)'])+np.array(nut_needs_subset.loc['Higher (safe)']))/2\n",
    "    solution = np.abs(np.dot(np.transpose(nut_calc_subset), X)-target)\n",
    "    Y=np.zeros((len(nut_subset)))\n",
    "    for i in range(len(nut_subset)):\n",
    "        nut = nut_subset[i]\n",
    "        val = solution[i]\n",
    "        Y[i] = weighted_errors(val, nut_needs_subset, nut, target[i])\n",
    "    return np.sum(Y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "diets_excel=pd.ExcelFile(\"Diets by period.xlsx\")\n",
    "\n",
    "sheets = diets_excel.sheet_names\n",
    "diets = pd.read_excel(\"Diets by period.xlsx\", index_col=0, sheet_name=sheets[0])\n",
    "# select some foods to combine\n",
    "diets_bis = diets.drop('Total:')\n",
    "food_subset = list(diets_bis.index)\n",
    "\n",
    "# select the nutrients we want to look at (bug when lower bound is 0 so those nutrients are removed)\n",
    "nut_subset=['Protein', 'Fat total',\n",
    "            #'Saturated fats', 'Trans fats',\n",
    "            'Carbohydrates','Fiber',\n",
    "            #'Sugars',\n",
    "            'Vitamin A', 'Vitamin E', 'Vitamin D', 'Vitamin C',\n",
    "           'Thiamine (B1)', 'Riboflavin (B2)', 'Niacin (B3)', 'Vitamin B6',\n",
    "           'Vitamin B12', 'Vitamin K', 'Folate (B9)', 'Pantothenic Acid (B5)',\n",
    "           'Calcium', 'Iron', 'Magnesium', 'Phosphorus', 'Potassium', 'Sodium',\n",
    "           'Zinc', 'Copper', 'Manganese', 'Selenium', 'Iodine', 'Omega-3 (ALA)', 'Omega-6 (LA)']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "Cattle (meat ...: 224 kcal\n",
      "Milk (whole) ...: 0 kcal\n",
      "Rice ...: 1264 kcal\n",
      "Potatoes ...: 140 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 0 kcal\n",
      "Canola oil ...: 472 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 0 kcal\n",
      "Bread (whole ...: 0 kcal\n",
      "1\n",
      "Cattle (meat ...: 1 kcal\n",
      "Milk (whole) ...: 94 kcal\n",
      "Rice ...: 1881 kcal\n",
      "Potatoes ...: 97 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 0 kcal\n",
      "Canola oil ...: 26 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 0 kcal\n",
      "Bread (whole ...: 0 kcal\n",
      "2\n",
      "Cattle (meat ...: 2 kcal\n",
      "Milk (whole) ...: 92 kcal\n",
      "Rice ...: 1883 kcal\n",
      "Potatoes ...: 96 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 0 kcal\n",
      "Canola oil ...: 27 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 0 kcal\n",
      "Bread (whole ...: 0 kcal\n",
      "3\n",
      "Cattle (meat ...: 0 kcal\n",
      "Milk (whole) ...: 0 kcal\n",
      "Rice ...: 1605 kcal\n",
      "Potatoes ...: 0 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 495 kcal\n",
      "Canola oil ...: 0 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 0 kcal\n",
      "Bread (whole ...: 0 kcal\n",
      "4\n",
      "Cattle (meat ...: 547 kcal\n",
      "Milk (whole) ...: 0 kcal\n",
      "Rice ...: 1068 kcal\n",
      "Potatoes ...: 0 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 0 kcal\n",
      "Canola oil ...: 485 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 0 kcal\n",
      "Bread (whole ...: 0 kcal\n",
      "5\n",
      "Cattle (meat ...: 9 kcal\n",
      "Milk (whole) ...: 79 kcal\n",
      "Rice ...: 1894 kcal\n",
      "Potatoes ...: 91 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 0 kcal\n",
      "Canola oil ...: 28 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 0 kcal\n",
      "Bread (whole ...: 0 kcal\n",
      "6\n",
      "Cattle (meat ...: 0 kcal\n",
      "Milk (whole) ...: 0 kcal\n",
      "Rice ...: 1174 kcal\n",
      "Potatoes ...: 557 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 204 kcal\n",
      "Canola oil ...: 0 kcal\n",
      "Soy flour ...: 103 kcal\n",
      "Cattle (orga ...: 62 kcal\n",
      "Bread (whole ...: 0 kcal\n",
      "7\n",
      "Cattle (meat ...: 10 kcal\n",
      "Milk (whole) ...: 77 kcal\n",
      "Rice ...: 1895 kcal\n",
      "Potatoes ...: 90 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 0 kcal\n",
      "Canola oil ...: 28 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 0 kcal\n",
      "Bread (whole ...: 0 kcal\n",
      "8\n",
      "Cattle (meat ...: 5 kcal\n",
      "Milk (whole) ...: 85 kcal\n",
      "Rice ...: 1889 kcal\n",
      "Potatoes ...: 94 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 0 kcal\n",
      "Canola oil ...: 27 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 0 kcal\n",
      "Bread (whole ...: 0 kcal\n",
      "9\n",
      "Cattle (meat ...: 105 kcal\n",
      "Milk (whole) ...: 40 kcal\n",
      "Rice ...: 844 kcal\n",
      "Potatoes ...: 0 kcal\n",
      "Barley ...: 88 kcal\n",
      "Anchovy ...: 498 kcal\n",
      "Canola oil ...: 486 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 10 kcal\n",
      "Bread (whole ...: 28 kcal\n",
      "Min of 10 trials\n",
      "Cattle (meat ...: 1 kcal\n",
      "Milk (whole) ...: 94 kcal\n",
      "Rice ...: 1881 kcal\n",
      "Potatoes ...: 97 kcal\n",
      "Barley ...: 0 kcal\n",
      "Anchovy ...: 0 kcal\n",
      "Canola oil ...: 26 kcal\n",
      "Soy flour ...: 0 kcal\n",
      "Cattle (orga ...: 0 kcal\n",
      "Bread (whole ...: 0 kcal\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:19: VisibleDeprecationWarning: Creating an ndarray from ragged nested sequences (which is a list-or-tuple of lists-or-tuples-or ndarrays with different lengths or shapes) is deprecated. If you meant to do this, you must specify 'dtype=object' when creating the ndarray.\n"
     ]
    }
   ],
   "source": [
    "bounds = optimize.Bounds(np.zeros(len(food_subset)), 21*np.ones(len(food_subset))) # bounds are 0 and 21*100 kcal for each food\n",
    "constraint = optimize.LinearConstraint(np.ones(len(food_subset)), 21, 21) # the sum of all caloric intake should be 21(*100 kcal)\n",
    "\n",
    "coef_list = []\n",
    "n_trials=10\n",
    "for i in range(n_trials):\n",
    "    X0 = 10*np.random.rand(len(food_subset), 1) # start with a somewhat random initial guess\n",
    "    coefs = optimize.minimize(intake_cat, X0,\n",
    "                              (nut_needs, nut_subset, nut_calc, food_subset),\n",
    "                              constraints=constraint,\n",
    "                              bounds=bounds,\n",
    "                             method='SLSQP') # optimize using SLSQP method\n",
    "    if coefs.success: # gather results to find the min of several trials\n",
    "        coef_list += [[coefs.x, coefs.fun]]\n",
    "        print(i)\n",
    "        for j in range(len(food_subset)):\n",
    "            print(food_subset[j][:12], '...:', round(coefs.x[j]*100), 'kcal')\n",
    "# find the min of the different results\n",
    "coef_array = np.array(coef_list)\n",
    "i_min = np.argmin(coef_array[:, 1])\n",
    "selected_coef = coef_array[i_min][0]\n",
    "print('Min of', n_trials, 'trials') # print the resulting diet\n",
    "for i in range(len(food_subset)):\n",
    "    print(food_subset[i][:12], '...:', round(selected_coef[i]*100), 'kcal')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Protein                    43.771276\n",
      "Fat total                  11.664714\n",
      "Saturated fats              4.117473\n",
      "Trans fats                  0.011768\n",
      "Carbohydrates             441.845556\n",
      "Fiber                       8.705062\n",
      "Sugars                      9.426902\n",
      "Vitamin A                  71.001442\n",
      "Vitamin E                1206.273879\n",
      "Vitamin D                   0.154351\n",
      "Vitamin C                  14.481824\n",
      "Thiamine (B1)               0.550231\n",
      "Riboflavin (B2)             0.536296\n",
      "Niacin (B3)                 9.999979\n",
      "Vitamin B6                  1.235410\n",
      "Vitamin B12                 0.708220\n",
      "Vitamin K                   5.553395\n",
      "Folate (B9)                60.114126\n",
      "Pantothenic Acid (B5)       6.382400\n",
      "Calcium                   326.875972\n",
      "Iron                        4.544859\n",
      "Magnesium                 168.877353\n",
      "Phosphorus                773.078850\n",
      "Potassium                1220.102346\n",
      "Sodium                     96.899364\n",
      "Zinc                        6.541967\n",
      "Copper                   1382.142556\n",
      "Manganese                   5.767258\n",
      "Selenium                   83.977237\n",
      "Iodine                     35.904305\n",
      "Chromium                    0.000000\n",
      "Molybdenum                  0.000000\n",
      "Histidine                   1.060499\n",
      "Isoleucine                  1.926725\n",
      "Leucine                     3.628228\n",
      "Lysine                      1.870337\n",
      "Methionine                  1.029632\n",
      "Cysteine                    0.809737\n",
      "Phenylalanine               2.311067\n",
      "Tyrosine                    1.550908\n",
      "Threonine                   1.599316\n",
      "Tryptophan                  0.522703\n",
      "Valine                      2.681692\n",
      "Omega-3 (ALA)               0.400022\n",
      "Omega-6 (LA)                1.542665\n",
      "dtype: float64\n",
      "AI ['Saturated fats', 'Trans fats', 'Sugars', 'Pantothenic Acid (B5)', 'Phosphorus', 'Manganese', 'Selenium', 'Histidine', 'Isoleucine', 'Leucine', 'Lysine', 'Methionine', 'Cysteine', 'Phenylalanine', 'Threonine', 'Tryptophan', 'Valine'] \n",
      "\n",
      "MRAI ['Carbohydrates 110%', 'Sodium 5%', 'Copper 95%', 'Tyrosine 156%'] \n",
      "\n",
      "SRAI ['Protein 86%', 'Fat total 25%', 'Fiber 35%', 'Vitamin A 10%', 'Vitamin E 8%', 'Vitamin D 1%', 'Vitamin C 18%', 'Thiamine (B1) 48%', 'Riboflavin (B2) 34%', 'Niacin (B3) 67%', 'Vitamin B6 75%', 'Vitamin B12 18%', 'Vitamin K 5%', 'Folate (B9) 18%', 'Calcium 33%', 'Iron 34%', 'Magnesium 47%', 'Potassium 26%', 'Zinc 69%', 'Iodine 24%', 'Omega-3 (ALA) 20%', 'Omega-6 (LA) 11%'] \n",
      "\n",
      "Danger ['Chromium 0%', 'Molybdenum 0%']\n"
     ]
    }
   ],
   "source": [
    "nut_calc_subset=nut_calc.loc[food_subset]\n",
    "nut_calc_subset_t=nut_calc_subset[nut_needs.columns].T\n",
    "combination = pd.Series(selected_coef, index=food_subset)\n",
    "#combination = pd.Series([100, 100, 250, 300, 200, 250, 400, 150, 100, 250], index=food_subset)/100\n",
    "diet = nut_calc_subset_t @ combination\n",
    "print(diet)\n",
    "\n",
    "nut_needs_subset = nut_needs[nut_subset]\n",
    "SRAI = []\n",
    "MRAI = []\n",
    "AI = []\n",
    "danger = []\n",
    "for i in range(len(diet)): # for the solution, divide in categories for each nutrient\n",
    "    nutrient=diet[i]\n",
    "    nut=diet.index[i]\n",
    "    lower_severe = nut_needs.at['Lower (severe risk)', nut]\n",
    "    lower_moderate = nut_needs.at['Lower (moderate risk)', nut]\n",
    "    higher_safe = nut_needs.at['Higher (safe)', nut]\n",
    "    higher_moderate = nut_needs.at['Higher (moderate risk)', nut]\n",
    "    higher_severe = nut_needs.at['Higher (severe risk)', nut]\n",
    "    if (nutrient>= lower_moderate) and (nutrient<=higher_safe):\n",
    "        AI += [nut]\n",
    "    elif (nutrient==0):\n",
    "        danger+= [nut + ' 0%']\n",
    "    elif (nutrient>higher_severe):\n",
    "        danger+= [nut + ' ' + str(round(nutrient/higher_safe*100))+'%']\n",
    "    elif (nutrient<lower_severe):\n",
    "        SRAI += [nut + ' ' + str(round(nutrient/lower_moderate*100))+'%']\n",
    "    elif (nutrient>higher_moderate):\n",
    "        SRAI += [nut + ' ' + str(round(nutrient/higher_safe*100))+'%']\n",
    "    elif (nutrient< lower_moderate):\n",
    "        MRAI += [nut + ' ' + str(round(nutrient/lower_moderate*100))+'%']\n",
    "    elif (nutrient>higher_safe):\n",
    "        MRAI += [nut + ' ' + str(round(nutrient/higher_safe*100))+'%']\n",
    "        \n",
    "print('AI', AI, '\\n\\nMRAI', MRAI, '\\n\\nSRAI', SRAI, '\\n\\nDanger', danger)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# just to see the shape of the weighted error function, that should be continuous\n",
    "nut_subset_fig=['Fat total']\n",
    "\n",
    "nut_calc_subset=nut_calc.loc[food_subset]\n",
    "nut_calc_subset=nut_calc_subset[nut_subset_fig]\n",
    "nut_needs_subset = nut_needs[nut_subset_fig]\n",
    "nut = nut_needs_subset.columns[0]\n",
    "\n",
    "lower_severe = nut_needs_subset.at['Lower (severe risk)', nut]\n",
    "lower_moderate = nut_needs_subset.at['Lower (moderate risk)', nut]\n",
    "higher_safe = nut_needs_subset.at['Higher (safe)', nut]\n",
    "higher_moderate = nut_needs_subset.at['Higher (moderate risk)', nut]\n",
    "higher_severe = nut_needs_subset.at['Higher (severe risk)', nut]\n",
    "\n",
    "target = (np.array(nut_needs_subset.loc['Lower (moderate risk)'])+np.array(nut_needs_subset.loc['Higher (safe)']))/2\n",
    "\n",
    "X = np.linspace(0, higher_moderate+10)\n",
    "Y=np.zeros(X.shape)\n",
    "\n",
    "for i in range(len(X)):\n",
    "    val=X[i]\n",
    "    Y[i] = weighted_errors(val, nut_needs_subset, nut, target)   \n",
    "    #print(val, err, Y[i])\n",
    "        \n",
    "plt.plot(X, Y)\n",
    "plt.axvline(0, color='r')\n",
    "plt.axvline(lower_severe, color='orange')\n",
    "plt.axvline(lower_moderate, color='green')\n",
    "plt.axvline(higher_safe, color='lightgreen')\n",
    "plt.axvline(higher_moderate, color='peachpuff')\n",
    "#plt.axvline(higher_severe, color='lightcoral')\n",
    "plt.title(nut_subset_fig[0] + ' weighted error function')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A la mano\n",
    "Caloric intake determined by hand, calculation of the weighted error function and adjustements when possible"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "diets_excel=pd.ExcelFile(\"Diets by period.xlsx\")\n",
    "\n",
    "sheets = diets_excel.sheet_names\n",
    "diets = pd.read_excel(\"Diets by period.xlsx\", index_col=0, sheet_name=sheets[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "diets = diets.drop('Total:')\n",
    "diets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "X = diets['kcal']\n",
    "X"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nut_subset=['Protein', 'Fat total',\n",
    "            'Saturated fats', 'Trans fats',\n",
    "            'Carbohydrates','Fiber',\n",
    "            'Sugars',\n",
    "            'Vitamin A', 'Vitamin E', 'Vitamin D', 'Vitamin C',\n",
    "           'Thiamine (B1)', 'Riboflavin (B2)', 'Niacin (B3)', 'Vitamin B6',\n",
    "           'Vitamin B12', 'Vitamin K', 'Folate (B9)', 'Pantothenic Acid (B5)',\n",
    "           'Calcium', 'Iron', 'Magnesium', 'Phosphorus', 'Potassium', 'Sodium',\n",
    "           'Zinc', 'Copper', 'Manganese', 'Selenium', 'Iodine', 'Omega-3 (ALA)', 'Omega-6 (LA)']\n",
    "food_subset = list(diets.index)\n",
    "y1 = intake_cat(X, nut_needs, nut_subset, nut_calc, food_subset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "X = [400, 300, 650, 250, 250, 250]\n",
    "y2 = intake_cat(X, nut_needs, nut_subset, nut_calc, food_subset)\n",
    "print(np.sum(X), y2-y1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "y1=y2\n",
    "X = [500, 300, 650, 250, 250, 150]\n",
    "y2 = intake_cat(X, nut_needs, nut_subset, nut_calc, food_subset)\n",
    "print(np.sum(X), y2-y1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}