Clubbing a dataframe column values based on a specific condition and storing it in a new column

user_16154762 0
Viewed 65

Trying to club the occurrences of col1 and storing it in a new column as col1_occurences, but unable to do so, please help

Input df:

col1     col2      
sheet1   john
sheet2   harry
sheet3   john
sheet4   mark
sheet5   mark

Expected Output

col1     col2    col1_occurences
sheet1   john     sheet1,sheet3
sheet2   harry    sheet2
sheet3   john     sheet1,sheet3
sheet4   mark     sheet4,sheet5
sheet5   mark     sheet4,sheet5
pandas-groupby dataframe python
1 Answers

Try with groupby transform:

df['col1_occurrences'] = df.groupby('col2')['col1'].transform(','.join)

df:

     col1   col2 col1_occurrences
0  sheet1   john    sheet1,sheet3
1  sheet2  harry           sheet2
2  sheet3   john    sheet1,sheet3
3  sheet4   mark    sheet4,sheet5
4  sheet5   mark    sheet4,sheet5

Complete Working Example:

import pandas as pd

df = pd.DataFrame({
    'col1': ['sheet1', 'sheet2', 'sheet3', 'sheet4', 'sheet5'],
    'col2': ['john', 'harry', 'john', 'mark', 'mark']
})
df['col1_occurrences'] = df.groupby('col2')['col1'].transform(','.join)
print(df)
user_154978880
Related