python/Pandas merge two data frames and return on the rows containing NaN

Question

Is it possible to use merge to df's, df1 and df2, to return only the rows that are Nan in df2? an example:

import pandas as pd
import numpy as np

data = {'X1': ['A', 'B', 'C', 'D', 'E'], 
        'X2': ['meow', 'bark', 'moo', 'squeak', 'cheep']}

data2 = {'X1': ['A', 'B', 'F', 'D', 'E'], 
         'X3': ['cat', 'dog', 'frog', 'mouse', 'chick']}

#df = pd.DataFrame(data, columns = ['name', 'age', 'preTestScore', 
'postTestScore'])
df = pd.DataFrame(data)
df2 = pd.DataFrame(data2)

pd.merge(df, df2, how ='left', on = 'X1')

This returns

X1   X2     X3
A   meow    cat
B   bark    dog
C   moo NaN
D   squeak  mouse
E   cheep   chick

I want a result that looks like:

X1   X2     X3
C   moo NaN

I have tried every combination of merge that I can think of but I can't get it. I know I can achieve the result with lots of manipulations to the merged df, but i was hoping there was a simple efficient answer

@student! Ha! that's what I was looking for! I didn't think I could use isin on a df column like that! Thanks! — Mick Hawkes
– Mick Hawkes, Commented Jun 17, 2018 at 0:46

BENY · Accepted Answer · 2018-06-17 01:26:31Z

1

Just using isnull and any with your result to filter it

s=pd.merge(df, df2, how ='left', on = 'X1')
s[s.isnull().any(1)]
Out[185]: 
  X1   X2   X3
2  C  moo  NaN

answered Jun 17, 2018 at 1:26

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

Mick Hawkes Over a year ago

thanks for that. It gives me another option to use besides students above. I wonder which method is the most efficient time wise

BENY Over a year ago

@MickHawkes this work for multiple column match if you need to match two key , I recommend merge rather than Isin

Collectives™ on Stack Overflow

python/Pandas merge two data frames and return on the rows containing NaN

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related