ordering each item in dataframe with python

Question

I have a dataframe as belwo.

And when I sort with A and value of B (assume ascending sort in this example),

I want to have sub index of each item like this is first a, second a, as below table.

How can I make sub_index column like this.

Thanks in advance.

question from:https://stackoverflow.com/questions/65856940/ordering-each-item-in-dataframe-with-python

Answer 1

You can use groupby with rank with method min to get the groupwise rankings based on smallest to largest value. Next you can sort_values by this ranking and the group.

df = pd.DataFrame({'A':['a','b','a','a','b'],'B':[100,100,120,150,200]})

df['subindex'] = df.groupby('A')['B'].rank(method='min')
df = df.sort_values(['A','subindex'])
print(df)

   A    B  subindex
0  a  100       1.0
2  a  120       2.0
3  a  150       3.0
1  b  100       1.0
4  b  200       2.0