ENH: Support for semi-join on index (subsetting a multi-index dataframe with a subset of the multi-index) about pandas HOT 5 OPEN

@gwerbin Can you share a reproducible example?

from pandas.

Seems to me this is P.join(Q, how="right"), but as @samukweku said - please provide a reproducible example with the input and desired output.

from pandas.

It's not exactly the same as the right join because I'm not interested in any of the columns from Q, only subsetting indices. But I realize now that you could write it like this, which is a lot tidier than what I was doing before:

P.join(Q.loc[:, []], how="right")

I also had different behavior in mind for if there were duplicates in Q.index than what right join offers, but that wasn't part of my original example.

Maybe this is a good enough recipe to warrant not adding new functionality to the Pandas interface, but it's not obvious and IMO would look like opaque magic to a typical non-expert user.

The non-duplicate case

P = pd.DataFrame(
    data=[
        (1, 9, "u", -1.70),
        (1, 9, "v", -1.75),
        (2, 8, "u", -1.60),
        (2, 8, "v", -1.65),
        (1, 8, "u", -1.50),
        (1, 8, "v", -1.55),
        (2, 7, "u", -1.40),
        (2, 7, "v", -1.45),
    ],
    columns=["a", "b", "c", "x"],
).set_index(["a", "b", "c"])

Q = pd.DataFrame(
    data=[
        (1, 9, 2.5),
        (2, 7, 3.5),
    ],
    columns=["a", "b", "y"],
).set_index(["a", "b"])

expected = pd.DataFrame(
    data=[
        (1, 9, "u", -1.70),
        (1, 9, "v", -1.75),
        (2, 7, "u", -1.40),
        (2, 7, "v", -1.45),
    ],
    columns=["a", "b", "c", "x"],
).set_index(["a", "b", "c"])

pd.testing.assert_frame_equal(
    P.join(Q.loc[:, []], how="right"),
    expected,
)

The duplicate case

I didn't want to complicate things by including this example, but this is where what I want diverges from a typical right join.

Inputs:

P = pd.DataFrame(
    data=[
        (1, 9, "u", -1.70),
        (1, 9, "v", -1.75),
        (2, 8, "u", -1.60),
        (2, 8, "v", -1.65),
        (1, 8, "u", -1.50),
        (1, 8, "v", -1.55),
        (2, 7, "u", -1.40),
        (2, 7, "v", -1.45),
    ],
    columns=["a", "b", "c", "x"],
).set_index(["a", "b", "c"])

Q = pd.DataFrame(
    data=[
        (1, 9, 2.5),
        (2, 7, 3.5),
        (2, 7, 4.5),
    ],
    columns=["a", "b", "y"],
).set_index(["a", "b"])

expected = pd.DataFrame(
    data=[
        (1, 9, "u", -1.70),
        (1, 9, "v", -1.75),
        (2, 7, "u", -1.40),
        (2, 7, "v", -1.45),
    ],
    columns=["a", "b", "c", "x"],
).set_index(["a", "b", "c"])

pd.testing.assert_frame_equal(
    P.join(Q.loc[~Q.index.duplicated(keep="first"), []], how="right"),
    expected,
)

Prior art

Note that Polars supports how="semi" join in the polars.DataFrame.join method: https://docs.pola.rs/py-polars/html/reference/dataframe/api/polars.DataFrame.join.html

They also support the similarly-useful how="anti" join, which was also not part of the original intended scope, but would IMO be useful in Pandas for similar reasons.

from pandas.

But I realize now that you could write it like this, which is a lot tidier than what I was doing before:

P.join(Q.loc[:, []], how="right")

It can be slightly simpler with P.join(Q[[]], how="right").

Maybe this is a good enough recipe to warrant not adding new functionality to the Pandas interface, but it's not obvious and IMO would look like opaque magic to a typical non-expert user.

Adding a new method/arguments to pandas for cases like this is not sustainable in my opinion.

from pandas.

P.join(Q.loc[~Q.index.duplicated(keep="first"), []], how="right")

Sorry - I missed this but my position is the same. pandas provides all the tools for you to accomplish the computation in a reasonable line of code.

from pandas.