Searching word frequency in pandas from dict

Here is the code which I am using:

import pandas as pd

data = [['This is a long sentence which contains a lot of words among them happy', 1],
       ['This is another sentence which contains the word happy* with special character', 1],
       ['Content and merry are another words which implies happy', 2],
       ['Sad is not happy', 2],
       ['unfortunate has negative conotations', 1]]
df = pd.DataFrame(data, columns=['string', 'id'])
words = {
    "positive" : ["happy", "content"],
    "negative" : ["sad", "unfortunate"],
    "neutral" : ["neutral", "000"]
    }

I want the output dataframe to look for keys in the dictionary and search for them in the dataframe but the key can be only be counted one time against an id.

Simply put:

• Group by id.
• For each group: see if at least one word in all sentences of a group is positive, negative and neutral.
• Then sum up the counts for all groups.

For example.

    string  id
0   This is a long sentence which contains a lot o...   1
1   This is another sentence which contains the wo...   1
2   Content and merry are another words which impl...   2
3   Sad is not happy    2
4   unfortunate has negative connotations   1

The id “1” in row number 0 and 1 both contain the dict values for key positive. Thus positive can be counted only 1 time for id 1. Also in the last row it contains the word “unfortunate” thus.

For id 1

positive : 1

negative : 1

neutral : 0

After all ids are summed up, the final dataframe should look like this:

word        freq
positive     2
negative     2
neutral      0

Could you please advise how this can be accomplished in pandas

Answer

This is efficient because any() short circuits (stops evaluation at the first value that matches).

texts = df.groupby('id')[['string']].agg(lambda x: ' '.join(x))

for k, v in words.items():
    texts[k] = texts['string'].transform(
        lambda text: any(word.lower() in text.lower() for word in v)
        )

result = texts[words.keys()].sum(axis=0)

---

result is a Series:

positive    2
negative    2
neutral     0
dtype: int64

You can convert it to a DataFrame like this:

result_df = result.to_frame().reset_index().set_axis(['word', 'freq'], axis=1)

       word  freq
0  positive     2
1  negative     2
2   neutral     0