0

I have data that is grouped and has a value associated with it. I'm fine with working out the rank of each value within its subgroup -

df['Rank'] = df.groupby('Group')['Value'].rank(ascending=True)

However, I'd also like to create an additional column that shows the top-ranked value and second-ranked value within each group as a separate column - i.e. in the image attached below (I've not worked out how to draw a table on this website yet!). Many thanks.

Data Table

Data Table

ti7
  • 16,375
  • 6
  • 40
  • 68
Richard Dixon
  • 111
  • 6
  • 1
    Please don't post data as image, we can not copy the content from image. Take a look at [How to ask](https://stackoverflow.com/help/how-to-ask), and the [Minimal Reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) also [How to make good reproducible pandas example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – ThePyGuy Aug 19 '21 at 17:08

1 Answers1

0

  1. Get unique rank values for each group

    group_data = df[['Group','Rank']].drop_duplicates()

  2. Get rank 1 and rank 2 data

     group_rank_1 = group_data.loc[group_data.Rank==1,'Value']
 group_rank_1.columns=['Group','GroupRank1Value']

 group_rank_2 = group_data.loc[group_data.Rank==2,'Value']
 group_rank_1.columns=['Group','GroupRank2Value']

  3. Join with the original data frame

    res = df.merge(group_rank_1,how='inner', on='Group')
res = res.merge(group_rank_2,how='inner', on='Group')
Resham Wadhwa
  • 129
  • 9
  • Thanks for the help, but I'm a bit confused by this - which of the steps here is passing on the Value of the rank 1 and rank 2 before it's joined back to the table? When I follow these instructions I just get two additional columns full of 1s and then 2s? Maybe I'm misinterpreting your instructions...? – Richard Dixon Aug 19 '21 at 17:57