本文探讨了如何将从SQL数据库中获取的长格式数据（Time, QuantityMeasured, Value）高效重构为Pandas中的宽格式列表型数组。文章对比了多种Python和Pandas处理方法，并提出了一种优化的Pandas策略，即先筛选再透视，以减少处理的数据量。此外，还介绍了将数据重构逻辑下推到SQL数据库执行的方案，这通常能带来显著的性能提升，尤其适用于大规模数据集。
数据重构需求与挑战
在数据分析和处理中，我们经常会遇到从关系型数据库（如MySQL）中提取数据，并需要将其从“长格式”（long format）转换为“宽格式”（wide format）的情况。例如，原始数据可能包含时间戳、测量类型和测量值，结构如下：
Time QuantityMeasured Value0 t1 A 71 t1 B 22 t1 C 83 t1 D 94 t1 E 5... ... ... ...18482 tn A 518483 tn C 318484 tn E 418485 tn B 518486 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操作在更小的数据集上进行，从而提高了性能。
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 DFROM your_table_name -- 替换为您的实际表名WHERE QuantityMeasured IN ('A', 'B', 'C', 'D') -- 提前过滤，减少聚合数据量GROUP BY TimeORDER 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。在转换为列表前，可能需要进行空值填充或删除。
通过理解这些不同的策略及其优缺点，您可以根据具体的项目需求和数据规模，选择最适合的高效数据重构方法。
以上就是使用Pandas和SQL高效重构长格式数据为列表型数组的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1377423.html
微信扫一扫 
支付宝扫一扫 
