CODEWITHSUNDEEP
pythonpandas
coding_monkey
coding_monkey

Reputation: 399

Pandas conditionally merge dataframes

I have the following 2 dataframes:

df1:
col1 col2 col3
a    d    1
a    d    2
b    e    3
b    e    4
c    f    5
c    f    6

df2:
col4 col5 col6
g    d    1
g    b    2
h    c    3
h    f    4
i    a    5
i    b    6

I want to merge the two dataframes by comparing values in col5 of df2 with col2 of df1 and only retain the matching values. So the merged dataframe would look like this:

df3:
col1 col2 col3 col4 col5 col6
a    d    1    NaN  NaN  NaN
a    d    2    NaN  NaN  NaN
NaN  NaN  NaN  g    d    1
c    f    5    NaN  NaN  NaN
c    f    6    NaN  NaN  NaN
NaN  NaN  NaN  h    f    4

I realize I can merge but it combines the rows, but I'd like to keep the rows separate.

Upvotes: 0

Views: 459

Answers (1)

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

What you need maybe is conditional selection and concatenation i.e 

one = df[df['col2'].isin(df2['col5'])]
two  = df2[df2['col5'].isin(df['col2'])]

ndf = pd.concat([one,two.set_axis(two.index + one.index.max()+1,inplace=False)], 1)
# Setting axis with `max+1` to avoid concatenation over same index. 


  col1 col2  col3 col4 col5  col6
0    a    d   1.0  NaN  NaN   NaN
1    a    d   2.0  NaN  NaN   NaN
4    c    f   5.0  NaN  NaN   NaN
5    c    f   6.0  NaN  NaN   NaN
6  NaN  NaN   NaN    g    d   1.0
9  NaN  NaN   NaN    h    f   4.0

Upvotes: 1

Related Questions