Check values of multiple categorical columns at a same time

user_14176886 0
Viewed 92

I have multiple categorical columns like Marital Status, Education, Gender, City and I wanted to check all the unique values inside these columns at once instead of writing this code every time.

df['Education'].value_counts()

I can only give an example of a few features but I need a solution when there are so many categorical features and its not possible to write code again and again to examine them.

Maritial_Status Education City
Married         UG        LA
Single          PHD       CA
Single          UG        Ca

Expected output:

Maritial_Status   Education  City
Married        1  UG       2 LA  1 
Single         2  PHD      1 CA  2

Is there any kind of method to do this in Python? Thanks

jupyter-notebook pandas python
3 Answers

Yes, you can get what you're looking for with the following approach (also you don't have to worry about if your df has more data than the 4 columns you specified):

  1. Get (only) all your categorical columns from your df in a list:
cat_cols = [i for i in df.columns if df[i].dtypes == 'O']
  1. Then, run a loop performing .size() on your grouped object, over your categorical columns, and store each result (which is a df object) in an empty list.
li = []
for col in cat_cols:
    li.append(df.groupby([col]).size().reset_index(name=col+'_count'))
  1. Lastly, concat the newly created dataframes within your list, into 1.
dat = pd.concat(li,axis=1)

All in 1 block:

cat_cols = [i for i in df.columns if df[i].dtypes == 'O']

li = []
for col in cat_cols:
    li.append(df.groupby([col]).size().reset_index(name=col+'_count'))

dat = pd.concat(li,axis=1)# use axis=1, so that the concatenation is column-wise

  Marital Status  Marital Status_count  ...       City  City_count
0       Divorced                   4.0  ...     Athens           4
1        Married                   3.0  ...     Berlin           2
2         Single                   3.0  ...     London           2
3        Widowed                   2.0  ...   New York           2
4            NaN                   NaN  ...  Singapore           2
user_91673820

Using value_counts, you can do the following

res = (df
       .apply(lambda x: x.value_counts()) # column by column value_counts would be applied
       .stack()
       .reset_index(level=0).sort_index(axis=0)
       .rename(columns={'level_0': 'Value', 0: 'value_counts'}))

enter image description here

Another format of the the output:

res['Id'] = res.groupby(level=0).cumcount()
res.set_index('Id', append=True)

enter image description here

Explanation:

After applying value_counts, you will get the following:

enter image description here

Then using stack you can remove the NAN and get all things "stacked up" and then you can do the formatting/ ordering of the output.

user_24551690

To know how many repeated unique values you have for each column, you can try drop_duplicates() method:

dataset.drop_duplicates()
user_143410740
Related