Nsryjdtyk

import pandas as pd



df1 = pd.DataFrame({'id':   [ 1,  1,  1,  2,  2,  2,  3,  3,  3], 

                    'nr':   [91, 92, 93, 91, 92, 93, 91, 92, 93], 

                    'val_a':[22, 23, 24, 33, 34, 35, 44, 43, 42]})



df2 = pd.DataFrame({'id':   [ 1,  1,  2,  3,  4,  4,  3,  5], 

                    'nr':   [91, 92, 91, 99, 92, 93, 92, 99], 

                    'val_a':[72, 27, 74, 83, 84, 85, 84, 83]})



def eliminate1 (): 

    for i1, row1 in df1.iterrows():

        for i2, row2 in df2.iterrows():

            if row1['id'] == row2['id'] and row1['nr'] == row2['nr']:

                df1.drop(i1, inplace=True)

    df1.reset_index(drop=True, inplace=True)

    print(df1)



eliminate1()

I want to drop all rows from df1, where 'id' AND 'nr' have equal values in any row of df2. eliminate1() works well, see result below, but is very slow in case of large data sets.

Here are df1 and df2:

   id  nr  val_a

0   1  91     22

1   1  92     23

2   1  93     24

3   2  91     33

4   2  92     34

5   2  93     35

6   3  91     44

7   3  92     43

8   3  93     42 



   id  nr  val_a

0   1  91     72

1   1  92     27

2   2  91     74

3   3  99     83

4   4  92     84

5   4  93     85

6   3  92     84

7   5  99     83 

And here the result as it should look like:

   id  nr  val_a

0   1  93     24

1   2  92     34

2   2  93     35

3   3  91     44

4   3  93     42

Does anyone know how to write a faster code and/or use an already existing function?

merge

You can merge with indicator=True and include only those rows marked 'left_only'.

res = df1.merge(df2.drop('val_a', 1), how='left', on=['id', 'nr'], indicator=True)

res = res.loc[res['_merge'] == 'left_only'].drop('_merge', 1)



print(res)



   id  nr  val_a

2   1  93     24

4   2  92     34

5   2  93     35

6   3  91     44

8   3  93     42

The solution is easily adaptable to any condition depending on 'left_only', 'right_only' or 'both'.

Method 1 isin after zip the merge column into tuple

df1[~df1[['id','nr']].apply(tuple,1).isin(df2[['id','nr']].apply(tuple,1))]

Out[43]: 

   id  nr  val_a

2   1  93     24

4   2  92     34

5   2  93     35

6   3  91     44

8   3  93     42

Method 2 numpy broadcast

s1=df1[['id','nr']].values

s2=df2[['id','nr']].values

df1[~np.any(np.all(s1==s2[:,None],-1),0)]

Out[64]: 

   id  nr  val_a

2   1  93     24

4   2  92     34

5   2  93     35

6   3  91     44

8   3  93     42

My method timing

%timeit df1[~df1[['id','nr']].apply(tuple,1).isin(df2[['id','nr']].apply(tuple,1))]

100 loops, best of 3: 3.67 ms per loop

def m2():

    s1 = df1[['id', 'nr']].values

    s2 = df2[['id', 'nr']].values

    return df1[~np.any(np.all(s1 == s2[:, None], -1), 0)]

%timeit m2()

1000 loops, best of 3: 926 µs per loop

Would an inner join solve your problem? Get the index of params that match the condition then filter it out. You'll just have to reset_index() afterwards if you wish to do so.

df3 = df1.merge(df2, how = 'inner', on = ['id','nr']).reset_index()

id_list = df3['id'].tolist()

df4 = df1[~df1['id'].isin(id_list)]