Sort to get the list of elements in first place , list of ones in 2nd place etc

Have a small dataframe (one group in a groupby, but not really relevant to this question), which looks something like this after sorting:

df = pd.DataFrame([['a', 'b', 'c'], [1,2,2], [1,3,3]]).T
df.columns = ['name', 'sorter1', 'sorter2']
sorted_df = df.sort_values(['sorter1', 'sorter2'])
name sorter1 sorter2
0 a 1 1
1 b 2 3
2 c 2 3

Want to transfer it into a dataframe of first, 2nd ad 3rd place, Got this:

result = sorted_df[['name']].T
result.columns = ['first', 'second', 'third']
first second third
name a b c

but what I really need is this:

first second third
name a ['b', 'c'] nan

any ideas, Pandas or other, are welcome, Thanks.

1 answer

  • answered 2021-06-23 07:24 Nk03

    One Way:

    df.sort_values(['sorter1', 'sorter2'], inplace=True)
    df = df.groupby(['sorter1', 'sorter2'], as_index=False, sort=False).agg(list).reindex(df.index)[['name']].T