How to count word frequency in python dataframe?

Question

I uploaded an excel text file. I want to count the number of times each word occurs, for instance:

Output:

was 2
report 1
county 5
increase 2

Code:

 news = pd.read_excel('C:\\Users\\farid-PC\\Desktop\\Tester.xlsx')
 pd.set_option('display.max_colwidth', 1000)
 print(news)
 #implement word counter?

Current Output:

   Text
0  Trump will drop a bomb on North Korea
1  Building a wall on the U.S.-Mexico border will take literally years
2  Wisconsin is on pace to double the number of layoffs this year.
3  Says John McCain has done nothing to help the vets.
4  Suzanne Bonamici supports a plan that will cut choice for Medicare

Any help will be appreciated.

It would be helpful if you showed us a few rows of your dataset (you can cut and paste news.head().to_dict() or something like that). Also, the code you provided doesn't attempt to solve the problem, it just shows how you loaded the data. Have you attempted anything so far? — sacuL
– sacuL, Commented Oct 23, 2018 at 20:09
Possible duplicate of python - find the occurrence of the word in a file — clearshot66
– clearshot66, Commented Oct 23, 2018 at 20:09
Show us how data looks like by posting output of df.head(5). You can use collections.Counter as well to count the frequency. — mad_
– mad_, Commented Oct 23, 2018 at 20:12
I tried to use news.read() but gives an error that dataframe doesn't have attribute read. — user3658513
– user3658513, Commented Oct 23, 2018 at 20:17

cs95 · Accepted Answer · 2018-10-23 20:27:25Z

6

With pandas, using split, stack and value_counts:

series = df.Text.str.split(expand=True).stack().value_counts()

A python-based alternative using chain.from_iterable (to flatten) and Counter (to count):

from collections import Counter
from itertools import chain

counter = Counter(chain.from_iterable(map(str.split, df.Text.tolist())))

Re-create a Series of counts using:

series = pd.Series(counter).sort_values(ascending=False)

Which is identical to the pandas solution above and should be much faster since there is no stacking involved (stack is a slow operation).

answered Oct 23, 2018 at 20:27

cs95

406k106 gold badges744 silver badges797 bronze badges

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

1 Comment

user3658513 Over a year ago

Thank you for the help.

Collectives™ on Stack Overflow

How to count word frequency in python dataframe?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related