Perform merge in 2 dataframes

Question

I have 2 dataframes as below:

Dataframe1:
i   j
3   4
5   6
7   2

Dataframe2:
k   n
1   a
2   b
3   c
4   d
5   e
6   f
7   g
8   h

How do I merge these 2 dataframes such that the key element is both i and j on the fist dataframe and k on the second dataframe such that the result is:

Dataframe1:
i   j   ni   nj
3   4   c   d
5   6   e   f
7   2   g   b

Answer 1

You can use map by Series created with set_index, also is necessary column k has to be unique:

s = df2.set_index('k')['n']
df1['ni'] = df1['i'].map(s)
df1['nj'] = df1['j'].map(s)
print (df1)

   i  j ni nj
0  3  4  c  d
1  5  6  e  f
2  7  2  g  b

Answer 2

Option 1

• Build a mapping dictionary with dict and zip
• Use applymap with the dictionary's get method
• Use add_prefix to get the 'n' in front
• Use join to merge

---

m = dict(zip(d2.k.values, d2.n.values))
d1.join(d1.applymap(m.get).add_prefix('n'))

   i  j ni nj
0  3  4  c  d
1  5  6  e  f
2  7  2  g  b

---

Equivalent 1-line

d1.join(d1.applymap(dict(zip(d2.k.values, d2.n.values)).get).add_prefix('n'))

   i  j ni nj
0  3  4  c  d
1  5  6  e  f
2  7  2  g  b

---

Option 2
Same as option 1 except we could use a stack/unstack idiom with map

m = dict(zip(d2.k.values, d2.n.values))
d1.join(d1.stack().map(m).unstack().add_prefix('n'))

   i  j ni nj
0  3  4  c  d
1  5  6  e  f
2  7  2  g  b