python / SQL 如何根据一个字段分组然后把另一个字段全部拼接起来

SQL:

SELECT name, CONCAT(name,',',GROUP_CONCAT(relatives)) AS relatives
FROM `test` GROUP BY name

 结果

还可以添加/替换间隔符

SELECT name, CONCAT(name,'&',GROUP_CONCAT(relatives SEPARATOR '&')) ASrelatives
FROM `test` GROUP BY name

Python:

import pandas as pd
test_dict = {'name':['Amber','Amber','Bobby','Bobby'],'relatives':['Alice','Bob','Cindy','Eric']}
df = pd.DataFrame(test_dict)
print('-------原dataframe-------')
print(df)
data = df.groupby('name')['relatives'].apply(lambda x:x.str.cat(sep=',')).reset_index()
print('----------结果表----------')
print(data)

*****注意: sql可以直接把分组字段也添加到合并字段里面 但是python这个代码不行