.. currentmodule:: pandas
.. ipython:: python :suppress: import numpy as np np.random.seed(123456) from pandas import * from pandas.core.reshape import * import pandas.util.testing as tm randn = np.random.randn np.set_printoptions(precision=4, suppress=True) from pandas.tools.tile import *
.. ipython::
:suppress:
In [1]: import pandas.util.testing as tm; tm.N = 3
In [2]: def unpivot(frame):
...: N, K = frame.shape
...: data = {'value' : frame.values.ravel('F'),
...: 'variable' : np.asarray(frame.columns).repeat(N),
...: 'date' : np.tile(np.asarray(frame.index), K)}
...: columns = ['date', 'variable', 'value']
...: return DataFrame(data, columns=columns)
...:
In [3]: df = unpivot(tm.makeTimeDataFrame())
Data is often stored in CSV files or databases in so-called "stacked" or "record" format:
.. ipython:: python df
For the curious here is how the above DataFrame was created:
import pandas.util.testing as tm; tm.N = 3
def unpivot(frame):
N, K = frame.shape
data = {'value' : frame.values.ravel('F'),
'variable' : np.asarray(frame.columns).repeat(N),
'date' : np.tile(np.asarray(frame.index), K)}
return DataFrame(data, columns=['date', 'variable', 'value'])
df = unpivot(tm.makeTimeDataFrame())To select out everything for variable A we could do:
.. ipython:: python df[df['variable'] == 'A']
But suppose we wish to do time series operations with the variables. A better
representation would be where the columns are the unique variables and an
index of dates identifies individual observations. To reshape the data into
this form, use the pivot function:
.. ipython:: python df.pivot(index='date', columns='variable', values='value')
If the values argument is omitted, and the input DataFrame has more than
one column of values which are not used as column or index inputs to pivot,
then the resulting "pivoted" DataFrame will have :ref:`hierarchical columns
<indexing.hierarchical>` whose topmost level indicates the respective value
column:
.. ipython:: python
df['value2'] = df['value'] * 2
pivoted = df.pivot('date', 'variable')
pivoted
You of course can then select subsets from the pivoted DataFrame:
.. ipython:: python pivoted['value2']
Note that this returns a view on the underlying data in the case where the data are homogeneously-typed.
Closely related to the pivot function are the related stack and
unstack functions currently available on Series and DataFrame. These
functions are designed to work together with MultiIndex objects (see the
section on :ref:`hierarchical indexing <indexing.hierarchical>`). Here are
essentially what these functions do:
stack: "pivot" a level of the (possibly hierarchical) column labels, returning a DataFrame with an index with a new inner-most level of row labels.unstack: inverse operation fromstack: "pivot" a level of the (possibly hierarchical) row index to the column axis, producing a reshaped DataFrame with a new inner-most level of column labels.
The clearest way to explain is by example. Let's take a prior example data set from the hierarchical indexing section:
.. ipython:: python
tuples = zip(*[['bar', 'bar', 'baz', 'baz',
'foo', 'foo', 'qux', 'qux'],
['one', 'two', 'one', 'two',
'one', 'two', 'one', 'two']])
index = MultiIndex.from_tuples(tuples, names=['first', 'second'])
df = DataFrame(randn(8, 2), index=index, columns=['A', 'B'])
df2 = df[:4]
df2
The stack function "compresses" a level in the DataFrame's columns to
produce either:
- A Series, in the case of a simple column Index
- A DataFrame, in the case of a
MultiIndexin the columns
If the columns have a MultiIndex, you can choose which level to stack. The
stacked level becomes the new lowest level in a MultiIndex on the columns:
.. ipython:: python stacked = df2.stack() stacked
With a "stacked" DataFrame or Series (having a MultiIndex as the
index), the inverse operation of stack is unstack, which by default
unstacks the last level:
.. ipython:: python stacked.unstack() stacked.unstack(1) stacked.unstack(0)
If the indexes have names, you can use the level names instead of specifying the level numbers:
.. ipython:: python
stacked.unstack('second')
You may also stack or unstack more than one level at a time by passing a list of levels, in which case the end result is as if each level in the list were processed individually.
These functions are intelligent about handling missing data and do not expect
each subgroup within the hierarchical index to have the same set of labels.
They also can handle the index being unsorted (but you can make it sorted by
calling sortlevel, of course). Here is a more complex example:
.. ipython:: python
columns = MultiIndex.from_tuples([('A', 'cat'), ('B', 'dog'),
('B', 'cat'), ('A', 'dog')],
names=['exp', 'animal'])
df = DataFrame(randn(8, 4), index=index, columns=columns)
df2 = df.ix[[0, 1, 2, 4, 5, 7]]
df2
As mentioned above, stack can be called with a level argument to select
which level in the columns to stack:
.. ipython:: python
df2.stack('exp')
df2.stack('animal')
Unstacking when the columns are a MultiIndex is also careful about doing
the right thing:
.. ipython:: python df[:3].unstack(0) df2.unstack(1)
The melt function found in pandas.core.reshape is useful to massage a
DataFrame into a format where one or more columns are identifier variables,
while all other columns, considered measured variables, are "pivoted" to the
row axis, leaving just two non-identifier columns, "variable" and "value".
For instance,
.. ipython:: python
cheese = DataFrame({'first' : ['John', 'Mary'],
'last' : ['Doe', 'Bo'],
'height' : [5.5, 6.0],
'weight' : [130, 150]})
cheese
melt(cheese, id_vars=['first', 'last'])
It should be no shock that combining pivot / stack / unstack with
GroupBy and the basic Series and DataFrame statistical functions can produce
some very expressive and fast data manipulations.
.. ipython:: python df df.stack().mean(1).unstack() # same result, another way df.groupby(level=1, axis=1).mean() df.stack().groupby(level=1).mean() df.mean().unstack(0)
The function pandas.pivot_table can be used to create spreadsheet-style pivot
tables. It takes a number of arguments
data: A DataFrame objectvalues: a column or a list of columns to aggregaterows: list of columns to group by on the table rowscols: list of columns to group by on the table columnsaggfunc: function to use for aggregation, defaulting tonumpy.mean
Consider a data set like this:
.. ipython:: python
df = DataFrame({'A' : ['one', 'one', 'two', 'three'] * 6,
'B' : ['A', 'B', 'C'] * 8,
'C' : ['foo', 'foo', 'foo', 'bar', 'bar', 'bar'] * 4,
'D' : np.random.randn(24),
'E' : np.random.randn(24)})
df
We can produce pivot tables from this data very easily:
.. ipython:: python pivot_table(df, values='D', rows=['A', 'B'], cols=['C']) pivot_table(df, values='D', rows=['B'], cols=['A', 'C'], aggfunc=np.sum) pivot_table(df, values=['D','E'], rows=['B'], cols=['A', 'C'], aggfunc=np.sum)
The result object is a DataFrame having potentially hierarchical indexes on the
rows and columns. If the values column name is not given, the pivot table
will include all of the data that can be aggregated in an additional level of
hierarchy in the columns:
.. ipython:: python pivot_table(df, rows=['A', 'B'], cols=['C'])
You can render a nice output of the table omitting the missing values by
calling to_string if you wish:
.. ipython:: python table = pivot_table(df, rows=['A', 'B'], cols=['C']) print table.to_string(na_rep='')
Note that pivot_table is also available as an instance method on DataFrame.
Use the crosstab function to compute a cross-tabulation of two (or more)
factors. By default crosstab computes a frequency table of the factors
unless an array of values and an aggregation function are passed.
It takes a number of arguments
rows: array-like, values to group by in the rowscols: array-like, values to group by in the columnsvalues: array-like, optional, array of values to aggregate according to the factorsaggfunc: function, optional, If no values array is passed, computes a frequency tablerownames: sequence, default None, must match number of row arrays passedcolnames: sequence, default None, if passed, must match number of column arrays passedmargins: boolean, default False, Add row/column margins (subtotals)
Any Series passed will have their name attributes used unless row or column names for the cross-tabulation are specified
For example:
.. ipython:: python
foo, bar, dull, shiny, one, two = 'foo', 'bar', 'dull', 'shiny', 'one', 'two'
a = np.array([foo, foo, bar, bar, foo, foo], dtype=object)
b = np.array([one, one, two, one, two, one], dtype=object)
c = np.array([dull, dull, shiny, dull, dull, shiny], dtype=object)
crosstab(a, [b, c], rownames=['a'], colnames=['b', 'c'])
If you pass margins=True to pivot_table, special All columns and
rows will be added with partial group aggregates across the categories on the
rows and columns:
.. ipython:: python df.pivot_table(rows=['A', 'B'], cols='C', margins=True, aggfunc=np.std)
The cut function computes groupings for the values of the input array and
is often used to transform continuous variables to discrete or categorical
variables:
.. ipython:: python ages = np.array([10, 15, 13, 12, 23, 25, 28, 59, 60]) cut(ages, bins=3)
If the bins keyword is an integer, then equal-width bins are formed.
Alternatively we can specify custom bin-edges:
.. ipython:: python cut(ages, bins=[0, 18, 35, 70])