Python Pandas: Vlookup across multiple dataframes

Question

I have a pandas dataframe that has a specific list of email addresses like:

Email
[email protected]
[email protected]

My master list of email contact info is split up across 5 pandas dataframes (imported from excel). I need to vlookup across these 5 dataframes to pull the first_name & last_names of the contacts in my initial dataframe. Is there a quick way of doing this?

EDIT: The other dataframes would have email addresses as the primary identifier, with separate columns for first_name & last_name like:

Email|first_name|last_name
[email protected]|James|Bond
[email protected]|Jason|Bourne

Thanks for your help!

Yes. Could you post a few example lines from those other DataFrames (as text, not images) and provide some expected output? — Peter Leimbigler
– Peter Leimbigler, Commented Nov 16, 2018 at 16:53
I edited the initial question to add examples from the other dataframes. All the other dataframes are identical to each other - the main issue that they were too big to be stored in a single excel file, so they were split up. I need to vlookup across all of them to get their names. — Abhay
– Abhay, Commented Nov 16, 2018 at 17:01

Peter Leimbigler · Accepted Answer · 2018-11-16 17:17:39Z

1

Once you've imported the Excel file(s) using df = pd.read_excel(path_to_file), you can use pd.merge:

df1 = pd.DataFrame({'Email': ['[email protected]', '[email protected]']})
df2 = pd.DataFrame({'Email': ['[email protected]', '[email protected]'], 
                    'first_name': ['James', 'Jason'], 
                    'last_name': ['Bond', 'Bourne']})

pd.merge(df1, df2, on='Email')
         Email first_name last_name
0  [email protected]      James      Bond
1  [email protected]      Jason    Bourne

answered Nov 16, 2018 at 17:17

Peter Leimbigler

11.1k1 gold badge27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abhay Over a year ago

That worked - I used concat to join the dataframes together and then merge worked. Thanks!

Peter Leimbigler Over a year ago

@Abhay, that's great to hear!

Collectives™ on Stack Overflow

Python Pandas: Vlookup across multiple dataframes

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related