The Series and DataFrame objects in pandas are powerful tools for exploring and analyzing data. Part of their power comes from a multifaceted approach to combining separate datasets. With pandas, you can merge, join, and concatenate your datasets, allowing you to unify and better understand your data as you analyze it.

In this tutorial, you’ll learn how and when to combine your data in pandas with:

  • merge() for combining data on common columns or indices
  • .join() for combining data on a key column or an index
  • concat() for combining DataFrames across rows or columns

If you have some experience using DataFrame and Series objects in pandas and you’re ready to learn how to combine them, then this tutorial will help you do exactly that. If you’re feeling a bit rusty, then you can watch a quick refresher on DataFrames before proceeding.

You can follow along with the examples in this tutorial using the interactive Jupyter Notebook and data files available at the link below:

pandas merge(): Combining Data on Common Columns or Indices

The first technique that you’ll learn is merge(). You can use merge() anytime you want functionality similar to a database’s join operations. It’s the most flexible of the three operations that you’ll learn.

When you want to combine data objects based on one or more keys, similar to what you’d do in a relational database, merge() is the tool you need. More specifically, merge() is most useful when you want to combine rows that share data.

You can achieve both many-to-one and many-to-many joins with merge(). In a many-to-one join, one of your datasets will have many rows in the merge column that repeat the same values. For example, the values could be 1, 1, 3, 5, and 5. At the same time, the merge column in the other dataset won’t have repeated values. Take 1, 3, and 5 as an example.

As you might have guessed, in a many-to-many join, both of your merge columns will have repeated values. These merges are more complex and result in the Cartesian product of the joined rows.

This means that, after the merge, you’ll have every combination of rows that share the same value in the key column. You’ll see this in action in the examples below.

What makes merge() so flexible is the sheer number of options for defining the behavior of your merge. While the list can seem daunting, with practice you’ll be able to expertly merge datasets of all kinds.

When you use merge(), you’ll provide two required arguments:

  1. The left DataFrame
  2. The right DataFrame

After that, you can provide a number of optional arguments to define how your datasets are merged:

  • how defines what kind of merge to make. It defaults to 'inner', but other possible options include 'outer', 'left', and 'right'.

  • on tells merge() which columns or indices, also called key columns or key indices, you want to join on. This is optional. If it isn’t specified, and left_index and right_index (covered below) are False, then columns from the two DataFrames that share names will be used as join keys. If you use on, then the column or index that you specify must be present in both objects.

  • left_on and right_on specify a column or index that’s present only in the left or right object that you’re merging. Both default to None.

  • left_index and right_index both default to False, but if you want to use the index of the left or right object to be merged, then you can set the relevant argument to True.

  • suffixes is a tuple of strings to append to identical column names that aren’t merge keys. This allows you to keep track of the origins of columns with the same name.

These are some of the most important parameters to pass to merge(). For the full list, see the pandas documentation.

How to Use merge()

Before getting into the details of how to use merge(), you should first understand the various forms of joins:

  • inner
  • outer
  • left
  • right

You’ll learn about these different joins in detail below, but first take a look at this visual representation of them:

Read the full article at https://realpython.com/pandas-merge-join-and-concat/ »


[ Improve Your Python With 🐍 Python Tricks 💌 – Get a short & sweet Python Trick delivered to your inbox every couple of days. >> Click here to learn more and see examples ]