Adding a ranked column

Question

Given input as

    query   recommend
0  orange  strawberry
1  orange        pear
2  orange       lemon
3   apple     nothing
4    meat        beer
5    meat       juice

How to provide a ranked column with respect to query, starting from 1? the expected output is

    query   recommend  rank
0  orange  strawberry     1
1  orange        pear     2
2  orange       lemon     3
3   apple     nothing     1
4    meat        beer     1
5    meat       juice     2

Here's the code for input

df_output = pd.DataFrame( {'query': {0: 'orange', 1: 'orange', 2: 'orange', 3: 'apple', 4: 'meat', 5: 'meat'}, 'recommend': {0: 'strawberry', 1: 'pear', 2: 'lemon', 3: 'nothing', 4: 'beer', 5: 'juice'}} )

use `df.groupby('query').cumcount().add(1)` – Mar 08 '22 at 07:08 — , Mar 08 '22 at 07:08

keramat · Answer 1 · 2022-03-08T07:44:41.587

Use:

df = pd.DataFrame({'query':['s', 'o', 'o', 'o', 'a'], 'recommend':['f', 's', 'p', 'l', 't']})
s=df.groupby('query').agg({'query': [lambda x: list(x.index), lambda x: range(1,len(x)+1)]})
s = s.explode([s.columns[0], s.columns[1]])
s.columns = s.columns.droplevel(0)
s = s.reset_index()
df['temp']=df.index
cols = df.columns.to_list()
cols.append('<lambda_1>')
cols.remove('temp')
cols[0]='query_x'
s.merge(df, left_on='<lambda_0>', right_on='temp')[cols]

output:

Adding a ranked column

1 Answers1