RageQuilt RageQuilt - 4 months ago 17
Python Question

Removing values in dataframe once threshold (min/max) value has been reached with Pandas

I would like to make a filter for the entire dataframe, which includes many columns beyond column C. I'd like this filter to return values in each column once a minimum threshold value has been reached, and stop when a maximum threshold value has been reached. I'd like the min threshold to be 6.5 and the max to be 9.0. It's not as simple as it sounds here so hang with me...

The dataframe:

Time A1 A2 A3
1 6.305 6.191 5.918
2 6.507 6.991 6.203
3 6.407 6.901 6.908
4 6.963 7.127 7.116
5 7.227 7.330 7.363
6 7.445 7.632 7.575
7 7.710 7.837 7.663
8 8.904 8.971 8.895
9 9.394 9.194 8.994
10 8.803 8.113 9.333
11 8.783 8.783 8.783


The desired result:

Time A1 A2 A3
1 NaN NaN NaN
2 6.507 6.991 NaN
3 6.407 6.901 6.908
4 6.963 7.127 7.116
5 7.227 7.330 7.363
6 7.445 7.632 7.575
7 7.710 7.837 7.663
8 8.904 8.971 8.895
9 NaN NaN 8.994
10 NaN NaN NaN
11 NaN NaN NaN


To drive home the point, in Column A, for example, at Time 3 there is a value 6.407, which is lower than the 6.5 threshold, but since the threshold was met at Time 2, I would like to keep the data once the min threshold has been met. As for the upper threshold, in Column A at Time 9, the value is above the 9.0 threshold, so I would like it to omit that value and the values beyond that, even though the remaining values are less than 9.0. I'm hoping to iterate this over many many more columns.

Thank you!!!

Answer

Try this:

df 
        A1     A2     A3
Time                     
1     6.305  6.191  5.918
2     6.507  6.991  6.203
3     6.407  6.901  6.908
4     6.963  7.127  7.116
5     7.227  7.330  7.363
6     7.445  7.632  7.575
7     7.710  7.837  7.663
8     8.904  8.971  8.895
9     9.394  9.194  8.994
10    8.803  8.113  9.333
11    8.783  8.783  8.783

df2 = df > 6.5 
df  = df[df2.cumsum()>0]
df2 = df > 9   
df  = df[~(df2.cumsum()>0)]

df 
         A1     A2     A3
Time                     
1       NaN    NaN    NaN
2     6.507  6.991    NaN
3     6.407  6.901  6.908
4     6.963  7.127  7.116
5     7.227  7.330  7.363
6     7.445  7.632  7.575
7     7.710  7.837  7.663
8     8.904  8.971  8.895
9       NaN    NaN  8.994
10      NaN    NaN    NaN
11      NaN    NaN    NaN
Comments