# How to merge DataFrames with pandas merge() With the pandas function DataFrame.merge(), you can combine DataFrames using keys. This allows you to efficiently combine data from various sources, enabling you to carry out more comprehensive analyses. ## What is the syntax for pandas `merge()`? The [Python pandas](https://www.ionos.ca/digitalguide/websites/web-development/python-pandas/) DataFrame `merge()` method can accept a range of different parameters, allowing developers to specify how DataFrames should be combined. The general syntax of the `merge()` function is as follows: ```python DataFrame.merge(left, right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=False, suffixes=('_x', '_y'), copy=True, indicator=False, validate=None) ``` Note The pandas merge() function is similar to the [JOIN operation in SQL](https://www.ionos.ca/digitalguide/server/configuration/sql-join/). Having experience with [relational databases](https://www.ionos.ca/digitalguide/hosting/technical-matters/relational-databases/) like [SQL](https://www.ionos.ca/digitalguide/server/know-how/what-is-sql/) can make it easier to understand how the pandas DataFrame merge() method words. Keep in mind, though, that there are some differences. In pandas, if both key columns have values that evaluate to *null*, those values will also be merged. ### What parameters can be used with pandas merge? The various parameters accepted by `merge()` allow you to not only specify which [pandas DataFrames](https://www.ionos.ca/digitalguide/websites/web-development/python-pandas-dataframe/) to combine but also which type of join to use as well as other details.
Parameter Description Default Value
left The first DataFrame to be merged
right The second DataFrame to be merged
how The type of join operation to perform (inner, outer, left, right) inner
on The column(s) or index level(s) to use as keys; must be present in both DataFrames
left\_on The column(s) or index level(s) of the left DataFrame that should be used as the key(s)
right\_on The column(s) or index level(s) of the right DataFrame that should be used as the key(s)
left\_index If True, the index of the left DataFrame is used as the key False
right\_index If True, the index of the right DataFrame is used as the key False
sort If True, the resulting DataFrame’s keys are sorted lexicographically False
suffixes Suffixes used to distinguish columns with the same name ("_x", "_y")
copy If False, a copy is avoided True
## How to use pandas merge() Below are some examples to help illustrate how to use pandas `merge()` effectively. ### `INNER JOIN` An [INNER JOIN](https://www.ionos.ca/digitalguide/hosting/technical-matters/sql-inner-join/) combines two pandas DataFrames and **only returns the rows where the keys match in both DataFrames**. To get a better idea of how this works, let’s create two DataFrames: ```python import pandas as pd # Sample DataFrames df1 = pd.DataFrame({ 'Key': ['A', 'B', 'C'], 'Value1': [1, 2, 3] }) df2 = pd.DataFrame({ 'Key': ['B', 'C', 'D'], 'Value2': [4, 5, 6] }) print(df1) print(df2) ``` The two resulting DataFrames look like this: ```none Key Value1 0 A 1 1 B 2 2 C 3 Key Value2 0 B 4 1 C 5 2 D 6 ``` Now, we can perform an INNER JOIN using the merge() function: ```python # INNER JOIN result = pd.merge(df1, df2, how='inner', on='Key') print(result) ``` The output shows that only the rows that have keys B and C are included in the DataFrame. This is because these two keys exist in **both of the original DataFrames**. ```none Key Value1 Value2 0 B 2 4 1 C 3 5 ``` ### OUTER JOIN An `OUTER JOIN` also merges two DataFrames, but unlike `INNER JOIN`, it returns **all the rows** and fills in **missing values with `NaN`**. ```python # OUTER JOIN result = pd.merge(df1, df2, how='outer', on='Key') print(result) ``` The DataFrame below includes **all the rows from both DataFrames**. NaN is used for the missing values in key A, which is only in df1 and key D, which is only in df2. ```none Key Value1 Value2 0 A 1.0 NaN 1 B 2.0 4.0 2 C 3.0 5.0 3 D NaN 6.0 ``` Note The other standard variants of JOIN work in a similar manner. ### Using left_on and right_on Sometimes, two DataFrames have different column names for their keys. In this case, you can use the `left_on` and `right_on` parameters to specify which columns to use. First, let’s create two new DataFrames: ```python df3 = pd.DataFrame({ 'Key': ['A', 'B', 'C'], 'Value1': [1, 2, 3] }) df4 = pd.DataFrame({ 'Key2': ['B', 'C', 'D'], 'Value2': [4, 5, 6] }) print(df3) print(df4) ``` The two DataFrames look like this: ```none Key Value1 0 A 1 1 B 2 2 C 3 Key2 Value2 0 B 4 1 C 5 2 D 6 ``` We can use the left_on and right_on parameters to perform the JOIN operation using different keys: ```python # Join with different key column names result = pd.merge(df3, df4, how='inner', left_on='Key', right_on='Key2') print(result) ``` By explicitly using left_on='Key' and right_on='Key2', the corresponding key columns are utilized for the merge. ```none Key Value1 Key2 Value2 0 B 2 B 4 1 C 3 C 5 ``` ### Using indices as keys You can also use the **indices of DataFrames as keys** by setting the `left_index` and `right_index` parameters to `True`. First, let’s create two new DataFrames with indices: ```python df5 = pd.DataFrame({ 'Value1': [1, 2, 3] }, index=['A', 'B', 'C']) df6 = pd.DataFrame({ 'Value2': [4, 5, 6] }, index=['B', 'C', 'D']) print(df5) print(df6) ``` Here are the DataFrames: ```none Value1 A 1 B 2 C 3 Value2 B 4 C 5 D 6 ``` Now, we can perform a JOIN operation using the indices: ```python # JOIN with indices result = pd.merge(df5, df6, how='inner', left_index=True, right_index=True) print(result) ``` The result is a JOIN that uses the indices from the DataFrames: ```none Value1 Value2 B 2 4 C 3 5 ``` This is a markdown version of: [https://www.ionos.ca/digitalguide/websites/web-development/python-pandas-dataframe-merge/](https://www.ionos.ca/digitalguide/websites/web-development/python-pandas-dataframe-merge/) for AI/LLM consumption.