[Python] In Python, how to compare the value to each subsequent value in a row until a...

I have a table in the general structure below:



example code:

import pandas as pd
data = {
'ID': [117, 117, 117, 117, 117, 117],
'Date': ['2023-11-14', '2024-01-25', '2024-02-01', '2024-02-04', '2024-02-11', '2024-03-04']
}
df = pd.DataFrame(data)


What I would like to do is, compare the first row to the next row, until the difference between the dates meets some threshold, say 30 days. Then, once that row meets the threshold, I'd like to then test the next row against the subsequent row. It would look like this:

Result, using threshold of 30:



So to reiterate, its comparing the FIRST row to subsequent rows until some threshold is met. Then the count starts over at the first rep after that within the group to subsequent rows within the group.

I've tried this but so far I haven't been able to get the right rowwise comparison to even implement the thresholding bit.

thres = 30

i = 0
control = True
for record in df['Date']:
if record > thres:
print(i, 'in position!', i)
control = False
i += 1


Update

I want to do the above with groups based on ID.



example code:

import pandas as pd
data = {
"ID": [117, 117, 117, 117, 117, 117, 118, 118, 118, 118, 118, 118],
"Date": ["2023-11-14", "2024-01-25", "2024-02-01", "2024-02-04", "2024-02-11", "2024-03-04",
"2024-01-02", "2024-01-28", "2024-02-04", "2024-02-18", "2024-03-11", "2024-06-05"]
}

df = pd.DataFrame(data)


desired output:



Continue reading...