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.

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.

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

   632
  1638
   569
   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.

counts.values

array([ 632, 1638,  569,  115])

counts.index

RangeIndex(start=0, stop=4, step=1)

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

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

bacteria

Firmicutes         632
Proteobacteria    1638
Actinobacteria     569
Bacteroidetes      115
dtype: int64

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

bacteria['Actinobacteria']

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

Proteobacteria    1638
Actinobacteria     569
dtype: int64

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

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

bacteria[0]

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

bacteria.name = 'counts'
bacteria.index.name = 'phylum'
bacteria

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.

np.log(bacteria)

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:

bacteria[bacteria>1000]

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:

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

Firmicutes         632
Proteobacteria    1638
Actinobacteria     569
Bacteroidetes      115
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.

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

Cyanobacteria        NaN
Firmicutes         632.0
Proteobacteria    1638.0
Actinobacteria     569.0
dtype: float64

bacteria2.isnull()

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:

bacteria + bacteria2

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.

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

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

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

data[['phylum','value','patient']]

	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:

data.columns

Index(['value', 'patient', 'phylum'], 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

data.dtypes

value       int64
patient     int64
phylum     object
dtype: object

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

data['patient']

  1
  1
  1
  1
  2
  2
  2
  2
Name: patient, dtype: int64

data.patient

  1
  1
  1
  1
  2
  2
  2
  2
Name: patient, dtype: int64

type(data.value)

pandas.core.series.Series

data[['value']]

	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.

data.loc[3]

value                115
patient                1
phylum     Bacteroidetes
Name: 3, dtype: object

Exercise#

Try out these commands to see what they return:

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

data.shape

(8, 3)

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

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}])

data

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

Its important to note that the Series returned when a DataFrame is indexted is merely a view on the DataFrame, and not a copy of the data itself. So you must be cautious when manipulating this data:

vals = data.value
vals

   632
  1638
   569
   115
   433
  1130
   754
   555
Name: value, dtype: int64

vals[5] = 0
vals

/tmp/ipykernel_2424/1693880163.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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  vals[5] = 0

   632
  1638
   569
   115
   433
     0
   754
   555
Name: value, dtype: int64

data

	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

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

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

	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:

data.value[[3,4,6]] = [14, 21, 5]
data

/tmp/ipykernel_2424/1243388670.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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  data.value[[3,4,6]] = [14, 21, 5]

	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

data['year'] = 2013
data

	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:

data.treatment = 1
data

	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

data.treatment

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.

# Write your answer here

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

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

  0
  0
  0
  0
  1
  1
dtype: int64

data['treatment'] = treatment
data

	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:

month = ['Jan', 'Feb', 'Mar', 'Apr']
data['month'] = month

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[40], line 2
      1 month = ['Jan', 'Feb', 'Mar', 'Apr']
----> 2 data['month'] = month

File /usr/share/miniconda/envs/arcdocs-jb/lib/python3.8/site-packages/pandas/core/frame.py:3980, in DataFrame.__setitem__(self, key, value)
   3977     self._setitem_array([key], value)
   3978 else:
   3979     # set column
-> 3980     self._set_item(key, value)

File /usr/share/miniconda/envs/arcdocs-jb/lib/python3.8/site-packages/pandas/core/frame.py:4174, in DataFrame._set_item(self, key, value)
   4164 def _set_item(self, key, value) -> None:
   4165     """
   4166     Add series to DataFrame in specified column.
   4167 
   (...)
   4172     ensure homogeneity.
   4173     """
-> 4174     value = self._sanitize_column(value)
   4176     if (
   4177         key in self.columns
   4178         and value.ndim == 1
   4179         and not is_extension_array_dtype(value)
   4180     ):
   4181         # broadcast across multiple columns if necessary
   4182         if not self.columns.is_unique or isinstance(self.columns, MultiIndex):

File /usr/share/miniconda/envs/arcdocs-jb/lib/python3.8/site-packages/pandas/core/frame.py:4915, in DataFrame._sanitize_column(self, value)
   4912     return _reindex_for_setitem(Series(value), self.index)
   4914 if is_list_like(value):
-> 4915     com.require_length_match(value, self.index)
   4916 return sanitize_array(value, self.index, copy=True, allow_2d=True)

File /usr/share/miniconda/envs/arcdocs-jb/lib/python3.8/site-packages/pandas/core/common.py:571, in require_length_match(data, index)
    567 """
    568 Check the length of data matches the length of the index.
    569 """
    570 if len(data) != len(index):
--> 571     raise ValueError(
    572         "Length of values "
    573         f"({len(data)}) "
    574         "does not match length of index "
    575         f"({len(index)})"
    576     )

ValueError: Length of values (4) does not match length of index (8)

data['month'] = ['Jan']*len(data)
data

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

del data['month']
data

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

data.values

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.

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

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

data.index

Index objects are immutable:

data.index[0] = 15

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

bacteria2.index = bacteria.index

bacteria2

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.

!cat ../data/microbiome.csv

This table can be read into a DataFrame using read_csv:

mb = pd.read_csv("../data/microbiome.csv")
mb

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.

pd.read_csv("../data/microbiome.csv", header=None).head()

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

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.

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

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:

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

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

pd.read_csv("../data/microbiome.csv", nrows=4)

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:

pd.read_csv("../data/microbiome.csv", chunksize=15)

data_chunks = pd.read_csv("../data/microbiome.csv", chunksize=15)

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

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.

!cat ../data/microbiome_missing.csv

pd.read_csv("../data/microbiome_missing.csv").head(20)

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

pd.isnull(pd.read_csv("../data/microbiome_missing.csv")).head(20)

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:

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

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

Microsoft Excel#

Since so much financial and scientific data ends up in Excel spreadsheets (regrettably), Pandas’ ability to directly import Excel spreadsheets is valuable. This support is contingent on having one or two dependencies (depending on what version of Excel file is being imported) installed: xlrd and openpyxl (these may be installed with either pip or easy_install).

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

mb_file = pd.ExcelFile('../data/microbiome/MID1.xls')
mb_file

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

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

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

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

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.

baseball = pd.read_csv("../data/baseball.csv", index_col='id')
baseball.head()

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:

player_id = baseball.player + baseball.year.astype(str)
baseball_newind = baseball.copy()
baseball_newind.index = player_id
baseball_newind.head()

This looks okay, but let’s check:

baseball_newind.index.is_unique

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

pd.Series(baseball_newind.index).value_counts()

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

baseball_newind.loc['wickmbo012007']

We will learn more about indexing below.

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

player_unique = baseball.player + baseball.team + baseball.year.astype(str)
baseball_newind = baseball.copy()
baseball_newind.index = player_unique
baseball_newind.head()

baseball_newind.index.is_unique

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:

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

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:

id_range = range(baseball.index.values.min(), baseball.index.values.max())
baseball.reindex(id_range).head()

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

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

baseball.reindex(id_range, fill_value='charliebrown', columns=['player']).head()

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:

baseball.shape

baseball.drop([89525, 89526])

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

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.

# Sample Series object
hits = baseball_newind.h
hits

# Numpy-style indexing
hits[:3]

# Indexing by label
hits[['womacto01CHN2006','schilcu01BOS2006']]

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

hits['womacto01CHN2006':'gonzalu01ARI2006']

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

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

baseball_newind[['h','ab']]

baseball_newind[baseball_newind.ab>500]

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:

baseball_newind.query('ab > 500')

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 @:

min_ab = 450

baseball_newind.query('ab > @min_ab')

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

baseball_newind.loc['gonzalu01ARI2006', ['h','X2b', 'X3b', 'hr']]

baseball_newind.loc[:'myersmi01NYA2006', 'hr']

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:

baseball_newind.iloc[:5, 5:8]

micro

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?

# 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:

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]

hr2007

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

hr_total = hr2006 + hr2007
hr_total

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.

hr_total[hr_total.notnull()]

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:

hr2007.add(hr2006, fill_value=0)

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:

baseball.hr - baseball.hr.max()

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

baseball.loc[89521, "player"]

stats = baseball[['h','X2b', 'X3b', 'hr']]
diff = stats - stats.loc[89521]
diff[:10]

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

stats.apply(np.median)

def range_calc(x):
    return x.max() - x.min()

stat_range = lambda x: x.max() - x.min()
stats.apply(stat_range)

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.

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)

Sorting and Ranking#

Pandas objects include methods for re-ordering data.

baseball_newind.sort_index().head()

baseball_newind.sort_index(ascending=False).head()

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

baseball_newind.sort_index(axis=1).head()

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

baseball.hr.sort_values()

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

baseball[['player','sb','cs']].sort_values(ascending=[False,True], 
                                           by=['sb', 'cs']).head(10)

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

baseball.hr.rank()

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

pd.Series([100,100]).rank()

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

baseball.hr.rank(method='first')

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

baseball.rank(ascending=False).head()

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

Exercise#

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

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

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

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

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.

baseball_h.index[:10]

baseball_h.index.is_unique

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

baseball_h.loc[(2007, 'ATL', 'francju01')]

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

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

mb.head(10)

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

mb.loc['Proteobacteria']

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

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

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

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

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

frame.loc['a', 'Ohio']

Try retrieving the value corresponding to b2 in Colorado:

# Write your answer here

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

mb.swaplevel('Patient', 'Taxon').head()

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

mb.sortlevel('Patient', ascending=False).head()

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

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

foo.isnull()

Missing values may be dropped or indexed out:

bacteria2

bacteria2.dropna()

bacteria2.isnull()

bacteria2[bacteria2.notnull()]

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

data

data.dropna()

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

data.dropna(how='all')

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

data.loc[7, 'year'] = np.nan
data

data.dropna(thresh=4)

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:

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

bacteria2.fillna(0)

data.fillna({'year': 2013, 'treatment':2})

Notice that fillna by default returns a new object with the desired filling behavior, rather than changing the Series or DataFrame in place (in general, we like to do this, by the way!).

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

data.year.fillna(2013, inplace=True)
data

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

bacteria2.fillna(method='bfill')

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.

baseball.sum()

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:

baseball.mean()

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

bacteria2

bacteria2.mean()

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

bacteria2.mean(skipna=False)

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

extra_bases = baseball[['X2b','X3b','hr']].sum(axis=1)
extra_bases.sort_values(ascending=False)

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

baseball.describe()

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

baseball.player.describe()

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})\]

baseball.hr.cov(baseball.X2b)

\[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}}\]

baseball.hr.corr(baseball.X2b)

baseball.ab.corr(baseball.h)

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

# 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:

mb.head()

mb.sum(level='Taxon')

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.

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.

baseball.to_pickle("baseball_pickle")

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

pd.read_pickle("baseball_pickle")

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.

# Write your answer here

References#

Python for Data Analysis Wes McKinney

SWD5: Scientific Python

Pandas

Contents

Pandas#

Pandas Data Structures#

Series#

DataFrame#

Exercise#

Exercise#

Importing data#

Microsoft Excel#

Pandas Fundamentals#

Manipulating indices#

Indexing and Selection#

Exercise#

Operations#

Sorting and Ranking#

Exercise#

Hierarchical indexing#

Missing data#

Exercise#

Data summarization#

Writing Data to Files#

Advanced Exercise: Compiling Ebola Data#

References#