Searching value in a dataframe and then putting corresponding value from dictionary in a new coloumn

Question

I have a dataframe which has a column of name.I have another dictionary which has names as keys and values associated with it.I want to search a name in dataframe and add corresponding value from dictionary in a new coloumn.

my dictionary   
fruits={'mango':1,'apple':2,'guava':0,'nut':1}

my dataframe
Fruit
mango
apple
guava
nut

Expected Output

  Fruit  Frequency
    mango   1
    apple   2
    guava   0
    nut     1

The data frame is already there.I just want to map the values with the corresponding element

Also if my Dictionary has more key-value pair than my original dataframe How can I add the keys and corresponding values to dataframe.

Suppose if my dataframe is

Fruit 
mango
apple

And now I would like to have just nut and not guava because it has value 0

So expected output would be

Fruit  Count
mango  1
apple  2
nut    1

Okay, wonderful. Where is your data? Why isn't it in your question? Please provide a [mcve]. — cs95, Dec 18 '17 at 13:18
On a side note can you tell if my dictionary is having more elements and I will like to add the one's which are not in my original dataframe How can i do that — Rookie_123, Dec 18 '17 at 13:41
Hmm, that's a different question. If you edit your question with this information / new data that describes your problem, I can reopen your question. — cs95, Dec 18 '17 at 13:42
Can you explain this? `How can I add the keys and corresponding values to dataframe.` It seems to describe something different from what your data shows. — cs95, Dec 18 '17 at 13:55
Now in my dataframe Nut is not there.But my dictionary has it.So for that Nut should get added in my dataframe — Rookie_123, Dec 18 '17 at 13:59

score 0 · Accepted Answer · answered Dec 18 '17 at 14:09

For the first part, use map/replace -

df

   Fruit
0  mango
1  apple
2  guava
3    nut

df['Frequency'] = df.Fruit.map(fruits)

df['Frequency'] = df.Fruit.replace(fruits)   # alternative to map

df

   Fruit  Frequency
0  mango          1
1  apple          2
2  guava          0
3    nut          1

For the second part, you can perform a set difference and concatenate with the original -

df

Fruit 
mango
apple

i = df.Fruit
j = pd.Series(list(set(fruits.keys()).difference(i)))

df = pd.concat([i, j], ignore_index=True).to_frame('Fruit')
df

   Fruit
0  mango
1  apple
2    nut
3  guava

To drop rows with Frequency == 0, use boolean indexing -

df

   Fruit  Frequency
0  mango          1
1  apple          2
2  guava          0
3    nut          1

df = df[df.Frequency.gt(0)]
df

   Fruit  Frequency
0  mango          1
1  apple          2
3    nut          1

Alternatively, use eval/query.

df.query("Frequency > 0")

   Fruit  Frequency
0  mango          1
1  apple          2
3    nut          1

Searching value in a dataframe and then putting corresponding value from dictionary in a new coloumn

1 Answers1