count function within for loop python

Question

I would like to create a count function based on a date. So it should start to count from 1 upwards till a new date is found in the dataset and then starts to count from 1 again, see example dataset and expected outcome below for an example:

data= pd.DataFrame(
    [[Timestamp('2022-08-05'), 140, 120],
    [Timestamp('2022-08-05'), 160, 155],
    [Timestamp('2022-08-06'), 230, 156],
    [Timestamp('2022-08-06'), 230, 155],
    [Timestamp('2022-08-06'), 230, 160],
    [Timestamp('2022-08-06'), 140, 130],
    [Timestamp('2022-08-07'), 140, 131],
    [Timestamp('2022-08-07'), 230, 170]],
    columns=['date', 'power', 'heart rate'])

data_expected =  pd.DataFrame(
    [[Timestamp('2022-08-05'), 140, 120, 1],
    [Timestamp('2022-08-05'), 160, 155, 2],
    [Timestamp('2022-08-06'), 230, 156, 1],
    [Timestamp('2022-08-06'), 230, 155, 2],
    [Timestamp('2022-08-06'), 230, 160, 3],
    [Timestamp('2022-08-06'), 140, 130, 4],
    [Timestamp('2022-08-07'), 140, 131, 1],
    [Timestamp('2022-08-07'), 230, 170, 2]],
    columns=['date', 'power', 'heart rate', 'count'])

what would be the best way to approach this, with a for loop?

Does this answer your question? How to add sequential counter column on groups using Pandas groupby — Rabinzel
– Rabinzel, Commented Aug 22, 2022 at 10:06

tlentali · Accepted Answer · 2022-08-22 10:19:57Z

3

From your DataFrame, we can use a groupby on the column date and the method cumcount to get the expected result :

data['count'] = data.groupby(['date']).cumcount()+1

Output :

    date        power   heart rate  count
0   2022-08-05  140     120         1
1   2022-08-05  160     155         2
2   2022-08-06  230     156         1
3   2022-08-06  230     155         2
4   2022-08-06  230     160         3
5   2022-08-06  140     130         4
6   2022-08-07  140     131         1
7   2022-08-07  230     170         2

edited Aug 22, 2022 at 10:19

answered Aug 22, 2022 at 10:07

tlentali

3,4632 gold badges18 silver badges23 bronze badges

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

Comments

0sVoid · Accepted Answer · 2022-08-22 12:08:33Z

0

data['count'] = data.groupby['date'].cumcount()

edited Aug 22, 2022 at 12:08

0sVoid

2,7881 gold badge13 silver badges26 bronze badges

answered Aug 22, 2022 at 10:20

pepijn

1117 bronze badges

Collectives™ on Stack Overflow

count function within for loop python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related