I have a DataFrame as following.
df = pd.DataFrame({'col1': ['a','b','c','c','d','e','a','h','i','a'],'col2':['3:00','3:00','4:00','4:00','3:00','5:00','5:00','3:00','3:00','2:00']})
df
Out[83]:
col1 col2
0 a 3:00
1 b 3:00
2 c 4:00
3 c 4:00
4 d 3:00
5 e 5:00
6 a 5:00
7 h 3:00
8 i 3:00
9 a 2:00
What I'd like to do is groupby 'col1' and assign a unique ID to different values in col2 as following:
col1 col2 ID
a 2:00 0
a 3:00 1
a 5:00 2
b 3:00 0
c 4:00 0
c 4:00 0
...
I tried to use pd.Categorical but can't quite get to where I wanted to be.