import numpy as np
from numpy.random import randn
import pytest

import pandas as pd
from pandas import DataFrame, Index, MultiIndex, Series
import pandas._testing as tm
from pandas.core.reshape.concat import concat
from pandas.core.reshape.merge import merge


@pytest.fixture
def left():
    """left dataframe (not multi-indexed) for multi-index join tests"""
    # a little relevant example with NAs
    key1 = ["bar", "bar", "bar", "foo", "foo", "baz", "baz", "qux", "qux", "snap"]
    key2 = ["two", "one", "three", "one", "two", "one", "two", "two", "three", "one"]

    data = np.random.randn(len(key1))
    return DataFrame({"key1": key1, "key2": key2, "data": data})


@pytest.fixture
def right():
    """right dataframe (multi-indexed) for multi-index join tests"""
    index = MultiIndex(
        levels=[["foo", "bar", "baz", "qux"], ["one", "two", "three"]],
        codes=[[0, 0, 0, 1, 1, 2, 2, 3, 3, 3], [0, 1, 2, 0, 1, 1, 2, 0, 1, 2]],
        names=["key1", "key2"],
    )

    return DataFrame(
        np.random.randn(10, 3), index=index, columns=["j_one", "j_two", "j_three"]
    )


@pytest.fixture
def left_multi():
    return DataFrame(
        dict(
            Origin=["A", "A", "B", "B", "C"],
            Destination=["A", "B", "A", "C", "A"],
            Period=["AM", "AM", "IP", "AM", "OP"],
            TripPurp=["hbw", "nhb", "hbo", "nhb", "hbw"],
            Trips=[1987, 3647, 2470, 4296, 4444],
        ),
        columns=["Origin", "Destination", "Period", "TripPurp", "Trips"],
    ).set_index(["Origin", "Destination", "Period", "TripPurp"])


@pytest.fixture
def right_multi():
    return DataFrame(
        dict(
            Origin=["A", "A", "B", "B", "C", "C", "E"],
            Destination=["A", "B", "A", "B", "A", "B", "F"],
            Period=["AM", "AM", "IP", "AM", "OP", "IP", "AM"],
            LinkType=["a", "b", "c", "b", "a", "b", "a"],
            Distance=[100, 80, 90, 80, 75, 35, 55],
        ),
        columns=["Origin", "Destination", "Period", "LinkType", "Distance"],
    ).set_index(["Origin", "Destination", "Period", "LinkType"])


@pytest.fixture
def on_cols_multi():
    return ["Origin", "Destination", "Period"]


@pytest.fixture
def idx_cols_multi():
    return ["Origin", "Destination", "Period", "TripPurp", "LinkType"]


class TestMergeMulti:
    def setup_method(self):
        self.index = MultiIndex(
            levels=[["foo", "bar", "baz", "qux"], ["one", "two", "three"]],
            codes=[[0, 0, 0, 1, 1, 2, 2, 3, 3, 3], [0, 1, 2, 0, 1, 1, 2, 0, 1, 2]],
            names=["first", "second"],
        )
        self.to_join = DataFrame(
            np.random.randn(10, 3),
            index=self.index,
            columns=["j_one", "j_two", "j_three"],
        )

        # a little relevant example with NAs
        key1 = ["bar", "bar", "bar", "foo", "foo", "baz", "baz", "qux", "qux", "snap"]
        key2 = [
            "two",
            "one",
            "three",
            "one",
            "two",
            "one",
            "two",
            "two",
            "three",
            "one",
        ]

        data = np.random.randn(len(key1))
        self.data = DataFrame({"key1": key1, "key2": key2, "data": data})

    def test_merge_on_multikey(self, left, right, join_type):
        on_cols = ["key1", "key2"]
        result = left.join(right, on=on_cols, how=join_type).reset_index(drop=True)

        expected = pd.merge(left, right.reset_index(), on=on_cols, how=join_type)

        tm.assert_frame_equal(result, expected)

        result = left.join(right, on=on_cols, how=join_type, sort=True).reset_index(
            drop=True
        )

        expected = pd.merge(
            left, right.reset_index(), on=on_cols, how=join_type, sort=True
        )

        tm.assert_frame_equal(result, expected)

    @pytest.mark.parametrize("sort", [False, True])
    def test_left_join_multi_index(self, left, right, sort):
        icols = ["1st", "2nd", "3rd"]

        def bind_cols(df):
            iord = lambda a: 0 if a != a else ord(a)
            f = lambda ts: ts.map(iord) - ord("a")
            return f(df["1st"]) + f(df["3rd"]) * 1e2 + df["2nd"].fillna(0) * 1e4

        def run_asserts(left, right, sort):
            res = left.join(right, on=icols, how="left", sort=sort)

            assert len(left) < len(res) + 1
            assert not res["4th"].isna().any()
            assert not res["5th"].isna().any()

            tm.assert_series_equal(res["4th"], -res["5th"], check_names=False)
            result = bind_cols(res.iloc[:, :-2])
            tm.assert_series_equal(res["4th"], result, check_names=False)
            assert result.name is None

            if sort:
                tm.assert_frame_equal(res, res.sort_values(icols, kind="mergesort"))

            out = merge(left, right.reset_index(), on=icols, sort=sort, how="left")

            res.index = np.arange(len(res))
            tm.assert_frame_equal(out, res)

        lc = list(map(chr, np.arange(ord("a"), ord("z") + 1)))
        left = DataFrame(np.random.choice(lc, (5000, 2)), columns=["1st", "3rd"])
        left.insert(1, "2nd", np.random.randint(0, 1000, len(left)))

        i = np.random.permutation(len(left))
        right = left.iloc[i].copy()

        left["4th"] = bind_cols(left)
        right["5th"] = -bind_cols(right)
        right.set_index(icols, inplace=True)

        run_asserts(left, right, sort)

        # inject some nulls
        left.loc[1::23, "1st"] = np.nan
        left.loc[2::37, "2nd"] = np.nan
        left.loc[3::43, "3rd"] = np.nan
        left["4th"] = bind_cols(left)

        i = np.random.permutation(len(left))
        right = left.iloc[i, :-1]
        right["5th"] = -bind_cols(right)
        right.set_index(icols, inplace=True)

        run_asserts(left, right, sort)

    @pytest.mark.parametrize("sort", [False, True])
    def test_merge_right_vs_left(self, left, right, sort):
        # compare left vs right merge with multikey
        on_cols = ["key1", "key2"]
        merged_left_right = left.merge(
            right, left_on=on_cols, right_index=True, how="left", sort=sort
        )

        merge_right_left = right.merge(
            left, right_on=on_cols, left_index=True, how="right", sort=sort
        )

        # Reorder columns
        merge_right_left = merge_right_left[merged_left_right.columns]

        tm.assert_frame_equal(merged_left_right, merge_right_left)

    def test_merge_multiple_cols_with_mixed_cols_index(self):
        # GH29522
        s = pd.Series(
            range(6),
            pd.MultiIndex.from_product([["A", "B"], [1, 2, 3]], names=["lev1", "lev2"]),
            name="Amount",
        )
        df = pd.DataFrame(
            {"lev1": list("AAABBB"), "lev2": [1, 2, 3, 1, 2, 3], "col": 0}
        )
        result = pd.merge(df, s.reset_index(), on=["lev1", "lev2"])
        expected = pd.DataFrame(
            {
                "lev1": list("AAABBB"),
                "lev2": [1, 2, 3, 1, 2, 3],
                "col": [0] * 6,
                "Amount": range(6),
            }
        )
        tm.assert_frame_equal(result, expected)

    def test_compress_group_combinations(self):

        # ~ 40000000 possible unique groups
        key1 = tm.rands_array(10, 10000)
        key1 = np.tile(key1, 2)
        key2 = key1[::-1]

        df = DataFrame({"key1": key1, "key2": key2, "value1": np.random.randn(20000)})

        df2 = DataFrame(
            {"key1": key1[::2], "key2": key2[::2], "value2": np.random.randn(10000)}
        )

        # just to hit the label compression code path
        merge(df, df2, how="outer")

    def test_left_join_index_preserve_order(self):

        on_cols = ["k1", "k2"]
        left = DataFrame(
            {
                "k1": [0, 1, 2] * 8,
                "k2": ["foo", "bar"] * 12,
                "v": np.array(np.arange(24), dtype=np.int64),
            }
        )

        index = MultiIndex.from_tuples([(2, "bar"), (1, "foo")])
        right = DataFrame({"v2": [5, 7]}, index=index)

        result = left.join(right, on=on_cols)

        expected = left.copy()
        expected["v2"] = np.nan
        expected.loc[(expected.k1 == 2) & (expected.k2 == "bar"), "v2"] = 5
        expected.loc[(expected.k1 == 1) & (expected.k2 == "foo"), "v2"] = 7

        tm.assert_frame_equal(result, expected)

        result.sort_values(on_cols, kind="mergesort", inplace=True)
        expected = left.join(right, on=on_cols, sort=True)

        tm.assert_frame_equal(result, expected)

        # test join with multi dtypes blocks
        left = DataFrame(
            {
                "k1": [0, 1, 2] * 8,
                "k2": ["foo", "bar"] * 12,
                "k3": np.array([0, 1, 2] * 8, dtype=np.float32),
                "v": np.array(np.arange(24), dtype=np.int32),
            }
        )

        index = MultiIndex.from_tuples([(2, "bar"), (1, "foo")])
        right = DataFrame({"v2": [5, 7]}, index=index)

        result = left.join(right, on=on_cols)

        expected = left.copy()
        expected["v2"] = np.nan
        expected.loc[(expected.k1 == 2) & (expected.k2 == "bar"), "v2"] = 5
        expected.loc[(expected.k1 == 1) & (expected.k2 == "foo"), "v2"] = 7

        tm.assert_frame_equal(result, expected)

        result = result.sort_values(on_cols, kind="mergesort")
        expected = left.join(right, on=on_cols, sort=True)

        tm.assert_frame_equal(result, expected)

    def test_left_join_index_multi_match_multiindex(self):
        left = DataFrame(
            [
                ["X", "Y", "C", "a"],
                ["W", "Y", "C", "e"],
                ["V", "Q", "A", "h"],
                ["V", "R", "D", "i"],
                ["X", "Y", "D", "b"],
                ["X", "Y", "A", "c"],
                ["W", "Q", "B", "f"],
                ["W", "R", "C", "g"],
                ["V", "Y", "C", "j"],
                ["X", "Y", "B", "d"],
            ],
            columns=["cola", "colb", "colc", "tag"],
            index=[3, 2, 0, 1, 7, 6, 4, 5, 9, 8],
        )

        right = DataFrame(
            [
                ["W", "R", "C", 0],
                ["W", "Q", "B", 3],
                ["W", "Q", "B", 8],
                ["X", "Y", "A", 1],
                ["X", "Y", "A", 4],
                ["X", "Y", "B", 5],
                ["X", "Y", "C", 6],
                ["X", "Y", "C", 9],
                ["X", "Q", "C", -6],
                ["X", "R", "C", -9],
                ["V", "Y", "C", 7],
                ["V", "R", "D", 2],
                ["V", "R", "D", -1],
                ["V", "Q", "A", -3],
            ],
            columns=["col1", "col2", "col3", "val"],
        ).set_index(["col1", "col2", "col3"])

        result = left.join(right, on=["cola", "colb", "colc"], how="left")

        expected = DataFrame(
            [
                ["X", "Y", "C", "a", 6],
                ["X", "Y", "C", "a", 9],
                ["W", "Y", "C", "e", np.nan],
                ["V", "Q", "A", "h", -3],
                ["V", "R", "D", "i", 2],
                ["V", "R", "D", "i", -1],
                ["X", "Y", "D", "b", np.nan],
                ["X", "Y", "A", "c", 1],
                ["X", "Y", "A", "c", 4],
                ["W", "Q", "B", "f", 3],
                ["W", "Q", "B", "f", 8],
                ["W", "R", "C", "g", 0],
                ["V", "Y", "C", "j", 7],
                ["X", "Y", "B", "d", 5],
            ],
            columns=["cola", "colb", "colc", "tag", "val"],
            index=[3, 3, 2, 0, 1, 1, 7, 6, 6, 4, 4, 5, 9, 8],
        )

        tm.assert_frame_equal(result, expected)

        result = left.join(right, on=["cola", "colb", "colc"], how="left", sort=True)

        expected = expected.sort_values(["cola", "colb", "colc"], kind="mergesort")

        tm.assert_frame_equal(result, expected)

    def test_left_join_index_multi_match(self):
        left = DataFrame(
            [["c", 0], ["b", 1], ["a", 2], ["b", 3]],
            columns=["tag", "val"],
            index=[2, 0, 1, 3],
        )

        right = DataFrame(
            [
                ["a", "v"],
                ["c", "w"],
                ["c", "x"],
                ["d", "y"],
                ["a", "z"],
                ["c", "r"],
                ["e", "q"],
                ["c", "s"],
            ],
            columns=["tag", "char"],
        ).set_index("tag")

        result = left.join(right, on="tag", how="left")

        expected = DataFrame(
            [
                ["c", 0, "w"],
                ["c", 0, "x"],
                ["c", 0, "r"],
                ["c", 0, "s"],
                ["b", 1, np.nan],
                ["a", 2, "v"],
                ["a", 2, "z"],
                ["b", 3, np.nan],
            ],
            columns=["tag", "val", "char"],
            index=[2, 2, 2, 2, 0, 1, 1, 3],
        )

        tm.assert_frame_equal(result, expected)

        result = left.join(right, on="tag", how="left", sort=True)
        expected2 = expected.sort_values("tag", kind="mergesort")

        tm.assert_frame_equal(result, expected2)

        # GH7331 - maintain left frame order in left merge
        result = merge(left, right.reset_index(), how="left", on="tag")
        expected.index = np.arange(len(expected))
        tm.assert_frame_equal(result, expected)

    def test_left_merge_na_buglet(self):
        left = DataFrame(
            {
                "id": list("abcde"),
                "v1": randn(5),
                "v2": randn(5),
                "dummy": list("abcde"),
                "v3": randn(5),
            },
            columns=["id", "v1", "v2", "dummy", "v3"],
        )
        right = DataFrame(
            {
                "id": ["a", "b", np.nan, np.nan, np.nan],
                "sv3": [1.234, 5.678, np.nan, np.nan, np.nan],
            }
        )

        result = merge(left, right, on="id", how="left")

        rdf = right.drop(["id"], axis=1)
        expected = left.join(rdf)
        tm.assert_frame_equal(result, expected)

    def test_merge_na_keys(self):
        data = [
            [1950, "A", 1.5],
            [1950, "B", 1.5],
            [1955, "B", 1.5],
            [1960, "B", np.nan],
            [1970, "B", 4.0],
            [1950, "C", 4.0],
            [1960, "C", np.nan],
            [1965, "C", 3.0],
            [1970, "C", 4.0],
        ]

        frame = DataFrame(data, columns=["year", "panel", "data"])

        other_data = [
            [1960, "A", np.nan],
            [1970, "A", np.nan],
            [1955, "A", np.nan],
            [1965, "A", np.nan],
            [1965, "B", np.nan],
            [1955, "C", np.nan],
        ]
        other = DataFrame(other_data, columns=["year", "panel", "data"])

        result = frame.merge(other, how="outer")

        expected = frame.fillna(-999).merge(other.fillna(-999), how="outer")
        expected = expected.replace(-999, np.nan)

        tm.assert_frame_equal(result, expected)

    @pytest.mark.parametrize("klass", [None, np.asarray, Series, Index])
    def test_merge_datetime_index(self, klass):
        # see gh-19038
        df = DataFrame(
            [1, 2, 3], ["2016-01-01", "2017-01-01", "2018-01-01"], columns=["a"]
        )
        df.index = pd.to_datetime(df.index)
        on_vector = df.index.year

        if klass is not None:
            on_vector = klass(on_vector)

        expected = DataFrame({"a": [1, 2, 3], "key_1": [2016, 2017, 2018]})

        result = df.merge(df, on=["a", on_vector], how="inner")
        tm.assert_frame_equal(result, expected)

        expected = DataFrame(
            {"key_0": [2016, 2017, 2018], "a_x": [1, 2, 3], "a_y": [1, 2, 3]}
        )

        result = df.merge(df, on=[df.index.year], how="inner")
        tm.assert_frame_equal(result, expected)

    def test_join_multi_levels(self):

        # GH 3662
        # merge multi-levels
        household = DataFrame(
            dict(
                household_id=[1, 2, 3],
                male=[0, 1, 0],
                wealth=[196087.3, 316478.7, 294750],
            ),
            columns=["household_id", "male", "wealth"],
        ).set_index("household_id")
        portfolio = DataFrame(
            dict(
                household_id=[1, 2, 2, 3, 3, 3, 4],
                asset_id=[
                    "nl0000301109",
                    "nl0000289783",
                    "gb00b03mlx29",
                    "gb00b03mlx29",
                    "lu0197800237",
                    "nl0000289965",
                    np.nan,
                ],
                name=[
                    "ABN Amro",
                    "Robeco",
                    "Royal Dutch Shell",
                    "Royal Dutch Shell",
                    "AAB Eastern Europe Equity Fund",
                    "Postbank BioTech Fonds",
                    np.nan,
                ],
                share=[1.0, 0.4, 0.6, 0.15, 0.6, 0.25, 1.0],
            ),
            columns=["household_id", "asset_id", "name", "share"],
        ).set_index(["household_id", "asset_id"])
        result = household.join(portfolio, how="inner")
        expected = (
            DataFrame(
                dict(
                    male=[0, 1, 1, 0, 0, 0],
                    wealth=[196087.3, 316478.7, 316478.7, 294750.0, 294750.0, 294750.0],
                    name=[
                        "ABN Amro",
                        "Robeco",
                        "Royal Dutch Shell",
                        "Royal Dutch Shell",
                        "AAB Eastern Europe Equity Fund",
                        "Postbank BioTech Fonds",
                    ],
                    share=[1.00, 0.40, 0.60, 0.15, 0.60, 0.25],
                    household_id=[1, 2, 2, 3, 3, 3],
                    asset_id=[
                        "nl0000301109",
                        "nl0000289783",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "lu0197800237",
                        "nl0000289965",
                    ],
                )
            )
            .set_index(["household_id", "asset_id"])
            .reindex(columns=["male", "wealth", "name", "share"])
        )
        tm.assert_frame_equal(result, expected)

        # equivalency
        result = merge(
            household.reset_index(),
            portfolio.reset_index(),
            on=["household_id"],
            how="inner",
        ).set_index(["household_id", "asset_id"])
        tm.assert_frame_equal(result, expected)

        result = household.join(portfolio, how="outer")
        expected = concat(
            [
                expected,
                (
                    DataFrame(
                        dict(share=[1.00]),
                        index=MultiIndex.from_tuples(
                            [(4, np.nan)], names=["household_id", "asset_id"]
                        ),
                    )
                ),
            ],
            axis=0,
            sort=True,
        ).reindex(columns=expected.columns)
        tm.assert_frame_equal(result, expected)

        # invalid cases
        household.index.name = "foo"

        with pytest.raises(
            ValueError, match="cannot join with no overlapping index names"
        ):
            household.join(portfolio, how="inner")

        portfolio2 = portfolio.copy()
        portfolio2.index.set_names(["household_id", "foo"])

        with pytest.raises(ValueError, match="columns overlap but no suffix specified"):
            portfolio2.join(portfolio, how="inner")

    def test_join_multi_levels2(self):

        # some more advanced merges
        # GH6360
        household = DataFrame(
            dict(
                household_id=[1, 2, 2, 3, 3, 3, 4],
                asset_id=[
                    "nl0000301109",
                    "nl0000301109",
                    "gb00b03mlx29",
                    "gb00b03mlx29",
                    "lu0197800237",
                    "nl0000289965",
                    np.nan,
                ],
                share=[1.0, 0.4, 0.6, 0.15, 0.6, 0.25, 1.0],
            ),
            columns=["household_id", "asset_id", "share"],
        ).set_index(["household_id", "asset_id"])

        log_return = DataFrame(
            dict(
                asset_id=[
                    "gb00b03mlx29",
                    "gb00b03mlx29",
                    "gb00b03mlx29",
                    "lu0197800237",
                    "lu0197800237",
                ],
                t=[233, 234, 235, 180, 181],
                log_return=[0.09604978, -0.06524096, 0.03532373, 0.03025441, 0.036997],
            )
        ).set_index(["asset_id", "t"])

        expected = (
            DataFrame(
                dict(
                    household_id=[2, 2, 2, 3, 3, 3, 3, 3],
                    asset_id=[
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "lu0197800237",
                        "lu0197800237",
                    ],
                    t=[233, 234, 235, 233, 234, 235, 180, 181],
                    share=[0.6, 0.6, 0.6, 0.15, 0.15, 0.15, 0.6, 0.6],
                    log_return=[
                        0.09604978,
                        -0.06524096,
                        0.03532373,
                        0.09604978,
                        -0.06524096,
                        0.03532373,
                        0.03025441,
                        0.036997,
                    ],
                )
            )
            .set_index(["household_id", "asset_id", "t"])
            .reindex(columns=["share", "log_return"])
        )

        # this is the equivalency
        result = merge(
            household.reset_index(),
            log_return.reset_index(),
            on=["asset_id"],
            how="inner",
        ).set_index(["household_id", "asset_id", "t"])
        tm.assert_frame_equal(result, expected)

        expected = (
            DataFrame(
                dict(
                    household_id=[1, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 4],
                    asset_id=[
                        "nl0000301109",
                        "nl0000301109",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "gb00b03mlx29",
                        "lu0197800237",
                        "lu0197800237",
                        "nl0000289965",
                        None,
                    ],
                    t=[None, None, 233, 234, 235, 233, 234, 235, 180, 181, None, None],
                    share=[
                        1.0,
                        0.4,
                        0.6,
                        0.6,
                        0.6,
                        0.15,
                        0.15,
                        0.15,
                        0.6,
                        0.6,
                        0.25,
                        1.0,
                    ],
                    log_return=[
                        None,
                        None,
                        0.09604978,
                        -0.06524096,
                        0.03532373,
                        0.09604978,
                        -0.06524096,
                        0.03532373,
                        0.03025441,
                        0.036997,
                        None,
                        None,
                    ],
                )
            )
            .set_index(["household_id", "asset_id", "t"])
            .reindex(columns=["share", "log_return"])
        )

        result = merge(
            household.reset_index(),
            log_return.reset_index(),
            on=["asset_id"],
            how="outer",
        ).set_index(["household_id", "asset_id", "t"])

        tm.assert_frame_equal(result, expected)


class TestJoinMultiMulti:
    def test_join_multi_multi(
        self, left_multi, right_multi, join_type, on_cols_multi, idx_cols_multi
    ):
        # Multi-index join tests
        expected = (
            pd.merge(
                left_multi.reset_index(),
                right_multi.reset_index(),
                how=join_type,
                on=on_cols_multi,
            )
            .set_index(idx_cols_multi)
            .sort_index()
        )

        result = left_multi.join(right_multi, how=join_type).sort_index()
        tm.assert_frame_equal(result, expected)

    def test_join_multi_empty_frames(
        self, left_multi, right_multi, join_type, on_cols_multi, idx_cols_multi
    ):

        left_multi = left_multi.drop(columns=left_multi.columns)
        right_multi = right_multi.drop(columns=right_multi.columns)

        expected = (
            pd.merge(
                left_multi.reset_index(),
                right_multi.reset_index(),
                how=join_type,
                on=on_cols_multi,
            )
            .set_index(idx_cols_multi)
            .sort_index()
        )

        result = left_multi.join(right_multi, how=join_type).sort_index()
        tm.assert_frame_equal(result, expected)

    @pytest.mark.parametrize("box", [None, np.asarray, Series, Index])
    def test_merge_datetime_index(self, box):
        # see gh-19038
        df = DataFrame(
            [1, 2, 3], ["2016-01-01", "2017-01-01", "2018-01-01"], columns=["a"]
        )
        df.index = pd.to_datetime(df.index)
        on_vector = df.index.year

        if box is not None:
            on_vector = box(on_vector)

        expected = DataFrame({"a": [1, 2, 3], "key_1": [2016, 2017, 2018]})

        result = df.merge(df, on=["a", on_vector], how="inner")
        tm.assert_frame_equal(result, expected)

        expected = DataFrame(
            {"key_0": [2016, 2017, 2018], "a_x": [1, 2, 3], "a_y": [1, 2, 3]}
        )

        result = df.merge(df, on=[df.index.year], how="inner")
        tm.assert_frame_equal(result, expected)

    def test_single_common_level(self):
        index_left = 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=index_left
        )

        index_right = 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_right,
        )

        result = left.join(right)
        expected = pd.merge(
            left.reset_index(), right.reset_index(), on=["key"], how="inner"
        ).set_index(["key", "X", "Y"])

        tm.assert_frame_equal(result, expected)

    def test_join_multi_wrong_order(self):
        # GH 25760
        # GH 28956

        midx1 = pd.MultiIndex.from_product([[1, 2], [3, 4]], names=["a", "b"])
        midx3 = pd.MultiIndex.from_tuples([(4, 1), (3, 2), (3, 1)], names=["b", "a"])

        left = pd.DataFrame(index=midx1, data={"x": [10, 20, 30, 40]})
        right = pd.DataFrame(index=midx3, data={"y": ["foo", "bar", "fing"]})

        result = left.join(right)

        expected = pd.DataFrame(
            index=midx1,
            data={"x": [10, 20, 30, 40], "y": ["fing", "foo", "bar", np.nan]},
        )

        tm.assert_frame_equal(result, expected)

[((20, 11, 20, 64), 'pandas.DataFrame', 'DataFrame', ({(20, 21, 20, 63): "{'key1': key1, 'key2': key2, 'data': data}"}, {}), "({'key1': key1, 'key2': key2, 'data': data})", False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((26, 12, 30, 5), 'pandas.MultiIndex', 'MultiIndex', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((124, 5, 124, 51), 'pytest.mark.parametrize', 'pytest.mark.parametrize', ({(124, 29, 124, 35): '"""sort"""', (124, 37, 124, 50): '[False, True]'}, {}), "('sort', [False, True])", False, 'import pytest\n'), ((179, 5, 179, 51), 'pytest.mark.parametrize', 'pytest.mark.parametrize', ({(179, 29, 179, 35): '"""sort"""', (179, 37, 179, 50): '[False, True]'}, {}), "('sort', [False, True])", False, 'import pytest\n'), ((463, 5, 463, 72), 'pytest.mark.parametrize', 'pytest.mark.parametrize', ({(463, 29, 463, 36): '"""klass"""', (463, 38, 463, 71): '[None, np.asarray, Series, Index]'}, {}), "('klass', [None, np.asarray, Series, Index])", False, 'import pytest\n'), ((775, 5, 775, 70), 'pytest.mark.parametrize', 'pytest.mark.parametrize', ({(775, 29, 775, 34): '"""box"""', (775, 36, 775, 69): '[None, np.asarray, Series, Index]'}, {}), "('box', [None, np.asarray, Series, Index])", False, 'import pytest\n'), ((33, 8, 33, 30), 'numpy.random.randn', 'np.random.randn', ({(33, 24, 33, 26): '(10)', (33, 28, 33, 29): '(3)'}, {}), '(10, 3)', True, 'import numpy as np\n'), ((77, 21, 81, 9), 'pandas.MultiIndex', 'MultiIndex', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((104, 20, 104, 73), 'pandas.DataFrame', 'DataFrame', ({(104, 30, 104, 72): "{'key1': key1, 'key2': key2, 'data': data}"}, {}), "({'key1': key1, 'key2': key2, 'data': data})", False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((112, 8, 112, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(112, 30, 112, 36): 'result', (112, 38, 112, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((122, 8, 122, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(122, 30, 122, 36): 'result', (122, 38, 122, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((194, 8, 194, 66), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(194, 30, 194, 47): 'merged_left_right', (194, 49, 194, 65): 'merge_right_left'}, {}), '(merged_left_right, merge_right_left)', True, 'import pandas._testing as tm\n'), ((215, 8, 215, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(215, 30, 215, 36): 'result', (215, 38, 215, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((220, 15, 220, 40), 'pandas._testing.rands_array', 'tm.rands_array', ({(220, 30, 220, 32): '10', (220, 34, 220, 39): '10000'}, {}), '(10, 10000)', True, 'import pandas._testing as tm\n'), ((221, 15, 221, 31), 'numpy.tile', 'np.tile', ({(221, 23, 221, 27): 'key1', (221, 29, 221, 30): '2'}, {}), '(key1, 2)', True, 'import numpy as np\n'), ((231, 8, 231, 35), 'pandas.core.reshape.merge.merge', 'merge', (), '', False, 'from pandas.core.reshape.merge import merge\n'), ((244, 16, 244, 64), 'pandas.MultiIndex.from_tuples', 'MultiIndex.from_tuples', ({(244, 39, 244, 63): "[(2, 'bar'), (1, 'foo')]"}, {}), "([(2, 'bar'), (1, 'foo')])", False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((245, 16, 245, 54), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((254, 8, 254, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(254, 30, 254, 36): 'result', (254, 38, 254, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((259, 8, 259, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(259, 30, 259, 36): 'result', (259, 38, 259, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((271, 16, 271, 64), 'pandas.MultiIndex.from_tuples', 'MultiIndex.from_tuples', ({(271, 39, 271, 63): "[(2, 'bar'), (1, 'foo')]"}, {}), "([(2, 'bar'), (1, 'foo')])", False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((272, 16, 272, 54), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((281, 8, 281, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(281, 30, 281, 36): 'result', (281, 38, 281, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((286, 8, 286, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(286, 30, 286, 36): 'result', (286, 38, 286, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((289, 15, 304, 9), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((328, 19, 347, 9), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((349, 8, 349, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(349, 30, 349, 36): 'result', (349, 38, 349, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((355, 8, 355, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(355, 30, 355, 36): 'result', (355, 38, 355, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((358, 15, 362, 9), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((380, 19, 393, 9), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((395, 8, 395, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(395, 30, 395, 36): 'result', (395, 38, 395, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((400, 8, 400, 48), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(400, 30, 400, 36): 'result', (400, 38, 400, 47): 'expected2'}, {}), '(result, expected2)', True, 'import pandas._testing as tm\n'), ((405, 8, 405, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(405, 30, 405, 36): 'result', (405, 38, 405, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((418, 16, 423, 9), 'pandas.DataFrame', 'DataFrame', ({(419, 12, 422, 13): "{'id': ['a', 'b', np.nan, np.nan, np.nan], 'sv3': [1.234, 5.678, np.nan, np\n    .nan, np.nan]}"}, {}), "({'id': ['a', 'b', np.nan, np.nan, np.nan], 'sv3': [1.234, 5.678,\n    np.nan, np.nan, np.nan]})", False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((425, 17, 425, 56), 'pandas.core.reshape.merge.merge', 'merge', (), '', False, 'from pandas.core.reshape.merge import merge\n'), ((429, 8, 429, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(429, 30, 429, 36): 'result', (429, 38, 429, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((444, 16, 444, 66), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((454, 16, 454, 72), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((461, 8, 461, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(461, 30, 461, 36): 'result', (461, 38, 461, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((466, 13, 468, 9), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((469, 19, 469, 43), 'pandas.to_datetime', 'pd.to_datetime', ({(469, 34, 469, 42): 'df.index'}, {}), '(df.index)', True, 'import pandas as pd\n'), ((475, 19, 475, 75), 'pandas.DataFrame', 'DataFrame', ({(475, 29, 475, 74): "{'a': [1, 2, 3], 'key_1': [2016, 2017, 2018]}"}, {}), "({'a': [1, 2, 3], 'key_1': [2016, 2017, 2018]})", False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((478, 8, 478, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(478, 30, 478, 36): 'result', (478, 38, 478, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((480, 19, 482, 9), 'pandas.DataFrame', 'DataFrame', ({(481, 12, 481, 77): "{'key_0': [2016, 2017, 2018], 'a_x': [1, 2, 3], 'a_y': [1, 2, 3]}"}, {}), "({'key_0': [2016, 2017, 2018], 'a_x': [1, 2, 3], 'a_y': [1, 2, 3]})", False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((485, 8, 485, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(485, 30, 485, 36): 'result', (485, 38, 485, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((553, 8, 553, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(553, 30, 553, 36): 'result', (553, 38, 553, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((562, 8, 562, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(562, 30, 562, 36): 'result', (562, 38, 562, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((580, 8, 580, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(580, 30, 580, 36): 'result', (580, 38, 580, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((670, 8, 670, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(670, 30, 670, 36): 'result', (670, 38, 670, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((732, 8, 732, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(732, 30, 732, 36): 'result', (732, 38, 732, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((752, 8, 752, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(752, 30, 752, 36): 'result', (752, 38, 752, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((773, 8, 773, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(773, 30, 773, 36): 'result', (773, 38, 773, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((778, 13, 780, 9), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((781, 19, 781, 43), 'pandas.to_datetime', 'pd.to_datetime', ({(781, 34, 781, 42): 'df.index'}, {}), '(df.index)', True, 'import pandas as pd\n'), ((787, 19, 787, 75), 'pandas.DataFrame', 'DataFrame', ({(787, 29, 787, 74): "{'a': [1, 2, 3], 'key_1': [2016, 2017, 2018]}"}, {}), "({'a': [1, 2, 3], 'key_1': [2016, 2017, 2018]})", False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((790, 8, 790, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(790, 30, 790, 36): 'result', (790, 38, 790, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((792, 19, 794, 9), 'pandas.DataFrame', 'DataFrame', ({(793, 12, 793, 77): "{'key_0': [2016, 2017, 2018], 'a_x': [1, 2, 3], 'a_y': [1, 2, 3]}"}, {}), "({'key_0': [2016, 2017, 2018], 'a_x': [1, 2, 3], 'a_y': [1, 2, 3]})", False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((797, 8, 797, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(797, 30, 797, 36): 'result', (797, 38, 797, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((800, 21, 802, 9), 'pandas.MultiIndex.from_tuples', 'pd.MultiIndex.from_tuples', (), '', True, 'import pandas as pd\n'), ((804, 15, 806, 9), 'pandas.DataFrame', 'pd.DataFrame', (), '', True, 'import pandas as pd\n'), ((808, 22, 810, 9), 'pandas.MultiIndex.from_tuples', 'pd.MultiIndex.from_tuples', (), '', True, 'import pandas as pd\n'), ((812, 16, 815, 9), 'pandas.DataFrame', 'pd.DataFrame', (), '', True, 'import pandas as pd\n'), ((822, 8, 822, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(822, 30, 822, 36): 'result', (822, 38, 822, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((828, 16, 828, 78), 'pandas.MultiIndex.from_product', 'pd.MultiIndex.from_product', (), '', True, 'import pandas as pd\n'), ((829, 16, 829, 85), 'pandas.MultiIndex.from_tuples', 'pd.MultiIndex.from_tuples', (), '', True, 'import pandas as pd\n'), ((831, 15, 831, 70), 'pandas.DataFrame', 'pd.DataFrame', (), '', True, 'import pandas as pd\n'), ((832, 16, 832, 77), 'pandas.DataFrame', 'pd.DataFrame', (), '', True, 'import pandas as pd\n'), ((836, 19, 839, 9), 'pandas.DataFrame', 'pd.DataFrame', (), '', True, 'import pandas as pd\n'), ((841, 8, 841, 47), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(841, 30, 841, 36): 'result', (841, 38, 841, 46): 'expected'}, {}), '(result, expected)', True, 'import pandas._testing as tm\n'), ((83, 12, 83, 34), 'numpy.random.randn', 'np.random.randn', ({(83, 28, 83, 30): '10', (83, 32, 83, 33): '3'}, {}), '(10, 3)', True, 'import numpy as np\n'), ((140, 12, 140, 78), 'pandas._testing.assert_series_equal', 'tm.assert_series_equal', (), '', True, 'import pandas._testing as tm\n'), ((142, 12, 142, 73), 'pandas._testing.assert_series_equal', 'tm.assert_series_equal', (), '', True, 'import pandas._testing as tm\n'), ((151, 12, 151, 43), 'pandas._testing.assert_frame_equal', 'tm.assert_frame_equal', ({(151, 34, 151, 37): 'out', (151, 39, 151, 42): 'res'}, {}), '(out, res)', True, 'import pandas._testing as tm\n'), ((154, 25, 154, 56), 'numpy.random.choice', 'np.random.choice', ({(154, 42, 154, 44): 'lc', (154, 46, 154, 55): '(5000, 2)'}, {}), '(lc, (5000, 2))', True, 'import numpy as np\n'), ((200, 12, 200, 87), 'pandas.MultiIndex.from_product', 'pd.MultiIndex.from_product', (), '', True, 'import pandas as pd\n'), ((585, 13, 587, 9), 'pytest.raises', 'pytest.raises', (), '', False, 'import pytest\n'), ((593, 13, 593, 87), 'pytest.raises', 'pytest.raises', (), '', False, 'import pytest\n'), ((224, 62, 224, 84), 'numpy.random.randn', 'np.random.randn', ({(224, 78, 224, 83): '20000'}, {}), '(20000)', True, 'import numpy as np\n'), ((227, 61, 227, 83), 'numpy.random.randn', 'np.random.randn', ({(227, 77, 227, 82): '10000'}, {}), '(10000)', True, 'import numpy as np\n'), ((266, 22, 266, 63), 'numpy.array', 'np.array', (), '', True, 'import numpy as np\n'), ((306, 16, 324, 9), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((364, 16, 376, 9), 'pandas.DataFrame', 'DataFrame', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n'), ((411, 22, 411, 30), 'numpy.random.randn', 'randn', ({(411, 28, 411, 29): '5'}, {}), '(5)', False, 'from numpy.random import randn\n'), ((412, 22, 412, 30), 'numpy.random.randn', 'randn', ({(412, 28, 412, 29): '5'}, {}), '(5)', False, 'from numpy.random import randn\n'), ((414, 22, 414, 30), 'numpy.random.randn', 'randn', ({(414, 28, 414, 29): '5'}, {}), '(5)', False, 'from numpy.random import randn\n'), ((240, 30, 240, 43), 'numpy.arange', 'np.arange', ({(240, 40, 240, 42): '24'}, {}), '(24)', True, 'import numpy as np\n'), ((267, 30, 267, 43), 'numpy.arange', 'np.arange', ({(267, 40, 267, 42): '24'}, {}), '(24)', True, 'import numpy as np\n'), ((571, 30, 573, 25), 'pandas.MultiIndex.from_tuples', 'MultiIndex.from_tuples', (), '', False, 'from pandas import DataFrame, Index, MultiIndex, Series\n')]