How do I create a list of columns which contain specified strings in their rows?

user_9936329 0
Viewed 41

I have a dataframe which has hundreds of columns, I want to return a set of the columns that contain only true/false strings.

i.e.

a     b     c  d   e
true  false 34 cat true
false false 16 dog true
true  true  16 cow false

I wish to return ['a', 'b', 'e']

All of the stack overflow questions I find seem to be searching for the string within the column header rather than within the row values.

Once it is found to contain a single example of true/false the column should be added to the list but I am wary that I would have to search the entire dataframe as it may contain a number of NULL's

My data is very large, is there a way I can optimise this search?

pandas subset dataframe list python
2 Answers

You can use DataFrame.isin assuming these are strings:

df.columns[df.isin(['true', 'false']).all()]
# Index(['a', 'b', 'e'], dtype='object')

If they are indeed boolean True/False values, you can use select_dtypes

df.infer_objects().select_dtypes(bool).columns
# Index(['a', 'b', 'e'], dtype='object')

Or, simple filtering on the dtypes:

df.columns[df.dtypes == bool]
# Index(['a', 'b', 'e'], dtype='object')
user_49090870

To complete the answer from cs95.

One other solution is to see all the possible value from a column and compare this set of value with the one you want to keep ("true", "false"). On the extra f column,as you can see, the returned value is False.

Here the code:

# Import module
import pandas as pd

df = pd.DataFrame(
    [["true",  "false", 34, "cat", "true", "true"],
     ["false", "false", 16, "dog", "true","false"],
     ["true", "true", 16, "cow", "false", "Other"],
     ["false", "false", 16, "dog", "Other","false"]],
    columns=["a", "b", "c", "d", "e", "f"]
)

# return if all values in the column are either "true" or "false"
def get_valid_column(col):
    return set(col.unique()) == set(["true", "false"])

print(df.apply(get_valid_column))
# a     True
# b     True
# c    False
# d    False
# e     True
# f    False
# dtype: bool
user_100418230
Related