d1=pd.DataFrame({'x':['a','b','c','c'],'y':[-1,-2,-3,0]})
d2=pd.DataFrame({'x':['d','c','a','b'],'y':[0.1,0.2,0.3,0.4]})

I want to replace d1.y where y<0 with the correspondent y in d2. It's something like vlookup in Excel. The core problem is replace y according to x rather than just simply manipulate y. What I want is

Out[40]: 
   x    y
0  a  0.3
1  b  0.4
2  c  0.2
3  c  0.0

2 Answers

3
jezrael On Best Solutions

Use Series.map with condition:

s = d2.set_index('x')['y']
d1.loc[d1.y < 0, 'y'] = d1['x'].map(s)
print (d1)

   x    y
0  a  0.3
1  b  0.4
2  c  0.2
3  c  0.0
1
hacker315 On

You can try this:

d1.loc[d1.y < 0, 'y'] = d2.loc[d1.y < 0, 'y']