Clever Geek Handbook

Merge on 2 data frames

I have 2 data frames as shown 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 to combine these 2 dataframes so that the key element is both i and j on fist and k on the second dataframe, so the result is:

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


source to share


3 answers


You can use map

with Series

, created with set_index

, the column also needs k

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
+3


source


Option 1

  • Create a mapping dictionary with dict

    andzip

  • Use applymap

    with the dictionary get

    method
  • Use add_prefix

    to get 'n'

    ahead
  • Use join

    to combine


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 to 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 that we can use the stack

/ unstack

s idiommap 

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
+4


source


Here's one approach using unstack

, merge

and pivot

:

df3 = (df1.unstack('j')
          .reset_index()
          .rename(columns={0:'k'})
          .merge(df2, on="k")
          .pivot(index='level_1', columns='level_0'))
df3.columns = ['{}{}'.format(a,b) if a == 'n' else b for a,b in df3.columns]
df3.set_index(['i','j'], inplace=True)

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


source






More articles:


All Articles