[python]使用 Pandas 处理 Excel 数据：分割与展开列操作

在数据处理的过程中，时常需要对 Excel 表格中的数据进行清洗与转换，下面介绍使用 Python 中的 Pandas 库对 Excel 文件中的数据进行操作，具体包括分割列、展开数据、清除空格以及格式转换等操作。

目标：

1. 读取一个没有表头的 Excel 文件。
2. 分割指定列的数据，并展开成多个行。
3. 清除空格，并按空格将列数据拆分成两列。
4. 删除原始列，保持数据格式整洁。
5. 保存处理后的数据到新的 Excel 文件。

示例代码

import pandas as pd# 读取 Excel 文件，假设没有表头，使用 header=None 参数
df = pd.read_excel('输入数据.xlsx', header=None, engine='openpyxl')# 假设前四列是需要保留的，第五列是需要分割的列
# 对第五列（索引为4）进行分割
df[4] = df[4].str.split(',')# 使用 pandas 的 `explode` 函数展开第五列
df_expanded = df.explode(4, ignore_index=True)# 去除第五列的空格
df_expanded[4] = df_expanded[4].str.strip()# 进一步将第五列按空格分割成两列
df_expanded[['分列1', '分列2']] = df_expanded[4].str.split(' ', n=1, expand=True)# 删除原始的第五列（分割前的列）
df_expanded = df_expanded.drop(columns=[4])# 将前四列设置为文本格式
df_expanded[0] = df_expanded[0].astype(str)
df_expanded[1] = df_expanded[1].astype(str)
df_expanded[2] = df_expanded[2].astype(str)
df_expanded[3] = df_expanded[3].astype(str)# 保存处理后的数据为新的 Excel 文件
df_expanded.to_excel('output2.xlsx', index=False, header=False, engine='openpyxl')print("处理完成，结果已保存为 'output2.xlsx'")

代码解析

1. 读取数据：

   df = pd.read_excel('输入数据.xlsx', header=None, engine='openpyxl')
   

   使用 pd.read_excel() 来读取 Excel 文件。在这个例子中，假设文件没有表头，因此设置 header=None。

2. 分割列：

   df[4] = df[4].str.split(',')
   

   假设第五列（即索引为 4 的列）需要进行分割。我们使用 str.split(',') 方法将该列的每个单元格按逗号分割成多个元素。

3. 展开列：

   df_expanded = df.explode(4, ignore_index=True)
   

   使用 explode() 函数将第五列的列表元素展开为多行，每一行对应列表中的一个元素。

4. 去除空格：

   df_expanded[4] = df_expanded[4].str.strip()
   

   对展开后的列进行清理，去除多余的空格。

5. 进一步分列：

   df_expanded[['分列1', '分列2']] = df_expanded[4].str.split(' ', n=1, expand=True)
   

   将经过清理的第五列按空格分割为两列，并分别命名为“分列1”和“分列2”。这里我们指定 n=1，确保只按第一个空格进行分割。

6. 删除原始列：

   df_expanded = df_expanded.drop(columns=[4])
   

   删除原始的第五列，以确保数据格式的整洁。

7. 转换数据格式：

   df_expanded[0] = df_expanded[0].astype(str)
   df_expanded[1] = df_expanded[1].astype(str)
   df_expanded[2] = df_expanded[2].astype(str)
   df_expanded[3] = df_expanded[3].astype(str)
   

   将前四列的数据转换为字符串格式，以避免格式不一致。

8. 保存结果：

   df_expanded.to_excel('output2.xlsx', index=False, header=False, engine='openpyxl')
   

   最后，将处理后的数据保存为新的 Excel 文件。这里我们指定不保存索引和表头，保持数据格式简洁。

结果

处理完成后，新的 Excel 文件 output2.xlsx 中包含了处理后的数据，其中原来的第五列被分割并展开为新的列

下面提供一个输入数据的示例，并展示经过处理后的输出数据。

输入数据 (输入数据.xlsx)

假设输入数据是一个没有表头的 Excel 文件，内容如下：

列1	列2	列3	列4	列5
A	B	C	D	1, John Doe
E	F	G	H	2, Jane Smith
I	J	K	L	3, Alice Brown
M	N	O	P	4, Bob White

其中，第五列的数据需要被分割和展开。该列中的内容为由逗号和空格分隔的姓名，接下来对其进行分割

处理步骤

1. 将第五列按逗号 , 分割成多个元素。
2. 使用 explode 将分割后的元素展开为多行。
3. 去除空格，并根据空格进一步将名字和姓氏拆分为两个新的列。

输出数据 (output2.xlsx)

处理后的数据如下：

列1	列2	列3	列4	分列1	分列2
A	B	C	D	1
A	B	C	D	John	Doe
E	F	G	H	2
E	F	G	H	Jane	Smith
I	J	K	L	3
I	J	K	L	Alice	Brown
M	N	O	P	4
M	N	O	P	Bob	White

说明

• 第五列被分割并展开：输入数据中的第五列（如 “1, John Doe”）被按逗号分割后，展开为多行。每行包含一个数字和一个名字。
• 进一步分割姓名：展开后的每一行，姓名（如 “John Doe”）被按空格分成了两列：“分列1” 为名字，“分列2” 为姓氏。
• 结果保存：最终的结果被保存为新的 Excel 文件 output2.xlsx。