how to combine multiple columns and multiple rows based on group by of another column

Question

I am trying to combine multiple columns and rows into single column based on group by field of ID column. The input is

|Id |   Sample_id | Sample_name |   Sample_number|
|:--|:------------|:-----------:|---------------:|             
|1  |  123        | Abcdef|ghij |  1234567       |
|1  |   345       | Vbnhj|tt|t  |   45678        |
|1  |   456       | Ffff|yyy|yy |   789000       |

Expected output : columns,rows belonging to same Id should be combined and form a list like below.

Id  Sample_details              
    123,Abcdef|ghij,1234567
 1  345,Vbnhj|tt|t, 45678       
    456 ,Ffff|yyy|yy,789000

 2   536 ,Ftff|uyy|iy,79000
     453, hnhj|tdd|rr, 67678

I tried below which is not working

df.groupby('Id')['Sample_id']['Sample_name']['Sample_number'].apply(','.join).reset_index()

Your outcome is not clear. Do you want to join by ',' for each row and join rows by '\n' to keep only one Id? So what do you mean by combined and form a list like? — Corralien
– Corralien, Commented Sep 23, 2021 at 12:42
yes you are correct , i want to join each column by , and each row by \n , for the grouped id. — behappy
– behappy, Commented Sep 23, 2021 at 12:55

Corralien · Accepted Answer · 2021-09-23 13:10:23Z

1

Try:

df['Sample_details'] = df.filter(like='Sample_').astype(str).apply(','.join, axis=1)

out = df.groupby('Id')['Sample_details'].apply('\n'.join).reset_index()

Output:

>>> out
   Id                                     Sample_details
0   1  123,Abcdef|ghij,1234567\n345,Vbnhj|tt|t,45678\...

Note: Pandas does not interpret escape sequence.

edited Sep 23, 2021 at 13:10

answered Sep 23, 2021 at 12:37

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

behappy Over a year ago

sorry, im not expecting this. all the list should be in single column row, i dont want 3 different rows in sample details. i'm updating the format in my question how my output should be represented. Thanks

behappy Over a year ago

I have updated question with the expected output format

Corralien Over a year ago

@behappy. I updated my answer too if you want to check.

Collectives™ on Stack Overflow

how to combine multiple columns and multiple rows based on group by of another column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related