How to compare two different pandas dataframe whose lengths are not equal?
<p>I'm having two dataframes i.e. <strong>df1 and df2</strong> </p>

<pre><code>df1:                                 df2:

  Column1  Column2             ColumnA ColumnB 
0    abc       a              0  stu       aaa
1    pqr       b              1  mno       bbb
2    stu       c              2  pqr       ccc
3    mno       d              3  abc       ddd
4    xyz       e              4  xyz       eee
                              5  uiq       fff
                              6  mls       ggg
                              7  qww       hhh
                              8  dfg       iii
</code></pre>

<p>Now I want to use <code>column1</code> first value i.e. <code>abc</code> and search for this value is available in the <code>columnA</code> of the <code>df2</code>. If match found then I want that matching row's value of <code>columnB</code> in one seperate column of <code>df1</code>. (note - every entry in <code>column1</code> may available at once or none in <code>columnA</code> of the <code>df2</code>).</p>

<p><strong>Output format :</strong></p>

<pre><code>df1 :
 Column1  Column2  Column3 
0    abc       a      ddd
1    pqr       b      ccc
2    stu       c      aaa 
3    mno       d      bbb 
4    xyz       e      eee
</code></pre>

<p>I tried with different scenarios. when I try to compare this dataframes like:</p>

<pre><code>df['column1'] == df['columnA']
</code></pre>

<p>I'm getting error because length of both dataframes is not same. How I can perform this type of operation in pandas dataframe?</p>

<p>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a>, but first rename column <code>ColumnA</code> and last <code>ColumnB</code>:</p>

<pre><code>print (pd.merge(df1,df2.rename(columns={'ColumnA':'Column1'}))
         .rename(columns={'ColumnB': 'Column3'}))

  Column1 Column2 Column3
0     abc       a     ddd
1     pqr       b     ccc
2     stu       c     aaa
3     mno       d     bbb
4     xyz       e     eee
</code></pre>

<p>Another solution with parameters <code>left_on</code> and <code>right_on</code>, but is necessary <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop.html" rel="nofollow"><code>drop</code></a> column <code>ColumnA</code>:</p>

<pre><code>print (pd.merge(df1,df2, left_on='Column1', right_on='ColumnA')
         .drop('ColumnA', axis=1)
         .rename(columns={'ColumnB': 'Column3'}))

  Column1 Column2 Column3
0     abc       a     ddd
1     pqr       b     ccc
2     stu       c     aaa
3     mno       d     bbb
4     xyz       e     eee
</code></pre>

<hr>

<p>EDIT by comment:</p>

<p>If joined values are duplicated, rows are multipled:</p>

<pre><code>import pandas as pd

data = [['abc','a'], ['pqr','b'], ['pqr','b'], ['pqr','b']]
df1 = pd.DataFrame(data, columns=['Column1','Column2'])


df2 = pd.DataFrame({'ColumnA': {0: 'stu', 1: 'pqr', 2: 'pqr'}, 
                    'ColumnB': {0: 'aaa', 1: 'ccc', 2: 'ccc'}})


print (df1)
  Column1 Column2
0     abc       a
1     pqr       b
2     pqr       b
3     pqr       b

print (df2)
  ColumnA ColumnB
0     stu     aaa
1     pqr     ccc
2     pqr     ccc

print (pd.merge(df1,df2.rename(columns={'ColumnA':'Column1'}))
         .rename(columns={'ColumnB': 'Column3'}))

  Column1 Column2 Column3
0     pqr       b     ccc
1     pqr       b     ccc
2     pqr       b     ccc
3     pqr       b     ccc
4     pqr       b     ccc
5     pqr       b     ccc
</code></pre>

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

<pre><code>print (df1)
  Column1 Column2
0     abc       a
1     pqr       b
2     pqr       b
3     pqr       b

df1 = df1.drop_duplicates()
print (df1)
  Column1 Column2
0     abc       a
1     pqr       b

print (df2)
  ColumnA ColumnB
0     stu     aaa
1     pqr     ccc
2     pqr     ccc

df2 = df2.drop_duplicates()
print (df2)
  ColumnA ColumnB
0     stu     aaa
1     pqr     ccc

print (pd.merge(df1,df2.rename(columns={'ColumnA':'Column1'}))
         .rename(columns={'ColumnB': 'Column3'}))

  Column1 Column2 Column3
0     pqr       b     ccc
</code></pre>

<p>EDIT1:</p>

<p>If <code>DataFrames</code> have more columns, is important specify join columns by parameter <code>on</code>:</p>

<pre><code>print (pd.merge(df1,df2.rename(columns={'ColumnA':'Column1'}), on='Column1')
         .rename(columns={'ColumnB': 'Column3'}))
</code></pre>

<p>EDIT2:</p>

<p>If need remove rows with <code>NaN</code> in selected columns use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dropna.html" rel="nofollow"><code>dropna</code></a>:</p>

<pre><code>df = pd.DataFrame({'A':[1,2,3],
                   'B':[4,5,np.nan],
                   'C':[7,8,np.nan],
                   'D':[np.nan,3,5],
                   'E':[5,3,6],
                   'F':[7,4,3]})

print (df)
   A    B    C    D  E  F
0  1  4.0  7.0  NaN  5  7
1  2  5.0  8.0  3.0  3  4
2  3  NaN  NaN  5.0  6  3

print (df.dropna(subset=['C','B']))
   A    B    C    D  E  F
0  1  4.0  7.0  NaN  5  7
1  2  5.0  8.0  3.0  3  4
</code></pre>

python pandas dataframe merge comparison