In Excel, the VLOOKUP function is used to search for a specific value in a table and return a corresponding value from a different column in the same row.
In Python, you can achieve similar functionality using a combination of the pandas library and the merge function.
Here is an example of how you could use pandas and merge to perform a VLOOKUP-like operation in Python:
import pandas as pd
# Load the data into pandas DataFrames
df1 = pd.read_excel('data1.xlsx')
df2 = pd.read_excel('data2.xlsx')
# Merge the two DataFrames using a common column as the key
result = pd.merge(df1, df2, on='ID')
# Print the resulting DataFrame
print(result)In a pandas merge operation, the how parameter specifies the type of merge that should be performed. The default value is inner, which means that only the rows that have matching keys in both DataFrames will be included in the result.
However, you can also specify left, right, or outer as the value of the how parameter to perform a left, right, or outer merge, respectively.
A left merge, or left join, includes all of the rows from the “left” DataFrame, along with any matching rows from the “right” DataFrame. Rows from the “left” DataFrame that do not have a match in the “right” DataFrame will have null values for the right-side columns.
A right merge, or right join, includes all of the rows from the “right” DataFrame, along with any matching rows from the “left” DataFrame. Rows from the “right” DataFrame that do not have a match in the “left” DataFrame will have null values for the left-side columns.
An outer merge, or full outer join, includes all rows from both DataFrames, regardless of whether there is a match in the other DataFrame. Rows that do not have a match will have null values for the columns from the other DataFrame.
Here is an example of how you could perform a left, right, and outer merge using the pandas merge function:
import pandas as pd
# Load the data into pandas DataFrames
df1 = pd.read_excel('data1.xlsx')
df2 = pd.read_excel('data2.xlsx')
# Perform a left merge
left_merge = pd.merge(df1, df2, on='ID', how='left')
# Perform a right merge
right_merge = pd.merge(df1, df2, on='ID', how='right')
# Perform an outer merge
outer_merge = pd.merge(df1, df2, on='ID', how='outer')In these examples, df1 and df2 are assumed to be pandas DataFrames containing the data you want to merge, and ID is the common column that you want to use as the key for the merge. The merge function will return a new DataFrame that combines the data from df1 and df2 according to the specified how parameter.
Well i will give here direct function f so you can just use as it is for your dataframe and impliment it directly
def vlookup(left_df,right_df,left_key,right_key,right_val):
"""
left_df : path to excel data which requires vlookup values
right_df: path to excel data which is going to provide vlookup values
left_key: the key column in the left dataset
right_key: the key column in the right dataset
right_val: the column in the right dataset whose values need to be moved to the right dataset
"""
left = pd.read_excel(left_df)
left.reset_index(inplace=True)
right = pd.read_excel(right_df)
right = right.loc[:,[str(right_key),str(right_val)]].rename(columns={right_key:left_key})
temp = left.merge(right, how="left", on=left_key)
temp.drop_duplicates(subset=["index"], keep ="first", inplace=True)
return temp.set_index("index")＃for case sensitive charactersdef vlookup(left_df, right_df, left_key, right_key, right_val):
"""
left_df : DataFrame which requires vlookup values
right_df: DataFrame which is going to provide vlookup values
left_key: the key column in the left DataFrame
right_key: the key column in the right DataFrame
right_val: the column in the right DataFrame whose values need to be moved to the left DataFrame
"""
left = left_df.copy()
left.reset_index(inplace=True)
right = right_df.copy()
right[right_key] = right[right_key].astype(str)
right = right.loc[:, [right_key, right_val]].rename(columns={right_key: left_key})
temp = left.merge(right, how="left", on=left_key)
temp.drop_duplicates(subset=["index"], keep="first", inplace=True)
return temp.set_index("index")
def vlookup1(left_df, right_df, left_key, right_key, right_val, result_col):
"""
left_df : DataFrame which requires vlookup values
right_df: DataFrame which is going to provide vlookup values
left_key: the key column in the left DataFrame
right_key: the key column in the right DataFrame
right_val: the column in the right DataFrame whose values need to be moved to the left DataFrame
result_col: the name of the column where the vlookup results should be added
"""
left = left_df.copy()
left.reset_index(inplace=True)
right = right_df.copy()
right[right_key] = right[right_key].astype(str)
right = right.loc[:, [right_key, right_val]].rename(columns={right_key: left_key})
temp = left.merge(right, how="left", on=left_key)
temp.drop_duplicates(subset=["index"], keep="first", inplace=True)
left_df[result_col] = temp.set_index("index")[right_val].values
return left_df
def vlookup2(left_df, right_df, left_key, right_key, left_val, result_col):
"""
left_df : DataFrame which requires vlookup values
right_df: DataFrame which is going to provide vlookup values
left_key: the key column in the left DataFrame
right_key: the key column in the right DataFrame
right_val: the column in the right DataFrame whose values need to be moved to the left DataFrame
result_col: the name of the column where the vlookup results should be added
"""
temp = pd.merge(left_df[[left_key]], right_df[[right_key, left_val]], left_on=left_key, right_on=right_key, how='left')
print(f"Temp DataFrame columns: {temp.columns}") # Debugging print statement
print(temp.head()) # Print the first few rows of temp DataFrame
left_df.loc[:, result_col] = temp[left_val].values
return left_dfIf any question do let me know in comments.
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
#Pandas Dataframe
#Pandas
#Machine Learning
#Artificial Intelligence
#Towards Data Science
Nice blog