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]:
import pandas as pd
import numpy as np

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 [2]:
counts = pd.Series([632, 1638, 569, 115])
counts
Out[2]:
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 [3]:
counts.values
Out[3]:
array([ 632, 1638,  569,  115])
In [4]:
counts.index
Out[4]:
RangeIndex(start=0, stop=4, step=1)

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

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

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

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

In [6]:
bacteria['Actinobacteria']
Out[6]:
569
In [7]:
bacteria[[name.endswith('bacteria') for name in bacteria.index]]
Out[7]:
Proteobacteria    1638
Actinobacteria     569
dtype: int64
In [8]:
[name.endswith('bacteria') for name in bacteria.index]
Out[8]:
[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 [9]:
bacteria[0]
Out[9]:
632

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

In [10]:
bacteria.name = 'counts'
bacteria.index.name = 'phylum'
bacteria
Out[10]:
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 [11]:
np.log(bacteria)
Out[11]:
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 [12]:
bacteria[bacteria>1000]
Out[12]:
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 [13]:
bacteria_dict = {'Firmicutes': 632, 'Proteobacteria': 1638, 'Actinobacteria': 569,
                 'Bacteroidetes': 115}
pd.Series(bacteria_dict)
Out[13]:
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 [14]:
bacteria2 = pd.Series(bacteria_dict, 
                      index=['Cyanobacteria','Firmicutes',
                             'Proteobacteria','Actinobacteria'])
bacteria2
Out[14]:
Cyanobacteria        NaN
Firmicutes         632.0
Proteobacteria    1638.0
Actinobacteria     569.0
dtype: float64
In [15]:
bacteria2.isnull()
Out[15]:
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 [16]:
bacteria + bacteria2
Out[16]:
Actinobacteria    1138.0
Bacteroidetes        NaN
Cyanobacteria        NaN
Firmicutes        1264.0
Proteobacteria    3276.0
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 [17]:
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[17]:
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 [18]:
data[['phylum','value','patient']]
Out[18]:
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 [19]:
data.columns
Out[19]:
Index(['patient', 'phylum', 'value'], dtype='object')

The dtypes attribute reveals the data type for each column in our DataFrame.

  • int64 is numeric integer values
  • object strings (letters and numbers)
  • float64 floating-point values
In [20]:
data.dtypes
Out[20]:
patient     int64
phylum     object
value       int64
dtype: object

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

In [21]:
data['patient']
Out[21]:
0    1
1    1
2    1
3    1
4    2
5    2
6    2
7    2
Name: patient, dtype: int64
In [22]:
data.patient
Out[22]:
0    1
1    1
2    1
3    1
4    2
5    2
6    2
7    2
Name: patient, dtype: int64
In [23]:
type(data.value)
Out[23]:
pandas.core.series.Series
In [24]:
data[['value']]
Out[24]:
value
0 632
1 1638
2 569
3 115
4 433
5 1130
6 754
7 555

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 loc attribute.

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

Exercise

Try out these commands to see what they return:

  • data.head()
  • data.tail(3)
  • data.shape

An alternative way of initializing a DataFrame is with a list of dicts:

In [26]:
data = pd.DataFrame([{'patient': 1, 'phylum': 'Firmicutes', 'value': 632},
                    {'patient': 1, 'phylum': 'Proteobacteria', 'value': 1638},
                    {'patient': 1, 'phylum': 'Actinobacteria', 'value': 569},
                    {'patient': 1, 'phylum': 'Bacteroidetes', 'value': 115},
                    {'patient': 2, 'phylum': 'Firmicutes', 'value': 433},
                    {'patient': 2, 'phylum': 'Proteobacteria', 'value': 1130},
                    {'patient': 2, 'phylum': 'Actinobacteria', 'value': 754},
                    {'patient': 2, 'phylum': 'Bacteroidetes', 'value': 555}])
In [27]:
data
Out[27]:
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 indexed 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 [28]:
vals = data.value
vals
Out[28]:
0     632
1    1638
2     569
3     115
4     433
5    1130
6     754
7     555
Name: value, dtype: int64
In [29]:
vals[5] = 0
vals
/Users/fonnescj/anaconda3/envs/dev/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.
Out[29]:
0     632
1    1638
2     569
3     115
4     433
5       0
6     754
7     555
Name: value, dtype: int64

If we plan on modifying an extracted Series, its a good idea to make a copy.

In [30]:
vals = data.value.copy()
vals[5] = 1000
data
Out[30]:
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 [31]:
data.value[[3,4,6]] = [14, 21, 5]
data
/Users/fonnescj/anaconda3/envs/dev/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.
Out[31]:
patient phylum value
0 1 Firmicutes 632
1 1 Proteobacteria 1638
2 1 Actinobacteria 569
3 1 Bacteroidetes 14
4 2 Firmicutes 21
5 2 Proteobacteria 0
6 2 Actinobacteria 5
7 2 Bacteroidetes 555
In [32]:
data['year'] = 2013
data
Out[32]:
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 21 2013
5 2 Proteobacteria 0 2013
6 2 Actinobacteria 5 2013
7 2 Bacteroidetes 555 2013

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

In [33]:
data.treatment = 1
data
Out[33]:
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 21 2013
5 2 Proteobacteria 0 2013
6 2 Actinobacteria 5 2013
7 2 Bacteroidetes 555 2013
In [34]:
data.treatment
Out[34]:
1

Exercise

From the data table above, create an index to return all rows for which the phylum name ends in "bacteria" and the value is greater than 1000.

In [35]:
# Write your answer here 

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

In [36]:
treatment = pd.Series([0]*4 + [1]*2)
treatment
Out[36]:
0    0
1    0
2    0
3    0
4    1
5    1
dtype: int64
In [37]:
data['treatment'] = treatment
data
Out[37]:
patient phylum value year treatment
0 1 Firmicutes 632 2013 0.0
1 1 Proteobacteria 1638 2013 0.0
2 1 Actinobacteria 569 2013 0.0
3 1 Bacteroidetes 14 2013 0.0
4 2 Firmicutes 21 2013 1.0
5 2 Proteobacteria 0 2013 1.0
6 2 Actinobacteria 5 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 [38]:
month = ['Jan', 'Feb', 'Mar', 'Apr']
data['month'] = month
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-38-360d03fdde9a> in <module>()
      1 month = ['Jan', 'Feb', 'Mar', 'Apr']
----> 2 data['month'] = month

~/anaconda3/envs/dev/lib/python3.6/site-packages/pandas/core/frame.py in __setitem__(self, key, value)
   2329         else:
   2330             # set column
-> 2331             self._set_item(key, value)
   2332 
   2333     def _setitem_slice(self, key, value):

~/anaconda3/envs/dev/lib/python3.6/site-packages/pandas/core/frame.py in _set_item(self, key, value)
   2395 
   2396         self._ensure_valid_index(value)
-> 2397         value = self._sanitize_column(key, value)
   2398         NDFrame._set_item(self, key, value)
   2399 

~/anaconda3/envs/dev/lib/python3.6/site-packages/pandas/core/frame.py in _sanitize_column(self, key, value, broadcast)
   2566 
   2567             # turn me into an ndarray
-> 2568             value = _sanitize_index(value, self.index, copy=False)
   2569             if not isinstance(value, (np.ndarray, Index)):
   2570                 if isinstance(value, list) and len(value) > 0:

~/anaconda3/envs/dev/lib/python3.6/site-packages/pandas/core/series.py in _sanitize_index(data, index, copy)
   2877 
   2878     if len(data) != len(index):
-> 2879         raise ValueError('Length of values does not match length of ' 'index')
   2880 
   2881     if isinstance(data, PeriodIndex):

ValueError: Length of values does not match length of index
In [39]:
data['month'] = ['Jan']*len(data)
data
Out[39]:
patient phylum value year treatment month
0 1 Firmicutes 632 2013 0.0 Jan
1 1 Proteobacteria 1638 2013 0.0 Jan
2 1 Actinobacteria 569 2013 0.0 Jan
3 1 Bacteroidetes 14 2013 0.0 Jan
4 2 Firmicutes 21 2013 1.0 Jan
5 2 Proteobacteria 0 2013 1.0 Jan
6 2 Actinobacteria 5 2013 NaN Jan
7 2 Bacteroidetes 555 2013 NaN Jan

We can use the drop method to remove rows or columns, which by default drops rows. We can be explicit by using the axis argument:

In [40]:
data.drop('month', axis=1)
data
Out[40]:
patient phylum value year treatment month
0 1 Firmicutes 632 2013 0.0 Jan
1 1 Proteobacteria 1638 2013 0.0 Jan
2 1 Actinobacteria 569 2013 0.0 Jan
3 1 Bacteroidetes 14 2013 0.0 Jan
4 2 Firmicutes 21 2013 1.0 Jan
5 2 Proteobacteria 0 2013 1.0 Jan
6 2 Actinobacteria 5 2013 NaN Jan
7 2 Bacteroidetes 555 2013 NaN Jan

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

In [41]:
data.values
Out[41]:
array([[1, 'Firmicutes', 632, 2013, 0.0, 'Jan'],
       [1, 'Proteobacteria', 1638, 2013, 0.0, 'Jan'],
       [1, 'Actinobacteria', 569, 2013, 0.0, 'Jan'],
       [1, 'Bacteroidetes', 14, 2013, 0.0, 'Jan'],
       [2, 'Firmicutes', 21, 2013, 1.0, 'Jan'],
       [2, 'Proteobacteria', 0, 2013, 1.0, 'Jan'],
       [2, 'Actinobacteria', 5, 2013, nan, 'Jan'],
       [2, 'Bacteroidetes', 555, 2013, nan, 'Jan']], 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 [42]:
df = pd.DataFrame({'foo': [1,2,3], 'bar':[0.4, -1.0, 4.5]})
df.values
Out[42]:
array([[ 0.4,  1. ],
       [-1. ,  2. ],
       [ 4.5,  3. ]])

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

In [43]:
data.index
Out[43]:
RangeIndex(start=0, stop=8, step=1)

Index objects are immutable:

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

~/anaconda3/envs/dev/lib/python3.6/site-packages/pandas/core/indexes/base.py in __setitem__(self, key, value)
   1668 
   1669     def __setitem__(self, key, value):
-> 1670         raise TypeError("Index does not support mutable operations")
   1671 
   1672     def __getitem__(self, key):

TypeError: Index 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 [45]:
bacteria2.index = bacteria.index
In [46]:
bacteria2
Out[46]:
phylum
Firmicutes           NaN
Proteobacteria     632.0
Actinobacteria    1638.0
Bacteroidetes      569.0
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 [47]:
!cat ../data/microbiome.csv
Taxon,Patient,Group,Tissue,Stool
Firmicutes,1,0,136,4182
Firmicutes,2,1,1174,703
Firmicutes,3,0,408,3946
Firmicutes,4,1,831,8605
Firmicutes,5,0,693,50
Firmicutes,6,1,718,717
Firmicutes,7,0,173,33
Firmicutes,8,1,228,80
Firmicutes,9,0,162,3196
Firmicutes,10,1,372,32
Firmicutes,11,0,4255,4361
Firmicutes,12,1,107,1667
Firmicutes,13,0,96,223
Firmicutes,14,1,281,2377
Proteobacteria,1,0,2469,1821
Proteobacteria,2,1,839,661
Proteobacteria,3,0,4414,18
Proteobacteria,4,1,12044,83
Proteobacteria,5,0,2310,12
Proteobacteria,6,1,3053,547
Proteobacteria,7,0,395,2174
Proteobacteria,8,1,2651,767
Proteobacteria,9,0,1195,76
Proteobacteria,10,1,6857,795
Proteobacteria,11,0,483,666
Proteobacteria,12,1,2950,3994
Proteobacteria,13,0,1541,816
Proteobacteria,14,1,1307,53
Actinobacteria,1,0,1590,4
Actinobacteria,2,1,25,2
Actinobacteria,3,0,259,300
Actinobacteria,4,1,568,7
Actinobacteria,5,0,1102,9
Actinobacteria,6,1,678,377
Actinobacteria,7,0,260,58
Actinobacteria,8,1,424,233
Actinobacteria,9,0,548,21
Actinobacteria,10,1,201,83
Actinobacteria,11,0,42,75
Actinobacteria,12,1,109,59
Actinobacteria,13,0,51,183
Actinobacteria,14,1,310,204
Bacteroidetes,1,0,67,0
Bacteroidetes,2,1,0,0
Bacteroidetes,3,0,85,5
Bacteroidetes,4,1,143,7
Bacteroidetes,5,0,678,2
Bacteroidetes,6,1,4829,209
Bacteroidetes,7,0,74,651
Bacteroidetes,8,1,169,254
Bacteroidetes,9,0,106,10
Bacteroidetes,10,1,73,381
Bacteroidetes,11,0,30,359
Bacteroidetes,12,1,51,51
Bacteroidetes,13,0,2473,2314
Bacteroidetes,14,1,102,33
Other,1,0,195,18
Other,2,1,42,2
Other,3,0,316,43
Other,4,1,202,40
Other,5,0,116,0
Other,6,1,527,12
Other,7,0,357,11
Other,8,1,106,11
Other,9,0,67,14
Other,10,1,203,6
Other,11,0,392,6
Other,12,1,28,25
Other,13,0,12,22
Other,14,1,305,32

This table can be read into a DataFrame using read_csv:

In [48]:
mb = pd.read_csv("../data/microbiome.csv")
mb
Out[48]:
Taxon Patient Group Tissue Stool
0 Firmicutes 1 0 136 4182
1 Firmicutes 2 1 1174 703
2 Firmicutes 3 0 408 3946
3 Firmicutes 4 1 831 8605
4 Firmicutes 5 0 693 50
5 Firmicutes 6 1 718 717
6 Firmicutes 7 0 173 33
7 Firmicutes 8 1 228 80
8 Firmicutes 9 0 162 3196
9 Firmicutes 10 1 372 32
10 Firmicutes 11 0 4255 4361
11 Firmicutes 12 1 107 1667
12 Firmicutes 13 0 96 223
13 Firmicutes 14 1 281 2377
14 Proteobacteria 1 0 2469 1821
15 Proteobacteria 2 1 839 661
16 Proteobacteria 3 0 4414 18
17 Proteobacteria 4 1 12044 83
18 Proteobacteria 5 0 2310 12
19 Proteobacteria 6 1 3053 547
20 Proteobacteria 7 0 395 2174
21 Proteobacteria 8 1 2651 767
22 Proteobacteria 9 0 1195 76
23 Proteobacteria 10 1 6857 795
24 Proteobacteria 11 0 483 666
25 Proteobacteria 12 1 2950 3994
26 Proteobacteria 13 0 1541 816
27 Proteobacteria 14 1 1307 53
28 Actinobacteria 1 0 1590 4
29 Actinobacteria 2 1 25 2
... ... ... ... ... ...
40 Actinobacteria 13 0 51 183
41 Actinobacteria 14 1 310 204
42 Bacteroidetes 1 0 67 0
43 Bacteroidetes 2 1 0 0
44 Bacteroidetes 3 0 85 5
45 Bacteroidetes 4 1 143 7
46 Bacteroidetes 5 0 678 2
47 Bacteroidetes 6 1 4829 209
48 Bacteroidetes 7 0 74 651
49 Bacteroidetes 8 1 169 254
50 Bacteroidetes 9 0 106 10
51 Bacteroidetes 10 1 73 381
52 Bacteroidetes 11 0 30 359
53 Bacteroidetes 12 1 51 51
54 Bacteroidetes 13 0 2473 2314
55 Bacteroidetes 14 1 102 33
56 Other 1 0 195 18
57 Other 2 1 42 2
58 Other 3 0 316 43
59 Other 4 1 202 40
60 Other 5 0 116 0
61 Other 6 1 527 12
62 Other 7 0 357 11
63 Other 8 1 106 11
64 Other 9 0 67 14
65 Other 10 1 203 6
66 Other 11 0 392 6
67 Other 12 1 28 25
68 Other 13 0 12 22
69 Other 14 1 305 32

70 rows × 5 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 [49]:
pd.read_csv("../data/microbiome.csv", header=None).head()
Out[49]:
0 1 2 3 4
0 Taxon Patient Group Tissue Stool
1 Firmicutes 1 0 136 4182
2 Firmicutes 2 1 1174 703
3 Firmicutes 3 0 408 3946
4 Firmicutes 4 1 831 8605

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

In [50]:
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 [51]:
mb = pd.read_csv("../data/microbiome.csv", index_col=['Patient','Taxon'])
mb.head()
Out[51]:
Group Tissue Stool
Patient Taxon
1 Firmicutes 0 136 4182
2 Firmicutes 1 1174 703
3 Firmicutes 0 408 3946
4 Firmicutes 1 831 8605
5 Firmicutes 0 693 50

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

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 [52]:
pd.read_csv("../data/microbiome.csv", skiprows=[3,4,6]).head()
Out[52]:
Taxon Patient Group Tissue Stool
0 Firmicutes 1 0 136 4182
1 Firmicutes 2 1 1174 703
2 Firmicutes 5 0 693 50
3 Firmicutes 7 0 173 33
4 Firmicutes 8 1 228 80

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

In [53]:
pd.read_csv("../data/microbiome.csv", nrows=4)
Out[53]:
Taxon Patient Group Tissue Stool
0 Firmicutes 1 0 136 4182
1 Firmicutes 2 1 1174 703
2 Firmicutes 3 0 408 3946
3 Firmicutes 4 1 831 8605

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 [54]:
data_chunks = pd.read_csv("../data/microbiome.csv", chunksize=14)

mean_tissue = pd.Series({chunk.iloc[0].Taxon:chunk.Tissue.mean() for chunk in data_chunks})
    
mean_tissue
Out[54]:
Actinobacteria     440.500000
Bacteroidetes      634.285714
Firmicutes         688.142857
Other              204.857143
Proteobacteria    3036.285714
dtype: float64

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 [55]:
!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 [56]:
pd.read_csv("../data/microbiome_missing.csv").head(20)
Out[56]:
Taxon Patient Tissue Stool
0 Firmicutes 1 632 305.0
1 Firmicutes 2 136 4182.0
2 Firmicutes 3 NaN 703.0
3 Firmicutes 4 408 3946.0
4 Firmicutes 5 831 8605.0
5 Firmicutes 6 693 50.0
6 Firmicutes 7 718 717.0
7 Firmicutes 8 173 33.0
8 Firmicutes 9 228 NaN
9 Firmicutes 10 162 3196.0
10 Firmicutes 11 372 -99999.0
11 Firmicutes 12 4255 4361.0
12 Firmicutes 13 107 1667.0
13 Firmicutes 14 ? 223.0
14 Firmicutes 15 281 2377.0
15 Proteobacteria 1 1638 3886.0
16 Proteobacteria 2 2469 1821.0
17 Proteobacteria 3 839 661.0
18 Proteobacteria 4 4414 18.0
19 Proteobacteria 5 12044 83.0

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

In [57]:
pd.isnull(pd.read_csv("../data/microbiome_missing.csv")).head(20)
Out[57]:
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 [58]:
pd.read_csv("../data/microbiome_missing.csv", na_values=['?', -99999]).head(20)
Out[58]:
Taxon Patient Tissue Stool
0 Firmicutes 1 632.0 305.0
1 Firmicutes 2 136.0 4182.0
2 Firmicutes 3 NaN 703.0
3 Firmicutes 4 408.0 3946.0
4 Firmicutes 5 831.0 8605.0
5 Firmicutes 6 693.0 50.0
6 Firmicutes 7 718.0 717.0
7 Firmicutes 8 173.0 33.0
8 Firmicutes 9 228.0 NaN
9 Firmicutes 10 162.0 3196.0
10 Firmicutes 11 372.0 NaN
11 Firmicutes 12 4255.0 4361.0
12 Firmicutes 13 107.0 1667.0
13 Firmicutes 14 NaN 223.0
14 Firmicutes 15 281.0 2377.0
15 Proteobacteria 1 1638.0 3886.0
16 Proteobacteria 2 2469.0 1821.0
17 Proteobacteria 3 839.0 661.0
18 Proteobacteria 4 4414.0 18.0
19 Proteobacteria 5 12044.0 83.0

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

The read_excel convenience function in pandas imports a specific sheet from an Excel file

In [59]:
mb = pd.read_excel('../data/microbiome/MID2.xls', sheetname='Sheet 1', header=None)
mb.head()
Out[59]:
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 [60]:
baseball = pd.read_csv("../data/baseball.csv", index_col='id')
baseball.head()
Out[60]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
id
88641 womacto01 2006 2 CHN NL 19 50 6 14 1 ... 2.0 1.0 1.0 4 4.0 0.0 0.0 3.0 0.0 0.0
88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
88649 helliri01 2006 1 MIL NL 20 3 0 0 0 ... 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0 0.0 0.0
88650 johnsra05 2006 1 NYA AL 33 6 0 1 0 ... 0.0 0.0 0.0 0 4.0 0.0 0.0 0.0 0.0 0.0

5 rows × 22 columns

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 [61]:
player_id = baseball.player + baseball.year.astype(str)
baseball_newind = baseball.copy()
baseball_newind.index = player_id
baseball_newind.head()
Out[61]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
womacto012006 womacto01 2006 2 CHN NL 19 50 6 14 1 ... 2.0 1.0 1.0 4 4.0 0.0 0.0 3.0 0.0 0.0
schilcu012006 schilcu01 2006 1 BOS AL 31 2 0 1 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
myersmi012006 myersmi01 2006 1 NYA AL 62 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
helliri012006 helliri01 2006 1 MIL NL 20 3 0 0 0 ... 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0 0.0 0.0
johnsra052006 johnsra05 2006 1 NYA AL 33 6 0 1 0 ... 0.0 0.0 0.0 0 4.0 0.0 0.0 0.0 0.0 0.0

5 rows × 22 columns

This looks okay, but let's check:

In [62]:
baseball_newind.index.is_unique
Out[62]:
False

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

In [63]:
pd.Series(baseball_newind.index).value_counts()
Out[63]:
cirilje012007    2
loftoke012007    2
benitar012007    2
trachst012007    2
claytro012007    2
wickmbo012007    2
hernaro012007    2
sweenma012007    2
wellsda012007    2
coninje012007    2
francju012007    2
gomezch022007    2
rogerke012007    1
mesajo012007     1
johnsra052007    1
vizquom012007    1
mabryjo012007    1
thomafr042007    1
alomasa022007    1
stantmi022007    1
biggicr012007    1
tavarju012007    1
johnsra052006    1
gonzalu012007    1
valenjo032007    1
bondsba012007    1
zaungr012007     1
parkch012007     1
sprinru012007    1
helliri012006    1
                ..
stinnke012007    1
walketo042007    1
ausmubr012007    1
sheffga012007    1
finlest012007    1
hoffmtr012007    1
glavito022007    1
oliveda022007    1
sosasa012007     1
maddugr012007    1
schmija012007    1
villoro012007    1
gordoto012007    1
delgaca012007    1
myersmi012006    1
whitero022007    1
schilcu012006    1
wakefti012007    1
whiteri012007    1
williwo022007    1
witasja012007    1
seleaa012007     1
piazzmi012007    1
gonzalu012006    1
schilcu012007    1
weathda012007    1
mussimi012007    1
smoltjo012007    1
griffke022007    1
stairma012007    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 [64]:
baseball_newind.loc['wickmbo012007']
Out[64]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
wickmbo012007 wickmbo01 2007 2 ARI NL 8 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
wickmbo012007 wickmbo01 2007 1 ATL NL 47 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0

2 rows × 22 columns

We will learn more about indexing below.

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

In [65]:
player_unique = baseball.player + baseball.team + baseball.year.astype(str)
baseball_newind = baseball.copy()
baseball_newind.index = player_unique
baseball_newind.head()
Out[65]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
womacto01CHN2006 womacto01 2006 2 CHN NL 19 50 6 14 1 ... 2.0 1.0 1.0 4 4.0 0.0 0.0 3.0 0.0 0.0
schilcu01BOS2006 schilcu01 2006 1 BOS AL 31 2 0 1 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
myersmi01NYA2006 myersmi01 2006 1 NYA AL 62 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
helliri01MIL2006 helliri01 2006 1 MIL NL 20 3 0 0 0 ... 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0 0.0 0.0
johnsra05NYA2006 johnsra05 2006 1 NYA AL 33 6 0 1 0 ... 0.0 0.0 0.0 0 4.0 0.0 0.0 0.0 0.0 0.0

5 rows × 22 columns

In [66]:
baseball_newind.index.is_unique
Out[66]:
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 [67]:
baseball.reindex(baseball.index[::-1]).head()
Out[67]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
id
89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 ... 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0 0.0 0.0
89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 ... 49.0 3.0 0.0 27 30.0 5.0 2.0 0.0 3.0 13.0
89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 ... 25.0 6.0 1.0 37 74.0 3.0 6.0 4.0 1.0 11.0
89526 benitar01 2007 1 SFN NL 19 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89525 benitar01 2007 2 FLO NL 34 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0

5 rows × 22 columns

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 [68]:
id_range = range(baseball.index.values.min(), baseball.index.values.max())
baseball.reindex(id_range).head()
Out[68]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
id
88641 womacto01 2006.0 2.0 CHN NL 19.0 50.0 6.0 14.0 1.0 ... 2.0 1.0 1.0 4.0 4.0 0.0 0.0 3.0 0.0 0.0
88642 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
88643 schilcu01 2006.0 1.0 BOS AL 31.0 2.0 0.0 1.0 0.0 ... 0.0 0.0 0.0 0.0 1.0 0.0 0.0 0.0 0.0 0.0
88644 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
88645 myersmi01 2006.0 1.0 NYA AL 62.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0

5 rows × 22 columns

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

In [69]:
baseball.reindex(id_range, method='ffill').head()
Out[69]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
id
88641 womacto01 2006 2 CHN NL 19 50 6 14 1 ... 2.0 1.0 1.0 4 4.0 0.0 0.0 3.0 0.0 0.0
88642 womacto01 2006 2 CHN NL 19 50 6 14 1 ... 2.0 1.0 1.0 4 4.0 0.0 0.0 3.0 0.0 0.0
88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
88644 schilcu01 2006 1 BOS AL 31 2 0 1 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0

5 rows × 22 columns

In [70]:
baseball.reindex(id_range, fill_value='charliebrown', columns=['player']).head()
Out[70]:
player
id
88641 womacto01
88642 charliebrown
88643 schilcu01
88644 charliebrown
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 [71]:
baseball.shape
Out[71]:
(100, 22)
In [72]:
baseball.drop([89525, 89526])
Out[72]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
id
88641 womacto01 2006 2 CHN NL 19 50 6 14 1 ... 2.0 1.0 1.0 4 4.0 0.0 0.0 3.0 0.0 0.0
88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
88649 helliri01 2006 1 MIL NL 20 3 0 0 0 ... 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0 0.0 0.0
88650 johnsra05 2006 1 NYA AL 33 6 0 1 0 ... 0.0 0.0 0.0 0 4.0 0.0 0.0 0.0 0.0 0.0
88652 finlest01 2006 1 SFN NL 139 426 66 105 21 ... 40.0 7.0 0.0 46 55.0 2.0 2.0 3.0 4.0 6.0
88653 gonzalu01 2006 1 ARI NL 153 586 93 159 52 ... 73.0 0.0 1.0 69 58.0 10.0 7.0 0.0 6.0 14.0
88662 seleaa01 2006 1 LAN NL 28 26 2 5 1 ... 0.0 0.0 0.0 1 7.0 0.0 0.0 6.0 0.0 1.0
89177 francju01 2007 2 ATL NL 15 40 1 10 3 ... 8.0 0.0 0.0 4 10.0 1.0 0.0 0.0 1.0 1.0
89178 francju01 2007 1 NYN NL 40 50 7 10 0 ... 8.0 2.0 1.0 10 13.0 0.0 0.0 0.0 1.0 1.0
89330 zaungr01 2007 1 TOR AL 110 331 43 80 24 ... 52.0 0.0 0.0 51 55.0 8.0 2.0 1.0 6.0 9.0
89333 witasja01 2007 1 TBA AL 3 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89334 williwo02 2007 1 HOU NL 33 59 3 6 0 ... 2.0 0.0 0.0 0 25.0 0.0 0.0 5.0 0.0 1.0
89335 wickmbo01 2007 2 ARI NL 8 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89336 wickmbo01 2007 1 ATL NL 47 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89337 whitero02 2007 1 MIN AL 38 109 8 19 4 ... 20.0 0.0 0.0 6 19.0 0.0 3.0 0.0 1.0 2.0
89338 whiteri01 2007 1 HOU NL 20 1 0 0 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
89339 wellsda01 2007 2 LAN NL 7 15 2 4 1 ... 1.0 0.0 0.0 0 6.0 0.0 0.0 0.0 0.0 0.0
89340 wellsda01 2007 1 SDN NL 22 38 1 4 0 ... 0.0 0.0 0.0 0 12.0 0.0 0.0 4.0 0.0 0.0
89341 weathda01 2007 1 CIN NL 67 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89343 walketo04 2007 1 OAK AL 18 48 5 13 1 ... 4.0 0.0 0.0 2 4.0 0.0 0.0 0.0 2.0 2.0
89345 wakefti01 2007 1 BOS AL 1 2 0 0 0 ... 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0 0.0 0.0
89347 vizquom01 2007 1 SFN NL 145 513 54 126 18 ... 51.0 14.0 6.0 44 48.0 6.0 1.0 14.0 3.0 14.0
89348 villoro01 2007 1 NYA AL 6 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89352 valenjo03 2007 1 NYN NL 51 166 18 40 11 ... 18.0 2.0 1.0 15 28.0 4.0 0.0 1.0 1.0 5.0
89354 trachst01 2007 2 CHN NL 4 7 0 1 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
89355 trachst01 2007 1 BAL AL 3 5 0 0 0 ... 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0 0.0 0.0
89359 timlimi01 2007 1 BOS AL 4 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89360 thomeji01 2007 1 CHA AL 130 432 79 119 19 ... 96.0 0.0 1.0 95 134.0 11.0 6.0 0.0 3.0 10.0
89361 thomafr04 2007 1 TOR AL 155 531 63 147 30 ... 95.0 0.0 0.0 81 94.0 3.0 7.0 0.0 5.0 14.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
89451 hernaro01 2007 2 LAN NL 22 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89452 hernaro01 2007 1 CLE AL 2 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89460 guarded01 2007 1 CIN NL 15 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89462 griffke02 2007 1 CIN NL 144 528 78 146 24 ... 93.0 6.0 1.0 85 99.0 14.0 1.0 0.0 9.0 14.0
89463 greensh01 2007 1 NYN NL 130 446 62 130 30 ... 46.0 11.0 1.0 37 62.0 4.0 5.0 1.0 1.0 14.0
89464 graffto01 2007 1 MIL NL 86 231 34 55 8 ... 30.0 0.0 1.0 24 44.0 6.0 3.0 0.0 2.0 7.0
89465 gordoto01 2007 1 PHI NL 44 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89466 gonzalu01 2007 1 LAN NL 139 464 70 129 23 ... 68.0 6.0 2.0 56 56.0 4.0 4.0 0.0 2.0 11.0
89467 gomezch02 2007 2 CLE AL 19 53 4 15 2 ... 5.0 0.0 0.0 0 6.0 0.0 0.0 1.0 1.0 1.0
89468 gomezch02 2007 1 BAL AL 73 169 17 51 10 ... 16.0 1.0 2.0 10 20.0 1.0 0.0 5.0 1.0 5.0
89469 glavito02 2007 1 NYN NL 33 56 3 12 1 ... 4.0 0.0 0.0 6 5.0 0.0 0.0 12.0 1.0 0.0
89473 floydcl01 2007 1 CHN NL 108 282 40 80 10 ... 45.0 0.0 0.0 35 47.0 5.0 5.0 0.0 0.0 6.0
89474 finlest01 2007 1 COL NL 43 94 9 17 3 ... 2.0 0.0 0.0 8 4.0 1.0 0.0 0.0 0.0 2.0
89480 embreal01 2007 1 OAK AL 4 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89481 edmonji01 2007 1 SLN NL 117 365 39 92 15 ... 53.0 0.0 2.0 41 75.0 2.0 0.0 2.0 3.0 9.0
89482 easleda01 2007 1 NYN NL 76 193 24 54 6 ... 26.0 0.0 1.0 19 35.0 1.0 5.0 0.0 1.0 2.0
89489 delgaca01 2007 1 NYN NL 139 538 71 139 30 ... 87.0 4.0 0.0 52 118.0 8.0 11.0 0.0 6.0 12.0
89493 cormirh01 2007 1 CIN NL 6 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89494 coninje01 2007 2 NYN NL 21 41 2 8 2 ... 5.0 0.0 0.0 7 8.0 2.0 0.0 1.0 1.0 1.0
89495 coninje01 2007 1 CIN NL 80 215 23 57 11 ... 32.0 4.0 0.0 20 28.0 0.0 0.0 1.0 6.0 4.0
89497 clemero02 2007 1 NYA AL 2 2 0 1 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89498 claytro01 2007 2 BOS AL 8 6 1 0 0 ... 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0 0.0 2.0
89499 claytro01 2007 1 TOR AL 69 189 23 48 14 ... 12.0 2.0 1.0 14 50.0 0.0 1.0 3.0 3.0 8.0
89501 cirilje01 2007 2 ARI NL 28 40 6 8 4 ... 6.0 0.0 0.0 4 6.0 0.0 0.0 0.0 0.0 1.0
89502 cirilje01 2007 1 MIN AL 50 153 18 40 9 ... 21.0 2.0 0.0 15 13.0 0.0 1.0 3.0 2.0 9.0
89521 bondsba01 2007 1 SFN NL 126 340 75 94 14 ... 66.0 5.0 0.0 132 54.0 43.0 3.0 0.0 2.0 13.0
89523 biggicr01 2007 1 HOU NL 141 517 68 130 31 ... 50.0 4.0 3.0 23 112.0 0.0 3.0 7.0 5.0 5.0
89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 ... 25.0 6.0 1.0 37 74.0 3.0 6.0 4.0 1.0 11.0
89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 ... 49.0 3.0 0.0 27 30.0 5.0 2.0 0.0 3.0 13.0
89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 ... 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0 0.0 0.0

98 rows × 22 columns

In [73]:
baseball.drop(['ibb','hbp'], axis=1)
Out[73]:
player year stint team lg g ab r h X2b X3b hr rbi sb cs bb so sh sf gidp
id
88641 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 2.0 1.0 1.0 4 4.0 3.0 0.0 0.0
88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0
88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
88649 helliri01 2006 1 MIL NL 20 3 0 0 0 0 0 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0
88650 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 0 0.0 0.0 0.0 0 4.0 0.0 0.0 0.0
88652 finlest01 2006 1 SFN NL 139 426 66 105 21 12 6 40.0 7.0 0.0 46 55.0 3.0 4.0 6.0
88653 gonzalu01 2006 1 ARI NL 153 586 93 159 52 2 15 73.0 0.0 1.0 69 58.0 0.0 6.0 14.0
88662 seleaa01 2006 1 LAN NL 28 26 2 5 1 0 0 0.0 0.0 0.0 1 7.0 6.0 0.0 1.0
89177 francju01 2007 2 ATL NL 15 40 1 10 3 0 0 8.0 0.0 0.0 4 10.0 0.0 1.0 1.0
89178 francju01 2007 1 NYN NL 40 50 7 10 0 0 1 8.0 2.0 1.0 10 13.0 0.0 1.0 1.0
89330 zaungr01 2007 1 TOR AL 110 331 43 80 24 1 10 52.0 0.0 0.0 51 55.0 1.0 6.0 9.0
89333 witasja01 2007 1 TBA AL 3 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89334 williwo02 2007 1 HOU NL 33 59 3 6 0 0 1 2.0 0.0 0.0 0 25.0 5.0 0.0 1.0
89335 wickmbo01 2007 2 ARI NL 8 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89336 wickmbo01 2007 1 ATL NL 47 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89337 whitero02 2007 1 MIN AL 38 109 8 19 4 0 4 20.0 0.0 0.0 6 19.0 0.0 1.0 2.0
89338 whiteri01 2007 1 HOU NL 20 1 0 0 0 0 0 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0
89339 wellsda01 2007 2 LAN NL 7 15 2 4 1 0 0 1.0 0.0 0.0 0 6.0 0.0 0.0 0.0
89340 wellsda01 2007 1 SDN NL 22 38 1 4 0 0 0 0.0 0.0 0.0 0 12.0 4.0 0.0 0.0
89341 weathda01 2007 1 CIN NL 67 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89343 walketo04 2007 1 OAK AL 18 48 5 13 1 0 0 4.0 0.0 0.0 2 4.0 0.0 2.0 2.0
89345 wakefti01 2007 1 BOS AL 1 2 0 0 0 0 0 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0
89347 vizquom01 2007 1 SFN NL 145 513 54 126 18 3 4 51.0 14.0 6.0 44 48.0 14.0 3.0 14.0
89348 villoro01 2007 1 NYA AL 6 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89352 valenjo03 2007 1 NYN NL 51 166 18 40 11 1 3 18.0 2.0 1.0 15 28.0 1.0 1.0 5.0
89354 trachst01 2007 2 CHN NL 4 7 0 1 0 0 0 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0
89355 trachst01 2007 1 BAL AL 3 5 0 0 0 0 0 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0
89359 timlimi01 2007 1 BOS AL 4 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89360 thomeji01 2007 1 CHA AL 130 432 79 119 19 0 35 96.0 0.0 1.0 95 134.0 0.0 3.0 10.0
89361 thomafr04 2007 1 TOR AL 155 531 63 147 30 0 26 95.0 0.0 0.0 81 94.0 0.0 5.0 14.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
89460 guarded01 2007 1 CIN NL 15 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89462 griffke02 2007 1 CIN NL 144 528 78 146 24 1 30 93.0 6.0 1.0 85 99.0 0.0 9.0 14.0
89463 greensh01 2007 1 NYN NL 130 446 62 130 30 1 10 46.0 11.0 1.0 37 62.0 1.0 1.0 14.0
89464 graffto01 2007 1 MIL NL 86 231 34 55 8 0 9 30.0 0.0 1.0 24 44.0 0.0 2.0 7.0
89465 gordoto01 2007 1 PHI NL 44 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89466 gonzalu01 2007 1 LAN NL 139 464 70 129 23 2 15 68.0 6.0 2.0 56 56.0 0.0 2.0 11.0
89467 gomezch02 2007 2 CLE AL 19 53 4 15 2 0 0 5.0 0.0 0.0 0 6.0 1.0 1.0 1.0
89468 gomezch02 2007 1 BAL AL 73 169 17 51 10 1 1 16.0 1.0 2.0 10 20.0 5.0 1.0 5.0
89469 glavito02 2007 1 NYN NL 33 56 3 12 1 0 0 4.0 0.0 0.0 6 5.0 12.0 1.0 0.0
89473 floydcl01 2007 1 CHN NL 108 282 40 80 10 1 9 45.0 0.0 0.0 35 47.0 0.0 0.0 6.0
89474 finlest01 2007 1 COL NL 43 94 9 17 3 0 1 2.0 0.0 0.0 8 4.0 0.0 0.0 2.0
89480 embreal01 2007 1 OAK AL 4 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89481 edmonji01 2007 1 SLN NL 117 365 39 92 15 2 12 53.0 0.0 2.0 41 75.0 2.0 3.0 9.0
89482 easleda01 2007 1 NYN NL 76 193 24 54 6 0 10 26.0 0.0 1.0 19 35.0 0.0 1.0 2.0
89489 delgaca01 2007 1 NYN NL 139 538 71 139 30 0 24 87.0 4.0 0.0 52 118.0 0.0 6.0 12.0
89493 cormirh01 2007 1 CIN NL 6 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89494 coninje01 2007 2 NYN NL 21 41 2 8 2 0 0 5.0 0.0 0.0 7 8.0 1.0 1.0 1.0
89495 coninje01 2007 1 CIN NL 80 215 23 57 11 1 6 32.0 4.0 0.0 20 28.0 1.0 6.0 4.0
89497 clemero02 2007 1 NYA AL 2 2 0 1 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89498 claytro01 2007 2 BOS AL 8 6 1 0 0 0 0 0.0 0.0 0.0 0 3.0 0.0 0.0 2.0
89499 claytro01 2007 1 TOR AL 69 189 23 48 14 0 1 12.0 2.0 1.0 14 50.0 3.0 3.0 8.0
89501 cirilje01 2007 2 ARI NL 28 40 6 8 4 0 0 6.0 0.0 0.0 4 6.0 0.0 0.0 1.0
89502 cirilje01 2007 1 MIN AL 50 153 18 40 9 2 2 21.0 2.0 0.0 15 13.0 3.0 2.0 9.0
89521 bondsba01 2007 1 SFN NL 126 340 75 94 14 0 28 66.0 5.0 0.0 132 54.0 0.0 2.0 13.0
89523 biggicr01 2007 1 HOU NL 141 517 68 130 31 3 10 50.0 4.0 3.0 23 112.0 7.0 5.0 5.0
89525 benitar01 2007 2 FLO NL 34 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89526 benitar01 2007 1 SFN NL 19 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0
89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 3 3 25.0 6.0 1.0 37 74.0 4.0 1.0 11.0
89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 1 13 49.0 3.0 0.0 27 30.0 0.0 3.0 13.0
89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 0 0 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0

100 rows × 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 [74]:
# Sample Series object
hits = baseball_newind.h
hits
Out[74]:
womacto01CHN2006     14
schilcu01BOS2006      1
myersmi01NYA2006      0
helliri01MIL2006      0
johnsra05NYA2006      1
finlest01SFN2006    105
gonzalu01ARI2006    159
seleaa01LAN2006       5
francju01ATL2007     10
francju01NYN2007     10
zaungr01TOR2007      80
witasja01TBA2007      0
williwo02HOU2007      6
wickmbo01ARI2007      0
wickmbo01ATL2007      0
whitero02MIN2007     19
whiteri01HOU2007      0
wellsda01LAN2007      4
wellsda01SDN2007      4
weathda01CIN2007      0
walketo04OAK2007     13
wakefti01BOS2007      0
vizquom01SFN2007    126
villoro01NYA2007      0
valenjo03NYN2007     40
trachst01CHN2007      1
trachst01BAL2007      0
timlimi01BOS2007      0
thomeji01CHA2007    119
thomafr04TOR2007    147
                   ... 
guarded01CIN2007      0
griffke02CIN2007    146
greensh01NYN2007    130
graffto01MIL2007     55
gordoto01PHI2007      0
gonzalu01LAN2007    129
gomezch02CLE2007     15
gomezch02BAL2007     51
glavito02NYN2007     12
floydcl01CHN2007     80
finlest01COL2007     17
embreal01OAK2007      0
edmonji01SLN2007     92
easleda01NYN2007     54
delgaca01NYN2007    139
cormirh01CIN2007      0
coninje01NYN2007      8
coninje01CIN2007     57
clemero02NYA2007      1
claytro01BOS2007      0
claytro01TOR2007     48
cirilje01ARI2007      8
cirilje01MIN2007     40
bondsba01SFN2007     94
biggicr01HOU2007    130
benitar01FLO2007      0
benitar01SFN2007      0
ausmubr01HOU2007     82
aloumo01NYN2007     112
alomasa02NYN2007      3
Name: h, Length: 100, dtype: int64
In [75]:
# Numpy-style indexing
hits[:3]
Out[75]:
womacto01CHN2006    14
schilcu01BOS2006     1
myersmi01NYA2006     0
Name: h, dtype: int64
In [76]:
# Indexing by label
hits[['womacto01CHN2006','schilcu01BOS2006']]
Out[76]:
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 [77]:
hits['womacto01CHN2006':'gonzalu01ARI2006']
Out[77]:
womacto01CHN2006     14
schilcu01BOS2006      1
myersmi01NYA2006      0
helliri01MIL2006      0
johnsra05NYA2006      1
finlest01SFN2006    105
gonzalu01ARI2006    159
Name: h, dtype: int64
In [78]:
hits['womacto01CHN2006':'gonzalu01ARI2006'] = 5
hits
/Users/fonnescj/anaconda3/envs/dev/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.
Out[78]:
womacto01CHN2006      5
schilcu01BOS2006      5
myersmi01NYA2006      5
helliri01MIL2006      5
johnsra05NYA2006      5
finlest01SFN2006      5
gonzalu01ARI2006      5
seleaa01LAN2006       5
francju01ATL2007     10
francju01NYN2007     10
zaungr01TOR2007      80
witasja01TBA2007      0
williwo02HOU2007      6
wickmbo01ARI2007      0
wickmbo01ATL2007      0
whitero02MIN2007     19
whiteri01HOU2007      0
wellsda01LAN2007      4
wellsda01SDN2007      4
weathda01CIN2007      0
walketo04OAK2007     13
wakefti01BOS2007      0
vizquom01SFN2007    126
villoro01NYA2007      0
valenjo03NYN2007     40
trachst01CHN2007      1
trachst01BAL2007      0
timlimi01BOS2007      0
thomeji01CHA2007    119
thomafr04TOR2007    147
                   ... 
guarded01CIN2007      0
griffke02CIN2007    146
greensh01NYN2007    130
graffto01MIL2007     55
gordoto01PHI2007      0
gonzalu01LAN2007    129
gomezch02CLE2007     15
gomezch02BAL2007     51
glavito02NYN2007     12
floydcl01CHN2007     80
finlest01COL2007     17
embreal01OAK2007      0
edmonji01SLN2007     92
easleda01NYN2007     54
delgaca01NYN2007    139
cormirh01CIN2007      0
coninje01NYN2007      8
coninje01CIN2007     57
clemero02NYA2007      1
claytro01BOS2007      0
claytro01TOR2007     48
cirilje01ARI2007      8
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 [79]:
baseball_newind[['h','ab']]
Out[79]:
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
zaungr01TOR2007 80 331
witasja01TBA2007 0 0
williwo02HOU2007 6 59
wickmbo01ARI2007 0 0
wickmbo01ATL2007 0 0
whitero02MIN2007 19 109
whiteri01HOU2007 0 1
wellsda01LAN2007 4 15
wellsda01SDN2007 4 38
weathda01CIN2007 0 0
walketo04OAK2007 13 48
wakefti01BOS2007 0 2
vizquom01SFN2007 126 513
villoro01NYA2007 0 0
valenjo03NYN2007 40 166
trachst01CHN2007 1 7
trachst01BAL2007 0 5
timlimi01BOS2007 0 0
thomeji01CHA2007 119 432
thomafr04TOR2007 147 531
... ... ...
guarded01CIN2007 0 0
griffke02CIN2007 146 528
greensh01NYN2007 130 446
graffto01MIL2007 55 231
gordoto01PHI2007 0 0
gonzalu01LAN2007 129 464
gomezch02CLE2007 15 53
gomezch02BAL2007 51 169
glavito02NYN2007 12 56
floydcl01CHN2007 80 282
finlest01COL2007 17 94
embreal01OAK2007 0 0
edmonji01SLN2007 92 365
easleda01NYN2007 54 193
delgaca01NYN2007 139 538
cormirh01CIN2007 0 0
coninje01NYN2007 8 41
coninje01CIN2007 57 215
clemero02NYA2007 1 2
claytro01BOS2007 0 6
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 × 2 columns

In [80]:
baseball_newind[baseball_newind.ab>500]
Out[80]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
gonzalu01ARI2006 gonzalu01 2006 1 ARI NL 153 586 93 5 52 ... 73.0 0.0 1.0 69 58.0 10.0 7.0 0.0 6.0 14.0
vizquom01SFN2007 vizquom01 2007 1 SFN NL 145 513 54 126 18 ... 51.0 14.0 6.0 44 48.0 6.0 1.0 14.0 3.0 14.0
thomafr04TOR2007 thomafr04 2007 1 TOR AL 155 531 63 147 30 ... 95.0 0.0 0.0 81 94.0 3.0 7.0 0.0 5.0 14.0
rodriiv01DET2007 rodriiv01 2007 1 DET AL 129 502 50 141 31 ... 63.0 2.0 2.0 9 96.0 1.0 1.0 1.0 2.0 16.0
griffke02CIN2007 griffke02 2007 1 CIN NL 144 528 78 146 24 ... 93.0 6.0 1.0 85 99.0 14.0 1.0 0.0 9.0 14.0
delgaca01NYN2007 delgaca01 2007 1 NYN NL 139 538 71 139 30 ... 87.0 4.0 0.0 52 118.0 8.0 11.0 0.0 6.0 12.0
biggicr01HOU2007 biggicr01 2007 1 HOU NL 141 517 68 130 31 ... 50.0 4.0 3.0 23 112.0 0.0 3.0 7.0 5.0 5.0

7 rows × 22 columns

For a more concise (and readable) syntax, we can use the new query method to perform selection on a DataFrame. Instead of having to type the fully-specified column, we can simply pass a string that describes what to select. The query above is then simply:

In [81]:
baseball_newind.query('ab > 500')
Out[81]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
gonzalu01ARI2006 gonzalu01 2006 1 ARI NL 153 586 93 5 52 ... 73.0 0.0 1.0 69 58.0 10.0 7.0 0.0 6.0 14.0
vizquom01SFN2007 vizquom01 2007 1 SFN NL 145 513 54 126 18 ... 51.0 14.0 6.0 44 48.0 6.0 1.0 14.0 3.0 14.0
thomafr04TOR2007 thomafr04 2007 1 TOR AL 155 531 63 147 30 ... 95.0 0.0 0.0 81 94.0 3.0 7.0 0.0 5.0 14.0
rodriiv01DET2007 rodriiv01 2007 1 DET AL 129 502 50 141 31 ... 63.0 2.0 2.0 9 96.0 1.0 1.0 1.0 2.0 16.0
griffke02CIN2007 griffke02 2007 1 CIN NL 144 528 78 146 24 ... 93.0 6.0 1.0 85 99.0 14.0 1.0 0.0 9.0 14.0
delgaca01NYN2007 delgaca01 2007 1 NYN NL 139 538 71 139 30 ... 87.0 4.0 0.0 52 118.0 8.0 11.0 0.0 6.0 12.0
biggicr01HOU2007 biggicr01 2007 1 HOU NL 141 517 68 130 31 ... 50.0 4.0 3.0 23 112.0 0.0 3.0 7.0 5.0 5.0

7 rows × 22 columns

The DataFrame.index and DataFrame.columns are placed in the query namespace by default. If you want to refer to a variable in the current namespace, you can prefix the variable with @:

In [82]:
min_ab = 450
In [83]:
baseball_newind.query('ab > @min_ab')
Out[83]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
gonzalu01ARI2006 gonzalu01 2006 1 ARI NL 153 586 93 5 52 ... 73.0 0.0 1.0 69 58.0 10.0 7.0 0.0 6.0 14.0
vizquom01SFN2007 vizquom01 2007 1 SFN NL 145 513 54 126 18 ... 51.0 14.0 6.0 44 48.0 6.0 1.0 14.0 3.0 14.0
thomafr04TOR2007 thomafr04 2007 1 TOR AL 155 531 63 147 30 ... 95.0 0.0 0.0 81 94.0 3.0 7.0 0.0 5.0 14.0
sheffga01DET2007 sheffga01 2007 1 DET AL 133 494 107 131 20 ... 75.0 22.0 5.0 84 71.0 2.0 9.0 0.0 6.0 10.0
rodriiv01DET2007 rodriiv01 2007 1 DET AL 129 502 50 141 31 ... 63.0 2.0 2.0 9 96.0 1.0 1.0 1.0 2.0 16.0
ramirma02BOS2007 ramirma02 2007 1 BOS AL 133 483 84 143 33 ... 88.0 0.0 0.0 71 92.0 13.0 7.0 0.0 8.0 21.0
kentje01LAN2007 kentje01 2007 1 LAN NL 136 494 78 149 36 ... 79.0 1.0 3.0 57 61.0 4.0 5.0 0.0 6.0 17.0
griffke02CIN2007 griffke02 2007 1 CIN NL 144 528 78 146 24 ... 93.0 6.0 1.0 85 99.0 14.0 1.0 0.0 9.0 14.0
gonzalu01LAN2007 gonzalu01 2007 1 LAN NL 139 464 70 129 23 ... 68.0 6.0 2.0 56 56.0 4.0 4.0 0.0 2.0 11.0
delgaca01NYN2007 delgaca01 2007 1 NYN NL 139 538 71 139 30 ... 87.0 4.0 0.0 52 118.0 8.0 11.0 0.0 6.0 12.0
biggicr01HOU2007 biggicr01 2007 1 HOU NL 141 517 68 130 31 ... 50.0 4.0 3.0 23 112.0 0.0 3.0 7.0 5.0 5.0

11 rows × 22 columns

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

In [84]:
baseball_newind.loc['gonzalu01ARI2006', ['h','X2b', 'X3b', 'hr']]
Out[84]:
h       5
X2b    52
X3b     2
hr     15
Name: gonzalu01ARI2006, dtype: object
In [85]:
baseball_newind.loc[:'myersmi01NYA2006', 'hr']
Out[85]:
womacto01CHN2006    1
schilcu01BOS2006    0
myersmi01NYA2006    0
Name: hr, dtype: int64

In addition to using loc to select rows and columns by label, pandas also allows indexing by position using the iloc attribute.

So, we can query rows and columns by absolute position, rather than by name:

In [86]:
baseball_newind.iloc[:5, 5:8]
Out[86]:
g ab r
womacto01CHN2006 19 50 6
schilcu01BOS2006 31 2 0
myersmi01NYA2006 62 0 0
helliri01MIL2006 20 3 0
johnsra05NYA2006 33 6 0

Exercise

You can use the isin method query a DataFrame based upon a list of values as follows:

data['phylum'].isin(['Firmacutes', 'Bacteroidetes'])

Use isin to find all players that played for the Los Angeles Dodgers (LAN) or the San Francisco Giants (SFN). How many records contain these values?

In [87]:
# Write your answer here

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. First, let's (artificially) construct two Series, consisting of home runs hit in years 2006 and 2007, respectively:

In [88]:
hr2006 = baseball.loc[baseball.year==2006, 'hr']
hr2006.index = baseball.player[baseball.year==2006]

hr2007 = baseball.loc[baseball.year==2007, 'hr']
hr2007.index = baseball.player[baseball.year==2007]
In [89]:
hr2007
Out[89]:
player
francju01     0
francju01     1
zaungr01     10
witasja01     0
williwo02     1
wickmbo01     0
wickmbo01     0
whitero02     4
whiteri01     0
wellsda01     0
wellsda01     0
weathda01     0
walketo04     0
wakefti01     0
vizquom01     4
villoro01     0
valenjo03     3
trachst01     0
trachst01     0
timlimi01     0
thomeji01    35
thomafr04    26
tavarju01     0
sweenma01     0
sweenma01     2
suppaje01     0
stinnke01     1
stantmi02     0
stairma01    21
sprinru01     0
             ..
guarded01     0
griffke02    30
greensh01    10
graffto01     9
gordoto01     0
gonzalu01    15
gomezch02     0
gomezch02     1
glavito02     0
floydcl01     9
finlest01     1
embreal01     0
edmonji01    12
easleda01    10
delgaca01    24
cormirh01     0
coninje01     0
coninje01     6
clemero02     0
claytro01     0
claytro01     1
cirilje01     0
cirilje01     2
bondsba01    28
biggicr01    10
benitar01     0
benitar01     0
ausmubr01     3
aloumo01     13
alomasa02     0
Name: hr, Length: 92, dtype: int64

Now, let's add them together, in hopes of getting 2-year home run totals:

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
cirilje01     NaN
claytro01     NaN
claytro01     NaN
clemero02     NaN
coninje01     NaN
coninje01     NaN
cormirh01     NaN
delgaca01     NaN
easleda01     NaN
edmonji01     NaN
embreal01     NaN
finlest01     7.0
floydcl01     NaN
francju01     NaN
francju01     NaN
glavito02     NaN
gomezch02     NaN
gomezch02     NaN
gonzalu01    30.0
gordoto01     NaN
graffto01     NaN
greensh01     NaN
             ... 
sosasa01      NaN
sprinru01     NaN
stairma01     NaN
stantmi02     NaN
stinnke01     NaN
suppaje01     NaN
sweenma01     NaN
sweenma01     NaN
tavarju01     NaN
thomafr04     NaN
thomeji01     NaN
timlimi01     NaN
trachst01     NaN
trachst01     NaN
valenjo03     NaN
villoro01     NaN
vizquom01     NaN
wakefti01     NaN
walketo04     NaN
weathda01     NaN
wellsda01     NaN
wellsda01     NaN
whiteri01     NaN
whitero02     NaN
wickmbo01     NaN
wickmbo01     NaN
williwo02     NaN
witasja01     NaN
womacto01     NaN
zaungr01      NaN
Name: hr, 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.0
gonzalu01    30.0
johnsra05     0.0
myersmi01     0.0
schilcu01     0.0
seleaa01      0.0
Name: hr, 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.0
aloumo01     13.0
ausmubr01     3.0
benitar01     0.0
benitar01     0.0
biggicr01    10.0
bondsba01    28.0
cirilje01     0.0
cirilje01     2.0
claytro01     0.0
claytro01     1.0
clemero02     0.0
coninje01     0.0
coninje01     6.0
cormirh01     0.0
delgaca01    24.0
easleda01    10.0
edmonji01    12.0
embreal01     0.0
finlest01     7.0
floydcl01     9.0
francju01     0.0
francju01     1.0
glavito02     0.0
gomezch02     0.0
gomezch02     1.0
gonzalu01    30.0
gordoto01     0.0
graffto01     9.0
greensh01    10.0
             ... 
sosasa01     21.0
sprinru01     0.0
stairma01    21.0
stantmi02     0.0
stinnke01     1.0
suppaje01     0.0
sweenma01     0.0
sweenma01     2.0
tavarju01     0.0
thomafr04    26.0
thomeji01    35.0
timlimi01     0.0
trachst01     0.0
trachst01     0.0
valenjo03     3.0
villoro01     0.0
vizquom01     4.0
wakefti01     0.0
walketo04     0.0
weathda01     0.0
wellsda01     0.0
wellsda01     0.0
whiteri01     0.0
whitero02     4.0
wickmbo01     0.0
wickmbo01     0.0
williwo02     1.0
witasja01     0.0
womacto01     1.0
zaungr01     10.0
Name: hr, 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
89177   -35
89178   -34
89330   -25
89333   -35
89334   -34
89335   -35
89336   -35
89337   -31
89338   -35
89339   -35
89340   -35
89341   -35
89343   -35
89345   -35
89347   -31
89348   -35
89352   -32
89354   -35
89355   -35
89359   -35
89360     0
89361    -9
         ..
89460   -35
89462    -5
89463   -25
89464   -26
89465   -35
89466   -20
89467   -35
89468   -34
89469   -35
89473   -26
89474   -34
89480   -35
89481   -23
89482   -25
89489   -11
89493   -35
89494   -35
89495   -29
89497   -35
89498   -35
89499   -34
89501   -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.loc[89521, "player"]
Out[94]:
'bondsba01'
In [95]:
stats = baseball[['h','X2b', 'X3b', 'hr']]
diff = stats - stats.loc[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.0
X2b    1.0
X3b    0.0
hr     0.0
dtype: float64
In [97]:
def range_calc(x):
    return x.max() - x.min()
In [98]:
stat_range = lambda x: x.max() - x.min()
stats.apply(stat_range)
Out[98]:
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 [99]:
def slugging(x): 
    bases = x['h']-x['X2b']-x['X3b']-x['hr'] + 2*x['X2b'] + 3*x['X3b'] + 4*x['hr']
    ab = x['ab']+1e-6
    
    return bases/ab

baseball.apply(slugging, axis=1).round(3)
Out[99]:
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
89177    0.325
89178    0.260
89330    0.411
89333    0.000
89334    0.153
89335    0.000
89336    0.000
89337    0.321
89338    0.000
89339    0.333
89340    0.105
89341    0.000
89343    0.292
89345    0.000
89347    0.316
89348    0.000
89352    0.373
89354    0.143
89355    0.000
89359    0.000
89360    0.562
89361    0.480
         ...  
89460    0.000
89462    0.496
89463    0.430
89464    0.390
89465    0.000
89466    0.433
89467    0.321
89468    0.391
89469    0.232
89473    0.422
89474    0.245
89480    0.000
89481    0.403
89482    0.466
89489    0.448
89493    0.000
89494    0.244
89495    0.409
89497    0.500
89498    0.000
89499    0.344
89501    0.300
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: float64

Sorting and Ranking

Pandas objects include methods for re-ordering data.

In [100]:
baseball_newind.sort_index().head()
Out[100]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
alomasa02NYN2007 alomasa02 2007 1 NYN NL 8 22 1 3 1 ... 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0 0.0 0.0
aloumo01NYN2007 aloumo01 2007 1 NYN NL 87 328 51 112 19 ... 49.0 3.0 0.0 27 30.0 5.0 2.0 0.0 3.0 13.0
ausmubr01HOU2007 ausmubr01 2007 1 HOU NL 117 349 38 82 16 ... 25.0 6.0 1.0 37 74.0 3.0 6.0 4.0 1.0 11.0
benitar01FLO2007 benitar01 2007 2 FLO NL 34 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
benitar01SFN2007 benitar01 2007 1 SFN NL 19 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0

5 rows × 22 columns

In [101]:
baseball_newind.sort_index(ascending=False).head()
Out[101]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
zaungr01TOR2007 zaungr01 2007 1 TOR AL 110 331 43 80 24 ... 52.0 0.0 0.0 51 55.0 8.0 2.0 1.0 6.0 9.0
womacto01CHN2006 womacto01 2006 2 CHN NL 19 50 6 5 1 ... 2.0 1.0 1.0 4 4.0 0.0 0.0 3.0 0.0 0.0
witasja01TBA2007 witasja01 2007 1 TBA AL 3 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
williwo02HOU2007 williwo02 2007 1 HOU NL 33 59 3 6 0 ... 2.0 0.0 0.0 0 25.0 0.0 0.0 5.0 0.0 1.0
wickmbo01ATL2007 wickmbo01 2007 1 ATL NL 47 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0

5 rows × 22 columns

Try sorting the columns instead of the rows, in ascending order:

In [102]:
baseball_newind.sort_index(axis=1).head()
Out[102]:
X2b X3b ab bb cs g gidp h hbp hr ... player r rbi sb sf sh so stint team year
womacto01CHN2006 1 0 50 4 1.0 19 0.0 5 0.0 1 ... womacto01 6 2.0 1.0 0.0 3.0 4.0 2 CHN 2006
schilcu01BOS2006 0 0 2 0 0.0 31 0.0 5 0.0 0 ... schilcu01 0 0.0 0.0 0.0 0.0 1.0 1 BOS 2006
myersmi01NYA2006 0 0 0 0 0.0 62 0.0 5 0.0 0 ... myersmi01 0 0.0 0.0 0.0 0.0 0.0 1 NYA 2006
helliri01MIL2006 0 0 3 0 0.0 20 0.0 5 0.0 0 ... helliri01 0 0.0 0.0 0.0 0.0 2.0 1 MIL 2006
johnsra05NYA2006 0 0 6 0 0.0 33 0.0 5 0.0 0 ... johnsra05 0 0.0 0.0 0.0 0.0 4.0 1 NYA 2006

5 rows × 22 columns

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

In [103]:
baseball.hr.sort_values()
Out[103]:
id
89534     0
89365     0
89469     0
89367     0
89370     0
89467     0
89372     0
89465     0
89375     0
89460     0
89381     0
89382     0
89452     0
89363     0
89384     0
89388     0
89450     0
89445     0
89442     0
89431     0
89402     0
89406     0
89410     0
89411     0
89412     0
89420     0
89421     0
89451     0
89425     0
89429     0
         ..
89530     3
89352     3
89337     4
89347     4
89438     6
89495     6
88652     6
89430     7
89398     8
89473     9
89464     9
89482    10
89463    10
89330    10
89523    10
89389    11
89481    12
89533    13
89466    15
88653    15
89439    20
89396    20
89374    21
89371    21
89489    24
89378    25
89361    26
89521    28
89462    30
89360    35
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_values:

In [104]:
baseball[['player','sb','cs']].sort_values(ascending=[False,True], 
                                           by=['sb', 'cs']).head(10)
Out[104]:
player sb cs
id
89378 sheffga01 22.0 5.0
89430 loftoke01 21.0 4.0
89347 vizquom01 14.0 6.0
89463 greensh01 11.0 1.0
88652 finlest01 7.0 0.0
89462 griffke02 6.0 1.0
89530 ausmubr01 6.0 1.0
89466 gonzalu01 6.0 2.0
89521 bondsba01 5.0 0.0
89438 kleskry01 5.0 1.0

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

In [105]:
baseball.hr.rank()
Out[105]:
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
89177     29.0
89178     62.5
89330     83.5
89333     29.0
89334     62.5
89335     29.0
89336     29.0
89337     73.5
89338     29.0
89339     29.0
89340     29.0
89341     29.0
89343     29.0
89345     29.0
89347     73.5
89348     29.0
89352     71.5
89354     29.0
89355     29.0
89359     29.0
89360    100.0
89361     97.0
         ...  
89460     29.0
89462     99.0
89463     83.5
89464     80.5
89465     29.0
89466     89.5
89467     29.0
89468     62.5
89469     29.0
89473     80.5
89474     62.5
89480     29.0
89481     87.0
89482     83.5
89489     95.0
89493     29.0
89494     29.0
89495     76.0
89497     29.0
89498     29.0
89499     62.5
89501     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 [106]:
pd.Series([100,100]).rank()
Out[106]:
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 [107]:
baseball.hr.rank(method='first')
Out[107]:
id
88641     58.0
88643      1.0
88645      2.0
88649      3.0
88650      4.0
88652     75.0
88653     89.0
88662      5.0
89177      6.0
89178     59.0
89330     82.0
89333      7.0
89334     60.0
89335      8.0
89336      9.0
89337     73.0
89338     10.0
89339     11.0
89340     12.0
89341     13.0
89343     14.0
89345     15.0
89347     74.0
89348     16.0
89352     71.0
89354     17.0
89355     18.0
89359     19.0
89360    100.0
89361     97.0
         ...  
89460     45.0
89462     99.0
89463     83.0
89464     80.0
89465     46.0
89466     90.0
89467     47.0
89468     65.0
89469     48.0
89473     81.0
89474     66.0
89480     49.0
89481     87.0
89482     84.0
89489     95.0
89493     50.0
89494     51.0
89495     77.0
89497     52.0
89498     53.0
89499     67.0
89501     54.0
89502     70.0
89521     98.0
89523     85.0
89525     55.0
89526     56.0
89530     72.0
89533     88.0
89534     57.0
Name: hr, Length: 100, dtype: float64

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

In [108]:
baseball.rank(ascending=False).head()
Out[108]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
id
88641 2.0 96.5 7.0 82.0 31.5 70.0 47.5 40.5 39.0 50.5 ... 51.0 24.5 17.5 44.5 59.0 66.0 65.5 16.0 70.0 76.5
88643 37.5 96.5 57.0 88.0 81.5 55.5 73.0 81.0 63.5 78.0 ... 78.5 63.5 62.5 79.0 73.0 66.0 65.5 67.5 70.0 76.5
88645 47.5 96.5 57.0 40.5 81.5 36.0 91.0 81.0 84.5 78.0 ... 78.5 63.5 62.5 79.0 89.0 66.0 65.5 67.5 70.0 76.5
88649 66.0 96.5 57.0 47.0 31.5 67.5 69.0 81.0 84.5 78.0 ... 78.5 63.5 62.5 79.0 67.0 66.0 65.5 67.5 70.0 76.5
88650 61.5 96.5 57.0 40.5 81.5 51.0 64.5 81.0 63.5 78.0 ... 78.5 63.5 62.5 79.0 59.0 66.0 65.5 67.5 70.0 76.5

5 rows × 22 columns

In [109]:
baseball[['r','h','hr']].rank(ascending=False).head()
Out[109]:
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 [110]:
# 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 [111]:
baseball_h = baseball.set_index(['year', 'team', 'player'])
baseball_h.head(10)
Out[111]:
stint lg g ab r h X2b X3b hr rbi sb cs bb so ibb hbp sh sf gidp
year team player
2006 CHN womacto01 2 NL 19 50 6 14 1 0 1 2.0 1.0 1.0 4 4.0 0.0 0.0 3.0 0.0 0.0
BOS schilcu01 1 AL 31 2 0 1 0 0 0 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
NYA myersmi01 1 AL 62 0 0 0 0 0 0 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
MIL helliri01 1 NL 20 3 0 0 0 0 0 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0 0.0 0.0
NYA johnsra05 1 AL 33 6 0 1 0 0 0 0.0 0.0 0.0 0 4.0 0.0 0.0 0.0 0.0 0.0
SFN finlest01 1 NL 139 426 66 105 21 12 6 40.0 7.0 0.0 46 55.0 2.0 2.0 3.0 4.0 6.0
ARI gonzalu01 1 NL 153 586 93 159 52 2 15 73.0 0.0 1.0 69 58.0 10.0 7.0 0.0 6.0 14.0
LAN seleaa01 1 NL 28 26 2 5 1 0 0 0.0 0.0 0.0 1 7.0 0.0 0.0 6.0 0.0 1.0
2007 ATL francju01 2 NL 15 40 1 10 3 0 0 8.0 0.0 0.0 4 10.0 1.0 0.0 0.0 1.0 1.0
NYN francju01 1 NL 40 50 7 10 0 0 1 8.0 2.0 1.0 10 13.0 0.0 0.0 0.0 1.0 1.0

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 [112]:
baseball_h.index[:10]
Out[112]:
MultiIndex(levels=[[2006, 2007], ['ARI', 'ATL', 'BAL', 'BOS', 'CHA', 'CHN', 'CIN', 'CLE', 'COL', 'DET', 'FLO', 'HOU', 'KCA', 'LAA', 'LAN', 'MIL', 'MIN', 'NYA', 'NYN', 'OAK', 'PHI', 'SDN', 'SFN', 'SLN', 'TBA', 'TEX', 'TOR'], ['alomasa02', 'aloumo01', 'ausmubr01', 'benitar01', 'biggicr01', 'bondsba01', 'cirilje01', 'claytro01', 'clemero02', 'coninje01', 'cormirh01', 'delgaca01', 'easleda01', 'edmonji01', 'embreal01', 'finlest01', 'floydcl01', 'francju01', 'glavito02', 'gomezch02', 'gonzalu01', 'gordoto01', 'graffto01', 'greensh01', 'griffke02', 'guarded01', 'helliri01', 'hernaro01', 'hoffmtr01', 'johnsra05', 'jonesto02', 'kentje01', 'kleskry01', 'loaizes01', 'loftoke01', 'mabryjo01', 'maddugr01', 'martipe02', 'mesajo01', 'moyerja01', 'mussimi01', 'myersmi01', 'oliveda02', 'parkch01', 'perezne01', 'piazzmi01', 'ramirma02', 'rodriiv01', 'rogerke01', 'sandere02', 'schilcu01', 'schmija01', 'seaneru01', 'seleaa01', 'sheffga01', 'smoltjo01', 'sosasa01', 'sprinru01', 'stairma01', 'stantmi02', 'stinnke01', 'suppaje01', 'sweenma01', 'tavarju01', 'thomafr04', 'thomeji01', 'timlimi01', 'trachst01', 'valenjo03', 'villoro01', 'vizquom01', 'wakefti01', 'walketo04', 'weathda01', 'wellsda01', 'whiteri01', 'whitero02', 'wickmbo01', 'williwo02', 'witasja01', 'womacto01', '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=['year', 'team', 'player'])
In [113]:
baseball_h.index.is_unique
Out[113]:
True

Try using this hierarchical index to retrieve Julio Franco (francju01), who played for the Atlanta Braves (ATL) in 2007:

In [114]:
baseball_h.loc[(2007, 'ATL', 'francju01')]
Out[114]:
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 [115]:
mb = pd.read_csv("../data/microbiome.csv", index_col=['Taxon','Patient'])
In [116]:
mb.head(10)
Out[116]:
Group Tissue Stool
Taxon Patient
Firmicutes 1 0 136 4182
2 1 1174 703
3 0 408 3946
4 1 831 8605
5 0 693 50
6 1 718 717
7 0 173 33
8 1 228 80
9 0 162 3196
10 1 372 32

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

In [117]:
mb.loc['Proteobacteria']
Out[117]:
Group Tissue Stool
Patient
1 0 2469 1821
2 1 839 661
3 0 4414 18
4 1 12044 83
5 0 2310 12
6 1 3053 547
7 0 395 2174
8 1 2651 767
9 0 1195 76
10 1 6857 795
11 0 483 666
12 1 2950 3994
13 0 1541 816
14 1 1307 53

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

In [118]:
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[118]:
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 [119]:
frame.index.names = ['key1', 'key2']
frame.columns.names = ['state', 'color']
frame
Out[119]:
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 [120]:
frame.loc['a', 'Ohio']
Out[120]:
color Green Red
key2
1 0 1
2 3 4

Try retrieving the value corresponding to b2 in Colorado:

In [121]:
# Write your answer here

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

In [122]:
mb.swaplevel('Patient', 'Taxon').head()
Out[122]:
Group Tissue Stool
Patient Taxon
1 Firmicutes 0 136 4182
2 Firmicutes 1 1174 703
3 Firmicutes 0 408 3946
4 Firmicutes 1 831 8605
5 Firmicutes 0 693 50

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

In [123]:
mb.sortlevel('Patient', ascending=False).head()
/Users/fonnescj/anaconda3/envs/dev/lib/python3.6/site-packages/ipykernel_launcher.py:1: FutureWarning: sortlevel is deprecated, use sort_index(level= ...)
  """Entry point for launching an IPython kernel.
Out[123]:
Group Tissue Stool
Taxon Patient
Proteobacteria 14 1 1307 53
Other 14 1 305 32
Firmicutes 14 1 281 2377
Bacteroidetes 14 1 102 33
Actinobacteria 14 1 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 [124]:
foo = pd.Series([np.nan, -3, None, 'foobar'])
foo
Out[124]:
0       NaN
1        -3
2      None
3    foobar
dtype: object
In [125]:
foo.isnull()
Out[125]:
0     True
1    False
2     True
3    False
dtype: bool

Missing values may be dropped or indexed out:

In [126]:
bacteria2
Out[126]:
phylum
Firmicutes           NaN
Proteobacteria     632.0
Actinobacteria    1638.0
Bacteroidetes      569.0
dtype: float64
In [127]:
bacteria2.dropna()
Out[127]:
phylum
Proteobacteria     632.0
Actinobacteria    1638.0
Bacteroidetes      569.0
dtype: float64
In [128]:
bacteria2.isnull()
Out[128]:
phylum
Firmicutes         True
Proteobacteria    False
Actinobacteria    False
Bacteroidetes     False
dtype: bool
In [129]:
bacteria2[bacteria2.notnull()]
Out[129]:
phylum
Proteobacteria     632.0
Actinobacteria    1638.0
Bacteroidetes      569.0
dtype: float64

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

In [130]:
data.dropna()
Out[130]:
patient phylum value year treatment month
0 1 Firmicutes 632 2013 0.0 Jan
1 1 Proteobacteria 1638 2013 0.0 Jan
2 1 Actinobacteria 569 2013 0.0 Jan
3 1 Bacteroidetes 14 2013 0.0 Jan
4 2 Firmicutes 21 2013 1.0 Jan
5 2 Proteobacteria 0 2013 1.0 Jan

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

In [131]:
data.dropna(how='all')
Out[131]:
patient phylum value year treatment month
0 1 Firmicutes 632 2013 0.0 Jan
1 1 Proteobacteria 1638 2013 0.0 Jan
2 1 Actinobacteria 569 2013 0.0 Jan
3 1 Bacteroidetes 14 2013 0.0 Jan
4 2 Firmicutes 21 2013 1.0 Jan
5 2 Proteobacteria 0 2013 1.0 Jan
6 2 Actinobacteria 5 2013 NaN Jan
7 2 Bacteroidetes 555 2013 NaN Jan

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

In [132]:
data.loc[7, 'year'] = np.nan
data
Out[132]:
patient phylum value year treatment month
0 1 Firmicutes 632 2013.0 0.0 Jan
1 1 Proteobacteria 1638 2013.0 0.0 Jan
2 1 Actinobacteria 569 2013.0 0.0 Jan
3 1 Bacteroidetes 14 2013.0 0.0 Jan
4 2 Firmicutes 21 2013.0 1.0 Jan
5 2 Proteobacteria 0 2013.0 1.0 Jan
6 2 Actinobacteria 5 2013.0 NaN Jan
7 2 Bacteroidetes 555 NaN NaN Jan
In [133]:
data.dropna(thresh=4)
Out[133]:
patient phylum value year treatment month
0 1 Firmicutes 632 2013.0 0.0 Jan
1 1 Proteobacteria 1638 2013.0 0.0 Jan
2 1 Actinobacteria 569 2013.0 0.0 Jan
3 1 Bacteroidetes 14 2013.0 0.0 Jan
4 2 Firmicutes 21 2013.0 1.0 Jan
5 2 Proteobacteria 0 2013.0 1.0 Jan
6 2 Actinobacteria 5 2013.0 NaN Jan
7 2 Bacteroidetes 555 NaN NaN Jan

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

Exercise

Try using the axis argument to drop columns with missing values:

In [134]:
# Write your answer here

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 [135]:
bacteria2.fillna(0)
Out[135]:
phylum
Firmicutes           0.0
Proteobacteria     632.0
Actinobacteria    1638.0
Bacteroidetes      569.0
dtype: float64
In [136]:
data.fillna({'year': 2013, 'treatment':2})
Out[136]:
patient phylum value year treatment month
0 1 Firmicutes 632 2013.0 0.0 Jan
1 1 Proteobacteria 1638 2013.0 0.0 Jan
2 1 Actinobacteria 569 2013.0 0.0 Jan
3 1 Bacteroidetes 14 2013.0 0.0 Jan
4 2 Firmicutes 21 2013.0 1.0 Jan
5 2 Proteobacteria 0 2013.0 1.0 Jan
6 2 Actinobacteria 5 2013.0 2.0 Jan
7 2 Bacteroidetes 555 2013.0 2.0 Jan

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

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

In [137]:
data.year.fillna(2013, inplace=True)
data
Out[137]:
patient phylum value year treatment month
0 1 Firmicutes 632 2013.0 0.0 Jan
1 1 Proteobacteria 1638 2013.0 0.0 Jan
2 1 Actinobacteria 569 2013.0 0.0 Jan
3 1 Bacteroidetes 14 2013.0 0.0 Jan
4 2 Firmicutes 21 2013.0 1.0 Jan
5 2 Proteobacteria 0 2013.0 1.0 Jan
6 2 Actinobacteria 5 2013.0 NaN Jan
7 2 Bacteroidetes 555 2013.0 NaN Jan

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

In [138]:
bacteria2.fillna(method='bfill')
Out[138]:
phylum
Firmicutes         632.0
Proteobacteria     632.0
Actinobacteria    1638.0
Bacteroidetes      569.0
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 [139]:
baseball.sum()
Out[139]:
player    womacto01schilcu01myersmi01helliri01johnsra05f...
year                                                 200692
stint                                                   113
team      CHNBOSNYAMILNYASFNARILANATLNYNTORTBAHOUARIATLM...
lg        NLALALNLALNLNLNLNLNLALALNLNLNLALNLNLNLNLALALNL...
g                                                      5238
ab                                                    13654
r                                                      1869
h                                                      3582
X2b                                                     739
X3b                                                      55
hr                                                      437
rbi                                                    1847
sb                                                      138
cs                                                       46
bb                                                     1549
so                                                     2408
ibb                                                     177
hbp                                                     112
sh                                                      138
sf                                                      120
gidp                                                    354
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 [140]:
baseball.mean()
Out[140]:
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 [141]:
bacteria2
Out[141]:
phylum
Firmicutes           NaN
Proteobacteria     632.0
Actinobacteria    1638.0
Bacteroidetes      569.0
dtype: float64
In [142]:
bacteria2.mean()
Out[142]:
946.3333333333334

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

In [143]:
bacteria2.mean(skipna=False)
Out[143]:
nan

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

In [144]:
extra_bases = baseball[['X2b','X3b','hr']].sum(axis=1)
extra_bases.sort_values(ascending=False)
Out[144]:
id
88653    69
89439    57
89361    56
89462    55
89396    54
89489    54
89360    54
89371    50
89378    46
89374    46
89389    45
89523    44
89521    42
89463    41
89466    40
88652    39
89438    36
89330    35
89533    33
89481    29
89430    26
89398    26
89347    25
89530    22
89473    20
89495    18
89464    17
89482    16
89499    15
89352    15
         ..
89498     0
89411     0
89525     0
89526     0
88650     0
88649     0
88645     0
88643     0
89341     0
89345     0
89381     0
89493     0
89450     0
89451     0
89372     0
89452     0
89370     0
89460     0
89367     0
89465     0
89384     0
89363     0
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 [145]:
baseball.describe()
Out[145]:
year stint g ab r h X2b X3b hr rbi sb cs bb so ibb hbp sh sf gidp
count 100.00000 100.000000 100.000000 100.000000 100.00000 100.000000 100.000000 100.000000 100.000000 100.00000 100.000000 100.000000 100.000000 100.000000 100.000000 100.00000 100.000000 100.000000 100.000000
mean 2006.92000 1.130000 52.380000 136.540000 18.69000 35.820000 7.390000 0.550000 4.370000 18.47000 1.380000 0.460000 15.490000 24.080000 1.770000 1.12000 1.380000 1.200000 3.540000
std 0.27266 0.337998 48.031299 181.936853 27.77496 50.221807 11.117277 1.445124 7.975537 28.34793 3.694878 1.067613 25.812649 32.804496 5.042957 2.23055 2.919042 2.035046 5.201826
min 2006.00000 1.000000 1.000000 0.000000 0.00000 0.000000 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 0.000000
25% 2007.00000 1.000000 9.500000 2.000000 0.00000 0.000000 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 0.000000 1.000000 0.000000 0.00000 0.000000 0.000000 0.000000
50% 2007.00000 1.000000 33.000000 40.500000 2.00000 8.000000 1.000000 0.000000 0.000000 2.00000 0.000000 0.000000 1.000000 7.000000 0.000000 0.00000 0.000000 0.000000 1.000000
75% 2007.00000 1.000000 83.250000 243.750000 33.25000 62.750000 11.750000 1.000000 6.000000 27.00000 1.000000 0.000000 19.250000 37.250000 1.250000 1.00000 1.000000 2.000000 6.000000
max 2007.00000 2.000000 155.000000 586.000000 107.00000 159.000000 52.000000 12.000000 35.000000 96.00000 22.000000 6.000000 132.000000 134.000000 43.000000 11.00000 14.000000 9.000000 21.000000

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

In [146]:
baseball.player.describe()
Out[146]:
count           100
unique           82
top       coninje01
freq              2
Name: player, 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 [147]:
baseball.hr.cov(baseball.X2b)
Out[147]:
69.076464646464643

$$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 [148]:
baseball.hr.corr(baseball.X2b)
Out[148]:
0.77906151825397507
In [149]:
baseball.ab.corr(baseball.h)
Out[149]:
0.99421740362723787

Try running corr on the entire baseball DataFrame to see what is returned:

In [150]:
# Write answer here

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 [151]:
mb.head()
Out[151]:
Group Tissue Stool
Taxon Patient
Firmicutes 1 0 136 4182
2 1 1174 703
3 0 408 3946
4 1 831 8605
5 0 693 50
In [152]:
mb.sum(level='Taxon')
Out[152]:
Group Tissue Stool
Taxon
Actinobacteria 7 6167 1615
Bacteroidetes 7 8880 4276
Firmicutes 7 9634 30172
Other 7 2868 242
Proteobacteria 7 42508 12483

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 [153]:
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 [154]:
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 [155]:
pd.read_pickle("baseball_pickle")
Out[155]:
player year stint team lg g ab r h X2b ... rbi sb cs bb so ibb hbp sh sf gidp
id
88641 womacto01 2006 2 CHN NL 19 50 6 14 1 ... 2.0 1.0 1.0 4 4.0 0.0 0.0 3.0 0.0 0.0
88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
88649 helliri01 2006 1 MIL NL 20 3 0 0 0 ... 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0 0.0 0.0
88650 johnsra05 2006 1 NYA AL 33 6 0 1 0 ... 0.0 0.0 0.0 0 4.0 0.0 0.0 0.0 0.0 0.0
88652 finlest01 2006 1 SFN NL 139 426 66 105 21 ... 40.0 7.0 0.0 46 55.0 2.0 2.0 3.0 4.0 6.0
88653 gonzalu01 2006 1 ARI NL 153 586 93 159 52 ... 73.0 0.0 1.0 69 58.0 10.0 7.0 0.0 6.0 14.0
88662 seleaa01 2006 1 LAN NL 28 26 2 5 1 ... 0.0 0.0 0.0 1 7.0 0.0 0.0 6.0 0.0 1.0
89177 francju01 2007 2 ATL NL 15 40 1 10 3 ... 8.0 0.0 0.0 4 10.0 1.0 0.0 0.0 1.0 1.0
89178 francju01 2007 1 NYN NL 40 50 7 10 0 ... 8.0 2.0 1.0 10 13.0 0.0 0.0 0.0 1.0 1.0
89330 zaungr01 2007 1 TOR AL 110 331 43 80 24 ... 52.0 0.0 0.0 51 55.0 8.0 2.0 1.0 6.0 9.0
89333 witasja01 2007 1 TBA AL 3 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89334 williwo02 2007 1 HOU NL 33 59 3 6 0 ... 2.0 0.0 0.0 0 25.0 0.0 0.0 5.0 0.0 1.0
89335 wickmbo01 2007 2 ARI NL 8 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89336 wickmbo01 2007 1 ATL NL 47 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89337 whitero02 2007 1 MIN AL 38 109 8 19 4 ... 20.0 0.0 0.0 6 19.0 0.0 3.0 0.0 1.0 2.0
89338 whiteri01 2007 1 HOU NL 20 1 0 0 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
89339 wellsda01 2007 2 LAN NL 7 15 2 4 1 ... 1.0 0.0 0.0 0 6.0 0.0 0.0 0.0 0.0 0.0
89340 wellsda01 2007 1 SDN NL 22 38 1 4 0 ... 0.0 0.0 0.0 0 12.0 0.0 0.0 4.0 0.0 0.0
89341 weathda01 2007 1 CIN NL 67 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89343 walketo04 2007 1 OAK AL 18 48 5 13 1 ... 4.0 0.0 0.0 2 4.0 0.0 0.0 0.0 2.0 2.0
89345 wakefti01 2007 1 BOS AL 1 2 0 0 0 ... 0.0 0.0 0.0 0 2.0 0.0 0.0 0.0 0.0 0.0
89347 vizquom01 2007 1 SFN NL 145 513 54 126 18 ... 51.0 14.0 6.0 44 48.0 6.0 1.0 14.0 3.0 14.0
89348 villoro01 2007 1 NYA AL 6 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89352 valenjo03 2007 1 NYN NL 51 166 18 40 11 ... 18.0 2.0 1.0 15 28.0 4.0 0.0 1.0 1.0 5.0
89354 trachst01 2007 2 CHN NL 4 7 0 1 0 ... 0.0 0.0 0.0 0 1.0 0.0 0.0 0.0 0.0 0.0
89355 trachst01 2007 1 BAL AL 3 5 0 0 0 ... 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0 0.0 0.0
89359 timlimi01 2007 1 BOS AL 4 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89360 thomeji01 2007 1 CHA AL 130 432 79 119 19 ... 96.0 0.0 1.0 95 134.0 11.0 6.0 0.0 3.0 10.0
89361 thomafr04 2007 1 TOR AL 155 531 63 147 30 ... 95.0 0.0 0.0 81 94.0 3.0 7.0 0.0 5.0 14.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
89460 guarded01 2007 1 CIN NL 15 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89462 griffke02 2007 1 CIN NL 144 528 78 146 24 ... 93.0 6.0 1.0 85 99.0 14.0 1.0 0.0 9.0 14.0
89463 greensh01 2007 1 NYN NL 130 446 62 130 30 ... 46.0 11.0 1.0 37 62.0 4.0 5.0 1.0 1.0 14.0
89464 graffto01 2007 1 MIL NL 86 231 34 55 8 ... 30.0 0.0 1.0 24 44.0 6.0 3.0 0.0 2.0 7.0
89465 gordoto01 2007 1 PHI NL 44 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89466 gonzalu01 2007 1 LAN NL 139 464 70 129 23 ... 68.0 6.0 2.0 56 56.0 4.0 4.0 0.0 2.0 11.0
89467 gomezch02 2007 2 CLE AL 19 53 4 15 2 ... 5.0 0.0 0.0 0 6.0 0.0 0.0 1.0 1.0 1.0
89468 gomezch02 2007 1 BAL AL 73 169 17 51 10 ... 16.0 1.0 2.0 10 20.0 1.0 0.0 5.0 1.0 5.0
89469 glavito02 2007 1 NYN NL 33 56 3 12 1 ... 4.0 0.0 0.0 6 5.0 0.0 0.0 12.0 1.0 0.0
89473 floydcl01 2007 1 CHN NL 108 282 40 80 10 ... 45.0 0.0 0.0 35 47.0 5.0 5.0 0.0 0.0 6.0
89474 finlest01 2007 1 COL NL 43 94 9 17 3 ... 2.0 0.0 0.0 8 4.0 1.0 0.0 0.0 0.0 2.0
89480 embreal01 2007 1 OAK AL 4 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89481 edmonji01 2007 1 SLN NL 117 365 39 92 15 ... 53.0 0.0 2.0 41 75.0 2.0 0.0 2.0 3.0 9.0
89482 easleda01 2007 1 NYN NL 76 193 24 54 6 ... 26.0 0.0 1.0 19 35.0 1.0 5.0 0.0 1.0 2.0
89489 delgaca01 2007 1 NYN NL 139 538 71 139 30 ... 87.0 4.0 0.0 52 118.0 8.0 11.0 0.0 6.0 12.0
89493 cormirh01 2007 1 CIN NL 6 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89494 coninje01 2007 2 NYN NL 21 41 2 8 2 ... 5.0 0.0 0.0 7 8.0 2.0 0.0 1.0 1.0 1.0
89495 coninje01 2007 1 CIN NL 80 215 23 57 11 ... 32.0 4.0 0.0 20 28.0 0.0 0.0 1.0 6.0 4.0
89497 clemero02 2007 1 NYA AL 2 2 0 1 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89498 claytro01 2007 2 BOS AL 8 6 1 0 0 ... 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0 0.0 2.0
89499 claytro01 2007 1 TOR AL 69 189 23 48 14 ... 12.0 2.0 1.0 14 50.0 0.0 1.0 3.0 3.0 8.0
89501 cirilje01 2007 2 ARI NL 28 40 6 8 4 ... 6.0 0.0 0.0 4 6.0 0.0 0.0 0.0 0.0 1.0
89502 cirilje01 2007 1 MIN AL 50 153 18 40 9 ... 21.0 2.0 0.0 15 13.0 0.0 1.0 3.0 2.0 9.0
89521 bondsba01 2007 1 SFN NL 126 340 75 94 14 ... 66.0 5.0 0.0 132 54.0 43.0 3.0 0.0 2.0 13.0
89523 biggicr01 2007 1 HOU NL 141 517 68 130 31 ... 50.0 4.0 3.0 23 112.0 0.0 3.0 7.0 5.0 5.0
89525 benitar01 2007 2 FLO NL 34 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89526 benitar01 2007 1 SFN NL 19 0 0 0 0 ... 0.0 0.0 0.0 0 0.0 0.0 0.0 0.0 0.0 0.0
89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 ... 25.0 6.0 1.0 37 74.0 3.0 6.0 4.0 1.0 11.0
89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 ... 49.0 3.0 0.0 27 30.0 5.0 2.0 0.0 3.0 13.0
89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 ... 0.0 0.0 0.0 0 3.0 0.0 0.0 0.0 0.0 0.0

100 rows × 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.

Advanced Exercise: Compiling Ebola Data

The data/ebola folder contains summarized reports of Ebola cases from three countries during the recent outbreak of the disease in West Africa. For each country, there are daily reports that contain various information about the outbreak in several cities in each country.

From these data files, use pandas to import them and create a single data frame that includes the daily totals of new cases and deaths for each country.

In [156]:
# Write your answer here

References

Python for Data Analysis Wes McKinney