CODEWITHSUNDEEP
pythonpandasdataframevlookup
machump
machump

Reputation: 1267

pandas map returning all NaNs

I have two data frames df and dfusers

df:

ID   Count
A1   5
B1   12
C1   7

dfusers:

ID  FullName
A1  Mike Frost
B1  Jack James
C1  Mickey Rose

I would like

ID  Count FullName
A1   5    Mike Frost
B1   12   Jack James
C1   7    Mickey Rose

In my research, I ended up trying df['Fullname']=df['ID'].map(dfusers['FullName'])

But the resulting dataframe returns NaN for all IDs. I was able to successfully create the desired dataframe using Excel Vlookup though. How come this is not working in pandas?

Upvotes: 4

Views: 2067

Answers (3)

Scott Boston
Scott Boston

Reputation: 153500

Use, set_index and map:

df = df.set_index('ID')
dfusers['Count'] = dfusers.ID.map(df['Count'])
print(dfusers)

Output:

   ID     FullName  Count
0  A1   Mike Frost      5
1  B1   Jack James     12
2  C1  Mickey Rose      7

Upvotes: 1

cs95
cs95

Reputation: 402844

Slight correction, pass a series of FullName with ID as the index:

df['Fullname'] = df['ID'].map(dfusers.set_index('ID')['FullName'])

Or, with replace (also works with Series, though slower, as not vectorised):

df['Fullname'] = df['ID'].replace(dfusers.set_index('ID')['FullName'])


df

   ID  Count     Fullname
0  A1      5   Mike Frost
1  B1     12   Jack James
2  C1      7  Mickey Rose

Another option may make use of pd.Series.searchsorted:

df['FullName'] = dfusers.loc[df.index[df.ID.searchsorted(df.ID)], 'FullName']
df

   ID  Count     Fullname
0  A1      5   Mike Frost
1  B1     12   Jack James
2  C1      7  Mickey Rose

Assuming df.ID is already sorted.

Upvotes: 1

zipa
zipa

Reputation: 27879

You can use merge to achieve this:

df = df.merge(dfusers, how='inner', on='ID')

Upvotes: 4

Related Questions