Merging dataframes on an index is more efficient in Pandas : <p>Why is merging dataframes in Pandas on an index more efficient (faster) than on a column? </p>



<pre class="lang-py prettyprint-override"><code>import pandas as pd

# Dataframes share the ID column
df = pd.DataFrame({'ID': [0, 1, 2, 3, 4],
                   'Job': ['teacher', 'scientist', 'manager', 'teacher', 'nurse']})

df2 = pd.DataFrame({'ID': [2, 3, 4, 5, 6, 7, 8],
                    'Level': [12, 15, 14, 20, 21, 11, 15], 
                    'Age': [33, 41, 42, 50, 45, 28, 32]})
</code></pre>

<p><a href="https://i.stack.imgur.com/SZ8pV.png" rel="noreferrer"><img src="https://i.stack.imgur.com/SZ8pV.png" alt="enter image description here"></a></p>

<pre class="lang-py prettyprint-override"><code>df = df.set_index('ID')
df2 = df2.set_index('ID')
</code></pre>

<p><a href="https://i.stack.imgur.com/NaXvo.png" rel="noreferrer"><img src="https://i.stack.imgur.com/NaXvo.png" alt="enter image description here"></a></p>

<p>This represents about a 3.5 times speed up! (Using Pandas 0.23.0) </p>

<p>Reading through the <a href="https://pandas.pydata.org/pandas-docs/stable/internals.html" rel="noreferrer">Pandas internals page</a> it says an Index "Populates a dict of label to location in Cython to do O(1) lookups." Does this mean that doing operations with an index is more efficient than with columns? Is it a best practice to always use the index for operations such as merges? </p>

<p>I read through the <a href="https://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging" rel="noreferrer">documentation for joining and merging</a> and it doesn't explicitly mention any benefits to using the index.  </p>
