{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercise 5.2: Adding data to a data frame\n",
    "\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "We continue working with the frog tongue data. Recall that the header comments in the data file contained information about the frogs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# These data are from the paper,\n",
      "#   Kleinteich and Gorb, Sci. Rep., 4, 5225, 2014.\n",
      "# It was featured in the New York Times.\n",
      "#    http://www.nytimes.com/2014/08/25/science/a-frog-thats-a-living-breathing-pac-man.html\n",
      "#\n",
      "# The authors included the data in their supplemental information.\n",
      "#\n",
      "# Importantly, the ID refers to the identifites of the frogs they tested.\n",
      "#   I:   adult, 63 mm snout-vent-length (SVL) and 63.1 g body weight,\n",
      "#        Ceratophrys cranwelli crossed with Ceratophrys cornuta\n",
      "#   II:  adult, 70 mm SVL and 72.7 g body weight,\n",
      "#        Ceratophrys cranwelli crossed with Ceratophrys cornuta\n",
      "#   III: juvenile, 28 mm SVL and 12.7 g body weight, Ceratophrys cranwelli\n",
      "#   IV:  juvenile, 31 mm SVL and 12.7 g body weight, Ceratophrys cranwelli\n",
      "date,ID,trial number,impact force (mN),impact time (ms),impact force / body weight,adhesive force (mN),time frog pulls on target (ms),adhesive force / body weight,adhesive impulse (N-s),total contact area (mm2),contact area without mucus (mm2),contact area with mucus / contact area without mucus,contact pressure (Pa),adhesive strength (Pa)\n",
      "2013_02_26,I,3,1205,46,1.95,-785,884,1.27,-0.290,387,70,0.82,3117,-2030\n",
      "2013_02_26,I,4,2527,44,4.08,-983,248,1.59,-0.181,101,94,0.07,24923,-9695\n",
      "2013_03_01,I,1,1745,34,2.82,-850,211,1.37,-0.157,83,79,0.05,21020,-10239\n",
      "2013_03_01,I,2,1556,41,2.51,-455,1025,0.74,-0.170,330,158,0.52,4718,-1381\n",
      "2013_03_01,I,3,493,36,0.80,-974,499,1.57,-0.423,245,216,0.12,2012,-3975\n"
     ]
    }
   ],
   "source": [
    "!head -20 data/frog_tongue_adhesion.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, each frog has associated with it an age (adult or juvenile), snout-vent-length (SVL), body weight, and species (either cross or *cranwelli*). For a tidy data frame, we should have a column for each of these values. Your task is to load in the data, and then add these columns to the data frame. For convenience, here is a data frame with data about each frog."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_frog = pd.DataFrame(\n",
    "    data={\n",
    "        \"ID\": [\"I\", \"II\", \"III\", \"IV\"],\n",
    "        \"age\": [\"adult\", \"adult\", \"juvenile\", \"juvenile\"],\n",
    "        \"SVL (mm)\": [63, 70, 28, 31],\n",
    "        \"weight (g)\": [63.1, 72.7, 12.7, 12.7],\n",
    "        \"species\": [\"cross\", \"cross\", \"cranwelli\", \"cranwelli\"],\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: There are lots of ways to solve this problem. This is a good exercise in searching through the [Pandas documentation](https://pandas.pydata.org/pandas-docs/stable/) and other online resources, such as [Stack Overflow](https://stackoverflow.com/questions). Remember, much of your programming efforts are spent searching through documentation and the internet."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Solution\n",
    "\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The most direct way is to use [built-in pd.merge() function](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html). This function finds a common column between two `DataFrames`, and then uses that column to merge them, filling in values that match in the common column. This is exactly what we want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the data\n",
    "df = pd.read_csv('data/frog_tongue_adhesion.csv', comment='#')\n",
    "\n",
    "# Perform merge\n",
    "df = df.merge(df_frog)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at the `DataFrame` to make sure it has what we expect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>date</th>\n",
       "      <th>ID</th>\n",
       "      <th>trial number</th>\n",
       "      <th>impact force (mN)</th>\n",
       "      <th>impact time (ms)</th>\n",
       "      <th>impact force / body weight</th>\n",
       "      <th>adhesive force (mN)</th>\n",
       "      <th>time frog pulls on target (ms)</th>\n",
       "      <th>adhesive force / body weight</th>\n",
       "      <th>adhesive impulse (N-s)</th>\n",
       "      <th>total contact area (mm2)</th>\n",
       "      <th>contact area without mucus (mm2)</th>\n",
       "      <th>contact area with mucus / contact area without mucus</th>\n",
       "      <th>contact pressure (Pa)</th>\n",
       "      <th>adhesive strength (Pa)</th>\n",
       "      <th>age</th>\n",
       "      <th>SVL (mm)</th>\n",
       "      <th>weight (g)</th>\n",
       "      <th>species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013_02_26</td>\n",
       "      <td>I</td>\n",
       "      <td>3</td>\n",
       "      <td>1205</td>\n",
       "      <td>46</td>\n",
       "      <td>1.95</td>\n",
       "      <td>-785</td>\n",
       "      <td>884</td>\n",
       "      <td>1.27</td>\n",
       "      <td>-0.290</td>\n",
       "      <td>387</td>\n",
       "      <td>70</td>\n",
       "      <td>0.82</td>\n",
       "      <td>3117</td>\n",
       "      <td>-2030</td>\n",
       "      <td>adult</td>\n",
       "      <td>63</td>\n",
       "      <td>63.1</td>\n",
       "      <td>cross</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013_02_26</td>\n",
       "      <td>I</td>\n",
       "      <td>4</td>\n",
       "      <td>2527</td>\n",
       "      <td>44</td>\n",
       "      <td>4.08</td>\n",
       "      <td>-983</td>\n",
       "      <td>248</td>\n",
       "      <td>1.59</td>\n",
       "      <td>-0.181</td>\n",
       "      <td>101</td>\n",
       "      <td>94</td>\n",
       "      <td>0.07</td>\n",
       "      <td>24923</td>\n",
       "      <td>-9695</td>\n",
       "      <td>adult</td>\n",
       "      <td>63</td>\n",
       "      <td>63.1</td>\n",
       "      <td>cross</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013_03_01</td>\n",
       "      <td>I</td>\n",
       "      <td>1</td>\n",
       "      <td>1745</td>\n",
       "      <td>34</td>\n",
       "      <td>2.82</td>\n",
       "      <td>-850</td>\n",
       "      <td>211</td>\n",
       "      <td>1.37</td>\n",
       "      <td>-0.157</td>\n",
       "      <td>83</td>\n",
       "      <td>79</td>\n",
       "      <td>0.05</td>\n",
       "      <td>21020</td>\n",
       "      <td>-10239</td>\n",
       "      <td>adult</td>\n",
       "      <td>63</td>\n",
       "      <td>63.1</td>\n",
       "      <td>cross</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013_03_01</td>\n",
       "      <td>I</td>\n",
       "      <td>2</td>\n",
       "      <td>1556</td>\n",
       "      <td>41</td>\n",
       "      <td>2.51</td>\n",
       "      <td>-455</td>\n",
       "      <td>1025</td>\n",
       "      <td>0.74</td>\n",
       "      <td>-0.170</td>\n",
       "      <td>330</td>\n",
       "      <td>158</td>\n",
       "      <td>0.52</td>\n",
       "      <td>4718</td>\n",
       "      <td>-1381</td>\n",
       "      <td>adult</td>\n",
       "      <td>63</td>\n",
       "      <td>63.1</td>\n",
       "      <td>cross</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013_03_01</td>\n",
       "      <td>I</td>\n",
       "      <td>3</td>\n",
       "      <td>493</td>\n",
       "      <td>36</td>\n",
       "      <td>0.80</td>\n",
       "      <td>-974</td>\n",
       "      <td>499</td>\n",
       "      <td>1.57</td>\n",
       "      <td>-0.423</td>\n",
       "      <td>245</td>\n",
       "      <td>216</td>\n",
       "      <td>0.12</td>\n",
       "      <td>2012</td>\n",
       "      <td>-3975</td>\n",
       "      <td>adult</td>\n",
       "      <td>63</td>\n",
       "      <td>63.1</td>\n",
       "      <td>cross</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date ID  trial number  impact force (mN)  impact time (ms)  \\\n",
       "0  2013_02_26  I             3               1205                46   \n",
       "1  2013_02_26  I             4               2527                44   \n",
       "2  2013_03_01  I             1               1745                34   \n",
       "3  2013_03_01  I             2               1556                41   \n",
       "4  2013_03_01  I             3                493                36   \n",
       "\n",
       "   impact force / body weight  adhesive force (mN)  \\\n",
       "0                        1.95                 -785   \n",
       "1                        4.08                 -983   \n",
       "2                        2.82                 -850   \n",
       "3                        2.51                 -455   \n",
       "4                        0.80                 -974   \n",
       "\n",
       "   time frog pulls on target (ms)  adhesive force / body weight  \\\n",
       "0                             884                          1.27   \n",
       "1                             248                          1.59   \n",
       "2                             211                          1.37   \n",
       "3                            1025                          0.74   \n",
       "4                             499                          1.57   \n",
       "\n",
       "   adhesive impulse (N-s)  total contact area (mm2)  \\\n",
       "0                  -0.290                       387   \n",
       "1                  -0.181                       101   \n",
       "2                  -0.157                        83   \n",
       "3                  -0.170                       330   \n",
       "4                  -0.423                       245   \n",
       "\n",
       "   contact area without mucus (mm2)  \\\n",
       "0                                70   \n",
       "1                                94   \n",
       "2                                79   \n",
       "3                               158   \n",
       "4                               216   \n",
       "\n",
       "   contact area with mucus / contact area without mucus  \\\n",
       "0                                               0.82      \n",
       "1                                               0.07      \n",
       "2                                               0.05      \n",
       "3                                               0.52      \n",
       "4                                               0.12      \n",
       "\n",
       "   contact pressure (Pa)  adhesive strength (Pa)    age  SVL (mm)  weight (g)  \\\n",
       "0                   3117                   -2030  adult        63        63.1   \n",
       "1                  24923                   -9695  adult        63        63.1   \n",
       "2                  21020                  -10239  adult        63        63.1   \n",
       "3                   4718                   -1381  adult        63        63.1   \n",
       "4                   2012                   -3975  adult        63        63.1   \n",
       "\n",
       "  species  \n",
       "0   cross  \n",
       "1   cross  \n",
       "2   cross  \n",
       "3   cross  \n",
       "4   cross  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Computing environment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPython 3.7.7\n",
      "IPython 7.16.1\n",
      "\n",
      "pandas 0.24.2\n",
      "jupyterlab 2.1.5\n"
     ]
    }
   ],
   "source": [
    "%load_ext watermark\n",
    "%watermark -v -p pandas,jupyterlab"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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
}