pandas_selecting_rows_on_conditions.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:7b1194e5cee86b4a52e08188d789ea87f179b8e77f96bddbe373aa2f0f436b57"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Selecting Pandas DataFrame Rows Based On Conditions\n",
      "\n",
      "This snippit was written by [Chris R. Albon](http://www.chrisralbon.com/) and is part of his collection of [well-documented Python snippits](https://github.com/chrisalbon/code_py). All code is written in Python 3 in iPython notebook and offered under the [Creative Commons Attribution-ShareAlike 4.0 International License](http://creativecommons.org/licenses/by-sa/4.0/)."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Preliminaries"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Import modules\n",
      "import pandas as pd\n",
      "\n",
      "# Set ipython's max row display\n",
      "pd.set_option('display.max_row', 1000)\n",
      "\n",
      "# Set iPython's max column width to 50\n",
      "pd.set_option('display.max_columns', 50)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Load example dataset\n",
      "df = pd.read_csv('https://www.dropbox.com/s/52cb7kcflr8qm2u/5kings_battles_v1.csv?dl=1')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 9
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Method 1: Using Boolean Variables"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Create variable with TRUE if stark is the attacker\n",
      "stark_attacker = df['attacker_king'] == \"Robb Stark\"\n",
      "\n",
      "# Create variable with TRUE if the attacker wins\n",
      "attacker_wins = df['attacker_outcome'] == \"win\"\n",
      "\n",
      "# Select all cases where stark is the attacker and the attacker wins\n",
      "df[stark_attacker & attacker_wins]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>name</th>\n",
        "      <th>year</th>\n",
        "      <th>battle_number</th>\n",
        "      <th>attacker_king</th>\n",
        "      <th>defender_king</th>\n",
        "      <th>attacker_1</th>\n",
        "      <th>attacker_2</th>\n",
        "      <th>attacker_3</th>\n",
        "      <th>attacker_4</th>\n",
        "      <th>defender_1</th>\n",
        "      <th>defender_2</th>\n",
        "      <th>defender_3</th>\n",
        "      <th>defender_4</th>\n",
        "      <th>attacker_outcome</th>\n",
        "      <th>battle_type</th>\n",
        "      <th>major_death</th>\n",
        "      <th>major_capture</th>\n",
        "      <th>attacker_size</th>\n",
        "      <th>defender_size</th>\n",
        "      <th>attacker_commander</th>\n",
        "      <th>defender_commander</th>\n",
        "      <th>summer</th>\n",
        "      <th>location</th>\n",
        "      <th>region</th>\n",
        "      <th>note</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>4 </th>\n",
        "      <td> Battle of the Whispering Wood</td>\n",
        "      <td> 298</td>\n",
        "      <td>  5</td>\n",
        "      <td> Robb Stark</td>\n",
        "      <td> Joffrey/Tommen Baratheon</td>\n",
        "      <td> Stark</td>\n",
        "      <td> Tully</td>\n",
        "      <td> NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> Lannister</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> win</td>\n",
        "      <td>         ambush</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1875</td>\n",
        "      <td>  6000</td>\n",
        "      <td>                     Robb Stark, Brynden Tully</td>\n",
        "      <td>                                   Jaime Lannister</td>\n",
        "      <td> 1</td>\n",
        "      <td>  Whispering Wood</td>\n",
        "      <td>  The Riverlands</td>\n",
        "      <td>                                               NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5 </th>\n",
        "      <td>           Battle of the Camps</td>\n",
        "      <td> 298</td>\n",
        "      <td>  6</td>\n",
        "      <td> Robb Stark</td>\n",
        "      <td> Joffrey/Tommen Baratheon</td>\n",
        "      <td> Stark</td>\n",
        "      <td> Tully</td>\n",
        "      <td> NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> Lannister</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> win</td>\n",
        "      <td>         ambush</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 6000</td>\n",
        "      <td> 12625</td>\n",
        "      <td>    Robb Stark, Tytos Blackwood, Brynden Tully</td>\n",
        "      <td>                  Lord Andros Brax, Forley Prester</td>\n",
        "      <td> 1</td>\n",
        "      <td>         Riverrun</td>\n",
        "      <td>  The Riverlands</td>\n",
        "      <td>                                               NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>10</th>\n",
        "      <td>    Battle of Torrhen's Square</td>\n",
        "      <td> 299</td>\n",
        "      <td> 11</td>\n",
        "      <td> Robb Stark</td>\n",
        "      <td>      Balon/Euron Greyjoy</td>\n",
        "      <td> Stark</td>\n",
        "      <td>   NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td>   Greyjoy</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> win</td>\n",
        "      <td> pitched battle</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td>  244</td>\n",
        "      <td>   900</td>\n",
        "      <td>                    Rodrik Cassel, Cley Cerwyn</td>\n",
        "      <td>                                   Dagmer Cleftjaw</td>\n",
        "      <td> 1</td>\n",
        "      <td> Torrhen's Square</td>\n",
        "      <td>       The North</td>\n",
        "      <td> Greyjoy's troop number comes from the 264 esti...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>14</th>\n",
        "      <td>             Battle of Oxcross</td>\n",
        "      <td> 299</td>\n",
        "      <td> 15</td>\n",
        "      <td> Robb Stark</td>\n",
        "      <td> Joffrey/Tommen Baratheon</td>\n",
        "      <td> Stark</td>\n",
        "      <td> Tully</td>\n",
        "      <td> NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> Lannister</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> win</td>\n",
        "      <td>         ambush</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> 6000</td>\n",
        "      <td> 10000</td>\n",
        "      <td>                     Robb Stark, Brynden Tully</td>\n",
        "      <td> Stafford Lannister, Roland Crakehall, Antario ...</td>\n",
        "      <td> 1</td>\n",
        "      <td>          Oxcross</td>\n",
        "      <td> The Westerlands</td>\n",
        "      <td>                                               NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>17</th>\n",
        "      <td>             Sack of Harrenhal</td>\n",
        "      <td> 299</td>\n",
        "      <td> 18</td>\n",
        "      <td> Robb Stark</td>\n",
        "      <td> Joffrey/Tommen Baratheon</td>\n",
        "      <td> Stark</td>\n",
        "      <td>   NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> Lannister</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> win</td>\n",
        "      <td>         ambush</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td>  100</td>\n",
        "      <td>   100</td>\n",
        "      <td>       Roose Bolton, Vargo Hoat, Robett Glover</td>\n",
        "      <td>                                       Amory Lorch</td>\n",
        "      <td> 1</td>\n",
        "      <td>        Harrenhal</td>\n",
        "      <td>  The Riverlands</td>\n",
        "      <td>                                               NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>18</th>\n",
        "      <td>            Battle of the Crag</td>\n",
        "      <td> 299</td>\n",
        "      <td> 19</td>\n",
        "      <td> Robb Stark</td>\n",
        "      <td> Joffrey/Tommen Baratheon</td>\n",
        "      <td> Stark</td>\n",
        "      <td>   NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> Lannister</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> win</td>\n",
        "      <td>         ambush</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 6000</td>\n",
        "      <td>   NaN</td>\n",
        "      <td> Robb Stark, Smalljon Umber, Black Walder Frey</td>\n",
        "      <td>                                      Rolph Spicer</td>\n",
        "      <td> 1</td>\n",
        "      <td>             Crag</td>\n",
        "      <td> The Westerlands</td>\n",
        "      <td>                                               NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>20</th>\n",
        "      <td>                Siege of Darry</td>\n",
        "      <td> 299</td>\n",
        "      <td> 21</td>\n",
        "      <td> Robb Stark</td>\n",
        "      <td> Joffrey/Tommen Baratheon</td>\n",
        "      <td> Darry</td>\n",
        "      <td>   NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> Lannister</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> win</td>\n",
        "      <td>          siege</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td>  NaN</td>\n",
        "      <td>   NaN</td>\n",
        "      <td>                               Helman Tallhart</td>\n",
        "      <td>                                               NaN</td>\n",
        "      <td> 1</td>\n",
        "      <td>            Darry</td>\n",
        "      <td>  The Riverlands</td>\n",
        "      <td>                                               NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>26</th>\n",
        "      <td>              Siege of Seagard</td>\n",
        "      <td> 299</td>\n",
        "      <td> 27</td>\n",
        "      <td> Robb Stark</td>\n",
        "      <td> Joffrey/Tommen Baratheon</td>\n",
        "      <td>  Frey</td>\n",
        "      <td>   NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td> Mallister</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> win</td>\n",
        "      <td>          siege</td>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "      <td>  NaN</td>\n",
        "      <td>   NaN</td>\n",
        "      <td>                                   Walder Frey</td>\n",
        "      <td>                                   Jason Mallister</td>\n",
        "      <td> 1</td>\n",
        "      <td>          Seagard</td>\n",
        "      <td>  The Riverlands</td>\n",
        "      <td>                                               NaN</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 6,
       "text": [
        "                             name  year  battle_number attacker_king  \\\n",
        "4   Battle of the Whispering Wood   298              5    Robb Stark   \n",
        "5             Battle of the Camps   298              6    Robb Stark   \n",
        "10     Battle of Torrhen's Square   299             11    Robb Stark   \n",
        "14              Battle of Oxcross   299             15    Robb Stark   \n",
        "17              Sack of Harrenhal   299             18    Robb Stark   \n",
        "18             Battle of the Crag   299             19    Robb Stark   \n",
        "20                 Siege of Darry   299             21    Robb Stark   \n",
        "26               Siege of Seagard   299             27    Robb Stark   \n",
        "\n",
        "               defender_king attacker_1 attacker_2 attacker_3 attacker_4  \\\n",
        "4   Joffrey/Tommen Baratheon      Stark      Tully        NaN        NaN   \n",
        "5   Joffrey/Tommen Baratheon      Stark      Tully        NaN        NaN   \n",
        "10       Balon/Euron Greyjoy      Stark        NaN        NaN        NaN   \n",
        "14  Joffrey/Tommen Baratheon      Stark      Tully        NaN        NaN   \n",
        "17  Joffrey/Tommen Baratheon      Stark        NaN        NaN        NaN   \n",
        "18  Joffrey/Tommen Baratheon      Stark        NaN        NaN        NaN   \n",
        "20  Joffrey/Tommen Baratheon      Darry        NaN        NaN        NaN   \n",
        "26  Joffrey/Tommen Baratheon       Frey        NaN        NaN        NaN   \n",
        "\n",
        "   defender_1 defender_2  defender_3  defender_4 attacker_outcome  \\\n",
        "4   Lannister        NaN         NaN         NaN              win   \n",
        "5   Lannister        NaN         NaN         NaN              win   \n",
        "10    Greyjoy        NaN         NaN         NaN              win   \n",
        "14  Lannister        NaN         NaN         NaN              win   \n",
        "17  Lannister        NaN         NaN         NaN              win   \n",
        "18  Lannister        NaN         NaN         NaN              win   \n",
        "20  Lannister        NaN         NaN         NaN              win   \n",
        "26  Mallister        NaN         NaN         NaN              win   \n",
        "\n",
        "       battle_type  major_death  major_capture  attacker_size  defender_size  \\\n",
        "4           ambush            1              1           1875           6000   \n",
        "5           ambush            0              0           6000          12625   \n",
        "10  pitched battle            0              0            244            900   \n",
        "14          ambush            1              1           6000          10000   \n",
        "17          ambush            1              0            100            100   \n",
        "18          ambush            0              0           6000            NaN   \n",
        "20           siege            0              0            NaN            NaN   \n",
        "26           siege            0              1            NaN            NaN   \n",
        "\n",
        "                               attacker_commander  \\\n",
        "4                       Robb Stark, Brynden Tully   \n",
        "5      Robb Stark, Tytos Blackwood, Brynden Tully   \n",
        "10                     Rodrik Cassel, Cley Cerwyn   \n",
        "14                      Robb Stark, Brynden Tully   \n",
        "17        Roose Bolton, Vargo Hoat, Robett Glover   \n",
        "18  Robb Stark, Smalljon Umber, Black Walder Frey   \n",
        "20                                Helman Tallhart   \n",
        "26                                    Walder Frey   \n",
        "\n",
        "                                   defender_commander  summer  \\\n",
        "4                                     Jaime Lannister       1   \n",
        "5                    Lord Andros Brax, Forley Prester       1   \n",
        "10                                    Dagmer Cleftjaw       1   \n",
        "14  Stafford Lannister, Roland Crakehall, Antario ...       1   \n",
        "17                                        Amory Lorch       1   \n",
        "18                                       Rolph Spicer       1   \n",
        "20                                                NaN       1   \n",
        "26                                    Jason Mallister       1   \n",
        "\n",
        "            location           region  \\\n",
        "4    Whispering Wood   The Riverlands   \n",
        "5           Riverrun   The Riverlands   \n",
        "10  Torrhen's Square        The North   \n",
        "14           Oxcross  The Westerlands   \n",
        "17         Harrenhal   The Riverlands   \n",
        "18              Crag  The Westerlands   \n",
        "20             Darry   The Riverlands   \n",
        "26           Seagard   The Riverlands   \n",
        "\n",
        "                                                 note  \n",
        "4                                                 NaN  \n",
        "5                                                 NaN  \n",
        "10  Greyjoy's troop number comes from the 264 esti...  \n",
        "14                                                NaN  \n",
        "17                                                NaN  \n",
        "18                                                NaN  \n",
        "20                                                NaN  \n",
        "26                                                NaN  "
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Method 2: Using variable attributes "
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Select the rows where attacker_2 is not missing, and defender_2 is not missing\n",
      "df[df['attacker_2'].notnull() & df['defender_2'].notnull()]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>name</th>\n",
        "      <th>year</th>\n",
        "      <th>battle_number</th>\n",
        "      <th>attacker_king</th>\n",
        "      <th>defender_king</th>\n",
        "      <th>attacker_1</th>\n",
        "      <th>attacker_2</th>\n",
        "      <th>attacker_3</th>\n",
        "      <th>attacker_4</th>\n",
        "      <th>defender_1</th>\n",
        "      <th>defender_2</th>\n",
        "      <th>defender_3</th>\n",
        "      <th>defender_4</th>\n",
        "      <th>attacker_outcome</th>\n",
        "      <th>battle_type</th>\n",
        "      <th>major_death</th>\n",
        "      <th>major_capture</th>\n",
        "      <th>attacker_size</th>\n",
        "      <th>defender_size</th>\n",
        "      <th>attacker_commander</th>\n",
        "      <th>defender_commander</th>\n",
        "      <th>summer</th>\n",
        "      <th>location</th>\n",
        "      <th>region</th>\n",
        "      <th>note</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>27</th>\n",
        "      <td> Battle of Castle Black</td>\n",
        "      <td> 300</td>\n",
        "      <td> 28</td>\n",
        "      <td> Stannis Baratheon</td>\n",
        "      <td>             Mance Rayder</td>\n",
        "      <td> Free folk</td>\n",
        "      <td>   Thenns</td>\n",
        "      <td>  Giants</td>\n",
        "      <td>    NaN</td>\n",
        "      <td> Night's Watch</td>\n",
        "      <td> Baratheon</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> loss</td>\n",
        "      <td> siege</td>\n",
        "      <td>  1</td>\n",
        "      <td>  1</td>\n",
        "      <td> 100000</td>\n",
        "      <td> 1240</td>\n",
        "      <td> Stannis Baratheon, Jon Snow, Donal Noye, Cotte...</td>\n",
        "      <td> Mance Rayder, Tormund Giantsbane, Harma Dogshe...</td>\n",
        "      <td> 0</td>\n",
        "      <td> Castle Black</td>\n",
        "      <td> Beyond the Wall</td>\n",
        "      <td> NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>37</th>\n",
        "      <td>    Siege of Winterfell</td>\n",
        "      <td> 300</td>\n",
        "      <td> 38</td>\n",
        "      <td> Stannis Baratheon</td>\n",
        "      <td> Joffrey/Tommen Baratheon</td>\n",
        "      <td> Baratheon</td>\n",
        "      <td> Karstark</td>\n",
        "      <td> Mormont</td>\n",
        "      <td> Glover</td>\n",
        "      <td>        Bolton</td>\n",
        "      <td>      Frey</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>  NaN</td>\n",
        "      <td>   NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>   5000</td>\n",
        "      <td> 8000</td>\n",
        "      <td>                                 Stannis Baratheon</td>\n",
        "      <td>                                      Roose Bolton</td>\n",
        "      <td> 0</td>\n",
        "      <td>   Winterfell</td>\n",
        "      <td>       The North</td>\n",
        "      <td> NaN</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 8,
       "text": [
        "                      name  year  battle_number      attacker_king  \\\n",
        "27  Battle of Castle Black   300             28  Stannis Baratheon   \n",
        "37     Siege of Winterfell   300             38  Stannis Baratheon   \n",
        "\n",
        "               defender_king attacker_1 attacker_2 attacker_3 attacker_4  \\\n",
        "27              Mance Rayder  Free folk     Thenns     Giants        NaN   \n",
        "37  Joffrey/Tommen Baratheon  Baratheon   Karstark    Mormont     Glover   \n",
        "\n",
        "       defender_1 defender_2  defender_3  defender_4 attacker_outcome  \\\n",
        "27  Night's Watch  Baratheon         NaN         NaN             loss   \n",
        "37         Bolton       Frey         NaN         NaN              NaN   \n",
        "\n",
        "   battle_type  major_death  major_capture  attacker_size  defender_size  \\\n",
        "27       siege            1              1         100000           1240   \n",
        "37         NaN          NaN            NaN           5000           8000   \n",
        "\n",
        "                                   attacker_commander  \\\n",
        "27  Stannis Baratheon, Jon Snow, Donal Noye, Cotte...   \n",
        "37                                  Stannis Baratheon   \n",
        "\n",
        "                                   defender_commander  summer      location  \\\n",
        "27  Mance Rayder, Tormund Giantsbane, Harma Dogshe...       0  Castle Black   \n",
        "37                                       Roose Bolton       0    Winterfell   \n",
        "\n",
        "             region note  \n",
        "27  Beyond the Wall  NaN  \n",
        "37        The North  NaN  "
       ]
      }
     ],
     "prompt_number": 8
    }
   ],
   "metadata": {}
  }
 ]
}