Count values across multiple columns with grouping variable

Question

I have a pandas dataframe that contains data that looks like this:

levels char_1 char_2
a      dog    dog
a      cat    dog
b      cow    cat
b      dog    dog

I'd like to group_by the levels column and compute the total number of times that a value appears either in the char_1 column or char_2 column

The resulting dataframe would look like:

levels char  count
a      dog    3
a      cat    1
b      dog    2
b      cow    1
b      cat    1

I've experimented with pivot tables, but can't wrap my head around pandas syntax.

jezrael · Accepted Answer · 2020-07-03 05:59:17Z

4

Use DataFrame.melt for unpivot and then GroupBy.size for counts:

df1 = (df.melt('levels', value_name='char')
         .groupby(['levels','char'])
         .size()
         .reset_index(name='count'))
print (df1)
  levels char  count
0      a  cat      1
1      a  dog      3
2      b  cat      1
3      b  cow      1
4      b  dog      2

answered Jul 3, 2020 at 5:59

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parseltongue Over a year ago

Shockingly fast answer. Of course! The answer is always melt. Thank you.

Collectives™ on Stack Overflow

Count values across multiple columns with grouping variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related