如何基于平均阈值拆分Pandas DataFrame列并展开为多行？

问题背景 #

我手头有一个包含百万级行的Pandas DataFrame，其中player_average列存储的是字符串格式的嵌套列表，每个子列表包含player_match（场次编号）和player_average（场均数据）两个值。现在需要完成两个核心操作：

1. 将player_average列拆分成player_match和player_average两列，并展开为多行
2. 仅保留player_average数值大于指定阈值的记录，同时要处理该列中存在的"NA"、"-"等非数值内容

输入的DataFrame结构示例如下：

sports_name,player_name,player_country,player_average
football,XYZ,US,"[['1', '62.58'], ['2', '25.34'],['3', '88.35'],['4', '59.39']]"
football,ABC,US,"[['1', '56.61'], ['2', '52.63'],['3', 'NA'],['4', '44.32'],['5', '39.69']]"
cricket,PQR,IND,"[['1', '98.73'], ['2', '72.62'],['3', '71.53'],['4', '73.72']]"
cricket,LMN,IND,"[['1', '72.52'], ['2', '71.82'],['3', '-'],['4', '62.72'],['5', '73.83']]"

期望输出示例（假设阈值为50）：

sports_name,player_name,player_country,player_match,player_average
football,XYZ,US,1,62.58
football,XYZ,US,3,88.35
football,XYZ,US,4,59.39
football,ABC,US,1,56.61
football,ABC,US,2,52.63
cricket,PQR,IND,1,98.73
cricket,PQR,IND,2,72.62
cricket,PQR,IND,3,71.53
cricket,PQR,IND,4,73.72
cricket,LMN,IND,1,72.52
cricket,LMN,IND,2,71.82
cricket,LMN,IND,4,62.72
cricket,LMN,IND,5,73.83

解决方案思路 #

由于要适配百万级行数+单列含2万+子列表的大数据场景，必须优先考虑效率，避免使用慢循环。核心思路是：

• 用安全高效的工具解析字符串为实际列表
• 利用Pandas内置的explode方法快速展开嵌套列表为多行
• 矢量化拆分列表为目标列，避免逐行处理
• 一键清洗非数值内容并过滤符合阈值的记录

完整代码实现 #

import pandas as pd
import ast

def process_player_average(df, minimum_average):
    # 1. 解析字符串格式的嵌套列表为Python列表（安全且高效）
    df['player_average'] = df['player_average'].apply(ast.literal_eval)
    
    # 2. 将每个嵌套列表展开为单独行，ignore_index重置索引避免重复
    df_exploded = df.explode('player_average', ignore_index=True)
    
    # 3. 把展开后的列表拆分为player_match和player_average两列
    df_exploded[['player_match', 'player_average']] = pd.DataFrame(
        df_exploded['player_average'].tolist(),
        index=df_exploded.index
    )
    
    # 4. 清洗数据：将非数值内容转为NaN，再转为浮点型
    df_exploded['player_average'] = pd.to_numeric(
        df_exploded['player_average'],
        errors='coerce'
    )
    
    # 5. 过滤出数值大于阈值且不为空的记录，删除原列（可选）
    df_filtered = df_exploded[
        df_exploded['player_average'] > minimum_average
    ].drop(columns=['player_average'])
    
    return df_filtered

# 测试用例
if __name__ == "__main__":
    # 构造输入DataFrame
    data = [
        ["football", "XYZ", "US", "[['1', '62.58'], ['2', '25.34'],['3', '88.35'],['4', '59.39']]"],
        ["football", "ABC", "US", "[['1', '56.61'], ['2', '52.63'],['3', 'NA'],['4', '44.32'],['5', '39.69']]"],
        ["cricket", "PQR", "IND", "[['1', '98.73'], ['2', '72.62'],['3', '71.53'],['4', '73.72']]"],
        ["cricket", "LMN", "IND", "[['1', '72.52'], ['2', '71.82'],['3', '-'],['4', '62.72'],['5', '73.83']]"]
    ]
    df = pd.DataFrame(data, columns=["sports_name", "player_name", "player_country", "player_average"])
    
    # 设置阈值并处理数据
    result = process_player_average(df, minimum_average=50)
    print(result.to_csv(index=False))

关键优化与注意事项 #

• 效率优先：全程使用Pandas矢量化操作和explode，比逐行循环效率高几个数量级，完全适配百万级数据
• 安全解析：用ast.literal_eval替代eval，避免执行恶意代码，同时保证规范字符串的解析速度
• 内存控制：如果数据量极大，可以配合pd.read_csv(chunksize=...)分块处理，避免一次性加载全部数据到内存
• 格式兼容：如果player_average列存在格式不规范的字符串（比如缺少引号、括号不匹配），需要先做预处理修复

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