将含数组的列转换为多列（BigQuery）：新手求助高效实现方案

嘿，刚接触BigQuery和DataFrame的话，这种把数组型字段转成宽表的需求确实很典型，而且完全不用低效的嵌套循环——不管是在BigQuery端直接处理，还是拉到DataFrame后操作，都有高效的内置方法，咱们分别来看：

一、在BigQuery端直接处理（推荐，大数据量更高效） #

BigQuery的UNNEST+PIVOT组合是处理这种数组转列场景的最优解，底层都是经过优化的分布式操作，比手动循环快N倍。

步骤说明： #

1. 拆分数组为行：用UNNEST同时拆分itemids和value数组，通过WITH OFFSET保证每个itemid和对应的value一一匹配（因为两个数组长度一致，偏移量相同的元素是一对）。
2. 行转列（PIVOT）：用PIVOT函数把拆分后的itemid值转成列，对应的value填充到列中，没有对应itemid的记录自动填充NULL。

完整查询语句 #

基于你提供的原始查询，修改后的代码如下：

%%bigquery lab_m_df
WITH raw_data AS (
  SELECT
    # 年龄处理逻辑保留
    IF(DATETIME_DIFF(ADMITTIME, DOB, DAY)/365.25 < 200, DATETIME_DIFF(ADMITTIME, DOB, DAY)/365.25, 95) AS age,
    gender,
    admission_type,
    itemids,
    value,
    admission_location,
    insurance,
    marital_status,
    ethnicity,
    died
  FROM (
    SELECT
      ARRAY_AGG(ITEMID) as itemids,
      ARRAY_AGG(IFNULL(VALUE, 'null')) as value,
      MAX(HOSPITAL_EXPIRE_FLAG) as died,
      ANY_VALUE(ADMITTIME) as ADMITTIME,
      ANY_VALUE(ADMISSION_TYPE) as admission_type,
      ANY_VALUE(ADMISSION_LOCATION) as admission_location,
      ANY_VALUE(INSURANCE) as insurance,
      ANY_VALUE(MARITAL_STATUS) as marital_status,
      ANY_VALUE(ETHNICITY) as ethnicity,
      HADM_ID,
      SUBJECT_ID
    FROM `{admissions_table}` AS adm
    JOIN `{labevents_table}` AS diag
    USING (HADM_ID, SUBJECT_ID)
    WHERE DATETIME_DIFF(CHARTTIME, ADMITTIME, HOUR) <= 4
    GROUP BY HADM_ID, SUBJECT_ID
  )
  JOIN `{patients_table}` AS patients
  USING (SUBJECT_ID)
),
unnested_data AS (
  SELECT
    age,
    gender,
    admission_type,
    admission_location,
    insurance,
    marital_status,
    ethnicity,
    died,
    # 同时拆分两个数组，通过offset保证对应关系
    itemid,
    val AS value
  FROM raw_data,
       UNNEST(itemids) WITH OFFSET AS itemid OFFSET,
       UNNEST(value) WITH OFFSET AS val OFFSET
  WHERE itemid OFFSET = val OFFSET -- 确保itemid和value一一对应
)
# 行转列，把itemid转成列
SELECT *
FROM unnested_data
PIVOT(
  MAX(value) -- 因为每个HADM_ID+SUBJECT_ID对应一个itemid的唯一值，用MAX/ANY_VALUE都可以
  FOR itemid IN (51493, 51516, 51943) -- 这里可以列出你需要的itemid，或者省略让BigQuery自动包含所有出现的itemid
)

如果不确定所有itemid的取值，可以去掉IN (51493, 51516, 51943)，BigQuery会自动把所有出现过的itemid作为列，但注意如果itemid数量极多，可能会导致列数过多。

二、拉到Pandas DataFrame后处理 #

如果已经把数据加载到了lab_m_df这个DataFrame中，可以用pandas的向量式操作来高效处理，避免循环：

步骤说明： #

1. 展开数组：用explode同时展开itemids和value列（注意pandas 1.3+支持多列同时explode）。
2. 行转列：用pivot_table把itemid转成列，填充对应的value，缺失值自动为NaN（对应SQL的NULL）。

代码示例 #

import pandas as pd

# 同时展开itemids和value数组，保证对应关系
df_exploded = lab_m_df.explode(['itemids', 'value'], ignore_index=True)

# 行转列，把itemids转成列，value作为值
df_wide = df_exploded.pivot_table(
    index=['age', 'gender', 'admission_type', 'admission_location', 'insurance', 'marital_status', 'ethnicity', 'died'],
    columns='itemids',
    values='value',
    aggfunc='first'  # 每个索引+itemid组合取第一个值（因为原始数据是聚合后的，每个组合唯一）
).reset_index()

# 重命名列，比如把itemids的数字转成字符串列名（可选）
df_wide.columns = [str(col) if isinstance(col, int) else col for col in df_wide.columns]

这种方法用的是pandas的内置优化操作，比手动嵌套循环高效得多，尤其是数据量较大时。

内容的提问来源于stack exchange，提问作者Bar