.. _merging:
{{ header }}
.. ipython:: python
:suppress:
from matplotlib import pyplot as plt
import pandas.util._doctools as doctools
p = doctools.TablePlotter()
************************************
Merge, join, concatenate and compare
************************************
pandas provides various methods for combining and comparing :class:`Series` or
:class:`DataFrame`.
* :func:`~pandas.concat`: Merge multiple :class:`Series` or :class:`DataFrame` objects along a shared index or column
* :meth:`DataFrame.join`: Merge multiple :class:`DataFrame` objects along the columns
* :meth:`DataFrame.combine_first`: Update missing values with non-missing values in the same location
* :func:`~pandas.merge`: Combine two :class:`Series` or :class:`DataFrame` objects with SQL-style joining
* :func:`~pandas.merge_ordered`: Combine two :class:`Series` or :class:`DataFrame` objects along an ordered axis
* :func:`~pandas.merge_asof`: Combine two :class:`Series` or :class:`DataFrame` objects by near instead of exact matching keys
* :meth:`Series.compare` and :meth:`DataFrame.compare`: Show differences in values between two :class:`Series` or :class:`DataFrame` objects
.. _merging.concat:
:func:`~pandas.concat`
----------------------
The :func:`~pandas.concat` function concatenates an arbitrary amount of
:class:`Series` or :class:`DataFrame` objects along an axis while
performing optional set logic (union or intersection) of the indexes on
the other axes. Like ``numpy.concatenate``, :func:`~pandas.concat`
takes a list or dict of homogeneously-typed objects and concatenates them.
.. ipython:: python
df1 = pd.DataFrame(
{
"A": ["A0", "A1", "A2", "A3"],
"B": ["B0", "B1", "B2", "B3"],
"C": ["C0", "C1", "C2", "C3"],
"D": ["D0", "D1", "D2", "D3"],
},
index=[0, 1, 2, 3],
)
df2 = pd.DataFrame(
{
"A": ["A4", "A5", "A6", "A7"],
"B": ["B4", "B5", "B6", "B7"],
"C": ["C4", "C5", "C6", "C7"],
"D": ["D4", "D5", "D6", "D7"],
},
index=[4, 5, 6, 7],
)
df3 = pd.DataFrame(
{
"A": ["A8", "A9", "A10", "A11"],
"B": ["B8", "B9", "B10", "B11"],
"C": ["C8", "C9", "C10", "C11"],
"D": ["D8", "D9", "D10", "D11"],
},
index=[8, 9, 10, 11],
)
frames = [df1, df2, df3]
result = pd.concat(frames)
result
.. ipython:: python
:suppress:
@savefig merging_concat_basic.png
p.plot(frames, result, labels=["df1", "df2", "df3"], vertical=True);
plt.close("all");
.. note::
:func:`~pandas.concat` makes a full copy of the data, and iteratively
reusing :func:`~pandas.concat` can create unnecessary copies. Collect all
:class:`DataFrame` or :class:`Series` objects in a list before using
:func:`~pandas.concat`.
.. code-block:: python
frames = [process_your_file(f) for f in files]
result = pd.concat(frames)
.. note::
When concatenating :class:`DataFrame` with named axes, pandas will attempt to preserve
these index/column names whenever possible. In the case where all inputs share a
common name, this name will be assigned to the result. When the input names do
not all agree, the result will be unnamed. The same is true for :class:`MultiIndex`,
but the logic is applied separately on a level-by-level basis.
Joining logic of the resulting axis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The ``join`` keyword specifies how to handle axis values that don't exist in the first
:class:`DataFrame`.
``join='outer'`` takes the union of all axis values
.. ipython:: python
df4 = pd.DataFrame(
{
"B": ["B2", "B3", "B6", "B7"],
"D": ["D2", "D3", "D6", "D7"],
"F": ["F2", "F3", "F6", "F7"],
},
index=[2, 3, 6, 7],
)
result = pd.concat([df1, df4], axis=1)
result
.. ipython:: python
:suppress:
@savefig merging_concat_axis1.png
p.plot([df1, df4], result, labels=["df1", "df4"], vertical=False);
plt.close("all");
``join='inner'`` takes the intersection of the axis values
.. ipython:: python
result = pd.concat([df1, df4], axis=1, join="inner")
result
.. ipython:: python
:suppress:
@savefig merging_concat_axis1_inner.png
p.plot([df1, df4], result, labels=["df1", "df4"], vertical=False);
plt.close("all");
To perform an effective "left" join using the *exact index* from the original
:class:`DataFrame`, result can be reindexed.
.. ipython:: python
result = pd.concat([df1, df4], axis=1).reindex(df1.index)
result
.. ipython:: python
:suppress:
@savefig merging_concat_axis1_join_axes.png
p.plot([df1, df4], result, labels=["df1", "df4"], vertical=False);
plt.close("all");
.. _merging.ignore_index:
Ignoring indexes on the concatenation axis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For :class:`DataFrame` objects which don't have a meaningful index, the ``ignore_index``
ignores overlapping indexes.
.. ipython:: python
result = pd.concat([df1, df4], ignore_index=True, sort=False)
result
.. ipython:: python
:suppress:
@savefig merging_concat_ignore_index.png
p.plot([df1, df4], result, labels=["df1", "df4"], vertical=True);
plt.close("all");
.. _merging.mixed_ndims:
Concatenating :class:`Series` and :class:`DataFrame` together
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You can concatenate a mix of :class:`Series` and :class:`DataFrame` objects. The
:class:`Series` will be transformed to :class:`DataFrame` with the column name as
the name of the :class:`Series`.
.. ipython:: python
s1 = pd.Series(["X0", "X1", "X2", "X3"], name="X")
result = pd.concat([df1, s1], axis=1)
result
.. ipython:: python
:suppress:
@savefig merging_concat_mixed_ndim.png
p.plot([df1, s1], result, labels=["df1", "s1"], vertical=False);
plt.close("all");
Unnamed :class:`Series` will be numbered consecutively.
.. ipython:: python
s2 = pd.Series(["_0", "_1", "_2", "_3"])
result = pd.concat([df1, s2, s2, s2], axis=1)
result
.. ipython:: python
:suppress:
@savefig merging_concat_unnamed_series.png
p.plot([df1, s2], result, labels=["df1", "s2"], vertical=False);
plt.close("all");
``ignore_index=True`` will drop all name references.
.. ipython:: python
result = pd.concat([df1, s1], axis=1, ignore_index=True)
result
.. ipython:: python
:suppress:
@savefig merging_concat_series_ignore_index.png
p.plot([df1, s1], result, labels=["df1", "s1"], vertical=False);
plt.close("all");
Resulting ``keys``
~~~~~~~~~~~~~~~~~~
The ``keys`` argument adds another axis level to the resulting index or column (creating
a :class:`MultiIndex`) associate specific keys with each original :class:`DataFrame`.
.. ipython:: python
result = pd.concat(frames, keys=["x", "y", "z"])
result
result.loc["y"]
.. ipython:: python
:suppress:
@savefig merging_concat_keys.png
p.plot(frames, result, labels=["df1", "df2", "df3"], vertical=True)
plt.close("all");
The ``keys`` argument cane override the column names
when creating a new :class:`DataFrame` based on existing :class:`Series`.
.. ipython:: python
s3 = pd.Series([0, 1, 2, 3], name="foo")
s4 = pd.Series([0, 1, 2, 3])
s5 = pd.Series([0, 1, 4, 5])
pd.concat([s3, s4, s5], axis=1)
pd.concat([s3, s4, s5], axis=1, keys=["red", "blue", "yellow"])
You can also pass a dict to :func:`concat` in which case the dict keys will be used
for the ``keys`` argument unless other ``keys`` argument is specified:
.. ipython:: python
pieces = {"x": df1, "y": df2, "z": df3}
result = pd.concat(pieces)
result
.. ipython:: python
:suppress:
@savefig merging_concat_dict.png
p.plot([df1, df2, df3], result, labels=["df1", "df2", "df3"], vertical=True);
plt.close("all");
.. ipython:: python
result = pd.concat(pieces, keys=["z", "y"])
result
.. ipython:: python
:suppress:
@savefig merging_concat_dict_keys.png
p.plot([df1, df2, df3], result, labels=["df1", "df2", "df3"], vertical=True);
plt.close("all");
The :class:`MultiIndex` created has levels that are constructed from the passed keys and
the index of the :class:`DataFrame` pieces:
.. ipython:: python
result.index.levels
``levels`` argument allows specifying resulting levels associated with the ``keys``
.. ipython:: python
result = pd.concat(
pieces, keys=["x", "y", "z"], levels=[["z", "y", "x", "w"]], names=["group_key"]
)
result
.. ipython:: python
:suppress:
@savefig merging_concat_dict_keys_names.png
p.plot([df1, df2, df3], result, labels=["df1", "df2", "df3"], vertical=True);
plt.close("all");
.. ipython:: python
result.index.levels
.. _merging.append.row:
Appending rows to a :class:`DataFrame`
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you have a :class:`Series` that you want to append as a single row to a :class:`DataFrame`, you can convert the row into a
:class:`DataFrame` and use :func:`concat`
.. ipython:: python
s2 = pd.Series(["X0", "X1", "X2", "X3"], index=["A", "B", "C", "D"])
result = pd.concat([df1, s2.to_frame().T], ignore_index=True)
result
.. ipython:: python
:suppress:
@savefig merging_append_series_as_row.png
p.plot([df1, s2], result, labels=["df1", "s2"], vertical=True);
plt.close("all");
.. _merging.join:
:func:`~pandas.merge`
---------------------
:func:`~pandas.merge` performs join operations similar to relational databases like SQL.
Users who are familiar with SQL but new to pandas can reference a
:ref:`comparison with SQL<compare_with_sql.join>`.
Merge types
~~~~~~~~~~~
:func:`~pandas.merge` implements common SQL style joining operations.
* **one-to-one**: joining two :class:`DataFrame` objects on
their indexes which must contain unique values.
* **many-to-one**: joining a unique index to one or
more columns in a different :class:`DataFrame`.
* **many-to-many** : joining columns on columns.
.. note::
When joining columns on columns, potentially a many-to-many join, any
indexes on the passed :class:`DataFrame` objects **will be discarded**.
For a **many-to-many** join, if a key combination appears
more than once in both tables, the :class:`DataFrame` will have the **Cartesian
product** of the associated data.
.. ipython:: python
left = pd.DataFrame(
{
"key": ["K0", "K1", "K2", "K3"],
"A": ["A0", "A1", "A2", "A3"],
"B": ["B0", "B1", "B2", "B3"],
}
)
right = pd.DataFrame(
{
"key": ["K0", "K1", "K2", "K3"],
"C": ["C0", "C1", "C2", "C3"],
"D": ["D0", "D1", "D2", "D3"],
}
)
result = pd.merge(left, right, on="key")
result
.. ipython:: python
:suppress:
@savefig merging_merge_on_key.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
The ``how`` argument to :func:`~pandas.merge` specifies which keys are
included in the resulting table. If a key combination **does not appear** in
either the left or right tables, the values in the joined table will be
``NA``. Here is a summary of the ``how`` options and their SQL equivalent names:
.. csv-table::
:header: "Merge method", "SQL Join Name", "Description"
:widths: 20, 20, 60
``left``, ``LEFT OUTER JOIN``, Use keys from left frame only
``right``, ``RIGHT OUTER JOIN``, Use keys from right frame only
``outer``, ``FULL OUTER JOIN``, Use union of keys from both frames
``inner``, ``INNER JOIN``, Use intersection of keys from both frames
``cross``, ``CROSS JOIN``, Create the cartesian product of rows of both frames
.. ipython:: python
left = pd.DataFrame(
{
"key1": ["K0", "K0", "K1", "K2"],
"key2": ["K0", "K1", "K0", "K1"],
"A": ["A0", "A1", "A2", "A3"],
"B": ["B0", "B1", "B2", "B3"],
}
)
right = pd.DataFrame(
{
"key1": ["K0", "K1", "K1", "K2"],
"key2": ["K0", "K0", "K0", "K0"],
"C": ["C0", "C1", "C2", "C3"],
"D": ["D0", "D1", "D2", "D3"],
}
)
result = pd.merge(left, right, how="left", on=["key1", "key2"])
result
.. ipython:: python
:suppress:
@savefig merging_merge_on_key_left.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. ipython:: python
result = pd.merge(left, right, how="right", on=["key1", "key2"])
result
.. ipython:: python
:suppress:
@savefig merging_merge_on_key_right.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
.. ipython:: python
result = pd.merge(left, right, how="outer", on=["key1", "key2"])
result
.. ipython:: python
:suppress:
@savefig merging_merge_on_key_outer.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. ipython:: python
result = pd.merge(left, right, how="inner", on=["key1", "key2"])
result
.. ipython:: python
:suppress:
@savefig merging_merge_on_key_inner.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. ipython:: python
result = pd.merge(left, right, how="cross")
result
.. ipython:: python
:suppress:
@savefig merging_merge_cross.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
You can :class:`Series` and a :class:`DataFrame` with a :class:`MultiIndex` if the names of
the :class:`MultiIndex` correspond to the columns from the :class:`DataFrame`. Transform
the :class:`Series` to a :class:`DataFrame` using :meth:`Series.reset_index` before merging
.. ipython:: python
df = pd.DataFrame({"Let": ["A", "B", "C"], "Num": [1, 2, 3]})
df
ser = pd.Series(
["a", "b", "c", "d", "e", "f"],
index=pd.MultiIndex.from_arrays(
[["A", "B", "C"] * 2, [1, 2, 3, 4, 5, 6]], names=["Let", "Num"]
),
)
ser
pd.merge(df, ser.reset_index(), on=["Let", "Num"])
Performing an outer join with duplicate join keys in :class:`DataFrame`
.. ipython:: python
left = pd.DataFrame({"A": [1, 2], "B": [2, 2]})
right = pd.DataFrame({"A": [4, 5, 6], "B": [2, 2, 2]})
result = pd.merge(left, right, on="B", how="outer")
result
.. ipython:: python
:suppress:
@savefig merging_merge_on_key_dup.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. warning::
Merging on duplicate keys significantly increase the dimensions of the result
and can cause a memory overflow.
.. _merging.validation:
Merge key uniqueness
~~~~~~~~~~~~~~~~~~~~
The ``validate`` argument checks whether the uniqueness of merge keys.
Key uniqueness is checked before merge operations and can protect against memory overflows
and unexpected key duplication.
.. ipython:: python
:okexcept:
left = pd.DataFrame({"A": [1, 2], "B": [1, 2]})
right = pd.DataFrame({"A": [4, 5, 6], "B": [2, 2, 2]})
result = pd.merge(left, right, on="B", how="outer", validate="one_to_one")
If the user is aware of the duplicates in the right :class:`DataFrame` but wants to
ensure there are no duplicates in the left :class:`DataFrame`, one can use the
``validate='one_to_many'`` argument instead, which will not raise an exception.
.. ipython:: python
pd.merge(left, right, on="B", how="outer", validate="one_to_many")
.. _merging.indicator:
Merge result indicator
~~~~~~~~~~~~~~~~~~~~~~
:func:`~pandas.merge` accepts the argument ``indicator``. If ``True``, a
Categorical-type column called ``_merge`` will be added to the output object
that takes on values:
=================================== ================
Observation Origin ``_merge`` value
=================================== ================
Merge key only in ``'left'`` frame ``left_only``
Merge key only in ``'right'`` frame ``right_only``
Merge key in both frames ``both``
=================================== ================
.. ipython:: python
df1 = pd.DataFrame({"col1": [0, 1], "col_left": ["a", "b"]})
df2 = pd.DataFrame({"col1": [1, 2, 2], "col_right": [2, 2, 2]})
pd.merge(df1, df2, on="col1", how="outer", indicator=True)
A string argument to ``indicator`` will use the value as the name for the indicator column.
.. ipython:: python
pd.merge(df1, df2, on="col1", how="outer", indicator="indicator_column")
Overlapping value columns
~~~~~~~~~~~~~~~~~~~~~~~~~
The merge ``suffixes`` argument takes a tuple of list of strings to append to
overlapping column names in the input :class:`DataFrame` to disambiguate the result
columns:
.. ipython:: python
left = pd.DataFrame({"k": ["K0", "K1", "K2"], "v": [1, 2, 3]})
right = pd.DataFrame({"k": ["K0", "K0", "K3"], "v": [4, 5, 6]})
result = pd.merge(left, right, on="k")
result
.. ipython:: python
:suppress:
@savefig merging_merge_overlapped.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. ipython:: python
result = pd.merge(left, right, on="k", suffixes=("_l", "_r"))
result
.. ipython:: python
:suppress:
@savefig merging_merge_overlapped_suffix.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
:meth:`DataFrame.join`
----------------------
:meth:`DataFrame.join` combines the columns of multiple,
potentially differently-indexed :class:`DataFrame` into a single result
:class:`DataFrame`.
.. ipython:: python
left = pd.DataFrame(
{"A": ["A0", "A1", "A2"], "B": ["B0", "B1", "B2"]}, index=["K0", "K1", "K2"]
)
right = pd.DataFrame(
{"C": ["C0", "C2", "C3"], "D": ["D0", "D2", "D3"]}, index=["K0", "K2", "K3"]
)
result = left.join(right)
result
.. ipython:: python
:suppress:
@savefig merging_join.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. ipython:: python
result = left.join(right, how="outer")
result
.. ipython:: python
:suppress:
@savefig merging_join_outer.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. ipython:: python
result = left.join(right, how="inner")
result
.. ipython:: python
:suppress:
@savefig merging_join_inner.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
:meth:`DataFrame.join` takes an optional ``on`` argument which may be a column
or multiple column names that the passed :class:`DataFrame` is to be
aligned.
.. ipython:: python
left = pd.DataFrame(
{
"A": ["A0", "A1", "A2", "A3"],
"B": ["B0", "B1", "B2", "B3"],
"key": ["K0", "K1", "K0", "K1"],
}
)
right = pd.DataFrame({"C": ["C0", "C1"], "D": ["D0", "D1"]}, index=["K0", "K1"])
result = left.join(right, on="key")
result
.. ipython:: python
:suppress:
@savefig merging_join_key_columns.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. ipython:: python
result = pd.merge(
left, right, left_on="key", right_index=True, how="left", sort=False
)
result
.. ipython:: python
:suppress:
@savefig merging_merge_key_columns.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. _merging.multikey_join:
To join on multiple keys, the passed :class:`DataFrame` must have a :class:`MultiIndex`:
.. ipython:: python
left = pd.DataFrame(
{
"A": ["A0", "A1", "A2", "A3"],
"B": ["B0", "B1", "B2", "B3"],
"key1": ["K0", "K0", "K1", "K2"],
"key2": ["K0", "K1", "K0", "K1"],
}
)
index = pd.MultiIndex.from_tuples(
[("K0", "K0"), ("K1", "K0"), ("K2", "K0"), ("K2", "K1")]
)
right = pd.DataFrame(
{"C": ["C0", "C1", "C2", "C3"], "D": ["D0", "D1", "D2", "D3"]}, index=index
)
result = left.join(right, on=["key1", "key2"])
result
.. ipython:: python
:suppress:
@savefig merging_join_multikeys.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. _merging.df_inner_join:
The default for :class:`DataFrame.join` is to perform a left join
which uses only the keys found in the
calling :class:`DataFrame`. Other join types can be specified with ``how``.
.. ipython:: python
result = left.join(right, on=["key1", "key2"], how="inner")
result
.. ipython:: python
:suppress:
@savefig merging_join_multikeys_inner.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. _merging.join_on_mi:
Joining a single Index to a MultiIndex
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You can join a :class:`DataFrame` with a :class:`Index` to a :class:`DataFrame` with a :class:`MultiIndex` on a level.
The ``name`` of the :class:`Index` with match the level name of the :class:`MultiIndex`.
.. ipython:: python
left = pd.DataFrame(
{"A": ["A0", "A1", "A2"], "B": ["B0", "B1", "B2"]},
index=pd.Index(["K0", "K1", "K2"], name="key"),
)
index = pd.MultiIndex.from_tuples(
[("K0", "Y0"), ("K1", "Y1"), ("K2", "Y2"), ("K2", "Y3")],
names=["key", "Y"],
)
right = pd.DataFrame(
{"C": ["C0", "C1", "C2", "C3"], "D": ["D0", "D1", "D2", "D3"]},
index=index,
)
result = left.join(right, how="inner")
result
.. ipython:: python
:suppress:
@savefig merging_join_multiindex_inner.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. _merging.join_with_two_multi_indexes:
Joining with two :class:`MultiIndex`
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The :class:`MultiIndex` of the input argument must be completely used
in the join and is a subset of the indices in the left argument.
.. ipython:: python
leftindex = pd.MultiIndex.from_product(
[list("abc"), list("xy"), [1, 2]], names=["abc", "xy", "num"]
)
left = pd.DataFrame({"v1": range(12)}, index=leftindex)
left
rightindex = pd.MultiIndex.from_product(
[list("abc"), list("xy")], names=["abc", "xy"]
)
right = pd.DataFrame({"v2": [100 * i for i in range(1, 7)]}, index=rightindex)
right
left.join(right, on=["abc", "xy"], how="inner")
.. ipython:: python
leftindex = pd.MultiIndex.from_tuples(
[("K0", "X0"), ("K0", "X1"), ("K1", "X2")], names=["key", "X"]
)
left = pd.DataFrame(
{"A": ["A0", "A1", "A2"], "B": ["B0", "B1", "B2"]}, index=leftindex
)
rightindex = pd.MultiIndex.from_tuples(
[("K0", "Y0"), ("K1", "Y1"), ("K2", "Y2"), ("K2", "Y3")], names=["key", "Y"]
)
right = pd.DataFrame(
{"C": ["C0", "C1", "C2", "C3"], "D": ["D0", "D1", "D2", "D3"]}, index=rightindex
)
result = pd.merge(
left.reset_index(), right.reset_index(), on=["key"], how="inner"
).set_index(["key", "X", "Y"])
result
.. ipython:: python
:suppress:
@savefig merging_merge_two_multiindex.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. _merging.merge_on_columns_and_levels:
Merging on a combination of columns and index levels
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Strings passed as the ``on``, ``left_on``, and ``right_on`` parameters
may refer to either column names or index level names. This enables merging
:class:`DataFrame` instances on a combination of index levels and columns without
resetting indexes.
.. ipython:: python
left_index = pd.Index(["K0", "K0", "K1", "K2"], name="key1")
left = pd.DataFrame(
{
"A": ["A0", "A1", "A2", "A3"],
"B": ["B0", "B1", "B2", "B3"],
"key2": ["K0", "K1", "K0", "K1"],
},
index=left_index,
)
right_index = pd.Index(["K0", "K1", "K2", "K2"], name="key1")
right = pd.DataFrame(
{
"C": ["C0", "C1", "C2", "C3"],
"D": ["D0", "D1", "D2", "D3"],
"key2": ["K0", "K0", "K0", "K1"],
},
index=right_index,
)
result = left.merge(right, on=["key1", "key2"])
result
.. ipython:: python
:suppress:
@savefig merge_on_index_and_column.png
p.plot([left, right], result, labels=["left", "right"], vertical=False);
plt.close("all");
.. note::
When :class:`DataFrame` are joined on a string that matches an index level in both
arguments, the index level is preserved as an index level in the resulting
:class:`DataFrame`.
.. note::
When :class:`DataFrame` are joined using only some of the levels of a :class:`MultiIndex`,
the extra levels will be dropped from the resulting join. To
preserve those levels, use :meth:`DataFrame.reset_index` on those level
names to move those levels to columns prior to the join.
.. _merging.multiple_join:
Joining multiple :class:`DataFrame`
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A list or tuple of ``:class:`DataFrame``` can also be passed to :meth:`~DataFrame.join`
to join them together on their indexes.
.. ipython:: python
right2 = pd.DataFrame({"v": [7, 8, 9]}, index=["K1", "K1", "K2"])
result = left.join([right, right2])
.. ipython:: python
:suppress:
@savefig merging_join_multi_df.png
p.plot(
[left, right, right2],
result,
labels=["left", "right", "right2"],
vertical=False,
);
plt.close("all");
.. _merging.combine_first.update:
:meth:`DataFrame.combine_first`
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:meth:`DataFrame.combine_first` update missing values from one :class:`DataFrame`
with the non-missing values in another :class:`DataFrame` in the corresponding
location.
.. ipython:: python
df1 = pd.DataFrame(
[[np.nan, 3.0, 5.0], [-4.6, np.nan, np.nan], [np.nan, 7.0, np.nan]]
)
df2 = pd.DataFrame([[-42.6, np.nan, -8.2], [-5.0, 1.6, 4]], index=[1, 2])
result = df1.combine_first(df2)
result
.. ipython:: python
:suppress:
@savefig merging_combine_first.png
p.plot([df1, df2], result, labels=["df1", "df2"], vertical=False);
plt.close("all");
.. _merging.merge_ordered:
:func:`merge_ordered`
---------------------
:func:`merge_ordered` combines order data such as numeric or time series data
with optional filling of missing data with ``fill_method``.
.. ipython:: python
left = pd.DataFrame(
{"k": ["K0", "K1", "K1", "K2"], "lv": [1, 2, 3, 4], "s": ["a", "b", "c", "d"]}
)
right = pd.DataFrame({"k": ["K1", "K2", "K4"], "rv": [1, 2, 3]})
pd.merge_ordered(left, right, fill_method="ffill", left_by="s")
.. _merging.merge_asof:
:func:`merge_asof`
---------------------
:func:`merge_asof` is similar to an ordered left-join except that mactches are on the
nearest key rather than equal keys. For each row in the ``left`` :class:`DataFrame`,
the last row in the ``right`` :class:`DataFrame` are selected where the ``on`` key is less
than the left's key. Both :class:`DataFrame` must be sorted by the key.
Optionally an :func:`merge_asof` can perform a group-wise merge by matching the
``by`` key in addition to the nearest match on the ``on`` key.
.. ipython:: python
trades = pd.DataFrame(
{
"time": pd.to_datetime(
[
"20160525 13:30:00.023",
"20160525 13:30:00.038",
"20160525 13:30:00.048",
"20160525 13:30:00.048",
"20160525 13:30:00.048",
]
),
"ticker": ["MSFT", "MSFT", "GOOG", "GOOG", "AAPL"],
"price": [51.95, 51.95, 720.77, 720.92, 98.00],
"quantity": [75, 155, 100, 100, 100],
},
columns=["time", "ticker", "price", "quantity"],
)
quotes = pd.DataFrame(
{
"time": pd.to_datetime(
[
"20160525 13:30:00.023",
"20160525 13:30:00.023",
"20160525 13:30:00.030",
"20160525 13:30:00.041",
"20160525 13:30:00.048",
"20160525 13:30:00.049",
"20160525 13:30:00.072",
"20160525 13:30:00.075",
]
),
"ticker": ["GOOG", "MSFT", "MSFT", "MSFT", "GOOG", "AAPL", "GOOG", "MSFT"],
"bid": [720.50, 51.95, 51.97, 51.99, 720.50, 97.99, 720.50, 52.01],
"ask": [720.93, 51.96, 51.98, 52.00, 720.93, 98.01, 720.88, 52.03],
},
columns=["time", "ticker", "bid", "ask"],
)
trades
quotes
pd.merge_asof(trades, quotes, on="time", by="ticker")
:func:`merge_asof` within ``2ms`` between the quote time and the trade time.
.. ipython:: python
pd.merge_asof(trades, quotes, on="time", by="ticker", tolerance=pd.Timedelta("2ms"))
:func:`merge_asof` within ``10ms`` between the quote time and the trade time and
exclude exact matches on time. Note that though we exclude the exact matches
(of the quotes), prior quotes **do** propagate to that point in time.
.. ipython:: python
pd.merge_asof(
trades,
quotes,
on="time",
by="ticker",
tolerance=pd.Timedelta("10ms"),
allow_exact_matches=False,
)
.. _merging.compare:
:meth:`~Series.compare`
-----------------------
The :meth:`Series.compare` and :meth:`DataFrame.compare` methods allow you to
compare two :class:`DataFrame` or :class:`Series`, respectively, and summarize their differences.
.. ipython:: python
df = pd.DataFrame(
{
"col1": ["a", "a", "b", "b", "a"],
"col2": [1.0, 2.0, 3.0, np.nan, 5.0],
"col3": [1.0, 2.0, 3.0, 4.0, 5.0],
},
columns=["col1", "col2", "col3"],
)
df
df2 = df.copy()
df2.loc[0, "col1"] = "c"
df2.loc[2, "col3"] = 4.0
df2
df.compare(df2)
By default, if two corresponding values are equal, they will be shown as ``NaN``.
Furthermore, if all values in an entire row / column, the row / column will be
omitted from the result. The remaining differences will be aligned on columns.
Stack the differences on rows.
.. ipython:: python
df.compare(df2, align_axis=0)
Keep all original rows and columns with ``keep_shape=True``
.. ipython:: python
df.compare(df2, keep_shape=True)
Keep all the original values even if they are equal.
.. ipython:: python
df.compare(df2, keep_shape=True, keep_equal=True)