pandas - filter dataframe by another dataframe by row elements

Solution 1:

You can do this efficiently using isin on a multiindex constructed from the desired columns:

df1 = pd.DataFrame({'c': ['A', 'A', 'B', 'C', 'C'],
                    'k': [1, 2, 2, 2, 2],
                    'l': ['a', 'b', 'a', 'a', 'd']})
df2 = pd.DataFrame({'c': ['A', 'C'],
                    'l': ['b', 'a']})
keys = list(df2.columns.values)
i1 = df1.set_index(keys).index
i2 = df2.set_index(keys).index
df1[~i1.isin(i2)]

I think this improves on @IanS's similar solution because it doesn't assume any column type (i.e. it will work with numbers as well as strings).

(Above answer is an edit. Following was my initial answer)

Interesting! This is something I haven't come across before... I would probably solve it by merging the two arrays, then dropping rows where df2 is defined. Here is an example, which makes use of a temporary array:

df1 = pd.DataFrame({'c': ['A', 'A', 'B', 'C', 'C'],
                    'k': [1, 2, 2, 2, 2],
                    'l': ['a', 'b', 'a', 'a', 'd']})
df2 = pd.DataFrame({'c': ['A', 'C'],
                    'l': ['b', 'a']})

# create a column marking df2 values
df2['marker'] = 1

# join the two, keeping all of df1's indices
joined = pd.merge(df1, df2, on=['c', 'l'], how='left')
joined

# extract desired columns where marker is NaN
joined[pd.isnull(joined['marker'])][df1.columns]

There may be a way to do this without using the temporary array, but I can't think of one. As long as your data isn't huge the above method should be a fast and sufficient answer.

Solution 2:

This is pretty succinct and works well:

df1 = df1[~df1.index.isin(df2.index)]

Solution 3:

Using DataFrame.merge & DataFrame.query:

A more elegant method would be to do left join with the argument indicator=True, then filter all the rows which are left_only with query:

d = (
    df1.merge(df2, 
              on=['c', 'l'],
              how='left', 
              indicator=True)
    .query('_merge == "left_only"')
    .drop(columns='_merge')
)

print(d)
   c  k  l
0  A  1  a
2  B  2  a
4  C  2  d

indicator=True returns a dataframe with an extra column _merge which marks each row left_only, both, right_only:

df1.merge(df2, on=['c', 'l'], how='left', indicator=True)

   c  k  l     _merge
0  A  1  a  left_only
1  A  2  b       both
2  B  2  a  left_only
3  C  2  a       both
4  C  2  d  left_only