CODEWITHSUNDEEP
pythonpandasdictionarymapping
Jonas_Konig
Jonas_Konig

Reputation: 119

Pandas: map dictionary values on an existing column based on key from another column to replace NaN

I've had a good look and I can't seem to find the answer to this question. I am wanting to replace all NaN values in my Department Code Column of my DataFrame with values from a dictionary, using the Job Number column as the Key matching that of the dictionary. The data can be seen Below: Please note there are many extra columns, these are just the two.)

df = 
       Job Number Department Code
    0      3525             403
    1      4555             NaN
    2      5575             407
    3      6515             407
    4      7525             NaN
    5      8535             102
    6      3545             403
    7      7455             102
    8      3365             NaN
    9      8275             403
    10     3185             408

dict = {'4555': '012', '7525': '077', '3365': '034'}

What I am hoping the output to look like is:

       Job Number Department Code
    0      3525             403
    1      4555             012
    2      5575             407
    3      6515             407
    4      7525             077
    5      8535             102
    6      3545             403
    7      7455             102
    8      3365             034
    9      8275             403
    10     3185             408

The two columns are object datatypes and I have tried the replace function which I have used before but that only replaces the value if the key is in the same column.

df['Department Code'].replace(dict, inplace=True)

This does not replace the NaN values.

I'm sure the answer is very simple and I apologies in advance but i'm just stuck.

(Excuse my poor code display, it's handwritten as not sure how to export code from python to here.)

Upvotes: 2

Views: 1629

Answers (2)

jezrael
jezrael

Reputation: 863226

Better is avoid variable dict, because builtin (python code word), then use Series.fillna for replace matched values with Series.map, if no match values return NaN, so no replacement:

d = {'4555': '012', '7525': '077', '3365': '034'}
df['Department Code'] = df['Department Code'].fillna(df['Job Number'].astype(str).map(d))
print (df)
    Job Number Department Code
0         3525             403
1         4555             012
2         5575             407
3         6515             407
4         7525             077
5         8535             102
6         3545             403
7         7455             102
8         3365             034
9         8275             403
10        3185             408

Upvotes: 2

yatu
yatu

Reputation: 88276

Or another way is using set_index and fillna:

df['Department Code'] = (df.set_index('Job Number')['Department Code']
                           .fillna(d).values)


print(df)

     Job Number Department Code
0       3525            403
1       4555            012
2       5575            407
3       6515            407
4       7525            077
5       8535            102
6       3545            403
7       7455            102
8       3365            034
9       8275            403
10      3185            408

Upvotes: 1

Related Questions