从SQL到Python：Pandas与SQL实现数据高效转置的策略

        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

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]

import pandas as pd
import 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)

# 使用 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)

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; -- 确保时间顺序一致

# 假设你已经建立了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)