Altcademy - a Forbes magazine logo Best Coding Bootcamp 2023

How to join two dataframes in Pandas

Understanding Dataframes in Pandas

Before we dive into the process of joining two dataframes, let's first understand what a dataframe is in the context of Pandas. Imagine you have a spreadsheet full of data, with rows and columns that contain various pieces of information. In Pandas, this spreadsheet is called a dataframe. It's a two-dimensional, size-mutable, and potentially heterogeneous tabular data structure with labeled axes (rows and columns).

To give you a more concrete example, think of a dataframe as a table at a library that holds information about different books. Each row could represent a book, and each column could represent details about the book, like its title, author, and publication year.

Preparing Our Data

Before we can join two dataframes, we need to have some data to work with. Let's create two simple dataframes using Pandas. If you haven't already, you will need to install Pandas in your Python environment using pip install pandas.

import pandas as pd

# Create the first dataframe
df1 = pd.DataFrame({
    'Book ID': [101, 102, 103],
    'Title': ['Book A', 'Book B', 'Book C']

# Create the second dataframe
df2 = pd.DataFrame({
    'Book ID': [101, 102, 104],
    'Author': ['Author X', 'Author Y', 'Author Z']

In this example, df1 contains a 'Book ID' and 'Title' for three different books, while df2 contains a 'Book ID' and 'Author' for three books, two of which match the IDs in df1.

The Concept of Joining Dataframes

Joining dataframes is similar to putting together pieces of a puzzle. You have one piece with certain information and another piece with different information. Your goal is to put them together to see the complete picture. In the world of data, joining is about combining data from two different sources (dataframes) based on a common piece of information, known as a 'key'. In our example, the 'Book ID' column will serve as the key.

Types of Joins

There are several ways to join two dataframes, each serving a different purpose. The four main types of joins are:

  1. Inner Join: This is like the intersection of two sets. Only the keys that are present in both dataframes are included in the final result.
  2. Outer Join: Think of this as the union of two sets. All keys from both dataframes are included, and missing values are filled with NaN (Not a Number, which is Pandas' way of indicating missing data).
  3. Left Join: This join focuses on the 'left' dataframe. It includes all keys from the left dataframe and any matching keys from the right dataframe.
  4. Right Join: As you might guess, this is the opposite of a left join. It includes all keys from the right dataframe and any matching keys from the left dataframe.

Performing an Inner Join

Let's start with an inner join. We want to combine df1 and df2 so that we only have rows with book IDs that exist in both dataframes.

# Perform an inner join
inner_joined_df = pd.merge(df1, df2, on='Book ID', how='inner')


This code will