Pandas与SQL高效重构长格式数据为宽格式列表

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

# 假设df是您的原始DataFrame
# df = pd.read_sql("SELECT Time, QuantityMeasured, Value FROM your_table", your_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')
print("原始pivot结果：")
print(pivot_df)

# 提取目标列表
list_of_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("\n提取的列表示例：")
print(f"Time: {list_of_time}")
print(f"A: {list_of_A}")

# 假设我们只需要 'A', 'B', 'C', 'D' 这四种测量值
desired_quantities = ['A', 'B', 'C', 'D']

agg_df = (
    df.query("QuantityMeasured in @desired_quantities") # 使用query进行预过滤
    .pivot(index='Time', columns='QuantityMeasured', values='Value')
)
print("\n预过滤后的pivot结果：")
print(agg_df)

# 提取目标列表
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("\n提取的列表示例（预过滤后）：")
print(f"Time: {list_of_time}")
print(f"A: {list_of_A}")

agg_df_unstack = (
    df
    .query("QuantityMeasured in @desired_quantities") # 同样进行预过滤
    .set_index(['Time', 'QuantityMeasured'])['Value'] # 将Time和QuantityMeasured设为多级索引
    .unstack() # 将QuantityMeasured从索引转换为列
)
print("\n使用set_index和unstack的结果：")
print(agg_df_unstack)

# 提取目标列表
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(f"Time: {list_of_time_unstack}")
print(f"A: {list_of_A_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 -- 将 'your_table_name' 替换为实际的表名
GROUP BY Time
ORDER BY Time; -- 可选：按时间排序

# 假设您已经建立了数据库连接
# import sqlalchemy
# engine = sqlalchemy.create_engine("mysql+mysqlconnector://user:password@host/db")

# 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
# GROUP BY Time
# ORDER BY Time;
# """

# pivoted_df_from_sql = pd.read_sql(sql_query, engine)

# # 从Pandas DataFrame中提取列表
# list_of_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()