CODEWITHSUNDEEP
pythonpandasdataframe
geekProgramer
geekProgramer

Reputation: 71

combining values columns in a list in a group by

i have a DF

User   A      B     C
u1     1     0.3    5
u1     5     4      5
u1     6     1.3    2
U2     5     6.2    11
U2     1     7      6.5

the output that i want :

User   combine 
U1     [1,0.3 ,5,5 ,4, 5,6 ,1.3, 2]
U2     [5,6.2,11,1,7,6.5]

i tried :

    DF['combine'] =DF.apply(lambda x: [x['A],x['B'],x['C']],axis=1)  

User   A      B     C     combine 
u1     1     0.3    5     [1,0.3,5]
u1     5     4      5     [5,4,5]
u1     6     1.3    2     [6,1.3,2]
U2     5     6.2    11    [5,6.5,11]
U2     1     7      6.5   [1,7,6.5]

and i group by user with join

DF.groupby(['user'])['combine'].agg(lambda x:','.join([str(x) for i in x])).to_frame()

User   A      B     C     combine 
u1     1     0.3    5     [1,0.3,5] [5,4,5]....
U2     5     6.2    11    [5,6.5,11] [1,7,6.5]

the result was not accurate

Upvotes: 1

Views: 773

Answers (3)

mozway
mozway

Reputation: 261900

You can use the underlying numpy array:

(df.groupby('User', sort=False)
   .apply(lambda x:x.drop(columns='User').values.ravel().tolist())
   .to_frame(name='combine')
   .reset_index()
 )

Output:

  User                                        combine
0   u1  [1.0, 0.3, 5.0, 5.0, 4.0, 5.0, 6.0, 1.3, 2.0]
1   U2                [5.0, 6.2, 11.0, 1.0, 7.0, 6.5]

Upvotes: 1

Corralien
Corralien

Reputation: 120479

Reformat your dataframe then group by User column and transform to a list:

out = df.set_index('User').stack().groupby(level=0, sort=False).apply(list) \
        .rename('combine').reset_index()
print(out)

# Output
  User                                        combine
0   u1  [1.0, 0.3, 5.0, 5.0, 4.0, 5.0, 6.0, 1.3, 2.0]
1   U2                [5.0, 6.2, 11.0, 1.0, 7.0, 6.5]

Upvotes: 1

BENY
BENY

Reputation: 323326

We can first agg the column into list , the groupby with sum

out = df[['A','B','C']].agg(list,1).groupby(df['User'],sort=False).sum().to_frame('combine').reset_index()
Out[621]: 
  User                                        combine
0   u1  [1.0, 0.3, 5.0, 5.0, 4.0, 5.0, 6.0, 1.3, 2.0]
1   U2                [5.0, 6.2, 11.0, 1.0, 7.0, 6.5]

Upvotes: 2

Related Questions