<p dir="auto">In pandas, unlike SQL, the rows seemed to be joining on null values. Is this a bug?<br>
related SO: <a href="http://stackoverflow.com/questions/23940181/pandas-merging-with-missing-values/23940686#23940686" rel="nofollow">http://stackoverflow.com/questions/23940181/pandas-merging-with-missing-values/23940686#23940686</a></p>
<p dir="auto">Code snippet</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import pandas as pd 
import numpy as np

df1 = pd.DataFrame(
    [[1, None],
    [2, 'y']],
    columns = ['A', 'B']
)
print df1

df2 = pd.DataFrame(
    [['y', 'Y'],
    [None, 'None1'],
    [None, 'None2']],
    columns = ['B', 'C']
)
print df2

print df1.merge(df2, on='B', how='outer')"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span> 
<span class="pl-k">import</span> <span class="pl-s1">numpy</span> <span class="pl-k">as</span> <span class="pl-s1">np</span>

<span class="pl-s1">df1</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>(
    [[<span class="pl-c1">1</span>, <span class="pl-c1">None</span>],
    [<span class="pl-c1">2</span>, <span class="pl-s">'y'</span>]],
    <span class="pl-s1">columns</span> <span class="pl-c1">=</span> [<span class="pl-s">'A'</span>, <span class="pl-s">'B'</span>]
)
<span class="pl-k">print</span> <span class="pl-s1">df1</span>

<span class="pl-s1">df2</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>(
    [[<span class="pl-s">'y'</span>, <span class="pl-s">'Y'</span>],
    [<span class="pl-c1">None</span>, <span class="pl-s">'None1'</span>],
    [<span class="pl-c1">None</span>, <span class="pl-s">'None2'</span>]],
    <span class="pl-s1">columns</span> <span class="pl-c1">=</span> [<span class="pl-s">'B'</span>, <span class="pl-s">'C'</span>]
)
<span class="pl-k">print</span> <span class="pl-s1">df2</span>

<span class="pl-k">print</span> <span class="pl-s1">df1</span>.<span class="pl-en">merge</span>(<span class="pl-s1">df2</span>, <span class="pl-s1">on</span><span class="pl-c1">=</span><span class="pl-s">'B'</span>, <span class="pl-s1">how</span><span class="pl-c1">=</span><span class="pl-s">'outer'</span>)</pre></div>
<p dir="auto">Output</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   A     B
0  1  None
1  2     y

      B      C
0     y      Y
1  None  None1
2  None  None2

   A     B      C
0  1  None  None1
1  1  None  None2
2  2     y      Y"><pre class="notranslate"><code class="notranslate">   A     B
0  1  None
1  2     y

      B      C
0     y      Y
1  None  None1
2  None  None2

   A     B      C
0  1  None  None1
1  1  None  None2
2  2     y      Y
</code></pre></div>
<p dir="auto">You can see row 0 in df1 unexpectedly joins to both rows in df2.</p>
<p dir="auto">I would expect the correct answer to be</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   A       B      C
0  1      None  None
1  2       y      Y
2 None None None1
3 None None None2"><pre class="notranslate"><code class="notranslate">   A       B      C
0  1      None  None
1  2       y      Y
2 None None None1
3 None None None2
</code></pre></div>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import pandas as pd

left = pd.DataFrame({&quot;a&quot;: [1, 2, pd.NA]}, dtype=&quot;Int64&quot;)
right = pd.DataFrame({&quot;b&quot;: [1, 2, pd.NA]}, dtype=&quot;Int64&quot;)

pd.merge(left, right, how=&quot;inner&quot;, left_on=&quot;a&quot;, right_on=&quot;b&quot;)
#       a     b
# 0     1     1
# 1     2     2
# 2  &lt;NA&gt;  &lt;NA&gt;"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>

<span class="pl-s1">left</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>({<span class="pl-s">"a"</span>: [<span class="pl-c1">1</span>, <span class="pl-c1">2</span>, <span class="pl-s1">pd</span>.<span class="pl-v">NA</span>]}, <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">"Int64"</span>)
<span class="pl-s1">right</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>({<span class="pl-s">"b"</span>: [<span class="pl-c1">1</span>, <span class="pl-c1">2</span>, <span class="pl-s1">pd</span>.<span class="pl-v">NA</span>]}, <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">"Int64"</span>)

<span class="pl-s1">pd</span>.<span class="pl-en">merge</span>(<span class="pl-s1">left</span>, <span class="pl-s1">right</span>, <span class="pl-s1">how</span><span class="pl-c1">=</span><span class="pl-s">"inner"</span>, <span class="pl-s1">left_on</span><span class="pl-c1">=</span><span class="pl-s">"a"</span>, <span class="pl-s1">right_on</span><span class="pl-c1">=</span><span class="pl-s">"b"</span>)
<span class="pl-c">#       a     b</span>
<span class="pl-c"># 0     1     1</span>
<span class="pl-c"># 1     2     2</span>
<span class="pl-c"># 2  &lt;NA&gt;  &lt;NA&gt;</span></pre></div>
<p dir="auto">(Above is from 1.0.1 and master)</p>
<p dir="auto">I think when joining on a nullable column we should not be matching <code class="notranslate">NA</code> with <code class="notranslate">NA</code> and should only be joining where we have unambiguous equality (as in SQL). Also worth noting that this is the same as what happens when we have <code class="notranslate">NaN</code> which also seems incorrect, so could be an opportunity to fix this behavior?</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="pd.merge(left.astype(float), right.astype(float), how=&quot;inner&quot;, left_on=&quot;a&quot;, right_on=&quot;b&quot;)
#      a    b
# 0  1.0  1.0
# 1  2.0  2.0
# 2  NaN  NaN"><pre class="notranslate"><span class="pl-s1">pd</span>.<span class="pl-en">merge</span>(<span class="pl-s1">left</span>.<span class="pl-en">astype</span>(<span class="pl-s1">float</span>), <span class="pl-s1">right</span>.<span class="pl-en">astype</span>(<span class="pl-s1">float</span>), <span class="pl-s1">how</span><span class="pl-c1">=</span><span class="pl-s">"inner"</span>, <span class="pl-s1">left_on</span><span class="pl-c1">=</span><span class="pl-s">"a"</span>, <span class="pl-s1">right_on</span><span class="pl-c1">=</span><span class="pl-s">"b"</span>)
<span class="pl-c">#      a    b</span>
<span class="pl-c"># 0  1.0  1.0</span>
<span class="pl-c"># 1  2.0  2.0</span>
<span class="pl-c"># 2  NaN  NaN</span></pre></div>
<h4 dir="auto">Expected Output</h4>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="      a     b
0     1     1
1     2     2"><pre class="notranslate"><code class="notranslate">      a     b
0     1     1
1     2     2
</code></pre></div>
<p dir="auto">cc <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/jorisvandenbossche/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/jorisvandenbossche">@jorisvandenbossche</a></p>