1

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!

Manohar
  • 39
  • 1
  • 9
  • Set `Fruit` as the index in both the dataframes, then use reindex on the second dataframe (from `b.xlsx`) with the index of the first dataframe(from `a.xlsx`)? – Ch3steR Jul 15 '20 at 17:27

1 Answers1

1

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

Caldass_
  • 82
  • 6