.. _reshaping:
{{ header }}
**************************
Reshaping and pivot tables
**************************
.. _reshaping.reshaping:
pandas provides methods for manipulating a :class:`Series` and :class:`DataFrame` to alter the
representation of the data for further data processing or data summarization.
* :func:`~pandas.pivot` and :func:`~pandas.pivot_table`: Group unique values within one or more discrete categories.
* :meth:`~DataFrame.stack` and :meth:`~DataFrame.unstack`: Pivot a column or row level to the opposite axis respectively.
* :func:`~pandas.melt` and :func:`~pandas.wide_to_long`: Unpivot a wide :class:`DataFrame` to a long format.
* :func:`~pandas.get_dummies` and :func:`~pandas.from_dummies`: Conversions with indicator variables.
* :meth:`~Series.explode`: Convert a column of list-like values to individual rows.
* :func:`~pandas.crosstab`: Calculate a cross-tabulation of multiple 1 dimensional factor arrays.
* :func:`~pandas.cut`: Transform continuous variables to discrete, categorical values
* :func:`~pandas.factorize`: Encode 1 dimensional variables into integer labels.
:func:`~pandas.pivot` and :func:`~pandas.pivot_table`
-----------------------------------------------------
.. image:: ../_static/reshaping_pivot.png
:func:`~pandas.pivot`
~~~~~~~~~~~~~~~~~~~~~
Data is often stored in so-called "stacked" or "record" format. In a "record" or "wide" format,
typically there is one row for each subject. In the "stacked" or "long" format there are
multiple rows for each subject where applicable.
.. ipython:: python
data = {
"value": range(12),
"variable": ["A"] * 3 + ["B"] * 3 + ["C"] * 3 + ["D"] * 3,
"date": pd.to_datetime(["2020-01-03", "2020-01-04", "2020-01-05"] * 4)
}
df = pd.DataFrame(data)
To perform time series operations with each unique variable, 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, we use the :meth:`DataFrame.pivot` method (also implemented as a
top level function :func:`~pandas.pivot`):
.. ipython:: python
pivoted = df.pivot(index="date", columns="variable", values="value")
pivoted
If the ``values`` argument is omitted, and the input :class:`DataFrame` has more than
one column of values which are not used as column or index inputs to :meth:`~DataFrame.pivot`,
then the resulting "pivoted" :class:`DataFrame` will have :ref:`hierarchical columns
<advanced.hierarchical>` whose topmost level indicates the respective value
column:
.. ipython:: python
df["value2"] = df["value"] * 2
pivoted = df.pivot(index="date", columns="variable")
pivoted
You can then select subsets from the pivoted :class:`DataFrame`:
.. ipython:: python
pivoted["value2"]
Note that this returns a view on the underlying data in the case where the data
are homogeneously-typed.
.. note::
:func:`~pandas.pivot` can only handle unique rows specified by ``index`` and ``columns``.
If you data contains duplicates, use :func:`~pandas.pivot_table`.
.. _reshaping.pivot:
:func:`~pandas.pivot_table`
~~~~~~~~~~~~~~~~~~~~~~~~~~~
While :meth:`~DataFrame.pivot` provides general purpose pivoting with various
data types, pandas also provides :func:`~pandas.pivot_table` or :meth:`~DataFrame.pivot_table`
for pivoting with aggregation of numeric data.
The function :func:`~pandas.pivot_table` can be used to create spreadsheet-style
pivot tables. See the :ref:`cookbook<cookbook.pivot>` for some advanced
strategies.
.. ipython:: python
import datetime
df = pd.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),
"F": [datetime.datetime(2013, i, 1) for i in range(1, 13)]
+ [datetime.datetime(2013, i, 15) for i in range(1, 13)],
}
)
df
pd.pivot_table(df, values="D", index=["A", "B"], columns=["C"])
pd.pivot_table(
df, values=["D", "E"],
index=["B"],
columns=["A", "C"],
aggfunc="sum",
)
pd.pivot_table(
df, values="E",
index=["B", "C"],
columns=["A"],
aggfunc=["sum", "mean"],
)
The result is a :class:`DataFrame` potentially having a :class:`MultiIndex` on the
index or column. If the ``values`` column name is not given, the pivot table
will include all of the data in an additional level of hierarchy in the columns:
.. ipython:: python
pd.pivot_table(df[["A", "B", "C", "D", "E"]], index=["A", "B"], columns=["C"])
Also, you can use :class:`Grouper` for ``index`` and ``columns`` keywords. For detail of :class:`Grouper`, see :ref:`Grouping with a Grouper specification <groupby.specify>`.
.. ipython:: python
pd.pivot_table(df, values="D", index=pd.Grouper(freq="ME", key="F"), columns="C")
.. _reshaping.pivot.margins:
Adding margins
^^^^^^^^^^^^^^
Passing ``margins=True`` to :meth:`~DataFrame.pivot_table` will add a row and column with an
``All`` label with partial group aggregates across the categories on the
rows and columns:
.. ipython:: python
table = df.pivot_table(
index=["A", "B"],
columns="C",
values=["D", "E"],
margins=True,
aggfunc="std"
)
table
Additionally, you can call :meth:`DataFrame.stack` to display a pivoted DataFrame
as having a multi-level index:
.. ipython:: python
table.stack(future_stack=True)
.. _reshaping.stacking:
:meth:`~DataFrame.stack` and :meth:`~DataFrame.unstack`
-------------------------------------------------------
.. image:: ../_static/reshaping_stack.png
Closely related to the :meth:`~DataFrame.pivot` method are the related
:meth:`~DataFrame.stack` and :meth:`~DataFrame.unstack` methods available on
:class:`Series` and :class:`DataFrame`. These methods are designed to work together with
:class:`MultiIndex` objects (see the section on :ref:`hierarchical indexing
<advanced.hierarchical>`).
* :meth:`~DataFrame.stack`: "pivot" a level of the (possibly hierarchical) column labels,
returning a :class:`DataFrame` with an index with a new inner-most level of row
labels.
* :meth:`~DataFrame.unstack`: (inverse operation of :meth:`~DataFrame.stack`) "pivot" a level of the
(possibly hierarchical) row index to the column axis, producing a reshaped
:class:`DataFrame` with a new inner-most level of column labels.
.. image:: ../_static/reshaping_unstack.png
.. ipython:: python
tuples = [
["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"],
["one", "two", "one", "two", "one", "two", "one", "two"],
]
index = pd.MultiIndex.from_arrays(tuples, names=["first", "second"])
df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=["A", "B"])
df2 = df[:4]
df2
The :meth:`~DataFrame.stack` function "compresses" a level in the :class:`DataFrame` columns to
produce either:
* A :class:`Series`, in the case of a :class:`Index` in the columns.
* A :class:`DataFrame`, in the case of a :class:`MultiIndex` in the columns.
If the columns have a :class:`MultiIndex`, you can choose which level to stack. The
stacked level becomes the new lowest level in a :class:`MultiIndex` on the columns:
.. ipython:: python
stacked = df2.stack(future_stack=True)
stacked
With a "stacked" :class:`DataFrame` or :class:`Series` (having a :class:`MultiIndex` as the
``index``), the inverse operation of :meth:`~DataFrame.stack` is :meth:`~DataFrame.unstack`, which by default
unstacks the **last level**:
.. ipython:: python
stacked.unstack()
stacked.unstack(1)
stacked.unstack(0)
.. _reshaping.unstack_by_name:
.. image:: ../_static/reshaping_unstack_1.png
If the indexes have names, you can use the level names instead of specifying
the level numbers:
.. ipython:: python
stacked.unstack("second")
.. image:: ../_static/reshaping_unstack_0.png
Notice that the :meth:`~DataFrame.stack` and :meth:`~DataFrame.unstack` methods implicitly sort the index
levels involved. Hence a call to :meth:`~DataFrame.stack` and then :meth:`~DataFrame.unstack`, or vice versa,
will result in a **sorted** copy of the original :class:`DataFrame` or :class:`Series`:
.. ipython:: python
index = pd.MultiIndex.from_product([[2, 1], ["a", "b"]])
df = pd.DataFrame(np.random.randn(4), index=index, columns=["A"])
df
all(df.unstack().stack(future_stack=True) == df.sort_index())
.. _reshaping.stack_multiple:
Multiple levels
~~~~~~~~~~~~~~~
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.
.. ipython:: python
columns = pd.MultiIndex.from_tuples(
[
("A", "cat", "long"),
("B", "cat", "long"),
("A", "dog", "short"),
("B", "dog", "short"),
],
names=["exp", "animal", "hair_length"],
)
df = pd.DataFrame(np.random.randn(4, 4), columns=columns)
df
df.stack(level=["animal", "hair_length"], future_stack=True)
The list of levels can contain either level names or level numbers but
not a mixture of the two.
.. ipython:: python
# df.stack(level=['animal', 'hair_length'], future_stack=True)
# from above is equivalent to:
df.stack(level=[1, 2], future_stack=True)
Missing data
~~~~~~~~~~~~
Unstacking can result in missing values if subgroups do not have the same
set of labels. By default, missing values will be replaced with the default
fill value for that data type.
.. ipython:: python
columns = pd.MultiIndex.from_tuples(
[
("A", "cat"),
("B", "dog"),
("B", "cat"),
("A", "dog"),
],
names=["exp", "animal"],
)
index = pd.MultiIndex.from_product(
[("bar", "baz", "foo", "qux"), ("one", "two")], names=["first", "second"]
)
df = pd.DataFrame(np.random.randn(8, 4), index=index, columns=columns)
df3 = df.iloc[[0, 1, 4, 7], [1, 2]]
df3
df3.unstack()
The missing value can be filled with a specific value with the ``fill_value`` argument.
.. ipython:: python
df3.unstack(fill_value=-1e9)
.. _reshaping.melt:
:func:`~pandas.melt` and :func:`~pandas.wide_to_long`
-----------------------------------------------------
.. image:: ../_static/reshaping_melt.png
The top-level :func:`~pandas.melt` function and the corresponding :meth:`DataFrame.melt`
are useful to massage a :class:`DataFrame` into a format where one or more columns
are *identifier variables*, while all other columns, considered *measured
variables*, are "unpivoted" to the row axis, leaving just two non-identifier
columns, "variable" and "value". The names of those columns can be customized
by supplying the ``var_name`` and ``value_name`` parameters.
.. ipython:: python
cheese = pd.DataFrame(
{
"first": ["John", "Mary"],
"last": ["Doe", "Bo"],
"height": [5.5, 6.0],
"weight": [130, 150],
}
)
cheese
cheese.melt(id_vars=["first", "last"])
cheese.melt(id_vars=["first", "last"], var_name="quantity")
When transforming a DataFrame using :func:`~pandas.melt`, the index will be ignored.
The original index values can be kept by setting the ``ignore_index=False`` parameter to ``False`` (default is ``True``).
``ignore_index=False`` will however duplicate index values.
.. ipython:: python
index = pd.MultiIndex.from_tuples([("person", "A"), ("person", "B")])
cheese = pd.DataFrame(
{
"first": ["John", "Mary"],
"last": ["Doe", "Bo"],
"height": [5.5, 6.0],
"weight": [130, 150],
},
index=index,
)
cheese
cheese.melt(id_vars=["first", "last"])
cheese.melt(id_vars=["first", "last"], ignore_index=False)
:func:`~pandas.wide_to_long` is similar to :func:`~pandas.melt` with more customization for
column matching.
.. ipython:: python
dft = pd.DataFrame(
{
"A1970": {0: "a", 1: "b", 2: "c"},
"A1980": {0: "d", 1: "e", 2: "f"},
"B1970": {0: 2.5, 1: 1.2, 2: 0.7},
"B1980": {0: 3.2, 1: 1.3, 2: 0.1},
"X": dict(zip(range(3), np.random.randn(3))),
}
)
dft["id"] = dft.index
dft
pd.wide_to_long(dft, ["A", "B"], i="id", j="year")
.. _reshaping.dummies:
:func:`~pandas.get_dummies` and :func:`~pandas.from_dummies`
------------------------------------------------------------
To convert categorical variables of a :class:`Series` into a "dummy" or "indicator",
:func:`~pandas.get_dummies` creates a new :class:`DataFrame` with columns of the unique
variables and the values representing the presence of those variables per row.
.. ipython:: python
df = pd.DataFrame({"key": list("bbacab"), "data1": range(6)})
pd.get_dummies(df["key"])
df["key"].str.get_dummies()
``prefix`` adds a prefix to the the column names which is useful for merging the result
with the original :class:`DataFrame`:
.. ipython:: python
dummies = pd.get_dummies(df["key"], prefix="key")
dummies
df[["data1"]].join(dummies)
This function is often used along with discretization functions like :func:`~pandas.cut`:
.. ipython:: python
values = np.random.randn(10)
values
bins = [0, 0.2, 0.4, 0.6, 0.8, 1]
pd.get_dummies(pd.cut(values, bins))
:func:`get_dummies` also accepts a :class:`DataFrame`. By default, ``object``, ``string``,
or ``categorical`` type columns are encoded as dummy variables with other columns unaltered.
.. ipython:: python
df = pd.DataFrame({"A": ["a", "b", "a"], "B": ["c", "c", "b"], "C": [1, 2, 3]})
pd.get_dummies(df)
Specifying the ``columns`` keyword will encode a column of any type.
.. ipython:: python
pd.get_dummies(df, columns=["A"])
As with the :class:`Series` version, you can pass values for the ``prefix`` and
``prefix_sep``. By default the column name is used as the prefix and ``_`` as
the prefix separator. You can specify ``prefix`` and ``prefix_sep`` in 3 ways:
* string: Use the same value for ``prefix`` or ``prefix_sep`` for each column
to be encoded.
* list: Must be the same length as the number of columns being encoded.
* dict: Mapping column name to prefix.
.. ipython:: python
simple = pd.get_dummies(df, prefix="new_prefix")
simple
from_list = pd.get_dummies(df, prefix=["from_A", "from_B"])
from_list
from_dict = pd.get_dummies(df, prefix={"B": "from_B", "A": "from_A"})
from_dict
To avoid collinearity when feeding the result to statistical models,
specify ``drop_first=True``.
.. ipython:: python
s = pd.Series(list("abcaa"))
pd.get_dummies(s)
pd.get_dummies(s, drop_first=True)
When a column contains only one level, it will be omitted in the result.
.. ipython:: python
df = pd.DataFrame({"A": list("aaaaa"), "B": list("ababc")})
pd.get_dummies(df)
pd.get_dummies(df, drop_first=True)
The values can be cast to a different type using the ``dtype`` argument.
.. ipython:: python
df = pd.DataFrame({"A": list("abc"), "B": [1.1, 2.2, 3.3]})
pd.get_dummies(df, dtype=np.float32).dtypes
.. versionadded:: 1.5.0
:func:`~pandas.from_dummies` converts the output of :func:`~pandas.get_dummies` back into
a :class:`Series` of categorical values from indicator values.
.. ipython:: python
df = pd.DataFrame({"prefix_a": [0, 1, 0], "prefix_b": [1, 0, 1]})
df
pd.from_dummies(df, sep="_")
Dummy coded data only requires ``k - 1`` categories to be included, in this case
the last category is the default category. The default category can be modified with
``default_category``.
.. ipython:: python
df = pd.DataFrame({"prefix_a": [0, 1, 0]})
df
pd.from_dummies(df, sep="_", default_category="b")
.. _reshaping.explode:
:meth:`~Series.explode`
-----------------------
For a :class:`DataFrame` column with nested, list-like values, :meth:`~Series.explode` will transform
each list-like value to a separate row. The resulting :class:`Index` will be duplicated corresponding
to the index label from the original row:
.. ipython:: python
keys = ["panda1", "panda2", "panda3"]
values = [["eats", "shoots"], ["shoots", "leaves"], ["eats", "leaves"]]
df = pd.DataFrame({"keys": keys, "values": values})
df
df["values"].explode()
:class:`DataFrame.explode` can also explode the column in the :class:`DataFrame`.
.. ipython:: python
df.explode("values")
:meth:`Series.explode` will replace empty lists with a missing value indicator and preserve scalar entries.
.. ipython:: python
s = pd.Series([[1, 2, 3], "foo", [], ["a", "b"]])
s
s.explode()
A comma-separated string value can be split into individual values in a list and then exploded to a new row.
.. ipython:: python
df = pd.DataFrame([{"var1": "a,b,c", "var2": 1}, {"var1": "d,e,f", "var2": 2}])
df.assign(var1=df.var1.str.split(",")).explode("var1")
.. _reshaping.crosstabulations:
:func:`~pandas.crosstab`
------------------------
Use :func:`~pandas.crosstab` to compute a cross-tabulation of two (or more)
factors. By default :func:`~pandas.crosstab` computes a frequency table of the factors
unless an array of values and an aggregation function are passed.
Any :class:`Series` passed will have their name attributes used unless row or column
names for the cross-tabulation are specified
.. ipython:: python
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)
pd.crosstab(a, [b, c], rownames=["a"], colnames=["b", "c"])
If :func:`~pandas.crosstab` receives only two :class:`Series`, it will provide a frequency table.
.. ipython:: python
df = pd.DataFrame(
{"A": [1, 2, 2, 2, 2], "B": [3, 3, 4, 4, 4], "C": [1, 1, np.nan, 1, 1]}
)
df
pd.crosstab(df["A"], df["B"])
:func:`~pandas.crosstab` can also summarize to :class:`Categorical` data.
.. ipython:: python
foo = pd.Categorical(["a", "b"], categories=["a", "b", "c"])
bar = pd.Categorical(["d", "e"], categories=["d", "e", "f"])
pd.crosstab(foo, bar)
For :class:`Categorical` data, to include **all** of data categories even if the actual data does
not contain any instances of a particular category, use ``dropna=False``.
.. ipython:: python
pd.crosstab(foo, bar, dropna=False)
Normalization
~~~~~~~~~~~~~
Frequency tables can also be normalized to show percentages rather than counts
using the ``normalize`` argument:
.. ipython:: python
pd.crosstab(df["A"], df["B"], normalize=True)
``normalize`` can also normalize values within each row or within each column:
.. ipython:: python
pd.crosstab(df["A"], df["B"], normalize="columns")
:func:`~pandas.crosstab` can also accept a third :class:`Series` and an aggregation function
(``aggfunc``) that will be applied to the values of the third :class:`Series` within
each group defined by the first two :class:`Series`:
.. ipython:: python
pd.crosstab(df["A"], df["B"], values=df["C"], aggfunc="sum")
Adding margins
~~~~~~~~~~~~~~
``margins=True`` will add a row and column with an ``All`` label with partial group aggregates
across the categories on the rows and columns:
.. ipython:: python
pd.crosstab(
df["A"], df["B"], values=df["C"], aggfunc="sum", normalize=True, margins=True
)
.. _reshaping.tile:
.. _reshaping.tile.cut:
:func:`~pandas.cut`
-------------------
The :func:`~pandas.cut` function computes groupings for the values of the input
array and is often used to transform continuous variables to discrete or
categorical variables:
An integer ``bins`` will form equal-width bins.
.. ipython:: python
ages = np.array([10, 15, 13, 12, 23, 25, 28, 59, 60])
pd.cut(ages, bins=3)
A list of ordered bin edges will assign an interval for each variable.
.. ipython:: python
pd.cut(ages, bins=[0, 18, 35, 70])
If the ``bins`` keyword is an :class:`IntervalIndex`, then these will be
used to bin the passed data.
.. ipython:: python
pd.cut(ages, bins=pd.IntervalIndex.from_breaks([0, 40, 70]))
.. _reshaping.factorize:
:func:`~pandas.factorize`
-------------------------
:func:`~pandas.factorize` encodes 1 dimensional values into integer labels. Missing values
are encoded as ``-1``.
.. ipython:: python
x = pd.Series(["A", "A", np.nan, "B", 3.14, np.inf])
x
labels, uniques = pd.factorize(x)
labels
uniques
:class:`Categorical` will similarly encode 1 dimensional values for further
categorical operations
.. ipython:: python
pd.Categorical(x)