Skip to content
Related Articles
Open in App
Not now

Related Articles

Joining Excel Data from Multiple files using Python Pandas

Improve Article
Save Article
  • Difficulty Level : Easy
  • Last Updated : 17 Aug, 2020
Improve Article
Save Article

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
         "STUDENT EMAIL ID "]].merge(f2[["REGISTRATION NO"
                                         "Name", "Marks Obtained"
                                     on = "REGISTRATION NO"
                                     how = "left")
# creating a new file
f3.to_excel("Results.xlsx", index = False)

Output :

My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!