How to splice values of mutiple columns in a dataframe after group by?

ZhaiShang 0
Viewed 20

I have a customized requirement of splicing values of mutiple columns after group by function. Use ':' as seperator when splice each values and '#' as seperator when splice each record.

My dataframe is like:

type1 type2 id score
A      B    123  78
A      B    124  89
A      C    126  45
A      C    231  98
A      C    657  92

The result should be like:

type1 type2 result
A      B    123:78#124:89
A      C    126:45#231:98#657:92

I figured out my way to solve it:

g=df.groupby(['type1','type2'])
final=pd.DataFrame(columns=['type1','type2','result'])
for i,j in g:
    j['total']=j.apply(lambda x:x['id']+':'+str(x['score']),axis=1)
    final.loc[len(final)]=[item for item in i]+['#'.join(j['total'])]

But this way is a little complex, is there any better and simple way to do it?

scipy dataframe pandas numpy python
1 Answers

First join both columns with :, assign to new column and then aggregate join per groups by GroupBy.agg:

df = (df.assign(result = df['id'].astype(str) + ':' + df['score'].astype(str))
        .groupby(['type1','type2'])['result']
        .agg('#'.join)
        .reset_index())
print (df)
  type1 type2                result
0     A     B         123:78#124:89
1     A     C  126:45#231:98#657:92

Another idea with GroupBy.apply and custom lambda function:

df = (df.groupby(['type1','type2'])[['id','score']]
        .apply(lambda x: '#'.join(str(a)+':'+ str(b) for a, b in zip(x['id'], x['score'])))
        .reset_index(name='result'))
print (df)
  type1 type2                result
0     A     B         123:78#124:89
1     A     C  126:45#231:98#657:92
jezrael0
Related