Any way to replace pandas pd.merge?
<p>I have two dataframe </p>

<pre><code>&gt;&gt;df1.info()
&gt;&gt;&lt;class 'pandas.core.frame.DataFrame'&gt;
  Int64Index: 2598374 entries, 3975 to 3054366
  Data columns (total 14 columns): ......
&gt;&gt;df2.info()
&gt;&gt;&lt;class 'pandas.core.frame.DataFrame'&gt;
  Int64Index: 2520405 entries, 2066 to 2519507
  Data columns (total 5 columns): ......
</code></pre>

<p>I wanna inner join them. I tried <code>pd.merge</code> and I got memory error. Thus, I tried to do same things without <code>pd.merge</code>.</p>

<p>Example dataframe for original method (failed: memory error)</p>

<pre><code>df1 = pd.DataFrame({'A': ['1', '2', '3', '4','5'],
              'B': ['1', '1', '1', '1','1'],
              'C': ['c', 'A1', 'a', 'c3','a'],
              'D': ['B1', 'B1', 'B2', 'B3','B4'],
              'E': ['3', '3', '3', '3','3'],
              'F': ['3', '4', '5', '6','7'],
              'G': ['2', '2', '2', '2','2']})

df2 = pd.DataFrame({'A': ['1', '2',  '8','4'],
              'B': ['1', '2',  '5','1'],
              'x': ['3', '3', '2','2'],
              'y': ['3', '4', '6','7'],
              'z': ['2', '2', '2','2']})
&gt;&gt;   df1
         A  B   C   D  E  F  G
      0  1  1   c  B1  3  3  2
      1  2  1  A1  B1  3  4  2
      2  3  1   a  B2  3  5  2
      3  4  1  c3  B3  3  6  2
      4  5  1   a  B4  3  7  2

     df2
         A  B  x  y  z
      0  1  1  3  3  2
      1  2  2  3  4  2
      2  8  5  2  6  2
      3  4  1  2  7  2

df1 = pd.merge(df1,df2,how='inner',on=['A','B']) 

&gt;&gt;   df1    
         A  B   C   D  E  F  G  x  y  z
      0  1  1   c  B1  3  3  2  3  3  2
      1  4  1  c3  B3  3  6  2  2  7  2
</code></pre>

<p>Example for new method<br>
(1) I tried to delete rows in df1 which are not in df2 by column['A']['B'].<br>
(2) concat x,y,z columns to df1</p>

<pre><code>df1 = pd.DataFrame({'A': ['1', '2', '3', '4','5'],
              'B': ['1', '1', '1', '1','1'],
              'C': ['c', 'A1', 'a', 'c3','a'],
              'D': ['B1', 'B1', 'B2', 'B3','B4'],
              'E': ['3', '3', '3', '3','3'],
              'F': ['3', '4', '5', '6','7'],
              'G': ['2', '2', '2', '2','2']})

df2 = pd.DataFrame({'A': ['1', '2',  '8','4'],
              'B': ['1', '2',  '5','1'],
              'x': ['3', '3', '2','2'],
              'y': ['3', '4', '6','7'],
              'z': ['2', '2', '2','2']})
&gt;&gt;   df1
         A  B   C   D  E  F  G
      0  1  1   c  B1  3  3  2
      1  2  1  A1  B1  3  4  2
      2  3  1   a  B2  3  5  2
      3  4  1  c3  B3  3  6  2
      4  5  1   a  B4  3  7  2

     df2
         A  B  x  y  z
      0  1  1  3  3  2
      1  2  2  3  4  2
      2  8  5  2  6  2
      3  4  1  2  7  2

df1 = df1.loc[((df1['A'].isin(df2.A)) &amp; (df1['B'].isin(df2.B)) ) ]

&gt;&gt;   df1    
         A  B   C   D  E  F  G  
      0  1  1   c  B1  3  3  2  
      1  2  1  A1  B1  3  4  2
      3  4  1  c3  B3  3  6  2  
</code></pre>

<p>however, I got a logical error and I have no idea to solve this problem.
Can anyone help? </p>

<p>You can try <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow"><code>concat</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow"><code>set_index</code></a>:</p>

<pre><code>df1 = pd.concat([df1.set_index(['A','B']),
                 df2.set_index(['A','B'])], axis=1, join='inner')
print (df1)              
      C   D  E  F  G  x  y  z
A B                          
1 1   c  B1  3  3  2  3  3  2
4 1  c3  B3  3  6  2  2  7  2
</code></pre>

<p>Or combination with <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a>:</p>

<pre><code>df1 = df1[((df1['A'].isin(df2.A)) &amp; (df1['B'].isin(df2.B)) ) ]
print (df1)
   A  B   C   D  E  F  G
0  1  1   c  B1  3  3  2
1  2  1  A1  B1  3  4  2
3  4  1  c3  B3  3  6  2

df2 = df2[((df2['A'].isin(df1.A)) &amp; (df2['B'].isin(df1.B)) ) ]
print (df2)
   A  B  x  y  z
0  1  1  3  3  2
3  4  1  2  7  2

df3 = pd.concat([df1.set_index(['A','B']),
                 df2.set_index(['A','B'])], axis=1, join='inner')
print (df3)              
      C   D  E  F  G  x  y  z
A B                          
1 1   c  B1  3  3  2  3  3  2
4 1  c3  B3  3  6  2  2  7  2
</code></pre>

<p>If <code>df1</code> after filtering is not large, use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a>:</p>

<pre><code>df1 = df1[((df1['A'].isin(df2.A)) &amp; (df1['B'].isin(df2.B)) ) ]
print (df1)
   A  B   C   D  E  F  G
0  1  1   c  B1  3  3  2
1  2  1  A1  B1  3  4  2
3  4  1  c3  B3  3  6  2

df2 = df2[((df2['A'].isin(df1.A)) &amp; (df2['B'].isin(df1.B)) ) ]
print (df2)
   A  B  x  y  z
0  1  1  3  3  2
3  4  1  2  7  2

df3 = pd.merge(df1,df2, on=['A','B']) 
print (df3)              
   A  B   C   D  E  F  G  x  y  z
0  1  1   c  B1  3  3  2  3  3  2
1  4  1  c3  B3  3  6  2  2  7  2
</code></pre>

python pandas