Image by Editor | Midjourney
To perform data analysis, you often need to merge and join datasets. Python offers several powerful libraries to merge and join datasets in Python. One of the versatile library is Pandas for data manipulation. In this article, we will show how to merge and join datasets in Python using the best practices.
Understanding the Basics of Merging
Merging DataFrames: Merging datasets means combining two or more data structures based on a common column or index. It’s very similar to SQL joins. In Python, the most commonly used function for merging is pd.merge(). It allows you to specify different types of joins: inner, outer, left, and right.
Joining DataFrames: Joining is essentially a simpler version of merging, and it’s done using the DataFrame.join() method. This method is generally used to join two DataFrames based on their indices or columns.
Concatenating DataFrames: You can also concatenate DataFrames vertically or horizontally using pd.concat().
Types of Merges and Joins:
- Inner Join: It will return only the matching rows.
- Outer Join: Returns all rows, and fills NaN if value is missing.
- Left Join: Keeps all rows from the left dataset and matches with the right dataset.
- Right Join: Keeps all rows from the right dataset and matches with the left dataset.
Using pd.merge Function for Merging
You can import or create a dataframe by using Python libraries. Let’s assume you have two sample datasets one is sales data, and another is product data. You can use the pd.merge function to perform inner, outer, left, and right joins.
import pandas as pd
# Dataset 1: Sales Data
sales_data = pd.DataFrame({
'Sale_ID': [101, 102, 103, 104, 105],
'Product_ID': ['P001', 'P002', 'P003', 'P004', 'P005'],
'Quantity': [10, 5, 8, 15, 12],
'Sales': [1000, 750, 1200, 2250, 1800]
})
# Dataset 2: Product Data
product_data = pd.DataFrame({
'Product_ID': ['P001', 'P002', 'P003', 'P004', 'P005', 'P007'],
'Product_Name': ['Laptop', 'Smartphone', 'Headphones', 'Camera', 'TV', 'Air Conditioner'],
'Category': ['Electronics', 'Electronics', 'Electronics', 'Electronics', 'Appliances', 'Appliances'],
'Price': [100, 150, 150, 150, 500, 700]
})
# Display the dataframes
print("Sales Data:", "\n", sales_data)
print("\nProduct Data:", "\n", product_data)
# Merge Sales and Product data
merged_inner = pd.merge(sales_data, product_data, on='Product_ID', how='inner')
print("\nMerged Data:", "\n", merged_inner)
Output:
Sales Data:
Sale_ID Product_ID Quantity Sales
0 101 P001 10 1000
1 102 P002 5 750
2 103 P003 8 1200
3 104 P004 15 2250
4 105 P005 12 1800
Product Data:
Product_ID Product_Name Category Price
0 P001 Laptop Electronics 100
1 P002 Smartphone Electronics 150
2 P003 Headphones Electronics 150
3 P004 Camera Electronics 150
4 P005 TV Appliances 500
5 P007 Air Conditioner Appliances 700
Merged Data:
Sale_ID Product_ID Quantity Sales Product_Name Category Price
0 101 P001 10 1000 Laptop Electronics 100
1 102 P002 5 750 Smartphone Electronics 150
2 103 P003 8 1200 Headphones Electronics 150
3 104 P004 15 2250 Camera Electronics 150
4 105 P005 12 1800 TV Appliances 500
- how=’inner’: Includes only matching rows from both DataFrames. “inner” join is the default operation of pd.merge function.
merged_inner = pd.merge(sales_data, product_data, on='Product_ID', how='inner') print(merged_inner)
Output:
Sale_ID Product_ID Quantity Amount Product_Name Category Price 0 101 P001 10 1000 Laptop Electronics 100 1 102 P002 5 750 Smartphone Electronics 150 2 103 P003 8 1200 Headphones Electronics 150 3 104 P004 15 2250 Camera Electronics 150 4 105 P005 12 1800 TV Appliances 500
- how=’left’: Includes all rows from sales_data and matching rows from product_data.
merged_left = pd.merge(sales_data, product_data, on='Product_ID', how='left') print(merged_left)
Output:
Sale_ID Product_ID Quantity Amount Product_Name Category Price 0 101 P001 10 1000 Laptop Electronics 100 1 102 P002 5 750 Smartphone Electronics 150 2 103 P003 8 1200 Headphones Electronics 150 3 104 P004 15 2250 Camera Electronics 150 4 105 P005 12 1800 TV Appliances 500
- how=’right’: Includes all rows from product_data and matching rows from sales_data .
merged_right = pd.merge(sales_data, product_data, on='Product_ID', how='right') print(merged_right)
Output:
Sale_ID Product_ID Quantity Amount Product_Name Category Price 0 101.0 P001 10.0 1000.0 Laptop Electronics 100 1 102.0 P002 5.0 750.0 Smartphone Electronics 150 2 103.0 P003 8.0 1200.0 Headphones Electronics 150 3 104.0 P004 15.0 2250.0 Camera Electronics 150 4 105.0 P005 12.0 1800.0 TV Appliances 500 5 NaN P007 NaN NaN Air Conditioner Appliances 700
- how=’outer’: Includes all rows from both datasets, with NaN for non-matching rows.
merged_outer = pd.merge(sales_data, product_data, on='Product_ID', how='outer') print(merged_outer)
Output:
Sale_ID Product_ID Quantity Amount Product_Name Category Price 0 101.0 P001 10.0 1000.0 Laptop Electronics 100 1 102.0 P002 5.0 750.0 Smartphone Electronics 150 2 103.0 P003 8.0 1200.0 Headphones Electronics 150 3 104.0 P004 15.0 2250.0 Camera Electronics 150 4 105.0 P005 12.0 1800.0 TV Appliances 500 5 NaN P007 NaN NaN Air Conditioner Appliances 700
Best Practices for Merging and Joining Datasets
1. Handling Key Column Differences
Sometimes key column names may differ in different datasets. In those cases, you will need to use left_on and right_on commands.
import pandas as pd
# Dataset 1: Sales Data
sales_data = pd.DataFrame({
'Sale_ID': [101, 102, 103, 104, 105],
'Product_ID': ['P001', 'P002', 'P003', 'P004', 'P005'],
'Quantity': [10, 5, 8, 15, 12],
'Sales': [1000, 750, 1200, 2250, 1800]
})
# Dataset 2: Product Data
product_data = pd.DataFrame({
'Item_ID': ['P001', 'P002', 'P003', 'P004', 'P005', 'P007'],
'Product_Name': ['Laptop', 'Smartphone', 'Headphones', 'Camera', 'TV', 'Air Conditioner'],
'Category': ['Electronics', 'Electronics', 'Electronics', 'Electronics', 'Appliances', 'Appliances'],
})
merged_diff_keys = pd.merge(sales_data, product_data, left_on='Product_ID', right_on='Item_ID')
print(merged_diff_keys)
Output:
Sale_ID Product_ID Quantity Amount Item_ID Product_Name Category 0 101 P001 10 1000 P001 Laptop Electronics 1 102 P002 5 750 P002 Smartphone Electronics 2 103 P003 8 1200 P003 Headphones Electronics 3 104 P004 15 2250 P004 Camera Electronics 4 105 P005 12 1800 P005 TV Appliances
2. Handling Duplicate Columns
If your datasets contain the same column name other than the key column, it’s essential to rename the column name. Otherwise, merge datasets by adding x and y with the column name to avoid ambiguity.
import pandas as pd
# Dataset 1: Sales Data
sales_data = pd.DataFrame({
'Sale_ID': [101, 102, 103, 104, 105],
'Product_ID': ['P001', 'P002', 'P003', 'P004', 'P005'],
'Product_Name': ['Printer', 'Smartphone', 'Cooler', 'Camera', 'Tablet'],
'Quantity': [10, 5, 8, 15, 12],
'Sales': [1000, 750, 1200, 2250, 1800]
})
# Dataset 2: Product Data
product_data = pd.DataFrame({
'Product_ID': ['P001', 'P002', 'P003', 'P004', 'P005', 'P007'],
'Product_Name': ['Laptop', 'Smartphone', 'Headphones', 'Camera', 'TV', 'Air Conditioner'],
'Category': ['Electronics', 'Electronics', 'Electronics', 'Electronics', 'Appliances', 'Appliances'],
})
# Renaming columns before merge
product_data = product_data.rename(columns={'Product_Name': 'Product_Name2'})
merged_data = pd.merge(sales_data, product_data, on='Product_ID',how='inner')
print(merged_diff_keys)
Output:
Sale_ID Product_ID Product_Name Quantity Sales Product_Name2 Category 0 101 P001 Printer 10 1000 Laptop Electronics 1 102 P002 Smartphone 5 750 Smartphone Electronics 2 103 P003 Cooler 8 1200 Headphones Electronics 3 104 P004 Camera 15 2250 Camera Electronics 4 105 P005 Tablet 12 1800 TV Appliances
Avoid Duplicate Columns:
You can use suffixes to resolve conflicts over the same data.
merged_with_suffixes = pd.merge(sales_data.reset_index(), product_data, on='Product_ID', suffixes=('_sale', '_product'))
4. Handling Missing Data
It is common to get missing values (NaN) after merging two datasets where no match exists. You can handle missing data using various methods, such as filling it with a default value or dropping rows/columns with missing data.
To fill missing data use the following code.
# Fill missing values with a placeholder
fill_data = merged_outer.fillna('Unknown')
print(fill_data)
Output:
Sale_ID Product_ID Quantity Sales Product_Name Category Price 0 101 P001 10 1000 Laptop Electronics 100 1 102 P002 5 750 Smartphone Electronics 150 2 103 P003 8 1200 Headphones Electronics 150 3 104 P004 15 2250 Camera Electronics 150 4 105 P005 12 1800 TV Appliances 500 5 Unknown P007 Unknown Unknown Air Conditioner Appliances 700
To drop rows/columns of missing data use the following code.
# Drop rows with missing values drop_data = merged_outer.dropna() print(drop_data)
Output:
Sale_ID Product_ID Quantity Sales Product_Name Category Price 0 101.0 P001 10.0 1000.0 Laptop Electronics 100 1 102.0 P002 5.0 750.0 Smartphone Electronics 150 2 103.0 P003 8.0 1200.0 Headphones Electronics 150 3 104.0 P004 15.0 2250.0 Camera Electronics 150 4 105.0 P005 12.0 1800.0 TV Appliances 500
5. Efficient Merging
While working with large datasets, always try to limit the size of the datasets you are merging by using filters or indexes to reduce the computational cost. You can also use sort=True to speed up mergers when merging on a sorted column.
# Efficient merge with sorted DataFrames sales_data = sales_data.sort_values(by='Product_ID') product_data = product_data.sort_values(by='Product_ID') merged_sorted_data = pd.merge(sales_data, product_data, on='Product_ID', how='inner', sort=True) print(merged_sorted_data)
Output:
Sale_ID Product_ID Quantity Sales Product_Name Category Price 0 101 P001 10 1000 Laptop Electronics 100 1 102 P002 5 750 Smartphone Electronics 150 2 103 P003 8 1200 Headphones Electronics 150 3 104 P004 15 2250 Camera Electronics 150 4 105 P005 12 1800 TV Appliances 500
6. Avoiding the Dreaded Cartesian Product
When using pd.merge(), if you don’t specify the right-on columns or if there are multiple matching rows in each dataset, the result could be a Cartesian product.
Where every row in one dataset matches with every row in the other dataset. This can increase the size of the resulting dataset.
Conclusion
Merging and joining datasets are necessary skills in data analysis. By exploring different join types and best practices, you can efficiently combine datasets to extract meaningful insights. These techniques will help you to work with relational datasets. By following these best practices, you can efficiently combine datasets and ensure your data analysis tasks run smoothly.
nice coding…