Updating a pandas column with a dictionary lookup

Question

Have a dataframe, df:

import pandas as pd
import numpy as np

i = ['dog', 'cat', 'rabbit', 'elephant'] * 3

df = pd.DataFrame(np.random.randn(12, 2), index=i, columns=list('AB'))

...and a lookup dict for column B:

b_dict = {'elephant': 2.0, 'dog': 5.0}

How can column B of df be replaced for elephant and dog rows?

df['B'].update(b_dict) gives:

AttributeError: 'dict' object has no attribute 'reindex_like'

@ChrisA ... df['B'].index.map(b_dict) makes the remaining nan. Don't want that. Any possibility of filling with fillna()? — reservoirinvest, Feb 12 '19 at 14:39
@Vaishali ... this is not the same question as what you have put. Here the values are looked up against the index. — reservoirinvest, Feb 12 '19 at 14:40

score 3 · Answer 1 · answered Feb 12 '19 at 14:34

3

Use np.where() to replace only where condition matches and retain the rest:

df['B']=np.where(df.index.isin(b_dict.keys()),df.index.map(b_dict),df.B)

answered Feb 12 '19 at 14:34

anky

74,114
11
41
70

1

I've had a similar question and have been looking for an answer for about an hour - this solved my problem perfectly. Thank you! – travelsandbooks Oct 04 '22 at 12:32

score 3 · Accepted Answer · answered Feb 12 '19 at 14:53

Convert to pd.Series and update will work

df['B'].update(pd.Series(b_dict))
df
Out[185]: 
                 A         B
dog      -1.340695  5.000000
cat      -0.196993 -0.021518
rabbit   -0.274504 -0.260294
elephant -0.170860  2.000000
dog      -0.432042  5.000000
cat       0.868669  0.204100
rabbit    0.435023 -1.968735
elephant -0.668397  2.000000
dog       0.706603  5.000000
cat       0.158067  0.675130
rabbit    0.429419  0.374914
elephant  1.559330  2.000000

score 1 · Answer 3 · answered Feb 12 '19 at 14:29

You could use np.select:

cond = [(df.index == x) for x in b_dict.keys()]
vals = b_dict.values()

df['B'] = np.select(cond, vals)

Out[13]:         A    B
dog       0.523732  5.0
cat      -0.923483  0.0
rabbit    0.339749  0.0
elephant  1.204516  2.0
dog       1.799287  5.0
cat       0.214085  0.0
rabbit   -1.685739  0.0
elephant -0.475113  2.0
dog       1.381671  5.0
cat       1.343616  0.0
rabbit    0.627273  0.0
elephant  0.630255  2.0

Updating a pandas column with a dictionary lookup

3 Answers3

Linked