python noob here trying to delete sets of rows in a dataset that meets certain criteria. The sets of rows are in chunks over a given timestamp range, but can occur any number of times over the full timeline of valid data.
To track where the event of interest occurs, I've created an extra column "Moved" to indicate rows of interest based on a formula applied to column Value and used the assignment operator for pd.loc[(criteria), 'Moved'] = 1,0,-1;
where
Moved = 1 (nominal data and can be within bad data sets. I want to count these that aren't within the bad range),
Moved = 0 (nominal data),
Moved = -1 (bad data has at a minimum started from now, but at max 100 rows prior, and for the next few days (data period is 4s)).
I created a loop that drops a chunk of the dataframe starting from the next occurrence of Moved = -1 index until the sum of the next 100 "Moved" values=0, I stop dropping indexes. Then I search again for a -1 index start. This takes a long time and then once the out-of-range values run out, I get an indexing error. The algorithm would restart the loop and drop the next chunk of data if it finds the Moved = -1.
The pseudo-code that I've implemented is like:
while pd.Moved.loc[pd.Moved == -1].count() > 0:
good_index = ** find the next index where 100 consecutive "Moved" rows == 0 **
pd.drop(pd[(pd.index >= pd.Moved.loc[pd.Moved == -1].index[0])][:rows_to_delete].index, inplace=True
| index | Value | Moved |
|---|---|---|
| time x | remove -100 rows up to be sure | 0 |
| time1 | bad data starts | -1 |
| time x | xxx | 0 |
| time x | xxx | 0 |
| time x | xxx | 1 |
| time x | xxx | 0 |
| time x | xxx | 0 |
| time x | xxx | 0 -> 100 rows of 0 value indicates I want to stop dropping rows |
| ... | good data | >-1 |
| time 2 | bad data starts again | -1 |
| time y | xxx | 1 |
| time y | xxx | 0 |
| time y | xxx | 0 |
| time x | xxx | 0 -> 100 rows of 0, bad data is done |
| ... | good data | >-1 |