Excel Python Chatgpt三合一: 合并数据

与数据拆分相反，常常需要将多个文件，或多个DataFrame的数据进行合并。合并数据，可以是简单拼接数据，也可以是根据关联变量进行合并。本节介绍后者。[大谦Excel，dqexcel点com]

合并工作表

【问题描述】

将一个工作簿中不同工作表的数据根据指定的关联变量合并到一个Excel文件。

【示例4-4】

本例使用的Excel文件的完整路径为“D:/Samples/ch04/02 数据合并/合并工作表/学生成绩.xlsx”。该文件打开后如图4-3所示，工作簿中有3个工作表，分别为同一批同学的语文、数学和英语成绩。要求将3个工作表的数据合并后保存到一个新的Excel文件。

图4-3 合并不同工作表的数据

ChatGPT提示词模板

新建ChatGPT会话，在提问文本框中输入下面的提示词：

prompt

你是pandas专家，文件路径为：D:/Samples/ch04/02 数据合并/合并工作表/学生成绩.xlsx。该Excel文件中有3个工作表。用pandas导入3个工作表的数据，第1列均为索引列，引擎为"openpyxl"。以“姓名”为关联变量，用merge函数外连接合并3个工作表的数据，输出合并数据并保存到Excel文件，保存路径为“D:/Samples/ch04/02 数据合并/合并工作表”。新文件的名称为“学生成绩_合并.xlsx”。

2. ChatGPT提示词模板说明

这里是根据共有的关联变量，即“姓名”列进行合并，明确指定“姓名”为关联变量，并指定使用pandas的merge函数进行合并。指定合并后新文件的保存路径和命名方式。

3. 代码

根据提示词得到类似下面的代码：

code.python

import pandas as pd
# 读取3个工作表的数据
df1 = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作表/学生成绩.xlsx', sheet_name='Sheet1', index_col=0, engine='openpyxl')
df2 = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作表/学生成绩.xlsx', sheet_name='Sheet2', index_col=0, engine='openpyxl')
df3 = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作表/学生成绩.xlsx', sheet_name='Sheet3', index_col=0, engine='openpyxl')
# 合并3个工作表的数据
merged_df = pd.merge(df1, df2, on='姓名', how='outer')
merged_df = pd.merge(merged_df, df3, on='姓名', how='outer')
#输出合并结果
print(merged_df)
# 将合并后的数据保存到Excel文件
writer = pd.ExcelWriter('D:/Samples/ch04/02 数据合并/合并工作表/学生成绩_合并.xlsx', engine='openpyxl')
merged_df.to_excel(writer, index=True)
writer.save()

4. 使用代码

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存到D:/Samples/1.py。运行脚本，实现数据的合并，在IDLE Shell窗口输出合并后的结果。

code.python

>>> == RESTART: D:/Samples/1.py =
       语文     数学     英语
姓名
王东   16.0   27.0   34.0
徐慧   85.0   54.0    NaN
王慧琴  99.0   73.0    NaN
章思思  95.0    NaN   62.0
阮锦绣  92.0    NaN   92.0
周洪宇  93.0   92.0  113.0
谢思明  98.0   95.0  117.0
程成   98.0   95.0  114.0
王洁    NaN  102.0  136.0
张丽君   NaN  104.0  105.0

可见，合并方式为”outer”时合并的结果是各数据集的并集。

可在原始数据文件的相同路径下找到新建的Excel文件。

【知识点扩展】

本例使用pandas的merge函数合并数据。该函数一次只能合并2个DataFrame，所以代码中连续用了两次merge函数。merge函数的主要参数的意义如表4-1所示。

表4-1 merge函数的主要参数

参数	说明
left	DataFrame数据1
right	DataFrame数据2
how	`数据合并的方式，有inner(内连接)、outer(外连接)、left(左连接)和right(右连接)4种，默认时为inner`
on	指定用于连接的列索引标签。如果没指定且其他参数也没有指定，用两个DataFrame的列索引标签交集作为连接键
left_on	指定左侧DataFrame用作连接键的列索引标签
right_on	指定右侧DataFrame用作连接键的列索引标签
left_index	值为True时指定左侧DataFrame的行索引作为连接键，默认值为False
right_index	值为True时指定右侧DataFrame的行索引作为连接键，默认值为False
sort	默认值为True，对合并后的数据进行排序；设置为False，取消排序
suffixes	两个DataFrame中如果存在除连接键以外的同名索引标签，合并后指定不同后缀进行区分，默认时为("_x","_y")

merge函数的使用，有几个关键内容要把握，即连接键的设置、连接键的数量关系和连接方式的设置。

连接键的设置

merge函数提供了类似于关系数据库连接的操作，可以根据一个或多个键将两个DataFrame数据连接起来。当进行连接的两个DataFrame有相同的列索引标签时，使用merge方法的on参数设置连接键。

如果用作连接键的索引列具有不同的标签，比如一个是“准考号”，另一个是“准考证”，它们表达的是一个意思。此时就不能用on参数进行设置，而是用left_on,参数和right_on参数分别设置两个DataFrame的连接键，即left_on= "准考号", right_on= "准考证"。

设置left_index参数或right_index参数的值为True时，指定左侧或右侧DataFrame的行索引作为连接键。适用于一个DataFrame的行索引与另一个DataFrame的索引列可用于连接的情况。

连接键的数量关系

根据连接键索引列中值的重复情况，可以有1对1、1对多、多对1和多对多等几种数量关系。本例中，连接的两个DataFrame中连接键“姓名”列中的值都是唯一的，没有出现重复的情况，这种情况称为1对1的数量关系。如果至少一个DataFrame中的值有重复，就会出现1对多、多对1或多对多的情况，这里不展开介绍。

连接方式

用how参数设置连接键连接的方式。有内连接(inner)、外连接(outer)、左连接(left)和右连接(right)等4种连接方式，它们对应的集合关系如图4-4所示。

图4-4 各连接方式对应的集合关系

本例中设置how参数的值为”outer”，得到的是各数据集的并集。

设置how参数的值为”inner”时，进行内连接，得到的是各数据集的交集。将本例代码中how参数的值改为”inner”，即

code.python

merged_df = pd.merge(df1, df2, on='姓名', how='inner')
merged_df = pd.merge(merged_df, df3, on='姓名', how='inner')

运行代码后输出的合并输入如下所示。

code.python

>>> == RESTART: D:/Samples/1.py =
     语文  数学   英语
姓名
王东   16  27   34
周洪宇  93  92  113
谢思明  98  95  117
程成   98  95  114

可见，得到的合并结果是各数据集的交集。

设置how参数的值为”left”时，进行左连接。将本例代码中how参数的值改为”left”，即

code.python

merged_df = pd.merge(df1, df2, on='姓名', how='left')
merged_df = pd.merge(merged_df, df3, on='姓名', how='left')

运行代码后输出的合并输入如下所示。

code.python

>>> == RESTART: D:/Samples/1.py =
     语文    数学     英语
姓名
王东   16  27.0   34.0
徐慧   85  54.0    NaN
王慧琴  99  73.0    NaN
章思思  95   NaN   62.0
阮锦绣  92   NaN   92.0
周洪宇  93  92.0  113.0
谢思明  98  95.0  117.0
程成   98  95.0  114.0

用左连接方式合并两个DataFrame时，合并结果是保持左侧的DataFrame不变，再并上两个DataFrame的交集。

设置how参数的值为”right”时，进行右连接。将本例代码中how参数的值改为”right”，即

code.python

merged_df = pd.merge(df1, df2, on='姓名', how='right')
merged_df = pd.merge(merged_df, df3, on='姓名', how='right')

运行代码后输出的合并输入如下所示。

code.python

>>> == RESTART: D:/Samples/1.py =
       语文     数学   英语
姓名
王东   16.0   27.0   34
章思思   NaN    NaN   62
阮锦绣   NaN    NaN   92
周洪宇  93.0   92.0  113
谢思明  98.0   95.0  117
程成   98.0   95.0  114
王洁    NaN  102.0  136
张丽君   NaN  104.0  105

用左连接方式合并两个DataFrame时，合并结果是保持右侧的DataFrame不变，再并上两个DataFrame的交集。

有非键列标签重复的情况

进行合并的两个DataFrame如果都有非键列标签，比如“身高”，则合并以后，为了进行区分，会自动给左侧DataFrame中的“身高”添加了后缀"_x"，给右侧DataFrame中的“身高”添加了后缀"_y"。这是默认设置。如果需要自定义后缀，可以用suffixes参数进行设置。如下面的语句设置用“姓名”连接时，如果有非键列标签，则左侧的标签添加后缀”_l”，右侧的标签添加后缀”_r”。

code.python

df3=pd.merge(df1,df2,on= "姓名",suffixes=("_l","_r"))

合并工作簿

【问题描述】

与合并不同工作表中的数据类似，只是现在数据是保存在不同Excel工作簿文件中的。

【示例4-5】

本例使用的Excel文件的完整路径为“D:/Samples/ch04/02 数据合并/合并工作簿/“，有3个Excel文件：学生成绩_语文.xlsx、学生成绩_数学.xlsx和学生成绩_英语.xlsx，如图4-5所示。要求以“姓名”为关联变量，用merge函数合并3个工作簿的数据并保存到Excel文件。

图4-5 给定的3个Excel工作簿文件

ChatGPT提示词模板

新建ChatGPT会话，在提问文本框中输入下面的提示词：

prompt

你是pandas专家，文件路径为：D:/Samples/ch04/02 数据合并/合并工作簿/，有3个Excel文件：学生成绩_语文.xlsx、学生成绩_数学.xlsx和学生成绩_英语.xlsx。用pandas导入3个工作表的数据，第1列均为索引列，引擎为"openpyxl"。以“姓名”为关联变量，用merge函数外连接合并3个工作簿的数据并保存到Excel文件，保存路径为“D:/Samples/ch04/02 数据合并/合并工作簿”。新文件的名称为“学生成绩_合并.xlsx”。

2. ChatGPT提示词模板说明

指定关联变量，指定用pandas的merge函数进行合并。指定合并后新文件的保存路径和命名方式。

3. 代码

根据提示词得到类似下面的代码：

code.python

import pandas as pd
# 读取3个Excel文件
df_chinese = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作簿/学生成绩_语文.xlsx', index_col=0, engine='openpyxl')
df_math = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作簿/学生成绩_数学.xlsx', index_col=0, engine='openpyxl')
df_english = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作簿/学生成绩_英语.xlsx', index_col=0, engine='openpyxl')
# 合并3个DataFrame，以“姓名”为关联变量
df_merge = pd.merge(df_chinese, df_math, on='姓名', how='outer')
df_merge = pd.merge(df_merge, df_english, on='姓名', how='outer')
# 将新表格保存到Excel文件中
writer = pd.ExcelWriter('D:/Samples/ch04/02 数据合并/合并工作簿/学生成绩_合并.xlsx',
                        engine='openpyxl')
df_merge.to_excel(writer, sheet_name='Sheet1')
writer.save()

4. 使用代码

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存到D:/Samples/1.py。运行脚本，实现数据的合并。可在原始数据文件的相同路径下找到新建的Excel文件。

拼接数据

4.2节使用pandas的merge函数合并了3个DataFrame，但是merge函数一次只能合并2个DataFrame，所以合并3个DataFrame需要将merge函数使用两次。本节使用pandas的concat函数进行合并，与merge函数不同的是，使用concat函数可以一次合并多个DataFrame。

【问题描述】

使用pandas的concat函数一次拼接多个DataFrame。

【示例4-6】

本例使用与示例4-4相同的数据。用concat函数水平拼接3个工作表的数据并保存到Excel文件，

ChatGPT提示词模板

新建ChatGPT会话，在提问文本框中输入下面的提示词：

prompt

你是pandas专家，文件路径为：D:/Samples/ch04/02 数据合并/拼接数据/学生成绩.xlsx。该Excel文件中有3个工作表。用pandas导入3个工作表的数据，第1列均为索引列，引擎为"openpyxl"。用concat函数水平拼接3个工作表的数据，输出数据并保存到Excel文件，保存路径为“D:/Samples/ch04/02 数据合并/拼接数据”。新文件的名称为“学生成绩_拼接.xlsx”。

2. ChatGPT提示词模板说明

明确指定使用concat函数进行拼接。指定合并后的文件所在的路径和名称。

3. 代码

根据提示词得到类似下面的代码：

code.python

import pandas as pd
# 读取三个工作表的数据
df1 = pd.read_excel('D:/Samples/ch04/02 数据合并/拼接数据/学生成绩.xlsx', sheet_name='Sheet1', index_col=0, engine='openpyxl')
df2 = pd.read_excel('D:/Samples/ch04/02 数据合并/拼接数据/学生成绩.xlsx', sheet_name='Sheet2', index_col=0, engine='openpyxl')
df3 = pd.read_excel('D:/Samples/ch04/02 数据合并/拼接数据/学生成绩.xlsx', sheet_name='Sheet3', index_col=0, engine='openpyxl')
# 使用concat函数水平拼接三个工作表的数据
result = pd.concat([df1, df2, df3], axis=1)
# 输出合并后的数据
print(result)
# 将结果保存到新的Excel文件中
result.to_excel('D:/Samples/ch04/02 数据合并/拼接数据/学生成绩_拼接.xlsx', index=True, engine='openpyxl')

4. 使用代码

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存到D:/Samples/1.py。运行脚本，实现数据的合并，在IDLE Shell窗口输出合并后的结果。

code.python

>>> == RESTART: D:/Samples/1.py =
      语文   数学   英语
姓名
王东    16   27   34
徐慧    85   54   92
王慧琴   99   73  118
章思思   95   83   62
阮锦绣   92   91   92
周洪宇   93   92  113
谢思明   98   95  117
程成    98   95  114
王洁   102  102  136
张丽君  107  104  105

可在原始数据文件的相同路径下找到新建的Excel文件。

【知识点扩展】

使用pandas的concat函数可以一次拼接多个DataFrame，该函数的参数的用法如表4-2所示。

表4-2 concat函数的参数

参数	说明
objs	指定进行拼接的对象集合，可以是Series, DataFrame等组成的列表等
axis	指定拼接的方向，默认值为0，垂向拼接，值为1时水平方向拼接
join	指定拼接的方法，值为outer或inner，相当于merge方法中how参数设置的外连接和内连接
join_axes	指定保留的轴，作用相当于merge方法中how参数设置的左连接和右连接
ignore_index	拼接后忽略原来的索引编号，重新编号
keys	添加一个键，指定数据来源

连接数据

【问题描述】

使用pandas的DataFrame对象的join方法一次连接多个DataFrame。

【示例4-7】

本例使用与示例4-4相同的数据。用DataFrame对象的join方法水平拼接3个工作表的数据并保存到Excel文件，

ChatGPT提示词模板

新建ChatGPT会话，在提问文本框中输入下面的提示词：

prompt

你是pandas专家，文件路径为：D:/Samples/ch04/02 数据合并/连接数据/学生成绩.xlsx。该Excel文件中有3个工作表。用pandas导入3个工作表的数据，第1列均为索引列，引擎为"openpyxl"。用join函数水平拼接3个工作表的数据，输出数据并保存到Excel文件，保存路径为“D:/Samples/ch04/02 数据合并/连接数据”。新文件的名称为“学生成绩_连接.xlsx”。

2. ChatGPT提示词模板说明

明确指定使用DataFrame对象的join方法进行连接。指定合并后的文件所在的路径和名称。

3. 代码

根据提示词得到类似下面的代码：

code.python

import pandas as pd
# 读取3个工作表数据
sheet1 = pd.read_excel(r'D:/Samples/ch04/02 数据合并/连接数据/学生成绩.xlsx', sheet_name='Sheet1', index_col=0, engine="openpyxl")
sheet2 = pd.read_excel(r'D:/Samples/ch04/02 数据合并/连接数据/学生成绩.xlsx', sheet_name='Sheet2', index_col=0, engine="openpyxl")
sheet3 = pd.read_excel(r'D:/Samples/ch04/02 数据合并/连接数据/学生成绩.xlsx', sheet_name='Sheet3', index_col=0, engine="openpyxl")
# join水平拼接3个工作表数据
merged_data = sheet1.join([sheet2, sheet3])
#输出合并数据
print(merged_data)
# 保存到Excel文件
merged_data.to_excel(r'D:/Samples/ch04/02 数据合并/连接数据/学生成绩_连接.xlsx')

4. 使用代码

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存到D:/Samples/1.py。运行脚本，实现数据的合并，在IDLE Shell窗口输出合并后的结果。

code.python

>>> == RESTART: D:/Samples/1.py =
      语文   数学   英语
姓名
王东    16   27   34
徐慧    85   54   92
王慧琴   99   73  118
章思思   95   83   62
阮锦绣   92   91   92
周洪宇   93   92  113
谢思明   98   95  117
程成    98   95  114
王洁   102  102  136
张丽君  107  104  105

可在原始数据文件的相同路径下找到新建的Excel文件。

【知识点扩展】

用DataFrame对象的join方法实现两个或多个DataFrame数据的连接。该方法的语法格式为：

code.python

df.join(other, on=None, how=’left’, lsuffix=’’, rsuffix=’’, sort=False)

方法各参数的含义与merge方法的基本相同。其中df为DataFrame对象，other为另外一个或多个DataFrame对象。join方法可以看作是merge方法的简化版本。

连接两个DataFrame数据时，可以使用on参数指定连接键索引列；连接多个DataFrame数据时，只能将行索引作为连接键。

追加数据

【问题描述】

在一个DataFrame的右侧或底部追加另外一个DataFrame。

【示例4-8】

本例使用的Excel文件的完整路径为“D:/Samples/ch04/02 数据合并/追加数据/学生成绩.xlsx”。该文件打开后如图4-6所示，用DataFrame对象的append方法垂直拼接2个工作表的数据并保存到Excel文件。

图4-6 学生考试成绩

ChatGPT提示词模板

新建ChatGPT会话，在提问文本框中输入下面的提示词：

prompt

你是pandas专家，文件路径为：D:/Samples/ch04/02 数据合并/追加数据/学生成绩.xlsx。该Excel文件中有2个工作表。用pandas导入2个工作表的数据，第1行均为索引列，引擎为"openpyxl"。用DataFrame对象的append方法垂直拼接2个工作表的数据，输出数据并保存到Excel文件，保存路径为“D:/Samples/ch04/02 数据合并/追加数据”。新文件的名称为“学生成绩_追加.xlsx”。

2. ChatGPT提示词模板说明

指定使用DataFrame对象的append方法实现追加。

3. 代码

根据提示词得到类似下面的代码：

code.python

import pandas as pd
# 读取第1个工作表数据
df1 = pd.read_excel('D:/Samples/ch04/02 数据合并/追加数据/学生成绩.xlsx', sheet_name='Sheet1', header=0, engine='openpyxl', index_col=0)
# 读取第2个工作表数据
df2 = pd.read_excel('D:/Samples/ch04/02 数据合并/追加数据/学生成绩.xlsx', sheet_name='Sheet2', header=0, engine='openpyxl', index_col=0)
# 将两个工作表垂直拼接
df = df1.append(df2)
print(df)
# 将结果保存到Excel文件中
save_path = 'D:/Samples/ch04/02 数据合并/追加数据/学生成绩_追加.xlsx'
df.to_excel(save_path, encoding='utf-8', index_label='序号')

4. 使用代码

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存到D:/Samples/1.py。运行脚本，实现数据的合并，在IDLE Shell窗口输出合并后的结果。

code.python

>>> == RESTART: D:/Samples/1.py =
      语文   数学   英语
姓名
王东    16   27   34
徐慧    85   54   92
王慧琴   99   73  118
章思思   95   83   62
阮锦绣   92   91   92
周洪宇   93   92  113
谢思明   98   95  117
程成    98   95  114
王洁   102  102  136
张丽君  107  104  105

可在原始数据文件的相同路径下找到新建的Excel文件。

【知识点扩展】

使用DataFrame对象的append方法给已有DataFrame数据在末行追加数据行(Series)或数据区域(DataFrame)。该方法的语法格式为：

code.python

df3=df1.append(df2)

其中df1是已有DataFrame数据，df2是追加的Series或DataFrame数据，追加后得到新的DataFrame数据df3。[大谦Excel，dqexcel点com]

本页目录

合并工作表

【问题描述】

【示例4-4】

【知识点扩展】

合并工作簿

【问题描述】

【示例4-5】

拼接数据

【问题描述】

【示例4-6】

【知识点扩展】

连接数据

【问题描述】

【示例4-7】

【知识点扩展】

追加数据

【问题描述】

【示例4-8】

【知识点扩展】