Multiple Vlookups on main dataframe from multiple sources?

Question

I have a master data that I wish to do vlookups for additional columnns. Here's what I am aiming to achieve:

Master Data:

Ctry    Product
CN         BTL
VN         HP

Ref table 1:

Ctry    Country
AU      Australia
CN      China
VN      Vietnam

Ref table 2:

ProductID  Product   
BTL        Bottles
HP         Handphone
PRN        Printer

How do I achieve it to combine all into the Master Data as below?

Expected Output:

Ctry    Product  Country    Product 
CN         BTL    China     Bottles
VN         HP     Vietnam   Handphone

My below codes only references 1 table and I'm stuck, how do I go about adding the additional columns to the existing Master Data Sheet?:

import pandas as pd

# IMPORT DATA 

df1 = pd.read_excel("Masterdata.xlsx")
df2 = pd.read_excel("Ref_table_1.xlsx")

Left_join = pd.merge(df1,df2, on = 'Ctry', how ='left') 

Left_join.to_excel("Output.xlsx", index = False)

do another pd.merge for ref table 2? `pd.merge(Left_join, df3, left_on="Product", right_on="Product_id")` — help-ukraine-now, Aug 24 '20 at 08:14

score 0 · Accepted Answer · answered Aug 24 '20 at 08:15

You can use this and eliminate columns not required:

import pandas as pd


df1 = pd.DataFrame({'Ctry': ['CN', 'VN'], 'Product': ['BTL', 'HP']})
df2 = pd.DataFrame({'Ctry': ['CN', 'VN', 'AU'], 'Country': ['Australia', 'China', 'Vietnam']})
df3 = pd.DataFrame({'ProductID': ['BTL', 'HP', 'PRN'], 'Product': ['Bottle', 'HandPhone', 'Printer']})

m1 = df1.merge(df2, how='left')
m2 = m1.merge(df3, how='left', left_on='Product', right_on='ProductID')
print(m2)

Multiple Vlookups on main dataframe from multiple sources?

1 Answers1