Pandas merge not keeping 'on' column
<p>I'm trying to merge two dataframes in <code>pandas</code> on a common column name (orderid).  The resulting dataframe (the merged dataframe) is dropping the orderid from the 2nd data frame.  Per the <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html" rel="nofollow" title="documentation">documentation</a>, the 'on' column should be kept unless you explicitly tell it not to.  </p>

<pre><code>import pandas as pd    
df = pd.DataFrame([[1,'a'], [2, 'b'], [3, 'c']], columns=['orderid', 'ordervalue'])
df['orderid'] = df['orderid'].astype(str)
df2 = pd.DataFrame([[1,200], [2, 300], [3, 400], [4,500]], columns=['orderid', 'ordervalue'])
df2['orderid'] = df2['orderid'].astype(str)
pd.merge(df, df2, on='orderid', how='outer', copy=True, suffixes=('_left', '_right'))
</code></pre>

<p>Which outputs this:</p>

<pre><code>|      |orderid | ordervalue_left | ordervalue_right |
|------|--------|-----------------|------------------|
| 0    | 1      | a               | 200              |
| 1    | 2      | b               | 300              |
| 2    | 3      | c               | 400              |
| 3    | 4      |                 | 500              |
</code></pre>

<p>What I am trying to create is this:</p>

<pre><code>|      | orderid_left | ordervalue_left | orderid_left | ordervalue_right |
|------|--------------|-----------------|--------------|------------------|
| 0    | 1            | a               | 1            | 200              |
| 1    | 2            | b               | 2            | 300              |
| 2    | 3            | c               | 3            | 400              |
| 3    | NaN          | NaN             | 4            | 500              |
</code></pre>

<p>How should I write this?</p>

<p>Rename the <code>orderid</code> columns so that <code>df</code> has a column named <code>orderid_left</code>,
and <code>df2</code> has a column named <code>orderid_right</code>:</p>

<pre><code>import pandas as pd    
df = pd.DataFrame([[1,'a'], [2, 'b'], [3, 'c']], columns=['orderid', 'ordervalue'])
df['orderid'] = df['orderid'].astype(str)
df2 = pd.DataFrame([[1,200], [2, 300], [3, 400], [4,500]], columns=['orderid', 'ordervalue'])
df2['orderid'] = df2['orderid'].astype(str)

df = df.rename(columns={'orderid':'orderid_left'})
df2 = df2.rename(columns={'orderid':'orderid_right'})
result = pd.merge(df, df2, left_on='orderid_left', right_on='orderid_right', 
                  how='outer', suffixes=('_left', '_right'))
print(result)
</code></pre>

<p>yields</p>

<pre><code>  orderid_left ordervalue_left orderid_right  ordervalue_right
0            1               a             1               200
1            2               b             2               300
2            3               c             3               400
3          NaN             NaN             4               500
</code></pre>

python pandas