A Comprehensive Guide to DataFrame Concatenation
[
Combining Data in pandas With merge(), .join(), and concat()
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 indexconcat()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:
Note: The techniques that you’ll learn about below will generally work for both DataFrame and Series objects. But for simplicity and concision, the examples will use the term dataset to refer to objects that can be either DataFrames or Series.
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.
How to Use merge()
To use merge(), you need two datasets that you want to merge. Here’s the basic syntax:
pd.merge(left, right, on=None, left_on=None, right_on=None, left_index=False, right_index=False, how='inner')leftandrightare the DataFrames that you want to merge.onis the column or index level names that you want to merge on. If left and right have the same column name, you can simply useon='column_name'.left_onandright_onare the column names you want to merge on from the separate datasets. Use these if the column names are different in the two datasets.left_indexandright_indexare boolean values indicating whether you want to merge on the left or right index, respectively.howspecifies what kind of merge to perform. The default is'inner', which performs an inner join.
Examples
Let’s look at some examples to understand how merge() works:
Example 1: Merging on a Single Column
Suppose you have two datasets: df1 and df2. You want to merge them on a common column, 'key'. Here’s how you can do it:
import pandas as pd
df1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'], 'value1': [1, 2, 3, 4]})df2 = pd.DataFrame({'key': ['B', 'D', 'E', 'F'], 'value2': [5, 6, 7, 8]})
result = pd.merge(df1, df2, on='key')
print(result)The output will be:
key value1 value20 B 2 51 D 4 6In this example, the resulting DataFrame contains only the rows that have a common 'key' value in both df1 and df2.
Example 2: Merging on Multiple Columns
In some cases, you might want to merge on multiple columns. Here’s an example:
import pandas as pd
df1 = pd.DataFrame({'key1': ['A', 'B', 'C', 'D'], 'key2': [1, 2, 3, 4], 'value1': [5, 6, 7, 8]})df2 = pd.DataFrame({'key1': ['B', 'D', 'E', 'F'], 'key2': [2, 4, 5, 6], 'value2': [9, 10, 11, 12]})
result = pd.merge(df1, df2, on=['key1', 'key2'])
print(result)The output will be:
key1 key2 value1 value20 B 2 6 91 D 4 8 10In this example, the resulting DataFrame contains only the rows that have common values in both 'key1' and 'key2' columns in both df1 and df2.
Using merge(), you can perform many more types of merges, such as many-to-one and many-to-many joins. Check out the official pandas documentation for more information and examples.
pandas .join(): Combining Data on a Column or Index
The second technique that you’ll learn is .join(). You can use .join() when you want to combine your data based on either columns or indices. It’s a convenient method when you have two DataFrames with the same index or a common column.
How to Use .join()
The basic syntax for .join() is very similar to merge():
left.join(right, lsuffix='', rsuffix='', sort=False)leftandrightare the DataFrames that you want to join.lsuffixandrsuffixare string suffixes to use for overlapping column names inleftandright, respectively.sortis a boolean value indicating whether to sort the resulting DataFrame by the join keys.
Examples
Here are some examples to help you understand how .join() works:
Example 1: Joining on Index
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'], 'B': ['B0', 'B1', 'B2']}, index=[0, 1, 2])df2 = pd.DataFrame({'C': ['C0', 'C1', 'C2'], 'D': ['D0', 'D1', 'D2']}, index=[0, 2, 3])
result = df1.join(df2)
print(result)The output will be:
A B C D0 A0 B0 C0 D01 A1 B1 NaN NaN2 A2 B2 C1 D1In this example, df1 and df2 have overlapping indices. When you use .join() without any additional parameters, it performs a left join by default.
Example 2: Joining on a Column
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'], 'B': ['B0', 'B1', 'B2'], 'key': ['K0', 'K1', 'K2']})df2 = pd.DataFrame({'C': ['C0', 'C1', 'C2'], 'D': ['D0', 'D1', 'D2'], 'key': ['K1', 'K2', 'K3']})
result = df1.join(df2.set_index('key'), on='key')
print(result)The output will be:
A B key C D0 A0 B0 K0 NaN NaN1 A1 B1 K1 C0 D02 A2 B2 K2 C1 D1In this example, df1 and df2 have a common column, 'key'. The resulting DataFrame contains the joined rows where the values in the 'key' column match.
pandas concat(): Combining Data Across Rows or Columns
The third technique that you’ll learn is concat(). You can use concat() when you want to combine DataFrames along either the rows or the columns. It’s useful for stacking or concatenating datasets.
How to Use concat()
The basic syntax for concat() is as follows:
pd.concat(objs, axis=0, join='outer', ignore_index=False)objsis a sequence or mapping of DataFrame or Series objects that you want to concatenate.axisis an integer specifying whether to concatenate along the rows (axis=0) or the columns (axis=1).joinspecifies how to handle overlapping index or columns names. The default is'outer', which performs a union of the indexes or columns.ignore_indexis a boolean value indicating whether to create a new index or maintain the original index values.
Examples
Here are some examples to help you understand how concat() works:
Example 1: Concatenating Along Rows
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'], 'B': ['B0', 'B1', 'B2']})df2 = pd.DataFrame({'A': ['A3', 'A4', 'A5'], 'B': ['B3', 'B4', 'B5']})
result = pd.concat([df1, df2])
print(result)The output will be:
A B0 A0 B01 A1 B12 A2 B20 A3 B31 A4 B42 A5 B5In this example, df1 and df2 have the same column names, so concat() concatenates along the rows by default.
Example 2: Concatenating Along Columns
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'], 'B': ['B0', 'B1', 'B2']})df2 = pd.DataFrame({'C': ['C0', 'C1', 'C2'], 'D': ['D0', 'D1', 'D2']})
result = pd.concat([df1, df2], axis=1)
print(result)The output will be:
A B C D0 A0 B0 C0 D01 A1 B1 C1 D12 A2 B2 C2 D2In this example, df1 and df2 have different column names, so concat() concatenates along the columns.
Conclusion
In this tutorial, you’ve learned how to combine your data in pandas using merge(), .join(), and concat(). These functions provide powerful tools for unifying and analyzing your data. By understanding when and how to use each function, you can efficiently manipulate and explore your datasets in pandas.
