Count pattern frequency in pandas dataframe column that has multiple patterns

Question

I have the dataframe below:


details = {
    'container_id' : [1, 2, 3, 4, 5, 6 ],
    'container' : ['black box', 'orange box', 'blue box', 'black box','blue box', 'white box'],
    'fruits' : ['apples, black currant', 'oranges','peaches, oranges', 'apples','apples, peaches, oranges', 'black berries, peaches, oranges, apples'],
}
  
# creating a Dataframe object 

df = pd.DataFrame(details)

I want to find the frequency of each fruit separately on a list.

I tried this code

df['fruits'].str.split(expand=True).stack().value_counts()

but I get the black count 2 times instead of 1 for black currant and 1 for black berries.

Rabinzel · Accepted Answer · 2022-10-29 06:52:08Z

1

You can do it like you did, but with specifying the delimiter. Be aware that when splitting the data, you get some leading whitespace unless your delimiter is a comma with a space. To be sure just use another step with str.strip.

df['fruits'].str.split(',', expand=False).explode().str.strip().value_counts()

your way (you can also use str.strip after the stack command if you want to)

df['fruits'].str.split(', ', expand=True).stack().value_counts()

Output:

apples           4
oranges          4
peaches          3
black currant    1
black berries    1
Name: fruits, dtype: int64

answered Oct 29, 2022 at 6:52

Rabinzel

7,9533 gold badges12 silver badges31 bronze badges

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

Comments

ScottC · Accepted Answer · 2022-10-29 06:47:33Z

0

Specify the comma separator followed by an optional space:

df['fruits'].str.split(',\s?', expand=True).stack().value_counts()

OUTPUT:

apples           4
oranges          4
peaches          3
black currant    1
black berries    1
dtype: int64

edited Oct 29, 2022 at 6:47

answered Oct 29, 2022 at 6:42

ScottC

4,1251 gold badge9 silver badges22 bronze badges

Collectives™ on Stack Overflow

Count pattern frequency in pandas dataframe column that has multiple patterns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related