Conditional word frequency count in Pandas

Question

I have a dataframe like below:

data = {'speaker':['Adam','Ben','Clair'],
        'speech': ['Thank you very much and good afternoon.',
                   'Let me clarify that because I want to make sure we have got everything right',
                   'By now you should have some good rest']}
df = pd.DataFrame(data)

I want to count the number of words in the speech column but only for the words from a pre-defined list. For example, the list is:

wordlist = ['much', 'good','right']

I want to generate a new column which shows the frequency of these three words in each row. My expected output is therefore:

     speaker                   speech                               words
0   Adam          Thank you very much and good afternoon.             2
1   Ben        Let me clarify that because I want to make sur...      1
2   Clair        By now you should have received a copy of our ...    1

I tried:

df['total'] = 0
for word in df['speech'].str.split():
    if word in wordlist: 
        df['total'] += 1

But I after running it, the total column is always zero. I am wondering what's wrong with my code?

Answer 1

You could use the following vectorised approach:

data = {'speaker':['Adam','Ben','Clair'],
        'speech': ['Thank you very much and good afternoon.',
                   'Let me clarify that because I want to make sure we have got everything right',
                   'By now you should have some good rest']}
df = pd.DataFrame(data)

wordlist = ['much', 'good','right']

df['total'] = df['speech'].str.count(r'\b|\b'.join(wordlist))

Which gives:

>>> df
  speaker                                             speech  total
0    Adam            Thank you very much and good afternoon.      2
1     Ben  Let me clarify that because I want to make sur...      1
2   Clair              By now you should have some good rest      1

Answer 2

This is a much faster (runtime wise) solution, if you have a very large list and a large data frame to search through.

I guess it is because it takes advantage of a dictionary (which takes O(N) to construct and O(1) to search through). Performance wise, regex search is slower.

import pandas as pd
from collections import Counter

def occurrence_counter(target_string, search_list):
    data = dict(Counter(target_string.split()))
    count = 0
    for key in search_list:
        if key in data:
            count+=data[key]
    return count

data = {'speaker':['Adam','Ben','Clair'],
        'speech': ['Thank you very much and good afternoon.',
                   'Let me clarify that because I want to make sure we have got everything right',
                   'By now you should have some good rest']}
df = pd.DataFrame(data)

wordlist = ['much', 'good','right']

df['speech'].apply(lambda x: occurrence_counter(x, wordlist))