# Join, Merge, and Combine Multiple Datasets Using pandas

Data processing becomes critical when training a robust machine learning model. We occasionally need to restructure and add new data to the datasets to increase the efficiency of the data.

We'll look at how to combine multiple datasets and merge multiple datasets with the same and different column names in this article. We'll use the `pandas` library's following functions to carry out these operations.

* `pandas.concat()`
    
* `pandas.merge()`
    
* `pandas.DataFrame.join()`
    

## Preparing Sample Data

We'll create sample datasets using `pandas.DataFrame()` function and then perform concatenating operations on them.

![Sample data creation](https://cdn.hashnode.com/res/hashnode/image/upload/v1688312481838/756e4599-13f5-441d-bd04-151c597a8f90.png align="center")

The code in the image will generate two datasets from `data` and `data1` using `pd.DataFrame(data)` and `pd.DataFrame(data1)` and store them in the variables `df1` and `df2`.

Then, using the `.to_csv()` function, `df1` and `df2` will be saved in the `CSV` format as `'employee.csv'` and `'employee1.csv'` respectively.

Here, the data that we created looks as shown in the following image.

![Data preview](https://cdn.hashnode.com/res/hashnode/image/upload/v1688315788419/0c4dcd89-2b72-4a9c-a8a6-fb11f8f46721.png align="center")

# Combining Data Using concat()

We can use the `pandas` library to analyze, modify, and do other things with our **CSV** (comma-separated value) data. The library includes the `concat()` function which we will use to perform the concatenation of multiple datasets.

There are two axes on which the datasets can be concatenated: the **row axis** and the **column axis**.

![Visual representation of concatenation on different axis](https://cdn.hashnode.com/res/hashnode/image/upload/v1688379587719/1ba7cc5d-0273-4573-a4cb-c101bc12ad48.png align="center")

## Combine Data Along the Row Axis

We previously created two datasets named `'employee.csv'` and `'employee1.csv'`. We'll concatenate them horizontally, which means the data will be spliced across the rows.

```python
combine = pd.concat([dt, dt1])
```

The above code demonstrates the basic use of the `concat()` function. We passed a list of datasets(`objects`) that will be combined along the **row axis** by default.

![Concatenated dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688316951499/27159683-2e33-4660-9f6f-c4aa8b690141.png align="center")

The `concat()` function accepts some parameters that affect the concatenation of the data.

The indices of the data are taken from their corresponding data, as seen in the above output. How do we create a new data index?

### **The** `ignore_index` **Parameter**

When `ignore_index=True` is set, a new index from `0` to `n-1` is created. The default value is `False`, which is why the indices were repeated in the above example.

```python
set_index = pd.concat([dt, dt1], ignore_index=True)
```

![Created new index](https://cdn.hashnode.com/res/hashnode/image/upload/v1688367288985/c2c43671-1aed-41e8-88ca-64ba2c070968.png align="center")

As shown in the image above, the dataset contains a new index ranging from `0` to `7`.

### **The** `join` **Parameter**

In the above image, we can see that the first four data points for the `Salary` and `No_of_awards` columns are missing.

This is due to the `join` parameter, which by default is set to `"outer"` which joins the data exactly as it is. If it is set to `"inner"`, data that does not match another dataset is removed.

```python
inner_join = pd.concat([dt, dt1], join="inner")
```

![Inner join](https://cdn.hashnode.com/res/hashnode/image/upload/v1688368658034/f1d72033-cf6e-4a91-bfa1-2693c3072f2f.png align="center")

### The `keys` Parameter

The `keys` parameter creates an index from the **keys** which is used to differentiate and identify the original data in the concatenated objects.

```python
keys = pd.concat([dt, dt1], keys=["Dataset1", "Dataset2"])
```

![Key index](https://cdn.hashnode.com/res/hashnode/image/upload/v1688371834403/ad99e572-4cfa-4a9a-bab3-49b5da309824.png align="center")

The datasets were concatenated, and a multi-level index was created, with the first level representing the outermost index (`Dataset1` and `Dataset2` from the `keys`) and the second level representing the original index.

## Combine Data Along the Column Axis

The datasets were concatenated along the row axis or horizontally in the previous section, but in this approach, we will stitch them **vertically** or along the **column axis** using the `axis` parameter.

The `axis` parameter is set to `0` or `"index"` by default, which concatenates the datasets along the **row axis**, but if we change its value to `1` or `"columns"`, it concatenates the datasets along the **column axis**.

```python
combine_vertically = pd.concat([dt, dt1], axis="columns")
#---------------------------OR---------------------------#
combine_vertically = pd.concat([dt, dt1], axis=1)
```

![Datasets concatenated vertically](https://cdn.hashnode.com/res/hashnode/image/upload/v1688376142904/a853436f-fdda-4aae-9c1c-7947f692a7f4.png align="center")

# Merging Data Using merge()

The `pandas.merge()` function merges data from one or more datasets based on common columns or indices.

We'll operate on a different dataset that we created and contains the information shown in the following image.

![Sample data preview](https://cdn.hashnode.com/res/hashnode/image/upload/v1688401571402/b5dcd788-c78d-4d6c-8726-469421b0598c.png align="center")

The `merge()` function takes `left` and `right` parameters which are datasets to be merged.

## The `how` Parameter

We can now specify the type of merge we want to perform on these datasets by providing the `how` parameter. The `how` parameter allows for five different types of merges:

* `inner`: Default. It only includes the values that match from both datasets.
    
* `outer`: It includes all of the values from both datasets but fills the missing values with `NaN` (**Not a Number**).
    
* `left`: It includes all of the values from the left dataset and replaces any missing values in the right dataset with `NaN`.
    
* `right`: It includes all of the values from the right dataset and replaces any missing values in the left dataset with `NaN`.
    
* `cross`: It creates the Cartesian product which means that the number of rows created will be equal to the product of the row counts of both datasets. If both datasets have four rows, then four times four (`4 * 4`) equals sixteen (`16`) rows.
    

### Examples

**Performing** `inner` **merge**

```python
inner_merging = pd.merge(dt1, dt2, how="inner")
```

![Inner merged dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688402124491/ca37d36f-41d5-4d67-b26e-e77fcc3d0acb.png align="center")

We can see that only values with the same `Id` from both datasets have been included.

**Performing** `outer` **merge**

```python
outer_merging = pd.merge(dt1, dt2, how="outer")
```

![Outer merged dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688402225067/dd8f9164-bcbd-4797-b24d-a5f8b684bf03.png align="center")

In the case of a **outer merge**, all of the values from both datasets were included, and the missing fields were filled in with `NaN`.

**Performing** `left` **merge**

```python
left_merging = pd.merge(dt1, dt2, how="left")
```

![Left merged dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688402295683/f05d51d1-d619-4a74-b12d-94da7dd00fac.png align="center")

The matching values of the right dataset (`dt2`) were merged in the left dataset (`dt1`) and the values of the last four columns (`Project_id_final`, `Age`, `Salary`, and `No_of_awards`) were not found for `A4`, so they were filled in with `NaN`.

**Performing** `right` **merge**

```python
right_merging = pd.merge(dt1, dt2, how="right")
```

![Right merged dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688402384016/f560d0da-0ad5-483a-ad17-15cb3f5e5d86.png align="center")

The matching values of the left dataset (`dt1`) were merged in the right dataset (`dt2`) and the values of the first five columns (`Project_id_initial`, `Name`, `Role`, `Experience`, and `Qualification`) were not found for `A6`, so they were filled in with `NaN`.

### **Cross Merging the Datasets**

The `how` parameter has five different types of merge, one of which is a `cross` merge.

As previously stated, it generates the Cartesian product, with the number of rows formed equal to the product of row counts from both datasets. Take a look at the illustration below to get a better understanding.

![Visual representation of the cross join](https://cdn.hashnode.com/res/hashnode/image/upload/v1688460790649/3fd45827-df2f-4839-aab5-7ab166ba7ebd.png align="center")

```python
cross_merging = pd.merge(dt1, dt2, how="cross")
```

![Cross merged dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688402472368/2cc13dcb-65b2-4d69-a6b9-023e3db216df.png align="center")

Both datasets have four rows each, and each row from `dt1` is repeated four times (row count of `dt2`), resulting in a data set of sixteen rows.

## The `on`, `left_on` & `right_on` Parameters

The `on` parameter accepts the name of a column or index(row) to join on. It could be a single name or a list of names.

The `left_on` and `right_on` parameter takes a column or index(row) name from the left and right dataset to join on. They are used when both datasets have different column names to join on.

### Merging Datasets on the Same Column

To merge the datasets based on the same column, we can use the `on` parameter and pass the common column name that both datasets must have.

```python
merging_on_same_column = pd.merge(dt1, dt2, on='Id')
```

We are merging datasets `dt1` and `dt2` based on the `'Id'` column that they both share.

![Data merged on same column](https://cdn.hashnode.com/res/hashnode/image/upload/v1688468673868/0f5ab5dc-46e8-40f9-8b2f-c9e5eaec822c.png align="center")

The matching `Id` column values from both datasets were merged, and the non-matching values were removed.

### Merging Datasets on Different Columns

To merge different columns in the left and right datasets, use the `left_on` and `right_on` parameters.

```python
left_right_merging = pd.merge(dt1, dt2, left_on="Project_id_initial", right_on='Project_id_final')
```

The joining column is the `"Project_id_initial"` column from the left dataset (`dt1`) and the `"Project_id_final"` column from the right dataset (`dt2`). The values shared by both columns will be used to merge them.

![Dataset merged on different columns](https://cdn.hashnode.com/res/hashnode/image/upload/v1688475877111/b2162899-fc9d-4bd8-b13b-714dcf065068.png align="center")

As we can see, the dataset includes both columns, as well as matching rows based on the common values in both the `"Project_id_initial"` and `"Project_id_final"` columns.

## Changing the Suffix of the Column

If you notice that the merged dataset has two `Id` columns labeled `Id_x` and `Id_y`, this is due to the `suffixes` parameter, which has default values `_x` and `_y`, and when overlapping column names are found in the left and right datasets, they are suffixed with default values.

```python
chg_suffix = pd.merge(dt1, dt2, suffixes=["_1", "_2"], left_on="Project_id_initial", right_on='Project_id_final')
```

This will append the suffixes `"_1"` and `"_2"` to the overlapping columns. Because both datasets have the same column name `Id`, the `Id` column will appear to be `Id_1` in the left dataset and `Id_2` in the right dataset.

![Suffix changed](https://cdn.hashnode.com/res/hashnode/image/upload/v1688478736090/82603a37-1a2b-4e53-b2e1-5e8a9b7a8c25.png align="center")

# Joining Datasets Using join()

The `join()` method works on the `DataFrame` object and joins the columns based on the index values. Let's perform a basic join operation on the dataset.

```python
dt1.join(dt2, lsuffix="_1", rsuffix="_2")
```

The columns of the `dt2` dataset will be joined with the `dt1` dataset based on the **index** values.

![Basic join operation output](https://cdn.hashnode.com/res/hashnode/image/upload/v1688483749935/bee37fdd-39ef-4b5c-ad84-fbd3af3da30e.png align="center")

Since the **index** values of both datasets are the same which is `0`, `1`, `2`, and `3`, that's why we got all the rows.

The `join()` method's parameters can be used to manipulate the dataset. The `join()` method, like the `merge()` function, includes `how` and `on` parameters.

* `how`: Default value is `left` join. It is the same as the `how` parameter of the `merge()` function, but the difference is that it performs **index-based** joins.
    
* `on`: A column or index name is required to join on the index in the specified dataset.
    
* `lsuffix` and `rsuffix`: Used to append the suffix to the left and right datasets' overlapping columns.
    

## Examples

**Left join on an index**

```python
dt1.join(dt2.set_index("Id"), on="Id", how="left")
#-------------------------OR----------------------#
dt1.join(dt2.set_index("Id"), on="Id")
```

In the above code, we use `set_index('Id')` to set the `Id` column of the `dt2` dataset as the index and perform a left join (`how="left"`) on the `Id` column (`on="Id"`) between `dt1` and `dt2`.

This will join matching values in the `Id` column of the `dt2` dataset with the `Id` column of the `dt1` dataset. If any values are missing, they will be filled in by `NaN`.

![Left joined dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688537470259/b08bc717-335b-4528-a8f8-39044abf9df8.png align="center")

It's the same as when we used the `merge()` function, but this time we're joining based on the **index**.

**Right join on an index**

```python
dt1.join(dt2.set_index("Id"), on="Id", how="right")
```

![Right joined dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688538412033/4b39b845-c422-42ca-993f-8f297fdf5151.png align="center")

We are joining the `dt1` dataset with the index of the `dt2` dataset based on the `Id` column. We got `NaN` in the first five columns for `A6` because there were no values specified in the `dt1` dataset.

**Inner join on an index**

```python
dt1.join(dt2.set_index("Id"), on="Id", how="inner")
```

![Inner joined dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688539283466/7fb5dccd-d3d6-4e25-8759-44aa42001f2f.png align="center")

The datasets were joined based on matching index values, i.e., both datasets `dt1` and `dt2` share `A1`, `A2`, and `A3`, so the values corresponding to these indices were joined.

**Outer join on an index**

```python
dt1.join(dt2.set_index("Id"), on="Id", how="outer")
```

![Outer joined dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688557033359/ee2d20f0-ec8a-4c6a-8fb8-9a7f7fdec2da.png align="center")

We performed the outer join, which included all of the rows from both datasets based on the `Id`. The corresponding values have been filled in, and missing values have been filled in with `NaN`.

**Cross Join**

```python
dt1.join(dt2, how="cross", lsuffix="_1", rsuffix="_2")
```

We didn't pass the `on` parameter, instead, we defined how the data should join (`how="cross"`). The resulting dataset will be the product of both datasets' row counts.

![Cross joined dataset](https://cdn.hashnode.com/res/hashnode/image/upload/v1688558752348/93631bed-e114-4145-b1dc-82b242ab97d0.png align="center")

# Conclusion

We've learned how to use `pandas.concat()`, `pandas.merge()`, and `pandas.DataFrame.join()` to combine, merge, and join DataFrames.

The `concat()` function in `pandas` is a go-to option for combining the DataFrames due to its simplicity. However, if we want more control over how the data is joined and on which column in the DataFrame, the `merge()` function is a good choice. If we want to join data based on the index, we should use the `join()` method.

---

🏆**Other articles you might be interested in if you liked this one**

✅[How to use assert statements for debugging in Python](https://geekpython.in/python-assert)?

✅[How to write unit tests using the unittest module in Python](https://geekpython.in/unit-tests-in-python)?

✅[What are the uses of asterisk(\*) in Python](https://geekpython.in/asterisk-in-python)?

✅[What are the init and new methods in Python](https://geekpython.in/init-vs-new)?

✅[How to build a custom deep learning model using Python](https://geekpython.in/using-transfer-learning-for-deep-learning-model)?

✅[How to generate temporary files and directories using tempfile in Python](https://geekpython.in/tempfile-in-python)?

✅[How to run the Flask app from the terminal](https://geekpython.in/run-flask-app-from-the-command-line-in-windows)?

---

**That's all for now**

**Keep coding✌✌**