Python | Pandas Merging, Joining and Concatenating

When we're working with multiple datasets we need to combine them in different ways. Pandas provides three simple methods like merging, joining and concatenating. These methods help us to combine data in various ways whether it's matching columns, using indexes or stacking data on top of each other. In this article, we'll see these methods.

Concatenating DataFrames

Concatenating DataFrames means combining them either by stacking them on top of each other (vertically) or placing them side by side (horizontally). In order to Concatenate dataframe, we use different methods which are as follows:

1. Concatenating DataFrame using .concat()

To concatenate DataFrames, we use the pd.concat() function. This function allows us to combine multiple DataFrames into one by specifying the axis (rows or columns).

Here we will be loading and printing the custom dataset, then we will perform the concatenation using pd.concat().

Output:

Now we apply .concat function in order to concat two dataframe.

Output:

2. Concatenating DataFrames by Setting Logic on Axes

We can modify the concatenation by setting logic on the axes. Specifically we can choose whether to take the Union (join='outer') or Intersection (join='inner') of columns.

Output:

Now we set axes join = inner for intersection of dataframe which keeps only the common columns.

Output:

Now we set axes join = outer for union of dataframe which keeps all columns from both DataFrames.

Output:

3. Concatenating DataFrames by Ignoring Indexes

Sometimes the indexes of the original DataFrames may not be relevant. We can ignore the indexes and reset them using the ignore_index argument. This is useful when we don't want to carry over any index information.

Output:

Now we are going to apply ignore_index as an argument.

Output:

4. Concatenating DataFrame with group keys :

If we want to retain information about the DataFrame from which each row came, we can use the keys argument. This assigns a label to each group of rows based on the source DataFrame.

Output:

Here we will use keys as an argument. The keys argument creates a hierarchical index where each row is labeled with the source DataFrame (df1 or df2).

Output:

5. Concatenating Mixed DataFrames and Series

We can also concatenate a mix of Series and DataFrames. If we include a Series in the list, it will automatically be converted to a DataFrame and we can specify the column name.

Output:

Here we are going to mix Series and dataframe together.

Output:

Merging DataFrame

Merging DataFrames in Pandas is similar to performing SQL joins. It is useful when we need to combine two DataFrames based on a common column or index. The merge() function provides flexibility for different types of joins.

There are four basic ways to handle the join (inner, left, right and outer) depending on which rows must retain their data.

1. Merging DataFrames Using One Key

We can merge DataFrames based on a common column by using the on argument. This allows us to combine the DataFrames where values in a specific column match.

Output:

Now here we are using .merge() with one unique key combination.

Output:

2. Merging DataFrames Using Multiple Keys

We can also merge DataFrames based on more than one column by passing a list of column names to the on argument.

Output:

Now we merge dataframe using multiple keys.

Output:

3. Merging DataFrames Using the how Argument

We use how argument to merge specifies how to find which keys are to be included in the resulting table. If a key combination does not appear in either the left or right tables, the values in the joined table will be NA. Here is a summary of the how options and their SQL equivalent names:

Output:

Now we set how = 'left' in order to use keys from left frame only. In this it includes all rows from the left DataFrame and only matching rows from the right.

Output:

 Now we set how = 'right' in order to use keys from right frame only. In this it includes all rows from the right DataFrame and only matching rows from the left.

Output:

 Now we set how = 'outer' in order to get union of keys from dataframes. In this it combines all rows from both DataFrames, filling missing values with NaN.

Output:

 Now we set how = 'inner' in order to get intersection of keys from dataframes. In this it only includes rows where there is a match in both DataFrames.

Output:

Joining DataFrame

The .join() method in Pandas is used to combine columns of two DataFrames based on their indexes. It's a simple way of merging two DataFrames when the relationship between them is primarily based on their row indexes. It is used when we want to combine DataFrames along their indexes rather than specific columns.

1. Joining DataFrames Using .join()

If both DataFrames have the same index, we can use the .join() function to combine their columns. This method is useful when we want to merge DataFrames based on their row indexes rather than columns.

Output:

Now we are using .join() method in order to join dataframes

Output:

 Now we use how = 'outer' in order to get union

Output:

2. Joining DataFrames Using the "on" Argument

If we want to join DataFrames based on a column (rather than the index), we can use the on argument. This allows us to specify which column(s) should be used to align the two DataFrames.

Output:

Now we are using .join with “on” argument.

Output:

3. Joining DataFrames with Different Index Levels (Multi-Index)

In some cases, we may be working with DataFrames that have multi-level indexes. The .join() function also supports joining DataFrames that have different index levels by specifying the index levels.

Output:

Now we join singly indexed dataframe with multi-indexed dataframe.

Output: