如何在Polars中合并重复观测并拼接唯一字段值？

基础场景：指定分组列拼接单个字段 #

给定如下Polars DataFrame，需按last_name和first_name分组，将每组内ssn的唯一值用分号;拼接，重复值仅保留一个：

import polars as pl

df = pl.DataFrame({'last_name':['mallesh','bhavik','jagarini','mallesh','jagarini'],
                  'first_name':['yamulla','vemulla','yegurla','yamulla','yegurla'],
                  'ssn':['1234','7847','0648','4567','0648']})

解决代码 #

result = df.group_by(['last_name', 'first_name'], maintain_order=True).agg(
    pl.col('ssn').unique().str.concat(';')
)
print(result)

输出结果 #

shape: (3, 3)
┌───────────┬────────────┬───────────┐
│ last_name ┆ first_name ┆ ssn       │
│ ---       ┆ ---        ┆ ---       │
│ str       ┆ str        ┆ str       │
╞═══════════╪════════════╪═══════════╡
│ mallesh   ┆ yamulla    ┆ 1234;4567 │
│ bhavik    ┆ vemulla    ┆ 7847      │
│ jagarini  ┆ yegurla    ┆ 0648      │
└───────────┴────────────┴───────────┘

补充场景1：指定分组列拼接所有剩余字段 #

若DataFrame新增其他字段（如dob），需按指定分组列对所有非分组列的唯一值执行分号拼接：

df = pl.DataFrame({'last_name':['mallesh','bhavik','jagarini','mallesh','jagarini'],
                  'first_name':['yamulla','vemulla','yegurla','yamulla','yegurla'],
                  'ssn':['1234','7847','0648','4567','0648'],
                  'dob':['10/11/1990','09/16/1991','01/01/1990','10/11/1990','02/14/1983']})

解决代码 #

通过pl.exclude()自动选中所有非分组列，无需逐个指定字段：

group_cols = ['last_name', 'first_name']
result = df.group_by(group_cols, maintain_order=True).agg(
    pl.exclude(group_cols).unique().str.concat(';')
)
print(result)

输出结果 #

shape: (3, 4)
┌───────────┬────────────┬───────────┬───────────────────────────┐
│ last_name ┆ first_name ┆ ssn       ┆ dob                       │
│ ---       ┆ ---        ┆ ---       ┆ ---                       │
│ str       ┆ str        ┆ str       ┆ str                       │
╞═══════════╪════════════╪═══════════╪═══════════════════════════╡
│ mallesh   ┆ yamulla    ┆ 1234;4567 ┆ 10/11/1990                │
│ bhavik    ┆ vemulla    ┆ 7847      ┆ 09/16/1991                │
│ jagarini  ┆ yegurla    ┆ 0648      ┆ 01/01/1990;02/14/1983     │
└───────────┴────────────┴───────────┴───────────────────────────┘

补充场景2：过滤空字符串后拼接 #

当字段存在空字符串时，需先过滤空值再执行拼接，避免出现;xxx这类无效格式：

df = pl.DataFrame({'last_name':['mallesh','bhavik','jagarini','mallesh','jagarini'],
                  'first_name':['yamulla','vemulla','yegurla','yamulla','yegurla'],
                  'ssn':['1234','7847','0648','4567','0648'],
                  'dob':['10/11/1990','09/16/1991','01/01/1990','','02/14/1983']})

解决代码 #

在处理前添加filter(pl.col() != "")过滤空字符串：

group_cols = ['last_name', 'first_name']
result = df.group_by(group_cols, maintain_order=True).agg(
    pl.exclude(group_cols)
      .filter(pl.col() != "")
      .unique()
      .str.concat(';')
)
print(result)

输出结果 #

shape: (3, 4)
┌───────────┬────────────┬───────────┬───────────────────────────┐
│ last_name ┆ first_name ┆ ssn       ┆ dob                       │
│ ---       ┆ ---        ┆ ---       ┆ ---                       │
│ str       ┆ str        ┆ str       ┆ str                       │
╞═══════════╪════════════╪═══════════╪═══════════════════════════╡
│ mallesh   ┆ yamulla    ┆ 1234;4567 ┆ 10/11/1990                │
│ bhavik    ┆ vemulla    ┆ 7847      ┆ 09/16/1991                │
│ jagarini  ┆ yegurla    ┆ 0648      ┆ 01/01/1990;02/14/1983     │
└───────────┴────────────┴───────────┴───────────────────────────┘

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