Open In App

Joining Excel Data from Multiple files using Python Pandas

Improve
Improve
Like Article
Like
Save
Share
Report

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 :


Last Updated : 17 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads