Introduction to Pandas

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.

pandas is well suited for:

  • Tabular data with heterogeneously-typed columns, as in an SQL table or Excel spreadsheet
  • Ordered and unordered (not necessarily fixed-frequency) time series data.
  • Arbitrary matrix data (homogeneously typed or heterogeneous) with row and column labels
  • 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

Key features:

  • Easy handling of missing data
  • Size mutability: columns can be inserted and deleted from DataFrame and higher dimensional objects
  • Automatic and explicit data alignment: objects can be explicitly aligned to a set of labels, or the data can be aligned automatically
  • Powerful, flexible group by functionality to perform split-apply-combine operations on data sets
  • Intelligent label-based slicing, fancy indexing, and subsetting of large data sets
  • Intuitive merging and joining data sets
  • Flexible reshaping and pivoting of data sets
  • Hierarchical labeling of axes
  • Robust IO tools for loading data from flat files, Excel files, databases, and HDF5
  • Time series functionality: date range generation and frequency conversion, moving window statistics, moving window linear regressions, date shifting and lagging, etc.

In [1]:
from IPython.core.display import HTML
HTML("<iframe src=http://pandas.pydata.org width=800 height=350></iframe>")


Out[1]:

In [2]:
%matplotlib inline
import pandas as pd
import numpy as np

# Set some Pandas options
pd.set_option('html', False)
pd.set_option('max_columns', 30)
pd.set_option('max_rows', 20)

Pandas Data Structures

Series

A Series is a single vector of data (like a NumPy array) with an index that labels each element in the vector.


In [3]:
counts = pd.Series([632, 1638, 569, 115])
counts


Out[3]:
0     632
1    1638
2     569
3     115
dtype: int64

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.


In [4]:
counts.values


Out[4]:
array([ 632, 1638,  569,  115])

In [5]:
counts.index


Out[5]:
Int64Index([0, 1, 2, 3], dtype='int64')

We can assign meaningful labels to the index, if they are available:


In [6]:
bacteria = pd.Series([632, 1638, 569, 115], 
    index=['Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes'])

bacteria


Out[6]:
Firmicutes         632
Proteobacteria    1638
Actinobacteria     569
Bacteroidetes      115
dtype: int64

These labels can be used to refer to the values in the Series.


In [7]:
bacteria['Actinobacteria']


Out[7]:
569

In [8]:
bacteria[[name.endswith('bacteria') for name in bacteria.index]]


Out[8]:
Proteobacteria    1638
Actinobacteria     569
dtype: int64

In [9]:
[name.endswith('bacteria') for name in bacteria.index]


Out[9]:
[False, True, True, False]

Notice that the indexing operation preserved the association between the values and the corresponding indices.

We can still use positional indexing if we wish.


In [10]:
bacteria[0]


Out[10]:
632

We can give both the array of values and the index meaningful labels themselves:


In [11]:
bacteria.name = 'counts'
bacteria.index.name = 'phylum'
bacteria


Out[11]:
phylum
Firmicutes         632
Proteobacteria    1638
Actinobacteria     569
Bacteroidetes      115
Name: counts, dtype: int64

NumPy's math functions and other operations can be applied to Series without losing the data structure.


In [12]:
np.log(bacteria)


Out[12]:
phylum
Firmicutes        6.448889
Proteobacteria    7.401231
Actinobacteria    6.343880
Bacteroidetes     4.744932
Name: counts, dtype: float64

We can also filter according to the values in the Series:


In [13]:
bacteria[bacteria>1000]


Out[13]:
phylum
Proteobacteria    1638
Name: counts, dtype: int64

A Series can be thought of as an ordered key-value store. In fact, we can create one from a dict:


In [14]:
bacteria_dict = {'Firmicutes': 632, 'Proteobacteria': 1638, 'Actinobacteria': 569, 'Bacteroidetes': 115}
pd.Series(bacteria_dict)


Out[14]:
Actinobacteria     569
Bacteroidetes      115
Firmicutes         632
Proteobacteria    1638
dtype: int64

Notice that the Series is created in key-sorted order.

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.


In [15]:
bacteria2 = pd.Series(bacteria_dict, index=['Cyanobacteria','Firmicutes','Proteobacteria','Actinobacteria'])
bacteria2


Out[15]:
Cyanobacteria      NaN
Firmicutes         632
Proteobacteria    1638
Actinobacteria     569
dtype: float64

In [16]:
bacteria2.isnull()


Out[16]:
Cyanobacteria      True
Firmicutes        False
Proteobacteria    False
Actinobacteria    False
dtype: bool

Critically, the labels are used to align data when used in operations with other Series objects:


In [17]:
bacteria + bacteria2


Out[17]:
Actinobacteria    1138
Bacteroidetes      NaN
Cyanobacteria      NaN
Firmicutes        1264
Proteobacteria    3276
dtype: float64

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.

DataFrame

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.

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.


In [18]:
data = pd.DataFrame({'value':[632, 1638, 569, 115, 433, 1130, 754, 555],
                     'patient':[1, 1, 1, 1, 2, 2, 2, 2],
                     'phylum':['Firmicutes', 'Proteobacteria', 'Actinobacteria', 
    'Bacteroidetes', 'Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes']})
data


Out[18]:
   patient          phylum  value
0        1      Firmicutes    632
1        1  Proteobacteria   1638
2        1  Actinobacteria    569
3        1   Bacteroidetes    115
4        2      Firmicutes    433
5        2  Proteobacteria   1130
6        2  Actinobacteria    754
7        2   Bacteroidetes    555

Notice the DataFrame is sorted by column name. We can change the order by indexing them in the order we desire:


In [19]:
data[['phylum','value','patient']]


Out[19]:
           phylum  value  patient
0      Firmicutes    632        1
1  Proteobacteria   1638        1
2  Actinobacteria    569        1
3   Bacteroidetes    115        1
4      Firmicutes    433        2
5  Proteobacteria   1130        2
6  Actinobacteria    754        2
7   Bacteroidetes    555        2

A DataFrame has a second index, representing the columns:


In [20]:
data.columns


Out[20]:
Index([u'patient', u'phylum', u'value'], dtype='object')

If we wish to access columns, we can do so either by dict-like indexing or by attribute:


In [21]:
data['value']


Out[21]:
0     632
1    1638
2     569
3     115
4     433
5    1130
6     754
7     555
Name: value, dtype: int64

In [22]:
data.value


Out[22]:
0     632
1    1638
2     569
3     115
4     433
5    1130
6     754
7     555
Name: value, dtype: int64

In [23]:
type(data.value)


Out[23]:
pandas.core.series.Series

In [24]:
type(data[['value']])


Out[24]:
pandas.core.frame.DataFrame

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.


In [25]:
data.ix[3]


Out[25]:
patient                1
phylum     Bacteroidetes
value                115
Name: 3, dtype: object

Alternatively, we can create a DataFrame with a dict of dicts:


In [26]:
data = pd.DataFrame({0: {'patient': 1, 'phylum': 'Firmicutes', 'value': 632},
                    1: {'patient': 1, 'phylum': 'Proteobacteria', 'value': 1638},
                    2: {'patient': 1, 'phylum': 'Actinobacteria', 'value': 569},
                    3: {'patient': 1, 'phylum': 'Bacteroidetes', 'value': 115},
                    4: {'patient': 2, 'phylum': 'Firmicutes', 'value': 433},
                    5: {'patient': 2, 'phylum': 'Proteobacteria', 'value': 1130},
                    6: {'patient': 2, 'phylum': 'Actinobacteria', 'value': 754},
                    7: {'patient': 2, 'phylum': 'Bacteroidetes', 'value': 555}})

In [27]:
data


Out[27]:
                  0               1               2              3  \
patient           1               1               1              1   
phylum   Firmicutes  Proteobacteria  Actinobacteria  Bacteroidetes   
value           632            1638             569            115   

                  4               5               6              7  
patient           2               2               2              2  
phylum   Firmicutes  Proteobacteria  Actinobacteria  Bacteroidetes  
value           433            1130             754            555  

We probably want this transposed:


In [28]:
data = data.T
data


Out[28]:
  patient          phylum value
0       1      Firmicutes   632
1       1  Proteobacteria  1638
2       1  Actinobacteria   569
3       1   Bacteroidetes   115
4       2      Firmicutes   433
5       2  Proteobacteria  1130
6       2  Actinobacteria   754
7       2   Bacteroidetes   555

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:


In [29]:
vals = data.value
vals


Out[29]:
0     632
1    1638
2     569
3     115
4     433
5    1130
6     754
7     555
Name: value, dtype: object

In [30]:
vals[5] = 0
vals


Out[30]:
0     632
1    1638
2     569
3     115
4     433
5       0
6     754
7     555
Name: value, dtype: object

In [31]:
data


Out[31]:
  patient          phylum value
0       1      Firmicutes   632
1       1  Proteobacteria  1638
2       1  Actinobacteria   569
3       1   Bacteroidetes   115
4       2      Firmicutes   433
5       2  Proteobacteria     0
6       2  Actinobacteria   754
7       2   Bacteroidetes   555

In [32]:
vals = data.value.copy()
vals[5] = 1000
data


Out[32]:
  patient          phylum value
0       1      Firmicutes   632
1       1  Proteobacteria  1638
2       1  Actinobacteria   569
3       1   Bacteroidetes   115
4       2      Firmicutes   433
5       2  Proteobacteria     0
6       2  Actinobacteria   754
7       2   Bacteroidetes   555

We can create or modify columns by assignment:


In [33]:
data.value[3] = 14
data


Out[33]:
  patient          phylum value
0       1      Firmicutes   632
1       1  Proteobacteria  1638
2       1  Actinobacteria   569
3       1   Bacteroidetes    14
4       2      Firmicutes   433
5       2  Proteobacteria     0
6       2  Actinobacteria   754
7       2   Bacteroidetes   555

In [34]:
data['year'] = 2013
data


Out[34]:
  patient          phylum value  year
0       1      Firmicutes   632  2013
1       1  Proteobacteria  1638  2013
2       1  Actinobacteria   569  2013
3       1   Bacteroidetes    14  2013
4       2      Firmicutes   433  2013
5       2  Proteobacteria     0  2013
6       2  Actinobacteria   754  2013
7       2   Bacteroidetes   555  2013

But note, we cannot use the attribute indexing method to add a new column:


In [35]:
data.treatment = 1
data


Out[35]:
  patient          phylum value  year
0       1      Firmicutes   632  2013
1       1  Proteobacteria  1638  2013
2       1  Actinobacteria   569  2013
3       1   Bacteroidetes    14  2013
4       2      Firmicutes   433  2013
5       2  Proteobacteria     0  2013
6       2  Actinobacteria   754  2013
7       2   Bacteroidetes   555  2013

In [36]:
data.treatment


Out[36]:
1

Specifying a Series as a new columns cause its values to be added according to the DataFrame's index:


In [37]:
treatment = pd.Series([0]*4 + [1]*2)
treatment


Out[37]:
0    0
1    0
2    0
3    0
4    1
5    1
dtype: int64

In [38]:
data['treatment'] = treatment
data


Out[38]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1
6       2  Actinobacteria   754  2013        NaN
7       2   Bacteroidetes   555  2013        NaN

Other Python data structures (ones without an index) need to be the same length as the DataFrame:


In [39]:
month = ['Jan', 'Feb', 'Mar', 'Apr']
data['month'] = month


---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-39-360d03fdde9a> in <module>()
      1 month = ['Jan', 'Feb', 'Mar', 'Apr']
----> 2 data['month'] = month

/usr/local/lib/python2.7/site-packages/pandas/core/frame.pyc in __setitem__(self, key, value)
   1899         else:
   1900             # set column
-> 1901             self._set_item(key, value)
   1902 
   1903     def _setitem_slice(self, key, value):

/usr/local/lib/python2.7/site-packages/pandas/core/frame.pyc in _set_item(self, key, value)
   1981         is_existing = key in self.columns
   1982         self._ensure_valid_index(value)
-> 1983         value = self._sanitize_column(key, value)
   1984         NDFrame._set_item(self, key, value)
   1985 

/usr/local/lib/python2.7/site-packages/pandas/core/frame.pyc in _sanitize_column(self, key, value)
   2031         elif isinstance(value, Index) or _is_sequence(value):
   2032             if len(value) != len(self.index):
-> 2033                 raise ValueError('Length of values does not match length of '
   2034                                  'index')
   2035 

ValueError: Length of values does not match length of index

In [40]:
data['month'] = ['Jan']*len(data)
data


Out[40]:
  patient          phylum value  year  treatment month
0       1      Firmicutes   632  2013          0   Jan
1       1  Proteobacteria  1638  2013          0   Jan
2       1  Actinobacteria   569  2013          0   Jan
3       1   Bacteroidetes    14  2013          0   Jan
4       2      Firmicutes   433  2013          1   Jan
5       2  Proteobacteria     0  2013          1   Jan
6       2  Actinobacteria   754  2013        NaN   Jan
7       2   Bacteroidetes   555  2013        NaN   Jan

We can use del to remove columns, in the same way dict entries can be removed:


In [41]:
del data['month']
data


Out[41]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1
6       2  Actinobacteria   754  2013        NaN
7       2   Bacteroidetes   555  2013        NaN

We can extract the underlying data as a simple ndarray by accessing the values attribute:


In [42]:
data.values


Out[42]:
array([[1, 'Firmicutes', 632, 2013, 0.0],
       [1, 'Proteobacteria', 1638, 2013, 0.0],
       [1, 'Actinobacteria', 569, 2013, 0.0],
       [1, 'Bacteroidetes', 14, 2013, 0.0],
       [2, 'Firmicutes', 433, 2013, 1.0],
       [2, 'Proteobacteria', 0, 2013, 1.0],
       [2, 'Actinobacteria', 754, 2013, nan],
       [2, 'Bacteroidetes', 555, 2013, nan]], dtype=object)

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.


In [43]:
df = pd.DataFrame({'foo': [1,2,3], 'bar':[0.4, -1.0, 4.5]})
df.values


Out[43]:
array([[ 0.4,  1. ],
       [-1. ,  2. ],
       [ 4.5,  3. ]])

Pandas uses a custom data structure to represent the indices of Series and DataFrames.


In [44]:
data.index


Out[44]:
Int64Index([0, 1, 2, 3, 4, 5, 6, 7], dtype='int64')

Index objects are immutable:


In [45]:
data.index[0] = 15


---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-45-42a852cc9eac> in <module>()
----> 1 data.index[0] = 15

/usr/local/lib/python2.7/site-packages/pandas/core/base.pyc in _disabled(self, *args, **kwargs)
    177         """This method will not function because object is immutable."""
    178         raise TypeError("'%s' does not support mutable operations." %
--> 179                         self.__class__)
    180 
    181     __setitem__ = __setslice__ = __delitem__ = __delslice__ = _disabled

TypeError: '<class 'pandas.core.index.Int64Index'>' does not support mutable operations.

This is so that Index objects can be shared between data structures without fear that they will be changed.


In [46]:
bacteria2.index = bacteria.index

In [47]:
bacteria2


Out[47]:
phylum
Firmicutes         NaN
Proteobacteria     632
Actinobacteria    1638
Bacteroidetes      569
dtype: float64

Importing data

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:

genes = np.loadtxt("genes.csv", delimiter=",", dtype=[('gene', '|S10'), ('value', '<f4')])

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.

Let's start with some more bacteria data, stored in csv format.


In [48]:
!cat data/microbiome.csv


Taxon,Patient,Tissue,Stool
Firmicutes,1,632,305
Firmicutes,2,136,4182
Firmicutes,3,1174,703
Firmicutes,4,408,3946
Firmicutes,5,831,8605
Firmicutes,6,693,50
Firmicutes,7,718,717
Firmicutes,8,173,33
Firmicutes,9,228,80
Firmicutes,10,162,3196
Firmicutes,11,372,32
Firmicutes,12,4255,4361
Firmicutes,13,107,1667
Firmicutes,14,96,223
Firmicutes,15,281,2377
Proteobacteria,1,1638,3886
Proteobacteria,2,2469,1821
Proteobacteria,3,839,661
Proteobacteria,4,4414,18
Proteobacteria,5,12044,83
Proteobacteria,6,2310,12
Proteobacteria,7,3053,547
Proteobacteria,8,395,2174
Proteobacteria,9,2651,767
Proteobacteria,10,1195,76
Proteobacteria,11,6857,795
Proteobacteria,12,483,666
Proteobacteria,13,2950,3994
Proteobacteria,14,1541,816
Proteobacteria,15,1307,53
Actinobacteria,1,569,648
Actinobacteria,2,1590,4
Actinobacteria,3,25,2
Actinobacteria,4,259,300
Actinobacteria,5,568,7
Actinobacteria,6,1102,9
Actinobacteria,7,678,377
Actinobacteria,8,260,58
Actinobacteria,9,424,233
Actinobacteria,10,548,21
Actinobacteria,11,201,83
Actinobacteria,12,42,75
Actinobacteria,13,109,59
Actinobacteria,14,51,183
Actinobacteria,15,310,204
Bacteroidetes,1,115,380
Bacteroidetes,2,67,0
Bacteroidetes,3,0,0
Bacteroidetes,4,85,5
Bacteroidetes,5,143,7
Bacteroidetes,6,678,2
Bacteroidetes,7,4829,209
Bacteroidetes,8,74,651
Bacteroidetes,9,169,254
Bacteroidetes,10,106,10
Bacteroidetes,11,73,381
Bacteroidetes,12,30,359
Bacteroidetes,13,51,51
Bacteroidetes,14,2473,2314
Bacteroidetes,15,102,33
Other,1,114,277
Other,2,195,18
Other,3,42,2
Other,4,316,43
Other,5,202,40
Other,6,116,0
Other,7,527,12
Other,8,357,11
Other,9,106,11
Other,10,67,14
Other,11,203,6
Other,12,392,6
Other,13,28,25
Other,14,12,22
Other,15,305,32

This table can be read into a DataFrame using read_csv:


In [49]:
mb = pd.read_csv("data/microbiome.csv")
mb


Out[49]:
         Taxon  Patient  Tissue  Stool
0   Firmicutes        1     632    305
1   Firmicutes        2     136   4182
2   Firmicutes        3    1174    703
3   Firmicutes        4     408   3946
4   Firmicutes        5     831   8605
5   Firmicutes        6     693     50
6   Firmicutes        7     718    717
7   Firmicutes        8     173     33
8   Firmicutes        9     228     80
9   Firmicutes       10     162   3196
..         ...      ...     ...    ...
65       Other        6     116      0
66       Other        7     527     12
67       Other        8     357     11
68       Other        9     106     11
69       Other       10      67     14
70       Other       11     203      6
71       Other       12     392      6
72       Other       13      28     25
73       Other       14      12     22
74       Other       15     305     32

[75 rows x 4 columns]

Notice that read_csv automatically considered the first row in the file to be a header row.

We can override default behavior by customizing some the arguments, like header, names or index_col.


In [50]:
pd.read_csv("data/microbiome.csv", header=None).head()


Out[50]:
            0        1       2      3
0       Taxon  Patient  Tissue  Stool
1  Firmicutes        1     632    305
2  Firmicutes        2     136   4182
3  Firmicutes        3    1174    703
4  Firmicutes        4     408   3946

read_csv is just a convenience function for read_table, since csv is such a common format:


In [51]:
mb = pd.read_table("data/microbiome.csv", sep=',')

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:

sep='\s+'

For a more useful index, we can specify the first two columns, which together provide a unique index to the data.


In [52]:
mb = pd.read_csv("data/microbiome.csv", index_col=['Taxon','Patient'])
mb.head()


Out[52]:
                    Tissue  Stool
Taxon      Patient               
Firmicutes 1           632    305
           2           136   4182
           3          1174    703
           4           408   3946
           5           831   8605

This is called a hierarchical index, which we will revisit later in the tutorial.

If we have sections of data that we do not wish to import (for example, known bad data), we can populate the skiprows argument:


In [53]:
pd.read_csv("data/microbiome.csv", skiprows=[3,4,6]).head()


Out[53]:
        Taxon  Patient  Tissue  Stool
0  Firmicutes        1     632    305
1  Firmicutes        2     136   4182
2  Firmicutes        5     831   8605
3  Firmicutes        7     718    717
4  Firmicutes        8     173     33

Conversely, if we only want to import a small number of rows from, say, a very large data file we can use nrows:


In [54]:
pd.read_csv("data/microbiome.csv", nrows=4)


Out[54]:
        Taxon  Patient  Tissue  Stool
0  Firmicutes        1     632    305
1  Firmicutes        2     136   4182
2  Firmicutes        3    1174    703
3  Firmicutes        4     408   3946

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:


In [55]:
data_chunks = pd.read_csv("data/microbiome.csv", chunksize=15)

mean_tissue = {chunk.Taxon[0]:chunk.Tissue.mean() for chunk in data_chunks}
    
mean_tissue


Out[55]:
{'Actinobacteria': 449.06666666666666,
 'Bacteroidetes': 599.66666666666663,
 'Firmicutes': 684.39999999999998,
 'Other': 198.80000000000001,
 'Proteobacteria': 2943.0666666666666}

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.


In [56]:
!cat data/microbiome_missing.csv


Taxon,Patient,Tissue,Stool
Firmicutes,1,632,305
Firmicutes,2,136,4182
Firmicutes,3,,703
Firmicutes,4,408,3946
Firmicutes,5,831,8605
Firmicutes,6,693,50
Firmicutes,7,718,717
Firmicutes,8,173,33
Firmicutes,9,228,NA
Firmicutes,10,162,3196
Firmicutes,11,372,-99999
Firmicutes,12,4255,4361
Firmicutes,13,107,1667
Firmicutes,14,?,223
Firmicutes,15,281,2377
Proteobacteria,1,1638,3886
Proteobacteria,2,2469,1821
Proteobacteria,3,839,661
Proteobacteria,4,4414,18
Proteobacteria,5,12044,83
Proteobacteria,6,2310,12
Proteobacteria,7,3053,547
Proteobacteria,8,395,2174
Proteobacteria,9,2651,767
Proteobacteria,10,1195,76
Proteobacteria,11,6857,795
Proteobacteria,12,483,666
Proteobacteria,13,2950,3994
Proteobacteria,14,1541,816
Proteobacteria,15,1307,53
Actinobacteria,1,569,648
Actinobacteria,2,1590,4
Actinobacteria,3,25,2
Actinobacteria,4,259,300
Actinobacteria,5,568,7
Actinobacteria,6,1102,9
Actinobacteria,7,678,377
Actinobacteria,8,260,58
Actinobacteria,9,424,233
Actinobacteria,10,548,21
Actinobacteria,11,201,83
Actinobacteria,12,42,75
Actinobacteria,13,109,59
Actinobacteria,14,51,183
Actinobacteria,15,310,204
Bacteroidetes,1,115,380
Bacteroidetes,2,67,0
Bacteroidetes,3,0,0
Bacteroidetes,4,85,5
Bacteroidetes,5,143,7
Bacteroidetes,6,678,2
Bacteroidetes,7,4829,209
Bacteroidetes,8,74,651
Bacteroidetes,9,169,254
Bacteroidetes,10,106,10
Bacteroidetes,11,73,381
Bacteroidetes,12,30,359
Bacteroidetes,13,51,51
Bacteroidetes,14,2473,2314
Bacteroidetes,15,102,33
Other,1,114,277
Other,2,195,18
Other,3,42,2
Other,4,316,43
Other,5,202,40
Other,6,116,0
Other,7,527,12
Other,8,357,11
Other,9,106,11
Other,10,67,14
Other,11,203,6
Other,12,392,6
Other,13,28,25
Other,14,12,22
Other,15,305,32

In [57]:
pd.read_csv("data/microbiome_missing.csv").head(20)


Out[57]:
             Taxon  Patient Tissue  Stool
0       Firmicutes        1    632    305
1       Firmicutes        2    136   4182
2       Firmicutes        3    NaN    703
3       Firmicutes        4    408   3946
4       Firmicutes        5    831   8605
5       Firmicutes        6    693     50
6       Firmicutes        7    718    717
7       Firmicutes        8    173     33
8       Firmicutes        9    228    NaN
9       Firmicutes       10    162   3196
10      Firmicutes       11    372 -99999
11      Firmicutes       12   4255   4361
12      Firmicutes       13    107   1667
13      Firmicutes       14      ?    223
14      Firmicutes       15    281   2377
15  Proteobacteria        1   1638   3886
16  Proteobacteria        2   2469   1821
17  Proteobacteria        3    839    661
18  Proteobacteria        4   4414     18
19  Proteobacteria        5  12044     83

Above, Pandas recognized NA and an empty field as missing data.


In [58]:
pd.isnull(pd.read_csv("data/microbiome_missing.csv")).head(20)


Out[58]:
    Taxon Patient Tissue  Stool
0   False   False  False  False
1   False   False  False  False
2   False   False   True  False
3   False   False  False  False
4   False   False  False  False
5   False   False  False  False
6   False   False  False  False
7   False   False  False  False
8   False   False  False   True
9   False   False  False  False
10  False   False  False  False
11  False   False  False  False
12  False   False  False  False
13  False   False  False  False
14  False   False  False  False
15  False   False  False  False
16  False   False  False  False
17  False   False  False  False
18  False   False  False  False
19  False   False  False  False

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:


In [59]:
pd.read_csv("data/microbiome_missing.csv", na_values=['?', -99999]).head(20)


Out[59]:
             Taxon  Patient  Tissue  Stool
0       Firmicutes        1     632    305
1       Firmicutes        2     136   4182
2       Firmicutes        3     NaN    703
3       Firmicutes        4     408   3946
4       Firmicutes        5     831   8605
5       Firmicutes        6     693     50
6       Firmicutes        7     718    717
7       Firmicutes        8     173     33
8       Firmicutes        9     228    NaN
9       Firmicutes       10     162   3196
10      Firmicutes       11     372    NaN
11      Firmicutes       12    4255   4361
12      Firmicutes       13     107   1667
13      Firmicutes       14     NaN    223
14      Firmicutes       15     281   2377
15  Proteobacteria        1    1638   3886
16  Proteobacteria        2    2469   1821
17  Proteobacteria        3     839    661
18  Proteobacteria        4    4414     18
19  Proteobacteria        5   12044     83

These can be specified on a column-wise basis using an appropriate dict as the argument for na_values.

Microsoft Excel

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).

Importing Excel data to Pandas is a two-step process. First, we create an ExcelFile object using the path of the file:


In [60]:
mb_file = pd.ExcelFile('data/microbiome/MID1.xls')
mb_file


Out[60]:
<pandas.io.excel.ExcelFile at 0x10f8426d0>

Then, since modern spreadsheets consist of one or more "sheets", we parse the sheet with the data of interest:


In [61]:
mb1 = mb_file.parse("Sheet 1", header=None)
mb1.columns = ["Taxon", "Count"]
mb1.head()


Out[61]:
                                               Taxon  Count
0  Archaea "Crenarchaeota" Thermoprotei Desulfuro...      7
1  Archaea "Crenarchaeota" Thermoprotei Desulfuro...      2
2  Archaea "Crenarchaeota" Thermoprotei Sulfoloba...      3
3  Archaea "Crenarchaeota" Thermoprotei Thermopro...      3
4  Archaea "Euryarchaeota" "Methanomicrobia" Meth...      7

There is now a read_excel conveneince function in Pandas that combines these steps into a single call:


In [62]:
mb2 = pd.read_excel('data/microbiome/MID2.xls', sheetname='Sheet 1', header=None)
mb2.head()


Out[62]:
                                                   0   1
0  Archaea "Crenarchaeota" Thermoprotei Acidiloba...   2
1  Archaea "Crenarchaeota" Thermoprotei Acidiloba...  14
2  Archaea "Crenarchaeota" Thermoprotei Desulfuro...  23
3  Archaea "Crenarchaeota" Thermoprotei Desulfuro...   1
4  Archaea "Crenarchaeota" Thermoprotei Desulfuro...   2

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.

Pandas Fundamentals

This section introduces the new user to the key functionality of Pandas that is required to use the software effectively.

For some variety, we will leave our digestive tract bacteria behind and employ some baseball data.


In [63]:
baseball = pd.read_csv("data/baseball.csv", index_col='id')
baseball.head()


Out[63]:
          player  year  stint team  lg   g  ab  r   h  X2b  X3b  hr  rbi  sb  \
id                                                                             
88641  womacto01  2006      2  CHN  NL  19  50  6  14    1    0   1    2   1   
88643  schilcu01  2006      1  BOS  AL  31   2  0   1    0    0   0    0   0   
88645  myersmi01  2006      1  NYA  AL  62   0  0   0    0    0   0    0   0   
88649  helliri01  2006      1  MIL  NL  20   3  0   0    0    0   0    0   0   
88650  johnsra05  2006      1  NYA  AL  33   6  0   1    0    0   0    0   0   

       cs  bb  so  ibb  hbp  sh  sf  gidp  
id                                         
88641   1   4   4    0    0   3   0     0  
88643   0   0   1    0    0   0   0     0  
88645   0   0   0    0    0   0   0     0  
88649   0   0   2    0    0   0   0     0  
88650   0   0   4    0    0   0   0     0  

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:


In [64]:
player_id = baseball.player + baseball.year.astype(str)
baseball_newind = baseball.copy()
baseball_newind.index = player_id
baseball_newind.head()


Out[64]:
                  player  year  stint team  lg   g  ab  r   h  X2b  X3b  hr  \
womacto012006  womacto01  2006      2  CHN  NL  19  50  6  14    1    0   1   
schilcu012006  schilcu01  2006      1  BOS  AL  31   2  0   1    0    0   0   
myersmi012006  myersmi01  2006      1  NYA  AL  62   0  0   0    0    0   0   
helliri012006  helliri01  2006      1  MIL  NL  20   3  0   0    0    0   0   
johnsra052006  johnsra05  2006      1  NYA  AL  33   6  0   1    0    0   0   

               rbi  sb  cs  bb  so  ibb  hbp  sh  sf  gidp  
womacto012006    2   1   1   4   4    0    0   3   0     0  
schilcu012006    0   0   0   0   1    0    0   0   0     0  
myersmi012006    0   0   0   0   0    0    0   0   0     0  
helliri012006    0   0   0   0   2    0    0   0   0     0  
johnsra052006    0   0   0   0   4    0    0   0   0     0  

This looks okay, but let's check:


In [65]:
baseball_newind.index.is_unique


Out[65]:
False

So, indices need not be unique. Our choice is not unique because some players change teams within years.


In [66]:
pd.Series(baseball_newind.index).value_counts()


Out[66]:
wickmbo012007    2
gomezch022007    2
sweenma012007    2
claytro012007    2
hernaro012007    2
loftoke012007    2
trachst012007    2
wellsda012007    2
...
myersmi012007    1
alomasa022007    1
edmonji012007    1
sheffga012007    1
whiteri012007    1
cormirh012007    1
floydcl012007    1
embreal012007    1
Length: 88, dtype: int64

The most important consequence of a non-unique index is that indexing by label will return multiple values for some labels:


In [67]:
baseball_newind.ix['wickmbo012007']


Out[67]:
                  player  year  stint team  lg   g  ab  r  h  X2b  X3b  hr  \
wickmbo012007  wickmbo01  2007      2  ARI  NL   8   0  0  0    0    0   0   
wickmbo012007  wickmbo01  2007      1  ATL  NL  47   0  0  0    0    0   0   

               rbi  sb  cs  bb  so  ibb  hbp  sh  sf  gidp  
wickmbo012007    0   0   0   0   0    0    0   0   0     0  
wickmbo012007    0   0   0   0   0    0    0   0   0     0  

We will learn more about indexing below.

We can create a truly unique index by combining player, team and year:


In [68]:
player_unique = baseball.player + baseball.team + baseball.year.astype(str)
baseball_newind = baseball.copy()
baseball_newind.index = player_unique
baseball_newind.head()


Out[68]:
                     player  year  stint team  lg   g  ab  r   h  X2b  X3b  \
womacto01CHN2006  womacto01  2006      2  CHN  NL  19  50  6  14    1    0   
schilcu01BOS2006  schilcu01  2006      1  BOS  AL  31   2  0   1    0    0   
myersmi01NYA2006  myersmi01  2006      1  NYA  AL  62   0  0   0    0    0   
helliri01MIL2006  helliri01  2006      1  MIL  NL  20   3  0   0    0    0   
johnsra05NYA2006  johnsra05  2006      1  NYA  AL  33   6  0   1    0    0   

                  hr  rbi  sb  cs  bb  so  ibb  hbp  sh  sf  gidp  
womacto01CHN2006   1    2   1   1   4   4    0    0   3   0     0  
schilcu01BOS2006   0    0   0   0   0   1    0    0   0   0     0  
myersmi01NYA2006   0    0   0   0   0   0    0    0   0   0     0  
helliri01MIL2006   0    0   0   0   0   2    0    0   0   0     0  
johnsra05NYA2006   0    0   0   0   0   4    0    0   0   0     0  

In [69]:
baseball_newind.index.is_unique


Out[69]:
True

We can create meaningful indices more easily using a hierarchical index; for now, we will stick with the numeric id field as our index.

Manipulating indices

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.

A simple use of reindex is to alter the order of the rows:


In [70]:
baseball.reindex(baseball.index[::-1]).head()


Out[70]:
          player  year  stint team  lg    g   ab   r    h  X2b  X3b  hr  rbi  \
id                                                                             
89534  alomasa02  2007      1  NYN  NL    8   22   1    3    1    0   0    0   
89533   aloumo01  2007      1  NYN  NL   87  328  51  112   19    1  13   49   
89530  ausmubr01  2007      1  HOU  NL  117  349  38   82   16    3   3   25   
89526  benitar01  2007      1  SFN  NL   19    0   0    0    0    0   0    0   
89525  benitar01  2007      2  FLO  NL   34    0   0    0    0    0   0    0   

       sb  cs  bb  so  ibb  hbp  sh  sf  gidp  
id                                             
89534   0   0   0   3    0    0   0   0     0  
89533   3   0  27  30    5    2   0   3    13  
89530   6   1  37  74    3    6   4   1    11  
89526   0   0   0   0    0    0   0   0     0  
89525   0   0   0   0    0    0   0   0     0  

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:


In [71]:
id_range = range(baseball.index.values.min(), baseball.index.values.max())
baseball.reindex(id_range).head()


Out[71]:
          player  year  stint team   lg   g  ab   r   h  X2b  X3b  hr  rbi  \
88641  womacto01  2006      2  CHN   NL  19  50   6  14    1    0   1    2   
88642        NaN   NaN    NaN  NaN  NaN NaN NaN NaN NaN  NaN  NaN NaN  NaN   
88643  schilcu01  2006      1  BOS   AL  31   2   0   1    0    0   0    0   
88644        NaN   NaN    NaN  NaN  NaN NaN NaN NaN NaN  NaN  NaN NaN  NaN   
88645  myersmi01  2006      1  NYA   AL  62   0   0   0    0    0   0    0   

       sb  cs  bb  so  ibb  hbp  sh  sf  gidp  
88641   1   1   4   4    0    0   3   0     0  
88642 NaN NaN NaN NaN  NaN  NaN NaN NaN   NaN  
88643   0   0   0   1    0    0   0   0     0  
88644 NaN NaN NaN NaN  NaN  NaN NaN NaN   NaN  
88645   0   0   0   0    0    0   0   0     0  

Missing values can be filled as desired, either with selected values, or by rule:


In [72]:
baseball.reindex(id_range, method='ffill', columns=['player','year']).head()


Out[72]:
          player  year
88641  womacto01  2006
88642  womacto01  2006
88643  schilcu01  2006
88644  schilcu01  2006
88645  myersmi01  2006

In [73]:
baseball.reindex(id_range, fill_value='mr.nobody', columns=['player']).head()


Out[73]:
          player
88641  womacto01
88642  mr.nobody
88643  schilcu01
88644  mr.nobody
88645  myersmi01

Keep in mind that reindex does not work if we pass a non-unique index series.

We can remove rows or columns via the drop method:


In [74]:
baseball.shape


Out[74]:
(100, 22)

In [75]:
baseball.drop([89525, 89526])


Out[75]:
          player  year  stint team  lg    g   ab   r    h  X2b  X3b  hr  rbi  \
id                                                                             
88641  womacto01  2006      2  CHN  NL   19   50   6   14    1    0   1    2   
88643  schilcu01  2006      1  BOS  AL   31    2   0    1    0    0   0    0   
88645  myersmi01  2006      1  NYA  AL   62    0   0    0    0    0   0    0   
88649  helliri01  2006      1  MIL  NL   20    3   0    0    0    0   0    0   
88650  johnsra05  2006      1  NYA  AL   33    6   0    1    0    0   0    0   
88652  finlest01  2006      1  SFN  NL  139  426  66  105   21   12   6   40   
88653  gonzalu01  2006      1  ARI  NL  153  586  93  159   52    2  15   73   
88662   seleaa01  2006      1  LAN  NL   28   26   2    5    1    0   0    0   
89177  francju01  2007      2  ATL  NL   15   40   1   10    3    0   0    8   
89178  francju01  2007      1  NYN  NL   40   50   7   10    0    0   1    8   
...          ...   ...    ...  ...  ..  ...  ...  ..  ...  ...  ...  ..  ...   
89497  clemero02  2007      1  NYA  AL    2    2   0    1    0    0   0    0   
89498  claytro01  2007      2  BOS  AL    8    6   1    0    0    0   0    0   
89499  claytro01  2007      1  TOR  AL   69  189  23   48   14    0   1   12   
89501  cirilje01  2007      2  ARI  NL   28   40   6    8    4    0   0    6   
89502  cirilje01  2007      1  MIN  AL   50  153  18   40    9    2   2   21   
89521  bondsba01  2007      1  SFN  NL  126  340  75   94   14    0  28   66   
89523  biggicr01  2007      1  HOU  NL  141  517  68  130   31    3  10   50   
89530  ausmubr01  2007      1  HOU  NL  117  349  38   82   16    3   3   25   
89533   aloumo01  2007      1  NYN  NL   87  328  51  112   19    1  13   49   
89534  alomasa02  2007      1  NYN  NL    8   22   1    3    1    0   0    0   

       sb  cs   bb   so  ibb  hbp  sh  sf  gidp  
id                                               
88641   1   1    4    4    0    0   3   0     0  
88643   0   0    0    1    0    0   0   0     0  
88645   0   0    0    0    0    0   0   0     0  
88649   0   0    0    2    0    0   0   0     0  
88650   0   0    0    4    0    0   0   0     0  
88652   7   0   46   55    2    2   3   4     6  
88653   0   1   69   58   10    7   0   6    14  
88662   0   0    1    7    0    0   6   0     1  
89177   0   0    4   10    1    0   0   1     1  
89178   2   1   10   13    0    0   0   1     1  
...    ..  ..  ...  ...  ...  ...  ..  ..   ...  
89497   0   0    0    0    0    0   0   0     0  
89498   0   0    0    3    0    0   0   0     2  
89499   2   1   14   50    0    1   3   3     8  
89501   0   0    4    6    0    0   0   0     1  
89502   2   0   15   13    0    1   3   2     9  
89521   5   0  132   54   43    3   0   2    13  
89523   4   3   23  112    0    3   7   5     5  
89530   6   1   37   74    3    6   4   1    11  
89533   3   0   27   30    5    2   0   3    13  
89534   0   0    0    3    0    0   0   0     0  

[98 rows x 22 columns]

In [76]:
baseball.drop(['ibb','hbp'], axis=1)


Out[76]:
          player  year  stint team  lg    g   ab   r    h  X2b  X3b  hr  rbi  \
id                                                                             
88641  womacto01  2006      2  CHN  NL   19   50   6   14    1    0   1    2   
88643  schilcu01  2006      1  BOS  AL   31    2   0    1    0    0   0    0   
88645  myersmi01  2006      1  NYA  AL   62    0   0    0    0    0   0    0   
88649  helliri01  2006      1  MIL  NL   20    3   0    0    0    0   0    0   
88650  johnsra05  2006      1  NYA  AL   33    6   0    1    0    0   0    0   
88652  finlest01  2006      1  SFN  NL  139  426  66  105   21   12   6   40   
88653  gonzalu01  2006      1  ARI  NL  153  586  93  159   52    2  15   73   
88662   seleaa01  2006      1  LAN  NL   28   26   2    5    1    0   0    0   
89177  francju01  2007      2  ATL  NL   15   40   1   10    3    0   0    8   
89178  francju01  2007      1  NYN  NL   40   50   7   10    0    0   1    8   
...          ...   ...    ...  ...  ..  ...  ...  ..  ...  ...  ...  ..  ...   
89499  claytro01  2007      1  TOR  AL   69  189  23   48   14    0   1   12   
89501  cirilje01  2007      2  ARI  NL   28   40   6    8    4    0   0    6   
89502  cirilje01  2007      1  MIN  AL   50  153  18   40    9    2   2   21   
89521  bondsba01  2007      1  SFN  NL  126  340  75   94   14    0  28   66   
89523  biggicr01  2007      1  HOU  NL  141  517  68  130   31    3  10   50   
89525  benitar01  2007      2  FLO  NL   34    0   0    0    0    0   0    0   
89526  benitar01  2007      1  SFN  NL   19    0   0    0    0    0   0    0   
89530  ausmubr01  2007      1  HOU  NL  117  349  38   82   16    3   3   25   
89533   aloumo01  2007      1  NYN  NL   87  328  51  112   19    1  13   49   
89534  alomasa02  2007      1  NYN  NL    8   22   1    3    1    0   0    0   

       sb  cs   bb   so  sh  sf  gidp  
id                                     
88641   1   1    4    4   3   0     0  
88643   0   0    0    1   0   0     0  
88645   0   0    0    0   0   0     0  
88649   0   0    0    2   0   0     0  
88650   0   0    0    4   0   0     0  
88652   7   0   46   55   3   4     6  
88653   0   1   69   58   0   6    14  
88662   0   0    1    7   6   0     1  
89177   0   0    4   10   0   1     1  
89178   2   1   10   13   0   1     1  
...    ..  ..  ...  ...  ..  ..   ...  
89499   2   1   14   50   3   3     8  
89501   0   0    4    6   0   0     1  
89502   2   0   15   13   3   2     9  
89521   5   0  132   54   0   2    13  
89523   4   3   23  112   7   5     5  
89525   0   0    0    0   0   0     0  
89526   0   0    0    0   0   0     0  
89530   6   1   37   74   4   1    11  
89533   3   0   27   30   0   3    13  
89534   0   0    0    3   0   0     0  

[100 rows x 20 columns]

Indexing and Selection

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.


In [77]:
# Sample Series object
hits = baseball_newind.h
hits


Out[77]:
womacto01CHN2006     14
schilcu01BOS2006      1
myersmi01NYA2006      0
helliri01MIL2006      0
johnsra05NYA2006      1
finlest01SFN2006    105
gonzalu01ARI2006    159
seleaa01LAN2006       5
...
cirilje01MIN2007     40
bondsba01SFN2007     94
biggicr01HOU2007    130
benitar01FLO2007      0
benitar01SFN2007      0
ausmubr01HOU2007     82
aloumo01NYN2007     112
alomasa02NYN2007      3
Name: h, Length: 100, dtype: int64

In [78]:
# Numpy-style indexing
hits[:3]


Out[78]:
womacto01CHN2006    14
schilcu01BOS2006     1
myersmi01NYA2006     0
Name: h, dtype: int64

In [79]:
# Indexing by label
hits[['womacto01CHN2006','schilcu01BOS2006']]


Out[79]:
womacto01CHN2006    14
schilcu01BOS2006     1
Name: h, dtype: int64

We can also slice with data labels, since they have an intrinsic order within the Index:


In [80]:
hits['womacto01CHN2006':'gonzalu01ARI2006']


Out[80]:
womacto01CHN2006     14
schilcu01BOS2006      1
myersmi01NYA2006      0
helliri01MIL2006      0
johnsra05NYA2006      1
finlest01SFN2006    105
gonzalu01ARI2006    159
Name: h, dtype: int64

In [81]:
hits['womacto01CHN2006':'gonzalu01ARI2006'] = 5
hits


Out[81]:
womacto01CHN2006    5
schilcu01BOS2006    5
myersmi01NYA2006    5
helliri01MIL2006    5
johnsra05NYA2006    5
finlest01SFN2006    5
gonzalu01ARI2006    5
seleaa01LAN2006     5
...
cirilje01MIN2007     40
bondsba01SFN2007     94
biggicr01HOU2007    130
benitar01FLO2007      0
benitar01SFN2007      0
ausmubr01HOU2007     82
aloumo01NYN2007     112
alomasa02NYN2007      3
Name: h, Length: 100, dtype: int64

In a DataFrame we can slice along either or both axes:


In [82]:
baseball_newind[['h','ab']]


Out[82]:
                    h   ab
womacto01CHN2006    5   50
schilcu01BOS2006    5    2
myersmi01NYA2006    5    0
helliri01MIL2006    5    3
johnsra05NYA2006    5    6
finlest01SFN2006    5  426
gonzalu01ARI2006    5  586
seleaa01LAN2006     5   26
francju01ATL2007   10   40
francju01NYN2007   10   50
...               ...  ...
claytro01TOR2007   48  189
cirilje01ARI2007    8   40
cirilje01MIN2007   40  153
bondsba01SFN2007   94  340
biggicr01HOU2007  130  517
benitar01FLO2007    0    0
benitar01SFN2007    0    0
ausmubr01HOU2007   82  349
aloumo01NYN2007   112  328
alomasa02NYN2007    3   22

[100 rows x 2 columns]

In [83]:
baseball_newind[baseball_newind.ab>500]


Out[83]:
                     player  year  stint team  lg    g   ab   r    h  X2b  \
gonzalu01ARI2006  gonzalu01  2006      1  ARI  NL  153  586  93    5   52   
vizquom01SFN2007  vizquom01  2007      1  SFN  NL  145  513  54  126   18   
thomafr04TOR2007  thomafr04  2007      1  TOR  AL  155  531  63  147   30   
rodriiv01DET2007  rodriiv01  2007      1  DET  AL  129  502  50  141   31   
griffke02CIN2007  griffke02  2007      1  CIN  NL  144  528  78  146   24   
delgaca01NYN2007  delgaca01  2007      1  NYN  NL  139  538  71  139   30   
biggicr01HOU2007  biggicr01  2007      1  HOU  NL  141  517  68  130   31   

                  X3b  hr  rbi  sb  cs  bb   so  ibb  hbp  sh  sf  gidp  
gonzalu01ARI2006    2  15   73   0   1  69   58   10    7   0   6    14  
vizquom01SFN2007    3   4   51  14   6  44   48    6    1  14   3    14  
thomafr04TOR2007    0  26   95   0   0  81   94    3    7   0   5    14  
rodriiv01DET2007    3  11   63   2   2   9   96    1    1   1   2    16  
griffke02CIN2007    1  30   93   6   1  85   99   14    1   0   9    14  
delgaca01NYN2007    0  24   87   4   0  52  118    8   11   0   6    12  
biggicr01HOU2007    3  10   50   4   3  23  112    0    3   7   5     5  

The indexing field ix allows us to select subsets of rows and columns in an intuitive way:


In [84]:
baseball_newind.ix['gonzalu01ARI2006', ['h','X2b', 'X3b', 'hr']]


Out[84]:
h       5
X2b    52
X3b     2
hr     15
Name: gonzalu01ARI2006, dtype: object

In [85]:
baseball_newind.ix[['gonzalu01ARI2006','finlest01SFN2006'], 5:8]


Out[85]:
                    g   ab   r
gonzalu01ARI2006  153  586  93
finlest01SFN2006  139  426  66

In [86]:
baseball_newind.ix[:'myersmi01NYA2006', 'hr']


Out[86]:
womacto01CHN2006    1
schilcu01BOS2006    0
myersmi01NYA2006    0
Name: hr, dtype: int64

Similarly, the cross-section method xs (not a field) extracts a single column or row by label and returns it as a Series:


In [87]:
baseball_newind.xs('myersmi01NYA2006')


Out[87]:
player    myersmi01
year           2006
stint             1
team            NYA
lg               AL
g                62
ab                0
r                 0
...
cs      0
bb      0
so      0
ibb     0
hbp     0
sh      0
sf      0
gidp    0
Name: myersmi01NYA2006, Length: 22, dtype: object

Operations

DataFrame and Series objects allow for several operations to take place either on a single object, or between two or more objects.

For example, we can perform arithmetic on the elements of two objects, such as combining baseball statistics across years:


In [88]:
hr2006 = baseball[baseball.year==2006].xs('hr', axis=1)
hr2006.index = baseball.player[baseball.year==2006]

hr2007 = baseball[baseball.year==2007].xs('hr', axis=1)
hr2007.index = baseball.player[baseball.year==2007]

In [89]:
hr2006 = pd.Series(baseball.hr[baseball.year==2006].values, index=baseball.player[baseball.year==2006])
hr2007 = pd.Series(baseball.hr[baseball.year==2007].values, index=baseball.player[baseball.year==2007])

In [90]:
hr_total = hr2006 + hr2007
hr_total


Out[90]:
player
alomasa02   NaN
aloumo01    NaN
ausmubr01   NaN
benitar01   NaN
benitar01   NaN
biggicr01   NaN
bondsba01   NaN
cirilje01   NaN
...
whiteri01   NaN
whitero02   NaN
wickmbo01   NaN
wickmbo01   NaN
williwo02   NaN
witasja01   NaN
womacto01   NaN
zaungr01    NaN
Length: 94, dtype: float64

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.


In [91]:
hr_total[hr_total.notnull()]


Out[91]:
player
finlest01     7
gonzalu01    30
johnsra05     0
myersmi01     0
schilcu01     0
seleaa01      0
dtype: float64

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:


In [92]:
hr2007.add(hr2006, fill_value=0)


Out[92]:
player
alomasa02     0
aloumo01     13
ausmubr01     3
benitar01     0
benitar01     0
biggicr01    10
bondsba01    28
cirilje01     0
...
whiteri01     0
whitero02     4
wickmbo01     0
wickmbo01     0
williwo02     1
witasja01     0
womacto01     1
zaungr01     10
Length: 94, dtype: float64

Operations can also be broadcast between rows or columns.

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:


In [93]:
baseball.hr - baseball.hr.max()


Out[93]:
id
88641   -34
88643   -35
88645   -35
88649   -35
88650   -35
88652   -29
88653   -20
88662   -35
...
89502   -33
89521    -7
89523   -25
89525   -35
89526   -35
89530   -32
89533   -22
89534   -35
Name: hr, Length: 100, dtype: int64

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


In [94]:
baseball.ix[89521]["player"]


Out[94]:
'bondsba01'

In [95]:
stats = baseball[['h','X2b', 'X3b', 'hr']]
diff = stats - stats.xs(89521)
diff[:10]


Out[95]:
        h  X2b  X3b  hr
id                     
88641 -80  -13    0 -27
88643 -93  -14    0 -28
88645 -94  -14    0 -28
88649 -94  -14    0 -28
88650 -93  -14    0 -28
88652  11    7   12 -22
88653  65   38    2 -13
88662 -89  -13    0 -28
89177 -84  -11    0 -28
89178 -84  -14    0 -27

We can also apply functions to each column or row of a DataFrame


In [96]:
stats.apply(np.median)


Out[96]:
h      8
X2b    1
X3b    0
hr     0
dtype: float64

In [97]:
stat_range = lambda x: x.max() - x.min()
stats.apply(stat_range)


Out[97]:
h      159
X2b     52
X3b     12
hr      35
dtype: int64

Lets use apply to calculate a meaningful baseball statistics, slugging percentage:

$$SLG = \frac{1B + (2 \times 2B) + (3 \times 3B) + (4 \times HR)}{AB}$$

And just for fun, we will format the resulting estimate.


In [98]:
slg = lambda x: (x['h']-x['X2b']-x['X3b']-x['hr'] + 2*x['X2b'] + 3*x['X3b'] + 4*x['hr'])/(x['ab']+1e-6)
baseball.apply(slg, axis=1).apply(lambda x: '%.3f' % x)


Out[98]:
id
88641    0.360
88643    0.500
88645    0.000
88649    0.000
88650    0.167
88652    0.394
88653    0.444
88662    0.231
...
89502    0.386
89521    0.565
89523    0.381
89525    0.000
89526    0.000
89530    0.324
89533    0.524
89534    0.182
Length: 100, dtype: object

Sorting and Ranking

Pandas objects include methods for re-ordering data.


In [99]:
baseball_newind.sort_index().head()


Out[99]:
                     player  year  stint team  lg    g   ab   r    h  X2b  \
alomasa02NYN2007  alomasa02  2007      1  NYN  NL    8   22   1    3    1   
aloumo01NYN2007    aloumo01  2007      1  NYN  NL   87  328  51  112   19   
ausmubr01HOU2007  ausmubr01  2007      1  HOU  NL  117  349  38   82   16   
benitar01FLO2007  benitar01  2007      2  FLO  NL   34    0   0    0    0   
benitar01SFN2007  benitar01  2007      1  SFN  NL   19    0   0    0    0   

                  X3b  hr  rbi  sb  cs  bb  so  ibb  hbp  sh  sf  gidp  
alomasa02NYN2007    0   0    0   0   0   0   3    0    0   0   0     0  
aloumo01NYN2007     1  13   49   3   0  27  30    5    2   0   3    13  
ausmubr01HOU2007    3   3   25   6   1  37  74    3    6   4   1    11  
benitar01FLO2007    0   0    0   0   0   0   0    0    0   0   0     0  
benitar01SFN2007    0   0    0   0   0   0   0    0    0   0   0     0  

In [100]:
baseball_newind.sort_index(ascending=False).head()


Out[100]:
                     player  year  stint team  lg    g   ab   r   h  X2b  X3b  \
zaungr01TOR2007    zaungr01  2007      1  TOR  AL  110  331  43  80   24    1   
womacto01CHN2006  womacto01  2006      2  CHN  NL   19   50   6   5    1    0   
witasja01TBA2007  witasja01  2007      1  TBA  AL    3    0   0   0    0    0   
williwo02HOU2007  williwo02  2007      1  HOU  NL   33   59   3   6    0    0   
wickmbo01ATL2007  wickmbo01  2007      1  ATL  NL   47    0   0   0    0    0   

                  hr  rbi  sb  cs  bb  so  ibb  hbp  sh  sf  gidp  
zaungr01TOR2007   10   52   0   0  51  55    8    2   1   6     9  
womacto01CHN2006   1    2   1   1   4   4    0    0   3   0     0  
witasja01TBA2007   0    0   0   0   0   0    0    0   0   0     0  
williwo02HOU2007   1    2   0   0   0  25    0    0   5   0     1  
wickmbo01ATL2007   0    0   0   0   0   0    0    0   0   0     0  

In [101]:
baseball_newind.sort_index(axis=1).head()


Out[101]:
                  X2b  X3b  ab  bb  cs   g  gidp  h  hbp  hr  ibb  lg  \
womacto01CHN2006    1    0  50   4   1  19     0  5    0   1    0  NL   
schilcu01BOS2006    0    0   2   0   0  31     0  5    0   0    0  AL   
myersmi01NYA2006    0    0   0   0   0  62     0  5    0   0    0  AL   
helliri01MIL2006    0    0   3   0   0  20     0  5    0   0    0  NL   
johnsra05NYA2006    0    0   6   0   0  33     0  5    0   0    0  AL   

                     player  r  rbi  sb  sf  sh  so  stint team  year  
womacto01CHN2006  womacto01  6    2   1   0   3   4      2  CHN  2006  
schilcu01BOS2006  schilcu01  0    0   0   0   0   1      1  BOS  2006  
myersmi01NYA2006  myersmi01  0    0   0   0   0   0      1  NYA  2006  
helliri01MIL2006  helliri01  0    0   0   0   0   2      1  MIL  2006  
johnsra05NYA2006  johnsra05  0    0   0   0   0   4      1  NYA  2006  

We can also use order to sort a Series by value, rather than by label.


In [102]:
baseball.hr.order(ascending=False)


Out[102]:
id
89360    35
89462    30
89521    28
89361    26
89378    25
89489    24
89371    21
89374    21
...
89465    0
89372    0
89467    0
89370    0
89367    0
89469    0
89365    0
89534    0
Name: hr, Length: 100, dtype: int64

For a DataFrame, we can sort according to the values of one or more columns using the by argument of sort_index:


In [103]:
baseball[['player','sb','cs']].sort_index(ascending=[False,True], by=['sb', 'cs']).head(10)


Out[103]:
          player  sb  cs
id                      
89378  sheffga01  22   5
89430  loftoke01  21   4
89347  vizquom01  14   6
89463  greensh01  11   1
88652  finlest01   7   0
89462  griffke02   6   1
89530  ausmubr01   6   1
89466  gonzalu01   6   2
89521  bondsba01   5   0
89438  kleskry01   5   1

Ranking does not re-arrange data, but instead returns an index that ranks each value relative to others in the Series.


In [104]:
baseball.hr.rank()


Out[104]:
id
88641    62.5
88643    29.0
88645    29.0
88649    29.0
88650    29.0
88652    76.0
88653    89.5
88662    29.0
...
89502    69.0
89521    98.0
89523    83.5
89525    29.0
89526    29.0
89530    71.5
89533    88.0
89534    29.0
Name: hr, Length: 100, dtype: float64

Ties are assigned the mean value of the tied ranks, which may result in decimal values.


In [105]:
pd.Series([100,100]).rank()


Out[105]:
0    1.5
1    1.5
dtype: float64

Alternatively, you can break ties via one of several methods, such as by the order in which they occur in the dataset:


In [106]:
baseball.hr.rank(method='first')


Out[106]:
id
88641    58
88643     1
88645     2
88649     3
88650     4
88652    75
88653    89
88662     5
...
89502    70
89521    98
89523    85
89525    55
89526    56
89530    72
89533    88
89534    57
Name: hr, Length: 100, dtype: float64

Calling the DataFrame's rank method results in the ranks of all columns:


In [107]:
baseball.rank(ascending=False).head()


Out[107]:
       player  year  stint  team    lg     g    ab     r     h   X2b   X3b  \
id                                                                           
88641     2.0  96.5      7  82.0  31.5  70.0  47.5  40.5  39.0  50.5  63.5   
88643    37.5  96.5     57  88.0  81.5  55.5  73.0  81.0  63.5  78.0  63.5   
88645    47.5  96.5     57  40.5  81.5  36.0  91.0  81.0  84.5  78.0  63.5   
88649    66.0  96.5     57  47.0  31.5  67.5  69.0  81.0  84.5  78.0  63.5   
88650    61.5  96.5     57  40.5  81.5  51.0  64.5  81.0  63.5  78.0  63.5   

         hr   rbi    sb    cs    bb  so  ibb   hbp    sh  sf  gidp  
id                                                                  
88641  38.5  51.0  24.5  17.5  44.5  59   66  65.5  16.0  70  76.5  
88643  72.0  78.5  63.5  62.5  79.0  73   66  65.5  67.5  70  76.5  
88645  72.0  78.5  63.5  62.5  79.0  89   66  65.5  67.5  70  76.5  
88649  72.0  78.5  63.5  62.5  79.0  67   66  65.5  67.5  70  76.5  
88650  72.0  78.5  63.5  62.5  79.0  59   66  65.5  67.5  70  76.5  

In [108]:
baseball[['r','h','hr']].rank(ascending=False).head()


Out[108]:
          r     h    hr
id                     
88641  40.5  39.0  38.5
88643  81.0  63.5  72.0
88645  81.0  84.5  72.0
88649  81.0  84.5  72.0
88650  81.0  63.5  72.0

Exercise

Calculate on base percentage for each player, and return the ordered series of estimates.

$$OBP = \frac{H + BB + HBP}{AB + BB + HBP + SF}$$

In [108]:
# Write your answer here

Hierarchical indexing

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.


In [109]:
baseball_h = baseball.set_index(['year', 'team', 'player'])
baseball_h.head(10)


Out[109]:
                     stint  lg    g   ab   r    h  X2b  X3b  hr  rbi  sb  cs  \
year team player                                                               
2006 CHN  womacto01      2  NL   19   50   6   14    1    0   1    2   1   1   
     BOS  schilcu01      1  AL   31    2   0    1    0    0   0    0   0   0   
     NYA  myersmi01      1  AL   62    0   0    0    0    0   0    0   0   0   
     MIL  helliri01      1  NL   20    3   0    0    0    0   0    0   0   0   
     NYA  johnsra05      1  AL   33    6   0    1    0    0   0    0   0   0   
     SFN  finlest01      1  NL  139  426  66  105   21   12   6   40   7   0   
     ARI  gonzalu01      1  NL  153  586  93  159   52    2  15   73   0   1   
     LAN  seleaa01       1  NL   28   26   2    5    1    0   0    0   0   0   
2007 ATL  francju01      2  NL   15   40   1   10    3    0   0    8   0   0   
     NYN  francju01      1  NL   40   50   7   10    0    0   1    8   2   1   

                     bb  so  ibb  hbp  sh  sf  gidp  
year team player                                     
2006 CHN  womacto01   4   4    0    0   3   0     0  
     BOS  schilcu01   0   1    0    0   0   0     0  
     NYA  myersmi01   0   0    0    0   0   0     0  
     MIL  helliri01   0   2    0    0   0   0     0  
     NYA  johnsra05   0   4    0    0   0   0     0  
     SFN  finlest01  46  55    2    2   3   4     6  
     ARI  gonzalu01  69  58   10    7   0   6    14  
     LAN  seleaa01    1   7    0    0   6   0     1  
2007 ATL  francju01   4  10    1    0   0   1     1  
     NYN  francju01  10  13    0    0   0   1     1  

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.


In [110]:
baseball_h.index[:10]


Out[110]:
MultiIndex(levels=[[2006, 2007], [u'ARI', u'ATL', u'BAL', u'BOS', u'CHA', u'CHN', u'CIN', u'CLE', u'COL', u'DET', u'FLO', u'HOU', u'KCA', u'LAA', u'LAN', u'MIL', u'MIN', u'NYA', u'NYN', u'OAK', u'PHI', u'SDN', u'SFN', u'SLN', u'TBA', u'TEX', u'TOR'], [u'alomasa02', u'aloumo01', u'ausmubr01', u'benitar01', u'biggicr01', u'bondsba01', u'cirilje01', u'claytro01', u'clemero02', u'coninje01', u'cormirh01', u'delgaca01', u'easleda01', u'edmonji01', u'embreal01', u'finlest01', u'floydcl01', u'francju01', u'glavito02', u'gomezch02', u'gonzalu01', u'gordoto01', u'graffto01', u'greensh01', u'griffke02', u'guarded01', u'helliri01', u'hernaro01', u'hoffmtr01', u'johnsra05', u'jonesto02', u'kentje01', u'kleskry01', u'loaizes01', u'loftoke01', u'mabryjo01', u'maddugr01', u'martipe02', u'mesajo01', u'moyerja01', u'mussimi01', u'myersmi01', u'oliveda02', u'parkch01', u'perezne01', u'piazzmi01', u'ramirma02', u'rodriiv01', u'rogerke01', u'sandere02', u'schilcu01', u'schmija01', u'seaneru01', u'seleaa01', u'sheffga01', u'smoltjo01', u'sosasa01', u'sprinru01', u'stairma01', u'stantmi02', u'stinnke01', u'suppaje01', u'sweenma01', u'tavarju01', u'thomafr04', u'thomeji01', u'timlimi01', u'trachst01', u'valenjo03', u'villoro01', u'vizquom01', u'wakefti01', u'walketo04', u'weathda01', u'wellsda01', u'whiteri01', u'whitero02', u'wickmbo01', u'williwo02', u'witasja01', u'womacto01', u'zaungr01']],
           labels=[[0, 0, 0, 0, 0, 0, 0, 0, 1, 1], [5, 3, 17, 15, 17, 22, 0, 14, 1, 18], [80, 50, 41, 26, 29, 15, 20, 53, 17, 17]],
           names=[u'year', u'team', u'player'])

In [111]:
baseball_h.index.is_unique


Out[111]:
True

In [112]:
baseball_h.ix[(2007, 'ATL', 'francju01')]


Out[112]:
stint     2
lg       NL
g        15
ab       40
r         1
h        10
X2b       3
X3b       0
hr        0
rbi       8
sb        0
cs        0
bb        4
so       10
ibb       1
hbp       0
sh        0
sf        1
gidp      1
Name: (2007, ATL, francju01), dtype: object

Recall earlier we imported some microbiome data using two index columns. This created a 2-level hierarchical index:


In [113]:
mb = pd.read_csv("data/microbiome.csv", index_col=['Taxon','Patient'])

In [114]:
mb.head(10)


Out[114]:
                    Tissue  Stool
Taxon      Patient               
Firmicutes 1           632    305
           2           136   4182
           3          1174    703
           4           408   3946
           5           831   8605
           6           693     50
           7           718    717
           8           173     33
           9           228     80
           10          162   3196

In [115]:
mb.index


Out[115]:
MultiIndex(levels=[[u'Actinobacteria', u'Bacteroidetes', u'Firmicutes', u'Other', u'Proteobacteria'], [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]],
           labels=[[2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3], [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14]],
           names=[u'Taxon', u'Patient'])

With a hierachical index, we can select subsets of the data based on a partial index:


In [116]:
mb.ix['Proteobacteria']


Out[116]:
         Tissue  Stool
Patient               
1          1638   3886
2          2469   1821
3           839    661
4          4414     18
5         12044     83
6          2310     12
7          3053    547
8           395   2174
9          2651    767
10         1195     76
11         6857    795
12          483    666
13         2950   3994
14         1541    816
15         1307     53

Hierarchical indices can be created on either or both axes. Here is a trivial example:


In [117]:
frame = pd.DataFrame(np.arange(12).reshape(( 4, 3)), 
                  index =[['a', 'a', 'b', 'b'], [1, 2, 1, 2]], 
                  columns =[['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']])

frame


Out[117]:
      Ohio       Colorado
     Green  Red     Green
a 1      0    1         2
  2      3    4         5
b 1      6    7         8
  2      9   10        11

If you want to get fancy, both the row and column indices themselves can be given names:


In [118]:
frame.index.names = ['key1', 'key2']
frame.columns.names = ['state', 'color']
frame


Out[118]:
state       Ohio       Colorado
color      Green  Red     Green
key1 key2                      
a    1         0    1         2
     2         3    4         5
b    1         6    7         8
     2         9   10        11

With this, we can do all sorts of custom indexing:


In [119]:
frame.ix['a']['Ohio']


Out[119]:
color  Green  Red
key2             
1          0    1
2          3    4

In [120]:
frame.ix['b', 2]['Colorado']


Out[120]:
color
Green    11
Name: (b, 2), dtype: int64

Additionally, the order of the set of indices in a hierarchical MultiIndex can be changed by swapping them pairwise:


In [121]:
mb.swaplevel('Patient', 'Taxon').head()


Out[121]:
                    Tissue  Stool
Patient Taxon                    
1       Firmicutes     632    305
2       Firmicutes     136   4182
3       Firmicutes    1174    703
4       Firmicutes     408   3946
5       Firmicutes     831   8605

Data can also be sorted by any index level, using sortlevel:


In [122]:
mb.sortlevel('Patient', ascending=False).head()


Out[122]:
                        Tissue  Stool
Taxon          Patient               
Proteobacteria 15         1307     53
Other          15          305     32
Firmicutes     15          281   2377
Bacteroidetes  15          102     33
Actinobacteria 15          310    204

Missing data

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.

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).


In [130]:
foo = pd.Series([np.nan, -3, None, 'foobar'])
foo


Out[130]:
0       NaN
1        -3
2      None
3    foobar
dtype: object

In [131]:
foo.isnull()


Out[131]:
0     True
1    False
2     True
3    False
dtype: bool

Missing values may be dropped or indexed out:


In [132]:
bacteria2


Out[132]:
phylum
Firmicutes         NaN
Proteobacteria     632
Actinobacteria    1638
Bacteroidetes      569
dtype: float64

In [133]:
bacteria2.dropna()


Out[133]:
phylum
Proteobacteria     632
Actinobacteria    1638
Bacteroidetes      569
dtype: float64

In [134]:
bacteria2[bacteria2.notnull()]


Out[134]:
phylum
Proteobacteria     632
Actinobacteria    1638
Bacteroidetes      569
dtype: float64

By default, dropna drops entire rows in which one or more values are missing.


In [135]:
data


Out[135]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1
6       2  Actinobacteria   754  2013        NaN
7       2   Bacteroidetes   555  2013        NaN

In [136]:
data.dropna()


Out[136]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1

This can be overridden by passing the how='all' argument, which only drops a row when every field is a missing value.


In [137]:
data.dropna(how='all')


Out[137]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1
6       2  Actinobacteria   754  2013        NaN
7       2   Bacteroidetes   555  2013        NaN

This can be customized further by specifying how many values need to be present before a row is dropped via the thresh argument.


In [140]:
data.ix[7, 'year'] = np.nan
data


Out[140]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1
6       2  Actinobacteria   754  2013        NaN
7       2   Bacteroidetes   555   NaN        NaN

In [141]:
data.dropna(thresh=4)


Out[141]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1
6       2  Actinobacteria   754  2013        NaN

This is typically used in time series applications, where there are repeated measurements that are incomplete for some subjects.

If we want to drop missing values column-wise instead of row-wise, we use axis=1.


In [142]:
data.dropna(axis=1)


Out[142]:
  patient          phylum value
0       1      Firmicutes   632
1       1  Proteobacteria  1638
2       1  Actinobacteria   569
3       1   Bacteroidetes    14
4       2      Firmicutes   433
5       2  Proteobacteria     0
6       2  Actinobacteria   754
7       2   Bacteroidetes   555

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.


In [143]:
bacteria2.fillna(0)


Out[143]:
phylum
Firmicutes           0
Proteobacteria     632
Actinobacteria    1638
Bacteroidetes      569
dtype: float64

In [144]:
data.fillna({'year': 2013, 'treatment':2})


Out[144]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1
6       2  Actinobacteria   754  2013          2
7       2   Bacteroidetes   555  2013          2

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!).


In [145]:
data


Out[145]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1
6       2  Actinobacteria   754  2013        NaN
7       2   Bacteroidetes   555   NaN        NaN

We can alter values in-place using inplace=True.


In [146]:
_ = data.year.fillna(2013, inplace=True)
data


Out[146]:
  patient          phylum value  year  treatment
0       1      Firmicutes   632  2013          0
1       1  Proteobacteria  1638  2013          0
2       1  Actinobacteria   569  2013          0
3       1   Bacteroidetes    14  2013          0
4       2      Firmicutes   433  2013          1
5       2  Proteobacteria     0  2013          1
6       2  Actinobacteria   754  2013        NaN
7       2   Bacteroidetes   555  2013        NaN

Missing values can also be interpolated, using any one of a variety of methods:


In [147]:
bacteria2.fillna(method='bfill')


Out[147]:
phylum
Firmicutes         632
Proteobacteria     632
Actinobacteria    1638
Bacteroidetes      569
dtype: float64

In [148]:
bacteria2.fillna(bacteria2.mean())


Out[148]:
phylum
Firmicutes         946.333333
Proteobacteria     632.000000
Actinobacteria    1638.000000
Bacteroidetes      569.000000
dtype: float64

Data summarization

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.


In [149]:
baseball.sum()


Out[149]:
player    womacto01schilcu01myersmi01helliri01johnsra05f...
year                                                 200692
stint                                                   113
team      CHNBOSNYAMILNYASFNARILANATLNYNTORTBAHOUARIATLM...
lg        NLALALNLALNLNLNLNLNLALALNLNLNLALNLNLNLNLALALNL...
g                                                      5238
ab                                                    13654
r                                                      1869
...
cs        46
bb      1549
so      2408
ibb      177
hbp      112
sh       138
sf       120
gidp     354
Length: 22, dtype: object

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:


In [150]:
baseball.mean()


Out[150]:
year     2006.92
stint       1.13
g          52.38
ab        136.54
r          18.69
h          35.82
X2b         7.39
X3b         0.55
hr          4.37
rbi        18.47
sb          1.38
cs          0.46
bb         15.49
so         24.08
ibb         1.77
hbp         1.12
sh          1.38
sf          1.20
gidp        3.54
dtype: float64

The important difference between NumPy's functions and Pandas' methods is that the latter have built-in support for handling missing data.


In [151]:
bacteria2


Out[151]:
phylum
Firmicutes         NaN
Proteobacteria     632
Actinobacteria    1638
Bacteroidetes      569
dtype: float64

In [152]:
bacteria2.mean()


Out[152]:
946.33333333333337

Sometimes we may not want to ignore missing values, and allow the nan to propagate.


In [153]:
bacteria2.mean(skipna=False)


Out[153]:
nan

Passing axis=1 will summarize over rows instead of columns, which only makes sense in certain situations.


In [154]:
extra_bases = baseball[['X2b','X3b','hr']].sum(axis=1)
extra_bases.order(ascending=False)


Out[154]:
id
88653    69
89439    57
89361    56
89462    55
89396    54
89489    54
89360    54
89371    50
...
89445    0
89388    0
89359    0
89355    0
89354    0
89480    0
89348    0
89420    0
Length: 100, dtype: int64

A useful summarization that gives a quick snapshot of multiple statistics for a Series or DataFrame is describe:


In [155]:
baseball.describe()


Out[155]:
             year       stint           g          ab          r           h  \
count   100.00000  100.000000  100.000000  100.000000  100.00000  100.000000   
mean   2006.92000    1.130000   52.380000  136.540000   18.69000   35.820000   
std       0.27266    0.337998   48.031299  181.936853   27.77496   50.221807   
min    2006.00000    1.000000    1.000000    0.000000    0.00000    0.000000   
25%    2007.00000    1.000000    9.500000    2.000000    0.00000    0.000000   
50%    2007.00000    1.000000   33.000000   40.500000    2.00000    8.000000   
75%    2007.00000    1.000000   83.250000  243.750000   33.25000   62.750000   
max    2007.00000    2.000000  155.000000  586.000000  107.00000  159.000000   

              X2b         X3b          hr        rbi          sb          cs  \
count  100.000000  100.000000  100.000000  100.00000  100.000000  100.000000   
mean     7.390000    0.550000    4.370000   18.47000    1.380000    0.460000   
std     11.117277    1.445124    7.975537   28.34793    3.694878    1.067613   
min      0.000000    0.000000    0.000000    0.00000    0.000000    0.000000   
25%      0.000000    0.000000    0.000000    0.00000    0.000000    0.000000   
50%      1.000000    0.000000    0.000000    2.00000    0.000000    0.000000   
75%     11.750000    1.000000    6.000000   27.00000    1.000000    0.000000   
max     52.000000   12.000000   35.000000   96.00000   22.000000    6.000000   

               bb          so         ibb        hbp          sh          sf  \
count  100.000000  100.000000  100.000000  100.00000  100.000000  100.000000   
mean    15.490000   24.080000    1.770000    1.12000    1.380000    1.200000   
std     25.812649   32.804496    5.042957    2.23055    2.919042    2.035046   
min      0.000000    0.000000    0.000000    0.00000    0.000000    0.000000   
25%      0.000000    1.000000    0.000000    0.00000    0.000000    0.000000   
50%      1.000000    7.000000    0.000000    0.00000    0.000000    0.000000   
75%     19.250000   37.250000    1.250000    1.00000    1.000000    2.000000   
max    132.000000  134.000000   43.000000   11.00000   14.000000    9.000000   

             gidp  
count  100.000000  
mean     3.540000  
std      5.201826  
min      0.000000  
25%      0.000000  
50%      1.000000  
75%      6.000000  
max     21.000000  

describe can detect non-numeric data and sometimes yield useful information about it.


In [156]:
baseball.player.describe()


Out[156]:
count           100
unique           82
top       coninje01
freq              2
dtype: object

We can also calculate summary statistics across multiple columns, for example, correlation and covariance.

$$cov(x,y) = \sum_i (x_i - \bar{x})(y_i - \bar{y})$$

In [157]:
baseball.hr.cov(baseball.X2b)


Out[157]:
69.076464646464629
$$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}}$$

In [158]:
baseball.hr.corr(baseball.X2b)


Out[158]:
0.77906151825397507

In [159]:
baseball.ab.corr(baseball.h)


Out[159]:
0.99421740362723787

In [160]:
baseball.corr()


Out[160]:
           year     stint         g        ab         r         h       X2b  \
year   1.000000  0.004384 -0.050874 -0.001360 -0.023315  0.001151 -0.052917   
stint  0.004384  1.000000 -0.257552 -0.216333 -0.209781 -0.206878 -0.196423   
g     -0.050874 -0.257552  1.000000  0.935910  0.910262  0.929292  0.885847   
ab    -0.001360 -0.216333  0.935910  1.000000  0.965609  0.994217  0.952249   
r     -0.023315 -0.209781  0.910262  0.965609  1.000000  0.970560  0.923508   
h      0.001151 -0.206878  0.929292  0.994217  0.970560  1.000000  0.957275   
X2b   -0.052917 -0.196423  0.885847  0.952249  0.923508  0.957275  1.000000   
X3b   -0.246099 -0.085821  0.518663  0.535986  0.500807  0.514245  0.493267   
hr     0.060199 -0.209124  0.802014  0.843308  0.890060  0.855163  0.779062   
rbi    0.042812 -0.205688  0.891563  0.947911  0.941483  0.952320  0.901751   
sb     0.030480 -0.120837  0.492362  0.533536  0.596343  0.530018  0.413655   
cs     0.058296 -0.055425  0.520923  0.577192  0.576454  0.571629  0.477487   
bb     0.005626 -0.190301  0.828572  0.850803  0.915010  0.853384  0.780012   
so     0.069610 -0.214121  0.866499  0.923926  0.879375  0.906966  0.862149   
ibb    0.015868 -0.118580  0.514423  0.506398  0.588882  0.513009  0.453301   
hbp   -0.000664 -0.195074  0.730161  0.767210  0.806523  0.767449  0.738226   
sh    -0.012184 -0.091527  0.079361  0.094537 -0.001273  0.045533  0.005659   
sf    -0.007282 -0.155662  0.767543  0.840361  0.839592  0.839737  0.819361   
gidp   0.052131 -0.224173  0.863041  0.926632  0.894724  0.935525  0.906860   

            X3b        hr       rbi        sb        cs        bb        so  \
year  -0.246099  0.060199  0.042812  0.030480  0.058296  0.005626  0.069610   
stint -0.085821 -0.209124 -0.205688 -0.120837 -0.055425 -0.190301 -0.214121   
g      0.518663  0.802014  0.891563  0.492362  0.520923  0.828572  0.866499   
ab     0.535986  0.843308  0.947911  0.533536  0.577192  0.850803  0.923926   
r      0.500807  0.890060  0.941483  0.596343  0.576454  0.915010  0.879375   
h      0.514245  0.855163  0.952320  0.530018  0.571629  0.853384  0.906966   
X2b    0.493267  0.779062  0.901751  0.413655  0.477487  0.780012  0.862149   
X3b    1.000000  0.210028  0.369890  0.450421  0.384312  0.350682  0.408800   
hr     0.210028  1.000000  0.948787  0.364346  0.345187  0.916774  0.865929   
rbi    0.369890  0.948787  1.000000  0.394633  0.435011  0.893945  0.929410   
sb     0.450421  0.364346  0.394633  1.000000  0.743921  0.491351  0.365841   
cs     0.384312  0.345187  0.435011  0.743921  1.000000  0.425352  0.426658   
bb     0.350682  0.916774  0.893945  0.491351  0.425352  1.000000  0.795751   
so     0.408800  0.865929  0.929410  0.365841  0.426658  0.795751  1.000000   
ibb    0.090993  0.673691  0.582982  0.209110  0.106152  0.792057  0.476613   
hbp    0.217474  0.767411  0.780899  0.413570  0.337129  0.742118  0.742547   
sh     0.187012 -0.145374 -0.054670  0.171910  0.293397 -0.044992  0.073624   
sf     0.394987  0.782038  0.855260  0.412947  0.422146  0.760932  0.782314   
gidp   0.411577  0.798350  0.906908  0.431198  0.456820  0.823631  0.846629   

            ibb       hbp        sh        sf      gidp  
year   0.015868 -0.000664 -0.012184 -0.007282  0.052131  
stint -0.118580 -0.195074 -0.091527 -0.155662 -0.224173  
g      0.514423  0.730161  0.079361  0.767543  0.863041  
ab     0.506398  0.767210  0.094537  0.840361  0.926632  
r      0.588882  0.806523 -0.001273  0.839592  0.894724  
h      0.513009  0.767449  0.045533  0.839737  0.935525  
X2b    0.453301  0.738226  0.005659  0.819361  0.906860  
X3b    0.090993  0.217474  0.187012  0.394987  0.411577  
hr     0.673691  0.767411 -0.145374  0.782038  0.798350  
rbi    0.582982  0.780899 -0.054670  0.855260  0.906908  
sb     0.209110  0.413570  0.171910  0.412947  0.431198  
cs     0.106152  0.337129  0.293397  0.422146  0.456820  
bb     0.792057  0.742118 -0.044992  0.760932  0.823631  
so     0.476613  0.742547  0.073624  0.782314  0.846629  
ibb    1.000000  0.431714 -0.075658  0.451377  0.572355  
hbp    0.431714  1.000000 -0.104810  0.648882  0.700380  
sh    -0.075658 -0.104810  1.000000 -0.002721  0.028924  
sf     0.451377  0.648882 -0.002721  1.000000  0.785489  
gidp   0.572355  0.700380  0.028924  0.785489  1.000000  

If we have a DataFrame with a hierarchical index (or indices), summary statistics can be applied with respect to any of the index levels:


In [161]:
mb.head()


Out[161]:
                    Tissue  Stool
Taxon      Patient               
Firmicutes 1           632    305
           2           136   4182
           3          1174    703
           4           408   3946
           5           831   8605

In [162]:
mb.sum(level='Taxon')


Out[162]:
                Tissue  Stool
Taxon                        
Actinobacteria    6736   2263
Bacteroidetes     8995   4656
Firmicutes       10266  30477
Other             2982    519
Proteobacteria   44146  16369

Writing Data to Files

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.


In [163]:
mb.to_csv("mb.csv")

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.

An efficient way of storing data to disk is in binary format. Pandas supports this using Python’s built-in pickle serialization.


In [164]:
baseball.to_pickle("baseball_pickle")

The complement to to_pickle is the read_pickle function, which restores the pickle to a DataFrame or Series:


In [165]:
pd.read_pickle("baseball_pickle")


Out[165]:
          player  year  stint team  lg    g   ab   r    h  X2b  X3b  hr  rbi  \
id                                                                             
88641  womacto01  2006      2  CHN  NL   19   50   6   14    1    0   1    2   
88643  schilcu01  2006      1  BOS  AL   31    2   0    1    0    0   0    0   
88645  myersmi01  2006      1  NYA  AL   62    0   0    0    0    0   0    0   
88649  helliri01  2006      1  MIL  NL   20    3   0    0    0    0   0    0   
88650  johnsra05  2006      1  NYA  AL   33    6   0    1    0    0   0    0   
88652  finlest01  2006      1  SFN  NL  139  426  66  105   21   12   6   40   
88653  gonzalu01  2006      1  ARI  NL  153  586  93  159   52    2  15   73   
88662   seleaa01  2006      1  LAN  NL   28   26   2    5    1    0   0    0   
89177  francju01  2007      2  ATL  NL   15   40   1   10    3    0   0    8   
89178  francju01  2007      1  NYN  NL   40   50   7   10    0    0   1    8   
...          ...   ...    ...  ...  ..  ...  ...  ..  ...  ...  ...  ..  ...   
89499  claytro01  2007      1  TOR  AL   69  189  23   48   14    0   1   12   
89501  cirilje01  2007      2  ARI  NL   28   40   6    8    4    0   0    6   
89502  cirilje01  2007      1  MIN  AL   50  153  18   40    9    2   2   21   
89521  bondsba01  2007      1  SFN  NL  126  340  75   94   14    0  28   66   
89523  biggicr01  2007      1  HOU  NL  141  517  68  130   31    3  10   50   
89525  benitar01  2007      2  FLO  NL   34    0   0    0    0    0   0    0   
89526  benitar01  2007      1  SFN  NL   19    0   0    0    0    0   0    0   
89530  ausmubr01  2007      1  HOU  NL  117  349  38   82   16    3   3   25   
89533   aloumo01  2007      1  NYN  NL   87  328  51  112   19    1  13   49   
89534  alomasa02  2007      1  NYN  NL    8   22   1    3    1    0   0    0   

       sb  cs   bb   so  ibb  hbp  sh  sf  gidp  
id                                               
88641   1   1    4    4    0    0   3   0     0  
88643   0   0    0    1    0    0   0   0     0  
88645   0   0    0    0    0    0   0   0     0  
88649   0   0    0    2    0    0   0   0     0  
88650   0   0    0    4    0    0   0   0     0  
88652   7   0   46   55    2    2   3   4     6  
88653   0   1   69   58   10    7   0   6    14  
88662   0   0    1    7    0    0   6   0     1  
89177   0   0    4   10    1    0   0   1     1  
89178   2   1   10   13    0    0   0   1     1  
...    ..  ..  ...  ...  ...  ...  ..  ..   ...  
89499   2   1   14   50    0    1   3   3     8  
89501   0   0    4    6    0    0   0   0     1  
89502   2   0   15   13    0    1   3   2     9  
89521   5   0  132   54   43    3   0   2    13  
89523   4   3   23  112    0    3   7   5     5  
89525   0   0    0    0    0    0   0   0     0  
89526   0   0    0    0    0    0   0   0     0  
89530   6   1   37   74    3    6   4   1    11  
89533   3   0   27   30    5    2   0   3    13  
89534   0   0    0    3    0    0   0   0     0  

[100 rows x 22 columns]

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.