本文探讨了如何高效地将sql数据库中的长格式数据重构为python中的宽格式列表。针对常见的数据转置需求，文章详细介绍了两种主要策略：首先，通过pandas的`query`结合`pivot`或`set_index`/`unstack`函数进行优化，实现python层面的高效处理；其次，提出将数据转置逻辑下推到sql数据库层面执行，利用sql的聚合函数和条件语句实现更快的性能。文章提供了具体的代码示例和性能考量，旨在帮助开发者根据实际场景选择最适合的数据重构方案。
在数据分析和处理中，我们经常会遇到需要将“长格式”（Long Format）数据转换为“宽格式”（Wide Format）的需求。例如，从关系型数据库中提取的数据通常是长格式，其中每个测量值都对应一行，包含时间戳、测量类型和值。然而，在Python中进行进一步的分析时，我们可能需要将特定测量类型的值组织成独立的列表或数组。本文将深入探讨如何使用Pandas和SQL两种方法，高效地完成这一数据重构任务。
原始数据结构与目标格式
假设我们从SQL数据库中获取的DataFrame结构如下：
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列表形式，仅关注’A’, ‘B’, ‘C’, ‘D’这四种测量类型：
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优化策略
在Python中，Pandas库提供了强大的数据处理能力。对于数据转置，pivot函数是常用的工具。然而，简单的pivot可能不是最优解，特别是当原始数据包含大量不需要的QuantityMeasured类别时。
立即学习“Python免费学习笔记（深入）”；
1. 预过滤结合 pivot
当只需要部分QuantityMeasured类别时，在执行转置操作之前先过滤掉不需要的数据，可以显著减少后续操作的数据量，从而提升性能。
import pandas as pdimport numpy as np# 假设 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)# 优化的Pandas转置方法：先过滤，再转置agg_df = ( df.query("QuantityMeasured in ['A', 'B', 'C', 'D']") # 过滤只保留所需类别 .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("Time:", list_of_time)print("A:", list_of_A)print("B:", list_of_B)print("C:", list_of_C)print("D:", list_of_D)
注意事项：
query()方法允许我们使用类似SQL的表达式来过滤DataFrame，它通常比布尔索引更简洁。pivot()函数需要指定index（新的行索引）、columns（新的列名）和values（填充单元格的值）。如果原始数据中某个Time下缺少某个QuantityMeasured，pivot结果中对应的单元格将为NaN。在转换为列表时，这些NaN值会保留，可能需要额外处理（例如，使用fillna()）。
2. 使用 set_index 和 unstack
pivot函数在底层也使用了set_index和unstack。在某些特定情况下，直接使用这两个函数可能会提供略微的性能优势。
小绿鲸英文文献阅读器
英文文献阅读器，专注提高SCI阅读效率
437 查看详情 
# 使用 set_index 和 unstack 进行转置agg_df_unstack = ( df .query("QuantityMeasured in ['A', 'B', 'C', 'D']") .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("nUsing set_index and 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优化方法比简单的循环或未过滤的pivot更快，但在处理非常大的数据集时，Python层面的数据操作仍可能遇到性能瓶颈。期望将处理时间降低一个数量级（例如，从0.2秒到0.02秒）在Python中可能不切实际，因为数据加载、Pandas内部操作和内存分配都有其固有的开销。
利用SQL进行数据转置
实现大幅度性能提升的更有效方法是，将数据转置的逻辑直接推送到SQL数据库层面执行。数据库通常针对这类聚合和转置操作进行了高度优化，并且可以避免将大量原始长格式数据传输到Python，从而减少网络I/O和内存开销。
SQL查询示例
以下是一个使用SQL CASE语句和 GROUP BY 实现数据转置的示例查询：
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 DFROM your_table_name -- 替换为你的实际表名WHERE QuantityMeasured IN ('A', 'B', 'C', 'D') -- 提前过滤，减少聚合数据量GROUP BY TimeORDER BY Time; -- 确保时间顺序一致
解释：
SUM(CASE WHEN QuantityMeasured = ‘A’ THEN Value ELSE 0 END) AS A: 对于每个Time组，如果QuantityMeasured是’A’，则取其Value；否则取0。由于每个Time和QuantityMeasured组合在原始长格式数据中通常是唯一的，SUM函数在这里实际上起到了选择非零值的作用。如果存在多个相同Time和QuantityMeasured的记录，SUM会将它们加起来，这可能需要根据业务逻辑调整（例如使用MAX或AVG）。GROUP BY Time: 按照Time列对数据进行分组，以便为每个时间戳计算出’A’, ‘B’, ‘C’, ‘D’的值。WHERE QuantityMeasured IN (‘A’, ‘B’, ‘C’, ‘D’): 这是一个关键的优化点，它在数据聚合之前就过滤掉了不需要的测量类型，大大减少了数据库需要处理的数据量。ORDER BY Time: 确保结果按照时间顺序排列，这对于后续在Python中直接转换为列表非常有用。
在Python中执行SQL查询并提取数据
一旦SQL查询执行完毕，你将获得一个已经转置好的结果集。你可以直接将这个结果集读取到Pandas DataFrame，然后轻松地提取所需的列表。
# 假设你已经建立了SQL连接 `your_sql_connection`# import sqlalchemy# engine = sqlalchemy.create_engine("mysql+mysqlconnector://user:password@host/db")# your_sql_connection = engine.connect()# sql_query = """# 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;# """# agg_df_sql = pd.read_sql(sql_query, your_sql_connection)# # 提取所需列表# list_of_time_sql = agg_df_sql['Time'].tolist()# list_of_A_sql = agg_df_sql['A'].tolist()# list_of_B_sql = agg_df_sql['B'].tolist()# list_of_C_sql = agg_df_sql['C'].tolist()# list_of_D_sql = agg_df_sql['D'].tolist()# print("nFrom SQL-pivoted data:")# print("Time:", list_of_time_sql)# print("A:", list_of_A_sql)# print("B:", list_of_B_sql)# print("C:", list_of_C_sql)# print("D:", list_of_D_sql)
优点：
极致性能： 数据库服务器通常拥有更强大的计算资源和优化的查询引擎，能够以更快的速度处理大规模数据转置。减少数据传输： 只将已经转置好的少量数据传输到Python，而非大量的原始长格式数据，显著减少网络I/O。减轻Python端负担： 将计算密集型任务从Python应用程序中卸载，使其能够专注于后续的分析和业务逻辑。
总结与建议
在将SQL中的长格式数据重构为Python列表时，选择正确的方法至关重要：
Pandas优化： 对于中小型数据集（例如几万到几十万行），或者当你需要更大的灵活性、数据量不大到足以造成显著性能问题时，Pandas的query结合pivot或set_index/unstack是一个不错的选择。通过预过滤不需要的类别，可以获得显著的性能提升。SQL端转置： 对于大型数据集，或者对性能有极高要求（例如需要将处理时间从0.2秒缩短到0.02秒）的场景，强烈建议将数据转置逻辑下推到SQL数据库层面执行。这种方法能够最大化利用数据库的优化能力，减少数据传输开销，从而实现最佳性能。
在实际应用中，你可以根据数据量、性能要求以及团队对SQL和Pandas的熟悉程度来选择最合适的策略。通常，对于性能敏感的生产环境，SQL端转置是更优的选择。
以上就是从SQL到Python：Pandas与SQL实现数据高效转置的策略的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/920198.html
微信扫一扫 
支付宝扫一扫 
