Python Pandas重塑复杂Excel数据技巧

本文将详细介绍如何使用Pandas库中的lreshape函数，将包含重复模式列（如id_mXX和mprice对）的宽格式Excel表格数据，高效地重塑为更易于分析的长格式数据。通过具体的代码示例，我们将展示如何处理此类复杂的数据转换需求，并提供实用的技巧与注意事项。

理解数据重塑的需求

在数据分析工作中，我们经常会遇到以“宽格式”存储的数据，其中相同类型的信息被分散到多个列中。例如，一个Excel表格可能包含日期列，以及多组重复的ID和价格列，如id_m00、mprice、id_m01、mprice等，这种模式可能重复数十次。

原始数据示例：

这种格式不利于进行聚合分析或绘制趋势图。我们的目标是将其转换为“长格式”，使每组ID和价格成为独立的一行，从而得到如下结构：

目标数据格式：

虽然Pandas的melt函数常用于数据透视，但在处理这种具有多个相关列组的复杂重塑时，lreshape函数通常更为直接和高效。melt在处理非唯一列名时可能会产生额外的列或大量的空值，而lreshape则能更好地处理成对的列。

Pandas lreshape：高效解决方案

pandas.lreshape函数是专门为处理这种“多列组”重塑问题设计的。它的核心在于通过一个映射字典（reshape_map）来定义如何将旧列分组并转换为新的列。

lreshape函数的核心参数

• data: 需要重塑的DataFrame。
• reshape_map: 一个字典，键是新列的名称，值是一个列表，包含所有将合并到该新列的旧列名称。lreshape会根据这些列表的顺序进行配对。

实战应用：重塑Excel数据

假设我们有一个名为data.xlsx的Excel文件，内容如上述原始数据示例。我们将使用pandas来读取并重塑它。

首先，确保你已经安装了Pandas库： pip install pandas openpyxl

接下来，我们将直接从Excel文件读取数据并进行重塑。需要注意的是，当Pandas从Excel文件中读取具有相同名称的列时（例如，多个mprice列），它会自动为重复的列名添加后缀（如mprice.1, mprice.2等），这正是lreshape能够识别和配对的关键。

import pandas as pd

# 模拟创建示例Excel文件 (实际应用中你将直接读取现有文件)
# 为了演示方便，我们手动创建一个DataFrame
data = {
    'Date': ['01.01.2023', '02.01.2023'],
    'id_m00': ['aa-bb-cc', 'aa-dd-ee'],
    'mprice': ['12,05', '09,55'],
    'id_m01': ['dd-ee-fr', 'ff-gg-gg'],
    'mprice.1': ['8,80', '7,50'] # Pandas读取Excel时会自动重命名重复列
}
# 在实际情况中，如果Excel文件中的mprice列名完全相同，Pandas会默认处理为mprice, mprice.1, mprice.2...
# 如果你的Excel文件真的有多个同名列，Pandas读取时会这样处理。
# 这里我们模拟Pandas读取后的DataFrame结构
df = pd.DataFrame(data)

# 将价格列中的逗号替换为小数点，并转换为数值类型
# 注意：这步是根据示例数据中的逗号作为小数分隔符进行的预处理
for col in df.filter(like='price').columns:
    df[col] = df[col].str.replace(',', '.', regex=False).astype(float)

print("原始DataFrame结构：")
print(df)
print("\n")

# 使用 lreshape 进行数据重塑
# 1. 读取Excel文件（假设文件名为 'file.xlsx'）
# df = pd.read_excel("file.xlsx") # 在实际应用中取消注释此行

# 2. 定义重塑映射
# 'id' 新列将包含所有以 'id_m' 开头的旧列
# 'mprice' 新列将包含所有以 'price' 开头（包括 mprice, mprice.1 等）的旧列
# Pandas的filter(like=...)方法非常适合动态选择这些模式匹配的列
out = pd.lreshape(
    df,
    {"id": df.filter(like="id_m").columns,
     "mprice": df.filter(like="price").columns}
)

print("重塑后的DataFrame：")
print(out)

代码解释：

1. 数据准备： 示例中我们手动创建了一个DataFrame来模拟从Excel读取的数据。实际应用中，你只需要使用df = pd.read_excel("your_file.xlsx")来读取你的Excel文件。请注意，Pandas在读取Excel时会自动处理重复的列名（例如，将第二个mprice列重命名为mprice.1），这正是lreshape能够正常工作的基础。
2. 数据类型转换： 原始数据中的价格使用了逗号作为小数分隔符。在进行数值计算前，我们将其替换为小数点并转换为浮点型。
3. pd.lreshape(df, reshape_map)： 这是核心操作。
   • df: 我们要操作的DataFrame。
   • {"id": df.filter(like="id_m").columns, "mprice": df.filter(like="price").columns}: 这是reshape_map字典。
     • "id": df.filter(like="id_m").columns: 表示新的id列将由所有名称中包含"id_m"的原始列（如id_m00, id_m01等）构成。
     • "mprice": df.filter(like="price").columns: 表示新的mprice列将由所有名称中包含"price"的原始列（如mprice, mprice.1等）构成。
   • lreshape会根据reshape_map中列表的顺序，将这些匹配到的列进行配对，并将其值合并到新的id和mprice列中。例如，id_m00会与第一个mprice配对，id_m01会与mprice.1配对，以此类推。

输出结果

原始DataFrame结构：
         Date    id_m00 mprice    id_m01  mprice.1
0  01.01.2023  aa-bb-cc  12.05  dd-ee-fr      8.80
1  02.01.2023  aa-dd-ee   9.55  ff-gg-gg      7.50

重塑后的DataFrame：
         Date        id  mprice
0  01.01.2023  aa-bb-cc   12.05
1  02.01.2023  aa-dd-ee    9.55
2  01.01.2023  dd-ee-fr    8.80
3  02.01.2023  ff-gg-gg    7.50

可以看到，原始的宽格式数据已被成功转换为我们期望的长格式，每一组id和mprice都成为了独立的一行，并且Date列被正确地复制。

注意事项与最佳实践

1. 列名的一致性： lreshape的强大之处在于它能够根据列名的模式进行自动配对。因此，确保你的原始数据中，需要重塑的列名遵循一致的命名模式（如id_mXX和mprice系列），这样df.filter(like=...)才能准确地选择它们。
2. Pandas读取Excel时的列名处理： 当Excel文件中存在多个同名列时，Pandas的read_excel函数会自动为重复的列名添加数字后缀（例如mprice, mprice.1, mprice.2等）。lreshape正是利用了这一点来进行正确的列配对。如果你手动创建DataFrame或从其他源加载数据，需要确保这些重复列的名称是唯一的，或者手动进行重命名以模拟read_excel的行为。
3. lreshape与melt的选择：
   • 选择lreshape： 当你需要将多组相关联的列（例如，id_X与value_X总是成对出现）重塑为少数几列时，lreshape是理想选择。它能确保这些成对关系在重塑后得到保持。
   • 选择melt： 当你只需要将一系列不相关的“值”列转换为一个“值”列和一个“变量”列时，melt更适用。它通常用于将一个或多个标识符列（id_vars）保持不变，然后将其他所有列（或指定的value_vars）堆叠起来。
4. 数据类型： 在重塑前，确保你的数据类型是正确的。例如，价格列可能需要从字符串转换为数值类型，以避免后续计算错误。

总结

通过使用Pandas的lreshape函数，我们可以高效且优雅地解决将复杂宽格式数据（特别是那些具有重复模式列组的数据）重塑为长格式的问题。这种转换对于后续的数据分析、可视化和建模至关重要，它使得数据结构更加规整，易于处理。掌握lreshape的使用，将大大提升你在Python中处理复杂数据重塑任务的能力。