Dataframe isin() on list of dicts which have list as property value

Question

i want to get the objects which contain a certain value stored in their property list "value" in this case.

import pandas as pd

mydataset = [{"name": "test", "values": ["3", "7", "2"]}, {"name": "test2", "values": ["4", "1", "7"]}]

myvar = pd.DataFrame.from_records(mydataset)

print(myvar[myvar["values"].isin(["3"])])

This codes gives an empty dataframe back.

If i initialize the dataframe with single object it works. How can i filter them when the list is a property value?

Answer 1

You cona use apply() theis way:

import pandas as pd

mydataset = [{"name": "test", "values": ["3", "7", "2"]}, {"name": "test2", "values": ["4", "1", "7"]}]
myvar = pd.DataFrame.from_records(mydataset)

filtered_data = myvar[myvar["values"].apply(lambda x: "3" in x)]
print(filtered_data)

which gives

   name     values
0  test  [3, 7, 2]

Answer 2

You have to loop over the values.

If you have a single target:

target = '3'

out = myvar[[any(val == target for val in l) for l in myvar['values']]]

Or, if you want to match several values (all of the targets):

target = {'3', '7'}

out = myvar[[target.issubset(l) for l in myvar['values']]]

any of the targets:

target = {'3', '10'}

out = myvar[[bool(target.intersection(l)) for l in myvar['values']]]

Output:

   name     values
0  test  [3, 7, 2]