Performing VLOOKUP in Python is not giving me correct result

user_18623652 0
Viewed 46

I have below data in Python.

dict1 = {"Col1" : (1790212,2232831,1910525,2158628,1751623,2246989,2157180,2041263)}
dict2 = {"Col1" : (2041263,2488561,2305036,2142484,1751623,2246989,2157180,1790212)}
a = pd.DataFrame(dict1)
b = pd.DataFrame(dict2)

After this I perform VLOOKUP on the above 3 data frame using pd.merge() function

c = pd.merge(a,b, on = 'Col1', how = 'left')

OUTPUT of the above code is

|    | COL1  |
|----|-------|
| 0  |1790212|
| 1  |2232831|
| 2  |1910525|
| 3  |2158628|
| 4  |1751623|
| 5  |2246989|
| 6  |2157180|
| 7  |2041263|

The value displayed is incorrect because 2232831,1910525,2158628 are not present in B Data frame it should have displayed NA value instead of this.

Kindly let me know if I have missed anything?

pandas python-3.x python
3 Answers

There are same columns names, so left join return original column, so seems no left join. If rename column all working like need:

c = pd.merge(a,
             b.rename(columns={'Col1':'Col2'}), 
             left_on = 'Col1',
             right_on='Col2', 
             how = 'left')
print (c)
      Col1       Col2
0  1790212  1790212.0
1  2232831        NaN
2  1910525        NaN
3  2158628        NaN
4  1751623  1751623.0
5  2246989  2246989.0
6  2157180  2157180.0
7  2041263  2041263.0

Or is possible use indicator parameter for see how merging working:

c = pd.merge(a,b, on = 'Col1', how = 'left', indicator=True)
print (c)
      Col1     _merge
0  1790212       both
1  2232831  left_only
2  1910525  left_only
3  2158628  left_only
4  1751623       both
5  2246989       both
6  2157180       both
7  2041263       both
user_29010020

I don't think you need DataFrame to achieve that. You can use sets:

dict3 = {'Col1': set(dict1['Col1']) & set(dict2['Col1'])}
user_181607540

you also can get the result with something like this:

a.where(a['Col1'].isin(b['Col1']),np.nan)
'''
        Col1
0  1790212.0
1        NaN
2        NaN
3        NaN
4  1751623.0
5  2246989.0
6  2157180.0
7  2041263.0
user_183445120
Related