Add simple counter column in pandas dataframe which resets when the date changes

Question

How can I add a simple counter column in a pandas dataframe which resets when the date changes? The date is a to_datetime object. Here is the desired result:

Date          Counter
2009-03-20    0
2009-03-20    1
2009-03-20    2
2009-03-20    3
2009-03-20    4
2009-03-20    5
2009-03-25    0
2009-03-25    1
2009-03-25    2
2009-03-25    3
2009-03-26    0
2009-03-26    1
2009-03-26    2
2009-03-26    3
2009-03-26    4
2009-03-26    5

`df.groupby('Date').cumcount()`, can't find the duplicate link, but it is. — Erfan, Jan 03 '21 at 20:52
Ah, I seem to have pasted my response and only now seen this in comments... — Danail Petrov, Jan 03 '21 at 20:55
Maybe this: https://stackoverflow.com/questions/23435270/how-to-add-sequential-counter-column-on-groups-using-pandas-groupby — perl, Jan 03 '21 at 20:58

score 0 · Answer 1 · answered Jan 03 '21 at 20:53

0

You need to gropuby date & cumcount:

>>> df.groupby('Date').cumcount()
0     0
1     1
2     2
3     3
4     4
5     5
6     0
7     1
8     2
9     3
10    0
11    1
12    2
13    3
14    4
15    5

answered Jan 03 '21 at 20:53

Danail Petrov

1,875
10
12

Add simple counter column in pandas dataframe which resets when the date changes

1 Answers1