大规模下基于app_id列表项更新DataFrame字段的最优方案问询

一、Pandas 端优化方案 #

原方案的核心问题是多次逐行调用apply，这在2000万行规模下效率极低。推荐用向量化操作+映射表关联的方式，避免逐行循环：

步骤1：构建app_id到属性的映射表 #

先把配置字典转换成扁平化的映射DataFrame，每个app_id对应要更新的developer和owner：

import pandas as pd

# 把配置字典转成映射表
mapping_data = []
for key, value in dict_apps_changes.items():
    for app in value['apps']:
        mapping_data.append({
            'app_id': app,
            'developer_new': value['developer'],
            'owner_new': value['owner']
        })
mapping_df = pd.DataFrame(mapping_data)

步骤2：展开app_id数组并关联映射表 #

通过explode把每行的app_id列表拆成单行，再和映射表关联，最后聚合回原行：

# 保留原索引，方便后续聚合
df_exploded = df.reset_index().explode('app_id')

# 关联映射表
df_merged = df_exploded.merge(mapping_df, on='app_id', how='left')

# 按原索引分组，取第一个匹配的更新值（保持原循环的优先级顺序）
# 注意：若一个行的app_id匹配多个规则，会按映射表中先出现的规则生效
df_grouped = df_merged.groupby('index').agg({
    'developer': 'first',
    'owner': 'first',
    'developer_new': 'first',
    'owner_new': 'first'
})

# 用新值替换原字段，无匹配则保留原值
df_updated = df_grouped.assign(
    developer=lambda x: x['developer_new'].combine_first(x['developer']),
    owner=lambda x: x['owner_new'].combine_first(x['owner'])
).drop(['developer_new', 'owner_new'], axis=1).reset_index(drop=True)

性能提升关键 #

• 用explode+merge的向量化操作替代逐行apply，Pandas内部会用C级别的运算，速度提升10~100倍
• 只做一次关联和聚合，避免循环中重复计算条件

二、BigQuery SQL 实现方案 #

既然数据来自BigQuery，直接在SQL层处理无需下载超大DataFrame，是更高效的方案（尤其适合2000万行规模）：

步骤1：构建映射关系表 #

先把配置字典转换成SQL中的映射表（可以用UNION ALL直接定义，或者创建临时表）：

WITH app_mapping AS (
    SELECT 'app_id_1' AS app_id, 'Developer 2' AS developer, 'Owner 2' AS owner UNION ALL
    SELECT 'app_id_2' AS app_id, 'Developer 2' AS developer, 'Owner 2' AS owner UNION ALL
    SELECT 'app_id_3' AS app_id, 'Developer 3' AS developer, 'Owner 3' AS owner
),
-- 展开原表的app_id数组
exploded_table AS (
    SELECT 
        original.*,
        app_id_single
    FROM 
        `your-project.your-dataset.your-table` original,
        UNNEST(original.app_id) AS app_id_single
),
-- 关联映射表并标记优先级（保持原规则的执行顺序）
mapped_table AS (
    SELECT 
        exploded_table.*,
        app_mapping.developer AS new_developer,
        app_mapping.owner AS new_owner,
        -- 给规则设置优先级，数字越小优先级越高（对应原字典的循环顺序）
        CASE 
            WHEN app_mapping.developer = 'Developer 2' THEN 1
            WHEN app_mapping.developer = 'Developer 3' THEN 2
        END AS rule_priority
    FROM exploded_table
    LEFT JOIN app_mapping ON exploded_table.app_id_single = app_mapping.app_id
)
-- 按原行取优先级最高的匹配值
SELECT 
    -- 取优先级最高的新值，无匹配则保留原值
    COALESCE(FIRST_VALUE(new_developer) OVER (PARTITION BY original_row_id ORDER BY rule_priority), ANY_VALUE(developer)) AS developer,
    COALESCE(FIRST_VALUE(new_owner) OVER (PARTITION BY original_row_id ORDER BY rule_priority), ANY_VALUE(owner)) AS owner,
    -- 保留其他字段
    ANY_VALUE(other_column1) AS other_column1,
    ANY_VALUE(other_column2) AS other_column2
FROM (
    SELECT 
        -- 用生成的唯一标识标记原行（如果原表有主键可以直接用主键）
        GENERATE_UUID() AS original_row_id,
        developer,
        owner,
        new_developer,
        new_owner,
        rule_priority,
        other_column1,
        other_column2
    FROM mapped_table
)
GROUP BY original_row_id

优势 #

• 利用BigQuery的分布式计算能力，处理2000万行数据速度远快于本地Pandas
• 无需将数据下载到本地，避免内存压力
• 规则调整只需修改app_mapping部分，维护更方便

补充说明 #

• 规则优先级：如果一个行的app_id同时匹配多个规则，两种方案都保持原配置字典的循环顺序（先出现的规则生效）
• 内存优化：如果本地Pandas处理仍有内存压力，可以分块处理（比如读取时指定chunksize），但优先推荐BigQuery方案

内容的提问来源于stack exchange，提问作者The App Investor