'Key length (1) was greater than MultiIndex lexsort depth (0)'
This is the result of the MultiIndex not being sorted; in general,
partial slices and other similar operations require the levels in the
``MultiIndex`` to be in sorted (i.e., lexographical) order.
Pandas provides a number of convenience routines to perform this type of
sorting; examples are the ``sort_index()`` and ``sortlevel()`` methods
of the ``DataFrame``.
.. code:: ipython3
data = data.sort_index()
data
.. parsed-literal::
char int
a 1 0.002105
2 0.280923
b 1 0.072270
2 0.273800
c 1 0.008604
2 0.631968
dtype: float64
With the index sorted in this way, partial slicing will work as
expected:
.. code:: ipython3
data['a':'b']
.. parsed-literal::
char int
a 1 0.002105
2 0.280923
b 1 0.072270
2 0.273800
dtype: float64
Stacking and unstacking indices
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
As we saw briefly before, it is possible to convert a dataset from a
stacked multi-index to a simple two-dimensional representation,
optionally specifying the level to use:
.. code:: ipython3
pop.unstack(level=0)
.. raw:: html
| state |
California |
New York |
Texas |
| year |
|
|
|
| 2000 |
33871648 |
18976457 |
20851820 |
| 2010 |
37253956 |
19378102 |
25145561 |
.. code:: ipython3
pop.unstack(level=1)
.. raw:: html
| year |
2000 |
2010 |
| state |
|
|
| California |
33871648 |
37253956 |
| New York |
18976457 |
19378102 |
| Texas |
20851820 |
25145561 |
The opposite of ``unstack()`` is ``stack()``, which here can be used to
recover the original series:
.. code:: ipython3
pop.unstack().stack()
.. parsed-literal::
state year
California 2000 33871648
2010 37253956
New York 2000 18976457
2010 19378102
Texas 2000 20851820
2010 25145561
dtype: int64
Index setting and resetting
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Another way to rearrange hierarchical data is to turn the index labels
into columns; this can be accomplished with the ``reset_index`` method.
Calling this on the population dictionary will result in a ``DataFrame``
with a *state* and *year* column holding the information that was
formerly in the index.
.. code:: ipython3
pop_flat = pop.reset_index(name='population') # specify the name of the data for the column
pop_flat
.. raw:: html
|
state |
year |
population |
| 0 |
California |
2000 |
33871648 |
| 1 |
California |
2010 |
37253956 |
| 2 |
New York |
2000 |
18976457 |
| 3 |
New York |
2010 |
19378102 |
| 4 |
Texas |
2000 |
20851820 |
| 5 |
Texas |
2010 |
25145561 |
Often when working with data in the real world, the raw input data looks
like this and it’s useful to build a ``MultiIndex`` from the column
values. This can be done with the ``set_index`` method of the
``DataFrame``, which returns a multiply indexed ``DataFrame``:
.. code:: ipython3
pop_flat.set_index(['state', 'year'])
.. raw:: html
|
|
population |
| state |
year |
|
| California |
2000 |
33871648 |
| 2010 |
37253956 |
| New York |
2000 |
18976457 |
| 2010 |
19378102 |
| Texas |
2000 |
20851820 |
| 2010 |
25145561 |
Data Aggregations on Multi-Indices
----------------------------------
We’ve previously seen that Pandas has built-in data aggregation methods,
such as ``mean()``, ``sum()``, and ``max()``. For hierarchically indexed
data, these can be passed a ``level`` parameter that controls which
subset of the data the aggregate is computed on.
.. code:: ipython3
health_data
.. raw:: html
|
subject |
Bob |
Guido |
Sue |
|
type |
HR |
Temp |
HR |
Temp |
HR |
Temp |
| year |
visit |
|
|
|
|
|
|
| 2013 |
1 |
48.0 |
38.1 |
19.0 |
38.4 |
52.0 |
38.8 |
| 2 |
34.0 |
38.0 |
37.0 |
36.9 |
31.0 |
37.6 |
| 2014 |
1 |
41.0 |
37.0 |
52.0 |
38.9 |
38.0 |
37.4 |
| 2 |
47.0 |
36.9 |
46.0 |
36.4 |
42.0 |
36.6 |
Perhaps we’d like to average-out the measurements in the two visits each
year. We can do this by naming the index level we’d like to explore, in
this case the year:
.. code:: ipython3
data_mean = health_data.mean(level='year')
data_mean
.. raw:: html
| subject |
Bob |
Guido |
Sue |
| type |
HR |
Temp |
HR |
Temp |
HR |
Temp |
| year |
|
|
|
|
|
|
| 2013 |
41.0 |
38.05 |
28.0 |
37.65 |
41.5 |
38.2 |
| 2014 |
44.0 |
36.95 |
49.0 |
37.65 |
40.0 |
37.0 |
By further making use of the ``axis`` keyword, we can take the mean
among levels on the columns as well:
.. code:: ipython3
data_mean.mean(axis=1, level='type')
.. raw:: html
| type |
HR |
Temp |
| year |
|
|
| 2013 |
36.833333 |
37.966667 |
| 2014 |
44.333333 |
37.200000 |
Combining Datasets: Concat and Append
=====================================
Some of the most interesting studies of data come from combining
different data sources. These operations can involve anything from very
straightforward concatenation of two different datasets, to more
complicated database-style joins and merges that correctly handle any
overlaps between the datasets. ``Series`` and ``DataFrame``\ s are built
with this type of operation in mind, and Pandas includes functions and
methods that make this sort of data wrangling fast and straightforward.
Here we’ll take a look at simple concatenation of ``Series`` and
``DataFrame``\ s with the ``pd.concat`` function; later we’ll dive into
more sophisticated in-memory merges and joins implemented in Pandas.
For convenience, we’ll define this function which creates a
``DataFrame`` of a particular form that will be useful below:
.. code:: ipython3
def make_df(cols, ind):
"""Quickly make a DataFrame"""
data = {c: [str(c) + str(i) for i in ind]
for c in cols}
return pd.DataFrame(data, ind)
# example DataFrame
make_df('ABC', range(3))
.. raw:: html
|
A |
B |
C |
| 0 |
A0 |
B0 |
C0 |
| 1 |
A1 |
B1 |
C1 |
| 2 |
A2 |
B2 |
C2 |
In addition, we’ll create a quick class that allows us to display
multiple ``DataFrame``\ s side by side. The code makes use of the
special ``_repr_html_`` method, which IPython uses to implement its rich
object display:
.. code:: ipython3
class display(object):
"""Display HTML representation of multiple objects"""
template = """"""
def __init__(self, *args):
self.args = args
def _repr_html_(self):
return '\n'.join(self.template.format(a, eval(a)._repr_html_())
for a in self.args)
def __repr__(self):
return '\n\n'.join(a + '\n' + repr(eval(a))
for a in self.args)
Simple Concatenation with ``pd.concat``
---------------------------------------
Pandas has a function, ``pd.concat()``, which has a similar syntax to
``np.concatenate`` but contains a number of options that we’ll discuss
momentarily:
.. code:: python
# Signature in Pandas v0.18
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
keys=None, levels=None, names=None, verify_integrity=False,
copy=True)
``pd.concat()`` can be used for a simple concatenation of ``Series`` or
``DataFrame`` objects, just as ``np.concatenate()`` can be used for
simple concatenations of arrays:
.. code:: ipython3
ser1 = pd.Series(['A', 'B', 'C'], index=[1, 2, 3])
ser2 = pd.Series(['D', 'E', 'F'], index=[4, 5, 6])
pd.concat([ser1, ser2])
.. parsed-literal::
1 A
2 B
3 C
4 D
5 E
6 F
dtype: object
.. code:: ipython3
df1 = make_df('AB', [1, 2])
df2 = make_df('AB', [3, 4])
display('df1', 'df2', 'pd.concat([df1, df2])')
.. raw:: html
pd.concat([df1, df2])
|
A |
B |
| 1 |
A1 |
B1 |
| 2 |
A2 |
B2 |
| 3 |
A3 |
B3 |
| 4 |
A4 |
B4 |
By default, the concatenation takes place row-wise within the
``DataFrame`` (i.e., ``axis=0``). Like ``np.concatenate``, ``pd.concat``
allows specification of an axis along which concatenation will take
place:
.. code:: ipython3
df3 = make_df('AB', [0, 1])
df4 = make_df('CD', [0, 1])
display('df3', 'df4', "pd.concat([df3, df4], axis=1)")
.. raw:: html
pd.concat([df3, df4], axis=1)
|
A |
B |
C |
D |
| 0 |
A0 |
B0 |
C0 |
D0 |
| 1 |
A1 |
B1 |
C1 |
D1 |
Duplicate indices
~~~~~~~~~~~~~~~~~
One important difference between ``np.concatenate`` and ``pd.concat`` is
that Pandas concatenation *preserves indices*, even if the result will
have duplicate indices:
.. code:: ipython3
x = make_df('AB', [0, 1])
y = make_df('AB', [2, 3])
y.index = x.index # make duplicate indices!
display('x', 'y', 'pd.concat([x, y])')
.. raw:: html
pd.concat([x, y])
|
A |
B |
| 0 |
A0 |
B0 |
| 1 |
A1 |
B1 |
| 0 |
A2 |
B2 |
| 1 |
A3 |
B3 |
Notice the repeated indices in the result. While this is valid within
``DataFrame``\ s, the outcome is often undesirable. ``pd.concat()``
gives us a few ways to handle it.
.. code:: ipython3
try:
pd.concat([x, y], verify_integrity=True)
except ValueError as e:
print("ValueError:", e)
.. parsed-literal::
ValueError: Indexes have overlapping values: Int64Index([0, 1], dtype='int64')
Ignoring the index
^^^^^^^^^^^^^^^^^^
Sometimes the index itself does not matter, and you would prefer it to
simply be ignored. This option can be specified using the
``ignore_index`` flag. With this set to true, the concatenation will
create a new integer index for the resulting ``Series``:
.. code:: ipython3
display('x', 'y', 'pd.concat([x, y], ignore_index=True)')
.. raw:: html
pd.concat([x, y], ignore_index=True)
|
A |
B |
| 0 |
A0 |
B0 |
| 1 |
A1 |
B1 |
| 2 |
A2 |
B2 |
| 3 |
A3 |
B3 |
Adding MultiIndex keys
^^^^^^^^^^^^^^^^^^^^^^
Another option is to use the ``keys`` option to specify a label for the
data sources; the result will be a hierarchically indexed series
containing the data:
.. code:: ipython3
display('x', 'y', "pd.concat([x, y], keys=['x', 'y'])")
.. raw:: html
pd.concat([x, y], keys=['x', 'y'])
|
|
A |
B |
| x |
0 |
A0 |
B0 |
| 1 |
A1 |
B1 |
| y |
0 |
A2 |
B2 |
| 1 |
A3 |
B3 |
Concatenation with joins
~~~~~~~~~~~~~~~~~~~~~~~~
In practice, data from different sources might have different sets of
column names, and ``pd.concat`` offers several options in this case.
Consider the concatenation of the following two ``DataFrame``\ s, which
have some (but not all!) columns in common:
.. code:: ipython3
df5 = make_df('ABC', [1, 2])
df6 = make_df('BCD', [3, 4])
display('df5', 'df6', 'pd.concat([df5, df6])')
.. raw:: html
df5
|
A |
B |
C |
| 1 |
A1 |
B1 |
C1 |
| 2 |
A2 |
B2 |
C2 |
df6
|
B |
C |
D |
| 3 |
B3 |
C3 |
D3 |
| 4 |
B4 |
C4 |
D4 |
pd.concat([df5, df6])
|
A |
B |
C |
D |
| 1 |
A1 |
B1 |
C1 |
NaN |
| 2 |
A2 |
B2 |
C2 |
NaN |
| 3 |
NaN |
B3 |
C3 |
D3 |
| 4 |
NaN |
B4 |
C4 |
D4 |
By default, the join is a union of the input column|s
(``join='outer'``), but we can change this to an intersection of the
columns using ``join='inner'``:
Another option is to directly specify the index of the remaininig colums
using the ``join_axes`` argument, which takes a list of index objects.
.. code:: ipython3
display('df5', 'df6', "pd.concat([df5, df6])")
.. raw:: html
df5
|
A |
B |
C |
| 1 |
A1 |
B1 |
C1 |
| 2 |
A2 |
B2 |
C2 |
df6
|
B |
C |
D |
| 3 |
B3 |
C3 |
D3 |
| 4 |
B4 |
C4 |
D4 |
pd.concat([df5, df6])
|
A |
B |
C |
D |
| 1 |
A1 |
B1 |
C1 |
NaN |
| 2 |
A2 |
B2 |
C2 |
NaN |
| 3 |
NaN |
B3 |
C3 |
D3 |
| 4 |
NaN |
B4 |
C4 |
D4 |
The ``append()`` method
~~~~~~~~~~~~~~~~~~~~~~~
Because direct array concatenation is so common, ``Series`` and
``DataFrame`` objects have an ``append`` method that can accomplish the
same thing in fewer keystrokes. For example, rather than calling
``pd.concat([df1, df2])``, you can simply call ``df1.append(df2)``:
.. code:: ipython3
display('df1', 'df2', 'df1.append(df2)')
.. raw:: html
df1.append(df2)
|
A |
B |
| 1 |
A1 |
B1 |
| 2 |
A2 |
B2 |
| 3 |
A3 |
B3 |
| 4 |
A4 |
B4 |
Keep in mind that unlike the ``append()`` and ``extend()`` methods of
Python lists, the ``append()`` method in Pandas does not modify the
original object–instead it creates a new object with the combined data.
It also is not a very efficient method, because it involves creation of
a new index *and* data buffer. Thus, if you plan to do multiple
``append`` operations, it is generally better to build a list of
``DataFrame``\ s and pass them all at once to the ``concat()`` function.
Combining Datasets: Merge and Join
==================================
One essential feature offered by Pandas is its high-performance,
in-memory join and merge operations. If you have ever worked with
databases, you should be familiar with this type of data interaction.
The main interface for this is the ``pd.merge`` function, and we’ll see
few examples of how this can work in practice.
For convenience, we will start by redefining the ``display()``
functionality:
.. code:: ipython3
class display(object):
"""Display HTML representation of multiple objects"""
template = """"""
def __init__(self, *args):
self.args = args
def _repr_html_(self):
return '\n'.join(self.template.format(a, eval(a)._repr_html_())
for a in self.args)
def __repr__(self):
return '\n\n'.join(a + '\n' + repr(eval(a))
for a in self.args)
Relational Algebra
------------------
The behavior implemented in ``pd.merge()`` is a subset of what is known
as *relational algebra*, which is a formal set of rules for manipulating
relational data, and forms the conceptual foundation of operations
available in most databases. The strength of the relational algebra
approach is that it proposes several primitive operations, which become
the building blocks of more complicated operations on any dataset. With
this lexicon of fundamental operations implemented efficiently in a
database or other program, a wide range of fairly complicated composite
operations can be performed.
Pandas implements several of these fundamental building-blocks in the
``pd.merge()`` function and the related ``join()`` method of ``Series``
and ``Dataframe``\ s.
Categories of Joins
-------------------
The ``pd.merge()`` function implements a number of types of joins: the
*one-to-one*, *many-to-one*, and *many-to-many* joins. All three types
of joins are accessed via an identical call to the ``pd.merge()``
interface; the type of join performed depends on the form of the input
data.
One-to-one joins
~~~~~~~~~~~~~~~~
Perhaps the simplest type of merge expresion is the one-to-one join,
which is in many ways very similar to the column-wise concatenation that
we have already seen. As a concrete example, consider the following two
``DataFrames`` which contain information on several employees in a
company:
.. code:: ipython3
df1 = pd.DataFrame({'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],
'group': ['Accounting', 'Engineering', 'Engineering', 'HR']})
df2 = pd.DataFrame({'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],
'hire_date': [2004, 2008, 2012, 2014]})
display('df1', 'df2')
.. raw:: html
df1
|
employee |
group |
| 0 |
Bob |
Accounting |
| 1 |
Jake |
Engineering |
| 2 |
Lisa |
Engineering |
| 3 |
Sue |
HR |
df2
|
employee |
hire_date |
| 0 |
Lisa |
2004 |
| 1 |
Bob |
2008 |
| 2 |
Jake |
2012 |
| 3 |
Sue |
2014 |
To combine this information into a single ``DataFrame``, we can use the
``pd.merge()`` function:
.. code:: ipython3
df3 = pd.merge(df1, df2)
df3
.. raw:: html
|
employee |
group |
hire_date |
| 0 |
Bob |
Accounting |
2008 |
| 1 |
Jake |
Engineering |
2012 |
| 2 |
Lisa |
Engineering |
2004 |
| 3 |
Sue |
HR |
2014 |
The ``pd.merge()`` function recognizes that each ``DataFrame`` has an
“employee” column, and automatically joins using this column as a key.
The result of the merge is a new ``DataFrame`` that combines the
information from the two inputs. Notice that the order of entries in
each column is not necessarily maintained: in this case, the order of
the “employee” column differs between ``df1`` and ``df2``, and the
``pd.merge()`` function correctly accounts for this. Additionally, keep
in mind that the merge in general discards the index, except in the
special case of merges by index (see the ``left_index`` and
``right_index`` keywords, discussed momentarily).
Many-to-one joins
~~~~~~~~~~~~~~~~~
Many-to-one joins are joins in which one of the two key columns contains
duplicate entries. For the many-to-one case, the resulting ``DataFrame``
will preserve those duplicate entries as appropriate:
.. code:: ipython3
df4 = pd.DataFrame({'group': ['Accounting', 'Engineering', 'HR'],
'supervisor': ['Carly', 'Guido', 'Steve']})
display('df3', 'df4', 'pd.merge(df3, df4)')
.. raw:: html
df3
|
employee |
group |
hire_date |
| 0 |
Bob |
Accounting |
2008 |
| 1 |
Jake |
Engineering |
2012 |
| 2 |
Lisa |
Engineering |
2004 |
| 3 |
Sue |
HR |
2014 |
df4
|
group |
supervisor |
| 0 |
Accounting |
Carly |
| 1 |
Engineering |
Guido |
| 2 |
HR |
Steve |
pd.merge(df3, df4)
|
employee |
group |
hire_date |
supervisor |
| 0 |
Bob |
Accounting |
2008 |
Carly |
| 1 |
Jake |
Engineering |
2012 |
Guido |
| 2 |
Lisa |
Engineering |
2004 |
Guido |
| 3 |
Sue |
HR |
2014 |
Steve |
Many-to-many joins
~~~~~~~~~~~~~~~~~~
Many-to-many joins are a bit confusing conceptually, but are
nevertheless well defined. If the key column in both the left and right
array contains duplicates, then the result is a many-to-many merge.
Consider the following, where we have a ``DataFrame`` showing one or
more skills associated with a particular group. By performing a
many-to-many join, we can recover the skills associated with any
individual person:
.. code:: ipython3
df5 = pd.DataFrame({'group': ['Accounting', 'Accounting',
'Engineering', 'Engineering', 'HR', 'HR'],
'skills': ['math', 'spreadsheets', 'coding', 'linux',
'spreadsheets', 'organization']})
display('df1', 'df5', "pd.merge(df1, df5)")
.. raw:: html
df1
|
employee |
group |
| 0 |
Bob |
Accounting |
| 1 |
Jake |
Engineering |
| 2 |
Lisa |
Engineering |
| 3 |
Sue |
HR |
df5
|
group |
skills |
| 0 |
Accounting |
math |
| 1 |
Accounting |
spreadsheets |
| 2 |
Engineering |
coding |
| 3 |
Engineering |
linux |
| 4 |
HR |
spreadsheets |
| 5 |
HR |
organization |
pd.merge(df1, df5)
|
employee |
group |
skills |
| 0 |
Bob |
Accounting |
math |
| 1 |
Bob |
Accounting |
spreadsheets |
| 2 |
Jake |
Engineering |
coding |
| 3 |
Jake |
Engineering |
linux |
| 4 |
Lisa |
Engineering |
coding |
| 5 |
Lisa |
Engineering |
linux |
| 6 |
Sue |
HR |
spreadsheets |
| 7 |
Sue |
HR |
organization |
Specification of the Merge Key
------------------------------
We’ve already seen the default behavior of ``pd.merge()``: it looks for
one or more matching column names between the two inputs, and uses this
as the key. However, often the column names will not match so nicely,
and ``pd.merge()`` provides a variety of options for handling this.
The ``on`` keyword
~~~~~~~~~~~~~~~~~~
Most simply, you can explicitly specify the name of the key column using
the ``on`` keyword, which takes a column name or a list of column names:
.. code:: ipython3
display('df1', 'df2', "pd.merge(df1, df2, on='employee')")
.. raw:: html
df1
|
employee |
group |
| 0 |
Bob |
Accounting |
| 1 |
Jake |
Engineering |
| 2 |
Lisa |
Engineering |
| 3 |
Sue |
HR |
df2
|
employee |
hire_date |
| 0 |
Lisa |
2004 |
| 1 |
Bob |
2008 |
| 2 |
Jake |
2012 |
| 3 |
Sue |
2014 |
pd.merge(df1, df2, on='employee')
|
employee |
group |
hire_date |
| 0 |
Bob |
Accounting |
2008 |
| 1 |
Jake |
Engineering |
2012 |
| 2 |
Lisa |
Engineering |
2004 |
| 3 |
Sue |
HR |
2014 |
The ``left_on`` and ``right_on`` keywords
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
At times you may wish to merge two datasets with different column names;
for example, we may have a dataset in which the employee name is labeled
as “name” rather than “employee”. In this case, we can use the
``left_on`` and ``right_on`` keywords to specify the two column names:
.. code:: ipython3
df3 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
'salary': [70000, 80000, 120000, 90000]})
display('df1', 'df3', 'pd.merge(df1, df3, left_on="employee", right_on="name")')
.. raw:: html
df1
|
employee |
group |
| 0 |
Bob |
Accounting |
| 1 |
Jake |
Engineering |
| 2 |
Lisa |
Engineering |
| 3 |
Sue |
HR |
df3
|
name |
salary |
| 0 |
Bob |
70000 |
| 1 |
Jake |
80000 |
| 2 |
Lisa |
120000 |
| 3 |
Sue |
90000 |
pd.merge(df1, df3, left_on="employee", right_on="name")
|
employee |
group |
name |
salary |
| 0 |
Bob |
Accounting |
Bob |
70000 |
| 1 |
Jake |
Engineering |
Jake |
80000 |
| 2 |
Lisa |
Engineering |
Lisa |
120000 |
| 3 |
Sue |
HR |
Sue |
90000 |
The result has a redundant column that we can drop if desired–for
example, by using the ``drop()`` method of ``DataFrame``\ s:
.. code:: ipython3
pd.merge(df1, df3, left_on="employee", right_on="name").drop('name', axis=1)
.. raw:: html
|
employee |
group |
salary |
| 0 |
Bob |
Accounting |
70000 |
| 1 |
Jake |
Engineering |
80000 |
| 2 |
Lisa |
Engineering |
120000 |
| 3 |
Sue |
HR |
90000 |
The ``left_index`` and ``right_index`` keywords
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sometimes, rather than merging on a column, you would instead like to
merge on an index. For example, your data might look like this:
.. code:: ipython3
df1.set_index?
.. code:: ipython3
df1a = df1.set_index([ 'group', 'employee'])
df1a
.. raw:: html
|
|
| group |
employee |
| Accounting |
Bob |
| Engineering |
Jake |
| Lisa |
| HR |
Sue |
.. code:: ipython3
df1a = df1.set_index(['employee', 'group'])
df2a = df2.set_index('employee')
display('df1a', 'df2a')
.. raw:: html
df1a
|
group |
| employee |
|
| Bob |
Accounting |
| Jake |
Engineering |
| Lisa |
Engineering |
| Sue |
HR |
df2a
|
hire_date |
| employee |
|
| Lisa |
2004 |
| Bob |
2008 |
| Jake |
2012 |
| Sue |
2014 |
You can use the index as the key for merging by specifying the
``left_index`` and/or ``right_index`` flags in ``pd.merge()``:
.. code:: ipython3
display('df1a', 'df2a', "pd.merge(df1a, df2a, left_index=True, right_index=True)")
.. raw:: html
df1a
|
group |
| employee |
|
| Bob |
Accounting |
| Jake |
Engineering |
| Lisa |
Engineering |
| Sue |
HR |
df2a
|
hire_date |
| employee |
|
| Lisa |
2004 |
| Bob |
2008 |
| Jake |
2012 |
| Sue |
2014 |
pd.merge(df1a, df2a, left_index=True, right_index=True)
|
group |
hire_date |
| employee |
|
|
| Bob |
Accounting |
2008 |
| Jake |
Engineering |
2012 |
| Lisa |
Engineering |
2004 |
| Sue |
HR |
2014 |
.. code:: ipython3
df1a, df2a, pd.merge(df1a, df2a, left_index=True, right_index=True)
.. parsed-literal::
( group
employee
Bob Accounting
Jake Engineering
Lisa Engineering
Sue HR,
hire_date
employee
Lisa 2004
Bob 2008
Jake 2012
Sue 2014,
group hire_date
employee
Bob Accounting 2008
Jake Engineering 2012
Lisa Engineering 2004
Sue HR 2014)
For convenience, ``DataFrame``\ s implement the ``join()`` method, which
performs a merge that defaults to joining on indices:
.. code:: ipython3
display('df1a', 'df2a', 'df1a.join(df2a)')
.. raw:: html
df1a
|
group |
| employee |
|
| Bob |
Accounting |
| Jake |
Engineering |
| Lisa |
Engineering |
| Sue |
HR |
df2a
|
hire_date |
| employee |
|
| Lisa |
2004 |
| Bob |
2008 |
| Jake |
2012 |
| Sue |
2014 |
df1a.join(df2a)
|
group |
hire_date |
| employee |
|
|
| Bob |
Accounting |
2008 |
| Jake |
Engineering |
2012 |
| Lisa |
Engineering |
2004 |
| Sue |
HR |
2014 |
If you’d like to mix indices and columns, you can combine ``left_index``
with ``right_on`` or ``left_on`` with ``right_index`` to get the desired
behavior:
.. code:: ipython3
display('df1a', 'df3', "pd.merge(df1a, df3, left_index=True, right_on='name')")
.. raw:: html
df1a
|
group |
| employee |
|
| Bob |
Accounting |
| Jake |
Engineering |
| Lisa |
Engineering |
| Sue |
HR |
df3
|
name |
salary |
| 0 |
Bob |
70000 |
| 1 |
Jake |
80000 |
| 2 |
Lisa |
120000 |
| 3 |
Sue |
90000 |
pd.merge(df1a, df3, left_index=True, right_on='name')
|
group |
name |
salary |
| 0 |
Accounting |
Bob |
70000 |
| 1 |
Engineering |
Jake |
80000 |
| 2 |
Engineering |
Lisa |
120000 |
| 3 |
HR |
Sue |
90000 |
Specifying Set Arithmetic for Joins
-----------------------------------
We have glossed over one important consideration in performing a join:
the type of set arithmetic used in the join. This comes up when a value
appears in one key column but not the other:
.. code:: ipython3
df6 = pd.DataFrame({'name': ['Peter', 'Paul', 'Mary'], 'food': ['fish', 'beans', 'bread']},
columns=['name', 'food'])
df7 = pd.DataFrame({'name': ['Mary', 'Joseph'], 'drink': ['wine', 'beer']},
columns=['name', 'drink'])
display('df6', 'df7', 'pd.merge(df6, df7)')
.. raw:: html
df6
|
name |
food |
| 0 |
Peter |
fish |
| 1 |
Paul |
beans |
| 2 |
Mary |
bread |
df7
|
name |
drink |
| 0 |
Mary |
wine |
| 1 |
Joseph |
beer |
pd.merge(df6, df7)
|
name |
food |
drink |
| 0 |
Mary |
bread |
wine |
Here we have merged two datasets that have only a single “name” entry in
common: Mary. By default, the result contains the *intersection* of the
two sets of inputs; this is what is known as an *inner join*. We can
specify this explicitly using the ``how`` keyword, which defaults to
``"inner"``:
.. code:: ipython3
pd.merge(df6, df7, how='inner')
.. raw:: html
|
name |
food |
drink |
| 0 |
Mary |
bread |
wine |
Other options for the ``how`` keyword are ``'outer'``, ``'left'``, and
``'right'``. An *outer join* returns a join over the union of the input
columns, and fills in all missing values with NAs:
.. code:: ipython3
display('df6', 'df7', "pd.merge(df6, df7, how='outer')")
.. raw:: html
df6
|
name |
food |
| 0 |
Peter |
fish |
| 1 |
Paul |
beans |
| 2 |
Mary |
bread |
df7
|
name |
drink |
| 0 |
Mary |
wine |
| 1 |
Joseph |
beer |
pd.merge(df6, df7, how='outer')
|
name |
food |
drink |
| 0 |
Peter |
fish |
NaN |
| 1 |
Paul |
beans |
NaN |
| 2 |
Mary |
bread |
wine |
| 3 |
Joseph |
NaN |
beer |
The *left join* and *right join* return joins over the left entries and
right entries, respectively:
.. code:: ipython3
display('df6', 'df7', "pd.merge(df6, df7, how='left')")
.. raw:: html
df6
|
name |
food |
| 0 |
Peter |
fish |
| 1 |
Paul |
beans |
| 2 |
Mary |
bread |
df7
|
name |
drink |
| 0 |
Mary |
wine |
| 1 |
Joseph |
beer |
pd.merge(df6, df7, how='left')
|
name |
food |
drink |
| 0 |
Peter |
fish |
NaN |
| 1 |
Paul |
beans |
NaN |
| 2 |
Mary |
bread |
wine |
.. code:: ipython3
display('df6', 'df7', "pd.merge(df6, df7, how='right')")
.. raw:: html
df6
|
name |
food |
| 0 |
Peter |
fish |
| 1 |
Paul |
beans |
| 2 |
Mary |
bread |
df7
|
name |
drink |
| 0 |
Mary |
wine |
| 1 |
Joseph |
beer |
pd.merge(df6, df7, how='right')
|
name |
food |
drink |
| 0 |
Mary |
bread |
wine |
| 1 |
Joseph |
NaN |
beer |
.. code:: ipython3
pd.merge?
Overlapping Column Names: The ``suffixes`` Keyword
--------------------------------------------------
Finally, you may end up in a case where your two input ``DataFrame``\ s
have conflicting column names:
.. code:: ipython3
df8 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
'rank': [1, 2, 3, 4]})
df9 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
'rank': [3, 1, 4, 2]})
display('df8', 'df9', 'pd.merge(df8, df9, on="name")')
.. raw:: html
df8
|
name |
rank |
| 0 |
Bob |
1 |
| 1 |
Jake |
2 |
| 2 |
Lisa |
3 |
| 3 |
Sue |
4 |
df9
|
name |
rank |
| 0 |
Bob |
3 |
| 1 |
Jake |
1 |
| 2 |
Lisa |
4 |
| 3 |
Sue |
2 |
pd.merge(df8, df9, on="name")
|
name |
rank_x |
rank_y |
| 0 |
Bob |
1 |
3 |
| 1 |
Jake |
2 |
1 |
| 2 |
Lisa |
3 |
4 |
| 3 |
Sue |
4 |
2 |
Because the output would have two conflicting column names, the merge
function automatically appends a suffix ``_x`` or ``_y`` to make the
output columns unique. If these defaults are inappropriate, it is
possible to specify a custom suffix using the ``suffixes`` keyword:
.. code:: ipython3
display('df8', 'df9', 'pd.merge(df8, df9, on="name", suffixes=["_L", "_R"])')
.. raw:: html
df8
|
name |
rank |
| 0 |
Bob |
1 |
| 1 |
Jake |
2 |
| 2 |
Lisa |
3 |
| 3 |
Sue |
4 |
df9
|
name |
rank |
| 0 |
Bob |
3 |
| 1 |
Jake |
1 |
| 2 |
Lisa |
4 |
| 3 |
Sue |
2 |
pd.merge(df8, df9, on="name", suffixes=["_L", "_R"])
|
name |
rank_L |
rank_R |
| 0 |
Bob |
1 |
3 |
| 1 |
Jake |
2 |
1 |
| 2 |
Lisa |
3 |
4 |
| 3 |
Sue |
4 |
2 |
Example: US States Data
-----------------------
Merge and join operations come up most often when combining data from
different sources. Here we will consider an example of some data about
US states and their populations. The data files can be found at
http://github.com/jakevdp/data-USstates/:
.. code:: ipython3
pop = pd.read_csv('data/state-population.csv')
areas = pd.read_csv('data/state-areas.csv')
abbrevs = pd.read_csv('data/state-abbrevs.csv')
display('pop.head()', 'areas.head()', 'abbrevs.head()')
.. raw:: html
pop.head()
|
state/region |
ages |
year |
population |
| 0 |
AL |
under18 |
2012 |
1117489.0 |
| 1 |
AL |
total |
2012 |
4817528.0 |
| 2 |
AL |
under18 |
2010 |
1130966.0 |
| 3 |
AL |
total |
2010 |
4785570.0 |
| 4 |
AL |
under18 |
2011 |
1125763.0 |
areas.head()
|
state |
area (sq. mi) |
| 0 |
Alabama |
52423 |
| 1 |
Alaska |
656425 |
| 2 |
Arizona |
114006 |
| 3 |
Arkansas |
53182 |
| 4 |
California |
163707 |
abbrevs.head()
|
state |
abbreviation |
| 0 |
Alabama |
AL |
| 1 |
Alaska |
AK |
| 2 |
Arizona |
AZ |
| 3 |
Arkansas |
AR |
| 4 |
California |
CA |
Given this information, say we want to compute a relatively
straightforward result: rank US states and territories by their 2010
population density. We clearly have the data here to find this result,
but we’ll have to combine the datasets to find the result.
We’ll start with a many-to-one merge that will give us the full state
name within the population ``DataFrame``. We want to merge based on the
``state/region`` column of ``pop``, and the ``abbreviation`` column of
``abbrevs``. We’ll use ``how='outer'`` to make sure no data is thrown
away due to mismatched labels.
.. code:: ipython3
merged = pd.merge(pop, abbrevs, how='outer',
left_on='state/region', right_on='abbreviation')
merged = merged.drop('abbreviation', 1) # drop duplicate info
merged.head()
.. raw:: html
|
state/region |
ages |
year |
population |
state |
| 0 |
AL |
under18 |
2012 |
1117489.0 |
Alabama |
| 1 |
AL |
total |
2012 |
4817528.0 |
Alabama |
| 2 |
AL |
under18 |
2010 |
1130966.0 |
Alabama |
| 3 |
AL |
total |
2010 |
4785570.0 |
Alabama |
| 4 |
AL |
under18 |
2011 |
1125763.0 |
Alabama |
Let’s double-check whether there were any mismatches here, which we can
do by looking for rows with nulls:
.. code:: ipython3
merged.isnull().any()
.. parsed-literal::
state/region False
ages False
year False
population True
state True
dtype: bool
Some of the ``population`` info is null:
.. code:: ipython3
merged[merged['population'].isnull()].head()
.. raw:: html
|
state/region |
ages |
year |
population |
state |
| 2448 |
PR |
under18 |
1990 |
NaN |
NaN |
| 2449 |
PR |
total |
1990 |
NaN |
NaN |
| 2450 |
PR |
total |
1991 |
NaN |
NaN |
| 2451 |
PR |
under18 |
1991 |
NaN |
NaN |
| 2452 |
PR |
total |
1993 |
NaN |
NaN |
It appears that all the null population values are from Puerto Rico
prior to the year 2000; this is likely due to this data not being
available from the original source.
More importantly, we see also that some of the new ``state`` entries are
also null, which means that there was no corresponding entry in the
``abbrevs`` key! Let’s figure out which regions lack this match:
.. code:: ipython3
merged.loc[merged['state'].isnull(), 'state/region'].unique()
.. parsed-literal::
array(['PR', 'USA'], dtype=object)
We can quickly infer the issue: our population data includes entries for
Puerto Rico (PR) and the United States as a whole (USA), while these
entries do not appear in the state abbreviation key. We can fix these
quickly by filling in appropriate entries:
.. code:: ipython3
merged.loc[merged['state/region'] == 'PR', 'state'] = 'Puerto Rico'
merged.loc[merged['state/region'] == 'USA', 'state'] = 'United States'
merged.isnull().any()
.. parsed-literal::
state/region False
ages False
year False
population True
state False
dtype: bool
No more nulls in the ``state`` column: we’re all set!
Now we can merge the result with the area data using a similar
procedure. Examining our results, we will want to join on the ``state``
column in both:
.. code:: ipython3
final = pd.merge(merged, areas, on='state', how='left')
final.head()
.. raw:: html
|
state/region |
ages |
year |
population |
state |
area (sq. mi) |
| 0 |
AL |
under18 |
2012 |
1117489.0 |
Alabama |
52423.0 |
| 1 |
AL |
total |
2012 |
4817528.0 |
Alabama |
52423.0 |
| 2 |
AL |
under18 |
2010 |
1130966.0 |
Alabama |
52423.0 |
| 3 |
AL |
total |
2010 |
4785570.0 |
Alabama |
52423.0 |
| 4 |
AL |
under18 |
2011 |
1125763.0 |
Alabama |
52423.0 |
Again, let’s check for nulls to see if there were any mismatches:
.. code:: ipython3
final.isnull().any()
.. parsed-literal::
state/region False
ages False
year False
population True
state False
area (sq. mi) True
dtype: bool
There are nulls in the ``area`` column; we can take a look to see which
regions were ignored here:
.. code:: ipython3
final['state'][final['area (sq. mi)'].isnull()].unique()
.. parsed-literal::
array(['United States'], dtype=object)
We see that our ``areas`` ``DataFrame`` does not contain the area of the
United States as a whole. We could insert the appropriate value (using
the sum of all state areas, for instance), but in this case we’ll just
drop the null values because the population density of the entire United
States is not relevant to our current discussion:
.. code:: ipython3
final.dropna(inplace=True)
final.head()
.. raw:: html
|
state/region |
ages |
year |
population |
state |
area (sq. mi) |
| 0 |
AL |
under18 |
2012 |
1117489.0 |
Alabama |
52423.0 |
| 1 |
AL |
total |
2012 |
4817528.0 |
Alabama |
52423.0 |
| 2 |
AL |
under18 |
2010 |
1130966.0 |
Alabama |
52423.0 |
| 3 |
AL |
total |
2010 |
4785570.0 |
Alabama |
52423.0 |
| 4 |
AL |
under18 |
2011 |
1125763.0 |
Alabama |
52423.0 |
Now we have all the data we need. To answer the question of interest,
let’s first select the portion of the data corresponding with the year
2000, and the total population. We’ll use the ``query()`` function to do
this quickly:
.. code:: ipython3
data2010 = final.query("year == 2010 & ages == 'total'")
data2010.head()
.. raw:: html
|
state/region |
ages |
year |
population |
state |
area (sq. mi) |
| 3 |
AL |
total |
2010 |
4785570.0 |
Alabama |
52423.0 |
| 91 |
AK |
total |
2010 |
713868.0 |
Alaska |
656425.0 |
| 101 |
AZ |
total |
2010 |
6408790.0 |
Arizona |
114006.0 |
| 189 |
AR |
total |
2010 |
2922280.0 |
Arkansas |
53182.0 |
| 197 |
CA |
total |
2010 |
37333601.0 |
California |
163707.0 |
Now let’s compute the population density and display it in order. We’ll
start by re-indexing our data on the state, and then compute the result:
.. code:: ipython3
data2010.set_index('state', inplace=True)
density = data2010['population'] / data2010['area (sq. mi)']
.. code:: ipython3
density.sort_values(ascending=False, inplace=True)
density.head()
.. parsed-literal::
state
District of Columbia 8898.897059
Puerto Rico 1058.665149
New Jersey 1009.253268
Rhode Island 681.339159
Connecticut 645.600649
dtype: float64
The result is a ranking of US states plus Washington, DC, and Puerto
Rico in order of their 2010 population density, in residents per square
mile. We can see that by far the densest region in this dataset is
Washington, DC (i.e., the District of Columbia); among states, the
densest is New Jersey.
We can also check the end of the list:
.. code:: ipython3
density.tail()
.. parsed-literal::
state
South Dakota 10.583512
North Dakota 9.537565
Montana 6.736171
Wyoming 5.768079
Alaska 1.087509
dtype: float64
We see that the least dense state, by far, is Alaska, averaging slightly
over one resident per square mile.
This type of messy data merging is a common task when trying to answer
questions using real-world data sources.
Aggregation and Grouping
========================
An essential piece of analysis of large data is efficient summarization:
computing aggregations like ``sum()``, ``mean()``, ``median()``,
``min()``, and ``max()``, in which a single number gives insight into
the nature of a potentially large dataset.
we’ll use the same ``display`` magic function as usual:
.. code:: ipython3
class display(object):
"""Display HTML representation of multiple objects"""
template = """"""
def __init__(self, *args):
self.args = args
def _repr_html_(self):
return '\n'.join(self.template.format(a, eval(a)._repr_html_())
for a in self.args)
def __repr__(self):
return '\n\n'.join(a + '\n' + repr(eval(a))
for a in self.args)
Planets Data
------------
Here we will use the Planets dataset, available via the `Seaborn
package `__. It gives information on planets
that astronomers have discovered around other stars (known as
*extrasolar planets* or *exoplanets* for short). It can be downloaded
with a simple Seaborn command:
.. code:: ipython3
import seaborn as sns
planets = sns.load_dataset('planets')
planets.shape # 1,000+ extrasolar planets discovered up to 2014.
::
---------------------------------------------------------------------------
SSLCertVerificationError Traceback (most recent call last)
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py in do_open(self, http_class, req, **http_conn_args)
1341 try:
-> 1342 h.request(req.get_method(), req.selector, req.data, headers,
1343 encode_chunked=req.has_header('Transfer-encoding'))
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py in request(self, method, url, body, headers, encode_chunked)
1254 """Send a complete request to the server."""
-> 1255 self._send_request(method, url, body, headers, encode_chunked)
1256
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py in _send_request(self, method, url, body, headers, encode_chunked)
1300 body = _encode(body, 'body')
-> 1301 self.endheaders(body, encode_chunked=encode_chunked)
1302
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py in endheaders(self, message_body, encode_chunked)
1249 raise CannotSendHeader()
-> 1250 self._send_output(message_body, encode_chunked=encode_chunked)
1251
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py in _send_output(self, message_body, encode_chunked)
1009 del self._buffer[:]
-> 1010 self.send(msg)
1011
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py in send(self, data)
949 if self.auto_open:
--> 950 self.connect()
951 else:
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/http/client.py in connect(self)
1423
-> 1424 self.sock = self._context.wrap_socket(self.sock,
1425 server_hostname=server_hostname)
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py in wrap_socket(self, sock, server_side, do_handshake_on_connect, suppress_ragged_eofs, server_hostname, session)
499 # ctx._wrap_socket()
--> 500 return self.sslsocket_class._create(
501 sock=sock,
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py in _create(cls, sock, server_side, do_handshake_on_connect, suppress_ragged_eofs, server_hostname, context, session)
1039 raise ValueError("do_handshake_on_connect should not be specified for non-blocking sockets")
-> 1040 self.do_handshake()
1041 except (OSError, ValueError):
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py in do_handshake(self, block)
1308 self.settimeout(None)
-> 1309 self._sslobj.do_handshake()
1310 finally:
SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1122)
During handling of the above exception, another exception occurred:
URLError Traceback (most recent call last)
in
1 import seaborn as sns
----> 2 planets = sns.load_dataset('planets')
3 planets.shape # 1,000+ extrasolar planets discovered up to 2014.
~/Developer/py-venvs/sphinx-venv/lib/python3.9/site-packages/seaborn/utils.py in load_dataset(name, cache, data_home, **kws)
483 os.path.basename(full_path))
484 if not os.path.exists(cache_path):
--> 485 if name not in get_dataset_names():
486 raise ValueError(f"'{name}' is not one of the example datasets.")
487 urlretrieve(full_path, cache_path)
~/Developer/py-venvs/sphinx-venv/lib/python3.9/site-packages/seaborn/utils.py in get_dataset_names()
417 """
418 url = "https://github.com/mwaskom/seaborn-data"
--> 419 with urlopen(url) as resp:
420 html = resp.read()
421
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py in urlopen(url, data, timeout, cafile, capath, cadefault, context)
212 else:
213 opener = _opener
--> 214 return opener.open(url, data, timeout)
215
216 def install_opener(opener):
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py in open(self, fullurl, data, timeout)
515
516 sys.audit('urllib.Request', req.full_url, req.data, req.headers, req.get_method())
--> 517 response = self._open(req, data)
518
519 # post-process response
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py in _open(self, req, data)
532
533 protocol = req.type
--> 534 result = self._call_chain(self.handle_open, protocol, protocol +
535 '_open', req)
536 if result:
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py in _call_chain(self, chain, kind, meth_name, *args)
492 for handler in handlers:
493 func = getattr(handler, meth_name)
--> 494 result = func(*args)
495 if result is not None:
496 return result
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py in https_open(self, req)
1383
1384 def https_open(self, req):
-> 1385 return self.do_open(http.client.HTTPSConnection, req,
1386 context=self._context, check_hostname=self._check_hostname)
1387
/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/urllib/request.py in do_open(self, http_class, req, **http_conn_args)
1343 encode_chunked=req.has_header('Transfer-encoding'))
1344 except OSError as err: # timeout error
-> 1345 raise URLError(err)
1346 r = h.getresponse()
1347 except:
URLError:
.. code:: ipython3
planets.head()
Simple Aggregation in Pandas
----------------------------
.. code:: ipython3
rng = np.random.RandomState(42)
ser = pd.Series(rng.rand(5))
ser
.. parsed-literal::
0 0.374540
1 0.950714
2 0.731994
3 0.598658
4 0.156019
dtype: float64
.. code:: ipython3
ser.sum(), ser.mean()
.. parsed-literal::
(2.811925491708157, 0.5623850983416314)
For a ``DataFrame``, by default the aggregates return results within
each column:
.. code:: ipython3
df = pd.DataFrame({'A': rng.rand(5), 'B': rng.rand(5)})
df
.. raw:: html
|
A |
B |
| 0 |
0.183405 |
0.611853 |
| 1 |
0.304242 |
0.139494 |
| 2 |
0.524756 |
0.292145 |
| 3 |
0.431945 |
0.366362 |
| 4 |
0.291229 |
0.456070 |
.. code:: ipython3
df.mean()
.. parsed-literal::
A 0.347115
B 0.373185
dtype: float64
By specifying the ``axis`` argument, you can instead aggregate within
each row:
.. code:: ipython3
df.mean(axis='columns')
.. parsed-literal::
0 0.397629
1 0.221868
2 0.408451
3 0.399153
4 0.373650
dtype: float64
Pandas ``Series`` and ``DataFrame``\ s provide a convenience method
``describe()`` that computes several common aggregates for each column
and returns the result:
.. code:: ipython3
planets.dropna().describe() # dropping rows with missing values
.. raw:: html
|
number |
orbital_period |
mass |
distance |
year |
| count |
498.00000 |
498.000000 |
498.000000 |
498.000000 |
498.000000 |
| mean |
1.73494 |
835.778671 |
2.509320 |
52.068213 |
2007.377510 |
| std |
1.17572 |
1469.128259 |
3.636274 |
46.596041 |
4.167284 |
| min |
1.00000 |
1.328300 |
0.003600 |
1.350000 |
1989.000000 |
| 25% |
1.00000 |
38.272250 |
0.212500 |
24.497500 |
2005.000000 |
| 50% |
1.00000 |
357.000000 |
1.245000 |
39.940000 |
2009.000000 |
| 75% |
2.00000 |
999.600000 |
2.867500 |
59.332500 |
2011.000000 |
| max |
6.00000 |
17337.500000 |
25.000000 |
354.000000 |
2014.000000 |
This can be a useful way to begin understanding the overall properties
of a dataset. For example, we see in the ``year`` column that although
exoplanets were discovered as far back as 1989, half of all known
expolanets were not discovered until 2010 or after. This is largely
thanks to the *Kepler* mission, which is a space-based telescope
specifically designed for finding eclipsing planets around other stars.
The following table summarizes some other built-in Pandas aggregations:
======================== ===============================
Aggregation Description
======================== ===============================
``count()`` Total number of items
``first()``, ``last()`` First and last item
``mean()``, ``median()`` Mean and median
``min()``, ``max()`` Minimum and maximum
``std()``, ``var()`` Standard deviation and variance
``mad()`` Mean absolute deviation
``prod()`` Product of all items
``sum()`` Sum of all items
======================== ===============================
These are all methods of ``DataFrame`` and ``Series`` objects.
GroupBy: Split, Apply, Combine
------------------------------
Simple aggregations can give you a flavor of your dataset, but often we
would prefer to aggregate conditionally on some label or index: this is
implemented in the so-called ``groupby`` operation. The name “group by”
comes from a command in the SQL database language, but it is perhaps
more illuminative to think of it in the terms first coined by Hadley
Wickham of Rstats fame: *split, apply, combine*.
Split, apply, combine
~~~~~~~~~~~~~~~~~~~~~
A canonical example of this split-apply-combine operation, where the
“apply” is a summation aggregation, is illustrated in this figure:
.. image:: images/03.08-split-apply-combine.png
This makes clear what the ``groupby`` accomplishes:
- The *split* step involves breaking up and grouping a ``DataFrame``
depending on the value of the specified key.
- The *apply* step involves computing some function, usually an
aggregate, transformation, or filtering, within the individual
groups.
- The *combine* step merges the results of these operations into an
output array.
While this could certainly be done manually using some combination of
the masking, aggregation, and merging commands covered earlier, an
important realization is that *the intermediate splits do not need to be
explicitly instantiated*. Rather, the ``GroupBy`` can (often) do this in
a single pass over the data, updating the sum, mean, count, min, or
other aggregate for each group along the way. The power of the
``GroupBy`` is that it abstracts away these steps: the user need not
think about *how* the computation is done under the hood, but rather
thinks about the *operation as a whole*.
As a concrete example, let’s take a look at using Pandas for the
computation shown in this diagram:
.. code:: ipython3
df = pd.DataFrame({'key': ['A', 'B', 'C', 'A', 'B', 'C'],
'data': range(6)}, columns=['key', 'data'])
df
.. raw:: html
|
key |
data |
| 0 |
A |
0 |
| 1 |
B |
1 |
| 2 |
C |
2 |
| 3 |
A |
3 |
| 4 |
B |
4 |
| 5 |
C |
5 |
The most basic split-apply-combine operation can be computed with the
``groupby()`` method of ``DataFrame``\ s, passing the name of the
desired key column:
.. code:: ipython3
df.groupby('key')
.. parsed-literal::
Notice that what is returned is not a set of ``DataFrame``\ s, but a
``DataFrameGroupBy`` object. This object is where the magic is: you can
think of it as a special view of the ``DataFrame``, which is poised to
dig into the groups but does no actual computation until the aggregation
is applied. This *lazy evaluation* approach means that common aggregates
can be implemented very efficiently in a way that is almost transparent
to the user.
To produce a result, we can apply an aggregate to this
``DataFrameGroupBy`` object, which will perform the appropriate
apply/combine steps to produce the desired result:
.. code:: ipython3
df.groupby('key').sum()
.. raw:: html
.. code:: ipython3
type(_)
.. parsed-literal::
pandas.core.frame.DataFrame
The ``sum()`` method is just one possibility here; you can apply
virtually any common Pandas or NumPy aggregation function, as well as
virtually any valid ``DataFrame`` operation.
The GroupBy object
~~~~~~~~~~~~~~~~~~
The ``GroupBy`` object is a very flexible abstraction. In many ways, you
can simply treat it as if it’s a collection of ``DataFrame``\ s, and it
does the difficult things under the hood. Let’s see some examples using
the Planets data.
Perhaps the most important operations made available by a ``GroupBy``
are *aggregate*, *filter*, *transform*, and *apply* but before that
let’s introduce some of the other functionality that can be used with
the basic ``GroupBy`` operation.
Column indexing
^^^^^^^^^^^^^^^
The ``GroupBy`` object supports column indexing in the same way as the
``DataFrame``, and returns a modified ``GroupBy`` object:
.. code:: ipython3
planets.groupby('method')
.. parsed-literal::
.. code:: ipython3
planets.groupby('method')['orbital_period']
.. parsed-literal::
Here we’ve selected a particular ``Series`` group from the original
``DataFrame`` group by reference to its column name. As with the
``GroupBy`` object, no computation is done until we call some aggregate
on the object:
.. code:: ipython3
planets.groupby('method')['orbital_period'].median()
.. parsed-literal::
method
Astrometry 631.180000
Eclipse Timing Variations 4343.500000
Imaging 27500.000000
Microlensing 3300.000000
Orbital Brightness Modulation 0.342887
Pulsar Timing 66.541900
Pulsation Timing Variations 1170.000000
Radial Velocity 360.200000
Transit 5.714932
Transit Timing Variations 57.011000
Name: orbital_period, dtype: float64
Iteration over groups
^^^^^^^^^^^^^^^^^^^^^
The ``GroupBy`` object supports direct iteration over the groups,
returning each group as a ``Series`` or ``DataFrame``:
.. code:: ipython3
for (method, group) in planets.groupby('method'):
print("{0:30s} shape={1}".format(method, group.shape))
.. parsed-literal::
Astrometry shape=(2, 6)
Eclipse Timing Variations shape=(9, 6)
Imaging shape=(38, 6)
Microlensing shape=(23, 6)
Orbital Brightness Modulation shape=(3, 6)
Pulsar Timing shape=(5, 6)
Pulsation Timing Variations shape=(1, 6)
Radial Velocity shape=(553, 6)
Transit shape=(397, 6)
Transit Timing Variations shape=(4, 6)
Dispatch methods
^^^^^^^^^^^^^^^^
Through some Python class magic, any method not explicitly implemented
by the ``GroupBy`` object will be passed through and called on the
groups, whether they are ``DataFrame`` or ``Series`` objects. For
example, you can use the ``describe()`` method of ``DataFrame``\ s to
perform a set of aggregations that describe each group in the data:
.. code:: ipython3
planets.groupby('method')['year'].describe()
.. raw:: html
|
count |
mean |
std |
min |
25% |
50% |
75% |
max |
| method |
|
|
|
|
|
|
|
|
| Astrometry |
2.0 |
2011.500000 |
2.121320 |
2010.0 |
2010.75 |
2011.5 |
2012.25 |
2013.0 |
| Eclipse Timing Variations |
9.0 |
2010.000000 |
1.414214 |
2008.0 |
2009.00 |
2010.0 |
2011.00 |
2012.0 |
| Imaging |
38.0 |
2009.131579 |
2.781901 |
2004.0 |
2008.00 |
2009.0 |
2011.00 |
2013.0 |
| Microlensing |
23.0 |
2009.782609 |
2.859697 |
2004.0 |
2008.00 |
2010.0 |
2012.00 |
2013.0 |
| Orbital Brightness Modulation |
3.0 |
2011.666667 |
1.154701 |
2011.0 |
2011.00 |
2011.0 |
2012.00 |
2013.0 |
| Pulsar Timing |
5.0 |
1998.400000 |
8.384510 |
1992.0 |
1992.00 |
1994.0 |
2003.00 |
2011.0 |
| Pulsation Timing Variations |
1.0 |
2007.000000 |
NaN |
2007.0 |
2007.00 |
2007.0 |
2007.00 |
2007.0 |
| Radial Velocity |
553.0 |
2007.518987 |
4.249052 |
1989.0 |
2005.00 |
2009.0 |
2011.00 |
2014.0 |
| Transit |
397.0 |
2011.236776 |
2.077867 |
2002.0 |
2010.00 |
2012.0 |
2013.00 |
2014.0 |
| Transit Timing Variations |
4.0 |
2012.500000 |
1.290994 |
2011.0 |
2011.75 |
2012.5 |
2013.25 |
2014.0 |
Looking at this table helps us to better understand the data: for
example, the vast majority of planets have been discovered by the Radial
Velocity and Transit methods, though the latter only became common (due
to new, more accurate telescopes) in the last decade. The newest methods
seem to be Transit Timing Variation and Orbital Brightness Modulation,
which were not used to discover a new planet until 2011.
This is just one example of the utility of dispatch methods. Notice that
they are applied *to each individual group*, and the results are then
combined within ``GroupBy`` and returned. Again, any valid
``DataFrame``/``Series`` method can be used on the corresponding
``GroupBy`` object, which allows for some very flexible and powerful
operations!
Aggregate, filter, transform, apply
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
``GroupBy`` objects have ``aggregate()``, ``filter()``, ``transform()``,
and ``apply()`` methods that efficiently implement a variety of useful
operations before combining the grouped data.
.. code:: ipython3
rng = np.random.RandomState(0)
df = pd.DataFrame({'key': ['A', 'B', 'C', 'A', 'B', 'C'],
'data1': range(6),
'data2': rng.randint(0, 10, 6)},
columns = ['key', 'data1', 'data2'])
df
.. raw:: html
|
key |
data1 |
data2 |
| 0 |
A |
0 |
5 |
| 1 |
B |
1 |
0 |
| 2 |
C |
2 |
3 |
| 3 |
A |
3 |
3 |
| 4 |
B |
4 |
7 |
| 5 |
C |
5 |
9 |
Aggregation
^^^^^^^^^^^
We’re now familiar with ``GroupBy`` aggregations with ``sum()``,
``median()``, and the like, but the ``aggregate()`` method allows for
even more flexibility. It can take a string, a function, or a list
thereof, and compute all the aggregates at once.
.. code:: ipython3
df.groupby('key').aggregate([min, np.median, max])
.. raw:: html
|
data1 |
data2 |
|
min |
median |
max |
min |
median |
max |
| key |
|
|
|
|
|
|
| A |
0 |
1.5 |
3 |
3 |
4.0 |
5 |
| B |
1 |
2.5 |
4 |
0 |
3.5 |
7 |
| C |
2 |
3.5 |
5 |
3 |
6.0 |
9 |
Filtering
^^^^^^^^^
A filtering operation allows you to drop data based on the group
properties. For example, we might want to keep all groups in which the
standard deviation is larger than some critical value:
.. code:: ipython3
def filter_func(x):
return x['data2'].std() > 4
display('df', "df.groupby('key').std()", "df.groupby('key').filter(filter_func)")
.. raw:: html
df
|
key |
data1 |
data2 |
| 0 |
A |
0 |
5 |
| 1 |
B |
1 |
0 |
| 2 |
C |
2 |
3 |
| 3 |
A |
3 |
3 |
| 4 |
B |
4 |
7 |
| 5 |
C |
5 |
9 |
df.groupby('key').std()
|
data1 |
data2 |
| key |
|
|
| A |
2.12132 |
1.414214 |
| B |
2.12132 |
4.949747 |
| C |
2.12132 |
4.242641 |
df.groupby('key').filter(filter_func)
|
key |
data1 |
data2 |
| 1 |
B |
1 |
0 |
| 2 |
C |
2 |
3 |
| 4 |
B |
4 |
7 |
| 5 |
C |
5 |
9 |
Transformation
^^^^^^^^^^^^^^
While aggregation must return a reduced version of the data,
transformation can return some transformed version of the full data to
recombine. For such a transformation, the output is the same shape as
the input.
.. code:: ipython3
df.groupby('key').transform(lambda x: x - x.mean())
.. raw:: html
|
data1 |
data2 |
| 0 |
-1.5 |
1.0 |
| 1 |
-1.5 |
-3.5 |
| 2 |
-1.5 |
-3.0 |
| 3 |
1.5 |
-1.0 |
| 4 |
1.5 |
3.5 |
| 5 |
1.5 |
3.0 |
The apply() method
^^^^^^^^^^^^^^^^^^
The ``apply()`` method lets you apply an arbitrary function to the group
results. The function should take a ``DataFrame``, and return either a
Pandas object (e.g., ``DataFrame``, ``Series``) or a scalar; the combine
operation will be tailored to the type of output returned.
.. code:: ipython3
def norm_by_data2(x):
# x is a DataFrame of group values
x['data1'] /= x['data2'].sum()
return x
display('df', "df.groupby('key').apply(norm_by_data2)")
.. raw:: html
df
|
key |
data1 |
data2 |
| 0 |
A |
0 |
5 |
| 1 |
B |
1 |
0 |
| 2 |
C |
2 |
3 |
| 3 |
A |
3 |
3 |
| 4 |
B |
4 |
7 |
| 5 |
C |
5 |
9 |
df.groupby('key').apply(norm_by_data2)
|
key |
data1 |
data2 |
| 0 |
A |
0.000000 |
5 |
| 1 |
B |
0.142857 |
0 |
| 2 |
C |
0.166667 |
3 |
| 3 |
A |
0.375000 |
3 |
| 4 |
B |
0.571429 |
7 |
| 5 |
C |
0.416667 |
9 |
Specifying the split key
~~~~~~~~~~~~~~~~~~~~~~~~
In the simple examples presented before, we split the ``DataFrame`` on a
single column name. This is just one of many options by which the groups
can be defined, and we’ll go through some other options for group
specification here.
A list, array, series, or index providing the grouping keys
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The key can be any series or list with a length matching that of the
``DataFrame``:
.. code:: ipython3
L = [0, 1, 0, 1, 2, 0]
display('df', 'df.groupby(L).sum()')
.. raw:: html
df
|
key |
data1 |
data2 |
| 0 |
A |
0 |
5 |
| 1 |
B |
1 |
0 |
| 2 |
C |
2 |
3 |
| 3 |
A |
3 |
3 |
| 4 |
B |
4 |
7 |
| 5 |
C |
5 |
9 |
df.groupby(L).sum()
|
data1 |
data2 |
| 0 |
7 |
17 |
| 1 |
4 |
3 |
| 2 |
4 |
7 |
Of course, this means there’s another, more verbose way of accomplishing
the ``df.groupby('key')`` from before:
.. code:: ipython3
display('df', "df.groupby(df['key']).sum()")
.. raw:: html
df
|
key |
data1 |
data2 |
| 0 |
A |
0 |
5 |
| 1 |
B |
1 |
0 |
| 2 |
C |
2 |
3 |
| 3 |
A |
3 |
3 |
| 4 |
B |
4 |
7 |
| 5 |
C |
5 |
9 |
df.groupby(df['key']).sum()
|
data1 |
data2 |
| key |
|
|
| A |
3 |
8 |
| B |
5 |
7 |
| C |
7 |
12 |
A dictionary or series mapping index to group
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Another method is to provide a dictionary that maps index values to the
group keys:
.. code:: ipython3
df2 = df.set_index('key')
mapping = {'A': 'vowel', 'B': 'consonant', 'C': 'consonant'}
display('df2', 'df2.groupby(mapping).sum()')
.. raw:: html
df2
|
data1 |
data2 |
| key |
|
|
| A |
0 |
5 |
| B |
1 |
0 |
| C |
2 |
3 |
| A |
3 |
3 |
| B |
4 |
7 |
| C |
5 |
9 |
df2.groupby(mapping).sum()
|
data1 |
data2 |
| consonant |
12 |
19 |
| vowel |
3 |
8 |
Any Python function
^^^^^^^^^^^^^^^^^^^
Similar to mapping, you can pass any Python function that will input the
index value and output the group:
.. code:: ipython3
display('df2', 'df2.groupby(str.lower).mean()')
.. raw:: html
df2
|
data1 |
data2 |
| key |
|
|
| A |
0 |
5 |
| B |
1 |
0 |
| C |
2 |
3 |
| A |
3 |
3 |
| B |
4 |
7 |
| C |
5 |
9 |
df2.groupby(str.lower).mean()
|
data1 |
data2 |
| a |
1.5 |
4.0 |
| b |
2.5 |
3.5 |
| c |
3.5 |
6.0 |
A list of valid keys
^^^^^^^^^^^^^^^^^^^^
Further, any of the preceding key choices can be combined to group on a
multi-index:
.. code:: ipython3
df2.groupby([str.lower, mapping]).mean()
.. raw:: html
|
|
data1 |
data2 |
| a |
vowel |
1.5 |
4.0 |
| b |
consonant |
2.5 |
3.5 |
| c |
consonant |
3.5 |
6.0 |
Grouping example
~~~~~~~~~~~~~~~~
As an example of this, in a couple lines of Python code we can put all
these together and count discovered planets by method and by decade:
.. code:: ipython3
decade = 10 * (planets['year'] // 10)
decade = decade.astype(str) + 's'
decade.name = 'decade'
planets.groupby(['method', decade])['number'].sum().unstack().fillna(0)
.. raw:: html
| decade |
1980s |
1990s |
2000s |
2010s |
| method |
|
|
|
|
| Astrometry |
0.0 |
0.0 |
0.0 |
2.0 |
| Eclipse Timing Variations |
0.0 |
0.0 |
5.0 |
10.0 |
| Imaging |
0.0 |
0.0 |
29.0 |
21.0 |
| Microlensing |
0.0 |
0.0 |
12.0 |
15.0 |
| Orbital Brightness Modulation |
0.0 |
0.0 |
0.0 |
5.0 |
| Pulsar Timing |
0.0 |
9.0 |
1.0 |
1.0 |
| Pulsation Timing Variations |
0.0 |
0.0 |
1.0 |
0.0 |
| Radial Velocity |
1.0 |
52.0 |
475.0 |
424.0 |
| Transit |
0.0 |
0.0 |
64.0 |
712.0 |
| Transit Timing Variations |
0.0 |
0.0 |
0.0 |
9.0 |
This shows the power of combining many of the operations we’ve discussed
up to this point when looking at realistic datasets. We immediately gain
a coarse understanding of when and how planets have been discovered over
the past several decades!
Pivot Tables
============
We have seen how the ``GroupBy`` abstraction lets us explore
relationships within a dataset. A *pivot table* is a similar operation
that is commonly seen in spreadsheets and other programs that operate on
tabular data: - The pivot table takes simple column-wise data as input,
and groups the entries into a two-dimensional table that provides a
multidimensional summarization of the data. - The difference between
pivot tables and ``GroupBy`` can sometimes cause confusion; it helps me
to think of pivot tables as essentially a *multidimensional* version of
``GroupBy`` aggregation. - That is, you split-apply-combine, but both
the split and the combine happen across not a one-dimensional index, but
across a two-dimensional grid.
Motivating Pivot Tables
-----------------------
We’ll use the database of passengers on the *Titanic*, available through
the Seaborn library:
.. code:: ipython3
import seaborn as sns
titanic = sns.load_dataset('titanic')
titanic.head()
.. raw:: html
|
survived |
pclass |
sex |
age |
sibsp |
parch |
fare |
embarked |
class |
who |
adult_male |
deck |
embark_town |
alive |
alone |
| 0 |
0 |
3 |
male |
22.0 |
1 |
0 |
7.2500 |
S |
Third |
man |
True |
NaN |
Southampton |
no |
False |
| 1 |
1 |
1 |
female |
38.0 |
1 |
0 |
71.2833 |
C |
First |
woman |
False |
C |
Cherbourg |
yes |
False |
| 2 |
1 |
3 |
female |
26.0 |
0 |
0 |
7.9250 |
S |
Third |
woman |
False |
NaN |
Southampton |
yes |
True |
| 3 |
1 |
1 |
female |
35.0 |
1 |
0 |
53.1000 |
S |
First |
woman |
False |
C |
Southampton |
yes |
False |
| 4 |
0 |
3 |
male |
35.0 |
0 |
0 |
8.0500 |
S |
Third |
man |
True |
NaN |
Southampton |
no |
True |
Pivot Tables by Hand
--------------------
To start learning more about this data, we might begin by grouping
according to gender, survival status, or some combination thereof. If
you have read the previous section, you might be tempted to apply a
``GroupBy`` operation–for example, let’s look at survival rate by
gender:
.. code:: ipython3
titanic.groupby('sex')[['survived']].mean()
.. raw:: html
|
survived |
| sex |
|
| female |
0.742038 |
| male |
0.188908 |
This immediately gives us some insight: overall, three of every four
females on board survived, while only one in five males survived.
This is useful, but we might like to go one step deeper and look at
survival by both sex and, say, class. Using the vocabulary of
``GroupBy``, we might proceed using something like this: we *group by*
class and gender, *select* survival, *apply* a mean aggregate, *combine*
the resulting groups, and then *unstack* the hierarchical index to
reveal the hidden multidimensionality. In code:
.. code:: ipython3
titanic.groupby(['sex', 'class'])['survived'].aggregate('mean').unstack()
.. raw:: html
| class |
First |
Second |
Third |
| sex |
|
|
|
| female |
0.968085 |
0.921053 |
0.500000 |
| male |
0.368852 |
0.157407 |
0.135447 |
This gives us a better idea of how both gender and class affected
survival, but the code is starting to look a bit garbled. While each
step of this pipeline makes sense in light of the tools we’ve previously
discussed, the long string of code is not particularly easy to read or
use. This two-dimensional ``GroupBy`` is common enough that Pandas
includes a convenience routine, ``pivot_table``, which succinctly
handles this type of multi-dimensional aggregation.
Pivot Table Syntax
------------------
Here is the equivalent to the preceding operation using the
``pivot_table`` method of ``DataFrame``\ s:
.. code:: ipython3
titanic.pivot_table('survived', index='sex', columns='class')
.. raw:: html
| class |
First |
Second |
Third |
| sex |
|
|
|
| female |
0.968085 |
0.921053 |
0.500000 |
| male |
0.368852 |
0.157407 |
0.135447 |
This is eminently more readable than the ``groupby`` approach, and
produces the same result. As you might expect of an early 20th-century
transatlantic cruise, the survival gradient favors both women and higher
classes. First-class women survived with near certainty (hi, Rose!),
while only one in ten third-class men survived (sorry, Jack!).
Multi-level pivot tables
~~~~~~~~~~~~~~~~~~~~~~~~
Just as in the ``GroupBy``, the grouping in pivot tables can be
specified with multiple levels, and via a number of options. For
example, we might be interested in looking at age as a third dimension.
We’ll bin the age using the ``pd.cut`` function:
.. code:: ipython3
age = pd.cut(titanic['age'], [0, 18, 80])
titanic.pivot_table('survived', ['sex', age], 'class')
.. raw:: html
|
class |
First |
Second |
Third |
| sex |
age |
|
|
|
| female |
(0, 18] |
0.909091 |
1.000000 |
0.511628 |
| (18, 80] |
0.972973 |
0.900000 |
0.423729 |
| male |
(0, 18] |
0.800000 |
0.600000 |
0.215686 |
| (18, 80] |
0.375000 |
0.071429 |
0.133663 |
We can apply the same strategy when working with the columns as well;
let’s add info on the fare paid using ``pd.qcut`` to automatically
compute quantiles:
.. code:: ipython3
fare = pd.qcut(titanic['fare'], 2)
titanic.pivot_table('survived', ['sex', age], [fare, 'class'])
.. raw:: html
|
fare |
(-0.001, 14.454] |
(14.454, 512.329] |
|
class |
First |
Second |
Third |
First |
Second |
Third |
| sex |
age |
|
|
|
|
|
|
| female |
(0, 18] |
NaN |
1.000000 |
0.714286 |
0.909091 |
1.000000 |
0.318182 |
| (18, 80] |
NaN |
0.880000 |
0.444444 |
0.972973 |
0.914286 |
0.391304 |
| male |
(0, 18] |
NaN |
0.000000 |
0.260870 |
0.800000 |
0.818182 |
0.178571 |
| (18, 80] |
0.0 |
0.098039 |
0.125000 |
0.391304 |
0.030303 |
0.192308 |
The result is a four-dimensional aggregation with hierarchical indices,
shown in a grid demonstrating the relationship between the values.
Additional pivot table options
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The full call signature of the ``pivot_table`` method of
``DataFrame``\ s is as follows:
.. code:: python
# call signature as of Pandas 0.18
DataFrame.pivot_table(data, values=None, index=None, columns=None,
aggfunc='mean', fill_value=None, margins=False,
dropna=True, margins_name='All')
We’ve already seen examples of the first three arguments; here we’ll
take a quick look at the remaining ones. Two of the options,
``fill_value`` and ``dropna``, have to do with missing data and are
fairly straightforward; we will not show examples of them here.
The ``aggfunc`` keyword controls what type of aggregation is applied,
which is a mean by default. As in the GroupBy, the aggregation
specification can be a string representing one of several common choices
(e.g., ``'sum'``, ``'mean'``, ``'count'``, ``'min'``, ``'max'``, etc.)
or a function that implements an aggregation (e.g., ``np.sum()``,
``min()``, ``sum()``, etc.).
Additionally, it can be specified as a dictionary mapping a column to
any of the above desired options:
.. code:: ipython3
titanic.pivot_table(index='sex', columns='class',
aggfunc={'survived':sum, 'fare':'mean'})
.. raw:: html
|
fare |
survived |
| class |
First |
Second |
Third |
First |
Second |
Third |
| sex |
|
|
|
|
|
|
| female |
106.125798 |
21.970121 |
16.118810 |
91 |
70 |
72 |
| male |
67.226127 |
19.741782 |
12.661633 |
45 |
17 |
47 |
Notice also here that we’ve omitted the ``values`` keyword; when
specifying a mapping for ``aggfunc``, this is determined automatically.
At times it’s useful to compute totals along each grouping. This can be
done via the ``margins`` keyword:
.. code:: ipython3
titanic.pivot_table('survived', index='sex', columns='class', margins=True)
.. raw:: html
| class |
First |
Second |
Third |
All |
| sex |
|
|
|
|
| female |
0.968085 |
0.921053 |
0.500000 |
0.742038 |
| male |
0.368852 |
0.157407 |
0.135447 |
0.188908 |
| All |
0.629630 |
0.472826 |
0.242363 |
0.383838 |
Here this automatically gives us information about the class-agnostic
survival rate by gender, the gender-agnostic survival rate by class, and
the overall survival rate of 38%. The margin label can be specified with
the ``margins_name`` keyword, which defaults to ``"All"``.
Example: Birthrate Data
-----------------------
As a more interesting example, let’s take a look at the freely available
data on births in the United States, provided by the Centers for Disease
Control (CDC). This data can be found at
https://raw.githubusercontent.com/jakevdp/data-CDCbirths/master/births.csv:
.. code:: ipython3
!curl -O https://raw.githubusercontent.com/jakevdp/data-CDCbirths/master/births.csv
.. parsed-literal::
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 258k 100 258k 0 0 258k 0 --:--:-- --:--:-- --:--:-- 258k
.. code:: ipython3
births = pd.read_csv('data/births.csv')
births.describe()
.. raw:: html
|
year |
month |
day |
births |
| count |
15547.000000 |
15547.000000 |
15067.000000 |
15547.000000 |
| mean |
1979.037435 |
6.515919 |
17.769894 |
9762.293561 |
| std |
6.728340 |
3.449632 |
15.284034 |
28552.465810 |
| min |
1969.000000 |
1.000000 |
1.000000 |
1.000000 |
| 25% |
1974.000000 |
4.000000 |
8.000000 |
4358.000000 |
| 50% |
1979.000000 |
7.000000 |
16.000000 |
4814.000000 |
| 75% |
1984.000000 |
10.000000 |
24.000000 |
5289.500000 |
| max |
2008.000000 |
12.000000 |
99.000000 |
199622.000000 |
Taking a look at the data, we see that it’s relatively simple–it
contains the number of births grouped by date and gender:
.. code:: ipython3
births.head()
.. raw:: html
|
year |
month |
day |
gender |
births |
| 0 |
1969 |
1 |
1.0 |
F |
4046 |
| 1 |
1969 |
1 |
1.0 |
M |
4440 |
| 2 |
1969 |
1 |
2.0 |
F |
4454 |
| 3 |
1969 |
1 |
2.0 |
M |
4548 |
| 4 |
1969 |
1 |
3.0 |
F |
4548 |
We can start to understand this data a bit more by using a pivot table.
Let’s add a decade column, and take a look at male and female births as
a function of decade:
.. code:: ipython3
births['decade'] = 10 * (births['year'] // 10)
births.pivot_table('births', index='decade', columns='gender', aggfunc='sum')
.. raw:: html
| gender |
F |
M |
| decade |
|
|
| 1960 |
1753634 |
1846572 |
| 1970 |
16263075 |
17121550 |
| 1980 |
18310351 |
19243452 |
| 1990 |
19479454 |
20420553 |
| 2000 |
18229309 |
19106428 |
We immediately see that male births outnumber female births in every
decade. To see this trend a bit more clearly, we can use the built-in
plotting tools in Pandas to visualize the total number of births by
year:
.. code:: ipython3
%matplotlib inline
import matplotlib.pyplot as plt
sns.set() # use Seaborn styles
births.pivot_table('births', index='year', columns='gender', aggfunc='sum').plot()
plt.ylabel('total births per year');
.. image:: pandas_files/pandas_477_0.png
With a simple pivot table and ``plot()`` method, we can immediately see
the annual trend in births by gender. By eye, it appears that over the
past 50 years male births have outnumbered female births by around 5%.
Further data exploration
~~~~~~~~~~~~~~~~~~~~~~~~
Though this doesn’t necessarily relate to the pivot table, there are a
few more interesting features we can pull out of this dataset using the
Pandas tools covered up to this point. We must start by cleaning the
data a bit, removing outliers caused by mistyped dates (e.g., June 31st)
or missing values (e.g., June 99th). One easy way to remove these all at
once is to cut outliers; we’ll do this via a robust sigma-clipping
operation:
.. code:: ipython3
quartiles = np.percentile(births['births'], [25, 50, 75])
mu = quartiles[1]
sig = 0.74 * (quartiles[2] - quartiles[0])
This final line is a robust estimate of the sample mean, where the 0.74
comes from the interquartile range of a Gaussian distribution.
With this we can use the ``query()`` method to filter-out rows with
births outside these values:
.. code:: ipython3
births = births.query('(births > @mu - 5 * @sig) & (births < @mu + 5 * @sig)')
Next we set the ``day`` column to integers; previously it had been a
string because some columns in the dataset contained the value
``'null'``:
.. code:: ipython3
# set 'day' column to integer; it originally was a string due to nulls
births['day'] = births['day'].astype(int)
Finally, we can combine the day, month, and year to create a Date index.
This allows us to quickly compute the weekday corresponding to each row:
.. code:: ipython3
# create a datetime index from the year, month, day
births.index = pd.to_datetime(10000 * births.year +
100 * births.month +
births.day, format='%Y%m%d')
births['dayofweek'] = births.index.dayofweek
.. code:: ipython3
import matplotlib.pyplot as plt
import matplotlib as mpl
births.pivot_table('births', index='dayofweek',
columns='decade', aggfunc='mean').plot()
plt.gca().set_xticklabels(['Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat', 'Sun'])
plt.ylabel('mean births by day');
.. image:: pandas_files/pandas_488_0.png
Apparently births are slightly less common on weekends than on weekdays!
Note that the 1990s and 2000s are missing because the CDC data contains
only the month of birth starting in 1989.
Another intersting view is to plot the mean number of births by the day
of the *year*. Let’s first group the data by month and day separately:
.. code:: ipython3
births_by_date = births.pivot_table('births', [births.index.month, births.index.day])
births_by_date.head()
.. raw:: html
|
|
births |
| 1 |
1 |
4009.225 |
| 2 |
4247.400 |
| 3 |
4500.900 |
| 4 |
4571.350 |
| 5 |
4603.625 |
The result is a multi-index over months and days.
To make this easily plottable, let’s turn these months and days into a
date by associating them with a dummy year variable (making sure to
choose a leap year so February 29th is correctly handled!)
.. code:: ipython3
from datetime import datetime
births_by_date.index = [datetime(2012, month, day)
for (month, day) in births_by_date.index]
births_by_date.head()
.. raw:: html
|
births |
| 2012-01-01 |
4009.225 |
| 2012-01-02 |
4247.400 |
| 2012-01-03 |
4500.900 |
| 2012-01-04 |
4571.350 |
| 2012-01-05 |
4603.625 |
Focusing on the month and day only, we now have a time series reflecting
the average number of births by date of the year. From this, we can use
the ``plot`` method to plot the data. It reveals some interesting
trends:
.. code:: ipython3
fig, ax = plt.subplots(figsize=(12, 4))
births_by_date.plot(ax=ax);
.. image:: pandas_files/pandas_496_0.png
In particular, the striking feature of this graph is the dip in
birthrate on US holidays (e.g., Independence Day, Labor Day,
Thanksgiving, Christmas, New Year’s Day) although this likely reflects
trends in scheduled/induced births rather than some deep psychosomatic
effect on natural births.
Looking at this short example, you can see that many of the Python and
Pandas tools we’ve seen to this point can be combined and used to gain
insight from a variety of datasets.
Working with Time Series
========================
Pandas was developed in the context of financial modeling, so as you
might expect, it contains a fairly extensive set of tools for working
with dates, times, and time-indexed data. Date and time data comes in a
few flavors:
- *Time stamps* reference particular moments in time (e.g., July 4th,
2015 at 7:00am).
- *Time intervals* and *periods* reference a length of time between a
particular beginning and end point; for example, the year 2015.
Periods usually reference a special case of time intervals in which
each interval is of uniform length and does not overlap (e.g., 24
hour-long periods comprising days).
- *Time deltas* or *durations* reference an exact length of time (e.g.,
a duration of 22.56 seconds).
Dates and Times in Python
-------------------------
The Python world has a number of available representations of dates,
times, deltas, and timespans. While the time series tools provided by
Pandas tend to be the most useful for data science applications, it is
helpful to see their relationship to other packages used in Python.
Native Python dates and times: ``datetime`` and ``dateutil``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Python’s basic objects for working with dates and times reside in the
built-in ``datetime`` module. Along with the third-party ``dateutil``
module, you can use it to quickly perform a host of useful
functionalities on dates and times:
.. code:: ipython3
from datetime import datetime
datetime(year=2015, month=7, day=4)
.. parsed-literal::
datetime.datetime(2015, 7, 4, 0, 0)
Or, using the ``dateutil`` module, you can parse dates from a variety of
string formats:
.. code:: ipython3
from dateutil import parser
date = parser.parse("4th of July, 2015")
date
.. parsed-literal::
datetime.datetime(2015, 7, 4, 0, 0)
Once you have a ``datetime`` object, you can do things like printing the
day of the week:
.. code:: ipython3
date.strftime('%A')
.. parsed-literal::
'Saturday'
A related package to be aware of is
```pytz`` `__, which contains tools for
working with the most migrane-inducing piece of time series data: time
zones.
The power of ``datetime`` and ``dateutil`` lie in their flexibility and
easy syntax: you can use these objects and their built-in methods to
easily perform nearly any operation you might be interested in. Where
they break down is when you wish to work with large arrays of dates and
times: just as lists of Python numerical variables are suboptimal
compared to NumPy-style typed numerical arrays, lists of Python datetime
objects are suboptimal compared to typed arrays of encoded dates.
Typed arrays of times: NumPy’s ``datetime64``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The weaknesses of Python’s datetime format inspired the NumPy team to
add a set of native time series data type to NumPy. The ``datetime64``
dtype encodes dates as 64-bit integers, and thus allows arrays of dates
to be represented very compactly.
.. code:: ipython3
date = np.array('2015-07-04', dtype=np.datetime64)
date
.. parsed-literal::
array('2015-07-04', dtype='datetime64[D]')
Once we have this date formatted, however, we can quickly do vectorized
operations on it:
.. code:: ipython3
date + np.arange(12)
.. parsed-literal::
array(['2015-07-04', '2015-07-05', '2015-07-06', '2015-07-07',
'2015-07-08', '2015-07-09', '2015-07-10', '2015-07-11',
'2015-07-12', '2015-07-13', '2015-07-14', '2015-07-15'],
dtype='datetime64[D]')
Because of the uniform type in NumPy ``datetime64`` arrays, this type of
operation can be accomplished much more quickly than if we were working
directly with Python’s ``datetime`` objects, especially as arrays get
large.
One detail of the ``datetime64`` and ``timedelta64`` objects is that
they are built on a *fundamental time unit*. Because the ``datetime64``
object is limited to 64-bit precision, the range of encodable times is
:math:`2^{64}` times this fundamental unit. In other words,
``datetime64`` imposes a trade-off between *time resolution* and
*maximum time span*.
If you want a time resolution of one nanosecond, you only have enough
information to encode a range of :math:`2^{64}` nanoseconds, or just
under 600 years:
.. code:: ipython3
np.datetime64('2015-07-04') # a day-based datetime
.. parsed-literal::
numpy.datetime64('2015-07-04')
.. code:: ipython3
np.datetime64('2015-07-04 12:00') # a minute-based datetime
.. parsed-literal::
numpy.datetime64('2015-07-04T12:00')
.. code:: ipython3
np.datetime64('2015-07-04 12:59:59.50', 'ns') # a nanosecond-based datetiem
.. parsed-literal::
numpy.datetime64('2015-07-04T12:59:59.500000000')
The following table lists the available format codes along with the
relative and absolute timespans that they can encode:
====== =========== ==================== ======================
Code Meaning Time span (relative) Time span (absolute)
====== =========== ==================== ======================
``Y`` Year ± 9.2e18 years [9.2e18 BC, 9.2e18 AD]
``M`` Month ± 7.6e17 years [7.6e17 BC, 7.6e17 AD]
``W`` Week ± 1.7e17 years [1.7e17 BC, 1.7e17 AD]
``D`` Day ± 2.5e16 years [2.5e16 BC, 2.5e16 AD]
``h`` Hour ± 1.0e15 years [1.0e15 BC, 1.0e15 AD]
``m`` Minute ± 1.7e13 years [1.7e13 BC, 1.7e13 AD]
``s`` Second ± 2.9e12 years [ 2.9e9 BC, 2.9e9 AD]
``ms`` Millisecond ± 2.9e9 years [ 2.9e6 BC, 2.9e6 AD]
``us`` Microsecond ± 2.9e6 years [290301 BC, 294241 AD]
``ns`` Nanosecond ± 292 years [ 1678 AD, 2262 AD]
``ps`` Picosecond ± 106 days [ 1969 AD, 1970 AD]
``fs`` Femtosecond ± 2.6 hours [ 1969 AD, 1970 AD]
``as`` Attosecond ± 9.2 seconds [ 1969 AD, 1970 AD]
====== =========== ==================== ======================
Dates and times in pandas: best of both worlds
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Pandas builds upon all the tools just discussed to provide a
``Timestamp`` object, which combines the ease-of-use of ``datetime`` and
``dateutil`` with the efficient storage and vectorized interface of
``numpy.datetime64``. From a group of these ``Timestamp`` objects,
Pandas can construct a ``DatetimeIndex`` that can be used to index data
in a ``Series`` or ``DataFrame``:
.. code:: ipython3
date = pd.to_datetime("4th of July, 2015")
date
.. parsed-literal::
Timestamp('2015-07-04 00:00:00')
.. code:: ipython3
date.strftime('%A')
.. parsed-literal::
'Saturday'
.. code:: ipython3
date + pd.to_timedelta(np.arange(12), 'D') # do NumPy-style vectorized operations directly on this same object
.. parsed-literal::
DatetimeIndex(['2015-07-04', '2015-07-05', '2015-07-06', '2015-07-07',
'2015-07-08', '2015-07-09', '2015-07-10', '2015-07-11',
'2015-07-12', '2015-07-13', '2015-07-14', '2015-07-15'],
dtype='datetime64[ns]', freq=None)
Pandas Time Series: Indexing by Time
------------------------------------
Where the Pandas time series tools really become useful is when you
begin to *index data by timestamps*:
.. code:: ipython3
index = pd.DatetimeIndex(['2014-07-04', '2014-08-04', '2015-07-04', '2015-08-04'])
data = pd.Series([0, 1, 2, 3], index=index)
data
.. parsed-literal::
2014-07-04 0
2014-08-04 1
2015-07-04 2
2015-08-04 3
dtype: int64
Now that we have this data in a ``Series``, we can make use of any of
the ``Series`` indexing patterns passing values that can be coerced into
dates:
.. code:: ipython3
data['2014-07-04':'2015-07-04']
.. parsed-literal::
2014-07-04 0
2014-08-04 1
2015-07-04 2
dtype: int64
.. code:: ipython3
data['2015'] # additional special date-only indexing operations
.. parsed-literal::
2015-07-04 2
2015-08-04 3
dtype: int64
Pandas Time Series Data Structures
----------------------------------
The fundamental Pandas data structures for working with time series
data:
- For *time stamps*, Pandas provides the ``Timestamp`` type. As
mentioned before, it is essentially a replacement for Python’s native
``datetime``, but is based on the more efficient ``numpy.datetime64``
data type. The associated Index structure is ``DatetimeIndex``.
- For *time Periods*, Pandas provides the ``Period`` type. This encodes
a fixed-frequency interval based on ``numpy.datetime64``. The
associated index structure is ``PeriodIndex``.
- For *time deltas* or *durations*, Pandas provides the ``Timedelta``
type. ``Timedelta`` is a more efficient replacement for Python’s
native ``datetime.timedelta`` type, and is based on
``numpy.timedelta64``. The associated index structure is
``TimedeltaIndex``.
The most fundamental of these date/time objects are the ``Timestamp``
and ``DatetimeIndex`` objects. While these class objects can be invoked
directly, it is more common to use the ``pd.to_datetime()`` function,
which can parse a wide variety of formats. Passing a single date to
``pd.to_datetime()`` yields a ``Timestamp``; passing a series of dates
by default yields a ``DatetimeIndex``:
.. code:: ipython3
dates = pd.to_datetime([datetime(2015, 7, 3), '4th of July, 2015',
'2015-Jul-6', '07-07-2015', '20150708'])
dates
.. parsed-literal::
DatetimeIndex(['2015-07-03', '2015-07-04', '2015-07-06', '2015-07-07',
'2015-07-08'],
dtype='datetime64[ns]', freq=None)
Any ``DatetimeIndex`` can be converted to a ``PeriodIndex`` with the
``to_period()`` function with the addition of a frequency code; here
we’ll use ``'D'`` to indicate daily frequency:
.. code:: ipython3
dates.to_period('D')
.. parsed-literal::
PeriodIndex(['2015-07-03', '2015-07-04', '2015-07-06', '2015-07-07',
'2015-07-08'],
dtype='period[D]', freq='D')
.. code:: ipython3
dates - dates[0] # A ``TimedeltaIndex`` is createdwhen subtracting
.. parsed-literal::
TimedeltaIndex(['0 days', '1 days', '3 days', '4 days', '5 days'], dtype='timedelta64[ns]', freq=None)
Regular sequences: ``pd.date_range()``
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To make the creation of regular date sequences more convenient, Pandas
offers a few functions for this purpose: ``pd.date_range()`` for
timestamps, ``pd.period_range()`` for periods, and
``pd.timedelta_range()`` for time deltas:
.. code:: ipython3
pd.date_range('2015-07-03', '2015-07-10') # by default, the frequency is one day
.. parsed-literal::
DatetimeIndex(['2015-07-03', '2015-07-04', '2015-07-05', '2015-07-06',
'2015-07-07', '2015-07-08', '2015-07-09', '2015-07-10'],
dtype='datetime64[ns]', freq='D')
.. code:: ipython3
pd.date_range('2015-07-03', periods=8)
.. parsed-literal::
DatetimeIndex(['2015-07-03', '2015-07-04', '2015-07-05', '2015-07-06',
'2015-07-07', '2015-07-08', '2015-07-09', '2015-07-10'],
dtype='datetime64[ns]', freq='D')
.. code:: ipython3
pd.date_range('2015-07-03', periods=8, freq='H')
.. parsed-literal::
DatetimeIndex(['2015-07-03 00:00:00', '2015-07-03 01:00:00',
'2015-07-03 02:00:00', '2015-07-03 03:00:00',
'2015-07-03 04:00:00', '2015-07-03 05:00:00',
'2015-07-03 06:00:00', '2015-07-03 07:00:00'],
dtype='datetime64[ns]', freq='H')
.. code:: ipython3
pd.period_range('2015-07', periods=8, freq='M')
.. parsed-literal::
PeriodIndex(['2015-07', '2015-08', '2015-09', '2015-10', '2015-11', '2015-12',
'2016-01', '2016-02'],
dtype='period[M]', freq='M')
.. code:: ipython3
pd.timedelta_range(0, periods=10, freq='H')
.. parsed-literal::
TimedeltaIndex(['00:00:00', '01:00:00', '02:00:00', '03:00:00', '04:00:00',
'05:00:00', '06:00:00', '07:00:00', '08:00:00', '09:00:00'],
dtype='timedelta64[ns]', freq='H')
Frequencies and Offsets
-----------------------
Fundamental to these Pandas time series tools is the concept of a
frequency or date offset. Just as we saw the ``D`` (day) and ``H``
(hour) codes above, we can use such codes to specify any desired
frequency spacing:
===== ============ ====== ====================
Code Description Code Description
===== ============ ====== ====================
``D`` Calendar day ``B`` Business day
``W`` Weekly
``M`` Month end ``BM`` Business month end
``Q`` Quarter end ``BQ`` Business quarter end
``A`` Year end ``BA`` Business year end
``H`` Hours ``BH`` Business hours
``T`` Minutes
``S`` Seconds
``L`` Milliseonds
``U`` Microseconds
``N`` nanoseconds
===== ============ ====== ====================
The monthly, quarterly, and annual frequencies are all marked at the end
of the specified period. By adding an ``S`` suffix to any of these, they
instead will be marked at the beginning:
| Code \| Description \|\| Code \| Description \|
\|———|————————||———|————————\| \| ``MS`` \| Month start \|\|\ ``BMS`` \|
Business month start \| \| ``QS`` \| Quarter start \|\|\ ``BQS`` \|
Business quarter start \| \| ``AS`` \| Year start \|\|\ ``BAS`` \|
Business year start \|
Additionally, you can change the month used to mark any quarterly or
annual code by adding a three-letter month code as a suffix:
- ``Q-JAN``, ``BQ-FEB``, ``QS-MAR``, ``BQS-APR``, etc.
- ``A-JAN``, ``BA-FEB``, ``AS-MAR``, ``BAS-APR``, etc.
In the same way, the split-point of the weekly frequency can be modified
by adding a three-letter weekday code:
- ``W-SUN``, ``W-MON``, ``W-TUE``, ``W-WED``, etc.
On top of this, codes can be combined with numbers to specify other
frequencies. For example, for a frequency of 2 hours 30 minutes, we can
combine the hour (``H``) and minute (``T``) codes as follows:
.. code:: ipython3
pd.timedelta_range(0, periods=9, freq="2H30T")
.. parsed-literal::
TimedeltaIndex(['00:00:00', '02:30:00', '05:00:00', '07:30:00', '10:00:00',
'12:30:00', '15:00:00', '17:30:00', '20:00:00'],
dtype='timedelta64[ns]', freq='150T')
All of these short codes refer to specific instances of Pandas time
series offsets, which can be found in the ``pd.tseries.offsets`` module.
For example, we can create a business day offset directly as follows:
.. code:: ipython3
from pandas.tseries.offsets import BDay
pd.date_range('2015-07-01', periods=5, freq=BDay())
.. parsed-literal::
DatetimeIndex(['2015-07-01', '2015-07-02', '2015-07-03', '2015-07-06',
'2015-07-07'],
dtype='datetime64[ns]', freq='B')
Resampling, Shifting, and Windowing
-----------------------------------
The ability to use dates and times as indices to intuitively organize
and access data is an important piece of the Pandas time series tools.
The benefits of indexed data in general (automatic alignment during
operations, intuitive data slicing and access, etc.) still apply, and
Pandas provides several additional time series-specific operations.
We will take a look at a few of those here, using some stock price data
as an example. Because Pandas was developed largely in a finance
context, it includes some very specific tools for financial data.
For example, the accompanying ``pandas-datareader`` package (installable
via ``pip install pandas-datareader``), knows how to import financial
data from a number of available sources, including Yahoo finance, Google
Finance, and others.
Download some data (**ADD A DESCRIPTION FOR IT**)
.. code:: ipython3
from pandas_datareader import data
goog = data.DataReader('VIXCLS', start='2004', end='2016', data_source='fred')
goog.head()
.. raw:: html
|
VIXCLS |
| DATE |
|
| 2004-01-01 |
NaN |
| 2004-01-02 |
18.22 |
| 2004-01-05 |
17.49 |
| 2004-01-06 |
16.73 |
| 2004-01-07 |
15.50 |
.. code:: ipython3
goog = goog['VIXCLS'] # for simplicity consider its sole column
.. code:: ipython3
%matplotlib inline
import matplotlib.pyplot as plt
import seaborn; seaborn.set()
goog.plot();
.. image:: pandas_files/pandas_548_0.png
Resampling and converting frequencies
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
One common need for time series data is resampling at a higher or lower
frequency. This can be done using the ``resample()`` method, or the much
simpler ``asfreq()`` method. The primary difference between the two is
that ``resample()`` is fundamentally a *data aggregation*, while
``asfreq()`` is fundamentally a *data selection*.
Let’s compare what the two return when we down-sample the data; we will
resample the data at the end of business year:
.. code:: ipython3
goog.plot(alpha=0.5, style='-')
goog.resample('BA').mean().plot(style=':')
goog.asfreq('BA').plot(style='--');
plt.legend(['input', 'resample', 'asfreq'], loc='upper left');
.. image:: pandas_files/pandas_551_0.png
Notice the difference: at each point, ``resample`` reports the *average
of the previous year*, while ``asfreq`` reports the *value at the end of
the year*.
For up-sampling, ``resample()`` and ``asfreq()`` are largely equivalent,
though resample has many more options available. In this case, the
default for both methods is to leave the up-sampled points empty, that
is, filled with NA values. Just as with the ``pd.fillna()`` function
discussed previously, ``asfreq()`` accepts a ``method`` argument to
specify how values are imputed.
We resample the business day data at a daily frequency (i.e., including
weekends):
.. code:: ipython3
fig, ax = plt.subplots(2, sharex=True)
data = goog.iloc[:10]
data.asfreq('D').plot(ax=ax[0], marker='o')
data.asfreq('D', method='bfill').plot(ax=ax[1], style='-o')
data.asfreq('D', method='ffill').plot(ax=ax[1], style='--o')
ax[1].legend(["back-fill", "forward-fill"]);
.. image:: pandas_files/pandas_555_0.png
The top panel is the default: non-business days are left as NA values
and do not appear on the plot. The bottom panel shows the differences
between two strategies for filling the gaps: forward-filling and
backward-filling.
Time-shifts
~~~~~~~~~~~
Another common time series-specific operation is shifting of data in
time. Pandas has two closely related methods for computing this:
``shift()`` and ``tshift()`` In short, the difference between them is
that ``shift()`` *shifts the data*, while ``tshift()`` *shifts the
index*. In both cases, the shift is specified in multiples of the
frequency.
Here we will both ``shift()`` and ``tshift()`` by 900 days;
.. code:: ipython3
fig, ax = plt.subplots(3, sharey=True)
# apply a frequency to the data
goog = goog.asfreq('D', method='pad')
goog.plot(ax=ax[0])
goog.shift(900).plot(ax=ax[1])
goog.tshift(900).plot(ax=ax[2])
# legends and annotations
local_max = pd.to_datetime('2007-11-05')
offset = pd.Timedelta(900, 'D')
ax[0].legend(['input'], loc=2)
ax[0].get_xticklabels()[2].set(weight='heavy', color='red')
ax[0].axvline(local_max, alpha=0.3, color='red')
ax[1].legend(['shift(900)'], loc=2)
ax[1].get_xticklabels()[2].set(weight='heavy', color='red')
ax[1].axvline(local_max + offset, alpha=0.3, color='red')
ax[2].legend(['tshift(900)'], loc=2)
ax[2].get_xticklabels()[1].set(weight='heavy', color='red')
ax[2].axvline(local_max + offset, alpha=0.3, color='red');
.. image:: pandas_files/pandas_558_0.png
We see here that ``shift(900)`` shifts the *data* by 900 days, pushing
some of it off the end of the graph (and leaving NA values at the other
end), while ``tshift(900)`` shifts the *index values* by 900 days.
A common context for this type of shift is in computing differences over
time. For example, we use shifted values to compute the one-year return
on investment for Google stock over the course of the dataset:
.. code:: ipython3
ROI = 100 * (goog.tshift(-365) / goog - 1)
ROI.plot()
plt.ylabel('% Return on Investment');
.. image:: pandas_files/pandas_560_0.png
This helps us to see the overall trend in Google stock: thus far, the
most profitable times to invest in Google have been (unsurprisingly, in
retrospect) shortly after its IPO, and in the middle of the 2009
recession.
Rolling windows
~~~~~~~~~~~~~~~
Rolling statistics are a third type of time series-specific operation
implemented by Pandas. These can be accomplished via the ``rolling()``
attribute of ``Series`` and ``DataFrame`` objects, which returns a view
similar to what we saw with the ``groupby`` operation (see `Aggregation
and Grouping <03.08-Aggregation-and-Grouping.ipynb>`__). This rolling
view makes available a number of aggregation operations by default.
For example, here is the one-year centered rolling mean and standard
deviation of the Google stock prices:
.. code:: ipython3
rolling = goog.rolling(40, center=True)
data = pd.DataFrame({'input': goog,
'one-year rolling_mean': rolling.mean(),
'one-year rolling_std': rolling.std()})
ax = data.plot(style=['-', '--', ':'])
ax.lines[0].set_alpha(0.3)
.. image:: pandas_files/pandas_564_0.png
As with group-by operations, the ``aggregate()`` and ``apply()`` methods
can be used for custom rolling computations.
Example: Visualizing Seattle Bicycle Counts
-------------------------------------------
As a more involved example of working with some time series data, let’s
take a look at bicycle counts on Seattle’s `Fremont
Bridge `__.
This data comes from an automated bicycle counter, installed in late
2012, which has inductive sensors on the east and west sidewalks of the
bridge. The hourly bicycle counts can be downloaded from
http://data.seattle.gov/; here is the `direct link to the
dataset `__.
As of summer 2016, the CSV can be downloaded as follows:
.. code:: ipython3
!curl -o FremontBridge.csv https://data.seattle.gov/api/views/65db-xm6k/rows.csv?accessType=DOWNLOAD
.. parsed-literal::
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 2024k 0 2024k 0 0 331k 0 --:--:-- 0:00:06 --:--:-- 462k
Once this dataset is downloaded, we can use Pandas to read the CSV
output into a ``DataFrame``. We will specify that we want the Date as an
index, and we want these dates to be automatically parsed:
.. code:: ipython3
data = pd.read_csv('data/FremontBridge.csv', index_col='Date', parse_dates=True)
data.head()
.. raw:: html
|
Fremont Bridge Total |
Fremont Bridge East Sidewalk |
Fremont Bridge West Sidewalk |
| Date |
|
|
|
| 2012-10-03 00:00:00 |
13.0 |
4.0 |
9.0 |
| 2012-10-03 01:00:00 |
10.0 |
4.0 |
6.0 |
| 2012-10-03 02:00:00 |
2.0 |
1.0 |
1.0 |
| 2012-10-03 03:00:00 |
5.0 |
2.0 |
3.0 |
| 2012-10-03 04:00:00 |
7.0 |
6.0 |
1.0 |
We’ll further process this dataset by shortening the column names and
adding a “Total” column:
.. code:: ipython3
data['Total'] = data["Fremont Bridge Total"]
data.dropna().describe() # have a look at summary statistics
.. raw:: html
|
Fremont Bridge Total |
Fremont Bridge East Sidewalk |
Fremont Bridge West Sidewalk |
Total |
| count |
64934.000000 |
64934.000000 |
64934.000000 |
64934.000000 |
| mean |
113.805033 |
51.976191 |
61.828842 |
113.805033 |
| std |
145.235402 |
67.013247 |
90.605138 |
145.235402 |
| min |
0.000000 |
0.000000 |
0.000000 |
0.000000 |
| 25% |
14.000000 |
6.000000 |
7.000000 |
14.000000 |
| 50% |
61.000000 |
28.000000 |
30.000000 |
61.000000 |
| 75% |
148.000000 |
70.000000 |
74.000000 |
148.000000 |
| max |
1097.000000 |
698.000000 |
850.000000 |
1097.000000 |
Visualizing the data
~~~~~~~~~~~~~~~~~~~~
We can gain some insight into the dataset by visualizing it:
.. code:: ipython3
%matplotlib inline
import seaborn; seaborn.set()
.. code:: ipython3
data.plot()
plt.ylabel('Hourly Bicycle Count');
.. image:: pandas_files/pandas_574_0.png
The ~25,000 hourly samples are far too dense for us to make much sense
of. We can gain more insight by resampling the data to a coarser grid:
.. code:: ipython3
weekly = data.resample('W').sum()
weekly.plot(style=[':', '--', '-'])
plt.ylabel('Weekly bicycle count');
.. image:: pandas_files/pandas_576_0.png
This shows us some interesting seasonal trends: as you might expect,
people bicycle more in the summer than in the winter, and even within a
particular season the bicycle use varies from week to week (likely
dependent on weather.
Another way that comes in handy for aggregating the data is to use a
rolling mean, utilizing the ``pd.rolling_mean()`` function. Here we’ll
do a 30 day rolling mean of our data, making sure to center the window:
.. code:: ipython3
daily = data.resample('D').sum()
daily.rolling(30, center=True).sum().plot(style=[':', '--', '-'])
plt.ylabel('mean hourly count');
.. image:: pandas_files/pandas_579_0.png
The jaggedness of the result is due to the hard cutoff of the window. We
can get a smoother version of a rolling mean using a window function–for
example, a Gaussian window.
The following code specifies both the width of the window (we chose 50
days) and the width of the Gaussian within the window (we chose 10
days):
.. code:: ipython3
daily.rolling(50, center=True,
win_type='gaussian').sum(std=10).plot(style=[':', '--', '-']);
.. image:: pandas_files/pandas_582_0.png
Digging into the data
~~~~~~~~~~~~~~~~~~~~~
While these smoothed data views are useful to get an idea of the general
trend in the data, they hide much of the interesting structure. For
example, we might want to look at the average traffic as a function of
the time of day; we do this by grouping:
.. code:: ipython3
by_time = data.groupby(data.index.time).mean()
hourly_ticks = 4 * 60 * 60 * np.arange(6)
by_time.plot(xticks=hourly_ticks, style=[':', '--', '-']);
.. image:: pandas_files/pandas_584_0.png
The hourly traffic is a strongly bimodal distribution, with peaks around
8:00 in the morning and 5:00 in the evening. This is likely evidence of
a strong component of commuter traffic crossing the bridge. This is
further evidenced by the differences between the western sidewalk
(generally used going toward downtown Seattle), which peaks more
strongly in the morning, and the eastern sidewalk (generally used going
away from downtown Seattle), which peaks more strongly in the evening.
We also might be curious about how things change based on the day of the
week. Again, we can do this with a simple groupby:
.. code:: ipython3
by_weekday = data.groupby(data.index.dayofweek).mean()
by_weekday.index = ['Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat', 'Sun']
by_weekday.plot(style=[':', '--', '-']);
.. image:: pandas_files/pandas_587_0.png
This shows a strong distinction between weekday and weekend totals, with
around twice as many average riders crossing the bridge on Monday
through Friday than on Saturday and Sunday.
With this in mind, let’s do a compound GroupBy and look at the hourly
trend on weekdays versus weekends. We’ll start by grouping by both a
flag marking the weekend, and the time of day:
.. code:: ipython3
weekend = np.where(data.index.weekday < 5, 'Weekday', 'Weekend')
by_time = data.groupby([weekend, data.index.time]).mean()
.. code:: ipython3
import matplotlib.pyplot as plt
fig, ax = plt.subplots(1, 2, figsize=(14, 5))
by_time.loc['Weekday'].plot(ax=ax[0], title='Weekdays',
xticks=hourly_ticks, style=[':', '--', '-'])
by_time.loc['Weekend'].plot(ax=ax[1], title='Weekends',
xticks=hourly_ticks, style=[':', '--', '-']);
.. image:: pandas_files/pandas_591_0.png
The result is very interesting: we see a bimodal commute pattern during
the work week, and a unimodal recreational pattern during the weekends.