Join 2 dataframes in pandas using join

Question

I have two data frames df1 and df2

df1

id     name
ada1  mike
ad23  tom
cev2  tim

df2

 id   month.   sales
 ada1. 1/11.    23
 ada1. 4/11.    34
 ad23. 3/12.    34
 cev2. 4/11.    32

I need :

 id   month.   sales name
 ada1. 1/11.    23.  mike
 ada1. 4/11.    34.  mike
 ad23. 3/12.    34.  tom
 cev2. 4/11.    32.  tim

I am struck between left join or right join, what should i use.

Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Ynjxsjmh, May 25 '22 at 18:29

score 0 · Accepted Answer · answered May 25 '22 at 18:29

0

Use pd.merge:

>>> df2.merge(df1, on='id', how='left')

     id month  sales  name
0  ada1  1/11     23  mike
1  ada1  4/11     34  mike
2  ad23  3/12     34   tom
3  cev2  4/11     32   tim

answered May 25 '22 at 18:29

Corralien

70,617
7
16
36

Thank you. Quick question. How to remove records with id=0 or null / blank values. – donald smith May 25 '22 at 18:38
Maybe you can use `how='inner'`. – Corralien May 25 '22 at 18:40

score 0 · Answer 2 · answered May 25 '22 at 18:29

0

Use map, since you are joining\merging on one column and returning one column.

df2['name'] = df2['id'].map(df1.set_index('id')['name'])

Map will outperform join\merge.

answered May 25 '22 at 18:29

Scott Boston

133,446
13
126
161

score 0 · Answer 3 · answered May 26 '22 at 01:17

Providing two methods that can help based on problem set

import pandas as pd

# retain df2 and merge df1 use 'leftjoin'
df2.merge(df1, on='id', how='left')

# Full Join, also known as Full Outer Join, returns all those records which 
 either have a match in the left or right dataframe

pd.merge(df2,df1,on='id',how='outer',indicator=True)

Join 2 dataframes in pandas using join

3 Answers3