How can you filter out columns in a DataFrame which all have the same values - for example, all categorical variables giving either Yes or No

user_16006002 0
Viewed 111

I have a large DataFrame which has multiple categorical variables which return either Yes or No. I want to filter out these specific categorical variables.

I've used this code to no avail:

#Seperating the Yes/No categorical variables
df_yn = [var for var in df.columns if df[var].unique() == pd.array(['Yes', 'No'])]

#As well as this:

#Seperating the Yes/No categorical variables
df_yn = [var for var in df.columns if df[var].unique() == (['Yes', 'No'])]

I tried running just one column with this:

df['PhoneService'].unique() == pd.array(['Yes', 'No'])

and I get this:

<BooleanArray>
[False, False]
Length: 2, dtype: boolean

Quick Update: Tried this, also doesn't work.

#Seperating the Yes/No categorical variables
temp = df['PhoneService'].unique()
df_yn = [var for var in df.columns if df[var].unique() == temp]

#output:
ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()
pandas dataframe numpy python
2 Answers

As you find out your comparison returned an array with 2 booleans. You could use all(...) to check all the values in that array are True. But what if your compared arrays are not in the same order? Or you have more unique values than planned?

>>> all(pd.array(['Yes', 'No']) == pd.array(['Yes', 'No']))
True
>>> all(pd.array(['Yes', 'No']) == pd.array(['No', 'Yes']))
False
>>> all(pd.array(['Yes', 'No']) == pd.array(['Yes', 'No', 'Foo']))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib64/python3.8/site-packages/pandas/core/ops/common.py", line 65, in new_method
    return method(self, other)
  File "/usr/lib64/python3.8/site-packages/pandas/core/arraylike.py", line 29, in __eq__
    return self._cmp_method(other, operator.eq)
  File "/usr/lib64/python3.8/site-packages/pandas/core/arrays/string_.py", line 351, in _cmp_method
    mask = isna(self) | isna(other)
ValueError: operands could not be broadcast together with shapes (2,) (3,)

There’s a “pure pandas” way that could use pd.array.equals() but that still doesn’t solve sorting:

>>> pd.array(['Yes', 'No']).equals(pd.array(['Yes', 'No']))
True
>>> pd.array(['Yes', 'No']).equals(pd.array(['No', 'Yes']))
False
>>> pd.array(['Yes', 'No']).equals(pd.array(['Yes', 'No', 'Foo']))
False

Since unique doesn’t sort for you, just make sure to use a sorted array as your basis for comparison.

>>> pd.array(['No', 'Yes']).equals(pd.array(sorted(df['PhoneService'].unique())))
True

That’s rather tedious, so we can use a number of numpy utility functions, depending on what you find most explicit:

>>> np.array_equal(['No', 'Yes'], sorted(df['PhoneService'].unique()))
True
>>> np.setdiff1d(['No', 'Yes'], df['PhoneService'].unique()).size == 0
True

Alternately you can do the comparison on all the elements and aggregate the resulting boolean series:

>>> df['PhoneService'].isin(['No', 'Yes']).all()
True
user_13873460

This won't be very performant for large dataframes as it applies the lambda function to all values in the DF. However, as a starter for ten, here's one option:

# Create dummy data – two columns with a mix of values, two with the same value
import numpy as np
import pandas as pd

df = pd.DataFrame({
    'a': np.random.choice(['Yes', 'No'], 10),
    'b': np.random.choice(['Yes', 'No'], 10),
    'c': ['Yes'] * 10,
    'd': ['No'] * 10,
})

>>> df
    a   b   c   d
0   Yes Yes Yes No
1   No  Yes Yes No
2   No  No  Yes No
3   No  No  Yes No
4   Yes Yes Yes No
5   Yes No  Yes No
6   Yes Yes Yes No
7   Yes Yes Yes No
8   Yes No  Yes No
9   No  No  Yes No

# Convert dataframe to ints
int_df = df.applymap(lambda x: 1 if x == 'Yes' else 0)

# Compute the variance of each column and use those with variance > 0 to filter the columns of the original dataframe
>>> df[int_df.var()[int_df.var() > 0].index]
    a   b
0   Yes Yes
1   No  Yes
2   No  No
3   No  No
4   Yes Yes
5   Yes No
6   Yes Yes
7   Yes Yes
8   Yes No
9   No  No
user_95768760
Related