Cfrgtkky

I have a big Dataframe, here is the sample data:

df['length']

353.216  

353.514  

273.559  

274.199  

353.813  

354.116 

I want to iterate over the rows and compare the i+1 with i row (and if the difference is less 2, then the value should stay, otherwise the whole row should be filtered out), I tried with Boolean indexing: diff = abs(df['length']).diff() < 2 and then df_clean = df[diff]

I want to get rid off all 'abnormal' rows. I know that every i+1 row should be in +- 2 range.
The problem is that there can be more than one row. I want to get rid of 273.559 and 274.199 (in this case), as the difference between them is less 2 I would need to iterate all the rows two times. Including a for loop to iterate over and over again doesn't seem the best approach to me, any good solutions?

Edit: My Output should look as follows:

df_clean_data ['length']

353.216  

353.514  

353.813  

354.116 

Thank you in advance
Ziga

The key to success is a function working almost like diff():

def mark(x):

    global prevX

    difr = abs(x - prevX)

    result = difr >= 2

    if not result:

        prevX = x

    return result

But the differences are that:

1. This function uses a global variable "previous x" (prevX),
   containing initially the first length (the program has to
   set it).


2. Substitution of the current x under prevX occurs only
   if the difference is less than 2. So, in this respect,
   we "skip" rows to be deleted.

The initial step is to set prevX to the 1st length:

prevX = df.loc[0, 'length']

And the actual processing is performed with a single instruction:

df.drop(df[df['length'].apply(mark)].index, inplace=True)

A bit of explanation:

• 
  df['length'].apply(mark) generates boolean array. True means "this row
  is to be deleted". For instruction purpose execute this command alone
  (before dropping).


• 
  df[...].index generates list of index values for these rows.


• 
  df.drop deletes rows with the given indices (in place).

So the whole script is like below:

import pandas as pd



def mark(x):

    global prevX

    difr = abs(x - prevX)

    result = difr > 2

    if not result:

        prevX = x

    return result



data={ 'length': [ 353.216, 353.514, 273.559, 274.199, 353.813, 354.116 ] }

df = pd.DataFrame(data)

prevX = df.loc[0, 'length']

df.drop(df[df['length'].apply(mark)].index, inplace=True)

The result is:

    length

0  353.216

1  353.514

4  353.813

5  354.116

Alternative: If you want the result in another Dataframe, delete
inplace=True and substitute the result under the target variable.

You have to iterate over your dataframe's rows like this as you can have multiple lines to filter between 2 values :

ref_row=df.iloc[0] # First line or first value you want to set as reference

valid_rows_indexes =  # Store valid lines indexes

for index, row in df.iterrows(): # Iterate over rows

    if abs(ref_row['length'] - row['length'])<2:

        valid_rows_indexes.append(index) # Append valid line index

        ref_row=row # Set this row as new reference value

df_clean_data = df.loc[valid_rows_indexes] # Filter dataframe

Hope this is helpfull.

your question is not crystal clear, but still whatever I understood I am trying to suggest some way.

1. sort the DataFrame on that column(length)




2. using for loop check for your difference




3. if you want that record add it in the new DataFrame




4. use new DataFrame

other way Because you have Big DataFrame

1. sort the DataFrame on that column(length)




2. create new column




3. using for loop check for your difference




4. if you don't want that record write np.nanin the new column




5. remove all the record which contain np.nan in new column