Merge Two Excel Files with a Single Line of Python Code
This tutorial shows how to read two Excel worksheets with pandas, merge them on matching name columns using a single merge command, and save the combined data back to a new Excel file, all with just a few lines of Python code.
1 Read the tables
Import pandas and read each Excel file in three lines, then display the first five rows.
import pandas as pd
data1 = pd.read_excel('表格1.xlsx')
data1.head()Similarly read the second file:
import pandas as pd
data2 = pd.read_excel('表格2.xlsx')
data2.head()2 Merge the tables
The two sheets contain questionnaire data for the same participants but with different column names for the name field ( name in the first sheet and 姓名 in the second). Use pd.merge to align rows based on these columns.
dataMerge = pd.merge(data1, data2, left_on='name', right_on='学员姓名', how='left')The how='left' keeps all rows from the first sheet; you can change it to right, outer, or inner as needed. If the name columns have the same label, a simpler call works:
dataMerge = pd.merge(data1, data2, on='name')3 Save the merged table
Write the result to a new Excel file with a single command:
dataMerge.to_excel('合并表格.xlsx')Running this creates an Excel file that can be opened for further inspection.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
