Explain how pandas DataFrame join works
<p>Why does inner join work so strange in pandas?</p>

<p><strong>For example:</strong></p>

<pre><code>import pandas as pd
import io

t1 = ('key,col1\n'
      '1,a\n'
      '2,b\n'
      '3,c\n'
      '4,d')

t2 = ('key,col2\n'
      '1,e\n'
      '2,f\n'
      '3,g\n'
      '4,h')


df1 = pd.read_csv(io.StringIO(t1), header=0)
df2 = pd.read_csv(io.StringIO(t2), header=0)

print(df1)
print()
print(df2)
print()
print(df2.join(df1, on='key', how='inner', lsuffix='_l'))
</code></pre>

<p><strong>Outputs:</strong></p>

<pre><code>   key col1
0    1    a
1    2    b
2    3    c
3    4    d

   key col2
0    1    e
1    2    f
2    3    g
3    4    h

   key_l col2  key col1
0      1    e    2    b
1      2    f    3    c
2      3    g    4    d
</code></pre>

<p>If I don't specify <code>lsuffix</code>, it says</p>

<pre><code>ValueError: columns overlap but no suffix specified: Index(['key'], dtype='object')
</code></pre>

<p>Does this function work differently from SQL's JOIN? Why does it want to create an extra 'key' column with a suffix? Why are there only 3 rows?
I expected it to output something like this:</p>

<pre><code>   key col1 col2
0    1    a    e
1    2    b    f
2    3    c    g
3    4    d    h
</code></pre>

<p>First things first:<br>
What you wanted was merge</p>

<pre><code>df1.merge(df2)
</code></pre>

<p><a href="http://i.stack.imgur.com/HnoA8.png" rel="nofollow"><img src="http://i.stack.imgur.com/HnoA8.png" alt="enter image description here"></a></p>

<hr>

<p><code>join</code> defaults to merging on the <code>index</code>.  You can specify the <code>on</code> parameter which only says which column from left side to match with the index of the right side.  </p>

<p>These might help illustrate</p>

<pre><code>df1.set_index('key').join(df2.set_index('key'))
</code></pre>

<p><a href="http://i.stack.imgur.com/Mpw4c.png" rel="nofollow"><img src="http://i.stack.imgur.com/Mpw4c.png" alt="enter image description here"></a></p>

<pre><code>df1.join(df2.set_index('key'), on='key')
</code></pre>

<p><a href="http://i.stack.imgur.com/9QQgK.png" rel="nofollow"><img src="http://i.stack.imgur.com/9QQgK.png" alt="enter image description here"></a></p>

<hr>

<p>Your example is matching the index of <code>df2</code> which looks like <code>[0, 1, 2, 3]</code> with the <code>key</code> column of <code>df1</code> which looks like <code>[1, 2, 3, 4]</code><br>
That's why you get <code>NaN</code> in <code>col2</code> when <code>key_l</code> is <code>4</code></p>

<pre><code>df1.join(df2, on='key', lsuffix='_l', how='outer')
</code></pre>

<p><a href="http://i.stack.imgur.com/Wg93O.png" rel="nofollow"><img src="http://i.stack.imgur.com/Wg93O.png" alt="enter image description here"></a></p>

python python-3.x pandas dataframe