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:
Key features:
import pandas as pd
import numpy as np
DATA_PATH = 'https://raw.githubusercontent.com/fonnesbeck/Bios8366/master/data/'
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
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
counts.index
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
These labels can be used to refer to the values in the Series
.
bacteria['Actinobacteria']
bacteria[[name.endswith('bacteria') for name in bacteria.index]]
[name.endswith('bacteria') for name in bacteria.index]
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
NumPy's math functions and other operations can be applied to Series without losing the data structure.
np.log(bacteria)
We can also filter according to the values in the Series
:
bacteria[bacteria>1000]
A Series
can be thought of as an ordered key-value store. In fact, we can create one from a dict
:
bacteria_dict = {'Proteobacteria': 1638, 'Actinobacteria': 569,'Firmicutes': 632,
'Bacteroidetes': 115}
pd.Series(bacteria_dict)
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
bacteria2.isnull()
Critically, the labels are used to align data when used in operations with other Series objects:
bacteria + bacteria2
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.
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
We can change the order by indexing them in the order we desire:
data[['phylum','value','patient']]
A DataFrame
has a second index, representing the columns:
data.columns
The dtypes
attribute reveals the data type for each column in our DataFrame.
int64
is numeric integer valuesobject
strings (letters and numbers)float64
floating-point valuesdata.dtypes
If we wish to access columns, we can do so either by dict-like indexing or by attribute:
data['patient']
data.patient
type(data.value)
data[['value']]
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[1]
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
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:
vals = data.value
vals
vals[5] = 0
vals
Now look at the original DataFrame
!
data
If we plan on modifying an extracted Series, its a good idea to make a copy.
vals = data.value.copy()
vals[5] = 1000
data
We can create or modify columns by assignment:
data.value[[3,4,6]] = [14, 21, 5]
data
data['year'] = 2013
data
But note, we cannot use the attribute indexing method to add a new column:
data.treatment = 1
data
data.treatment
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.
data[data.phylum.str.endswith('bacteria') & (data.value > 1000)]
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
data['treatment'] = treatment
data
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
data['month'] = ['Jan']*len(data)
data
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:
data.drop('month', axis=1, inplace=True)
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
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_PATH + "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_PATH + "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_PATH + "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_PATH + "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_PATH + "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_PATH + "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:
data_chunks = pd.read_csv(DATA_PATH + "microbiome.csv", chunksize=14)
mean_tissue = pd.Series({chunk.iloc[0].Taxon: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_PATH + "microbiome_missing.csv").head(20)
Above, Pandas recognized NA
and an empty field as missing data.
pd.isnull(pd.read_csv(DATA_PATH + "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_PATH + "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
.
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
mb = pd.read_excel(DATA_PATH + 'microbiome/MID2.xls', sheet_name='Sheet 1', header=None)
mb.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.