Pandas与SQL高效数据重构：从长格式到宽格式的性能优化实践

本文深入探讨了如何高效地将sql数据库中拉取的长格式数据重构为pandas中的宽格式，并最终转换为独立的python列表。我们比较了多种客户端（pandas）和服务器端（sql）的优化策略，包括预过滤、`pivot`、`set_index`与`unstack`组合，以及利用sql的`case`语句进行数据透视。文章旨在提供实用的代码示例和性能考量，帮助开发者在处理大规模数据重构时选择最佳方案，实现显著的性能提升。

数据重构需求与挑战

在数据分析和处理中，我们经常会遇到将“长格式”（Long Format）数据转换为“宽格式”（Wide Format）的需求。长格式数据通常包含一个标识符列、一个类别列和一个值列，例如：

而宽格式数据则将类别列的每个唯一值转换为一个独立的列，以便于后续的分析或机器学习模型输入：

list_of_time = ['t1', ..., 'tn']
list_of_A    = [7, ..., 5]
list_of_B    = [2, ..., 5]
list_of_C    = [8, ..., 3]
list_of_D    = [9, ..., 8]

这种转换在处理从关系型数据库（如MySQL）中提取的数据时尤为常见。当数据集规模较大（例如数万行）时，如何高效地完成这一转换，并将其性能优化至秒级甚至毫秒级，成为一个关键挑战。

Pandas客户端优化策略

在Python中使用Pandas进行数据重构是常见的做法。然而，不加优化的方法可能效率低下。以下我们将探讨几种Pandas的优化策略。

1. 预过滤与pivot函数

最常见的重构方法是使用Pandas的pivot函数。然而，如果原始数据包含大量不需要的QuantityMeasured类别，直接对整个DataFrame进行pivot操作会增加不必要的计算量和内存消耗。一个关键的优化是在透视操作之前对数据进行预过滤。

假设我们只关心'A', 'B', 'C', 'D'这几个QuantityMeasured类别。我们可以先使用query方法筛选出相关行，然后再进行pivot。这将显著减少透视操作的数据量，从而提升性能。

import pandas as pd
import numpy as np

# 模拟原始DataFrame数据
data = {
    'Time': ['t1', 't1', 't1', 't1', 't1', 'tn', 'tn', 'tn', 'tn', 'tn'],
    'QuantityMeasured': ['A', 'B', 'C', 'D', 'E', 'A', 'C', 'E', 'B', 'D'],
    'Value': [7, 2, 8, 9, 5, 5, 3, 4, 5, 1]
}
df = pd.DataFrame(data)

# 目标类别
target_quantities = ['A', 'B', 'C', 'D']

# 优化后的Pandas重构
# 1. 预过滤：只保留目标类别的数据
# 2. pivot：将QuantityMeasured转换为列
agg_df = (
    df.query("QuantityMeasured in @target_quantities")
    .pivot(index='Time', columns='QuantityMeasured', values='Value')
)

# 将结果转换为所需的列表格式
list_of_time = agg_df.index.tolist()
list_of_A = agg_df['A'].tolist()
list_of_B = agg_df['B'].tolist()
list_of_C = agg_df['C'].tolist()
list_of_D = agg_df['D'].tolist()

print("优化后的Pandas结果:")
print("Time:", list_of_time)
print("A:", list_of_A)
print("B:", list_of_B)
print("C:", list_of_C)
print("D:", list_of_D)

注意事项：

• pivot函数要求index和columns的组合是唯一的。如果存在重复，需要使用pivot_table并指定聚合函数。
• query方法在处理大型DataFrame时非常高效，因为它使用NumExpr库进行优化。

2. set_index与unstack组合

pivot函数在底层实际上是调用了set_index和unstack的组合。在某些特定情况下，直接使用set_index和unstack可能会带来微小的性能提升，尤其是在处理多级索引时。

比格设计

比格设计是135编辑器旗下一款一站式、多场景、智能化的在线图片编辑器

124

查看详情

# 使用set_index和unstack进行重构
agg_df_unstack = (
    df
    .query("QuantityMeasured in @target_quantities")
    .set_index(['Time', 'QuantityMeasured'])['Value']
    .unstack()
)

# 结果与pivot相同
list_of_time_unstack = agg_df_unstack.index.tolist()
list_of_A_unstack = agg_df_unstack['A'].tolist()
list_of_B_unstack = agg_df_unstack['B'].tolist()
list_of_C_unstack = agg_df_unstack['C'].tolist()
list_of_D_unstack = agg_df_unstack['D'].tolist()

print("\n使用set_index和unstack的结果:")
print("Time:", list_of_time_unstack)
print("A:", list_of_A_unstack)
print("B:", list_of_B_unstack)
print("C:", list_of_C_unstack)
print("D:", list_of_D_unstack)

这两种Pandas方法在经过预过滤后，通常能达到客户端处理的性能极限。对于1.8万行左右的数据集，期望将其处理时间从0.03秒再缩短一个数量级（到0.003秒）在Python中可能不切实际，因为Pandas操作本身也存在一定的开销。

SQL服务器端优化策略

如果性能要求极高，并且你拥有对SQL查询的控制权，那么将数据重构的任务下推到数据库服务器端通常是最高效的解决方案。数据库引擎在处理聚合和透视操作方面通常比客户端脚本更优化，并且可以减少传输到客户端的数据量。

在SQL中，我们可以使用CASE语句配合聚合函数（如SUM或MAX）来实现数据透视。

SELECT
  Time,
  SUM(CASE WHEN QuantityMeasured = 'A' THEN Value ELSE 0 END) AS A,
  SUM(CASE WHEN QuantityMeasured = 'B' THEN Value ELSE 0 END) AS B,
  SUM(CASE WHEN QuantityMeasured = 'C' THEN Value ELSE 0 END) AS C,
  SUM(CASE WHEN QuantityMeasured = 'D' THEN Value ELSE 0 END) AS D
FROM your_table_name  -- 替换为你的实际表名
WHERE QuantityMeasured IN ('A', 'B', 'C', 'D') -- 预过滤，进一步优化
GROUP BY Time
ORDER BY Time; -- 可选，确保时间顺序

解析：

• WHERE QuantityMeasured IN ('A', 'B', 'C', 'D')：这是关键的预过滤步骤，它确保数据库只处理我们感兴趣的类别，大大减少了后续聚合的数据量。
• SUM(CASE WHEN QuantityMeasured = 'A' THEN Value ELSE 0 END) AS A：对于每个Time组，如果QuantityMeasured是'A'，则取其Value；否则取0。然后对这些值求和。由于每个Time和QuantityMeasured组合只有一个Value，SUM在这里实际上起到了选择相应值的作用。如果存在多个相同Time和QuantityMeasured的记录，SUM会进行实际的聚合。如果需要其他聚合方式（如取第一个值），可以考虑使用窗口函数或更复杂的逻辑。
• GROUP BY Time：将结果按Time列进行分组，确保每个Time只对应一行数据。

通过这种方式，数据库直接返回一个已经透视好的宽格式表，Pandas只需读取这个结果，然后将其转换为列表即可，极大地减少了Python端的计算负担。

总结与建议

在高效地从SQL表重构数据到Pandas并转换为列表时，以下是关键的优化点：

1. 预过滤： 无论是客户端Pandas操作还是服务器端SQL查询，始终优先过滤掉不需要的数据。这能显著减少处理的数据量。
2. 选择合适的Pandas方法： df.query(...).pivot(...) 或 df.query(...).set_index(...).unstack() 是Pandas中执行此类重构的高效方式。它们通常能达到客户端处理的性能上限。
3. 考虑服务器端处理： 对于性能要求极高或数据量特别大的场景，将数据透视逻辑下推到SQL数据库是最佳实践。利用SQL的CASE语句和GROUP BY可以实现高效的宽格式转换。这不仅利用了数据库引擎的优化能力，也减少了网络传输的数据量。
4. 性能预期： 对于数万行的数据集，在Python中期望将处理时间缩短到毫秒级（如0.002秒）可能不切实际。通常，数十毫秒到数百毫秒是较为合理的预期范围。如果需要更高的性能，SQL层面的优化通常能带来更大的突破。

通过综合运用这些策略，你可以根据具体的数据量、性能要求和对SQL的控制能力，选择最适合的方案来高效完成数据重构任务。

以上就是Pandas与SQL高效数据重构：从长格式到宽格式的性能优化实践的详细内容，更多请关注php中文网其它相关文章！