Python pandas 合并关联行非空que_text后仅保留首页行的实现

实现思路 #

• 从identifier字段提取两个辅助信息：同一份多页文件的公共分组标识、当前文件对应的页码
• 按公共分组标识聚合数据，将同组所有非空的que_text值用空格拼接
• 其余字段统一取分组内首页（页码000）的对应值，最终得到仅保留首页、且que_text已合并的结果

完整代码 #

import pandas as pd
import re

# 示例数据初始化
asker1 = ['Peter', 'Markus', 'Rebecca', None, None, None, None, 'Sofie', 'Jesus', None]
que_text = ['QQQ', 'RRR', 'GGG', 'GGG', None, None, None, None, 'WWW', 'AAA']
date = ['14.10.2001', '12. October 1999', '14.10.2004', '14.10.2002', '14.10.2002', '14.10.2002', '14.10.2002', '14.10.2010', '14.10.2000', '14.10.2000']
identifier = ['Drs_2_00028_1', 'Drs_2_00029_1', 'Drs_2_00030_1_KlAnfr_000.tif', 'Drs_2_00030_1_KlAnfr_001.tif', 'Drs_2_00030_1_KlAnfr_002.tif', 'Drs_2_00030_1_KlAnfr_003.tif', 'Drs_2_00030_1_KlAnfr_004.tif', 'Drs_2_00052_1', 'Drs_2_00054_1_KlAnfr_000.tif', 'Drs_2_00054_1_KlAnfr_001.tif']
df2 = pd.DataFrame(
    list(zip(asker1, que_text, date, identifier)),
    columns =['asker1', 'que_text', 'date', 'identifier']
)

# 提取分组标识和页码
def extract_info(iden):
    match_res = re.search(r'^(.*_KlAnfr)_(\d{3})\.tif$', iden)
    if match_res:
        return match_res.group(1), int(match_res.group(2))
    # 非多页文件单独作为一组，页码设为0
    return iden, 0

df2[['group_id', 'page_num']] = df2['identifier'].apply(lambda x: pd.Series(extract_info(x)))

# 分组聚合得到结果
result = df2.groupby('group_id').agg(
    asker1=('asker1', lambda s: s[df2.loc[s.index, 'page_num'] == 0].iloc[0]),
    date=('date', lambda s: s[df2.loc[s.index, 'page_num'] == 0].iloc[0]),
    identifier=('identifier', lambda s: s[df2.loc[s.index, 'page_num'] == 0].iloc[0]),
    que_text=('que_text', lambda s: ' '.join([i for i in s if pd.notna(i)]) if any(pd.notna(i) for i in s) else None)
).reset_index(drop=True)

print(result)

输出结果 #

asker1 que_text              date                    identifier
0    Peter      QQQ        14.10.2001                 Drs_2_00028_1
1   Markus      RRR  12. October 1999                 Drs_2_00029_1
2  Rebecca  GGG GGG        14.10.2004  Drs_2_00030_1_KlAnfr_000.tif
3    Sofie     None        14.10.2010                 Drs_2_00052_1
4    Jesus  WWW AAA        14.10.2000  Drs_2_00054_1_KlAnfr_000.tif

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