Apply filter repeatedly until condition exhausted in Python

user_11901732 0
Viewed 81

I would like to filter a dataframe by removing pairs that satisfy some conditions repeatedly until the condition is exhausted.

df:

        Date        ITEM_ID TYPE    VALUE
342874  2012-06-18  ECE240  O       1.0 
342415  2012-06-18  ECE240  O       25.0
325718  2012-06-18  ECE240  O       1.0 
334488  2012-06-18  ECE240  O       1.0 
342412  2012-06-18  ECE240  O       25.0
341634  2012-06-18  ECE240  O       9.0
341996  2012-06-19  ECE240  O       2.0 
341747  2012-06-19  ECE240  O       1.0 
272185  2012-06-24  ECE240  I       1.0 
219     2012-06-24  ECE240  I       1.0 
6896    2012-06-24  ECE240  I       2.0 
351560  2012-06-24  ECE240  O       1.0 
312636  2012-06-26  ECE240  I       1.0 
2376    2012-06-30  ECE240  I       1.0 
350922  2012-07-02  ECE240  O       1.0 
270589  2012-07-09  ECE240  I       4.0
331689  2012-07-15  ECE240  I       1.0 
299912  2012-07-23  ECE240  I       1.0 
212418  2012-07-23  ECE240  I       3.0
3992    2012-07-24  ECE240  I       2.0
388937  2012-08-10  ECE240  O       10.0
124596  2012-08-18  ECE240  I       1.0 
368945  2012-08-19  ECE240  O       12.0
368944  2012-08-19  ECE240  O       6.0
239581  2012-08-24  ECE240  I       4.0

The conditions for any two rows to satisfy as a pair are:

  1. The two rows have same the ITEM_ID;
  2. The two rows have same the VALUE;
  3. The row in front has 'TYPE==O' and the row behind has 'TYPE==I'

Code:

mask = df.sort_values('Date')\
  .groupby(['ITEM_ID', 'VALUE'])['TYPE']\
  .apply(lambda x: ((x == 'O') & (x.shift(-1) == 'I')) | (x == 'I') & (x.shift(1) == 'O'))

df= df.loc[~mask]
df

I could do it manually by applying the filter repeatedly for a few times but I am not sure how many times I need to apply the filter before the condition is exhausted. I wonder if it's possible to do it with a while loop, maybe something like:


def myfunc(df):

    mask = df.sort_values('Date')\
    .groupby(['ITEM_ID', 'VALUE'])['TYPE']\
    .apply(lambda x: ((x == 'O') & (x.shift(-1) == 'I')) | (x == 'I') & (x.shift(1) == 'O'))

    df = df.loc[~mask]

    return df


while True:
    myfunc(df)

but it is taking very long to run the function(a few minutes and still running), as compared to doing it manually, which only takes a few seconds in total.

pandas while-loop function python
1 Answers

You can put your code in while loop and break the code whenever cur_length and new_length is same i.e. no more matches remaining.

def myfunc(df):

    mask = df.sort_values('Date')\
    .groupby(['ITEM_ID', 'VALUE'])['TYPE']\
    .apply(lambda x: ((x == 'O') & (x.shift(-1) == 'I')) | (x == 'I') & (x.shift(1) == 'O'))
    df = df.loc[~mask]
    return df


while True:
    cur_length = len(df)
    df = myfunc(df)
    new_len = len(df)
    print(cur_length ,new_len)
    if cur_length == new_len:
        break
df

Output:

Date    ITEM_ID TYPE    VALUE
2012-06-18  ECE240  O   25.0
2012-06-18  ECE240  O   25.0
2012-06-18  ECE240  O   9.0
2012-07-09  ECE240  I   4.0
2012-07-23  ECE240  I   3.0
2012-07-24  ECE240  I   2.0
2012-08-10  ECE240  O   10.0
2012-08-18  ECE240  I   1.0
2012-08-19  ECE240  O   12.0
2012-08-19  ECE240  O   6.0
2012-08-24  ECE240  I   4.0
user_56602840
Related