How to group by one column and find second occurance of values greater than a threshold

Question

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.

Answer 1

I found one solution thanks to this SO thread. What I ended up doing was the following:

First, I made the indicator variable described in the question:

df['High'] = np.where((df['Systolic'] >= 140) & (df['Diastolic'] >= 90) , 1, 0)

Then I made a cumulative sum to count how many times this person has had a blood pressure spike by a specific week.

df['Prev_highBP'] = df.groupby(['ID'])['High'].cumsum().astype(int)

Then I filtered for people with high blood pressure, grouped by ID, then made a new variable to hold the second smallest week within that group.

grouped = df.loc[testPre['High']==1].groupby(['ID'])['Week']
df['second_spike'] = grouped.transform(lambda x: x.nsmallest(2).max())

Finally I fixed the cases where there was only one blood pressure spike using the cumulative sum variable created earlier.

df['second_spike'] = np.where((df['Prev_highBP'] != 2) , np.NaN, df['second_spike'])

I can get the dataframe I wanted then by just dropping the duplicates:

secondSpike = df.drop_duplicates(subset=['ID'], keep='last')

There's likely a cleaner or more efficient way to do this, but it seems to work for the moment.