class_08_datascience1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "417c5a88",
   "metadata": {},
   "source": [
    "# Class 8:  Data Science 1 — Pandas, SQL, Regressions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "928529ff",
   "metadata": {},
   "source": [
    "1. Come in. Sit down. Open Teams.\n",
    "2. Make sure your notebook from last class is saved.\n",
    "3. Open up the Jupyter Lab server.\n",
    "4. Open up the Jupyter Lab terminal.\n",
    "5. Activate Conda: `module load anaconda3/2022.05`\n",
    "6. Activate the shared virtual environment: `source activate /courses/PHYS7332.202510/shared/phys7332-env/`\n",
    "7. Run `python3 git_fixer2.py`\n",
    "8. Github:\n",
    "    - git status (figure out what files have changed)\n",
    "    - git add ... (add the file that you changed, aka the `_MODIFIED` one(s))\n",
    "    - git commit -m \"your changes\"\n",
    "    - git push origin main\n",
    "________"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0e43ef7",
   "metadata": {},
   "source": [
    "## Goals of today's class\n",
    "1. Get comfortable using `pandas` for tabular data\n",
    "2. Learn how to use basic SQL commands\n",
    "3. Understand how regressions work at a very high level."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fec59c7",
   "metadata": {},
   "source": [
    "## Pandas\n",
    "Adapted from [10 minutes to pandas](https://pandas.pydata.org/docs/user_guide/10min.html)\n",
    "\n",
    "pandas is a Python library for manipulating & analyzing data.\n",
    "\n",
    "We usually import pandas like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "a367c521",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bfc7785",
   "metadata": {},
   "source": [
    "pandas has two basic units that we care about: a `Series` and a `DataFrame`. `Series` are roughly akin to lists -- they're one-dimensional and can hold data of any type. `DataFrames` are two-dimensional (they have rows and columns) and are indexed by one or more columns. This means that a row can be identified by the labels in the index column. Each column of a `DataFrame` is also a `Series`. \n",
    "\n",
    "We'll focus on `DataFrames` for now. We can populate a `DataFrame` in a few different ways. First, let's instantiate a `DataFrame` with our own data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07c81924",
   "metadata": {},
   "source": [
    "## Making a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "081fa862",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({\n",
    "    \"Name\": [\"Orca\", \"Blue Whale\", \"Beluga Whale\", \"Narwhal\"],\n",
    "    \"Weight\": [22000, 441000, 2530, 3500],\n",
    "    \"Cute\":[True, True, True, True],\n",
    "    \"Habitat\": [\"Everywhere\", \"Everywhere\", \"Arctic\", \"Arctic\"],\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d035aad",
   "metadata": {},
   "source": [
    "Let's look at the types the columns of `df` have: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bfc84236",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name       object\n",
       "Weight      int64\n",
       "Cute         bool\n",
       "Habitat    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c493995",
   "metadata": {},
   "source": [
    "Wow! These are a lot of different types! \n",
    "\n",
    "We can also access the column names like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2335107c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Name', 'Weight', 'Cute', 'Habitat'], dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d120cee8",
   "metadata": {},
   "source": [
    "or see the first 3 entries like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "81e09f3c",
   "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>Name</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Cute</th>\n",
       "      <th>Habitat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Orca</td>\n",
       "      <td>22000</td>\n",
       "      <td>True</td>\n",
       "      <td>Everywhere</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Blue Whale</td>\n",
       "      <td>441000</td>\n",
       "      <td>True</td>\n",
       "      <td>Everywhere</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Beluga Whale</td>\n",
       "      <td>2530</td>\n",
       "      <td>True</td>\n",
       "      <td>Arctic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Name  Weight  Cute     Habitat\n",
       "0          Orca   22000  True  Everywhere\n",
       "1    Blue Whale  441000  True  Everywhere\n",
       "2  Beluga Whale    2530  True      Arctic"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0241575b",
   "metadata": {},
   "source": [
    "## Common Pandas operations\n",
    "### Sorting\n",
    "Now we'll practice some common operations in pandas. One thing we might want to do is sort a dataframe by a particular column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7061e646",
   "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>Name</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Cute</th>\n",
       "      <th>Habitat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Blue Whale</td>\n",
       "      <td>441000</td>\n",
       "      <td>True</td>\n",
       "      <td>Everywhere</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Orca</td>\n",
       "      <td>22000</td>\n",
       "      <td>True</td>\n",
       "      <td>Everywhere</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Narwhal</td>\n",
       "      <td>3500</td>\n",
       "      <td>True</td>\n",
       "      <td>Arctic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Beluga Whale</td>\n",
       "      <td>2530</td>\n",
       "      <td>True</td>\n",
       "      <td>Arctic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Name  Weight  Cute     Habitat\n",
       "1    Blue Whale  441000  True  Everywhere\n",
       "0          Orca   22000  True  Everywhere\n",
       "3       Narwhal    3500  True      Arctic\n",
       "2  Beluga Whale    2530  True      Arctic"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(by=\"Weight\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f58e52fa",
   "metadata": {},
   "source": [
    "What happens if we change the `ascending` parameter to True?\n",
    "Can you sort the dataframe alphabetically by species name? \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "69aa6506",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your turn!!!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "058d3582",
   "metadata": {},
   "source": [
    "### Filtering\n",
    "Another common thing we can do in Pandas is filtering. This means we only select the rows that match a specific condition. Let's filter for all the cetaceans whose weight is over 5000 pounds:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "04522bec",
   "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>Name</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Cute</th>\n",
       "      <th>Habitat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Orca</td>\n",
       "      <td>22000</td>\n",
       "      <td>True</td>\n",
       "      <td>Everywhere</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Blue Whale</td>\n",
       "      <td>441000</td>\n",
       "      <td>True</td>\n",
       "      <td>Everywhere</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Name  Weight  Cute     Habitat\n",
       "0        Orca   22000  True  Everywhere\n",
       "1  Blue Whale  441000  True  Everywhere"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.Weight > 5000]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6f79730",
   "metadata": {},
   "source": [
    "Here, we're referring to the `Weight` column as an _attribute_ of our dataframe `df`. However, we can also refer to the column like we would a key in a dictionary, with the same result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2fc7057c",
   "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>Name</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Cute</th>\n",
       "      <th>Habitat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Orca</td>\n",
       "      <td>22000</td>\n",
       "      <td>True</td>\n",
       "      <td>Everywhere</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Blue Whale</td>\n",
       "      <td>441000</td>\n",
       "      <td>True</td>\n",
       "      <td>Everywhere</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Name  Weight  Cute     Habitat\n",
       "0        Orca   22000  True  Everywhere\n",
       "1  Blue Whale  441000  True  Everywhere"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Weight'] > 5000]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a41c354d",
   "metadata": {},
   "source": [
    "Can you filter the dataframe to get all the cetaceans that live in the Arctic?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fdab82ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your turn!!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8398bbb1",
   "metadata": {},
   "source": [
    "### Adding a column\n",
    "We can also add a column to the dataframe as long as it's the same length as the existing dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d587ae07",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Food\"] = [\"Everything\", \"Krill\", \"Squid, Clams, Octopus, Cod, Herring\", \"Fish\"]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e723e176",
   "metadata": {},
   "source": [
    "And we can create a column from an existing column using the `apply` function:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "19cbb914",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_from_lbs_to_kg(wt):\n",
    "    return wt * 0.45\n",
    "df[\"Weight_kg\"] = df[\"Weight\"].apply(convert_from_lbs_to_kg)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "428bb029",
   "metadata": {},
   "source": [
    "When we apply a function, we can use an actual function, as we did above, or we can use an __anonymous function__, or a \"lambda function\". "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "02603ece",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Weight_tons\"] = df[\"Weight\"].apply(lambda b: b / 2000)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b725376b",
   "metadata": {},
   "source": [
    "### Grouping\n",
    "\n",
    "We also might want to group our dataframe into chunks that have something in common."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "985d5578",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Arctic\n",
      "           Name  Weight  Cute Habitat                                 Food  \\\n",
      "2  Beluga Whale    2530  True  Arctic  Squid, Clams, Octopus, Cod, Herring   \n",
      "3       Narwhal    3500  True  Arctic                                 Fish   \n",
      "\n",
      "   Weight_kg  Weight_tons  \n",
      "2     1138.5        1.265  \n",
      "3     1575.0        1.750  \n",
      "Everywhere\n",
      "         Name  Weight  Cute     Habitat        Food  Weight_kg  Weight_tons\n",
      "0        Orca   22000  True  Everywhere  Everything     9900.0         11.0\n",
      "1  Blue Whale  441000  True  Everywhere       Krill   198450.0        220.5\n"
     ]
    }
   ],
   "source": [
    "groups = df.groupby('Habitat')\n",
    "for habitat, df_gr in groups:\n",
    "    print(habitat)\n",
    "    print(df_gr.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4657c48b",
   "metadata": {},
   "source": [
    "Here, we're grouping our dataframe by the `Habitat` column. `groups` is an iterable (we can iterate over the items in it, in the order given to us) that has two variables. One of them is the unique value of the column we grouped by (`Everywhere` and `Arctic`) and the other one is the chunk of the dataframe that had that unique value in that column. \n",
    "\n",
    "Can you group the cetaceans that weigh more than 5000 pounds apart from the ones that weigh less than 5000 pounds? (Hint: You might have to create a new column!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a7eb72e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your Turn!!!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12d01421",
   "metadata": {},
   "source": [
    "## Practicing with Pandas\n",
    "We can also load a pandas dataframe from a `.csv` (comma separated values) file. We're going to load up `WhaleFromSpaceDB_Whales.csv`, which we sourced from the [UK Polar Data Centre](https://ramadda.data.bas.ac.uk/repository/entry/show?entryid=c1afe32c-493c-4dc7-af9f-649593b97b2c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e5f29bff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              MstLklSp SpAbbr PtlOtrSp  BoL  BoW  BoS  BoC  FlukeP  Blow  \\\n",
      "0  Eubalaena australis    SRW     None    2    1    1    2       0     0   \n",
      "1  Eubalaena australis    SRW     None    2    1    1    2       0     0   \n",
      "2  Eubalaena australis    SRW     None    1    1    1    2       0     0   \n",
      "3  Eubalaena australis    SRW     None    1    1    1    2       0     0   \n",
      "4  Eubalaena australis    SRW     None    1    1    1    2       0     0   \n",
      "\n",
      "   Contour  ...        Long   ImageID  \\\n",
      "0        0  ...  166.281952  1.01E+15   \n",
      "1        0  ...  166.278841  1.01E+15   \n",
      "2        0  ...  166.274348  1.01E+15   \n",
      "3        0  ...  166.291132  1.01E+15   \n",
      "4        0  ...  166.268754  1.01E+15   \n",
      "\n",
      "                                      ImageFile  ImageDate  Satellite  \\\n",
      "0  06AUG12231250-P2AS-052609152010_01_P001+M2AS   20060812        QB2   \n",
      "1  06AUG12231250-P2AS-052609152010_01_P001+M2AS   20060812        QB2   \n",
      "2  06AUG12231250-P2AS-052609152010_01_P001+M2AS   20060812        QB2   \n",
      "3  06AUG12231250-P2AS-052609152010_01_P001+M2AS   20060812        QB2   \n",
      "4  06AUG12231250-P2AS-052609152010_01_P001+M2AS   20060812        QB2   \n",
      "\n",
      "   NumWhale        BoxSize  SpatialRes                  BoxID/ImageChip  \\\n",
      "0         1  128x128pixels       0.54m  Auckland_SRW_QB2_PS_20060812_B0   \n",
      "1         1  128x128pixels       0.54m  Auckland_SRW_QB2_PS_20060812_B1   \n",
      "2         1  128x128pixels       0.54m  Auckland_SRW_QB2_PS_20060812_B2   \n",
      "3         1  128x128pixels       0.54m  Auckland_SRW_QB2_PS_20060812_B3   \n",
      "4         1  128x128pixels       0.54m  Auckland_SRW_QB2_PS_20060812_B4   \n",
      "\n",
      "                           PointID  \n",
      "0  Auckland_SRW_QB2_PS_20060812_P0  \n",
      "1  Auckland_SRW_QB2_PS_20060812_P1  \n",
      "2  Auckland_SRW_QB2_PS_20060812_P2  \n",
      "3  Auckland_SRW_QB2_PS_20060812_P3  \n",
      "4  Auckland_SRW_QB2_PS_20060812_P4  \n",
      "\n",
      "[5 rows x 34 columns]\n",
      "Index(['MstLklSp', 'SpAbbr', 'PtlOtrSp', 'BoL', 'BoW', 'BoS', 'BoC', 'FlukeP',\n",
      "       'Blow', 'Contour', 'Wake', 'AfterB', 'Defecation', 'MudTrail',\n",
      "       'OtherDistu', 'Fluke', 'Flipper', 'HeadCalosi', 'Movement',\n",
      "       'Certainty2', 'ClassScore', 'Location', 'GCS', 'Lat', 'Long', 'ImageID',\n",
      "       'ImageFile', 'ImageDate', 'Satellite', 'NumWhale', 'BoxSize',\n",
      "       'SpatialRes', 'BoxID/ImageChip', 'PointID'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "df_whales = pd.read_csv('data/WhaleFromSpaceDB_Whales.csv')\n",
    "print(df_whales.head(5))\n",
    "print(df_whales.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70b65d64",
   "metadata": {},
   "source": [
    "Let's explore the dataset a little bit! The `value_counts` function tells us how many times each unique value showed up in a column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b9edde37",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Eubalaena australis       463\n",
       "Eschrichtius robustus      80\n",
       "Megaptera novaeangliae     56\n",
       "Balaenoptera physalus      34\n",
       "Name: MstLklSp, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_whales['MstLklSp'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0104c215",
   "metadata": {},
   "source": [
    "Now let's check for flukes! Flukes are valuable to marine biologists because they help them identify individuals. For each whale species, can you count how many times flukes were and were not seen?\n",
    "\n",
    "We have a column, `Certainty2`, that tells us how certain the identification of a whale was. \n",
    "Let's assign numeric values to each of the identification values. We'll say `Definite = 1.0`, `Probable = 0.8`, and `Possible = 0.6`. \n",
    "What is the average certainty for each species? (Hint: `df.column.mean()` gives the average value of a column, and you'll have to use the `apply` function to create a numeric column for certainty!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f737108a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your Turn!!!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b010cfa4",
   "metadata": {},
   "source": [
    "## SQL\n",
    "\n",
    "### What is SQL?\n",
    "SQL stands for \"__Structured Query Language__\". It is a way to query a particular kind of database that has **tabular data**."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51597a99",
   "metadata": {},
   "source": [
    "### What is tabular data?\n",
    "Tabular data lends itself well to being organized in rows and columns. Each row represents one instance/observation, and each column represents a different attribute the instances/observations have. \n",
    "\n",
    "### When is it used?\n",
    "SQL is used a lot in industry to store and query data for analytic purposes. When you have a ton of data to deal with, SQL is a great way to do that. \n",
    "\n",
    "## Getting connected to SQL on Discovery\n",
    "We'll connect to SQL on Discovery using `SQLAlchemy`. This is a package that lets you connect to many different kinds of SQL databases using Python, so we can connect from right inside our Jupyter notebooks on Discovery. \n",
    "\n",
    "## Secrets\n",
    "You'll notice that I didn't give you the MySQL username and password in plain text in this notebook; this is intentional. While this particular set of credentials isn't very useful unless you have Discovery access, it's nonetheless good practice to **never commit credentials to git** and **never share credentials in plain text with others via the internet**. If you do commit credentials or other secret to GitHub, [this](https://docs.github.com/en/authentication/keeping-your-account-and-data-secure/removing-sensitive-data-from-a-repository) is how you get rid of the problematic commits. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cc5610e5",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "my_creds = []\n",
    "with open('../../../shared/student_mysql_credentials.txt', 'r') as f:\n",
    "    for line in f.readlines():\n",
    "        my_creds.append(line.strip())\n",
    "\n",
    "hostname=\"mysql-0005\"\n",
    "dbname=\"PHYS7332\"\n",
    "uname=my_creds[0]\n",
    "pwd=my_creds[1]\n",
    "\n",
    "engine = create_engine(\"mysql+pymysql://{user}:{pw}@{host}/{db}\"\n",
    "                           .format(host=hostname, db=dbname,\n",
    "                                   user=uname,pw=pwd))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "818afdfe",
   "metadata": {},
   "source": [
    "## How do we query a SQL table?\n",
    "We can query a SQL table by giving `pandas` a SQL engine object using the `sqlalchemy` package. This makes our life easier so that we just have to worry about the SQL syntax. \n",
    "\n",
    "The most basic way to extract data from an SQL table is selecting everything. Here we have a table of species in a Florida marine ecosystem, their node IDs, and the category of organisms they belong to. Let's select them all:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ff40f0a7",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>node_id</th>\n",
       "      <th>name</th>\n",
       "      <th>my_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2um Spherical Phytoplankt</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Synedococcus</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Oscillatoria</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Small Diatoms (&lt;20um)</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Big Diatoms (&gt;20um)</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>123</td>\n",
       "      <td>Benthic POC</td>\n",
       "      <td>Detritus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>124</td>\n",
       "      <td>DOC</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>125</td>\n",
       "      <td>Input</td>\n",
       "      <td>Detritus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>126</td>\n",
       "      <td>Output</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>127</td>\n",
       "      <td>Respiration</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     node_id                       name  my_group\n",
       "0          0  2um Spherical Phytoplankt      NULL\n",
       "1          1               Synedococcus      NULL\n",
       "2          2               Oscillatoria      NULL\n",
       "3          3      Small Diatoms (<20um)      NULL\n",
       "4          4        Big Diatoms (>20um)      NULL\n",
       "..       ...                        ...       ...\n",
       "123      123                Benthic POC  Detritus\n",
       "124      124                        DOC      NULL\n",
       "125      125                      Input  Detritus\n",
       "126      126                     Output      NULL\n",
       "127      127                Respiration      NULL\n",
       "\n",
       "[128 rows x 3 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qu = 'SELECT * FROM fla_species;'\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e7ea82b",
   "metadata": {},
   "source": [
    "This will get us everything in `fla_species`, so sometimes it's not a good idea to pull **all the data**. In this case, we can set a limit of 5 entries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "f80100ba",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": 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>node_id</th>\n",
       "      <th>name</th>\n",
       "      <th>my_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2um Spherical Phytoplankt</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Synedococcus</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Oscillatoria</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Small Diatoms (&lt;20um)</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Big Diatoms (&gt;20um)</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   node_id                       name my_group\n",
       "0        0  2um Spherical Phytoplankt     NULL\n",
       "1        1               Synedococcus     NULL\n",
       "2        2               Oscillatoria     NULL\n",
       "3        3      Small Diatoms (<20um)     NULL\n",
       "4        4        Big Diatoms (>20um)     NULL"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qu = 'SELECT * FROM fla_species LIMIT 5;'\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "544bfe48",
   "metadata": {},
   "source": [
    "But what if we want to filter our data? In this example, we select all entries from the table named `table` that have a value in `group` that is equal to `'Pelagic Fishes'`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "93668182",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": 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>node_id</th>\n",
       "      <th>name</th>\n",
       "      <th>my_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>56</td>\n",
       "      <td>Sardines</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>57</td>\n",
       "      <td>Anchovy</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58</td>\n",
       "      <td>Bay Anchovy</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>64</td>\n",
       "      <td>Halfbeaks</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>65</td>\n",
       "      <td>Needlefish</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>71</td>\n",
       "      <td>Silverside</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>90</td>\n",
       "      <td>Mullet</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>98</td>\n",
       "      <td>Other Pelagic Fishes</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   node_id                  name        my_group\n",
       "0       56              Sardines  Pelagic Fishes\n",
       "1       57               Anchovy  Pelagic Fishes\n",
       "2       58           Bay Anchovy  Pelagic Fishes\n",
       "3       64             Halfbeaks  Pelagic Fishes\n",
       "4       65            Needlefish  Pelagic Fishes\n",
       "5       71            Silverside  Pelagic Fishes\n",
       "6       90                Mullet  Pelagic Fishes\n",
       "7       98  Other Pelagic Fishes  Pelagic Fishes"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qu = \"SELECT * FROM fla_species WHERE my_group = 'Pelagic Fishes';\"\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0284f98c",
   "metadata": {},
   "source": [
    "Or if we want to select based on multiple conditions, we can do this:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "904d33df",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": 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>node_id</th>\n",
       "      <th>name</th>\n",
       "      <th>my_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71</td>\n",
       "      <td>Silverside</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90</td>\n",
       "      <td>Mullet</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>98</td>\n",
       "      <td>Other Pelagic Fishes</td>\n",
       "      <td>Pelagic Fishes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   node_id                  name        my_group\n",
       "0       71            Silverside  Pelagic Fishes\n",
       "1       90                Mullet  Pelagic Fishes\n",
       "2       98  Other Pelagic Fishes  Pelagic Fishes"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qu = \"SELECT * FROM fla_species WHERE my_group = 'Pelagic Fishes' AND node_id > 65;\"\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57f26c6f",
   "metadata": {},
   "source": [
    "We can select just one or two columns by replacing the `*` with the column names, and if we don't want duplicate rows, we can use `SELECT DISTINCT`: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "fbeddec8",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": 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>my_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Demersal Producer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Seagrass Producer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            my_group\n",
       "0               NULL\n",
       "1  Demersal Producer\n",
       "2  Seagrass Producer"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qu = \"SELECT DISTINCT my_group FROM fla_species WHERE node_id < 10;\"\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0acfc3df",
   "metadata": {},
   "source": [
    "## Aggregating\n",
    "What if we want to get aggregate information about the species we're seeing? We can use an operation called `GROUP BY` in SQL to do this. `GROUP BY` puts the table into chunks that all have the same value of the variable we're grouping by. Then, we can aggregate the values in other columns to learn about each group in aggregate. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "057baddc",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": 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>my_group</th>\n",
       "      <th>COUNT(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Algae Producer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Benthic Fishes</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Demersal Fishes</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Demersal Producer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Detritus</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Macroinvertebrates</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Microbial Microfauna</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NULL</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Pelagic Fishes</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Seagrass Producer</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Sediment Organism Microfauna</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Zooplankton Microfauna</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        my_group  COUNT(*)\n",
       "0                 Algae Producer         2\n",
       "1                 Benthic Fishes         7\n",
       "2                Demersal Fishes         5\n",
       "3              Demersal Producer         1\n",
       "4                       Detritus         3\n",
       "5             Macroinvertebrates        21\n",
       "6           Microbial Microfauna         3\n",
       "7                           NULL        66\n",
       "8                 Pelagic Fishes         8\n",
       "9              Seagrass Producer         3\n",
       "10  Sediment Organism Microfauna         3\n",
       "11        Zooplankton Microfauna         6"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qu = \"SELECT my_group, COUNT(*) FROM fla_species GROUP BY my_group;\"\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f94b641f",
   "metadata": {},
   "source": [
    "Here, we're grabbing each value of `group`, a column that refers to the species type, and counting how many rows have that value in their `group` column. `COUNT(*)` tells us how many rows are in each chunk. Other commonly used aggregate operations include `MIN`, `MAX`, and `AVG`. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "601a6b76",
   "metadata": {},
   "source": [
    "## Joining\n",
    "We also have a network of species that forms a food web in this Florida marine ecosystem. In a food web edge `(i, j)`, energy (or carbon) flows from organism `i` to organism `j`. A whale eating krill would be rpresented as `(krill, whale)`, for example. Let's select the first few rows of data about our food web:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6402ec89",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": 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>RowId</th>\n",
       "      <th>FromNodeId</th>\n",
       "      <th>ToNodeId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>125</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>125</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>125</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>125</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>125</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>125</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>125</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>125</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>125</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>125</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RowId  FromNodeId  ToNodeId\n",
       "0      0         125         0\n",
       "1      1         125         1\n",
       "2      2         125         2\n",
       "3      3         125         3\n",
       "4      4         125         4\n",
       "5      5         125         5\n",
       "6      6         125         6\n",
       "7      7         125         7\n",
       "8      8         125         8\n",
       "9      9         125         9"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qu = \"SELECT * FROM fla_foodweb LIMIT 10;\"\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71aed94c",
   "metadata": {},
   "source": [
    "Here we can see that species are referred to by their node IDs -- we saw those in the other table, `fla_species`. What if we want information on the species incorporated with our information on which species consume each other?\n",
    "\n",
    "We can *join tables* to make this happen. When we join tables, we usually pick a *condition* to use to make sure we are joining together rows correctly. In this case, we want to join based on the ID of the node. Let's try an inner join, also just referred to in SQL as `JOIN`. This matches all items that have values for `ToNodeId` in `fla_foodweb` and `NodeId` in `fla_species`. It does not match items that do not have values in **both tables**. Let's see what happens:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "4c3d7072",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": 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>RowId</th>\n",
       "      <th>FromNodeId</th>\n",
       "      <th>ToNodeId</th>\n",
       "      <th>node_id</th>\n",
       "      <th>name</th>\n",
       "      <th>my_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>125</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2um Spherical Phytoplankt</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>125</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Synedococcus</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>125</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Oscillatoria</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>125</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Small Diatoms (&lt;20um)</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>125</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Big Diatoms (&gt;20um)</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>125</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>Dinoflagellates</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>125</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>Other Phytoplankton</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>125</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>Benthic Phytoplankton</td>\n",
       "      <td>Demersal Producer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>125</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>Thalassia</td>\n",
       "      <td>Seagrass Producer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>125</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>Halodule</td>\n",
       "      <td>Seagrass Producer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RowId  FromNodeId  ToNodeId  node_id                       name  \\\n",
       "0      0         125         0        0  2um Spherical Phytoplankt   \n",
       "1      1         125         1        1               Synedococcus   \n",
       "2      2         125         2        2               Oscillatoria   \n",
       "3      3         125         3        3      Small Diatoms (<20um)   \n",
       "4      4         125         4        4        Big Diatoms (>20um)   \n",
       "5      5         125         5        5            Dinoflagellates   \n",
       "6      6         125         6        6        Other Phytoplankton   \n",
       "7      7         125         7        7      Benthic Phytoplankton   \n",
       "8      8         125         8        8                  Thalassia   \n",
       "9      9         125         9        9                   Halodule   \n",
       "\n",
       "            my_group  \n",
       "0               NULL  \n",
       "1               NULL  \n",
       "2               NULL  \n",
       "3               NULL  \n",
       "4               NULL  \n",
       "5               NULL  \n",
       "6               NULL  \n",
       "7  Demersal Producer  \n",
       "8  Seagrass Producer  \n",
       "9  Seagrass Producer  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qu = \"\"\"\n",
    "    SELECT * FROM fla_foodweb JOIN fla_species ON \\\n",
    "    fla_foodweb.ToNodeId = fla_species.node_id LIMIT 10;\"\"\"\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0438c54",
   "metadata": {},
   "source": [
    "Now we have a species name for each species that's consuming another species! \n",
    "\n",
    "Other types of joins include left joins and right joins. A **left join** will keep all the rows of the left table and add information from the right table if it matches on the condition we're using to match up items. A right join does the opposite. \n",
    "\n",
    "We're also going to create a variable in SQL to do some fancy joining now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "dc536892",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": 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>ToNodeId</th>\n",
       "      <th>COUNT(FromNodeId)</th>\n",
       "      <th>node_id</th>\n",
       "      <th>name</th>\n",
       "      <th>my_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2um Spherical Phytoplankt</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Synedococcus</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Oscillatoria</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Small Diatoms (&lt;20um)</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>Big Diatoms (&gt;20um)</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>123.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>123</td>\n",
       "      <td>Benthic POC</td>\n",
       "      <td>Detritus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>124.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>124</td>\n",
       "      <td>DOC</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125</td>\n",
       "      <td>Input</td>\n",
       "      <td>Detritus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>126.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>126</td>\n",
       "      <td>Output</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>127.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>127</td>\n",
       "      <td>Respiration</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ToNodeId  COUNT(FromNodeId)  node_id                       name  my_group\n",
       "0         0.0                1.0        0  2um Spherical Phytoplankt      NULL\n",
       "1         1.0                1.0        1               Synedococcus      NULL\n",
       "2         2.0                1.0        2               Oscillatoria      NULL\n",
       "3         3.0                1.0        3      Small Diatoms (<20um)      NULL\n",
       "4         4.0                1.0        4        Big Diatoms (>20um)      NULL\n",
       "..        ...                ...      ...                        ...       ...\n",
       "123     123.0               44.0      123                Benthic POC  Detritus\n",
       "124     124.0               13.0      124                        DOC      NULL\n",
       "125       NaN                NaN      125                      Input  Detritus\n",
       "126     126.0               40.0      126                     Output      NULL\n",
       "127     127.0              110.0      127                Respiration      NULL\n",
       "\n",
       "[128 rows x 5 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qu = \"\"\"\n",
    "        SELECT * FROM (SELECT ToNodeId, COUNT(FromNodeId) FROM \\\n",
    "        fla_foodweb GROUP BY ToNodeId) \\\n",
    "        species_eaten RIGHT JOIN fla_species ON \\\n",
    "        species_eaten.ToNodeId = fla_species.node_id;\"\"\"\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a41d858c",
   "metadata": {},
   "source": [
    "What are we doing here? First, we're creating a variable in SQL. It's called `species_eaten`, and it itself is a table. We make it by grouping `fla_foodweb` by `ToNodeId` -- the organism doing the consuming. Then, we count how many other types of organisms that each unique value of `ToNodeId` consumed. That makes our table `species_eaten`!\n",
    "\n",
    "Now we join it with `fla_species` to get species names attached to our number of species consumed by each organism! Because we're doing a `RIGHT JOIN` operation, and `fla_species` is the right hand table, we're going to keep all the rows of `fla_species`, even if a species does not consume any other species. Some of the rows returned might have null values in some columns, and that is okay!\n",
    "\n",
    "Can you figure out how to count the number of unique species consumed by each **group** of species? (Hint: the `SELECT DISTINCT` or `COUNT DISTINCT`[operations](https://www.w3schools.com/Sql/sql_distinct.asp) might come in handy here. These only select unique values or combinations of values (if you select multiple columns), so `SELECT DISTINCT group FROM fla_species;` will only show you each value `group` takes once). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69b57577",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your Turn!!!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14be0aa5",
   "metadata": {},
   "source": [
    "## Regressions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a483204",
   "metadata": {},
   "source": [
    "Regressions are used to figure out how variables in a dataset are related to each other. Usually, you have a dependent variable (the __outcome__ we care about) and some set of __independent variables__ that influence the outcome. For example, let's say we have a bunch of fish and their dimensional measurements. We want to figure out how much the fish weighs, given its measurements. Regression is one way we can do this!\n",
    "\n",
    "In its most basic form, a linear regression will try to minimize the __sum of squared error__. Let's say we have a bunch of observations $y_i$ and $x_i$. Each $y_i$ is a numeric value, and each $x_i$ is a vector of dependent variables. We want to find a vector $\\beta$ such that $\\sum_{i=0}^{n} (\\beta x_i - y_i)^2$ is minimized. This is also referred to the sum of the squares of the residuals -- __residual__ is another word for how incorrect an individual prediction is. In aggregate, we want the amount that we are wrong to be as small as possible. \n",
    "\n",
    "We often use $R^2$, or the [\"coefficient of determination,\"](https://en.wikipedia.org/wiki/Coefficient_of_determination)  to estimate how well a regression explains data. Formally, it takes the following form:\n",
    "$SS_{res} = \\sum_{i=0}^{n} (\\beta x_i - y_i)^2$ is the sum of the squares of the __residuals__ (remember, this is how wrong an individual prediction is).\n",
    "$SS_{tot} = \\sum_{i=0}^{n} (y_i - \\bar{y})^2$ is the __total sum of squares__ - this is proportional to the variance of the dataset. \n",
    "\n",
    "Now, $R^2 = 1 - \\frac{SS_{res}}{SS_{tot}}$. In other words, we're asking \"how big is our error compared to all the variance in the dataset?\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7ac4d21",
   "metadata": {},
   "source": [
    "### When are regressions useful?\n",
    "Regressions are useful for figuring out how different independent variables affect a dependent variable. They are also helpful in predicting how a system might act in the future. For example, if you have a lot of fish and a linear regression model that can predict their weights pretty well, you can then measure even more fish and predict their weights! "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3492e756",
   "metadata": {},
   "source": [
    "### When do they fall short?\n",
    "Sometimes data isn't linear! Other times, you might have something that looks like a good fit, but it doesn't explain your data well. [Anscombe's Quartet](https://en.wikipedia.org/wiki/Anscombe%27s_quartet) is a good example that explains why just going ahead and fitting a linear model to your data may not always be wise! \n",
    "\n",
    "![Anscombe's Quartet](images/anscombe_quartet.png)\n",
    "\n",
    "As you can see, these datasets are doing very different things, but the linear model that best describes them is the same for all four datasets. It also fits each dataset as well as the next: the $R^2$ is $0.67$ for all the regressions. This is a cautionary tale: sometimes a linear model is simply not the right model!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "799161a7",
   "metadata": {},
   "source": [
    "## Running a linear regression on a Pandas dataframe\n",
    "Now we're going to load another CSV; this time, it will be full of fish measurements ([source](https://www.kaggle.com/datasets/vipullrathod/fish-market)) that we will attempt to correlate to the fishes' weights."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "893b6ad6",
   "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>Species</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Length1</th>\n",
       "      <th>Length2</th>\n",
       "      <th>Length3</th>\n",
       "      <th>Height</th>\n",
       "      <th>Width</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bream</td>\n",
       "      <td>242.0</td>\n",
       "      <td>23.2</td>\n",
       "      <td>25.4</td>\n",
       "      <td>30.0</td>\n",
       "      <td>11.5200</td>\n",
       "      <td>4.0200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bream</td>\n",
       "      <td>290.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>26.3</td>\n",
       "      <td>31.2</td>\n",
       "      <td>12.4800</td>\n",
       "      <td>4.3056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bream</td>\n",
       "      <td>340.0</td>\n",
       "      <td>23.9</td>\n",
       "      <td>26.5</td>\n",
       "      <td>31.1</td>\n",
       "      <td>12.3778</td>\n",
       "      <td>4.6961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bream</td>\n",
       "      <td>363.0</td>\n",
       "      <td>26.3</td>\n",
       "      <td>29.0</td>\n",
       "      <td>33.5</td>\n",
       "      <td>12.7300</td>\n",
       "      <td>4.4555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bream</td>\n",
       "      <td>430.0</td>\n",
       "      <td>26.5</td>\n",
       "      <td>29.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>12.4440</td>\n",
       "      <td>5.1340</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Species  Weight  Length1  Length2  Length3   Height   Width\n",
       "0   Bream   242.0     23.2     25.4     30.0  11.5200  4.0200\n",
       "1   Bream   290.0     24.0     26.3     31.2  12.4800  4.3056\n",
       "2   Bream   340.0     23.9     26.5     31.1  12.3778  4.6961\n",
       "3   Bream   363.0     26.3     29.0     33.5  12.7300  4.4555\n",
       "4   Bream   430.0     26.5     29.0     34.0  12.4440  5.1340"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fish = pd.read_csv('data/Fish.csv')\n",
    "df_fish.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10d8ef8c",
   "metadata": {},
   "source": [
    "First, we'll try doing a basic regression with sklearn. We need to make two `numpy` arrays for the linear regression module to use. So we're going to learn how to grab `numpy` arrays from a pandas dataframe. We'll use the columns `Length1`, `Length2`, `Length3`, `Height`, and `Width` as our _independent variables_. Our _dependent variable_, of course,  is `Weight`. \n",
    "\n",
    "To get a subset of columns from a dataframe, we simply list the columns we want. then call the `.numpy()` function. This converts our dataframe into a numeric numpy array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "184c993c",
   "metadata": {},
   "outputs": [],
   "source": [
    "col_subset = df_fish[['Length1', 'Length2', 'Length3', 'Height', 'Width']]\n",
    "x_mtx = col_subset.to_numpy()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73c54d4b",
   "metadata": {},
   "source": [
    "Can you figure out how to obtain the array of fish weights?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "513291ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your Turn!!!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bd86ee5",
   "metadata": {},
   "source": [
    "### Importing `sklearn`\n",
    "`sklearn`, or [scikit-learn](https://scikit-learn.org/stable/index.html), is a Python package that has a bunch of machine learning algorithms built in. We're going to use it to practice running linear regressions. We're also going to import `numpy`, which is a great [package](https://numpy.org/) for numerical computation and array manipulation. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "a77d241a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LinearRegression()"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn import linear_model\n",
    "import numpy as np\n",
    "reg = linear_model.LinearRegression()\n",
    "reg.fit(x_mtx, y_mtx)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a3ca33c",
   "metadata": {},
   "source": [
    "We have created a `LinearRegression` object, called `reg`. When we call the `fit()` method, we compute the best coefficients to multiply the values in `x_mtx` by to get something close to `y_mtx`. How good is our model? The `score()` method gives us $R^2$. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "f741a7ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8852867046546207"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reg.score(x_mtx, y_mtx)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8afcca6",
   "metadata": {},
   "source": [
    "That's not too bad!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81a9baec",
   "metadata": {},
   "source": [
    "#### Adding in Fish Species\n",
    "What if our fish are different densities depending on species? Let's see if we can make our regression a little better. One way to do this is to introduce a _categorical variable_. In this case, we'll have a column for each fish species. Its entry for each row will take the value $1$ if the fish in that row is of that species; otherwise, it'll be $0$. This is really easy to do with `pandas`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "a76ec3bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fish[\"fish_category\"] = df_fish[\"Species\"].astype(\"category\")\n",
    "fish_dummies = pd.get_dummies(df_fish['fish_category'], prefix='species_')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c5a567a",
   "metadata": {},
   "source": [
    "We've created what's called one-hot encodings, or dummmy variables, which is great news for our fish regression! Let's create a matrix of species dummy variables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "17a89377",
   "metadata": {},
   "outputs": [],
   "source": [
    "species_mtx = fish_dummies.to_numpy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3e9d677",
   "metadata": {},
   "source": [
    "And now we'll concatenate that (along the horizontal axis) to our original matrix of fish measurements:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "dee52a99",
   "metadata": {},
   "outputs": [],
   "source": [
    "x_new = np.hstack([species_mtx, x_mtx])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4157714",
   "metadata": {},
   "source": [
    "We'll make a new regression object:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "bf3aa7c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LinearRegression()"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reg1 = linear_model.LinearRegression()\n",
    "reg1.fit(x_new, y_mtx)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d7ccf47",
   "metadata": {},
   "source": [
    "How well did we do?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "46a534fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9360849020585846"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reg1.score(x_new, y_mtx)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a90e6def",
   "metadata": {},
   "source": [
    "That's better! Adding more information doesn't always help, but this time around it did!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0b64bc6",
   "metadata": {},
   "source": [
    "### Getting Fancy\n",
    "We can also use something called a *kernel function* to transform our linear data into higher dimensional space. Specifically, we can apply arbitrary functions to our fish data and add them to our linear regression matrices. Let's say we have two concentric circles as a dataset:\n",
    "![Concentric circles](images/concentric_circles.png)\n",
    "We're going to have a hard time separating these with either of their $x$ and $y$ coordinates! But what if we add a new column that is $\\sqrt{x_i^2 + y_i^2}$? We're able to separate them easily because we've projected our points into a higher dimensional space. Neat!!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39646abc",
   "metadata": {},
   "source": [
    "Let's try applying this to the fish dataset. Can you engineer a new column for the fish dataset that improves your $R^2$?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "04654f30",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your Turn!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71afc1e6",
   "metadata": {},
   "source": [
    "## Resources & Acknowledgements\n",
    "\n",
    "Our intro to `pandas` borrows heavily from [10 minutes to pandas](https://pandas.pydata.org/docs/user_guide/10min.html)\n",
    "\n",
    "Our whale dataset comes from the [UK Polar Data Centre](https://ramadda.data.bas.ac.uk/repository/entry/show?entryid=c1afe32c-493c-4dc7-af9f-649593b97b2c)\n",
    "\n",
    "Our fish dataset is sourced from [Kaggle](https://www.kaggle.com/datasets/vipullrathod/fish-market)\n",
    "\n",
    "The kernel function image comes from the [Carpentries Incubator](https://carpentries-incubator.github.io/machine-learning-novice-sklearn/04-clustering/index.html)\n",
    "\n",
    "For more on kernel methods, check out [this textbook chapter](https://people.eecs.berkeley.edu/~jordan/kernels/0521813972c02_p25-46.pdf)\n",
    "\n",
    "Here's a [list](https://www.sqltutorial.org/) of SQL commands you might want to know at some point."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "368be901",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Edit Metadata",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}