append columns to pandas dataframe with duplicate rows
<p>How can I append the columns from the <code>data</code> dataframe to the <code>q</code> dataframe, while maintaining the same order and number of rows in <code>q</code>? The challenge is that there can be duplicates in <code>data</code> and <code>q</code>.</p>

<pre><code>In [2]: data = pd.DataFrame([[3,4,333],[5,6,111],[2,9,222],[5,6,111]], columns=['a','b','id'])

In [3]: data.index = data.id

In [4]: q = pd.DataFrame([[333],[111]], columns=['id'])

In [5]: q.index = q.id

In [6]: data
Out[6]: 
     a  b   id
id            
333  3  4  333
111  5  6  111
222  2  9  222
111  5  6  111

In [7]: q
Out[7]: 
      id
id      
333  333
111  111
</code></pre>

<p>The result should look something like:</p>

<pre><code>   a  b   id
0  3  4  333
1  5  6  111
</code></pre>

<p>Some stuff that doesn't work:</p>

<pre><code>pd.merge(q, data, how='left')
data.ix[q.iloc[:,0],['a','b','id']]
</code></pre>

<p>I would prefer to not do a <code>unique</code> on <code>data</code> as this would create another large object.</p>

<p>One possible solution without drop duplicates is create new columns in both <code>DataFrames</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.cumcount.html" rel="nofollow"><code>cumcount</code></a> and then <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a> by <code>id</code> and <code>g</code>. Last need remove column <code>g</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop.html" rel="nofollow"><code>drop</code></a> and reorder columns:</p>

<pre><code>data['g'] = data.groupby('id').cumcount()
q['g'] = q.groupby('id').cumcount()

print (data)
     a  b   id  g
id               
333  3  4  333  0
111  5  6  111  0
222  2  9  222  0
111  5  6  111  1

print (q)
      id  g
id         
333  333  0
111  111  0

print (pd.merge(q, data, on=['id','g'], how='left')
         .drop('g', axis=1)[['a','b','id']])

   a  b   id
0  3  4  333
1  5  6  111
</code></pre>

<p>With <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop_duplicates.html" rel="nofollow"><code>drop_duplicates</code></a> it is easy:</p>

<pre><code>data = data.drop_duplicates('id')

print (data)
     a  b   id
id            
333  3  4  333
111  5  6  111
222  2  9  222

print (q)
      id
id      
333  333
111  111

print (pd.merge(q, data, how='left')[['a','b','id']])
   a  b   id
0  3  4  333
1  5  6  111
</code></pre>

python pandas