Assigning a number by ID by date

Question

This is an example of my CSV data:

[1] https://i.stack.imgur.com/5mOvb.png

How to assign an attempt number for each ID by date (incrementing)? For example, in the pictured piece should be two attempts. TYSM!

Answer 1

Use:

#preparing data
import pandas as pd
df = pd.DataFrame({'id':[3,3,3,3,3,3], 'date': pd.date_range('2021.5.11','2021.5.13', 6)})
df['date'] = df['date'].dt.date

#solution
df['attampt'] = df.groupby(['id', 'date'])['id'].transform(lambda x: range(1, len(list(x))+1))
df

Output:

    id  date    attampt
0   3   2021-05-11  1
1   3   2021-05-11  2
2   3   2021-05-11  3
3   3   2021-05-12  1
4   3   2021-05-12  2
5   3   2021-05-13  1

Answer 2

If you want an ID by date, one option is to .shift() the column you want to count the changes by 1 and compare this to the original. You have to fill the one existing Nan with .fillna(). If the shift is not equal to the original, than you get a Ture and this can be counted by .cumsum().

import pandas as pd
df = pd.DataFrame({'Test Date':['1/10/2017', '1/10/2017', '1/10/2017', '11/4/2016', '11/4/2016']})
df['Attempt'] = (df['Test Date'] != df['Test Date'].shift(1)).fillna(True).cumsum()
>>> df
   Test Date  Attempt
0  1/10/2017        1
1  1/10/2017        1
2  1/10/2017        1
3  11/4/2016        2
4  11/4/2016        2