Search code examples
pythonpandasconcatenation

How to .map a column form 1 df for another df by checking the value in 2 columns

How to .map 1 column to 2 columns ??

  • The goal is to map cost from a REF table to the Data by id.
  • The issue .map just compare 1 column with 1 column

The Data is mess up and for some rows there are id names rather then id's. I could create a new REF table to replace theses id_names to id, the problem is there is so many..

Reff table

google =

id   id_name      cost

154  campaign1    15
155  campaign2    12
1566  campaign33  12
158  campaign4    33

Data

cw = 

id  

154
155
campaign1    
campaign33
1566
158
campaign4

Desired output


cw =

id            cost

154            15
155            12
campaign1      15
campaign33     12
1566           12
158            33
campaign4      33

What I done..

# Just work for one column

cw['cost'] = cw['id'].map(google.set_index('id')['cost'])
Solution

  • you can melt and map:

    map_series = google.melt(id_vars='cost').set_index('value')['cost']

cw['cost'] = cw['id'].map(map_series)