Selecting rows from a dataframe using a list of values

Question

I have a list of values that I would like to use to select rows in a dataframe. The trick is I would like to select any row where the list value is in the row. Example:

index    color    shape
 1       blue     star
 2       red      square
 3       yellow   circle

My list would be

list_vals = ['sq', 'blu']

I would like to select the rows

index    color   shape
1        blue    star
2        red     square

Could you explain how you selected index 3? It seems that row does not contain the required search terms in list_vals. — edesz
– edesz, Commented Apr 17, 2019 at 16:14
Yes sorry, not paying enough attention. I am interested in selecting index 1 and 2. I have made edits to the original post. — magladde
– magladde, Commented Apr 17, 2019 at 16:25

Chris Adams · Accepted Answer · 2019-04-17 16:39:22Z

3

Use DataFrame.stack to convert to a Series, then use Series.str.contains to find the strings your interested in - we'll use '|'.join to create a regex 'OR' patter combining all items from list_items.

For reference, this regex pattern looks like 'sq|blu' in this case.

Next, Series.unstack to get back to original shape and use DataFrame.any over axis 1 to create the boolean index we'll use to return the desired rows.

df[df.stack().str.contains('|'.join(list_vals)).unstack().any(1)]

[out]

   ndex color   shape
0     1  blue    star
1     2   red  square

edited Apr 17, 2019 at 16:39

answered Apr 17, 2019 at 16:17

Chris Adams

18.7k4 gold badges26 silver badges44 bronze badges

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

Comments

anky · Accepted Answer · 2019-04-17 16:19:25Z

2

Or join the list with a pipe and check with str.contains() over the df:

df[df.apply(lambda x: x.str.contains('|'.join(list_vals))).any(axis=1)]

       color   shape
index              
1      blue    star
2       red  square

answered Apr 17, 2019 at 16:19

anky

75.3k11 gold badges46 silver badges76 bronze badges

Comments

iamklaus · Accepted Answer · 2019-04-17 16:19:09Z

2

df[df['shape'].apply(lambda x: any(s in x[:len(s)] for s in list_vals))]

Output

  color   shape
1   red  square

answered Apr 17, 2019 at 16:19

iamklaus

3,7682 gold badges14 silver badges21 bronze badges

Comments

edesz · Accepted Answer · 2019-04-17 16:57:45Z

Here is one approach

df_filtered = (
    df[(df['color'].str.contains(list_vals[0])) |
        (df['shape'].str.contains(list_vals[1]))
        ]
                )

print(df_filtered)
   index color   shape
0      1  blue    star
1      2   red  square

EDIT

Another approach is based on this SO post (which contains the full explanation of this method)

the only changes I made were (1) to join your search list into a single search string and (2) to return the DataFrame (row) index of the search (filtered) results (this is then used to slice the original DataFrame)

def find_subtext(df, txt):
    contains = df.stack().str.contains(txt).unstack()
    return contains[contains.any(1)].index
df_filtered = find_subtext(df, '|'.join(list_vals))

print(df.iloc[df_filtered, :])
   index color   shape
0      1  blue    star
1      2   red  square

Collectives™ on Stack Overflow

Selecting rows from a dataframe using a list of values

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related