Pandas列表单元格拆分及多列标准化格式转换问题

解决步骤 #

1. 先按Statement、Standard A分组聚合，把同一声明下分散的Standard B、Standard C值合并到同一行，解决错位问题
2. 编写通用函数处理单标准列：拆分逗号分隔的引用、补全对应前缀、去除空白字符
3. 对齐三个标准列拆分后的列表长度，短列表补空值后逐行展开，解决多列拆分长度不一致报错问题

完整实现代码 #

import pandas as pd
import numpy as np

# 构造原始DataFrame
raw_data = {
    "Statement": ["Statement 1", "Statement 1", "Statement 2",
                  "Statement 3", "Statement 3",],
    "Standard A": ["A1", "A1", "A2", "A3", "A3"],
    "Standard B": ["B-1.2.3, 1.2.4", np.nan, np.nan, "B-1.2.5", np.nan],
    "Standard C": [np.nan, "C2, 3, 4 ", "C5  ", np.nan, "C6,7"],
}
df = pd.DataFrame(raw_data)

# 步骤1：按声明和标准A分组，合并同一声明下的其他标准列值
df_agg = df.groupby(['Statement', 'Standard A'], as_index=False).agg(
    {'Standard B': lambda x: ','.join(x.dropna().str.strip()),
     'Standard C': lambda x: ','.join(x.dropna().str.strip())}
)

# 步骤2：定义标准列处理函数
def process_standard_col(s, prefix):
    if not s:
        return []
    # 拆分+去空白
    items = [i.strip() for i in s.split(',') if i.strip()]
    if not items:
        return []
    # 补前缀：第一个项已经带前缀，后续项无则补
    res = [items[0]]
    for item in items[1:]:
        if not item.startswith(prefix):
            res.append(f"{prefix}{item}")
        else:
            res.append(item)
    return res

# 处理各标准列
df_agg['Standard B'] = df_agg['Standard B'].apply(lambda x: process_standard_col(x, 'B-'))
df_agg['Standard C'] = df_agg['Standard C'].apply(lambda x: process_standard_col(x, 'C'))
# Standard A已经是单值，转成单元素列表方便后续对齐
df_agg['Standard A'] = df_agg['Standard A'].apply(lambda x: [x])

# 步骤3：对齐各列列表长度，补空值后展开
def align_lists(row):
    # 取三个列的最大长度
    max_len = max(len(row['Standard A']), len(row['Standard B']), len(row['Standard C']))
    # 短列表补空字符串
    row['Standard A'] = row['Standard A'] + ['']*(max_len - len(row['Standard A']))
    row['Standard B'] = row['Standard B'] + ['']*(max_len - len(row['Standard B']))
    row['Standard C'] = row['Standard C'] + ['']*(max_len - len(row['Standard C']))
    return row

df_agg = df_agg.apply(align_lists, axis=1)
# 逐列展开成多行
df_result = df_agg.explode(['Standard A', 'Standard B', 'Standard C']).reset_index(drop=True)

# 输出结果
print(df_result)

如果不需要补全前缀，删除process_standard_col中前缀补全的逻辑即可。

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