如何用Pandas将列表格式的检测数据转换为行列结构DataFrame

现有一个存储在Pandas DataFrame中的数据集，结构如下：

Analyte_line
0                                                  NaN
1    [['Urea', 3.0, '3', ''], ['Creatinine', 3.0, '...
2    [['Total Protein', '', '6', ''], ['Albumin', '...
3    [['HGB', '', '18', ''], ['RBC', '', '1', ''], ...
4    [['Total Protein', '', '23', ''], ['Albumin', ...
..                                                 ...
102            [['Rapid Malaria', '', 'NEGATIVE', '']]
103  [['Rapid Malaria', '', 'POSITIVE (P.VIVAX)', '']]
104            [['Rapid Malaria', '', 'NEGATIVE', '']]
105  [['Rapid Malaria', '', 'POSITIVE (P.VIVAX)', '']]
106            [['Rapid Malaria', '', 'NEGATIVE', '']]

该DataFrame的Analyte_line列中，每行以嵌套列表形式存储检测项名称及其对应值（每个子列表格式为[检测项名称, 值1, 值2, 备注]），需要将其转换为标准的行列结构，示例如下：

步骤1：解析每行的嵌套列表 #

先过滤空值，再对每行的嵌套列表做解析——把每个检测项的非空值拼接起来，特殊处理Rapid Malaria这类需要映射结果的项：

import pandas as pd

# 模拟用户的原始DataFrame（实际使用时直接用自己的df即可）
data = {
    'Analyte_line': [
        None,
        [['Urea', 3.0, '3', ''], ['Creatinine', 3.0, '3', ''], ['Uric Acid', 3.0, '3', ''], ['Alkaline Phosphatase', '', '4', '']],
        [['Total Protein', '', '6', ''], ['Albumin', '', '3', '']],
        [['Rapid Malaria', '', 'POSITIVE (P.VIVAX)', '']],
        [['Rapid Malaria', '', 'NEGATIVE', '']]
    ]
}
df = pd.DataFrame(data)

def parse_line(line):
    if pd.isna(line):
        return {}
    result_dict = {}
    for item in line:
        name, val1, val2, _ = item
        # 收集非空的数值并拼接
        valid_vals = [str(v) for v in [val1, val2] if v != '']
        if name == 'Rapid Malaria':
            # 把结果映射成Positive/Negative
            result_dict['Test'] = 'Positive' if 'POSITIVE' in val2 else 'Negative'
        else:
            result_dict[name] = ' , '.join(valid_vals) if valid_vals else None
    return result_dict

# 对每行应用解析函数，得到字典列表
parsed_data = df['Analyte_line'].apply(parse_line).tolist()

步骤2：转换为标准DataFrame #

把解析后的字典列表直接转成DataFrame，Pandas会自动对齐所有检测项，缺失的项用NaN填充：

final_df = pd.DataFrame(parsed_data)
print(final_df)

输出结果示例：

Urea Creatinine   Uric Acid Alkaline Phosphatase     Test Total Protein Albumin
0        NaN        NaN         NaN                  NaN      NaN           NaN     NaN
1  3.0 , 3  3.0 , 3    3.0 , 3                     4      NaN           NaN     NaN
2        NaN        NaN         NaN                  NaN      NaN             6       3
3        NaN        NaN         NaN                  NaN  Positive           NaN     NaN
4        NaN        NaN         NaN                  NaN  Negative           NaN     NaN

步骤3：可选优化（按需调整） #

如果需要指定列顺序、填充空值，可以加以下操作：

# 定义想要的列顺序
target_columns = ['Urea', 'Creatinine', 'Uric Acid', 'Alkaline Phosphatase', 'Test', 'Total Protein', 'Albumin']
# 调整列顺序并把空值换成'-'
final_df = final_df[target_columns].fillna('-')

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