Joining Excel Data from Multiple files using Python Pandas

Let us see how to join the data of two excel files and save the merged  data as a new Excel file.

We are having 7 columns in this file with 14 unique students details. Column names are as follows :

• Admission Date
• Name of Student
• Gender
• DOB
• Student email Id
• Enquiry No.
• Registration No.

We are having 7 columns in this file with 32 unique students' details. Column names are as follows :

• Registration No.
• Name
• No.of questions attempted
• Correct
• Incorrect
• Marks Obtained
• Percentage

Now, let's see the common columns between these two files :

So the common column between the excel files is REGISTRATION NO. So we need to merge these two files in such a way that the new excel file will only hold the required columns i.e. :

1. Import the Pandas module.
2. Read both the files using the read_excel() function.
3. Combine them using the merge() function.
4. Use the to_excel() function, to create the resultant file.

# importing the module
import pandas
# reading the files
f1 = pandas.read_excel("registration details.xlsx")
f2 = pandas.read_excel("exam results.xlsx")
# merging the files
f3 = f1[["REGISTRATION NO", 
         "STUDENT EMAIL ID "]].merge(f2[["REGISTRATION NO", 
                                         "Name", "Marks Obtained", 
                                         "Percentage"]], 
                                     on = "REGISTRATION NO", 
                                     how = "left")
# creating a new file
f3.to_excel("Results.xlsx", index = False)