Pandas use a list of values to select rows from a column

Pandas use a list of values to select rows from a column

Filtering pandas dataframe by list of a values is a common operation in data science world. You have two main ways of selecting data:

  • select pandas rows by exact match from a list
  • filter pandas rows by partial match from a list

Related resources:

Also pandas offers big variety of options to solve those problems. I'll recommend to use vectorized operations when it's possible because it's much faster:

Vectorization is the process of executing operations on entire arrays.

So let say that we have this data(Value count for a given column):

Value Count
Another engineering discipline (ex. civil, electrical, mechanical) 6945
Information systems, information technology, or system administration 6507
A natural science (ex. biology, chemistry, physics) 3050
Mathematics or statistics 2818
Web development or web design 2418

and our goal is to find are this values part of the column and create a series with it:

area_list = ['biology', 'physics', 'Computer', 'enginnering']

to get output like:

biology physics Computer enginnering
0 False False False False
1 True True False False
2 False False True False
3 False False True False
4 False False True False

and total count:

biology physics
False 73294 85904
True 18804 6194

This can be done by using this code:

import re
area_df = pd.DataFrame(dict((area, df.UndergradMajor.str.contains(area))
                             for area in area_list))

where:

  • we create a new dataframe for the result
  • use vectorized function str.contains in order to verify if the value is part of the column
  • create a dictionary for the result of the all values

This example show a partial match. If you want to use a full match than you can use another vectorized method from pandas which is str.isin. This is how to filter rows by exact match for the values of a list:

df[df['UndergradMajor'].isin(['Mathematics or statistics', 
                              'Web development or web design'])]

This will filter the rows of the dataframe which contains exactly the values from the list.

The bonus tip for today is how to apply value_counts for the whole dataframe or several columns. This can be done by:

df.apply(pd.Series.value_counts)

the result will be:

Mobile Data QA
False 73294 70209 85904
True 18804 21889 6194

And perform value counts for several columns:

df[['Mobile','QA']].apply(pd.Series.value_counts)

the result will be:

Mobile QA
False 73294 85904
True 18804 6194
Share Tweet Send
0 Comments
Loading...