CODEWITHSUNDEEP
pythonpandasdictionarydataframe
MattR
MattR

Reputation: 5126

Set Pandas Value Based on Dictionary

I would like to replace the value of a Dataframe with the Value from A dictionary. In plain english: If There is a value in Column C that matches a Dictionary Key, then Replace Column D with the value in the Dictionary that corresponds to that specific key.

import pandas as pd
import numpy as np
dfp = pd.DataFrame({'A' : [np.NaN,np.NaN,3,4,5,5,3,1,5,np.NaN], 
                    'B' : [1,0,3,5,0,0,np.NaN,9,0,0], 
                    'C' : ['AA1233445','A9875', 'rmacy','Idaho Rx','Ab123455','TV192837','RX','Ohio Drugs','RX12345','USA Pharma'], 
                    'D' : [123456,123456,1234567,12345678,12345,12345,12345678,123456789,1234567,np.NaN],
                    'E' : ['Assign','Unassign','Assign','Ugly','Appreciate','Undo','Assign','Unicycle','Assign','Unicorn',]})
print(dfp)

z = {'rmacy': 999}
dfp.loc[dfp['C'].isin(z.keys()), 'D' ] = z.values() # <--- code to change

Output: 
     A    B           C            D           E
0  NaN  1.0   AA1233445       123456      Assign
1  NaN  0.0       A9875       123456    Unassign
2  3.0  3.0       rmacy        (999)      Assign #<--- Worked with paranthesis
3  4.0  5.0    Idaho Rx  1.23457e+07        Ugly
4  5.0  0.0    Ab123455        12345  Appreciate
5  5.0  0.0    TV192837        12345        Undo
6  3.0  NaN          RX  1.23457e+07      Assign
7  1.0  9.0  Ohio Drugs  1.23457e+08    Unicycle
8  5.0  0.0     RX12345  1.23457e+06      Assign
9  NaN  0.0  USA Pharma          NaN     Unicorn

The above code works (except is puts the value in Paranthesis. But if the dictionary is larger than one key, it will put both values in Column D because there are two matches in the column.

     A    B           C            D           E
0  NaN  1.0   AA1233445       123456      Assign
1  NaN  0.0       A9875       123456    Unassign
2  3.0  3.0       rmacy   (999, 333)      Assign
3  4.0  5.0    Idaho Rx  1.23457e+07        Ugly
4  5.0  0.0    Ab123455        12345  Appreciate
5  5.0  0.0    TV192837        12345        Undo
6  3.0  NaN          RX   (999, 333)      Assign
7  1.0  9.0  Ohio Drugs  1.23457e+08    Unicycle
8  5.0  0.0     RX12345  1.23457e+06      Assign
9  NaN  0.0  USA Pharma          NaN     Unicorn

How would one resolve this?

Upvotes: 3

Views: 1328

Answers (1)

piRSquared
piRSquared

Reputation: 294228

use map and fillna

dfp.assign(D=dfp.C.map(z).fillna(dfp.D))

     A    B           C            D           E
0  NaN  1.0   AA1233445     123456.0      Assign
1  NaN  0.0       A9875     123456.0    Unassign
2  3.0  3.0       rmacy        999.0      Assign
3  4.0  5.0    Idaho Rx   12345678.0        Ugly
4  5.0  0.0    Ab123455      12345.0  Appreciate
5  5.0  0.0    TV192837      12345.0        Undo
6  3.0  NaN          RX   12345678.0      Assign
7  1.0  9.0  Ohio Drugs  123456789.0    Unicycle
8  5.0  0.0     RX12345    1234567.0      Assign
9  NaN  0.0  USA Pharma          NaN     Unicorn

Upvotes: 5

Related Questions