如何按ASSIGNMENT升序分组合并Pandas DataFrame字符串

现有如下Pandas DataFrame：

import pandas as pd
df = pd.DataFrame({
    "RTG_CODE": ["RTG0538","RTG0538","RTG0538","RTG0538","RTG0538","RTG0538","RTG0538","RTG350001","RTG350001","RTG350001","RTG353904","RTG353904","RTG353904","RTG353904","RTG353904","RTG373902","RTG373902","RTG373902","Baltic","Baltic","Baltic","RTG407002","RTG407002","RTG407002","RTG407002","RTG407002"],
    "ELEMENT": ["TM~WCO~BKBK","AY~JHGT~BKBK","AY~EHUY~BKBK","AY~RUYI~BKBK","FT~QQ~BKBK","CM~ROIP~BKBK","CM~PPOI~BKBK","TM~WCO~10030269","AY~SO-3.9-4.9.SOT96SD112~10030269","FT~SG~10030269","TM~WCO~KHKH","AY~HVQFN.SOT617CD35~KHKH","AY~WB-QFN-ADV~KHKH","AY~SG-QFN-5X5~KHKH","FT~3L/F~KHKH","TM~WCO~10030269","AY~HVSON.SOT782HA16~10030269","FT~SG~10030269","TM~WCO~10034140","AY~DHX2QFN-2.4-2.SOT1442AA1~10034140","FT~HM/F~10034140","TM~WCO~KHKH","AY~HVQFN.SOT617CD39~KHKH","AY~WB-QFN-ADV~KHKH","FT~DQ/F~KHKH","FTT~FT0010330035/F~KHKH"],
    "ASSIGNMENT": [-50,110,120,130,200,300,380,-50,130,200,-50,110,120,130,200,-50,120,200,-50,130,200,-50,120,130,200,250]
})

需要完成以下操作：

• 按RTG_CODE分组
• 每组内根据ASSIGNMENT数值从小到大排序
• 将排序后的ELEMENT值合并为用::分隔的长字符串

最终需要得到如下结构的输出DataFrame：

df_output = pd.DataFrame({
    "RTG_CODE": ["RTG0538","RTG350001","RTG353904","RTG373902","Baltic","RTG407002"],
    "MERGED": [
        "TM~WCO~BKBK::AY~JHGT~BKBK::AY~EHUY~BKBK::AY~RUYI~BKBK::FT~QQ~BKBK::CM~ROIP~BKBK::CM~PPOI~BKBK",
        "TM~WCO~10030269::AY~SO-3.9-4.9.SOT96SD112~10030269::FT~SG~10030269",
        "TM~WCO~KHKH::AY~HVQFN.SOT617CD35~KHKH::AY~WB-QFN-ADV~KHKH::AY~SG-QFN-5X5~KHKH::FT~3L/F~KHKH",
        "TM~WCO~10030269::AY~HVSON.SOT782HA16~10030269::FT~SG~10030269",
        "TM~WCO~10034140::AY~DHX2QFN-2.4-2.SOT1442AA1~10034140::FT~HM/F~10034140",
        "TM~WCO~KHKH::AY~HVQFN.SOT617CD39~KHKH::AY~WB-QFN-ADV~KHKH::FT~DQ/F~KHKH::FTT~FT0010330035/F~KHKH"
    ]
})

通过Pandas的排序、分组和聚合操作即可完成需求，完整代码如下：

# 按RTG_CODE分组，每组内按ASSIGNMENT升序排序后合并ELEMENT
df_output = (
    df.sort_values(by=["RTG_CODE", "ASSIGNMENT"])
      .groupby("RTG_CODE")["ELEMENT"]
      .apply("::".join)
      .reset_index(name="MERGED")
)

# 验证结果
print(df_output)

步骤说明： #

1. 排序：sort_values(by=["RTG_CODE", "ASSIGNMENT"])先按RTG_CODE分组排序，再在每组内按ASSIGNMENT升序排列，保证后续聚合时元素顺序符合要求
2. 分组聚合：groupby("RTG_CODE")["ELEMENT"]按RTG_CODE分组后仅保留ELEMENT列，apply("::".join)将每组的ELEMENT列表用::连接成字符串
3. 调整结构：reset_index(name="MERGED")将分组后的索引转为普通列，并把聚合后的列命名为MERGED，得到目标DataFrame结构

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