{
"metadata": {
"name": "",
"signature": "sha256:d0efb3c41fba53401eaf91c234a4b9ba9837b813cd6b66769a532e8c319c9013"
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Selecting Pandas DataFrame Rows Based On Conditions\n",
"\n",
"- **Author:** [Chris Albon](http://www.chrisalbon.com/), [@ChrisAlbon](https://twitter.com/chrisalbon)\n",
"- **Date:** -\n",
"- **Repo:** [Python 3 code snippets for data science](https://github.com/chrisalbon/code_py)\n",
"- **Note:**"
]
},
{
"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": [
"
\n",
"
\n",
" \n",
" \n",
" | \n",
" name | \n",
" year | \n",
" battle_number | \n",
" attacker_king | \n",
" defender_king | \n",
" attacker_1 | \n",
" attacker_2 | \n",
" attacker_3 | \n",
" attacker_4 | \n",
" defender_1 | \n",
" defender_2 | \n",
" defender_3 | \n",
" defender_4 | \n",
" attacker_outcome | \n",
" battle_type | \n",
" major_death | \n",
" major_capture | \n",
" attacker_size | \n",
" defender_size | \n",
" attacker_commander | \n",
" defender_commander | \n",
" summer | \n",
" location | \n",
" region | \n",
" note | \n",
"
\n",
" \n",
" \n",
" \n",
" 4 | \n",
" Battle of the Whispering Wood | \n",
" 298 | \n",
" 5 | \n",
" Robb Stark | \n",
" Joffrey/Tommen Baratheon | \n",
" Stark | \n",
" Tully | \n",
" NaN | \n",
" NaN | \n",
" Lannister | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" win | \n",
" ambush | \n",
" 1 | \n",
" 1 | \n",
" 1875 | \n",
" 6000 | \n",
" Robb Stark, Brynden Tully | \n",
" Jaime Lannister | \n",
" 1 | \n",
" Whispering Wood | \n",
" The Riverlands | \n",
" NaN | \n",
"
\n",
" \n",
" 5 | \n",
" Battle of the Camps | \n",
" 298 | \n",
" 6 | \n",
" Robb Stark | \n",
" Joffrey/Tommen Baratheon | \n",
" Stark | \n",
" Tully | \n",
" NaN | \n",
" NaN | \n",
" Lannister | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" win | \n",
" ambush | \n",
" 0 | \n",
" 0 | \n",
" 6000 | \n",
" 12625 | \n",
" Robb Stark, Tytos Blackwood, Brynden Tully | \n",
" Lord Andros Brax, Forley Prester | \n",
" 1 | \n",
" Riverrun | \n",
" The Riverlands | \n",
" NaN | \n",
"
\n",
" \n",
" 10 | \n",
" Battle of Torrhen's Square | \n",
" 299 | \n",
" 11 | \n",
" Robb Stark | \n",
" Balon/Euron Greyjoy | \n",
" Stark | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" Greyjoy | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" win | \n",
" pitched battle | \n",
" 0 | \n",
" 0 | \n",
" 244 | \n",
" 900 | \n",
" Rodrik Cassel, Cley Cerwyn | \n",
" Dagmer Cleftjaw | \n",
" 1 | \n",
" Torrhen's Square | \n",
" The North | \n",
" Greyjoy's troop number comes from the 264 esti... | \n",
"
\n",
" \n",
" 14 | \n",
" Battle of Oxcross | \n",
" 299 | \n",
" 15 | \n",
" Robb Stark | \n",
" Joffrey/Tommen Baratheon | \n",
" Stark | \n",
" Tully | \n",
" NaN | \n",
" NaN | \n",
" Lannister | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" win | \n",
" ambush | \n",
" 1 | \n",
" 1 | \n",
" 6000 | \n",
" 10000 | \n",
" Robb Stark, Brynden Tully | \n",
" Stafford Lannister, Roland Crakehall, Antario ... | \n",
" 1 | \n",
" Oxcross | \n",
" The Westerlands | \n",
" NaN | \n",
"
\n",
" \n",
" 17 | \n",
" Sack of Harrenhal | \n",
" 299 | \n",
" 18 | \n",
" Robb Stark | \n",
" Joffrey/Tommen Baratheon | \n",
" Stark | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" Lannister | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" win | \n",
" ambush | \n",
" 1 | \n",
" 0 | \n",
" 100 | \n",
" 100 | \n",
" Roose Bolton, Vargo Hoat, Robett Glover | \n",
" Amory Lorch | \n",
" 1 | \n",
" Harrenhal | \n",
" The Riverlands | \n",
" NaN | \n",
"
\n",
" \n",
" 18 | \n",
" Battle of the Crag | \n",
" 299 | \n",
" 19 | \n",
" Robb Stark | \n",
" Joffrey/Tommen Baratheon | \n",
" Stark | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" Lannister | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" win | \n",
" ambush | \n",
" 0 | \n",
" 0 | \n",
" 6000 | \n",
" NaN | \n",
" Robb Stark, Smalljon Umber, Black Walder Frey | \n",
" Rolph Spicer | \n",
" 1 | \n",
" Crag | \n",
" The Westerlands | \n",
" NaN | \n",
"
\n",
" \n",
" 20 | \n",
" Siege of Darry | \n",
" 299 | \n",
" 21 | \n",
" Robb Stark | \n",
" Joffrey/Tommen Baratheon | \n",
" Darry | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" Lannister | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" win | \n",
" siege | \n",
" 0 | \n",
" 0 | \n",
" NaN | \n",
" NaN | \n",
" Helman Tallhart | \n",
" NaN | \n",
" 1 | \n",
" Darry | \n",
" The Riverlands | \n",
" NaN | \n",
"
\n",
" \n",
" 26 | \n",
" Siege of Seagard | \n",
" 299 | \n",
" 27 | \n",
" Robb Stark | \n",
" Joffrey/Tommen Baratheon | \n",
" Frey | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" Mallister | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" win | \n",
" siege | \n",
" 0 | \n",
" 1 | \n",
" NaN | \n",
" NaN | \n",
" Walder Frey | \n",
" Jason Mallister | \n",
" 1 | \n",
" Seagard | \n",
" The Riverlands | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"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": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" name | \n",
" year | \n",
" battle_number | \n",
" attacker_king | \n",
" defender_king | \n",
" attacker_1 | \n",
" attacker_2 | \n",
" attacker_3 | \n",
" attacker_4 | \n",
" defender_1 | \n",
" defender_2 | \n",
" defender_3 | \n",
" defender_4 | \n",
" attacker_outcome | \n",
" battle_type | \n",
" major_death | \n",
" major_capture | \n",
" attacker_size | \n",
" defender_size | \n",
" attacker_commander | \n",
" defender_commander | \n",
" summer | \n",
" location | \n",
" region | \n",
" note | \n",
"
\n",
" \n",
" \n",
" \n",
" 27 | \n",
" Battle of Castle Black | \n",
" 300 | \n",
" 28 | \n",
" Stannis Baratheon | \n",
" Mance Rayder | \n",
" Free folk | \n",
" Thenns | \n",
" Giants | \n",
" NaN | \n",
" Night's Watch | \n",
" Baratheon | \n",
" NaN | \n",
" NaN | \n",
" loss | \n",
" siege | \n",
" 1 | \n",
" 1 | \n",
" 100000 | \n",
" 1240 | \n",
" Stannis Baratheon, Jon Snow, Donal Noye, Cotte... | \n",
" Mance Rayder, Tormund Giantsbane, Harma Dogshe... | \n",
" 0 | \n",
" Castle Black | \n",
" Beyond the Wall | \n",
" NaN | \n",
"
\n",
" \n",
" 37 | \n",
" Siege of Winterfell | \n",
" 300 | \n",
" 38 | \n",
" Stannis Baratheon | \n",
" Joffrey/Tommen Baratheon | \n",
" Baratheon | \n",
" Karstark | \n",
" Mormont | \n",
" Glover | \n",
" Bolton | \n",
" Frey | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 5000 | \n",
" 8000 | \n",
" Stannis Baratheon | \n",
" Roose Bolton | \n",
" 0 | \n",
" Winterfell | \n",
" The North | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"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": {}
}
]
}