CODEWITHSUNDEEP
pythonpandasdataframe
Jonathan
Jonathan

Reputation: 2043

Python pandas: map and return Nan

I have two data frame, the first one is:

id code
1   2
2   3
3   3
4   1

and the second one is:

id code  name
1    1   Mary
2    2   Ben
3    3   John

I would like to map the data frame 1 so that it looks like:

id code  name
1   2    Ben
2   3    John
3   3    John
4   1    Mary

I try to use this code:

mapping = dict(df2[['code','name']].values)
df1['name'] = df1['code'].map(mapping)

My mapping is correct, but the mapping value are all NAN:

mapping = {1:"Mary", 2:"Ben", 3:"John"}

id code  name
1   2    NaN
2   3    NaN
3   3    NaN
4   1    NaN

Can anyone know why an how to solve?

Upvotes: 3

Views: 5456

Answers (2)

jezrael
jezrael

Reputation: 863226

Problem is different type of values in column code so necessary converting to integers or strings by astype for same types in both:

print (df1['code'].dtype)
object

print (df2['code'].dtype)
int64

print (type(df1.loc[0, 'code']))
<class 'str'>

print (type(df2.loc[0, 'code']))
<class 'numpy.int64'>

mapping = dict(df2[['code','name']].values)
#same dtypes - integers
df1['name'] = df1['code'].astype(int).map(mapping)

#same dtypes - object (obviously strings)
df2['code'] = df2['code'].astype(str)
mapping = dict(df2[['code','name']].values)
df1['name'] = df1['code'].map(mapping)

print (df1)
   id code  name
0   1    2   Ben
1   2    3  John
2   3    3  John
3   4    1  Mary

Upvotes: 8

Sociopath
Sociopath

Reputation: 13426

Alternate way is using dataframe.merge

df.merge(df2.drop(['id'],1), how='left', on=['code'])

Output:

    id  code   name
0   1   2      Ben
1   2   3      John
2   3   3      John
3   4   1      Mery

Upvotes: 2

Related Questions