Merge Two Excel Files with a Single Line 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.