Python - Conditional Word Frequencies in Dataframe w/ SQL style Search terms

user_6593031 0
Viewed 48

I have an example dataframe:

df = pd.DataFrame({'Reference':[1,2,3,4,5], 
                  'Description':["foo", "bar", "green bond house", "supply dog chain", "climate finance house"]})

And a list of keywords that I am interested in knowing how often they appear in each row of the dataframe:

wordlist = ["green finance", "green bond", "green credit", "green financing", "climate finance"]

The following code does this and works perfectly:

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

With the following output:

enter image description here

However. My keywords are not always simple strings, sometimes they can be like this:

  • "climate finance" AND NOT "house"
  • NEAR((supply, chain), 2) i.e. where the word 'supply' appears within 2 words of 'chain'

Is this type of str.count possible in Python? And if not, does anyone have any advice on how to solve this problem?

Many thanks

pandas python
1 Answers

I have figured out how to do it with regex. An example below in case anyone sees this:

df = pd.DataFrame({'Reference':[1,2,3,4,5,6,7], 
                  'Description':["foo", "bar train", "phone mouse", "phone train", "green bond house", "supply dog chain", "climate finance house"]})

df['Total'] = df.Description.str.count("\\b(?:supply\\W+(?:\\w+\\W+){0,2}chain|chain\\W+(?:\\w+\\W+){0,2}supply)\\b") + df.Description.str.count("green bond|house") + df.Description.str.count("^(?=.*(bar|phone))(?:(?!train).)*$") + df.Description.str.count("^(?=.*(bar|phone)).*train.*")

So the regex in this counts the number of times "supply" and "chain" appear within two words, if "green bond" OR "house" appears, if "bar" OR "phone" but NOT "train" appear, if "bar" OR "phone" AND "train" appears.

enter image description here

user_65930310
Related