Filter pandas dataframe rows if any value on a list inside the dataframe is in another list

Question

I have a pandas dataframe that contains a list in column split_categories:

df.head()

      album_id categories split_categories
    0    66562    480.494       [480, 494]
    1   114582        128            [128]
    2     4846          5              [5]
    3     1709          9              [9]
    4    59239    105.104       [105, 104]

I would like to select all the rows where the at least one category in a specific list [480, 9, 104].

Expected output:

  album_id categories split_categories
0    66562    480.494       [480, 494]
3     1709          9              [9]
4    59239    105.104       [105, 104]

I manage to do it using apply:

def match_categories(row):
    selected_categories =  [480, 9, 104]
    result = [int(i) for i in row['split_categories'] if i in selected_categories]
    return result

df['matched_categories'] = df.apply(match_categories, axis=1)

But this code runs on production and this way takes too long (I run it for multiple columns containing lists)

Is there a way to run something like:

df[~(df['split_categories'].anyvalue.isin([480, 9, 104]))]

Thanks

Answer 1

You can convert each list to sets, get intersection and convert to bool:

L = [480, 9, 104]
mask = np.array([bool(set(map(int, x)) & set(L))  for x in df['split_categories']])

Or convert list column to DataFrame, cast to float and compare with isin:

df1 = pd.DataFrame(df['split_categories'].values.tolist(), index=df.index)
mask = df1.astype(float).isin(L).any(axis=1)

---

df = df[mask]
print (df)
  album_id categories split_categories
0    66562    480.494       [480, 494]
3     1709          9              [9]
4    59239    105.104       [105, 104]

Answer 2

You can expand the inner list, and check if any items in the inner lists are contained in [480, 9, 104]:

l = [480, 9, 104]
df[df.categories.str.split('.', expand=True).isin(map(str,l)).any(axis=1)]

   album_id  categories split_categories
0     66562     480.494        [480,494]
3      1709       9.000              [9]
4     59239     105.104        [105,104]