Pandas card returning all NaN

Question

I have two data frames dfanddfusers

DF:

ID   Count
A1   5
B1   12
C1   7

dfusers:

ID  FullName
A1  Mike Frost
B1  Jack James
C1  Mickey Rose

I would like to

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 NaNfor everyone IDs. However, I was able to successfully create the required frame using Excel Vlookup. Why does this not work in pandas?

Answer 1

Slight correction, pass series FullNamec IDas an index:

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

Or, with replace(also works with the Series, albeit more slowly, since it is not vectorized):

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 might use 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.IDalready sorted.

Answer 2

:

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

Answer 3

set_index map:

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

:

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