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 have 2 files, registration details.xlsx and exam results.xlsx.

registration details.xlsx

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.

exam results.xlsx

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

You can download these files from these links : registration details.xlsx and exam results.xlsx.

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. :

Algorithm :

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)

Output :