Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Joining Excel Data from Multiple files using Python Pandas

  • Difficulty Level : Basic
  • Last Updated : 17 Aug, 2020

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 :

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!