Map discrete columns to the index of their unique values

Question

I have a dataframe with an int columns:

df=pd.DataFrame(data=2*np.random.randint(0,high=10,size=5),columns=['N'])



    N

0   8

1   4

2   8

3  14

4   2

5  18

I would like to generate another dataframe as:

where

ID

is the index of the sorted list of unique values in N

I would need a computationally cheap solution as it needs to run on large dataframes and be updated very often.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Use rank + sub + astype:

df['ID'] = df['N'].rank(method='dense').sub(1).astype(int)
print (df)
    N  ID
0   8   2
1   4   1
2   8   2
3  14   3
4   2   0
5  18   4

Map discrete columns to the index of their unique values

Sign up