CODEWITHSUNDEEP
pythonpython-3.xpandasmerge
Rich C
Rich C

Reputation: 3244

Pandas Merge with NaN in key

I'm trying to merge 2 dataframes that both have NaN in their key column. NaN does not equal NaN, but yet the two NaNs in the "key" columns are matching. Why is that, and how can I get them not to match? I'm using python 3.6.

df1 = pd.DataFrame({'key': [3,2,1,1,np.nan,5], 'value': np.random.randn(6)})
df2 = pd.DataFrame({'key': [1,3,np.nan], 'value': np.random.randn(3)})

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

print(df1)
print(df2)
print(df)

   key     value
0  3.0  0.642917
1  2.0  1.347245
2  1.0 -1.381299
3  1.0  1.839940
4  NaN  0.770599
5  5.0 -0.137404

   key     value
0  1.0  0.580794
1  3.0  0.569973
2  NaN -0.078336

   key   value_x   value_y
0  3.0  0.642917  0.569973
1  2.0  1.347245       NaN
2  1.0 -1.381299  0.580794
3  1.0  1.839940  0.580794
4  NaN  0.770599 -0.078336
5  5.0 -0.137404       NaN

np.nan == np.nan
Out[25]: False

Upvotes: 10

Views: 9529

Answers (1)

cs95
cs95

Reputation: 402363

I once answered a question on the "why" part, you can read more at Why does pandas merge on NaN?.

To fix, why not just call dropna before merging?

df1.merge(df2.dropna(subset=['key']), on='key', how='left')

   key   value_x   value_y
0  3.0 -0.177450 -1.879047
1  2.0  0.179939       NaN
2  1.0 -1.033730 -1.433606
3  1.0  1.426648 -1.433606
4  NaN -0.320173       NaN
5  5.0 -1.824740       NaN

Upvotes: 9

Related Questions