Create two dataframes from two dataframes based on multiindex in one dataframe and columns in another dataframe

Question

I m not sure if this has been answered before. But my requirement is that I have a dataframe like this:

df1:

         A  B
I1 I2

x11 x12  a11 b11
x12 x22  a21 b21

Note that this has multiindex of [I1, I2] and columns [A, B]

and then another dataframe like this:

df2:

    I1   I2
  0  x11  x12
  1  y11  y12

This has columns [I1, I2] which is the same as multiindex of df1.

Now what I would like to create is two dataframes like below:

df3 which has rows for which the index in df1 matches to that of column values in df2

A  B
a11 b11

df4 with the remaining i.e.

A  B
a21 b21

I know how to do this using iterrows() but it is not efficient. Looking for a vectorized solution. Thanks.

BENY · Accepted Answer · 2020-05-31 02:08:42Z

1

Let us try reset_index with merge

df3=df1.reset_index().merge(df2).set_index(['I1','I2'])
df4=df1.drop(df3.index)

Or

idx=pd.MultiIndex.from_frame(df2)
df3=df1.reindex(idx).dropna()
df4=df1.drop(df3.index)

edited May 31, 2020 at 2:08

answered May 31, 2020 at 2:03

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SomeDude Over a year ago

Thank you, I m going to check on this. Also on another note, with the first approach I get a multiindex of type [I1, [I21, I22]] , how can I flatten this like [I1, I21], [I1, I22] ?

BENY Over a year ago

@check itertools product ?

SomeDude · Accepted Answer · 2020-05-31 19:39:25Z

0

Just to record another way of doing it, posting this:

I could set_index on df2 with [I1, I2] and then do a isin like:

is_index_there = df1.index.isin(df2.set_index([I1, I2]).index)

and then use that to create separate dfs like :

df3 = df1.loc[is_index_there == True] and

df4 = df2.loc[is_index_there == False]

edited May 31, 2020 at 19:39

answered May 31, 2020 at 16:16

SomeDude

14.3k5 gold badges26 silver badges49 bronze badges

Collectives™ on Stack Overflow

Create two dataframes from two dataframes based on multiindex in one dataframe and columns in another dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related