How to count word occurrences in a text column against values in another column?

Question

I have a data frame with a column of text and another column (score) that describes a score for the text and a column (score_label) that provides a label 'b' or 'h' according to the respective scores. The text is sentences that may or may not contain the key terms that I am looking for. I want to count the number of rows of the column 'text' that contain my list of key terms for each of the values in the 'score_label' column, i.e., for 'b' and 'h' separately. I am trying to modify the following code such that it provides the value counts according to the score_label:

df['text'].str.lower().str.contains('key').value_counts(normalize=True)

Here's a sample dataframe:

df = pd.DataFrame({'id': [10, 46, 75, 12, 99, 84],
                   'text': ['John passed the course',         
                            'The highest score was Annas',
                            '',
                            'The grades are all up.',
                            'Annas score was higher than johns',
                            'Paul did just fine.'],
                   'score': [0.2, 4.3, 6.3, 1.2, 0.9, 5.4],
                   'score_label': ['h', 'h', 'b', 'h', 'h', 'b']
                                   })

I tried the following code but it doesn't work:

key = ['john', 'Anna']
df['text'].apply(lambda x: df['text'].str.lower().str.contains('key').value_counts() for x in df['score_label'])

I have also tried the following loop:

def term_count(terms):
    print(df_btw_all['text'].str.lower().str.contains(terms).sum()   
key = ['john', 'anna']
for k in key:
    if df.loc[df['score_label']=='b']:
        term_count(k)

but it throws a ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I would really appreciate it if someone could suggest a fix.

Answer 1

I want to count the number of rows of the column 'text' that contain my list of key terms for each of the values in the 'score_label' column, i.e., for 'b' and 'h' separately.

Are you looking for something like this:

keys = ['john', 'Anna']
pattern = r"(?i)" + "|".join(keys)
result = df["text"].str.contains(pattern).groupby(df["score_label"]).sum()

Result:

score_label
b    0
h    3
Name: text, dtype: int64

Or (with the same pattern):

df["match"] = df["text"].str.contains(pattern)
result = df.groupby("score_label")[["match"]].sum()

Result:

             match
score_label       
b                0
h                3

Answer 2

I'm not exactly sure what you try to achieve and did not fix the code entirely. Please rewrite the question so it's easier to understand.

I fixed some general issues in your code (missing bracket, wrong variable) but its not executeable yet.

Also added all to your if statement. If can only handle a single boolean value. df.loc[df['score_label']=='b' returns an array of booleans.

import pandas as pd
df = pd.DataFrame({'text': [['John passed the course'],         
                            ['The highest score was Annas'],
                            [],
                            ['The grades are all up.'],
                            ['Annas score was higher than johns'],
                            ['Paul did just fine.']],
                   'score': [0.2, 4.3, 6.3, 1.2, 0.9, 5.4],
                   'score_label': [['h'], ['h'], ['b'], ['h'], ['h'], ['b']]
                                   })
def term_count(terms):
    print(df['text'].str.lower().str.contains(terms).sum())
    
key = ['john', 'anna']
for k in key:
    if all(df.loc[df['score_label']=='b']):
        term_count(k)