Count number of occurences in past 14 days of certain value

Question

I have a pandas dataframe with a date column and a id column. I would like to return the number of occurences the the id of each line, in the past 14 days prior to the corresponding date of each line. That means, I would like to return "1, 2, 1, 2, 3, 4, 1". How can I do this? Performance is important since the dataframe has a len of 200,000 rows or so. Thanks !

date	id
2021-01-01	1
2021-01-04	1
2021-01-05	2
2021-01-06	2
2021-01-07	1
2021-01-08	1
2021-01-28	1

Answer 1

I am not sure whether this is the best idea or not, but the code below is what I have come up with:

from datetime import timedelta
df["date"] = pd.to_datetime(df["date"])
newColumn = []
for index, row in df.iterrows():
  endDate = row["date"]
  startDate = endDate - timedelta(days=14)
  id = row["id"]
  summation = df[(df["date"] >= startDate) & (df["date"] <= endDate) & (df["id"] == id)]["id"].count()
  newColumn.append(summation)
df["check_column"] = newColumn
df

Output

	date	id	check_column
0	2021-01-01 00:00:00	1	1
1	2021-01-04 00:00:00	1	2
2	2021-01-05 00:00:00	2	1
3	2021-01-06 00:00:00	2	2
4	2021-01-07 00:00:00	1	3
5	2021-01-08 00:00:00	1	4
6	2021-01-28 00:00:00	1	1

Explanation

In this approach, I have used iterrows in order to loop over the dataframe's rows. Additionally, I have used timedelta in order to subtract 14 days from the date column.