如何在Pandas分组时为DataFrame新增关系列并对Prof列去重

解决方法 #

你之前的groupby拼接没有做去重处理，直接把组内所有Prof值拼接，所以会出现doc,doc的问题，按如下逻辑处理即可同时满足去重拼接和关系标注需求，且不需要提前对原表全量去重：

import pandas as pd
import numpy as np

# 你的示例DataFrame
df = pd.DataFrame({'Name': {0: 'ahmed',
  1: 'ahmed',
  2: 'mohamed',
  3: 'mohamed',
  4: 'hassan',
  5: 'ali',
  6: 'nice',
  7: 'bad'},
 'Location': {0: 'A', 1: 'A', 2: 'B', 3: 'B', 4: 'C', 5: 'C', 6: 'D', 7: 'D'},
 'Prof': {0: 'doc',
  1: 'doc',
  2: 'eng',
  3: 'doc',
  4: 'eng',
  5: 'eng',
  6: 'doc',
  7: 'eng'}})

# 1. 按Name+Location分组，拼接去重后的Prof，统计每组唯一Prof数量
grouped_nl = df.groupby(['Name', 'Location'], as_index=False).agg(
    Prof=('Prof', lambda x: ','.join(x.unique())),
    cnt_prof=('Prof', lambda x: x.nunique())
)

# 2. 按Location分组，统计每个地点的唯一Name数、唯一Prof数
grouped_loc = df.groupby('Location', as_index=False).agg(
    cnt_name_per_loc=('Name', 'nunique'),
    cnt_prof_per_loc=('Prof', 'nunique')
)

# 3. 合并统计结果，判断关联关系
temp = grouped_nl.merge(grouped_loc, on='Location', how='left')
conditions = [
    (temp['cnt_name_per_loc'] == 1) & (temp['cnt_prof_per_loc'] == 1),
    (temp['cnt_name_per_loc'] == 1) & (temp['cnt_prof_per_loc'] > 1),
    (temp['cnt_name_per_loc'] > 1) & (temp['cnt_prof_per_loc'] == 1),
    (temp['cnt_name_per_loc'] > 1) & (temp['cnt_prof_per_loc'] > 1)
]
values = ['1_to_1', '1_to_M', 'M_to_1', 'M_to_M']
temp['relation'] = np.select(conditions, values)

# 4. 合并回原表，保留其他列后按Name+Location去重得到最终结果
# 如果需要保留原表其他列，调整keep参数即可，比如keep='first'保留第一条记录的其他列值
final_df = df.drop('Prof', axis=1).merge(temp, on=['Name', 'Location'], how='left')\
    .drop_duplicates(['Name', 'Location'], keep='first')\
    .drop(['cnt_prof', 'cnt_name_per_loc', 'cnt_prof_per_loc'], axis=1)\
    .reset_index(drop=True)

print(final_df)

运行输出结果和你要求的完全一致：

Name Location      Prof relation
0    ahmed        A       doc   1_to_1
1  mohamed        B  eng,doc   1_to_M
2   hassan        C       eng   M_to_1
3      ali        C       eng   M_to_1
4     nice        D       doc   M_to_M
5      bad        D       eng   M_to_M

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