Count how many times a column contains a certain value in Pandas

Question

Let's say my dataframe looks like this:

   column_name
1  book
2  fish
3  icecream|book
4  fish
5  campfire|book

Now, if I use df['column_name'].value_counts() it will tell me fish is the most frequent value.

However, I want book to be returned, since row 1, 3 and 5 contain the word 'book'.

I know .value_counts() recognizes icecream|book as one value, but is there a way I can determine the most frequent value by counting the amount of times each column cell CONTAINS a certain value, so that 'book' will the most frequent value?

Answer 1

Use split with stack for Series:

a = df['column_name'].str.split('|', expand=True).stack().value_counts()
print (a)
book        3
fish        2
icecream    1
campfire    1
dtype: int64

Or Counter with list comprehension with flattening:

from collections import Counter

a = pd.Series(Counter([y for x in df['column_name'] for y in x.split('|')]))
print (a)
book        3
fish        2
icecream    1
campfire    1
dtype: int64