CODEWITHSUNDEEP
pythondictionarypandas
fuzzy_logic_77
fuzzy_logic_77

Reputation: 373

Using Pandas to fill NaN entries based on values in a different column, using a dictionary as a guide

I have a large dataframe where I'm trying to populate the NaN entries of column B based on the values in column A, using a dictionary as a guide. For example:

df = 
   A    B 
0  Red  628  
1  Red  149  
2  Red  NaN  
3  Green  575  
4  Green  687
5  Green  NaN
6  Blue  159
7  Blue  NaN

and the dictionary is (for example)

dict = {"Red": 123, "Green": 456, "Blue": 789}

I am curious as to the best way to replace each NaN with the corresponding number from the dictionary using Pandas. I'm not sure how to use the .fillna() or .isnull() methods in this situation. I'm new to Pandas so any help is appreciated! Thanks.

Upvotes: 0

Views: 1205

Answers (2)

Stefan
Stefan

Reputation: 42905

Select the relevant rows using boolean indexing (see docs), and map your dictionary to translate A to B values where necessary:

na_map = {"Red": 123, "Green": 456, "Blue": 789}
mask = df.B.isnull()

mask looks as follows:

0    False
1    False
2     True
3    False
4    False
5     True
6    False
7     True

Finally:

df.loc[mask, 'B'] = df.loc[mask, 'A'].map(na_map)

       A    B
0    Red  628
1    Red  149
2    Red  123
3  Green  575
4  Green  687
5  Green  456
6   Blue  159
7   Blue  789

Upvotes: 1

EdChum
EdChum

Reputation: 394179

I think your index looks iffy, the following does what you want:

In [19]:
df['B'] = df.set_index('A')['B'].fillna(d).reset_index()['B']

df
Out[19]:
       A    B
0    Red  628
1    Red  149
2    Red  123
3  Green  575
4  Green  687
5  Green  456
6   Blue  159
7   Blue  789

Here we set the index to column 'A' and then call fillna passing your dict, this performs the lookup using the index ('A') to return the associated dict value, we then reset the index and overwrite column 'B'

Upvotes: 2

Related Questions