Skip to content
Related Articles

Related Articles

Joining Excel Data from Multiple files using Python Pandas
  • 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 :

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course

My Personal Notes arrow_drop_up
Recommended Articles
Page :