CODEWITHSUNDEEP
pythonexcelpandasdataframemerge
Manohar
Manohar

Reputation: 49

Merge two data Frame if data from one column is present in another column

I have two excel files.

Excel 1 - a.xlsx containing
SL NO       Fruit   Colour  Quantity
1           Apple       
2           Orange      
3           Banana      
4           Pineapple       
5           pears       
6           Grape       
7           Mango

and Excel 2 - b.xlsx containing

SL NO       Fruit       Colour      Quantity
1           Apple       RED         2
2           Orange      Orange      4
3           Pineapple   Brown       6
4           Grape       Purple      8

I want a new excel file which is complete like this

SL NO       Fruit       Colour      Quantity
1           Apple       RED         2
2           Orange      Orange      4
3           Banana      
4           Pineapple   Brown       6
5           pears       
6           Grape       Purple      8
7           Mango

How do I perform the same? The matching should be done based on the fruit name. I am able to load it into different data frames, but how do I operate after that and create a new excel with the merged output? I am new to python and I am learning, so any help will be appreciated. Thanks in advance!

Upvotes: 2

Views: 55

Answers (1)

Caldass_
Caldass_

Reputation: 82

First you need to install the pandas library. If you don't have it yet, use:

pip install pandas

Now, import pandas and you can use pd.merge function:

import pandas as pd

new_excel = pd.merge(excel_1, excel_2, on = ['Fruit'], how = 'left')

This is going to be similar to a VLOOKUP function in excel if that helps you understand. If it's of your interest, check out this post to learn more: Pandas Merging 101

Upvotes: 1

Related Questions