Replace values in a text with Integer values in a python dictionary and find its summation

user_10214220 0
Viewed 40

I have a dataframe which is something like this:

column1 column2
   1     apple,apple,apple
   2     ball,ball,ball
   3     cat,dog,eel
   4     dog,dog,dog
   5     apple,cat,eel
   6     apple,ball,cat

I have a dictionary which has value against each word:

{apple:1,
 ball:2,
 cat:3,
 dog:4,
 eel:5}

I want to use this dictionary to substitute values in the dataframe and find sum of each row. How can I do that?

In the end I want something like this:

column1      column2          column3 
   1     apple,apple,apple      3
   2     ball,ball,ball         6 
   3     cat,dog,eel            12
   4     dog,dog,dog            12
   5     apple,cat,eel          9
   6     apple,ball,cat         6
pandas numpy dictionary text python
2 Answers

IIUC split + explode then map the value 

df.column2.str.split(',').explode().map(d).sum(level=0)
Out[286]: 
0     3
1     6
2    12
3    12
4     9
5     6
Name: column2, dtype: int64
df['column3']=df.column2.str.split(',').explode().map(d).sum(level=0)
user_79645270

Another way is using list comprehension

d = {'apple':1, 'ball':2, 'cat':3, 'dog':4, 'eel':5}

df['column3'] = [sum(d[word] for word in x) for x in df.column2.str.split(',')]

Out[429]:
   column1            column2  column3
0        1  apple,apple,apple        3
1        2     ball,ball,ball        6
2        3        cat,dog,eel       12
3        4        dog,dog,dog       12
4        5      apple,cat,eel        9
5        6     apple,ball,cat        6
user_101892140
Related