Adding an index in Pandas Dataframe

Question

I have this dataframe in Pandas:

id   animal
0    dog
0    cat
1    goat
1    cow
1    sheep
1    pig
2    lion
2    tiger
2    bear

I want to add a column as follows. I don't know what you call it but basically it's an index for each unique id.

id   animal   ix
0    dog      0
0    cat      1
1    goat     0
1    cow      1
1    sheep    2
1    pig      3
2    lion     0
2    tiger    1
2    bear     2

Notice how it increments going down for whenever it continues to see the same id but it resets back to 0 whenever a new id has been found. Anyone knows the best way it could be done? I was thinking of obtaining all unique id's and increment an ix for each row of the same id that I see. Is there a better way to do it? (Side note, I shouldn't call it id since it's not unique in the df but I just can't think of a better column name)

@Rodalm yes, exactly what I need. Thanks! I didn't know this function exists — M. Fire
– M. Fire, Commented Aug 22, 2022 at 19:23

Rodalm · Accepted Answer · 2022-08-22 19:27:27Z

1

Group by id and use GroupBy.cumcount

df['ix'] = df.groupby('id').cumcount()

Output:

>>> df

   id animal  ix
0   0    dog   0
1   0    cat   1
2   1   goat   0
3   1    cow   1
4   1  sheep   2
5   1    pig   3
6   2   lion   0
7   2  tiger   1
8   2   bear   2

answered Aug 22, 2022 at 19:27

Rodalm

5,7589 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding an index in Pandas Dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related