The Problem
I have a pandas dataframe that contains series of people, the week number that a visit occurred, and their systolic and diastolic blood pressures.
ID Weeks Systolic Diastolic
1 9 140 90
1 15 155 97
2 7 140 90
2 8 121 75
2 9 161 93
3 2 160 92
3 20 139 87
3 21 140 95
3 22 145 96
4 5 155 90
4 3 150 97
What I want to do is group each patient by ID, mark when someone's blood pressure went above 140/90, and find out when a patient's blood pressure went above that value for a second time.
So for example, in the table above patient 3 has their blood pressure go above 140/90 at weeks 2, 21, and 22, so the second instance would be at week 21. The resulting dataframe would look like this then:
ID Week of Second Spike
1 15
2 9
3 21
4 5
What I've tried
I can make an indicator variable that shows where in the dataframe the blood pressure is above those values:
df['High'] = np.where((df['Systolic'] >= 140) & (df['Diastolic'] >= 90) , 1, 0)
But after that point I'm unsure of how to indicate which week is the second week of high blood pressure for each patient. I know I can also perform a groupby to group IDs together, but I'm stuck after that point.