Pandas groupby with no function and display each unique value only once

user_7191927 0
Viewed 39

I'm not sure Pandas does this or not.

Given a dataframe that looks like this:

enter image description here

I want to group by Id and then list sorted by descending order on Score, like this:

Id        Name              Score   Index
-------------------------------------------
123       John Smith        1.0     AM
          Johnny Smith      0.92    PP
345       John Smith        1.0     WL
789       John Smith        1.0     WL
          Jonathan Smith    0.91    PP
011       Jon Smithson      0.80    AM
012       Jon Smythe        0.77    WL

One of the main requirements here is that we only want each distinct Id to be displayed one time. If there is a way to accomplish this but the format is not exactly like the above, but conveys the same message, that's ok. Note that there is no function to be applied to any column (mean, sum, etc.).

Here is the code to reproduce the Dataframe:

import pandas as pd

arrays = [['123', '345', '789', '123', '789', '011','012'],
        ['John Smith', 'John Smith', 'John Smith', 'Johnny Smith', 'Jonathan Smith', 'Jon Smithson', 'John Smythe'],
          [1.0, 1.0, 1.0, 0.92, 0.91, 0.80, 0.77],
          ['AM', 'WL','WL', 'PP', 'PP', 'AM', 'WL']]
index = pd.MultiIndex.from_arrays(arrays, names=('Id', 'Name', 'Score', 'Index'))
df = pd.DataFrame(index=index)
df
pandas-groupby pandas
1 Answers

You could do this:

df = df.sort_values('Id').set_index(['Id', 'Name'])

>>> print(df)
                    Score Index
Id  Name                       
11  Jon-Smithson     0.80    AM
12  Jon-Smythe       0.77    WL
123 John-Smith       1.00    AM
    Johnny-Smith     0.92    PP
345 John-Smith       1.00    WL
789 Jonathan-Smith   0.91    PP
    John-Smith       1.00    WL

Or this:

df = df.sort_values('Id').set_index('Id')

>>> print(df)
               Name  Score Index
Id                              
11     Jon-Smithson   0.80    AM
12       Jon-Smythe   0.77    WL
123      John-Smith   1.00    AM
123    Johnny-Smith   0.92    PP
345      John-Smith   1.00    WL
789  Jonathan-Smith   0.91    PP
789      John-Smith   1.00    WL

If the Id column is numeric (of dtype int) and you want it to be zero-padded, you can do the following, and then use one of the above solutions, substituting new_df for df:

i = df['Id'].astype(str)
i = i.str.rjust(i.str.len().max(), '0')
new_df = df.copy()
new_df['Id'] = i

e.g.

>>> new_df = new_df.sort_values('Id').set_index(['Id', 'Name'])
>>> print(new_df)
                    Score Index
Id  Name                       
011 Jon-Smithson     0.80    AM
012 Jon-Smythe       0.77    WL
123 John-Smith       1.00    AM
    Johnny-Smith     0.92    PP
345 John-Smith       1.00    WL
789 Jonathan-Smith   0.91    PP
    John-Smith       1.00    WL
Admin1
Related