Grouping Data On Column Value

Hi I have data (in excel and text file as well) like C1 C2 C3 1 p a 1 q b 2 r c 2 s d And I want the output like: C1 C2 C3 1 p,q a,b

Solution 1:

First pandas.read_excel - output is DataFrame:

df = pd.read_excel('file.xlsx')

Then you can use groupby with aggjoin:

df = df.groupby('C1').agg(','.join).reset_index()
print (df)
   C1   C2   C3
0   1  p,q  a,b
1   2  r,s  c,d

If more columns in df and need filter only C2 and C3:

df = df.groupby('C1')['C2','C3'].agg(','.join).reset_index()
print (df)
   C1   C2   C3
0   1  p,q  a,b
1   2  r,s  c,d

For save to excel file use DataFrame.to_excel, obviously without index:

df.to_excel('file.xlsx', index=False)