Get weighted value of pandas string series

user_5120049 0
Viewed 236

The Series is as below:

value
aa aa bb cc
dd ee aa
ff aa cc

I want to count the occurrence of a word in the row and multiply it with weight given in the dictionary

weights = {
   'aa':1,
   'bb':1,
   'cc':0.5
}

The resultant should be

value_score
3.5
1
1.5

Above could be explained as sum(occurrence of word in dictionary * weight from dictionary) i.e for first value it is 2*1 + 1*1 + 1*0.5 = 3.5

I have currently implemented using str.count, but as more values come in, it is not efficent

df['value_score'] = (df['value'].str.count('aa', regex=False) * weights['aa'] +
                     df['value'].str.count('bb', regex=False) * weights['bb'] +
                     df['value'].str.count('cc', regex=False) * weights['cc'] )
pandas counter count python string
2 Answers

Use list comprehension with get for 0 for unmatched values:

df['value_score'] = df['value'].apply(lambda x: sum(weights.get(y, 0) for y in x.split()))
print (df)
         value  value_score
0  aa aa bb cc          3.5
1     dd ee aa          1.0
2     ff aa cc          1.5

Another solution:

df['value_score'] = df['value'].str.split(expand=True).stack().map(weights).sum(level=0)
print (df)
         value  value_score
0  aa aa bb cc          3.5
1     dd ee aa          1.0
2     ff aa cc          1.5
user_29010020

You can use collections.Counter:

from collections import Counter

df['value_score'] = [sum(weights.get(k, 0) * v for k, v in Counter(x.split()).items()) \
                     for x in df['value']]

print(df)

         value  value_score
0  aa aa bb cc          3.5
1     dd ee aa          1.0
2     ff aa cc          1.5

No vectorised solution is possible. For performance, you should favour list comprehensions instead of Pandas str methods.

user_92095460
Related