merge: Pandas DataFrame
DataFrame.merge(self,right, how, on, left_on, right_on,left_index, right_index, sort, suffix, copy, indicator,validation)
self: The first DataFrame or left side one.
right : The second DataFrame or right side one
how :left, right, inner ( default ) , outer. Type of merge
on : label or list , column name using which merge will apply
left_on: label or list if name in left DataFrame is different than right
ight_on: label or list if name in right DataFrame is different than left
sort: Boolean , sort the join keys
suffixes: Suffix to apply on overlapping columns.
copy: Boolean,
indicator: (Boolean, default False ) Add indicator to column
validate: check the merger type
| Type | Table 1 | Table 2 | Details |
|---|---|---|---|
| LEFT | A + B | D | All from Left ( Table 1 ) and matching from right (Table 2 ) |
| RIGHT | A | C + D | All from Right ( Table 2 ) and matching from left (Table 1 ) |
| INNER | A | D | Matching from Left ( Table 1 ) and right (Table 2 ) |
| OUTER | A + B | C + D | All from Left ( Table 1 ) and all from right (Table 2 ) |
how = 'left'
All the rows of left table ( or table 1 ) and matching rows of right ( Table 2 ) . In this case all rows of sales DataFrame have matching product in product table. Now we have extra columns like price required for our further calculation.
my_data=pd.merge(sales,products,on='p_id',how='left')
print(my_data) sale_id c_id p_id product_x qty store product_y price
0 1 2 3 Monitor 2 ABC Monitor 75
1 2 2 4 CPU 1 DEF CPU 55
2 3 1 3 Monitor 3 ABC Monitor 75
3 4 4 2 RAM 2 DEF RAM 90
4 5 2 3 Monitor 3 ABC Monitor 75
5 6 3 3 Monitor 2 DEF Monitor 75
6 7 2 2 RAM 3 ABC RAM 90
7 8 3 2 RAM 2 DEF RAM 90
8 9 2 3 Monitor 2 ABC Monitor 75my_data=pd.merge(products,sales,on='p_id',how='left') p_id product_x price sale_id c_id product_y qty store
0 1 Hard Disk 80 NaN NaN NaN NaN NaN
1 2 RAM 90 4.0 4.0 RAM 2.0 DEF
2 2 RAM 90 7.0 2.0 RAM 3.0 ABC
3 2 RAM 90 8.0 3.0 RAM 2.0 DEF
4 3 Monitor 75 1.0 2.0 Monitor 2.0 ABC
5 3 Monitor 75 3.0 1.0 Monitor 3.0 ABC
6 3 Monitor 75 5.0 2.0 Monitor 3.0 ABC
7 3 Monitor 75 6.0 3.0 Monitor 2.0 DEF
8 3 Monitor 75 9.0 2.0 Monitor 2.0 ABC
9 4 CPU 55 2.0 2.0 CPU 1.0 DEF
10 5 Keyboard 20 NaN NaN NaN NaN NaN
11 6 Mouse 10 NaN NaN NaN NaN NaN
12 7 Motherboard 50 NaN NaN NaN NaN NaN
13 8 Power supply 20 NaN NaN NaN NaN NaNWatch the product_x and product_y columns
my_data=pd.merge(products,sales,on='p_id',how='left')
print(my_data[my_data['sale_id'].isnull()]) p_id product_x price sale_id c_id product_y qty store
0 1 Hard Disk 80 NaN NaN NaN NaN NaN
10 5 Keyboard 20 NaN NaN NaN NaN NaN
11 6 Mouse 10 NaN NaN NaN NaN NaN
12 7 Motherboard 50 NaN NaN NaN NaN NaN
13 8 Power supply 20 NaN NaN NaN NaN NaNhow='right'
With how='right' all rows of right DataFrame and matching rows of left DataFrame will be displayed.
my_data=pd.merge(sales,products,on='p_id',how='right')
print(my_data)
sale_id c_id p_id product_x qty store product_y price
0 1.0 2.0 3 Monitor 2.0 ABC Monitor 75
1 3.0 1.0 3 Monitor 3.0 ABC Monitor 75
2 5.0 2.0 3 Monitor 3.0 ABC Monitor 75
3 6.0 3.0 3 Monitor 2.0 DEF Monitor 75
4 9.0 2.0 3 Monitor 2.0 ABC Monitor 75
5 2.0 2.0 4 CPU 1.0 DEF CPU 55
6 4.0 4.0 2 RAM 2.0 DEF RAM 90
7 7.0 2.0 2 RAM 3.0 ABC RAM 90
8 8.0 3.0 2 RAM 2.0 DEF RAM 90
9 NaN NaN 1 NaN NaN NaN Hard Disk 80
10 NaN NaN 5 NaN NaN NaN Keyboard 20
11 NaN NaN 6 NaN NaN NaN Mouse 10
12 NaN NaN 7 NaN NaN NaN Motherboard 50
13 NaN NaN 8 NaN NaN NaN Power supply 20how='inner'
Matching rows of both the tables.
my_data=pd.merge(sales,products,on='p_id',how='inner') sale_id c_id p_id product_x qty store product_y price
0 1 2 3 Monitor 2 ABC Monitor 75
1 3 1 3 Monitor 3 ABC Monitor 75
2 5 2 3 Monitor 3 ABC Monitor 75
3 6 3 3 Monitor 2 DEF Monitor 75
4 9 2 3 Monitor 2 ABC Monitor 75
5 2 2 4 CPU 1 DEF CPU 55
6 4 4 2 RAM 2 DEF RAM 90
7 7 2 2 RAM 3 ABC RAM 90
8 8 3 2 RAM 2 DEF RAM 90how='outer'
Joins all the records of both the tables irrespective of matching or not.
my_data=pd.merge(sales,products,on='p_id',how='outer') sale_id c_id p_id product_x qty store product_y price
0 1.0 2.0 3 Monitor 2.0 ABC Monitor 75
1 3.0 1.0 3 Monitor 3.0 ABC Monitor 75
2 5.0 2.0 3 Monitor 3.0 ABC Monitor 75
3 6.0 3.0 3 Monitor 2.0 DEF Monitor 75
4 9.0 2.0 3 Monitor 2.0 ABC Monitor 75
5 2.0 2.0 4 CPU 1.0 DEF CPU 55
6 4.0 4.0 2 RAM 2.0 DEF RAM 90
7 7.0 2.0 2 RAM 3.0 ABC RAM 90
8 8.0 3.0 2 RAM 2.0 DEF RAM 90
9 NaN NaN 1 NaN NaN NaN Hard Disk 80
10 NaN NaN 5 NaN NaN NaN Keyboard 20
11 NaN NaN 6 NaN NaN NaN Mouse 10
12 NaN NaN 7 NaN NaN NaN Motherboard 50
13 NaN NaN 8 NaN NaN NaN Power supply 20Merge Without common key
We may require one cross join where we don't have any key to use on. For this we will add one new key to both DataFrames and then merge the two. After merging we can remove the newly added column.import pandas as pd
d1={'NAME':['Alex','Ravi','John'],'AGE':[22,23,21]}
d2={'SUBJECT':['Hindi','English']}
df1=pd.DataFrame(data=d1)
df2=pd.DataFrame(data=d2)
df1['key']=1
df2['key']=1
df1.merge(df2,how='outer',on='key').drop("key", 1) NAME AGE SUBJECT
0 Alex 22 Hindi
1 Alex 22 English
2 Ravi 23 Hindi
3 Ravi 23 English
4 John 21 Hindi
5 John 21 EnglishGroupby can be used by merging more than one table. Check the exercise on Pandas DataFrame to understand application of groupby in developing reports.
Pandas
Pandas DataFrame
Subhendu Mohapatra
Author
🎥 Join me live on YouTubePassionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.
Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
Python Video Tutorials