Python pandas dataframe check if values of one column is in another list

Question

I have a Pandas dataframe:

id         attr
1          val1
2          val1||val2
3          val1||val3
4          val3

and a list special_val = ['val1', 'val2', 'val4']

I want to filter the first dataframe to keep rows whose ALL attr values are in the list. So I need the results to be like this:

id     attr
1      val1                #val1 is in special_val
2      val1||val2          #both val1 and val2 are in special_val

I am thinking of using pandas.DataFrame.isin or pandas.Series.isin but I can't come up with the correct syntax. Could you help?

Quang Hoang · Accepted Answer · 2020-02-06 19:17:11Z

2

You can combine str.split, isin(), and groupby():

s = df['attr'].str.split('\|+', expand=True).stack().isin(special_val).groupby(level=0).all()
df[s]

Output:

   id        attr
0   1        val1
1   2  val1||val2

answered Feb 6, 2020 at 19:17

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

null · Accepted Answer · 2020-02-08 11:05:56Z

1

You can try the following.

df['match'] = df['attr'].apply(lambda x: True if set(x.split('||')).intersection(set(special_val)) else False)
df[df['match'] == True]

Output

   id        attr
0   1        val1
1   2  val1||val2

edited Feb 8, 2020 at 11:05

answered Feb 6, 2020 at 19:26

null

2,1571 gold badge21 silver badges31 bronze badges

Comments

Georgina Skibinski · Accepted Answer · 2020-02-06 19:41:01Z

0

You can do:

import numpy as np
special_val = set(['val1', 'val2', 'val4'])

df["attr2"]=df["attr"].str.split("\|\|").map(set)
df=df.loc[df["attr2"].eq(np.bitwise_and(df["attr2"], special_val))].drop(columns="attr2")

Outputs:

   id        attr
0   1        val1
1   2  val1||val2

answered Feb 6, 2020 at 19:41

Georgina Skibinski

13.5k2 gold badges16 silver badges44 bronze badges

Collectives™ on Stack Overflow

Python pandas dataframe check if values of one column is in another list

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related