CODEWITHSUNDEEP
pythonpandasdataframemerge
emax
emax

Reputation: 7245

Pandas: How to merge two columns with a second DataFrame?

I have a dataframe df containing new IDs while in dfOld I have the corresponce between old and new IDs

df
   ID1new ID2new
0  5       3
1  4       2
2  3       7


dfOld

   IDold IDnew
0  33       0
1  78       1
2  65       2
3  12       3
4  24       4
5  89       5
6  77       6
7  16       7
8  69       8

I want to add two columns to df containing the old IDs for ID1new and ID2new, so

df1
   ID1new ID2new   ID1old   ID2old
0  5       3         89       12
1  4       2         24       65
2  3       7         12       16

I am brutally doing a loop like this:

df1 = df
df1['ID1old']=0
df1['ID2old']=0
for i in net.index:
    tmp  = dfOld[dfOld.IDnew == df.ID1new[i]]
    tmp0 = dfOld[dfOld.IDnew == df.ID2new[i]]
    net.ID1old[i] = tmp.IDold[tmp.index[0]]
    net.ID1old[i] = tmp0.IDold[tmp0.index[0]]

Upvotes: 2

Views: 120

Answers (1)

jezrael
jezrael

Reputation: 862511

You can use map by dict d or Series s:

d = dfOld.set_index('IDnew')['IDold'].to_dict()
print (d)
{0: 33, 1: 78, 2: 65, 3: 12, 4: 24, 5: 89, 6: 77, 7: 16, 8: 69}

#s = dfOld.set_index('IDnew')['IDold']

df['ID1old'] = df.ID1new.map(d)
df['ID2old'] = df.ID2new.map(d)
print (df)
   ID1new  ID2new  ID1old  ID2old
0       5       3      89      12
1       4       2      24      65
2       3       7      12      16

Upvotes: 1

Related Questions