如何基于Groupby为Pandas原DataFrame补充缺失名称的行？

需求说明：

• 按text字段对DataFrame分组
• 若分组内存在至少一个非None的有效name，则针对names_set={'A','B','C'}中未在该分组出现的name，为每个缺失name添加一行
• 新增行的id、start、end字段为NaN，text为对应分组的text值，name为缺失的名称
• 允许同一text和name对应多行不同start/end的情况

初始DataFrame #

import pandas as pd

columns = ['id','text','name','start','end']
data = [
    [1,"this is text 1", 'A',0,4],
    [2,"this is text 1", 'B',4,5],
    [3,"this is text 1", 'C',4,5],
    [3,"this is text 2", 'A',6,8],
    [4,'this is text 3',None, None, None],
    [5,"this is text 4", 'B',10,13],
    [6,"this is text 4", 'B',1,5]
]
df1 = pd.DataFrame(data= data,columns=columns)

期望输出DataFrame #

columns2 = ['id','text','name','start','end']
data2 = [
    [1,"this is text 1", 'A',0,4],
    [2,"this is text 1", 'B',4,5],
    [3,"this is text 1", 'C',4,5],
    [3,"this is text 2", 'A',6,8],
    [None,"this is text 2", 'B',None,None],
    [None,"this is text 2", 'C',None,None],
    [4,'this is text 3',None, None, None],
    [None,"this is text 4", 'A',None,None],
    [5,"this is text 4", 'B',10,13],
    [6,"this is text 4", 'B',1,5],
    [None,"this is text 4", 'C',None,None]
]
df2 = pd.DataFrame(data= data2,columns=columns2)

现有代码片段 #

names_set = {'A','B','C'}
g = df1.groupby('text')
text_names_group = df1.groupby("text")["name"].agg(list)
text_names_group
for text in text_names_group:
  if len(text) == 1 and text[0] is None:
    continue
  cur_names = set(text)
  missing_names_per_text = names_set - cur_names 

解决方案 #

你可以通过构建缺失行的DataFrame，再与原DataFrame合并的方式完成需求，以下是完整实现代码：

import pandas as pd

columns = ['id','text','name','start','end']
data = [
    [1,"this is text 1", 'A',0,4],
    [2,"this is text 1", 'B',4,5],
    [3,"this is text 1", 'C',4,5],
    [3,"this is text 2", 'A',6,8],
    [4,'this is text 3',None, None, None],
    [5,"this is text 4", 'B',10,13],
    [6,"this is text 4", 'B',1,5]
]
df1 = pd.DataFrame(data= data,columns=columns)
names_set = {'A','B','C'}

# 存储需要添加的缺失行
missing_rows = []

# 遍历每个text分组的键和对应名称列表
for text_val, names_list in df1.groupby("text")["name"].agg(list).items():
    # 跳过全是None的分组
    if all(name is None for name in names_list):
        continue
    # 获取当前分组已有的非None名称集合
    cur_names = {name for name in names_list if name is not None}
    # 计算缺失的名称
    missing_names = names_set - cur_names
    # 为每个缺失名称生成一行数据
    for name in missing_names:
        missing_rows.append({
            'id': None,
            'text': text_val,
            'name': name,
            'start': None,
            'end': None
        })

# 将缺失行转为DataFrame并合并到原DataFrame
df_result = pd.concat([df1, pd.DataFrame(missing_rows)], ignore_index=True)
# 按text和name排序，匹配期望输出的顺序
df_result = df_result.sort_values(by=['text', 'name'], na_position='last').reset_index(drop=True)

print(df_result)

关键修正与说明 #

1. 遍历分组键值对：原代码仅遍历了分组的名称列表，改为for text_val, names_list in ...items()才能获取对应分组的text值，这是实现的核心修正点
2. 准确判断全None分组：用all(name is None for name in names_list)替代原逻辑，能更精准识别无有效名称的分组
3. 生成缺失行：为每个缺失name构造字典数据，统一存入列表后转为DataFrame
4. 合并与排序：通过pd.concat合并原数据和缺失行，最后排序让结果与期望输出顺序一致

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