Concatenating pandas DataFrames keeping only rows with matching values in a column?
<p>I am trying to "merge-concatenate" two pandas DataFrames. Basically, I want to stack the two DataFrames, but only keep the rows from each DataFrame which matching values in the other DataFrame. So for example:</p>

<pre><code>data1:

+---+------------+-----------+-------+
|   | first_name | last_name | class |
+---+------------+-----------+-------+
| 0 | Alex       | Anderson  |     1 |
| 1 | Amy        | Ackerman  |     2 |
| 2 | Allen      | Ali       |     3 |
| 3 | Alice      | Aoni      |     4 |
| 4 | Andrew     | Andrews   |     4 |
| 5 | Ayoung     | Atiches   |     5 |
+---+------------+-----------+-------+

data2:

+---+------------+-----------+-------+
|   | first_name | last_name | class |
+---+------------+-----------+-------+
| 0 | Billy      | Bonder    |     4 |
| 1 | Brian      | Black     |     5 |
| 2 | Bran       | Balwner   |     6 |
| 3 | Bryce      | Brice     |     7 |
| 4 | Betty      | Btisan    |     8 |
| 5 | Bruce      | Bronson   |     8 |
+---+------------+-----------+-------+
</code></pre>

<p>Then the resulting data frame after performing this operation on <code>data1</code> and <code>data2</code> should look like:</p>

<pre><code>result:

+---+------------+-----------+-------+
|   | first_name | last_name | class |
+---+------------+-----------+-------+
| 3 | Alice      | Aoni      |     4 |
| 4 | Andrew     | Andrews   |     4 |
| 5 | Ayoung     | Atiches   |     5 |
| 0 | Billy      | Bonder    |     4 |
| 1 | Brian      | Black     |     5 |
+---+------------+-----------+-------+
</code></pre>

<p>Basically, I'm trying to merge the two data sets, and then stack the columns. I can think of a couple ways to do this, but they're all sort of hack-y. I could merge <code>data1</code> and <code>data2</code> and then stack up the columns, or use a map like:</p>

<pre><code>map1 = data1['subject_id'].map(lambda x: x in list(data2['subject_id']))
map2 = data2['subject_id'].map(lambda x: x in list(data1['subject_id']))
pd.concat([data1[map1], data2[map2]])
</code></pre>

<p>But is there a more elegant solution to this?</p>

<p>How about this?</p>

<pre><code>In [335]: cls = np.intersect1d(data1['class'], data2['class'])

In [336]: cls
Out[336]: array([4, 5], dtype=int64)

In [337]: pd.concat([data1.ix[data1['class'].isin(cls)], data2.ix[data2['class'].isin(cls)]])
Out[337]:
  first_name last_name  class
3      Alice      Aoni      4
4     Andrew   Andrews      4
5     Ayoung   Atiches      5
0      Billy    Bonder      4
1      Brian     Black      5
</code></pre>

<p>or:</p>

<pre><code>In [338]: data1.ix[data1['class'].isin(cls)].append(data2.ix[data2['class'].isin(cls)])
Out[338]:
  first_name last_name  class
3      Alice      Aoni      4
4     Andrew   Andrews      4
5     Ayoung   Atiches      5
0      Billy    Bonder      4
1      Brian     Black      5
</code></pre>

python pandas dataframe