Dataframe using Pandas delimiting values using colon, commas and sorting-CodePudding

I have this DataFrame:

C0  C1   C2
0   jjj  3
0   aaa  2
1   bbb  7

What's the most pythonic way of using Pandas to get this new DataFrame?

C0  C1  
0   aaa:2,jjj:3  
1   bbb:7

CodePudding user response：

I had a similar approach to that of @Ch3ster, a bit different pipeline:

(df.sort_values('C1')
   .assign(C1=lambda d: d['C1'] ':' d['C2'].astype(str))
   .groupby('C0', as_index=False)['C1'].apply(','.join)
 )

Output:

   C0           C1
0   0  aaa:2,jjj:3
1   1        bbb:7

CodePudding user response：

You could sort the dataframe using DataFrame.sort_values. You can use Series.str.cat for concatenation with sep. Then groupby and use str.join.

df = df.sort_values('C1')
df["C1"].str.cat(df["C2"].astype(str), ":").groupby(df["C0"]).agg(
    ",".join
).to_frame().reset_index()

   C0           C1
0   0  aaa:2,jjj:3
1   1        bbb:7