merging.rst

.. _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 can 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)