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.

Answer 1

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).