NaNs after merging two dataframes

Question

I have two dataframes like the following:

df1

         id         name
-------------------------
0        43          c
1        23          t
2        38          j
3         9          s

df2

          user        id
--------------------------------------------------
0         222087      27,26
1         1343649     6,47,17
2         404134      18,12,23,22,27,43,38,20,35,1
3         1110200     9,23,2,20,26,47,37

I want to split all the ids in df2 into multiple rows and join the resultant dataframe to df1 on "id".

I do the following:

b = pd.DataFrame(df2['id'].str.split(',').tolist(), index=df2.user_id).stack()
b = b.reset_index()[[0, 'user_id']] # var1 variable is currently labeled 0
b.columns = ['Item_id', 'user_id']

When I try to merge, I get NaNs in the resultant dataframe.

pd.merge(b, df1, on = "id", how="left")

              id       user      name
-------------------------------------
0              27      222087     NaN
1              26      222087     NaN
2              6      1343649     NaN
3              47     1343649     NaN
4              17     1343649     NaN

So, I tried doing the following:

b['name']=np.nan
for i in range(0, len(df1)):
    b['name'][(b['id'] == df1['id'][i])] = df1['name'][i]

It still gives the same result as above. I am confused as to what could cause this because I am sure both of them should work! Any help would be much appreciated!

I read similar posts on SO but none seemed to have a concrete answer. I am also not sure if this is not at all related to coding or not.

Thanks in advance!

I think the issue is that original you had a string and then you split, so you may have incompatible dtypes, try b = pd.DataFrame(df2['id'].str.split(',').tolist(), index=df2.user_id).stack().astype(int) — EdChum

jezrael jezrael · Accepted Answer · 2017-03-14T09:22:22

Problem is you need convert column id in df2 to int, because output of string functions is always string, also if works with numeric.

df2.id = df2.id.astype(int)

Another solution is convert df1.id to string:

df1.id = df1.id.astype(str)

And get NaNs because no match - str values doesnt match with int values.

NaNs after merging two dataframes

1 Answers