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 :
- Import the Pandas module.
- Read both the files using the
read_excel()
function.
- Combine them using the
merge()
function.
- Use the
to_excel()
function, to create the resultant file.
import pandas
f1 = pandas.read_excel( "registration details.xlsx" )
f2 = pandas.read_excel( "exam results.xlsx" )
f3 = f1[[ "REGISTRATION NO" ,
"STUDENT EMAIL ID " ]].merge(f2[[ "REGISTRATION NO" ,
"Name" , "Marks Obtained" ,
"Percentage" ]],
on = "REGISTRATION NO" ,
how = "left" )
f3.to_excel( "Results.xlsx" , index = False )
|
Output :
Last Updated :
17 Aug, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...