Max-Min-Range-Queries/max_min_range_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This workbook was the result of some data analysis during a programming and pizza workshop.  We were working on getting the row where a max and min value occurs when there are multiple rows with a measurement.\n",
    "\n",
    "The ecology data carpentry data set was a good way to illustrate (https://figshare.com/articles/Portal_Project_Teaching_Database/1314459) get the surveys.csv file if you want to follow along.  \n",
    "\n",
    "We were actually doing this in R with a different data set, but it's similar enough to python dataframes that it turned out to be transferable.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from pandasql import sqldf\n",
    "pysqldf = lambda q: sqldf(q, globals())\n",
    "\n",
    "surveys = pd.read_csv(\"data/surveys.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>max(hindfoot_length)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   max(hindfoot_length)\n",
       "0                  70.0"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pysqldf(\"SELECT max(hindfoot_length) from surveys WHERE species_id = 'NL'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>record_id</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>year</th>\n",
       "      <th>plot_id</th>\n",
       "      <th>species_id</th>\n",
       "      <th>sex</th>\n",
       "      <th>max_hindfoot_length</th>\n",
       "      <th>min_weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35549</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2002</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33713</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>2002</td>\n",
       "      <td>13</td>\n",
       "      <td>AB</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35546</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2002</td>\n",
       "      <td>15</td>\n",
       "      <td>AH</td>\n",
       "      <td>None</td>\n",
       "      <td>35.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20588</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1993</td>\n",
       "      <td>12</td>\n",
       "      <td>AS</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16599</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>1989</td>\n",
       "      <td>21</td>\n",
       "      <td>BA</td>\n",
       "      <td>M</td>\n",
       "      <td>16.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>33109</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>2001</td>\n",
       "      <td>23</td>\n",
       "      <td>CB</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>13337</td>\n",
       "      <td>8</td>\n",
       "      <td>26</td>\n",
       "      <td>1987</td>\n",
       "      <td>15</td>\n",
       "      <td>CM</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9364</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>1984</td>\n",
       "      <td>3</td>\n",
       "      <td>CQ</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>15878</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1989</td>\n",
       "      <td>11</td>\n",
       "      <td>CS</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>28988</td>\n",
       "      <td>12</td>\n",
       "      <td>23</td>\n",
       "      <td>1998</td>\n",
       "      <td>6</td>\n",
       "      <td>CT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>21209</td>\n",
       "      <td>11</td>\n",
       "      <td>13</td>\n",
       "      <td>1993</td>\n",
       "      <td>7</td>\n",
       "      <td>CU</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>13313</td>\n",
       "      <td>8</td>\n",
       "      <td>26</td>\n",
       "      <td>1987</td>\n",
       "      <td>15</td>\n",
       "      <td>CV</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>22178</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1995</td>\n",
       "      <td>17</td>\n",
       "      <td>DM</td>\n",
       "      <td>F</td>\n",
       "      <td>50.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10094</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>1985</td>\n",
       "      <td>2</td>\n",
       "      <td>DO</td>\n",
       "      <td>F</td>\n",
       "      <td>64.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>11282</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1986</td>\n",
       "      <td>22</td>\n",
       "      <td>DS</td>\n",
       "      <td>M</td>\n",
       "      <td>58.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>35511</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2002</td>\n",
       "      <td>11</td>\n",
       "      <td>DX</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>7949</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>1983</td>\n",
       "      <td>20</td>\n",
       "      <td>NL</td>\n",
       "      <td>M</td>\n",
       "      <td>70.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17435</td>\n",
       "      <td>4</td>\n",
       "      <td>26</td>\n",
       "      <td>1990</td>\n",
       "      <td>6</td>\n",
       "      <td>OL</td>\n",
       "      <td>F</td>\n",
       "      <td>39.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>13225</td>\n",
       "      <td>8</td>\n",
       "      <td>25</td>\n",
       "      <td>1987</td>\n",
       "      <td>22</td>\n",
       "      <td>OT</td>\n",
       "      <td>F</td>\n",
       "      <td>50.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>9209</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>1984</td>\n",
       "      <td>14</td>\n",
       "      <td>OX</td>\n",
       "      <td>M</td>\n",
       "      <td>21.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>34131</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "      <td>2002</td>\n",
       "      <td>8</td>\n",
       "      <td>PB</td>\n",
       "      <td>F</td>\n",
       "      <td>47.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>28728</td>\n",
       "      <td>10</td>\n",
       "      <td>24</td>\n",
       "      <td>1998</td>\n",
       "      <td>23</td>\n",
       "      <td>PC</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1921</td>\n",
       "      <td>7</td>\n",
       "      <td>25</td>\n",
       "      <td>1979</td>\n",
       "      <td>17</td>\n",
       "      <td>PE</td>\n",
       "      <td>M</td>\n",
       "      <td>30.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>218</td>\n",
       "      <td>9</td>\n",
       "      <td>13</td>\n",
       "      <td>1977</td>\n",
       "      <td>1</td>\n",
       "      <td>PF</td>\n",
       "      <td>M</td>\n",
       "      <td>38.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>30463</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2000</td>\n",
       "      <td>18</td>\n",
       "      <td>PG</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>17683</td>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>1990</td>\n",
       "      <td>22</td>\n",
       "      <td>PH</td>\n",
       "      <td>F</td>\n",
       "      <td>28.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>32708</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "      <td>2001</td>\n",
       "      <td>3</td>\n",
       "      <td>PI</td>\n",
       "      <td>M</td>\n",
       "      <td>23.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27792</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1998</td>\n",
       "      <td>7</td>\n",
       "      <td>PL</td>\n",
       "      <td>F</td>\n",
       "      <td>22.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>9812</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>1985</td>\n",
       "      <td>19</td>\n",
       "      <td>PM</td>\n",
       "      <td>M</td>\n",
       "      <td>36.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29906</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1999</td>\n",
       "      <td>4</td>\n",
       "      <td>PP</td>\n",
       "      <td>M</td>\n",
       "      <td>41.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>22075</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1995</td>\n",
       "      <td>23</td>\n",
       "      <td>PU</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>25137</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>1997</td>\n",
       "      <td>13</td>\n",
       "      <td>PX</td>\n",
       "      <td>M</td>\n",
       "      <td>20.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>16243</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>1989</td>\n",
       "      <td>6</td>\n",
       "      <td>RF</td>\n",
       "      <td>F</td>\n",
       "      <td>20.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>8736</td>\n",
       "      <td>12</td>\n",
       "      <td>8</td>\n",
       "      <td>1983</td>\n",
       "      <td>19</td>\n",
       "      <td>RM</td>\n",
       "      <td>M</td>\n",
       "      <td>22.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>33397</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>2002</td>\n",
       "      <td>1</td>\n",
       "      <td>RO</td>\n",
       "      <td>M</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>25122</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>1997</td>\n",
       "      <td>5</td>\n",
       "      <td>RX</td>\n",
       "      <td>M</td>\n",
       "      <td>20.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>27629</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1998</td>\n",
       "      <td>3</td>\n",
       "      <td>SA</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>16002</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>1989</td>\n",
       "      <td>19</td>\n",
       "      <td>SC</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>20902</td>\n",
       "      <td>6</td>\n",
       "      <td>18</td>\n",
       "      <td>1993</td>\n",
       "      <td>18</td>\n",
       "      <td>SF</td>\n",
       "      <td>M</td>\n",
       "      <td>38.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>16473</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>1989</td>\n",
       "      <td>3</td>\n",
       "      <td>SH</td>\n",
       "      <td>M</td>\n",
       "      <td>39.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>20267</td>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "      <td>1992</td>\n",
       "      <td>2</td>\n",
       "      <td>SO</td>\n",
       "      <td>F</td>\n",
       "      <td>36.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>4875</td>\n",
       "      <td>10</td>\n",
       "      <td>24</td>\n",
       "      <td>1981</td>\n",
       "      <td>20</td>\n",
       "      <td>SS</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>20723</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>1993</td>\n",
       "      <td>20</td>\n",
       "      <td>ST</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>22264</td>\n",
       "      <td>4</td>\n",
       "      <td>30</td>\n",
       "      <td>1995</td>\n",
       "      <td>11</td>\n",
       "      <td>SU</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>21601</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>1994</td>\n",
       "      <td>8</td>\n",
       "      <td>UL</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>17654</td>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>1990</td>\n",
       "      <td>1</td>\n",
       "      <td>UP</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>34575</td>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "      <td>2002</td>\n",
       "      <td>6</td>\n",
       "      <td>UR</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>35544</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2002</td>\n",
       "      <td>15</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>14351</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>1988</td>\n",
       "      <td>23</td>\n",
       "      <td>ZL</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    record_id  month  day  year  plot_id species_id   sex  \\\n",
       "0       35549     12   31  2002        5       None  None   \n",
       "1       33713      3   14  2002       13         AB  None   \n",
       "2       35546     12   31  2002       15         AH  None   \n",
       "3       20588      1   24  1993       12         AS  None   \n",
       "4       16599     11    4  1989       21         BA     M   \n",
       "5       33109     11   17  2001       23         CB  None   \n",
       "6       13337      8   26  1987       15         CM  None   \n",
       "7        9364      7    4  1984        3         CQ  None   \n",
       "8       15878      4    1  1989       11         CS  None   \n",
       "9       28988     12   23  1998        6         CT  None   \n",
       "10      21209     11   13  1993        7         CU  None   \n",
       "11      13313      8   26  1987       15         CV  None   \n",
       "12      22178      4    1  1995       17         DM     F   \n",
       "13      10094      3   17  1985        2         DO     F   \n",
       "14      11282      3    8  1986       22         DS     M   \n",
       "15      35511     12   31  2002       11         DX  None   \n",
       "16       7949      6   17  1983       20         NL     M   \n",
       "17      17435      4   26  1990        6         OL     F   \n",
       "18      13225      8   25  1987       22         OT     F   \n",
       "19       9209      5   27  1984       14         OX     M   \n",
       "20      34131      5   16  2002        8         PB     F   \n",
       "21      28728     10   24  1998       23         PC  None   \n",
       "22       1921      7   25  1979       17         PE     M   \n",
       "23        218      9   13  1977        1         PF     M   \n",
       "24      30463      3    4  2000       18         PG  None   \n",
       "25      17683      7   21  1990       22         PH     F   \n",
       "26      32708      9   23  2001        3         PI     M   \n",
       "27      27792      5    2  1998        7         PL     F   \n",
       "28       9812      1   19  1985       19         PM     M   \n",
       "29      29906     10   10  1999        4         PP     M   \n",
       "30      22075      2    4  1995       23         PU  None   \n",
       "31      25137      2    9  1997       13         PX     M   \n",
       "32      16243      7    3  1989        6         RF     F   \n",
       "33       8736     12    8  1983       19         RM     M   \n",
       "34      33397      2    9  2002        1         RO     M   \n",
       "35      25122      2    9  1997        5         RX     M   \n",
       "36      27629      3    2  1998        3         SA  None   \n",
       "37      16002      5    9  1989       19         SC  None   \n",
       "38      20902      6   18  1993       18         SF     M   \n",
       "39      16473      9    4  1989        3         SH     M   \n",
       "40      20267      8   29  1992        2         SO     F   \n",
       "41       4875     10   24  1981       20         SS     F   \n",
       "42      20723      3   17  1993       20         ST  None   \n",
       "43      22264      4   30  1995       11         SU  None   \n",
       "44      21601      5   19  1994        8         UL  None   \n",
       "45      17654      7   21  1990        1         UP  None   \n",
       "46      34575      7   14  2002        6         UR  None   \n",
       "47      35544     12   31  2002       15         US  None   \n",
       "48      14351      4   17  1988       23         ZL  None   \n",
       "\n",
       "    max_hindfoot_length  min_weight  \n",
       "0                   NaN         NaN  \n",
       "1                   NaN         NaN  \n",
       "2                  35.0         NaN  \n",
       "3                   NaN         NaN  \n",
       "4                  16.0         6.0  \n",
       "5                   NaN         NaN  \n",
       "6                   NaN         NaN  \n",
       "7                   NaN         NaN  \n",
       "8                   NaN         NaN  \n",
       "9                   NaN         NaN  \n",
       "10                  NaN         NaN  \n",
       "11                  NaN         NaN  \n",
       "12                 50.0        10.0  \n",
       "13                 64.0        12.0  \n",
       "14                 58.0        12.0  \n",
       "15                  NaN         NaN  \n",
       "16                 70.0        30.0  \n",
       "17                 39.0        10.0  \n",
       "18                 50.0         5.0  \n",
       "19                 21.0        18.0  \n",
       "20                 47.0        12.0  \n",
       "21                  NaN         NaN  \n",
       "22                 30.0         8.0  \n",
       "23                 38.0         4.0  \n",
       "24                  NaN         NaN  \n",
       "25                 28.0        18.0  \n",
       "26                 23.0        17.0  \n",
       "27                 22.0         8.0  \n",
       "28                 36.0         7.0  \n",
       "29                 41.0         4.0  \n",
       "30                  NaN         NaN  \n",
       "31                 20.0        18.0  \n",
       "32                 20.0         9.0  \n",
       "33                 22.0         4.0  \n",
       "34                 17.0         8.0  \n",
       "35                 20.0        11.0  \n",
       "36                  NaN         NaN  \n",
       "37                  NaN         NaN  \n",
       "38                 38.0        24.0  \n",
       "39                 39.0        16.0  \n",
       "40                 36.0        15.0  \n",
       "41                  NaN        57.0  \n",
       "42                  NaN         NaN  \n",
       "43                  NaN         NaN  \n",
       "44                  NaN         NaN  \n",
       "45                  NaN         NaN  \n",
       "46                  NaN         NaN  \n",
       "47                  NaN         NaN  \n",
       "48                  NaN         NaN  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pysqldf(\"SELECT record_id, month, day, year, plot_id, species_id, sex, MAX(hindfoot_length) AS max_hindfoot_length, \\\n",
    "        MIN(weight) AS min_weight FROM surveys GROUP BY(species_id)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MAX(hindfoot_length)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MAX(hindfoot_length)\n",
       "0                  16.0"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how to get just one without aggregation, we did this as a spot check\n",
    "pysqldf(\"select MAX(hindfoot_length) from surveys where species_id = 'BA'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next task, what if you'd like to set a range for each species, and get the results that show up within that range?\n",
    "\n",
    "We created a sample data set, start_end.csv, for a couple of species, with a start and end range."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "start_end = pd.read_csv(\"data/start_end.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>species_id</th>\n",
       "      <th>Start</th>\n",
       "      <th>End</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BA</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NL</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  species_id  Start  End\n",
       "0         BA      6   10\n",
       "1         NL      5   11"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>record_id</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>year</th>\n",
       "      <th>plot_id</th>\n",
       "      <th>species_id</th>\n",
       "      <th>sex</th>\n",
       "      <th>hindfoot_length</th>\n",
       "      <th>weight</th>\n",
       "      <th>species_id</th>\n",
       "      <th>Start</th>\n",
       "      <th>End</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19015</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1991</td>\n",
       "      <td>19</td>\n",
       "      <td>BA</td>\n",
       "      <td>F</td>\n",
       "      <td>7.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>BA</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19567</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>1992</td>\n",
       "      <td>19</td>\n",
       "      <td>BA</td>\n",
       "      <td>M</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>BA</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   record_id  month  day  year  plot_id species_id sex  hindfoot_length  \\\n",
       "0      19015      9    9  1991       19         BA   F              7.0   \n",
       "1      19567      1    8  1992       19         BA   M              6.0   \n",
       "\n",
       "   weight species_id  Start  End  \n",
       "0    11.0         BA      6   10  \n",
       "1     8.0         BA      6   10  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# now you can join with the start_end table and use the range\n",
    "# set for each species to filter\n",
    "\n",
    "pysqldf(\"SELECT * from surveys join start_end on \\\n",
    "surveys.species_id = start_end.species_id \\\n",
    "WHERE hindfoot_length >= start_end.Start \\\n",
    "AND hindfoot_length <= start_end.End \\\n",
    "ORDER BY surveys.species_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}