pandas get duplicate level for each duplicate entry

user_1977867 0
Viewed 26

I have a df like

    type   val greet
0   foo    0   hi
1   foo    1   hello
2   foo    1   bye
3   bar    1   come
4   foo    0   go
5   foo    0   no

I want to find duplicates based on type and val (both together) like type=foo and val=0 is duplicated 3 times But I need to get duplicate instance value as well like

    type   val greet  instance
0   foo    0   hi     1
1   foo    1   hello  1
2   foo    1   bye    2
3   bar    1   come   1
4   foo    0   go     2
5   foo    0   no     3

Tried df.drop_duplicates(subset=['type', 'val'], keep=first) but this is not the correct way to approach. Also read this I think it will be done using groupby but adding instance count is still not clear to me.

pandas python
1 Answers

Group by 'type' and 'val', apply cumcount and add one.

>>> df['instance'] = df.groupby(['type', 'val']).cumcount() + 1
>>> df
  type  val  greet  instance
0  foo    0     hi         1
1  foo    1  hello         1
2  foo    1    bye         2
3  bar    1   come         1
4  foo    0     go         2
5  foo    0     no         3
user_36200030
Related