Skip to content

Instantly share code, notes, and snippets.

@fonnesbeck

fonnesbeck/1. Introduction to Pandas.ipynb

Created June 24, 2013 14:17
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save fonnesbeck/5850375 to your computer and use it in GitHub Desktop.
Save fonnesbeck/5850375 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
1. Introduction to Pandas.ipynb
{
"metadata": {
"name": "1. Introduction to Pandas"
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Introduction to Pandas\n",
"\n",
"**pandas** is a Python package providing fast, flexible, and expressive data structures designed to work with *relational* or *labeled* data both. It is a fundamental high-level building block for doing practical, real world data analysis in Python. \n",
"\n",
"pandas is well suited for:\n",
"\n",
"- Tabular data with heterogeneously-typed columns, as in an SQL table or Excel spreadsheet\n",
"- Ordered and unordered (not necessarily fixed-frequency) time series data.\n",
"- Arbitrary matrix data (homogeneously typed or heterogeneous) with row and column labels\n",
"- Any other form of observational / statistical data sets. The data actually need not be labeled at all to be placed into a pandas data structure\n",
"\n",
"\n",
"Key features:\n",
" \n",
"- Easy handling of **missing data**\n",
"- **Size mutability**: columns can be inserted and deleted from DataFrame and higher dimensional objects\n",
"- Automatic and explicit **data alignment**: objects can be explicitly aligned to a set of labels, or the data can be aligned automatically\n",
"- Powerful, flexible **group by functionality** to perform split-apply-combine operations on data sets\n",
"- Intelligent label-based **slicing, fancy indexing, and subsetting** of large data sets\n",
"- Intuitive **merging and joining** data sets\n",
"- Flexible **reshaping and pivoting** of data sets\n",
"- **Hierarchical labeling** of axes\n",
"- Robust **IO tools** for loading data from flat files, Excel files, databases, and HDF5\n",
"- **Time series functionality**: date range generation and frequency conversion, moving window statistics, moving window linear regressions, date shifting and lagging, etc."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"from IPython.core.display import HTML\n",
"HTML(\"<iframe src=http://pandas.pydata.org width=800 height=350></iframe>\")"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<iframe src=http://pandas.pydata.org width=800 height=350></iframe>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 1,
"text": [
"<IPython.core.display.HTML at 0x10530f950>"
]
}
],
"prompt_number": 1
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import pandas as pd\n",
"\n",
"# Set some Pandas options\n",
"pd.set_option('html', False)\n",
"pd.set_option('max_columns', 30)\n",
"pd.set_option('max_rows', 20)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 2
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Pandas Data Structures"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Series\n",
"\n",
"A **Series** is a single vector of data (like a NumPy array) with an *index* that labels each element in the vector."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"counts = pd.Series([632, 1638, 569, 115])\n",
"counts"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 3,
"text": [
"0 632\n",
"1 1638\n",
"2 569\n",
"3 115\n",
"dtype: int64"
]
}
],
"prompt_number": 3
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If an index is not specified, a default sequence of integers is assigned as the index. A NumPy array comprises the values of the `Series`, while the index is a pandas `Index` object."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"counts.values"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 4,
"text": [
"array([ 632, 1638, 569, 115])"
]
}
],
"prompt_number": 4
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"counts.index"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 5,
"text": [
"Int64Index([0, 1, 2, 3], dtype=int64)"
]
}
],
"prompt_number": 5
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can assign meaningful labels to the index, if they are available:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria = pd.Series([632, 1638, 569, 115], \n",
" index=['Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes'])\n",
"\n",
"bacteria"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 6,
"text": [
"Firmicutes 632\n",
"Proteobacteria 1638\n",
"Actinobacteria 569\n",
"Bacteroidetes 115\n",
"dtype: int64"
]
}
],
"prompt_number": 6
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"These labels can be used to refer to the values in the `Series`."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria['Actinobacteria']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 8,
"text": [
"569"
]
}
],
"prompt_number": 8
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria[[name.endswith('bacteria') for name in bacteria.index]]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 9,
"text": [
"Proteobacteria 1638\n",
"Actinobacteria 569\n",
"dtype: int64"
]
}
],
"prompt_number": 9
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"[name.endswith('bacteria') for name in bacteria.index]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 10,
"text": [
"[False, True, True, False]"
]
}
],
"prompt_number": 10
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice that the indexing operation preserved the association between the values and the corresponding indices.\n",
"\n",
"We can still use positional indexing if we wish."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria[0]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 11,
"text": [
"632"
]
}
],
"prompt_number": 11
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can give both the array of values and the index meaningful labels themselves:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria.name = 'counts'\n",
"bacteria.index.name = 'phylum'\n",
"bacteria"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 12,
"text": [
"phylum\n",
"Firmicutes 632\n",
"Proteobacteria 1638\n",
"Actinobacteria 569\n",
"Bacteroidetes 115\n",
"Name: counts, dtype: int64"
]
}
],
"prompt_number": 12
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"NumPy's math functions and other operations can be applied to Series without losing the data structure."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"np.log(bacteria)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 13,
"text": [
"phylum\n",
"Firmicutes 6.448889\n",
"Proteobacteria 7.401231\n",
"Actinobacteria 6.343880\n",
"Bacteroidetes 4.744932\n",
"Name: counts, dtype: float64"
]
}
],
"prompt_number": 13
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can also filter according to the values in the `Series`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria[bacteria>1000]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 14,
"text": [
"phylum\n",
"Proteobacteria 1638\n",
"Name: counts, dtype: int64"
]
}
],
"prompt_number": 14
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A `Series` can be thought of as an ordered key-value store. In fact, we can create one from a `dict`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria_dict = {'Firmicutes': 632, 'Proteobacteria': 1638, 'Actinobacteria': 569, 'Bacteroidetes': 115}\n",
"pd.Series(bacteria_dict)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 15,
"text": [
"Actinobacteria 569\n",
"Bacteroidetes 115\n",
"Firmicutes 632\n",
"Proteobacteria 1638\n",
"dtype: int64"
]
}
],
"prompt_number": 15
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice that the `Series` is created in key-sorted order.\n",
"\n",
"If we pass a custom index to `Series`, it will select the corresponding values from the dict, and treat indices without corrsponding values as missing. Pandas uses the `NaN` (not a number) type for missing values."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2 = pd.Series(bacteria_dict, index=['Cyanobacteria','Firmicutes','Proteobacteria','Actinobacteria'])\n",
"bacteria2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 16,
"text": [
"Cyanobacteria NaN\n",
"Firmicutes 632\n",
"Proteobacteria 1638\n",
"Actinobacteria 569\n",
"dtype: float64"
]
}
],
"prompt_number": 16
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2.isnull()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 17,
"text": [
"Cyanobacteria True\n",
"Firmicutes False\n",
"Proteobacteria False\n",
"Actinobacteria False\n",
"dtype: bool"
]
}
],
"prompt_number": 17
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Critically, the labels are used to **align data** when used in operations with other Series objects:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria + bacteria2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 18,
"text": [
"Actinobacteria 1138\n",
"Bacteroidetes NaN\n",
"Cyanobacteria NaN\n",
"Firmicutes 1264\n",
"Proteobacteria 3276\n",
"dtype: float64"
]
}
],
"prompt_number": 18
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Contrast this with NumPy arrays, where arrays of the same length will combine values element-wise; adding Series combined values with the same label in the resulting series. Notice also that the missing values were propogated by addition."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### DataFrame\n",
"\n",
"Inevitably, we want to be able to store, view and manipulate data that is *multivariate*, where for every index there are multiple fields or columns of data, often of varying data type.\n",
"\n",
"A `DataFrame` is a tabular data structure, encapsulating multiple series like columns in a spreadsheet. Data are stored internally as a 2-dimensional object, but the `DataFrame` allows us to represent and manipulate higher-dimensional data."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data = pd.DataFrame({'value':[632, 1638, 569, 115, 433, 1130, 754, 555],\n",
" 'patient':[1, 1, 1, 1, 2, 2, 2, 2],\n",
" 'phylum':['Firmicutes', 'Proteobacteria', 'Actinobacteria', \n",
" 'Bacteroidetes', 'Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes']})\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 19,
"text": [
" patient phylum value\n",
"0 1 Firmicutes 632\n",
"1 1 Proteobacteria 1638\n",
"2 1 Actinobacteria 569\n",
"3 1 Bacteroidetes 115\n",
"4 2 Firmicutes 433\n",
"5 2 Proteobacteria 1130\n",
"6 2 Actinobacteria 754\n",
"7 2 Bacteroidetes 555"
]
}
],
"prompt_number": 19
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice the `DataFrame` is sorted by column name. We can change the order by indexing them in the order we desire:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data[['phylum','value','patient']]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 21,
"text": [
" phylum value patient\n",
"0 Firmicutes 632 1\n",
"1 Proteobacteria 1638 1\n",
"2 Actinobacteria 569 1\n",
"3 Bacteroidetes 115 1\n",
"4 Firmicutes 433 2\n",
"5 Proteobacteria 1130 2\n",
"6 Actinobacteria 754 2\n",
"7 Bacteroidetes 555 2"
]
}
],
"prompt_number": 21
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A `DataFrame` has a second index, representing the columns:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.columns"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 22,
"text": [
"Index([u'patient', u'phylum', u'value'], dtype=object)"
]
}
],
"prompt_number": 22
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If we wish to access columns, we can do so either by dict-like indexing or by attribute:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data['value']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 23,
"text": [
"0 632\n",
"1 1638\n",
"2 569\n",
"3 115\n",
"4 433\n",
"5 1130\n",
"6 754\n",
"7 555\n",
"Name: value, dtype: int64"
]
}
],
"prompt_number": 23
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.value"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 24,
"text": [
"0 632\n",
"1 1638\n",
"2 569\n",
"3 115\n",
"4 433\n",
"5 1130\n",
"6 754\n",
"7 555\n",
"Name: value, dtype: int64"
]
}
],
"prompt_number": 24
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"type(data.value)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 25,
"text": [
"pandas.core.series.Series"
]
}
],
"prompt_number": 25
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"type(data[['value']])"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 27,
"text": [
"pandas.core.frame.DataFrame"
]
}
],
"prompt_number": 27
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice this is different than with `Series`, where dict-like indexing retrieved a particular element (row). If we want access to a row in a `DataFrame`, we index its `ix` attribute.\n"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.ix[3]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 28,
"text": [
"patient 1\n",
"phylum Bacteroidetes\n",
"value 115\n",
"Name: 3, dtype: object"
]
}
],
"prompt_number": 28
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Alternatively, we can create a `DataFrame` with a dict of dicts:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data = pd.DataFrame({0: {'patient': 1, 'phylum': 'Firmicutes', 'value': 632},\n",
" 1: {'patient': 1, 'phylum': 'Proteobacteria', 'value': 1638},\n",
" 2: {'patient': 1, 'phylum': 'Actinobacteria', 'value': 569},\n",
" 3: {'patient': 1, 'phylum': 'Bacteroidetes', 'value': 115},\n",
" 4: {'patient': 2, 'phylum': 'Firmicutes', 'value': 433},\n",
" 5: {'patient': 2, 'phylum': 'Proteobacteria', 'value': 1130},\n",
" 6: {'patient': 2, 'phylum': 'Actinobacteria', 'value': 754},\n",
" 7: {'patient': 2, 'phylum': 'Bacteroidetes', 'value': 555}})"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 29
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 30,
"text": [
" 0 1 2 3 4 5 6 7\n",
"patient 1 1 1 1 2 2 2 2\n",
"phylum Firmicutes Proteobacteria Actinobacteria Bacteroidetes Firmicutes Proteobacteria Actinobacteria Bacteroidetes\n",
"value 632 1638 569 115 433 1130 754 555"
]
}
],
"prompt_number": 30
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We probably want this transposed:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data = data.T\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 31,
"text": [
" patient phylum value\n",
"0 1 Firmicutes 632\n",
"1 1 Proteobacteria 1638\n",
"2 1 Actinobacteria 569\n",
"3 1 Bacteroidetes 115\n",
"4 2 Firmicutes 433\n",
"5 2 Proteobacteria 1130\n",
"6 2 Actinobacteria 754\n",
"7 2 Bacteroidetes 555"
]
}
],
"prompt_number": 31
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Its important to note that the Series returned when a DataFrame is indexted is merely a **view** on the DataFrame, and not a copy of the data itself. So you must be cautious when manipulating this data:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"vals = data.value\n",
"vals"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 32,
"text": [
"0 632\n",
"1 1638\n",
"2 569\n",
"3 115\n",
"4 433\n",
"5 1130\n",
"6 754\n",
"7 555\n",
"Name: value, dtype: object"
]
}
],
"prompt_number": 32
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"vals[5] = 0\n",
"vals"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 33,
"text": [
"0 632\n",
"1 1638\n",
"2 569\n",
"3 115\n",
"4 433\n",
"5 0\n",
"6 754\n",
"7 555\n",
"Name: value, dtype: object"
]
}
],
"prompt_number": 33
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 34,
"text": [
" patient phylum value\n",
"0 1 Firmicutes 632\n",
"1 1 Proteobacteria 1638\n",
"2 1 Actinobacteria 569\n",
"3 1 Bacteroidetes 115\n",
"4 2 Firmicutes 433\n",
"5 2 Proteobacteria 0\n",
"6 2 Actinobacteria 754\n",
"7 2 Bacteroidetes 555"
]
}
],
"prompt_number": 34
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"vals = data.value.copy()\n",
"vals[5] = 1000\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 35,
"text": [
" patient phylum value\n",
"0 1 Firmicutes 632\n",
"1 1 Proteobacteria 1638\n",
"2 1 Actinobacteria 569\n",
"3 1 Bacteroidetes 115\n",
"4 2 Firmicutes 433\n",
"5 2 Proteobacteria 0\n",
"6 2 Actinobacteria 754\n",
"7 2 Bacteroidetes 555"
]
}
],
"prompt_number": 35
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can create or modify columns by assignment:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.value[3] = 14\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 36,
"text": [
" patient phylum value\n",
"0 1 Firmicutes 632\n",
"1 1 Proteobacteria 1638\n",
"2 1 Actinobacteria 569\n",
"3 1 Bacteroidetes 14\n",
"4 2 Firmicutes 433\n",
"5 2 Proteobacteria 0\n",
"6 2 Actinobacteria 754\n",
"7 2 Bacteroidetes 555"
]
}
],
"prompt_number": 36
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data['year'] = 2013\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 37,
"text": [
" patient phylum value year\n",
"0 1 Firmicutes 632 2013\n",
"1 1 Proteobacteria 1638 2013\n",
"2 1 Actinobacteria 569 2013\n",
"3 1 Bacteroidetes 14 2013\n",
"4 2 Firmicutes 433 2013\n",
"5 2 Proteobacteria 0 2013\n",
"6 2 Actinobacteria 754 2013\n",
"7 2 Bacteroidetes 555 2013"
]
}
],
"prompt_number": 37
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"But note, we cannot use the attribute indexing method to add a new column:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.treatment = 1\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 38,
"text": [
" patient phylum value year\n",
"0 1 Firmicutes 632 2013\n",
"1 1 Proteobacteria 1638 2013\n",
"2 1 Actinobacteria 569 2013\n",
"3 1 Bacteroidetes 14 2013\n",
"4 2 Firmicutes 433 2013\n",
"5 2 Proteobacteria 0 2013\n",
"6 2 Actinobacteria 754 2013\n",
"7 2 Bacteroidetes 555 2013"
]
}
],
"prompt_number": 38
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.treatment"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 39,
"text": [
"1"
]
}
],
"prompt_number": 39
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Specifying a `Series` as a new columns cause its values to be added according to the `DataFrame`'s index:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"treatment = pd.Series([0]*4 + [1]*2)\n",
"treatment"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 40,
"text": [
"0 0\n",
"1 0\n",
"2 0\n",
"3 0\n",
"4 1\n",
"5 1\n",
"dtype: int64"
]
}
],
"prompt_number": 40
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data['treatment'] = treatment\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 41,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1\n",
"6 2 Actinobacteria 754 2013 NaN\n",
"7 2 Bacteroidetes 555 2013 NaN"
]
}
],
"prompt_number": 41
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Other Python data structures (ones without an index) need to be the same length as the `DataFrame`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"month = ['Jan', 'Feb', 'Mar', 'Apr']\n",
"data['month'] = month"
],
"language": "python",
"metadata": {},
"outputs": [
{
"ename": "AssertionError",
"evalue": "Length of values does not match length of index",
"output_type": "pyerr",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mAssertionError\u001b[0m Traceback (most recent call last)",
"\u001b[0;32m<ipython-input-42-360d03fdde9a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0mmonth\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'Jan'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Feb'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Mar'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Apr'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mdata\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'month'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmonth\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
"\u001b[0;32m/Library/Python/2.7/site-packages/pandas-0.11.1.dev_fcced51_20130617-py2.7-macosx-10.8-intel.egg/pandas/core/frame.pyc\u001b[0m in \u001b[0;36m__setitem__\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 2137\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2138\u001b[0m \u001b[0;31m# set column\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2139\u001b[0;31m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 2140\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2141\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_setitem_slice\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m/Library/Python/2.7/site-packages/pandas-0.11.1.dev_fcced51_20130617-py2.7-macosx-10.8-intel.egg/pandas/core/frame.pyc\u001b[0m in \u001b[0;36m_set_item\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 2183\u001b[0m \u001b[0mensure\u001b[0m \u001b[0mhomogeneity\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2184\u001b[0m \"\"\"\n\u001b[0;32m-> 2185\u001b[0;31m \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sanitize_column\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 2186\u001b[0m \u001b[0mNDFrame\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2187\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m/Library/Python/2.7/site-packages/pandas-0.11.1.dev_fcced51_20130617-py2.7-macosx-10.8-intel.egg/pandas/core/frame.pyc\u001b[0m in \u001b[0;36m_sanitize_column\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 2217\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2218\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2219\u001b[0;31m raise AssertionError('Length of values does not match '\n\u001b[0m\u001b[1;32m 2220\u001b[0m 'length of index')\n\u001b[1;32m 2221\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;31mAssertionError\u001b[0m: Length of values does not match length of index"
]
}
],
"prompt_number": 42
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data['month'] = ['Jan']*len(data)\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 43,
"text": [
" patient phylum value year treatment month\n",
"0 1 Firmicutes 632 2013 0 Jan\n",
"1 1 Proteobacteria 1638 2013 0 Jan\n",
"2 1 Actinobacteria 569 2013 0 Jan\n",
"3 1 Bacteroidetes 14 2013 0 Jan\n",
"4 2 Firmicutes 433 2013 1 Jan\n",
"5 2 Proteobacteria 0 2013 1 Jan\n",
"6 2 Actinobacteria 754 2013 NaN Jan\n",
"7 2 Bacteroidetes 555 2013 NaN Jan"
]
}
],
"prompt_number": 43
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can use `del` to remove columns, in the same way `dict` entries can be removed:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"del data['month']\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 44,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1\n",
"6 2 Actinobacteria 754 2013 NaN\n",
"7 2 Bacteroidetes 555 2013 NaN"
]
}
],
"prompt_number": 44
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can extract the underlying data as a simple `ndarray` by accessing the `values` attribute:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.values"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 46,
"text": [
"array([[1, 'Firmicutes', 632, 2013, 0.0],\n",
" [1, 'Proteobacteria', 1638, 2013, 0.0],\n",
" [1, 'Actinobacteria', 569, 2013, 0.0],\n",
" [1, 'Bacteroidetes', 14, 2013, 0.0],\n",
" [2, 'Firmicutes', 433, 2013, 1.0],\n",
" [2, 'Proteobacteria', 0, 2013, 1.0],\n",
" [2, 'Actinobacteria', 754, 2013, nan],\n",
" [2, 'Bacteroidetes', 555, 2013, nan]], dtype=object)"
]
}
],
"prompt_number": 46
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice that because of the mix of string and integer (and `NaN`) values, the dtype of the array is `object`. The dtype will automatically be chosen to be as general as needed to accomodate all the columns."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df = pd.DataFrame({'foo': [1,2,3], 'bar':[0.4, -1.0, 4.5]})\n",
"df.values"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 55,
"text": [
"array([[ 0.4, 1. ],\n",
" [-1. , 2. ],\n",
" [ 4.5, 3. ]])"
]
}
],
"prompt_number": 55
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Pandas uses a custom data structure to represent the indices of Series and DataFrames."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.index"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 56,
"text": [
"Int64Index([0, 1, 2, 3, 4, 5, 6, 7], dtype=int64)"
]
}
],
"prompt_number": 56
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Index objects are immutable:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.index[0] = 15"
],
"language": "python",
"metadata": {},
"outputs": [
{
"ename": "Exception",
"evalue": "<class 'pandas.core.index.Int64Index'> object is immutable",
"output_type": "pyerr",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mException\u001b[0m Traceback (most recent call last)",
"\u001b[0;32m<ipython-input-57-42a852cc9eac>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m15\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
"\u001b[0;32m/Library/Python/2.7/site-packages/pandas-0.11.1.dev_fcced51_20130617-py2.7-macosx-10.8-intel.egg/pandas/core/index.pyc\u001b[0m in \u001b[0;36m__setitem__\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 362\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 363\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m__setitem__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 364\u001b[0;31m \u001b[0;32mraise\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__class__\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m' object is immutable'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 365\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 366\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m__getitem__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;31mException\u001b[0m: <class 'pandas.core.index.Int64Index'> object is immutable"
]
}
],
"prompt_number": 57
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This is so that Index objects can be shared between data structures without fear that they will be changed."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2.index = bacteria.index"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 58
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 59,
"text": [
"phylum\n",
"Firmicutes NaN\n",
"Proteobacteria 632\n",
"Actinobacteria 1638\n",
"Bacteroidetes 569\n",
"dtype: float64"
]
}
],
"prompt_number": 59
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Importing data"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A key, but often under-appreciated, step in data analysis is importing the data that we wish to analyze. Though it is easy to load basic data structures into Python using built-in tools or those provided by packages like NumPy, it is non-trivial to import structured data well, and to easily convert this input into a robust data structure:\n",
"\n",
" genes = np.loadtxt(\"genes.csv\", delimiter=\",\", dtype=[('gene', '|S10'), ('value', '<f4')])\n",
"\n",
"Pandas provides a convenient set of functions for importing tabular data in a number of formats directly into a `DataFrame` object. These functions include a slew of options to perform type inference, indexing, parsing, iterating and cleaning automatically as data are imported."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's start with some more bacteria data, stored in csv format."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"!cat data/microbiome.csv"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"Taxon,Patient,Tissue,Stool\r\n",
"Firmicutes,1,632,305\r\n",
"Firmicutes,2,136,4182\r\n",
"Firmicutes,3,1174,703\r\n",
"Firmicutes,4,408,3946\r\n",
"Firmicutes,5,831,8605\r\n",
"Firmicutes,6,693,50\r\n",
"Firmicutes,7,718,717\r\n",
"Firmicutes,8,173,33\r\n",
"Firmicutes,9,228,80\r\n",
"Firmicutes,10,162,3196\r\n",
"Firmicutes,11,372,32\r\n",
"Firmicutes,12,4255,4361\r\n",
"Firmicutes,13,107,1667\r\n",
"Firmicutes,14,96,223\r\n",
"Firmicutes,15,281,2377\r\n",
"Proteobacteria,1,1638,3886\r\n",
"Proteobacteria,2,2469,1821\r\n",
"Proteobacteria,3,839,661\r\n",
"Proteobacteria,4,4414,18\r\n",
"Proteobacteria,5,12044,83\r\n",
"Proteobacteria,6,2310,12\r\n",
"Proteobacteria,7,3053,547\r\n",
"Proteobacteria,8,395,2174\r\n",
"Proteobacteria,9,2651,767\r\n",
"Proteobacteria,10,1195,76\r\n",
"Proteobacteria,11,6857,795\r\n",
"Proteobacteria,12,483,666\r\n",
"Proteobacteria,13,2950,3994\r\n",
"Proteobacteria,14,1541,816\r\n",
"Proteobacteria,15,1307,53\r\n",
"Actinobacteria,1,569,648\r\n",
"Actinobacteria,2,1590,4\r\n",
"Actinobacteria,3,25,2\r\n",
"Actinobacteria,4,259,300\r\n",
"Actinobacteria,5,568,7\r\n",
"Actinobacteria,6,1102,9\r\n",
"Actinobacteria,7,678,377\r\n",
"Actinobacteria,8,260,58\r\n",
"Actinobacteria,9,424,233\r\n",
"Actinobacteria,10,548,21\r\n",
"Actinobacteria,11,201,83\r\n",
"Actinobacteria,12,42,75\r\n",
"Actinobacteria,13,109,59\r\n",
"Actinobacteria,14,51,183\r\n",
"Actinobacteria,15,310,204\r\n",
"Bacteroidetes,1,115,380\r\n",
"Bacteroidetes,2,67,0\r\n",
"Bacteroidetes,3,0,0\r\n",
"Bacteroidetes,4,85,5\r\n",
"Bacteroidetes,5,143,7\r\n",
"Bacteroidetes,6,678,2\r\n",
"Bacteroidetes,7,4829,209\r\n",
"Bacteroidetes,8,74,651\r\n",
"Bacteroidetes,9,169,254\r\n",
"Bacteroidetes,10,106,10\r\n",
"Bacteroidetes,11,73,381\r\n",
"Bacteroidetes,12,30,359\r\n",
"Bacteroidetes,13,51,51\r\n",
"Bacteroidetes,14,2473,2314\r\n",
"Bacteroidetes,15,102,33\r\n",
"Other,1,114,277\r\n",
"Other,2,195,18\r\n",
"Other,3,42,2\r\n",
"Other,4,316,43\r\n",
"Other,5,202,40\r\n",
"Other,6,116,0\r\n",
"Other,7,527,12\r\n",
"Other,8,357,11\r\n",
"Other,9,106,11\r\n",
"Other,10,67,14\r\n",
"Other,11,203,6\r\n",
"Other,12,392,6\r\n",
"Other,13,28,25\r\n",
"Other,14,12,22\r\n",
"Other,15,305,32"
]
}
],
"prompt_number": 60
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This table can be read into a DataFrame using `read_csv`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb = pd.read_csv(\"data/microbiome.csv\")\n",
"mb"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 61,
"text": [
"<class 'pandas.core.frame.DataFrame'>\n",
"Int64Index: 75 entries, 0 to 74\n",
"Data columns (total 4 columns):\n",
"Taxon 75 non-null values\n",
"Patient 75 non-null values\n",
"Tissue 75 non-null values\n",
"Stool 75 non-null values\n",
"dtypes: int64(3), object(1)"
]
}
],
"prompt_number": 61
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice that `read_csv` automatically considered the first row in the file to be a header row.\n",
"\n",
"We can override default behavior by customizing some the arguments, like `header`, `names` or `index_col`."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pd.read_csv(\"data/microbiome.csv\", header=None).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 64,
"text": [
" 0 1 2 3\n",
"0 Taxon Patient Tissue Stool\n",
"1 Firmicutes 1 632 305\n",
"2 Firmicutes 2 136 4182\n",
"3 Firmicutes 3 1174 703\n",
"4 Firmicutes 4 408 3946"
]
}
],
"prompt_number": 64
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"`read_csv` is just a convenience function for `read_table`, since csv is such a common format:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb = pd.read_table(\"data/microbiome.csv\", sep=',')"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 65
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The `sep` argument can be customized as needed to accomodate arbitrary separators. For example, we can use a regular expression to define a variable amount of whitespace, which is unfortunately very common in some data formats: \n",
" \n",
" sep='\\s+'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"For a more useful index, we can specify the first two columns, which together provide a unique index to the data."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb = pd.read_csv(\"data/microbiome.csv\", index_col=['Taxon','Patient'])\n",
"mb.head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 66,
"text": [
" Tissue Stool\n",
"Taxon Patient \n",
"Firmicutes 1 632 305\n",
" 2 136 4182\n",
" 3 1174 703\n",
" 4 408 3946\n",
" 5 831 8605"
]
}
],
"prompt_number": 66
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This is called a *hierarchical* index, which we will revisit later in the tutorial."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If we have sections of data that we do not wish to import (for example, known bad data), we can populate the `skiprows` argument:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pd.read_csv(\"data/microbiome.csv\", skiprows=[3,4,6]).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 67,
"text": [
" Taxon Patient Tissue Stool\n",
"0 Firmicutes 1 632 305\n",
"1 Firmicutes 2 136 4182\n",
"2 Firmicutes 5 831 8605\n",
"3 Firmicutes 7 718 717\n",
"4 Firmicutes 8 173 33"
]
}
],
"prompt_number": 67
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Conversely, if we only want to import a small number of rows from, say, a very large data file we can use `nrows`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pd.read_csv(\"data/microbiome.csv\", nrows=4)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 68,
"text": [
" Taxon Patient Tissue Stool\n",
"0 Firmicutes 1 632 305\n",
"1 Firmicutes 2 136 4182\n",
"2 Firmicutes 3 1174 703\n",
"3 Firmicutes 4 408 3946"
]
}
],
"prompt_number": 68
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Alternately, if we want to process our data in reasonable chunks, the `chunksize` argument will return an iterable object that can be employed in a data processing loop. For example, our microbiome data are organized by bacterial phylum, with 15 patients represented in each:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data_chunks = pd.read_csv(\"data/microbiome.csv\", chunksize=15)\n",
"\n",
"mean_tissue = {chunk.Taxon[0]:chunk.Tissue.mean() for chunk in data_chunks}\n",
" \n",
"mean_tissue"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 70,
"text": [
"{'Actinobacteria': 449.06666666666666,\n",
" 'Bacteroidetes': 599.66666666666663,\n",
" 'Firmicutes': 684.39999999999998,\n",
" 'Other': 198.80000000000001,\n",
" 'Proteobacteria': 2943.0666666666666}"
]
}
],
"prompt_number": 70
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Most real-world data is incomplete, with values missing due to incomplete observation, data entry or transcription error, or other reasons. Pandas will automatically recognize and parse common missing data indicators, including `NA` and `NULL`."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"!cat data/microbiome_missing.csv"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"Taxon,Patient,Tissue,Stool\r\n",
"Firmicutes,1,632,305\r\n",
"Firmicutes,2,136,4182\r\n",
"Firmicutes,3,,703\r\n",
"Firmicutes,4,408,3946\r\n",
"Firmicutes,5,831,8605\r\n",
"Firmicutes,6,693,50\r\n",
"Firmicutes,7,718,717\r\n",
"Firmicutes,8,173,33\r\n",
"Firmicutes,9,228,NA\r\n",
"Firmicutes,10,162,3196\r\n",
"Firmicutes,11,372,-99999\r\n",
"Firmicutes,12,4255,4361\r\n",
"Firmicutes,13,107,1667\r\n",
"Firmicutes,14,?,223\r\n",
"Firmicutes,15,281,2377\r\n",
"Proteobacteria,1,1638,3886\r\n",
"Proteobacteria,2,2469,1821\r\n",
"Proteobacteria,3,839,661\r\n",
"Proteobacteria,4,4414,18\r\n",
"Proteobacteria,5,12044,83\r\n",
"Proteobacteria,6,2310,12\r\n",
"Proteobacteria,7,3053,547\r\n",
"Proteobacteria,8,395,2174\r\n",
"Proteobacteria,9,2651,767\r\n",
"Proteobacteria,10,1195,76\r\n",
"Proteobacteria,11,6857,795\r\n",
"Proteobacteria,12,483,666\r\n",
"Proteobacteria,13,2950,3994\r\n",
"Proteobacteria,14,1541,816\r\n",
"Proteobacteria,15,1307,53\r\n",
"Actinobacteria,1,569,648\r\n",
"Actinobacteria,2,1590,4\r\n",
"Actinobacteria,3,25,2\r\n",
"Actinobacteria,4,259,300\r\n",
"Actinobacteria,5,568,7\r\n",
"Actinobacteria,6,1102,9\r\n",
"Actinobacteria,7,678,377\r\n",
"Actinobacteria,8,260,58\r\n",
"Actinobacteria,9,424,233\r\n",
"Actinobacteria,10,548,21\r\n",
"Actinobacteria,11,201,83\r\n",
"Actinobacteria,12,42,75\r\n",
"Actinobacteria,13,109,59\r\n",
"Actinobacteria,14,51,183\r\n",
"Actinobacteria,15,310,204\r\n",
"Bacteroidetes,1,115,380\r\n",
"Bacteroidetes,2,67,0\r\n",
"Bacteroidetes,3,0,0\r\n",
"Bacteroidetes,4,85,5\r\n",
"Bacteroidetes,5,143,7\r\n",
"Bacteroidetes,6,678,2\r\n",
"Bacteroidetes,7,4829,209\r\n",
"Bacteroidetes,8,74,651\r\n",
"Bacteroidetes,9,169,254\r\n",
"Bacteroidetes,10,106,10\r\n",
"Bacteroidetes,11,73,381\r\n",
"Bacteroidetes,12,30,359\r\n",
"Bacteroidetes,13,51,51\r\n",
"Bacteroidetes,14,2473,2314\r\n",
"Bacteroidetes,15,102,33\r\n",
"Other,1,114,277\r\n",
"Other,2,195,18\r\n",
"Other,3,42,2\r\n",
"Other,4,316,43\r\n",
"Other,5,202,40\r\n",
"Other,6,116,0\r\n",
"Other,7,527,12\r\n",
"Other,8,357,11\r\n",
"Other,9,106,11\r\n",
"Other,10,67,14\r\n",
"Other,11,203,6\r\n",
"Other,12,392,6\r\n",
"Other,13,28,25\r\n",
"Other,14,12,22\r\n",
"Other,15,305,32"
]
}
],
"prompt_number": 71
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pd.read_csv(\"data/microbiome_missing.csv\").head(20)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 72,
"text": [
" Taxon Patient Tissue Stool\n",
"0 Firmicutes 1 632 305\n",
"1 Firmicutes 2 136 4182\n",
"2 Firmicutes 3 NaN 703\n",
"3 Firmicutes 4 408 3946\n",
"4 Firmicutes 5 831 8605\n",
"5 Firmicutes 6 693 50\n",
"6 Firmicutes 7 718 717\n",
"7 Firmicutes 8 173 33\n",
"8 Firmicutes 9 228 NaN\n",
"9 Firmicutes 10 162 3196\n",
"10 Firmicutes 11 372 -99999\n",
"11 Firmicutes 12 4255 4361\n",
"12 Firmicutes 13 107 1667\n",
"13 Firmicutes 14 ? 223\n",
"14 Firmicutes 15 281 2377\n",
"15 Proteobacteria 1 1638 3886\n",
"16 Proteobacteria 2 2469 1821\n",
"17 Proteobacteria 3 839 661\n",
"18 Proteobacteria 4 4414 18\n",
"19 Proteobacteria 5 12044 83"
]
}
],
"prompt_number": 72
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Above, Pandas recognized `NA` and an empty field as missing data."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pd.isnull(pd.read_csv(\"data/microbiome_missing.csv\")).head(20)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 73,
"text": [
" Taxon Patient Tissue Stool\n",
"0 False False False False\n",
"1 False False False False\n",
"2 False False True False\n",
"3 False False False False\n",
"4 False False False False\n",
"5 False False False False\n",
"6 False False False False\n",
"7 False False False False\n",
"8 False False False True\n",
"9 False False False False\n",
"10 False False False False\n",
"11 False False False False\n",
"12 False False False False\n",
"13 False False False False\n",
"14 False False False False\n",
"15 False False False False\n",
"16 False False False False\n",
"17 False False False False\n",
"18 False False False False\n",
"19 False False False False"
]
}
],
"prompt_number": 73
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Unfortunately, there will sometimes be inconsistency with the conventions for missing data. In this example, there is a question mark \"?\" and a large negative number where there should have been a positive integer. We can specify additional symbols with the `na_values` argument:\n",
" "
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pd.read_csv(\"data/microbiome_missing.csv\", na_values=['?', -99999]).head(20)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 74,
"text": [
" Taxon Patient Tissue Stool\n",
"0 Firmicutes 1 632 305\n",
"1 Firmicutes 2 136 4182\n",
"2 Firmicutes 3 NaN 703\n",
"3 Firmicutes 4 408 3946\n",
"4 Firmicutes 5 831 8605\n",
"5 Firmicutes 6 693 50\n",
"6 Firmicutes 7 718 717\n",
"7 Firmicutes 8 173 33\n",
"8 Firmicutes 9 228 NaN\n",
"9 Firmicutes 10 162 3196\n",
"10 Firmicutes 11 372 NaN\n",
"11 Firmicutes 12 4255 4361\n",
"12 Firmicutes 13 107 1667\n",
"13 Firmicutes 14 NaN 223\n",
"14 Firmicutes 15 281 2377\n",
"15 Proteobacteria 1 1638 3886\n",
"16 Proteobacteria 2 2469 1821\n",
"17 Proteobacteria 3 839 661\n",
"18 Proteobacteria 4 4414 18\n",
"19 Proteobacteria 5 12044 83"
]
}
],
"prompt_number": 74
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"These can be specified on a column-wise basis using an appropriate dict as the argument for `na_values`."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Microsoft Excel\n",
"\n",
"Since so much financial and scientific data ends up in Excel spreadsheets (regrettably), Pandas' ability to directly import Excel spreadsheets is valuable. This support is contingent on having one or two dependencies (depending on what version of Excel file is being imported) installed: `xlrd` and `openpyxl` (these may be installed with either `pip` or `easy_install`).\n",
"\n",
"Importing Excel data to Pandas is a two-step process. First, we create an `ExcelFile` object using the path of the file: "
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb_file = pd.ExcelFile('data/microbiome/MID1.xls')\n",
"mb_file"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 75,
"text": [
"<pandas.io.excel.ExcelFile object at 0x10583a710>"
]
}
],
"prompt_number": 75
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Then, since modern spreadsheets consist of one or more \"sheets\", we parse the sheet with the data of interest:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb1 = mb_file.parse(\"Sheet 1\", header=None)\n",
"mb1.columns = [\"Taxon\", \"Count\"]\n",
"mb1.head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 76,
"text": [
" Taxon Count\n",
"0 Archaea \"Crenarchaeota\" Thermoprotei Desulfuro... 7\n",
"1 Archaea \"Crenarchaeota\" Thermoprotei Desulfuro... 2\n",
"2 Archaea \"Crenarchaeota\" Thermoprotei Sulfoloba... 3\n",
"3 Archaea \"Crenarchaeota\" Thermoprotei Thermopro... 3\n",
"4 Archaea \"Euryarchaeota\" \"Methanomicrobia\" Meth... 7"
]
}
],
"prompt_number": 76
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"There is now a `read_excel` conveneince function in Pandas that combines these steps into a single call:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb2 = pd.read_excel('data/microbiome/MID2.xls', sheetname='Sheet 1', header=None)\n",
"mb2.head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 77,
"text": [
" 0 1\n",
"0 Archaea \"Crenarchaeota\" Thermoprotei Acidiloba... 2\n",
"1 Archaea \"Crenarchaeota\" Thermoprotei Acidiloba... 14\n",
"2 Archaea \"Crenarchaeota\" Thermoprotei Desulfuro... 23\n",
"3 Archaea \"Crenarchaeota\" Thermoprotei Desulfuro... 1\n",
"4 Archaea \"Crenarchaeota\" Thermoprotei Desulfuro... 2"
]
}
],
"prompt_number": 77
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"There are several other data formats that can be imported into Python and converted into DataFrames, with the help of buitl-in or third-party libraries. These include JSON, XML, HDF5, relational and non-relational databases, and various web APIs. These are beyond the scope of this tutorial, but are covered in [Python for Data Analysis](http://shop.oreilly.com/product/0636920023784.do)."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Pandas Fundamentals"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This section introduces the new user to the key functionality of Pandas that is required to use the software effectively.\n",
"\n",
"For some variety, we will leave our digestive tract bacteria behind and employ some baseball data."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball = pd.read_csv(\"data/baseball.csv\", index_col='id')\n",
"baseball.head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 78,
"text": [
" player year stint team lg g ab r h X2b X3b hr rbi sb \\\n",
"id \n",
"88641 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 2 1 \n",
"88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 0 0 \n",
"88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 0 0 \n",
"88649 helliri01 2006 1 MIL NL 20 3 0 0 0 0 0 0 0 \n",
"88650 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 0 0 0 \n",
"\n",
" cs bb so ibb hbp sh sf gidp \n",
"id \n",
"88641 1 4 4 0 0 3 0 0 \n",
"88643 0 0 1 0 0 0 0 0 \n",
"88645 0 0 0 0 0 0 0 0 \n",
"88649 0 0 2 0 0 0 0 0 \n",
"88650 0 0 4 0 0 0 0 0 "
]
}
],
"prompt_number": 78
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice that we specified the `id` column as the index, since it appears to be a unique identifier. We could try to create a unique index ourselves by combining `player` and `year`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"player_id = baseball.player + baseball.year.astype(str)\n",
"baseball_newind = baseball.copy()\n",
"baseball_newind.index = player_id\n",
"baseball_newind.head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 79,
"text": [
" player year stint team lg g ab r h X2b X3b hr \\\n",
"womacto012006 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 \n",
"schilcu012006 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 \n",
"myersmi012006 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 \n",
"helliri012006 helliri01 2006 1 MIL NL 20 3 0 0 0 0 0 \n",
"johnsra052006 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 0 \n",
"\n",
" rbi sb cs bb so ibb hbp sh sf gidp \n",
"womacto012006 2 1 1 4 4 0 0 3 0 0 \n",
"schilcu012006 0 0 0 0 1 0 0 0 0 0 \n",
"myersmi012006 0 0 0 0 0 0 0 0 0 0 \n",
"helliri012006 0 0 0 0 2 0 0 0 0 0 \n",
"johnsra052006 0 0 0 0 4 0 0 0 0 0 "
]
}
],
"prompt_number": 79
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This looks okay, but let's check:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.index.is_unique"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 80,
"text": [
"False"
]
}
],
"prompt_number": 80
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"So, indices need not be unique. Our choice is not unique because some players change teams within years."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pd.Series(baseball_newind.index).value_counts()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 81,
"text": [
"wickmbo012007 2\n",
"gomezch022007 2\n",
"sweenma012007 2\n",
"claytro012007 2\n",
"hernaro012007 2\n",
"loftoke012007 2\n",
"trachst012007 2\n",
"wellsda012007 2\n",
"...\n",
"myersmi012007 1\n",
"alomasa022007 1\n",
"edmonji012007 1\n",
"sheffga012007 1\n",
"whiteri012007 1\n",
"cormirh012007 1\n",
"floydcl012007 1\n",
"embreal012007 1\n",
"Length: 88, dtype: int64"
]
}
],
"prompt_number": 81
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The most important consequence of a non-unique index is that indexing by label will return multiple values for some labels:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.ix['wickmbo012007']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 82,
"text": [
" player year stint team lg g ab r h X2b X3b hr \\\n",
"wickmbo012007 wickmbo01 2007 2 ARI NL 8 0 0 0 0 0 0 \n",
"wickmbo012007 wickmbo01 2007 1 ATL NL 47 0 0 0 0 0 0 \n",
"\n",
" rbi sb cs bb so ibb hbp sh sf gidp \n",
"wickmbo012007 0 0 0 0 0 0 0 0 0 0 \n",
"wickmbo012007 0 0 0 0 0 0 0 0 0 0 "
]
}
],
"prompt_number": 82
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We will learn more about indexing below."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can create a truly unique index by combining `player`, `team` and `year`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"player_unique = baseball.player + baseball.team + baseball.year.astype(str)\n",
"baseball_newind = baseball.copy()\n",
"baseball_newind.index = player_unique\n",
"baseball_newind.head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 83,
"text": [
" player year stint team lg g ab r h X2b X3b \\\n",
"womacto01CHN2006 womacto01 2006 2 CHN NL 19 50 6 14 1 0 \n",
"schilcu01BOS2006 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 \n",
"myersmi01NYA2006 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 \n",
"helliri01MIL2006 helliri01 2006 1 MIL NL 20 3 0 0 0 0 \n",
"johnsra05NYA2006 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 \n",
"\n",
" hr rbi sb cs bb so ibb hbp sh sf gidp \n",
"womacto01CHN2006 1 2 1 1 4 4 0 0 3 0 0 \n",
"schilcu01BOS2006 0 0 0 0 0 1 0 0 0 0 0 \n",
"myersmi01NYA2006 0 0 0 0 0 0 0 0 0 0 0 \n",
"helliri01MIL2006 0 0 0 0 0 2 0 0 0 0 0 \n",
"johnsra05NYA2006 0 0 0 0 0 4 0 0 0 0 0 "
]
}
],
"prompt_number": 83
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.index.is_unique"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 84,
"text": [
"True"
]
}
],
"prompt_number": 84
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can create meaningful indices more easily using a hierarchical index; for now, we will stick with the numeric `id` field as our index."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Manipulating indices\n",
"\n",
"**Reindexing** allows users to manipulate the data labels in a DataFrame. It forces a DataFrame to conform to the new index, and optionally, fill in missing data if requested.\n",
"\n",
"A simple use of `reindex` is to alter the order of the rows:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.reindex(baseball.index[::-1]).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 85,
"text": [
" player year stint team lg g ab r h X2b X3b hr rbi \\\n",
"id \n",
"89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 0 0 0 \n",
"89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 1 13 49 \n",
"89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 3 3 25 \n",
"89526 benitar01 2007 1 SFN NL 19 0 0 0 0 0 0 0 \n",
"89525 benitar01 2007 2 FLO NL 34 0 0 0 0 0 0 0 \n",
"\n",
" sb cs bb so ibb hbp sh sf gidp \n",
"id \n",
"89534 0 0 0 3 0 0 0 0 0 \n",
"89533 3 0 27 30 5 2 0 3 13 \n",
"89530 6 1 37 74 3 6 4 1 11 \n",
"89526 0 0 0 0 0 0 0 0 0 \n",
"89525 0 0 0 0 0 0 0 0 0 "
]
}
],
"prompt_number": 85
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice that the `id` index is not sequential. Say we wanted to populate the table with every `id` value. We could specify and index that is a sequence from the first to the last `id` numbers in the database, and Pandas would fill in the missing data with `NaN` values:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"id_range = range(baseball.index.values.min(), baseball.index.values.max())\n",
"baseball.reindex(id_range).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 86,
"text": [
" player year stint team lg g ab r h X2b X3b hr rbi \\\n",
"88641 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 2 \n",
"88642 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN \n",
"88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 0 \n",
"88644 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN \n",
"88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 0 \n",
"\n",
" sb cs bb so ibb hbp sh sf gidp \n",
"88641 1 1 4 4 0 0 3 0 0 \n",
"88642 NaN NaN NaN NaN NaN NaN NaN NaN NaN \n",
"88643 0 0 0 1 0 0 0 0 0 \n",
"88644 NaN NaN NaN NaN NaN NaN NaN NaN NaN \n",
"88645 0 0 0 0 0 0 0 0 0 "
]
}
],
"prompt_number": 86
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Missing values can be filled as desired, either with selected values, or by rule:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.reindex(id_range, method='ffill', columns=['player','year']).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 87,
"text": [
" player year\n",
"88641 womacto01 2006\n",
"88642 womacto01 2006\n",
"88643 schilcu01 2006\n",
"88644 schilcu01 2006\n",
"88645 myersmi01 2006"
]
}
],
"prompt_number": 87
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.reindex(id_range, fill_value='mr.nobody', columns=['player']).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 88,
"text": [
" player\n",
"88641 womacto01\n",
"88642 mr.nobody\n",
"88643 schilcu01\n",
"88644 mr.nobody\n",
"88645 myersmi01"
]
}
],
"prompt_number": 88
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Keep in mind that `reindex` does not work if we pass a non-unique index series."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can remove rows or columns via the `drop` method:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.shape"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 89,
"text": [
"(100, 22)"
]
}
],
"prompt_number": 89
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.drop([89525, 89526])"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 90,
"text": [
"<class 'pandas.core.frame.DataFrame'>\n",
"Int64Index: 98 entries, 88641 to 89534\n",
"Data columns (total 22 columns):\n",
"player 98 non-null values\n",
"year 98 non-null values\n",
"stint 98 non-null values\n",
"team 98 non-null values\n",
"lg 98 non-null values\n",
"g 98 non-null values\n",
"ab 98 non-null values\n",
"r 98 non-null values\n",
"h 98 non-null values\n",
"X2b 98 non-null values\n",
"X3b 98 non-null values\n",
"hr 98 non-null values\n",
"rbi 98 non-null values\n",
"sb 98 non-null values\n",
"cs 98 non-null values\n",
"bb 98 non-null values\n",
"so 98 non-null values\n",
"ibb 98 non-null values\n",
"hbp 98 non-null values\n",
"sh 98 non-null values\n",
"sf 98 non-null values\n",
"gidp 98 non-null values\n",
"dtypes: float64(9), int64(10), object(3)"
]
}
],
"prompt_number": 90
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.drop(['ibb','hbp'], axis=1)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 91,
"text": [
"<class 'pandas.core.frame.DataFrame'>\n",
"Int64Index: 100 entries, 88641 to 89534\n",
"Data columns (total 20 columns):\n",
"player 100 non-null values\n",
"year 100 non-null values\n",
"stint 100 non-null values\n",
"team 100 non-null values\n",
"lg 100 non-null values\n",
"g 100 non-null values\n",
"ab 100 non-null values\n",
"r 100 non-null values\n",
"h 100 non-null values\n",
"X2b 100 non-null values\n",
"X3b 100 non-null values\n",
"hr 100 non-null values\n",
"rbi 100 non-null values\n",
"sb 100 non-null values\n",
"cs 100 non-null values\n",
"bb 100 non-null values\n",
"so 100 non-null values\n",
"sh 100 non-null values\n",
"sf 100 non-null values\n",
"gidp 100 non-null values\n",
"dtypes: float64(7), int64(10), object(3)"
]
}
],
"prompt_number": 91
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Indexing and Selection\n",
"\n",
"Indexing works analogously to indexing in NumPy arrays, except we can use the labels in the `Index` object to extract values in addition to arrays of integers."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# Sample Series object\n",
"hits = baseball_newind.h\n",
"hits"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 92,
"text": [
"womacto01CHN2006 14\n",
"schilcu01BOS2006 1\n",
"myersmi01NYA2006 0\n",
"helliri01MIL2006 0\n",
"johnsra05NYA2006 1\n",
"finlest01SFN2006 105\n",
"gonzalu01ARI2006 159\n",
"seleaa01LAN2006 5\n",
"...\n",
"cirilje01MIN2007 40\n",
"bondsba01SFN2007 94\n",
"biggicr01HOU2007 130\n",
"benitar01FLO2007 0\n",
"benitar01SFN2007 0\n",
"ausmubr01HOU2007 82\n",
"aloumo01NYN2007 112\n",
"alomasa02NYN2007 3\n",
"Name: h, Length: 100, dtype: int64"
]
}
],
"prompt_number": 92
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# Numpy-style indexing\n",
"hits[:3]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 93,
"text": [
"womacto01CHN2006 14\n",
"schilcu01BOS2006 1\n",
"myersmi01NYA2006 0\n",
"Name: h, dtype: int64"
]
}
],
"prompt_number": 93
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# Indexing by label\n",
"hits[['womacto01CHN2006','schilcu01BOS2006']]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 94,
"text": [
"womacto01CHN2006 14\n",
"schilcu01BOS2006 1\n",
"Name: h, dtype: int64"
]
}
],
"prompt_number": 94
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can also slice with data labels, since they have an intrinsic order within the Index:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"hits['womacto01CHN2006':'gonzalu01ARI2006']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 95,
"text": [
"womacto01CHN2006 14\n",
"schilcu01BOS2006 1\n",
"myersmi01NYA2006 0\n",
"helliri01MIL2006 0\n",
"johnsra05NYA2006 1\n",
"finlest01SFN2006 105\n",
"gonzalu01ARI2006 159\n",
"Name: h, dtype: int64"
]
}
],
"prompt_number": 95
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"hits['womacto01CHN2006':'gonzalu01ARI2006'] = 5\n",
"hits"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 97,
"text": [
"womacto01CHN2006 5\n",
"schilcu01BOS2006 5\n",
"myersmi01NYA2006 5\n",
"helliri01MIL2006 5\n",
"johnsra05NYA2006 5\n",
"finlest01SFN2006 5\n",
"gonzalu01ARI2006 5\n",
"seleaa01LAN2006 5\n",
"...\n",
"cirilje01MIN2007 40\n",
"bondsba01SFN2007 94\n",
"biggicr01HOU2007 130\n",
"benitar01FLO2007 0\n",
"benitar01SFN2007 0\n",
"ausmubr01HOU2007 82\n",
"aloumo01NYN2007 112\n",
"alomasa02NYN2007 3\n",
"Name: h, Length: 100, dtype: int64"
]
}
],
"prompt_number": 97
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In a `DataFrame` we can slice along either or both axes:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind[['h','ab']]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 98,
"text": [
"<class 'pandas.core.frame.DataFrame'>\n",
"Index: 100 entries, womacto01CHN2006 to alomasa02NYN2007\n",
"Data columns (total 2 columns):\n",
"h 100 non-null values\n",
"ab 100 non-null values\n",
"dtypes: int64(2)"
]
}
],
"prompt_number": 98
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind[baseball_newind.ab>500]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 99,
"text": [
" player year stint team lg g ab r h X2b \\\n",
"gonzalu01ARI2006 gonzalu01 2006 1 ARI NL 153 586 93 5 52 \n",
"vizquom01SFN2007 vizquom01 2007 1 SFN NL 145 513 54 126 18 \n",
"thomafr04TOR2007 thomafr04 2007 1 TOR AL 155 531 63 147 30 \n",
"rodriiv01DET2007 rodriiv01 2007 1 DET AL 129 502 50 141 31 \n",
"griffke02CIN2007 griffke02 2007 1 CIN NL 144 528 78 146 24 \n",
"delgaca01NYN2007 delgaca01 2007 1 NYN NL 139 538 71 139 30 \n",
"biggicr01HOU2007 biggicr01 2007 1 HOU NL 141 517 68 130 31 \n",
"\n",
" X3b hr rbi sb cs bb so ibb hbp sh sf gidp \n",
"gonzalu01ARI2006 2 15 73 0 1 69 58 10 7 0 6 14 \n",
"vizquom01SFN2007 3 4 51 14 6 44 48 6 1 14 3 14 \n",
"thomafr04TOR2007 0 26 95 0 0 81 94 3 7 0 5 14 \n",
"rodriiv01DET2007 3 11 63 2 2 9 96 1 1 1 2 16 \n",
"griffke02CIN2007 1 30 93 6 1 85 99 14 1 0 9 14 \n",
"delgaca01NYN2007 0 24 87 4 0 52 118 8 11 0 6 12 \n",
"biggicr01HOU2007 3 10 50 4 3 23 112 0 3 7 5 5 "
]
}
],
"prompt_number": 99
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The indexing field `ix` allows us to select subsets of rows and columns in an intuitive way:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.ix['gonzalu01ARI2006', ['h','X2b', 'X3b', 'hr']]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 100,
"text": [
"h 5\n",
"X2b 52\n",
"X3b 2\n",
"hr 15\n",
"Name: gonzalu01ARI2006, dtype: object"
]
}
],
"prompt_number": 100
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.ix[['gonzalu01ARI2006','finlest01SFN2006'], 5:8]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 101,
"text": [
" g ab r\n",
"gonzalu01ARI2006 153 586 93\n",
"finlest01SFN2006 139 426 66"
]
}
],
"prompt_number": 101
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.ix[:'myersmi01NYA2006', 'hr']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 102,
"text": [
"womacto01CHN2006 1\n",
"schilcu01BOS2006 0\n",
"myersmi01NYA2006 0\n",
"Name: hr, dtype: int64"
]
}
],
"prompt_number": 102
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Similarly, the cross-section method `xs` (not a field) extracts a single column or row *by label* and returns it as a `Series`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.xs('myersmi01NYA2006')"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 103,
"text": [
"player myersmi01\n",
"year 2006\n",
"stint 1\n",
"team NYA\n",
"lg AL\n",
"g 62\n",
"ab 0\n",
"r 0\n",
"...\n",
"cs 0\n",
"bb 0\n",
"so 0\n",
"ibb 0\n",
"hbp 0\n",
"sh 0\n",
"sf 0\n",
"gidp 0\n",
"Name: myersmi01NYA2006, Length: 22, dtype: object"
]
}
],
"prompt_number": 103
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Operations\n",
"\n",
"`DataFrame` and `Series` objects allow for several operations to take place either on a single object, or between two or more objects.\n",
"\n",
"For example, we can perform arithmetic on the elements of two objects, such as combining baseball statistics across years:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"hr2006 = baseball[baseball.year==2006].xs('hr', axis=1)\n",
"hr2006.index = baseball.player[baseball.year==2006]\n",
"\n",
"hr2007 = baseball[baseball.year==2007].xs('hr', axis=1)\n",
"hr2007.index = baseball.player[baseball.year==2007]"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 104
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"hr2006 = pd.Series(baseball.hr[baseball.year==2006].values, index=baseball.player[baseball.year==2006])\n",
"hr2007 = pd.Series(baseball.hr[baseball.year==2007].values, index=baseball.player[baseball.year==2007])"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 105
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"hr_total = hr2006 + hr2007\n",
"hr_total"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 106,
"text": [
"player\n",
"alomasa02 NaN\n",
"aloumo01 NaN\n",
"ausmubr01 NaN\n",
"benitar01 NaN\n",
"benitar01 NaN\n",
"biggicr01 NaN\n",
"bondsba01 NaN\n",
"cirilje01 NaN\n",
"...\n",
"whiteri01 NaN\n",
"whitero02 NaN\n",
"wickmbo01 NaN\n",
"wickmbo01 NaN\n",
"williwo02 NaN\n",
"witasja01 NaN\n",
"womacto01 NaN\n",
"zaungr01 NaN\n",
"Length: 94, dtype: float64"
]
}
],
"prompt_number": 106
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Pandas' data alignment places `NaN` values for labels that do not overlap in the two Series. In fact, there are only 6 players that occur in both years."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"hr_total[hr_total.notnull()]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 107,
"text": [
"player\n",
"finlest01 7\n",
"gonzalu01 30\n",
"johnsra05 0\n",
"myersmi01 0\n",
"schilcu01 0\n",
"seleaa01 0\n",
"dtype: float64"
]
}
],
"prompt_number": 107
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"While we do want the operation to honor the data labels in this way, we probably do not want the missing values to be filled with `NaN`. We can use the `add` method to calculate player home run totals by using the `fill_value` argument to insert a zero for home runs where labels do not overlap:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"hr2007.add(hr2006, fill_value=0)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 108,
"text": [
"player\n",
"alomasa02 0\n",
"aloumo01 13\n",
"ausmubr01 3\n",
"benitar01 0\n",
"benitar01 0\n",
"biggicr01 10\n",
"bondsba01 28\n",
"cirilje01 0\n",
"...\n",
"whiteri01 0\n",
"whitero02 4\n",
"wickmbo01 0\n",
"wickmbo01 0\n",
"williwo02 1\n",
"witasja01 0\n",
"womacto01 1\n",
"zaungr01 10\n",
"Length: 94, dtype: float64"
]
}
],
"prompt_number": 108
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Operations can also be **broadcast** between rows or columns.\n",
"\n",
"For example, if we subtract the maximum number of home runs hit from the `hr` column, we get how many fewer than the maximum were hit by each player:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.hr - baseball.hr.max()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 109,
"text": [
"id\n",
"88641 -34\n",
"88643 -35\n",
"88645 -35\n",
"88649 -35\n",
"88650 -35\n",
"88652 -29\n",
"88653 -20\n",
"88662 -35\n",
"...\n",
"89502 -33\n",
"89521 -7\n",
"89523 -25\n",
"89525 -35\n",
"89526 -35\n",
"89530 -32\n",
"89533 -22\n",
"89534 -35\n",
"Name: hr, Length: 100, dtype: int64"
]
}
],
"prompt_number": 109
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Or, looking at things row-wise, we can see how a particular player compares with the rest of the group with respect to important statistics"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.ix[89521][\"player\"]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 110,
"text": [
"'bondsba01'"
]
}
],
"prompt_number": 110
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"stats = baseball[['h','X2b', 'X3b', 'hr']]\n",
"diff = stats - stats.xs(89521)\n",
"diff[:10]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 111,
"text": [
" h X2b X3b hr\n",
"id \n",
"88641 -80 -13 0 -27\n",
"88643 -93 -14 0 -28\n",
"88645 -94 -14 0 -28\n",
"88649 -94 -14 0 -28\n",
"88650 -93 -14 0 -28\n",
"88652 11 7 12 -22\n",
"88653 65 38 2 -13\n",
"88662 -89 -13 0 -28\n",
"89177 -84 -11 0 -28\n",
"89178 -84 -14 0 -27"
]
}
],
"prompt_number": 111
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can also apply functions to each column or row of a `DataFrame`"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"stats.apply(np.median)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 112,
"text": [
"h 8\n",
"X2b 1\n",
"X3b 0\n",
"hr 0\n",
"dtype: float64"
]
}
],
"prompt_number": 112
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"stat_range = lambda x: x.max() - x.min()\n",
"stats.apply(stat_range)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 113,
"text": [
"h 159\n",
"X2b 52\n",
"X3b 12\n",
"hr 35\n",
"dtype: int64"
]
}
],
"prompt_number": 113
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Lets use apply to calculate a meaningful baseball statistics, slugging percentage:\n",
"\n",
"$$SLG = \\frac{1B + (2 \\times 2B) + (3 \\times 3B) + (4 \\times HR)}{AB}$$\n",
"\n",
"And just for fun, we will format the resulting estimate."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"slg = lambda x: (x['h']-x['X2b']-x['X3b']-x['hr'] + 2*x['X2b'] + 3*x['X3b'] + 4*x['hr'])/(x['ab']+1e-6)\n",
"baseball.apply(slg, axis=1).apply(lambda x: '%.3f' % x)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 114,
"text": [
"id\n",
"88641 0.360\n",
"88643 0.500\n",
"88645 0.000\n",
"88649 0.000\n",
"88650 0.167\n",
"88652 0.394\n",
"88653 0.444\n",
"88662 0.231\n",
"...\n",
"89502 0.386\n",
"89521 0.565\n",
"89523 0.381\n",
"89525 0.000\n",
"89526 0.000\n",
"89530 0.324\n",
"89533 0.524\n",
"89534 0.182\n",
"Length: 100, dtype: object"
]
}
],
"prompt_number": 114
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Sorting and Ranking\n",
"\n",
"Pandas objects include methods for re-ordering data."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.sort_index().head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 115,
"text": [
" player year stint team lg g ab r h X2b \\\n",
"alomasa02NYN2007 alomasa02 2007 1 NYN NL 8 22 1 3 1 \n",
"aloumo01NYN2007 aloumo01 2007 1 NYN NL 87 328 51 112 19 \n",
"ausmubr01HOU2007 ausmubr01 2007 1 HOU NL 117 349 38 82 16 \n",
"benitar01FLO2007 benitar01 2007 2 FLO NL 34 0 0 0 0 \n",
"benitar01SFN2007 benitar01 2007 1 SFN NL 19 0 0 0 0 \n",
"\n",
" X3b hr rbi sb cs bb so ibb hbp sh sf gidp \n",
"alomasa02NYN2007 0 0 0 0 0 0 3 0 0 0 0 0 \n",
"aloumo01NYN2007 1 13 49 3 0 27 30 5 2 0 3 13 \n",
"ausmubr01HOU2007 3 3 25 6 1 37 74 3 6 4 1 11 \n",
"benitar01FLO2007 0 0 0 0 0 0 0 0 0 0 0 0 \n",
"benitar01SFN2007 0 0 0 0 0 0 0 0 0 0 0 0 "
]
}
],
"prompt_number": 115
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.sort_index(ascending=False).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 116,
"text": [
" player year stint team lg g ab r h X2b X3b \\\n",
"zaungr01TOR2007 zaungr01 2007 1 TOR AL 110 331 43 80 24 1 \n",
"womacto01CHN2006 womacto01 2006 2 CHN NL 19 50 6 5 1 0 \n",
"witasja01TBA2007 witasja01 2007 1 TBA AL 3 0 0 0 0 0 \n",
"williwo02HOU2007 williwo02 2007 1 HOU NL 33 59 3 6 0 0 \n",
"wickmbo01ATL2007 wickmbo01 2007 1 ATL NL 47 0 0 0 0 0 \n",
"\n",
" hr rbi sb cs bb so ibb hbp sh sf gidp \n",
"zaungr01TOR2007 10 52 0 0 51 55 8 2 1 6 9 \n",
"womacto01CHN2006 1 2 1 1 4 4 0 0 3 0 0 \n",
"witasja01TBA2007 0 0 0 0 0 0 0 0 0 0 0 \n",
"williwo02HOU2007 1 2 0 0 0 25 0 0 5 0 1 \n",
"wickmbo01ATL2007 0 0 0 0 0 0 0 0 0 0 0 "
]
}
],
"prompt_number": 116
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_newind.sort_index(axis=1).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 117,
"text": [
" X2b X3b ab bb cs g gidp h hbp hr ibb lg \\\n",
"womacto01CHN2006 1 0 50 4 1 19 0 5 0 1 0 NL \n",
"schilcu01BOS2006 0 0 2 0 0 31 0 5 0 0 0 AL \n",
"myersmi01NYA2006 0 0 0 0 0 62 0 5 0 0 0 AL \n",
"helliri01MIL2006 0 0 3 0 0 20 0 5 0 0 0 NL \n",
"johnsra05NYA2006 0 0 6 0 0 33 0 5 0 0 0 AL \n",
"\n",
" player r rbi sb sf sh so stint team year \n",
"womacto01CHN2006 womacto01 6 2 1 0 3 4 2 CHN 2006 \n",
"schilcu01BOS2006 schilcu01 0 0 0 0 0 1 1 BOS 2006 \n",
"myersmi01NYA2006 myersmi01 0 0 0 0 0 0 1 NYA 2006 \n",
"helliri01MIL2006 helliri01 0 0 0 0 0 2 1 MIL 2006 \n",
"johnsra05NYA2006 johnsra05 0 0 0 0 0 4 1 NYA 2006 "
]
}
],
"prompt_number": 117
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can also use `order` to sort a `Series` by value, rather than by label."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.hr.order(ascending=False)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 118,
"text": [
"id\n",
"89360 35\n",
"89462 30\n",
"89521 28\n",
"89361 26\n",
"89378 25\n",
"89489 24\n",
"89374 21\n",
"89371 21\n",
"...\n",
"89335 0\n",
"89333 0\n",
"89177 0\n",
"88662 0\n",
"88650 0\n",
"88649 0\n",
"88645 0\n",
"88643 0\n",
"Name: hr, Length: 100, dtype: int64"
]
}
],
"prompt_number": 118
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"For a `DataFrame`, we can sort according to the values of one or more columns using the `by` argument of `sort_index`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball[['player','sb','cs']].sort_index(ascending=[False,True], by=['sb', 'cs']).head(10)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 119,
"text": [
" player sb cs\n",
"id \n",
"89378 sheffga01 22 5\n",
"89430 loftoke01 21 4\n",
"89347 vizquom01 14 6\n",
"89463 greensh01 11 1\n",
"88652 finlest01 7 0\n",
"89462 griffke02 6 1\n",
"89530 ausmubr01 6 1\n",
"89466 gonzalu01 6 2\n",
"89521 bondsba01 5 0\n",
"89438 kleskry01 5 1"
]
}
],
"prompt_number": 119
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Ranking** does not re-arrange data, but instead returns an index that ranks each value relative to others in the Series."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.hr.rank()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 120,
"text": [
"id\n",
"88641 62.5\n",
"88643 29.0\n",
"88645 29.0\n",
"88649 29.0\n",
"88650 29.0\n",
"88652 76.0\n",
"88653 89.5\n",
"88662 29.0\n",
"...\n",
"89502 69.0\n",
"89521 98.0\n",
"89523 83.5\n",
"89525 29.0\n",
"89526 29.0\n",
"89530 71.5\n",
"89533 88.0\n",
"89534 29.0\n",
"Name: hr, Length: 100, dtype: float64"
]
}
],
"prompt_number": 120
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Ties are assigned the mean value of the tied ranks, which may result in decimal values."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pd.Series([100,100]).rank()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 121,
"text": [
"0 1.5\n",
"1 1.5\n",
"dtype: float64"
]
}
],
"prompt_number": 121
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Alternatively, you can break ties via one of several methods, such as by the order in which they occur in the dataset:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.hr.rank(method='first')"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 122,
"text": [
"id\n",
"88641 58\n",
"88643 1\n",
"88645 2\n",
"88649 3\n",
"88650 4\n",
"88652 75\n",
"88653 89\n",
"88662 5\n",
"...\n",
"89502 70\n",
"89521 98\n",
"89523 85\n",
"89525 55\n",
"89526 56\n",
"89530 72\n",
"89533 88\n",
"89534 57\n",
"Name: hr, Length: 100, dtype: float64"
]
}
],
"prompt_number": 122
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Calling the `DataFrame`'s `rank` method results in the ranks of all columns:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.rank(ascending=False).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 123,
"text": [
" player year stint team lg g ab r h X2b X3b \\\n",
"id \n",
"88641 2.0 96.5 7 82.0 31.5 70.0 47.5 40.5 39.0 50.5 63.5 \n",
"88643 37.5 96.5 57 88.0 81.5 55.5 73.0 81.0 63.5 78.0 63.5 \n",
"88645 47.5 96.5 57 40.5 81.5 36.0 91.0 81.0 84.5 78.0 63.5 \n",
"88649 66.0 96.5 57 47.0 31.5 67.5 69.0 81.0 84.5 78.0 63.5 \n",
"88650 61.5 96.5 57 40.5 81.5 51.0 64.5 81.0 63.5 78.0 63.5 \n",
"\n",
" hr rbi sb cs bb so ibb hbp sh sf gidp \n",
"id \n",
"88641 38.5 51.0 24.5 17.5 44.5 59 66 65.5 16.0 70 76.5 \n",
"88643 72.0 78.5 63.5 62.5 79.0 73 66 65.5 67.5 70 76.5 \n",
"88645 72.0 78.5 63.5 62.5 79.0 89 66 65.5 67.5 70 76.5 \n",
"88649 72.0 78.5 63.5 62.5 79.0 67 66 65.5 67.5 70 76.5 \n",
"88650 72.0 78.5 63.5 62.5 79.0 59 66 65.5 67.5 70 76.5 "
]
}
],
"prompt_number": 123
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball[['r','h','hr']].rank(ascending=False).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 124,
"text": [
" r h hr\n",
"id \n",
"88641 40.5 39.0 38.5\n",
"88643 81.0 63.5 72.0\n",
"88645 81.0 84.5 72.0\n",
"88649 81.0 84.5 72.0\n",
"88650 81.0 63.5 72.0"
]
}
],
"prompt_number": 124
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Exercise\n",
"\n",
"Calculate **on base percentage** for each player, and return the ordered series of estimates.\n",
"\n",
"$$OBP = \\frac{H + BB + HBP}{AB + BB + HBP + SF}$$"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 124
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Hierarchical indexing\n",
"\n",
"In the baseball example, I was forced to combine 3 fields to obtain a unique index that was not simply an integer value. A more elegant way to have done this would be to create a hierarchical index from the three fields."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_h = baseball.set_index(['year', 'team', 'player'])\n",
"baseball_h.head(10)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 125,
"text": [
" stint lg g ab r h X2b X3b hr rbi sb cs \\\n",
"year team player \n",
"2006 CHN womacto01 2 NL 19 50 6 14 1 0 1 2 1 1 \n",
" BOS schilcu01 1 AL 31 2 0 1 0 0 0 0 0 0 \n",
" NYA myersmi01 1 AL 62 0 0 0 0 0 0 0 0 0 \n",
" MIL helliri01 1 NL 20 3 0 0 0 0 0 0 0 0 \n",
" NYA johnsra05 1 AL 33 6 0 1 0 0 0 0 0 0 \n",
" SFN finlest01 1 NL 139 426 66 105 21 12 6 40 7 0 \n",
" ARI gonzalu01 1 NL 153 586 93 159 52 2 15 73 0 1 \n",
" LAN seleaa01 1 NL 28 26 2 5 1 0 0 0 0 0 \n",
"2007 ATL francju01 2 NL 15 40 1 10 3 0 0 8 0 0 \n",
" NYN francju01 1 NL 40 50 7 10 0 0 1 8 2 1 \n",
"\n",
" bb so ibb hbp sh sf gidp \n",
"year team player \n",
"2006 CHN womacto01 4 4 0 0 3 0 0 \n",
" BOS schilcu01 0 1 0 0 0 0 0 \n",
" NYA myersmi01 0 0 0 0 0 0 0 \n",
" MIL helliri01 0 2 0 0 0 0 0 \n",
" NYA johnsra05 0 4 0 0 0 0 0 \n",
" SFN finlest01 46 55 2 2 3 4 6 \n",
" ARI gonzalu01 69 58 10 7 0 6 14 \n",
" LAN seleaa01 1 7 0 0 6 0 1 \n",
"2007 ATL francju01 4 10 1 0 0 1 1 \n",
" NYN francju01 10 13 0 0 0 1 1 "
]
}
],
"prompt_number": 125
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This index is a `MultiIndex` object that consists of a sequence of tuples, the elements of which is some combination of the three columns used to create the index. Where there are multiple repeated values, Pandas does not print the repeats, making it easy to identify groups of values."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_h.index[:10]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 126,
"text": [
"MultiIndex\n",
"[(2006, u'CHN', u'womacto01'), (2006, u'BOS', u'schilcu01'), (2006, u'NYA', u'myersmi01'), (2006, u'MIL', u'helliri01'), (2006, u'NYA', u'johnsra05'), (2006, u'SFN', u'finlest01'), (2006, u'ARI', u'gonzalu01'), (2006, u'LAN', u'seleaa01'), (2007, u'ATL', u'francju01'), (2007, u'NYN', u'francju01')]"
]
}
],
"prompt_number": 126
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_h.index.is_unique"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 127,
"text": [
"True"
]
}
],
"prompt_number": 127
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball_h.ix[(2007, 'ATL', 'francju01')]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 128,
"text": [
"stint 2\n",
"lg NL\n",
"g 15\n",
"ab 40\n",
"r 1\n",
"h 10\n",
"X2b 3\n",
"X3b 0\n",
"hr 0\n",
"rbi 8\n",
"sb 0\n",
"cs 0\n",
"bb 4\n",
"so 10\n",
"ibb 1\n",
"hbp 0\n",
"sh 0\n",
"sf 1\n",
"gidp 1\n",
"Name: (2007, ATL, francju01), dtype: object"
]
}
],
"prompt_number": 128
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Recall earlier we imported some microbiome data using two index columns. This created a 2-level hierarchical index:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb = pd.read_csv(\"data/microbiome.csv\", index_col=['Taxon','Patient'])"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 129
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb.head(10)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 130,
"text": [
" Tissue Stool\n",
"Taxon Patient \n",
"Firmicutes 1 632 305\n",
" 2 136 4182\n",
" 3 1174 703\n",
" 4 408 3946\n",
" 5 831 8605\n",
" 6 693 50\n",
" 7 718 717\n",
" 8 173 33\n",
" 9 228 80\n",
" 10 162 3196"
]
}
],
"prompt_number": 130
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb.index"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 131,
"text": [
"MultiIndex\n",
"[(u'Firmicutes', 1), (u'Firmicutes', 2), (u'Firmicutes', 3), (u'Firmicutes', 4), (u'Firmicutes', 5), (u'Firmicutes', 6), (u'Firmicutes', 7), (u'Firmicutes', 8), (u'Firmicutes', 9), (u'Firmicutes', 10), (u'Firmicutes', 11), (u'Firmicutes', 12), (u'Firmicutes', 13), (u'Firmicutes', 14), (u'Firmicutes', 15), (u'Proteobacteria', 1), (u'Proteobacteria', 2), (u'Proteobacteria', 3), (u'Proteobacteria', 4), (u'Proteobacteria', 5), (u'Proteobacteria', 6), (u'Proteobacteria', 7), (u'Proteobacteria', 8), (u'Proteobacteria', 9), (u'Proteobacteria', 10), (u'Proteobacteria', 11), (u'Proteobacteria', 12), (u'Proteobacteria', 13), (u'Proteobacteria', 14), (u'Proteobacteria', 15), (u'Actinobacteria', 1), (u'Actinobacteria', 2), (u'Actinobacteria', 3), (u'Actinobacteria', 4), (u'Actinobacteria', 5), (u'Actinobacteria', 6), (u'Actinobacteria', 7), (u'Actinobacteria', 8), (u'Actinobacteria', 9), (u'Actinobacteria', 10), (u'Actinobacteria', 11), (u'Actinobacteria', 12), (u'Actinobacteria', 13), (u'Actinobacteria', 14), (u'Actinobacteria', 15), (u'Bacteroidetes', 1), (u'Bacteroidetes', 2), (u'Bacteroidetes', 3), (u'Bacteroidetes', 4), (u'Bacteroidetes', 5), (u'Bacteroidetes', 6), (u'Bacteroidetes', 7), (u'Bacteroidetes', 8), (u'Bacteroidetes', 9), (u'Bacteroidetes', 10), (u'Bacteroidetes', 11), (u'Bacteroidetes', 12), (u'Bacteroidetes', 13), (u'Bacteroidetes', 14), (u'Bacteroidetes', 15), (u'Other', 1), (u'Other', 2), (u'Other', 3), (u'Other', 4), (u'Other', 5), (u'Other', 6), (u'Other', 7), (u'Other', 8), (u'Other', 9), (u'Other', 10), (u'Other', 11), (u'Other', 12), (u'Other', 13), (u'Other', 14), (u'Other', 15)]"
]
}
],
"prompt_number": 131
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"With a hierachical index, we can select subsets of the data based on a *partial* index:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb.ix['Proteobacteria']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 132,
"text": [
" Tissue Stool\n",
"Patient \n",
"1 1638 3886\n",
"2 2469 1821\n",
"3 839 661\n",
"4 4414 18\n",
"5 12044 83\n",
"6 2310 12\n",
"7 3053 547\n",
"8 395 2174\n",
"9 2651 767\n",
"10 1195 76\n",
"11 6857 795\n",
"12 483 666\n",
"13 2950 3994\n",
"14 1541 816\n",
"15 1307 53"
]
}
],
"prompt_number": 132
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Hierarchical indices can be created on either or both axes. Here is a trivial example:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"frame = pd.DataFrame(np.arange(12).reshape(( 4, 3)), \n",
" index =[['a', 'a', 'b', 'b'], [1, 2, 1, 2]], \n",
" columns =[['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']])\n",
"\n",
"frame"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 133,
"text": [
" Ohio Colorado\n",
" Green Red Green\n",
"a 1 0 1 2\n",
" 2 3 4 5\n",
"b 1 6 7 8\n",
" 2 9 10 11"
]
}
],
"prompt_number": 133
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If you want to get fancy, both the row and column indices themselves can be given names:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"frame.index.names = ['key1', 'key2']\n",
"frame.columns.names = ['state', 'color']\n",
"frame"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 134,
"text": [
"state Ohio Colorado\n",
"color Green Red Green\n",
"key1 key2 \n",
"a 1 0 1 2\n",
" 2 3 4 5\n",
"b 1 6 7 8\n",
" 2 9 10 11"
]
}
],
"prompt_number": 134
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"With this, we can do all sorts of custom indexing:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"frame.ix['a']['Ohio']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 135,
"text": [
"color Green Red\n",
"key2 \n",
"1 0 1\n",
"2 3 4"
]
}
],
"prompt_number": 135
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"frame.ix['b', 2]['Colorado']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 136,
"text": [
"color\n",
"Green 11\n",
"Name: (b, 2), dtype: int64"
]
}
],
"prompt_number": 136
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Additionally, the order of the set of indices in a hierarchical `MultiIndex` can be changed by swapping them pairwise:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb.swaplevel('Patient', 'Taxon').head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 137,
"text": [
" Tissue Stool\n",
"Patient Taxon \n",
"1 Firmicutes 632 305\n",
"2 Firmicutes 136 4182\n",
"3 Firmicutes 1174 703\n",
"4 Firmicutes 408 3946\n",
"5 Firmicutes 831 8605"
]
}
],
"prompt_number": 137
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Data can also be sorted by any index level, using `sortlevel`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb.sortlevel('Patient', ascending=False).head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 138,
"text": [
" Tissue Stool\n",
"Taxon Patient \n",
"Proteobacteria 15 1307 53\n",
"Other 15 305 32\n",
"Firmicutes 15 281 2377\n",
"Bacteroidetes 15 102 33\n",
"Actinobacteria 15 310 204"
]
}
],
"prompt_number": 138
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Missing data\n",
"\n",
"The occurence of missing data is so prevalent that it pays to use tools like Pandas, which seamlessly integrates missing data handling so that it can be dealt with easily, and in the manner required by the analysis at hand.\n",
"\n",
"Missing data are represented in `Series` and `DataFrame` objects by the `NaN` floating point value. However, `None` is also treated as missing, since it is commonly used as such in other contexts (*e.g.* NumPy)."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"foo = pd.Series([NaN, -3, None, 'foobar'])\n",
"foo"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 139,
"text": [
"0 NaN\n",
"1 -3\n",
"2 None\n",
"3 foobar\n",
"dtype: object"
]
}
],
"prompt_number": 139
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"foo.isnull()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 140,
"text": [
"0 True\n",
"1 False\n",
"2 True\n",
"3 False\n",
"dtype: bool"
]
}
],
"prompt_number": 140
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Missing values may be dropped or indexed out:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 141,
"text": [
"phylum\n",
"Firmicutes NaN\n",
"Proteobacteria 632\n",
"Actinobacteria 1638\n",
"Bacteroidetes 569\n",
"dtype: float64"
]
}
],
"prompt_number": 141
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2.dropna()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 142,
"text": [
"phylum\n",
"Proteobacteria 632\n",
"Actinobacteria 1638\n",
"Bacteroidetes 569\n",
"dtype: float64"
]
}
],
"prompt_number": 142
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2[bacteria2.notnull()]"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 143,
"text": [
"phylum\n",
"Proteobacteria 632\n",
"Actinobacteria 1638\n",
"Bacteroidetes 569\n",
"dtype: float64"
]
}
],
"prompt_number": 143
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"By default, `dropna` drops entire rows in which one or more values are missing."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 144,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1\n",
"6 2 Actinobacteria 754 2013 NaN\n",
"7 2 Bacteroidetes 555 2013 NaN"
]
}
],
"prompt_number": 144
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.dropna()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 145,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1"
]
}
],
"prompt_number": 145
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This can be overridden by passing the `how='all'` argument, which only drops a row when every field is a missing value."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.dropna(how='all')"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 146,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1\n",
"6 2 Actinobacteria 754 2013 NaN\n",
"7 2 Bacteroidetes 555 2013 NaN"
]
}
],
"prompt_number": 146
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This can be customized further by specifying how many values need to be present before a row is dropped via the `thresh` argument."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.ix[7, 'year'] = nan\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 147,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1\n",
"6 2 Actinobacteria 754 2013 NaN\n",
"7 2 Bacteroidetes 555 NaN NaN"
]
}
],
"prompt_number": 147
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.dropna(thresh=4)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 148,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1\n",
"6 2 Actinobacteria 754 2013 NaN"
]
}
],
"prompt_number": 148
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This is typically used in time series applications, where there are repeated measurements that are incomplete for some subjects."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If we want to drop missing values column-wise instead of row-wise, we use `axis=1`."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.dropna(axis=1)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 149,
"text": [
" patient phylum value\n",
"0 1 Firmicutes 632\n",
"1 1 Proteobacteria 1638\n",
"2 1 Actinobacteria 569\n",
"3 1 Bacteroidetes 14\n",
"4 2 Firmicutes 433\n",
"5 2 Proteobacteria 0\n",
"6 2 Actinobacteria 754\n",
"7 2 Bacteroidetes 555"
]
}
],
"prompt_number": 149
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Rather than omitting missing data from an analysis, in some cases it may be suitable to fill the missing value in, either with a default value (such as zero) or a value that is either imputed or carried forward/backward from similar data points. We can do this programmatically in Pandas with the `fillna` argument."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2.fillna(0)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 150,
"text": [
"phylum\n",
"Firmicutes 0\n",
"Proteobacteria 632\n",
"Actinobacteria 1638\n",
"Bacteroidetes 569\n",
"dtype: float64"
]
}
],
"prompt_number": 150
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.fillna({'year': 2013, 'treatment':2})"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 151,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1\n",
"6 2 Actinobacteria 754 2013 2\n",
"7 2 Bacteroidetes 555 2013 2"
]
}
],
"prompt_number": 151
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice that `fillna` by default returns a new object with the desired filling behavior, rather than changing the `Series` or `DataFrame` in place (**in general, we like to do this, by the way!**)."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 152,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1\n",
"6 2 Actinobacteria 754 2013 NaN\n",
"7 2 Bacteroidetes 555 NaN NaN"
]
}
],
"prompt_number": 152
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can alter values in-place using `inplace=True`."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"_ = data.year.fillna(2013, inplace=True)\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 153,
"text": [
" patient phylum value year treatment\n",
"0 1 Firmicutes 632 2013 0\n",
"1 1 Proteobacteria 1638 2013 0\n",
"2 1 Actinobacteria 569 2013 0\n",
"3 1 Bacteroidetes 14 2013 0\n",
"4 2 Firmicutes 433 2013 1\n",
"5 2 Proteobacteria 0 2013 1\n",
"6 2 Actinobacteria 754 2013 NaN\n",
"7 2 Bacteroidetes 555 2013 NaN"
]
}
],
"prompt_number": 153
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Missing values can also be interpolated, using any one of a variety of methods:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2.fillna(method='bfill')"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 154,
"text": [
"phylum\n",
"Firmicutes 632\n",
"Proteobacteria 632\n",
"Actinobacteria 1638\n",
"Bacteroidetes 569\n",
"dtype: float64"
]
}
],
"prompt_number": 154
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2.fillna(bacteria2.mean())"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 155,
"text": [
"phylum\n",
"Firmicutes 946.333333\n",
"Proteobacteria 632.000000\n",
"Actinobacteria 1638.000000\n",
"Bacteroidetes 569.000000\n",
"dtype: float64"
]
}
],
"prompt_number": 155
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Data summarization\n",
"\n",
"We often wish to summarize data in `Series` or `DataFrame` objects, so that they can more easily be understood or compared with similar data. The NumPy package contains several functions that are useful here, but several summarization or reduction methods are built into Pandas data structures."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.sum()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 156,
"text": [
"player womacto01schilcu01myersmi01helliri01johnsra05f...\n",
"year 200692\n",
"stint 113\n",
"team CHNBOSNYAMILNYASFNARILANATLNYNTORTBAHOUARIATLM...\n",
"lg NLALALNLALNLNLNLNLNLALALNLNLNLALNLNLNLNLALALNL...\n",
"g 5238\n",
"ab 13654\n",
"r 1869\n",
"...\n",
"cs 46\n",
"bb 1549\n",
"so 2408\n",
"ibb 177\n",
"hbp 112\n",
"sh 138\n",
"sf 120\n",
"gidp 354\n",
"Length: 22, dtype: object"
]
}
],
"prompt_number": 156
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Clearly, `sum` is more meaningful for some columns than others. For methods like `mean` for which application to string variables is not just meaningless, but impossible, these columns are automatically exculded:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.mean()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 157,
"text": [
"year 2006.92\n",
"stint 1.13\n",
"g 52.38\n",
"ab 136.54\n",
"r 18.69\n",
"h 35.82\n",
"X2b 7.39\n",
"X3b 0.55\n",
"hr 4.37\n",
"rbi 18.47\n",
"sb 1.38\n",
"cs 0.46\n",
"bb 15.49\n",
"so 24.08\n",
"ibb 1.77\n",
"hbp 1.12\n",
"sh 1.38\n",
"sf 1.20\n",
"gidp 3.54\n",
"dtype: float64"
]
}
],
"prompt_number": 157
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The important difference between NumPy's functions and Pandas' methods is that the latter have built-in support for handling missing data."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 158,
"text": [
"phylum\n",
"Firmicutes NaN\n",
"Proteobacteria 632\n",
"Actinobacteria 1638\n",
"Bacteroidetes 569\n",
"dtype: float64"
]
}
],
"prompt_number": 158
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2.mean()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 159,
"text": [
"946.33333333333337"
]
}
],
"prompt_number": 159
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Sometimes we may not want to ignore missing values, and allow the `nan` to propagate."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bacteria2.mean(skipna=False)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 160,
"text": [
"nan"
]
}
],
"prompt_number": 160
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Passing `axis=1` will summarize over rows instead of columns, which only makes sense in certain situations."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"extra_bases = baseball[['X2b','X3b','hr']].sum(axis=1)\n",
"extra_bases.order(ascending=False)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 161,
"text": [
"id\n",
"88653 69\n",
"89439 57\n",
"89361 56\n",
"89462 55\n",
"89489 54\n",
"89396 54\n",
"89360 54\n",
"89371 50\n",
"...\n",
"89338 0\n",
"89336 0\n",
"89335 0\n",
"89333 0\n",
"88650 0\n",
"88649 0\n",
"88645 0\n",
"88643 0\n",
"Length: 100, dtype: int64"
]
}
],
"prompt_number": 161
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A useful summarization that gives a quick snapshot of multiple statistics for a `Series` or `DataFrame` is `describe`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.describe()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 162,
"text": [
" year stint g ab r h \\\n",
"count 100.00000 100.000000 100.000000 100.000000 100.00000 100.000000 \n",
"mean 2006.92000 1.130000 52.380000 136.540000 18.69000 35.820000 \n",
"std 0.27266 0.337998 48.031299 181.936853 27.77496 50.221807 \n",
"min 2006.00000 1.000000 1.000000 0.000000 0.00000 0.000000 \n",
"25% 2007.00000 1.000000 9.500000 2.000000 0.00000 0.000000 \n",
"50% 2007.00000 1.000000 33.000000 40.500000 2.00000 8.000000 \n",
"75% 2007.00000 1.000000 83.250000 243.750000 33.25000 62.750000 \n",
"max 2007.00000 2.000000 155.000000 586.000000 107.00000 159.000000 \n",
"\n",
" X2b X3b hr rbi sb cs \\\n",
"count 100.000000 100.000000 100.000000 100.00000 100.000000 100.000000 \n",
"mean 7.390000 0.550000 4.370000 18.47000 1.380000 0.460000 \n",
"std 11.117277 1.445124 7.975537 28.34793 3.694878 1.067613 \n",
"min 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 \n",
"25% 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 \n",
"50% 1.000000 0.000000 0.000000 2.00000 0.000000 0.000000 \n",
"75% 11.750000 1.000000 6.000000 27.00000 1.000000 0.000000 \n",
"max 52.000000 12.000000 35.000000 96.00000 22.000000 6.000000 \n",
"\n",
" bb so ibb hbp sh sf \\\n",
"count 100.000000 100.000000 100.000000 100.00000 100.000000 100.000000 \n",
"mean 15.490000 24.080000 1.770000 1.12000 1.380000 1.200000 \n",
"std 25.812649 32.804496 5.042957 2.23055 2.919042 2.035046 \n",
"min 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 \n",
"25% 0.000000 1.000000 0.000000 0.00000 0.000000 0.000000 \n",
"50% 1.000000 7.000000 0.000000 0.00000 0.000000 0.000000 \n",
"75% 19.250000 37.250000 1.250000 1.00000 1.000000 2.000000 \n",
"max 132.000000 134.000000 43.000000 11.00000 14.000000 9.000000 \n",
"\n",
" gidp \n",
"count 100.000000 \n",
"mean 3.540000 \n",
"std 5.201826 \n",
"min 0.000000 \n",
"25% 0.000000 \n",
"50% 1.000000 \n",
"75% 6.000000 \n",
"max 21.000000 "
]
}
],
"prompt_number": 162
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"`describe` can detect non-numeric data and sometimes yield useful information about it."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.player.describe()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 163,
"text": [
"count 100\n",
"unique 82\n",
"top wickmbo01\n",
"freq 2\n",
"dtype: object"
]
}
],
"prompt_number": 163
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can also calculate summary statistics *across* multiple columns, for example, correlation and covariance.\n",
"\n",
"$$cov(x,y) = \\sum_i (x_i - \\bar{x})(y_i - \\bar{y})$$"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.hr.cov(baseball.X2b)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 164,
"text": [
"69.076464646464643"
]
}
],
"prompt_number": 164
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"$$corr(x,y) = \\frac{cov(x,y)}{(n-1)s_x s_y} = \\frac{\\sum_i (x_i - \\bar{x})(y_i - \\bar{y})}{\\sqrt{\\sum_i (x_i - \\bar{x})^2 \\sum_i (y_i - \\bar{y})^2}}$$"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.hr.corr(baseball.X2b)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 165,
"text": [
"0.77906151825397518"
]
}
],
"prompt_number": 165
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.ab.corr(baseball.h)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 166,
"text": [
"0.99421740362723776"
]
}
],
"prompt_number": 166
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.corr()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 167,
"text": [
" year stint g ab r h X2b \\\n",
"year 1.000000 0.004384 -0.050874 -0.001360 -0.023315 0.001151 -0.052917 \n",
"stint 0.004384 1.000000 -0.257552 -0.216333 -0.209781 -0.206878 -0.196423 \n",
"g -0.050874 -0.257552 1.000000 0.935910 0.910262 0.929292 0.885847 \n",
"ab -0.001360 -0.216333 0.935910 1.000000 0.965609 0.994217 0.952249 \n",
"r -0.023315 -0.209781 0.910262 0.965609 1.000000 0.970560 0.923508 \n",
"h 0.001151 -0.206878 0.929292 0.994217 0.970560 1.000000 0.957275 \n",
"X2b -0.052917 -0.196423 0.885847 0.952249 0.923508 0.957275 1.000000 \n",
"X3b -0.246099 -0.085821 0.518663 0.535986 0.500807 0.514245 0.493267 \n",
"hr 0.060199 -0.209124 0.802014 0.843308 0.890060 0.855163 0.779062 \n",
"rbi 0.042812 -0.205688 0.891563 0.947911 0.941483 0.952320 0.901751 \n",
"sb 0.030480 -0.120837 0.492362 0.533536 0.596343 0.530018 0.413655 \n",
"cs 0.058296 -0.055425 0.520923 0.577192 0.576454 0.571629 0.477487 \n",
"bb 0.005626 -0.190301 0.828572 0.850803 0.915010 0.853384 0.780012 \n",
"so 0.069610 -0.214121 0.866499 0.923926 0.879375 0.906966 0.862149 \n",
"ibb 0.015868 -0.118580 0.514423 0.506398 0.588882 0.513009 0.453301 \n",
"hbp -0.000664 -0.195074 0.730161 0.767210 0.806523 0.767449 0.738226 \n",
"sh -0.012184 -0.091527 0.079361 0.094537 -0.001273 0.045533 0.005659 \n",
"sf -0.007282 -0.155662 0.767543 0.840361 0.839592 0.839737 0.819361 \n",
"gidp 0.052131 -0.224173 0.863041 0.926632 0.894724 0.935525 0.906860 \n",
"\n",
" X3b hr rbi sb cs bb so \\\n",
"year -0.246099 0.060199 0.042812 0.030480 0.058296 0.005626 0.069610 \n",
"stint -0.085821 -0.209124 -0.205688 -0.120837 -0.055425 -0.190301 -0.214121 \n",
"g 0.518663 0.802014 0.891563 0.492362 0.520923 0.828572 0.866499 \n",
"ab 0.535986 0.843308 0.947911 0.533536 0.577192 0.850803 0.923926 \n",
"r 0.500807 0.890060 0.941483 0.596343 0.576454 0.915010 0.879375 \n",
"h 0.514245 0.855163 0.952320 0.530018 0.571629 0.853384 0.906966 \n",
"X2b 0.493267 0.779062 0.901751 0.413655 0.477487 0.780012 0.862149 \n",
"X3b 1.000000 0.210028 0.369890 0.450421 0.384312 0.350682 0.408800 \n",
"hr 0.210028 1.000000 0.948787 0.364346 0.345187 0.916774 0.865929 \n",
"rbi 0.369890 0.948787 1.000000 0.394633 0.435011 0.893945 0.929410 \n",
"sb 0.450421 0.364346 0.394633 1.000000 0.743921 0.491351 0.365841 \n",
"cs 0.384312 0.345187 0.435011 0.743921 1.000000 0.425352 0.426658 \n",
"bb 0.350682 0.916774 0.893945 0.491351 0.425352 1.000000 0.795751 \n",
"so 0.408800 0.865929 0.929410 0.365841 0.426658 0.795751 1.000000 \n",
"ibb 0.090993 0.673691 0.582982 0.209110 0.106152 0.792057 0.476613 \n",
"hbp 0.217474 0.767411 0.780899 0.413570 0.337129 0.742118 0.742547 \n",
"sh 0.187012 -0.145374 -0.054670 0.171910 0.293397 -0.044992 0.073624 \n",
"sf 0.394987 0.782038 0.855260 0.412947 0.422146 0.760932 0.782314 \n",
"gidp 0.411577 0.798350 0.906908 0.431198 0.456820 0.823631 0.846629 \n",
"\n",
" ibb hbp sh sf gidp \n",
"year 0.015868 -0.000664 -0.012184 -0.007282 0.052131 \n",
"stint -0.118580 -0.195074 -0.091527 -0.155662 -0.224173 \n",
"g 0.514423 0.730161 0.079361 0.767543 0.863041 \n",
"ab 0.506398 0.767210 0.094537 0.840361 0.926632 \n",
"r 0.588882 0.806523 -0.001273 0.839592 0.894724 \n",
"h 0.513009 0.767449 0.045533 0.839737 0.935525 \n",
"X2b 0.453301 0.738226 0.005659 0.819361 0.906860 \n",
"X3b 0.090993 0.217474 0.187012 0.394987 0.411577 \n",
"hr 0.673691 0.767411 -0.145374 0.782038 0.798350 \n",
"rbi 0.582982 0.780899 -0.054670 0.855260 0.906908 \n",
"sb 0.209110 0.413570 0.171910 0.412947 0.431198 \n",
"cs 0.106152 0.337129 0.293397 0.422146 0.456820 \n",
"bb 0.792057 0.742118 -0.044992 0.760932 0.823631 \n",
"so 0.476613 0.742547 0.073624 0.782314 0.846629 \n",
"ibb 1.000000 0.431714 -0.075658 0.451377 0.572355 \n",
"hbp 0.431714 1.000000 -0.104810 0.648882 0.700380 \n",
"sh -0.075658 -0.104810 1.000000 -0.002721 0.028924 \n",
"sf 0.451377 0.648882 -0.002721 1.000000 0.785489 \n",
"gidp 0.572355 0.700380 0.028924 0.785489 1.000000 "
]
}
],
"prompt_number": 167
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If we have a `DataFrame` with a hierarchical index (or indices), summary statistics can be applied with respect to any of the index levels:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb.head()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 168,
"text": [
" Tissue Stool\n",
"Taxon Patient \n",
"Firmicutes 1 632 305\n",
" 2 136 4182\n",
" 3 1174 703\n",
" 4 408 3946\n",
" 5 831 8605"
]
}
],
"prompt_number": 168
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb.sum(level='Taxon')"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 169,
"text": [
" Tissue Stool\n",
"Taxon \n",
"Actinobacteria 6736 2263\n",
"Bacteroidetes 8995 4656\n",
"Firmicutes 10266 30477\n",
"Other 2982 519\n",
"Proteobacteria 44146 16369"
]
}
],
"prompt_number": 169
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Writing Data to Files\n",
"\n",
"As well as being able to read several data input formats, Pandas can also export data to a variety of storage formats. We will bring your attention to just a couple of these."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mb.to_csv(\"mb.csv\")"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 170
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The `to_csv` method writes a `DataFrame` to a comma-separated values (csv) file. You can specify custom delimiters (via `sep` argument), how missing values are written (via `na_rep` argument), whether the index is writen (via `index` argument), whether the header is included (via `header` argument), among other options."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"An efficient way of storing data to disk is in binary format. Pandas supports this using Python\u2019s built-in pickle serialization."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"baseball.to_pickle(\"baseball_pickle\")"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 171
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The complement to `to_pickle` is the `read_pickle` function, which restores the pickle to a `DataFrame` or `Series`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pd.read_pickle(\"baseball_pickle\")"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 172,
"text": [
"<class 'pandas.core.frame.DataFrame'>\n",
"Int64Index: 100 entries, 88641 to 89534\n",
"Data columns (total 22 columns):\n",
"player 100 non-null values\n",
"year 100 non-null values\n",
"stint 100 non-null values\n",
"team 100 non-null values\n",
"lg 100 non-null values\n",
"g 100 non-null values\n",
"ab 100 non-null values\n",
"r 100 non-null values\n",
"h 100 non-null values\n",
"X2b 100 non-null values\n",
"X3b 100 non-null values\n",
"hr 100 non-null values\n",
"rbi 100 non-null values\n",
"sb 100 non-null values\n",
"cs 100 non-null values\n",
"bb 100 non-null values\n",
"so 100 non-null values\n",
"ibb 100 non-null values\n",
"hbp 100 non-null values\n",
"sh 100 non-null values\n",
"sf 100 non-null values\n",
"gidp 100 non-null values\n",
"dtypes: float64(9), int64(10), object(3)"
]
}
],
"prompt_number": 172
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"As Wes warns in his book, it is recommended that binary storage of data via pickle only be used as a temporary storage format, in situations where speed is relevant. This is because there is no guarantee that the pickle format will not change with future versions of Python."
]
}
],
"metadata": {}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment