Combining Multiple Datasets: Merging, Joining, and Concatenating

Welcome back, aspiring data scientists! Today, we are going to dive into one of the most important skills you’ll need in data preparation: combining multiple datasets. In real-world scenarios, data is often scattered across different files, databases, or even APIs. Being able to combine this data effectively is crucial for building comprehensive datasets that are ready for analysis.

By the end of this guide, you’ll understand different ways to combine datasets, when to use which method, and how to handle any issues that might come up. Let’s get started!

Why Combine Datasets?

Combining datasets is an essential part of data preparation. Here’s why:

• Creating a Complete Picture: Information about a particular topic is often divided across multiple sources. For example, customer demographics might be in one file, while purchase history is in another.
• Better Analysis: When you combine different datasets, you gain access to more features, which can help in creating more accurate machine learning models.
• Handling Fragmented Data: Datasets are often broken into parts, and combining them allows you to leverage all the available data effectively.

Ways to Combine Datasets

There are several ways to combine datasets, and each method has its own purpose. The most common techniques are merging, joining, and concatenating. Let’s explore each one in detail.

1. Merging Datasets

Merging is like combining two datasets based on a common column. Think of it as similar to looking up a value in a dictionary—you connect information from one dataset to another based on a key.

• Inner Merge: Keeps only the rows that have matching keys in both datasets.
• Outer Merge: Keeps all rows, regardless of whether they have matches in both datasets. Missing values are filled with NaN.
• Left Merge: Keeps all rows from the left dataset and the matching rows from the right dataset.
• Right Merge: Keeps all rows from the right dataset and the matching rows from the left dataset.

Example: Suppose you have two datasets—one contains customer information (customer_id, name, location), and the other contains purchase details (customer_id, product, price). By merging them on customer_id, you can see what each customer bought.

import pandas as pd

# Customer Data
df_customers = pd.DataFrame({
    'customer_id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'location': ['New York', 'Los Angeles', 'Chicago']
})

# Purchase Data
df_purchases = pd.DataFrame({
    'customer_id': [1, 2, 4],
    'product': ['Laptop', 'Tablet', 'Phone'],
    'price': [1200, 300, 800]
})

# Merge the dataframes on customer_id
merged_df = pd.merge(df_customers, df_purchases, on='customer_id', how='inner')
print(merged_df)

This would merge the rows based on customer_id and show only customers who have purchase records.

2. Joining Datasets

Joining is similar to merging, but it is used when you have dataframes with an index that you want to match on. It’s more suitable when working with relational data.

• Left Join: Retains all rows from the left dataframe and only matching rows from the right dataframe.
• Right Join: Retains all rows from the right dataframe and only matching rows from the left dataframe.

Example: Let’s say you have two dataframes, and one of them has a customer_id as the index. You can use .join() to combine these datasets.

# Set customer_id as the index
df_customers.set_index('customer_id', inplace=True)
joined_df = df_customers.join(df_purchases.set_index('customer_id'), how='left')
print(joined_df)

This will combine the customer information with the purchase data, even if some customers haven’t made a purchase yet.

3. Concatenating Datasets

Concatenation is used when you want to add datasets either vertically (stacking rows) or horizontally (adding columns). This is useful when you have datasets that contain similar information but in separate files.

• Vertical Concatenation: Stack datasets on top of each other, such as when you have monthly sales data in separate files.
• Horizontal Concatenation: Add datasets side by side, such as when adding more columns of features.

Example: Let’s concatenate two datasets that contain sales data for different months.

# Sales data for January
df_january = pd.DataFrame({
    'product': ['Laptop', 'Tablet'],
    'sales': [10, 15]
})

# Sales data for February
df_february = pd.DataFrame({
    'product': ['Laptop', 'Tablet'],
    'sales': [12, 18]
})

# Concatenate the dataframes vertically
concatenated_df = pd.concat([df_january, df_february], ignore_index=True)
print(concatenated_df)

This will stack the January and February data on top of each other.

Handling Issues When Combining Datasets

Combining datasets is not always straightforward—sometimes you may encounter issues such as:

• Missing Values: Merging and joining may result in missing values (NaN). You need to decide whether to fill them, drop them, or handle them in another way.
• Duplicates: Ensure there are no unintended duplicate rows after combining datasets.
• Data Type Mismatches: Columns from different datasets might have different data types, which can cause errors.

Example: Handling Missing Values

When you perform an outer merge, missing values are common. You can use .fillna() to replace them with a default value.

# Fill missing values with a default
merged_df.fillna('N/A', inplace=True)
print(merged_df)

Mini Project: Merge Sales and Customer Data

Try this small exercise to practice combining datasets.

1. You have two datasets: one contains customer_id, name, and email, and the other contains customer_id, purchase_date, and amount.
2. Merge these datasets to create a complete view of customer transactions.
3. Fill any missing values with a placeholder.

Quiz Time!

1. Which of the following is a valid way to combine datasets?

• a) Stacking
• b) Merging
• c) Painting

1. What does an inner merge do?

• a) Keeps only matching rows from both datasets
• b) Keeps all rows from both datasets
• c) Keeps rows from the left dataset only

Answers: 1-b, 2-a

Key Takeaways

• Merging combines datasets based on common keys, with different types like inner, outer, left, and right merges.
• Joining is useful for combining datasets with indexes, similar to merging.
• Concatenating allows you to add datasets either vertically or horizontally.
• Handling issues like missing values, duplicates, and data type mismatches is important when combining data.

Next Steps

Practice merging, joining, and concatenating different datasets to become more comfortable with these techniques. In our next article, we will discuss Text Data Basics: Preprocessing Text for Analysis, where we will explore how to handle text data effectively. Stay tuned!