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.


1 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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.