Get the count of strings found in column A from column B and return value in a new column

user_4250010 0
Viewed 89

I'm trying to get the count of the contents of one column in a second column in pandas. I would like to put the frequency counts in a new column called frequency.

I would like to find the number of times the string in my [Keyword] column is found in the [Description] column in a new column called [Frequency].

Desired output

[keyword]           [Description]          [Frequency]
car                 car dog car car             3
car                 car dog dog dog             1
new car             old car car dog             0

Code I've tried

I tried the following code, but had two problems. (The frequency count wasn't accurate and formatting was completely wrong).

s = df['Keyword']
pat = r'\b{}\b'.format('|'.join(s))

from sklearn.preprocessing import MultiLabelBinarizer

mlb = MultiLabelBinarizer()
df_new = pd.DataFrame(mlb.fit_transform(df['Description'].str.findall(pat)),
                  columns=mlb.classes_,
                  index=df.index).reindex(columns=s, fill_value=0)
pandas dataframe python
2 Answers

Use this, if you want exact word matches:

df['frequency'] = [len(re.findall(rf'\b{k}\b', d)) for k, d in zip(df['keyword'], df['Description'])]
print(df)

Output

   keyword       Description  frequency
0      car   car dog car car          3
1      car   car dog dog dog          1
2  new car   old car car dog          0

A better alternative, suggested by @jezrael, is:

df['frequency'] = [len(re.findall(rf'\b{k}\b', d)) for k, d in df[['Description', 'keyword']].to_numpy()]
user_40015920

If exact match is not important use count, it means if carito in description it match car. If need avoid it use @Dani Mesejo answer.

df['new'] = df.apply(lambda x: x['Description'].count(x['keyword']), axis=1)
print (df)
   keyword      Description  Frequency  new
0      car  car dog car car          3    3
1      car  car dog dog dog          1    1
2  new car  old car car dog          0    0
user_29010020
Related