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>You could use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.join.html" rel="nofollow"><code>join</code></a> to join the columns of the two dataframes on a common index, <code>id</code>. Then, drop the duplicated values along with dropping off <code>Nans</code> if present as shown:</p>

<pre><code>data[['a', 'b']].join(q['id'])                           \
                .drop_duplicates()                       \ 
                .dropna()                                \ 
                .sort_values('id', ascending=False)      \
                .reset_index(drop=True)                  \
                .astype(int)                              

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

python pandas