CODEWITHSUNDEEP
pythonpandas
eljusticiero67
eljusticiero67

Reputation: 2492

Iteratively Capture Value Counts in Single DataFrame

I have a pandas dataframe that looks something like this:

id   group  gender  age_grp  status
1    1      m       over21   active
2    4      m       under21  active
3    2      f       over21   inactive

I have over 100 columns and thousands of rows. I am trying to create a single pandas dataframe of the value_counts of each of the colums. So I want something that looks like this:

                  group1
gender  m         100
        f         89
age     over21    98
        under21   11
status  active    87
        inactive  42

Any one know a simple way I can iteratively concat the value_counts from each of the 100+ columns in the original dataset while capturing the name of the columns as a hierarchical index?

Eventually I want to be able to merge with another dataframe of a different group to look like this:

                  group1   group2
gender  m         100      75
        f         89       92
age     over21    98       71
        under21   11       22
status  active    87       44 
        inactive  42       13

Thanks!

Upvotes: 1

Views: 73

Answers (1)

piRSquared
piRSquared

Reputation: 294516

This should do it:

df.stack().groupby(level=1).value_counts()

id       1           1
         2           1
         3           1
group    1           1
         2           1
         4           1
gender   m           2
         f           1
age_grp  over21      2
         under21     1
status   active      2
         inactive    1
dtype: int64

Upvotes: 1

Related Questions