VLOOKUP in pandas

To perform Vlookup in pandas first we need to understand n excel we use vlookup to seach data vertically from another sheet.If we have two sheets Lets say sheet1 & sheet 2 and we have to search for data that is not available in sheet2 but Available in sheet1 then we can use VLOOKUP. but in pandas we have to use pandas.merge function to find this.

Suppose we have two data frame df1 and df2 as shown below. we have to search data that is not available in df2 but available in df1.

Vlookup in pandas:

# Create 1st data frame
df1 = pd.DataFrame(
{
"Name": [
"Harris",
" William",
"Bonnell"]
,
"Age": [18, 20, 40],
"Sex": ["male", "female", "female"],
}
)
# Create 2nd Data frame
df2 = pd.DataFrame(
{
"Name": [
"Harris","Harris"]
,
"Age": [18,18],
"Sex": ["male","male"],
}
)
# Merge data using merge function on name column
df_merge = df1.merge(df2.drop_duplicates(), on=['Name'],
how='left', indicator=True)
#It will result all data of  df1 and also rows that are common in both
Output: Name	Age_x	Sex_x	Age_y	Sex_y	_merge
0	Harris	18	male	18.0	male	both
1	William	20	female	NaN	NaN	left_only
2	Bonnell	40	female	NaN	NaN	left_only
# filter using left only indicator in merge columns
df_merge[df_merge['_merge']=='left_only']
# rows that are not present in df2 
Output:
Name	Age_x	Sex_x	Age_y	Sex_y	_merge
1	William	20	female	NaN	NaN	left_only
2	Bonnell	40	female	NaN	NaN	left_only

similarly we look for data the vice versa .