使用Pandas和SQL高效重构长格式数据为列表型数组-Python教程-PHP中文网

使用Pandas和SQL高效重构长格式数据为列表型数组

花韻仙語

发布： 2025-10-18 12:27:01

原创

696人浏览过

使用pandas和sql高效重构长格式数据为列表型数组

本文探讨了如何将从SQL数据库中获取的长格式数据（Time, QuantityMeasured, Value）高效重构为Pandas中的宽格式列表型数组。文章对比了多种Python和Pandas处理方法，并提出了一种优化的Pandas策略，即先筛选再透视，以减少处理的数据量。此外，还介绍了将数据重构逻辑下推到SQL数据库执行的方案，这通常能带来显著的性能提升，尤其适用于大规模数据集。

数据重构需求与挑战

在数据分析和处理中，我们经常会遇到从关系型数据库（如MySQL）中提取数据，并需要将其从“长格式”（long format）转换为“宽格式”（wide format）的情况。例如，原始数据可能包含时间戳、测量类型和测量值，结构如下：

        Time    QuantityMeasured       Value
0       t1          A                       7
1       t1          B                       2
2       t1          C                       8
3       t1          D                       9
4       t1          E                       5
...     ...         ...                     ...
18482   tn          A                       5
18483   tn          C                       3
18484   tn          E                       4
18485   tn          B                       5
18486   tn          D                       1

登录后复制

而最终目标是将其转换为独立的Python列表或NumPy数组，每个列表对应一种测量类型，例如：

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]

登录后复制

这种转换在处理来自传感器、日志或金融交易等多种时间序列数据时尤为常见。挑战在于如何高效地完成这一转换，尤其是在数据量较大时。

Pandas数据重构策略

Pandas库提供了强大的数据处理能力，是Python中进行数据重构的首选工具。

1. 基础透视（Pivot）操作

最直观的方法是使用pivot函数将长格式数据转换为宽格式。

import pandas as pd

# 假设df是您的原始DataFrame
# df = pd.read_sql("SELECT Time, QuantityMeasured, Value FROM your_table", your_sql_connection)

# 示例数据
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)

pivot_df = df.pivot(index='Time', columns='QuantityMeasured', values='Value')

# 提取所需列表
time = pivot_df.index.tolist()
list_of_A = pivot_df['A'].tolist()
list_of_B = pivot_df['B'].tolist()
list_of_C = pivot_df['C'].tolist()
list_of_D = pivot_df['D'].tolist()

print("Pivot DataFrame:\n", pivot_df)
print("\nlist_of_A:", list_of_A)

登录后复制

这种方法虽然简洁，但如果QuantityMeasured列包含大量不需要的类别，pivot操作会创建一个非常宽的DataFrame，其中包含许多空值（NaN），这会增加内存消耗和计算时间。

2. 优化：先筛选后透视

为了提高效率，尤其是当只需要部分QuantityMeasured类别时，应在透视之前进行数据筛选。这可以显著减少透视操作的数据量。

# 筛选出我们需要的'A', 'B', 'C', 'D'类别
agg_df = (
    df.query("QuantityMeasured in ['A', 'B', 'C', 'D']")
    .pivot(index='Time', columns='QuantityMeasured', values='Value')
)

# 提取所需列表
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("\nOptimized Pivot DataFrame:\n", agg_df)
print("\nlist_of_A (optimized):", list_of_A)

登录后复制

这种方法通过query函数提前过滤掉不相关的行，使得pivot操作在更小的数据集上进行，从而提高了性能。

即构数智人

即构数智人是由即构科技推出的AI虚拟数字人视频创作平台，支持数字人形象定制、短视频创作、数字人直播等。

查看详情

3. 替代透视方法：set_index与unstack

pivot函数在底层通常会调用set_index和unstack。在某些情况下，直接使用这两个函数可能会略微更快，因为它提供了更细粒度的控制。

agg_df_unstack = (
    df
    .query("QuantityMeasured in ['A', 'B', 'C', 'D']")
    .set_index(['Time', 'QuantityMeasured'])['Value']
    .unstack()
)

# 提取所需列表
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("\nUnstack DataFrame:\n", agg_df_unstack)
print("\nlist_of_A (unstack):", list_of_A_unstack)

登录后复制

这两种Pandas优化方法在处理约1.8万行数据时，可以将处理时间从0.18-0.22秒缩短到0.03秒左右，这是一个显著的提升。然而，要达到数量级（例如0.002秒）的性能提升，在Python/Pandas层面通常很难实现，因为这已经接近了Python数据结构操作的性能极限。

SQL端数据重构：将逻辑下推至数据库

对于大规模数据集或对性能有极高要求的情况，最有效的策略是将数据重构的逻辑下推到数据库层面执行。SQL数据库在处理聚合和透视操作方面通常比Python/Pandas更高效，因为它们是为这类操作而优化的。

通过在SQL查询中使用CASE WHEN语句和GROUP BY子句，可以在数据被拉取到Python之前就完成透视操作。

SELECT
  Time,
  SUM(CASE WHEN QuantityMeasured = 'A' THEN Value ELSE NULL END) AS A,
  SUM(CASE WHEN QuantityMeasured = 'B' THEN Value ELSE NULL END) AS B,
  SUM(CASE WHEN QuantityMeasured = 'C' THEN Value ELSE NULL END) AS C,
  SUM(CASE WHEN QuantityMeasured = 'D' THEN Value ELSE NULL END) AS D
FROM your_table_name  -- 替换为您的实际表名
WHERE QuantityMeasured IN ('A', 'B', 'C', 'D') -- 提前过滤，减少聚合数据量
GROUP BY Time
ORDER BY Time; -- 确保时间顺序一致

登录后复制

说明：

SUM(CASE WHEN ... THEN ... ELSE NULL END)：对于每个Time组，CASE WHEN会检查QuantityMeasured是否匹配特定类别。如果匹配，则取Value；否则，取NULL。SUM函数会忽略NULL值，从而有效地为每个类别生成一个聚合值。如果一个Time只有一个QuantityMeasured对应一个Value，SUM在这里就起到了选择该Value的作用。如果存在多个相同Time和QuantityMeasured的记录，SUM会将它们加起来，这可能需要根据实际业务逻辑调整（例如使用MAX或MIN）。
WHERE QuantityMeasured IN ('A', 'B', 'C', 'D')：在聚合之前进行过滤，只处理我们需要的测量类型，这与Pandas中的query操作类似，能大幅提高SQL查询的效率。
GROUP BY Time：按照时间戳进行分组，为每个时间戳生成一行包含所有所需测量类型的值。
ORDER BY Time：确保结果按时间顺序排列，这对于生成时间序列列表非常重要。

执行这样的SQL查询后，您将直接从数据库获得一个宽格式的结果集，然后可以轻松地将其加载到Pandas DataFrame中，并进一步提取为独立的Python列表。

# 假设conn是您的SQL连接对象
# sql_query = """
# SELECT
#   Time,
#   SUM(CASE WHEN QuantityMeasured = 'A' THEN Value ELSE NULL END) AS A,
#   SUM(CASE WHEN QuantityMeasured = 'B' THEN Value ELSE NULL END) AS B,
#   SUM(CASE WHEN QuantityMeasured = 'C' THEN Value ELSE NULL END) AS C,
#   SUM(CASE WHEN QuantityMeasured = 'D' THEN Value ELSE NULL END) AS D
# FROM your_table_name
# WHERE QuantityMeasured IN ('A', 'B', 'C', 'D')
# GROUP BY Time
# ORDER BY Time;
# """
#
# pivoted_df_from_sql = pd.read_sql(sql_query, conn)
#
# time_sql = pivoted_df_from_sql['Time'].tolist()
# list_of_A_sql = pivoted_df_from_sql['A'].tolist()
# list_of_B_sql = pivoted_df_from_sql['B'].tolist()
# list_of_C_sql = pivoted_df_from_sql['C'].tolist()
# list_of_D_sql = pivoted_df_from_sql['D'].tolist()
#
# print("\nData from SQL Pivot:\n", pivoted_df_from_sql)

登录后复制

这种方法通常能提供最佳的性能，因为它利用了数据库的优化能力，减少了数据传输量和Python端的处理负担。

总结与注意事项

选择合适的工具： 对于小到中等规模的数据集，Pandas的优化透视方法（先筛选后透视）是一个很好的选择。它提供了灵活性和Python生态系统的便利。
性能瓶颈： 在Python/Pandas中，数据重构的性能提升往往存在瓶颈。期望达到数量级的速度提升可能不现实，尤其是在数据量较大时。
数据库优先： 对于大规模数据集或对性能有严格要求的情况，将数据重构逻辑下推到SQL数据库执行通常是最佳实践。SQL查询在处理这类聚合和透视操作时效率更高。
数据完整性： 在进行透视操作时，务必注意原始数据中是否存在重复的Time和QuantityMeasured组合。如果存在，pivot或unstack可能会报错，或者需要pivot_table配合聚合函数来处理。SQL的SUM或MAX等聚合函数可以自然地处理这些情况。
空值处理： 透视操作后，如果某些Time没有对应的QuantityMeasured值，结果中会出现NaN。在转换为列表前，可能需要进行空值填充或删除。