python - PANDAS vlookup against series with common index using map -

import pandas pd import numpy np  pb = {"mark_up_id":{"0":"123","1":"456","2":"789","3":"111","4":"222"},"mark_up":{"0":1.2987,"1":1.5625,"2":1.3698,"3":1.3333,"4":1.4589}}  data = {"id":{"0":"k69","1":"k70","2":"k71","3":"k72","4":"k73","5":"k74","6":"k75","7":"k79","8":"k86","9":"k100"},"cost":{"0":29.74,"1":9.42,"2":9.42,"3":9.42,"4":9.48,"5":9.48,"6":24.36,"7":5.16,"8":9.8,"9":3.28},"mark_up_id":{"0":"123","1":"456","2":"789","3":"111","4":"222","5":"333","6":"444","7":"555","8":"666","9":"777"}}  pb = pd.dataframe(data=pb).set_index('mark_up_id') df = pd.dataframe(data=data) 

i know can use like:

df['mark_up_id'].map(pb['mark_up']) 

to perform v-look-up. i'd take mark-up returns , multiply each cost common index yield new column called price.

i know can merge 2 , run calculation. that's how produced desired output. i'd able similar how you'd loop through dictionary , use keys find values in dictionary , perform kind of computation inside of loop. considering pandas dataframes sit on top of dictionaries, there must way of using combination of join/map/apply without joining 2 data-sets in memory.

desired output:

desired_output = {"cost":{"0":29.74,"1":9.42,"2":9.42,"3":9.42,"4":9.48},"id":{"0":"k69","1":"k70","2":"k71","3":"k72","4":"k73"},"mark_up_id":{"0":"123","1":"456","2":"111","3":"123","4":"789"},"price":{"0":38.623338,"1":14.71875,"2":12.559686,"3":12.233754,"4":12.985704}} = pd.dataframe(data=desired_output) 

bonus points:

explain difference between accepted answer and...

pb.loc[df['mark_up_id']]['mark_up'] * df.set_index('mark_up_id')['cost'] 

and why following lambda function derived above hits error...

df.apply(lambda x : x['cost']*pb.loc[x['mark_up_id']],axis=1 ) 

returns error saying:

keyerror: ('the label [333] not in [index]', u'occurred @ index 5') 

try

df['price'] = df['mark_up_id'].map(pb['mark_up']) * df['cost'] 

you get

    cost    id  mark_up_id  price 0   29.74   k69 123         38.623338 1   9.42    k70 456         14.718750 2   9.42    k71 111         12.559686 3   9.42    k72 123         12.233754 4   9.48    k73 789         12.985704