Pandas: select consecutive rows where the first and last rows in the selected range form an expression

user_1917407 0
Viewed 266

My dataframe looks like this:

    Type  Pressure  Height  Temp  Dew_pt  WindDir  WindSpd
0      5     10002     158  22.8    20.2       33        2
1      4     10000     159  22.8    20.2       33        2
2      5      9947     209  22.1    20.0       37        2
3      5      9851     295  21.2    19.8       39        3
4      5      9711     419  21.0    18.6       42        2
5      5      9530     581  21.1    18.4      318        2
6      5      9314     776  20.8    17.6      281        7
7      4      9250     836  20.9    20.0      277        8
8      5      9079     999  21.0    20.5      266       12
9      5      8823    1248  20.1    20.0      253       17
10     5      8539    1531  18.1    16.0      247       21

I want to select the first range of consecutive rows such that

  • The pressure in each row in the range is at least 200 less than the pressure at index 0
  • The difference between the pressure in the first row in the range and the last row is at least 200
  • The Temp minus the Dew_pt is less than 1.5 for each row in the range.

This query would return

7      4      9250     836  20.9    20.0      277        8
8      5      9079     999  21.0    20.5      266       12
9      5      8823    1248  20.1    20.0      253       17

Row 10 would not be included because it does not satisfy the third condition.

I know I can do this with itertuples, but I'm wondering if it would be possible to do this in a faster way. I've been thinking about something like this solution but I'm not sure how to go about evaluating the first and last rows in the range for the second condition. The problem is that there is no regular interval for the pressure readings, so the range could be many rows long. Can anyone advise on how to do this query?

pandas python
1 Answers

Check with

cond1 = df.Pressure.sub(df.Pressure.iloc[0])<=-200
cond2 = (df.Temp-df.Dew_pt)<1.5
s = (~(cond1 & cond2)).cumsum()
out = df.groupby(s).Pressure.agg(np.ptp)
out = df[s.isin(out[out>200].index)].iloc[1:,]
Out[362]: 
   Type  Pressure  Height  Temp  Dew_pt  WindDir  WindSpd
7     4      9250     836  20.9    20.0      277        8
8     5      9079     999  21.0    20.5      266       12
9     5      8823    1248  20.1    20.0      253       17
user_79645270
Related