pandas dataframe group rows based on specific column [duplicate]

Question

I have a table that looks like this:

   P_id   S_id   Time
1  20     A    15 
2  30     B    50
3  50     A    99 
4  70     A    60

I want to group the table, based on the column "Sid", and sorted by Column "Time" so it will look like this:

     P_id       S_id   
1  20,70,50       A     
2    30           B

What is the best way to do this?

Where does the 99 come from in the P_id column of the result? — timgeb
– timgeb, Commented Dec 13, 2018 at 10:21
It should be 50 instead of 99 in P_id column of the expected output. — Mayank Porwal
– Mayank Porwal, Commented Dec 13, 2018 at 10:22
Use df = df.sort_values('Time') .groupby('S_id', sort=False)['P_id'].agg(lambda x: ','.join(x.astype(str))).reset_index() — jezrael
– jezrael, Commented Dec 13, 2018 at 10:23

Jorge · Accepted Answer · 2018-12-13 10:52:12Z

0

You can try this. df here is the name of your data frame

import pandas as pd
df2 = pd.DataFrame({'S_id': ['A', 'B']})
df2.loc[:,'P_id'] = ''
for letter in df2.S_id.unique():
    indx = df2.loc[df2['S_id']==letter].index.values
    df1 = df.sort_values(by = ['S_id' ,'Time'])
    array_values = list(df1[df1.S_id ==letter].P_id.values)#.astype(object)
    df2.at[indx[0], 'P_id'] = array_values
df2

answered Dec 13, 2018 at 10:52

Jorge

2,2491 gold badge24 silver badges32 bronze badges

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

2 Comments

oren_isp Over a year ago

Is it possible to do it in the SQL query that generated that table? (Select P_ids, S_id, time from T)

Jorge Over a year ago

@oren_isp , I am sure you can do it in SQL. Not sure your suggested command will work

Collectives™ on Stack Overflow

pandas dataframe group rows based on specific column [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related