CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
amy thomspon
amy thomspon

Reputation: 43

How do I create a new df by replace the df ID# with names of another df?

I have to DFs one contains just ID# and the other is a more comprehensive DF with names, salary, ages and ID#'s

df1 looks like this 

Name           Salary     ID     Age   City 
Sam            52000      542       52    NYC
Bob            15000      451       21    LA
Sam            72000      556       21    SF

where df 2 looks like this with the ID#

 Index     1     2   3     4    
 a         542  352  581   521   
 b         451  215  556   451   
 c         540  332  511   121   
 d         451  515  156   951

Note some of the ID# reap and that is expected

How do get df2 but just replacing them with the names (and some do repeat I do not want to drop them)

My goal was to have a new df3 where it looked like this

df3

Index     1    2     3       4
 a        Sam  Bill  Le     Sam
 b        Mike Jane  Kevin  Le
 c        Jame Kerry David  Mike
 d        Andy Steve Jane   Andy

(note my examples id# does not match the names please forgive me)

Upvotes: 0

Views: 49

Answers (1)

Allen Qin
Allen Qin

Reputation: 19947

You can fist build a lookup dict and then use applymap

name_map = df.set_index('ID')['Name'].to_dict()
df2.applymap(name_map.get)

    1       2       3       4
0   Sam     None    None    None
1   Bob     None    Sam     Bob
2   None    None    None    None
3   Bob     None    None    None

Upvotes: 1

Related Questions