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

edit
close

play_arrow

link
brightness_4
code

# 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)

chevron_right


Output :

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.