CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
Kamikaze K
Kamikaze K

Reputation: 191

How can I merge,Join row cells that have same values in pandas data frame

I have the following data frame;

enter image description here

I would like to merge the cells that contain the same values in the concurrent rows and also join the first field to one cell. An output that I am after based on this is

enter image description here

I have tried a few Groupby options but with no luck

Upvotes: 0

Views: 48

Answers (1)

Hammurabi
Hammurabi

Reputation: 1179

You can groupby multiple columns, then aggregate the matching Field 1 into a list:

df = pd.DataFrame({'Field 1':['A', 'B', 'A', 'B'],'Field 2':[1, 1, 2, 1], 'Field 3': [2, 2, 3, 12], 'Field 4': [3, 3, 4, 13]})

dfg = df.groupby(['Field 2', 'Field 3', 'Field 4'], as_index=False).agg({
    'Field 1': lambda x : ', '.join(sorted(list(x)))  })

to get dfg:

   Field 2  Field 3  Field 4 Field 1
0        1        2        3    A, B
1        1       12       13       B
2        2        3        4       A

Upvotes: 1

Related Questions