# join-datatables

## Description

<mark style="color:purple;">`join-datatables`</mark> *adaptor combines two datatables based on the values of a common column between both datatables.*

## Inputs

**`main data`**\
Type: `datatable`\
Required: Yes\
The main (left) datatable. When `inner join` is `False`, all rows from this datatable will be included.

**`main column`**\
Type: `text`\
Required: Yes\
The column in `main data` containing values shared by `other column` in `other data`.

**`other data`**\
Type: `datatable`\
Required: Yes\
The other (right) datatable, to be joined to main datatable.

**`other column`**\
Type: `text`\
Required: No\
The column in `other data` containing values shared by `main column` in `main data`. If more than one row match main column value, only the first matching row will be joined. If unspecified, the name of `main column` will be used.

**`join type`**\
Type: `text`\
Required: No\
Specifies the [joining method](https://www.datasciencemadesimple.com/join-in-r-merge-in-r/). If unspecified, defaults to `Left Join`.

**`case sensitive`**\
Type: `boolean`\
Required: No\
When set to `False`, lowercase and uppercase letters are treated as equivalent when matching values, e.g. `Peru` = `peru`. If unspecified, defaults to `False`.

**`match diacritics`**\
Type: `boolean`\
Required: No\
When set to `False`, letters with and without diacritics are treated as equivalent when matching values, e.g. `Perú` = `Peru`. If unspecified, defaults to `False`.

**`columns`**\
Type: `list`\
Required: No\
Specifies which columns of `other data` to include in the output datatable. If unassigned, all columns in `other data` will be included.

**`prefix`**\
Type: `text`\
Required: No\
An optional text to be prepended to `other data` column names in the output datatable. If unspecified, column names remain unchanged.

## Outputs

**`data`**\
Type: `datatable`\
A datatable containing joined rows and columns.

**`unmatched`**\
Type: `datatable`\
A datatable containing `main data` rows which do not have a match in `other data`.

## Examples

### Example 1: Default behaviour.

#### Inputs:

`main data:`

<table><thead><tr><th width="403">species</th><th>country</th></tr></thead><tbody><tr><td>Bovine</td><td>de</td></tr><tr><td>Gibbon</td><td>fr</td></tr><tr><td>Human</td><td>gb</td></tr><tr><td>Mouse</td><td>GB</td></tr></tbody></table>

`main column:` country

`other data:`&#x20;

| code | name           |
| ---- | -------------- |
| de   | Germany        |
| fr   | France         |
| gb   | United Kingdom |
| us   | United states  |

`other column:` code

`join type:` *null (empty)*

`case sensitive:` *null (empty)*

`match diatrics:` *null (empty)*

`columns:` *null (empty)*

`prefix:` *null (empty)*

#### Outputs:

`data`:

<table><thead><tr><th>species</th><th width="244">country</th><th>code</th><th>name</th></tr></thead><tbody><tr><td>Bovine</td><td>de</td><td>de</td><td>Germany</td></tr><tr><td>Gibbon</td><td>fr</td><td>fr</td><td>France</td></tr><tr><td>Human</td><td>gb</td><td>gb</td><td>United Kingdom</td></tr><tr><td>Mouse</td><td>GB</td><td>GB</td><td>United Kingdom</td></tr></tbody></table>

-> Left Joined the two datatables based on the common columns `country` and `code.`

### Example 2: Join datatables with specific join type&#x20;

#### Inputs:

`main data:`

<table><thead><tr><th width="403">species</th><th>country</th></tr></thead><tbody><tr><td>Bovine</td><td>de</td></tr><tr><td>Gibbon</td><td>fr</td></tr><tr><td>Human</td><td>gb</td></tr><tr><td>Mouse</td><td>GB</td></tr><tr><td>Orangutan</td><td></td></tr><tr><td>Gorilla</td><td></td></tr></tbody></table>

`main column:` country

`other data:`&#x20;

| code | name           |
| ---- | -------------- |
| de   | Germany        |
| fr   | France         |
| gb   | United Kingdom |
| us   | United states  |

`other column:` code

`join type:` Inner Join

`case sensitive:` *null (empty)*

`match diatrics:` *null (empty)*

`columns:` *null (empty)*

`prefix:` *null (empty)*

#### Outputs:

`data`:

| species | country | code | name           |
| ------- | ------- | ---- | -------------- |
| Bovine  | de      | de   | Germany        |
| Gibbon  | fr      | fr   | France         |
| Human   | gb      | gb   | United Kingdom |
| Mouse   | GB      | GB   | United Kingdom |

-> Inner Joined the two datatables based on the common columns `country` and `code.`Since the species `Gorilla` and `Orangutan` have no corresponding country value they are not included in the output.

### Example 3: Join datatable with specific prefix and case sensitive set to true.

#### Inputs:

`main data:`

<table><thead><tr><th width="403">species</th><th>country</th></tr></thead><tbody><tr><td>Bovine</td><td>de</td></tr><tr><td>Gibbon</td><td>fr</td></tr><tr><td>Human</td><td>gb</td></tr><tr><td>Mouse</td><td>GB</td></tr></tbody></table>

`main column:` country

`other data:`&#x20;

| code | name           |
| ---- | -------------- |
| de   | Germany        |
| fr   | France         |
| gb   | United Kingdom |
| us   | United states  |

`other column:` code

`join type:` Left Join

`case sensitive:` *True*

`match diatrics:` *null (empty)*

`columns:` *null (empty)*

`prefix:` *country*

#### Outputs:

`data`:

<table><thead><tr><th>species</th><th width="210">country</th><th>country.code</th><th>country.name</th></tr></thead><tbody><tr><td>Bovine</td><td>de</td><td>de</td><td>Germany</td></tr><tr><td>Gibbon</td><td>fr</td><td>fr</td><td>France</td></tr><tr><td>Human</td><td>gb</td><td>gb</td><td>United Kingdom</td></tr><tr><td>Mouse</td><td>GB</td><td></td><td></td></tr></tbody></table>

-> Left Joined the two datatables based on the common columns `country` and `code`, with prefix `country` applied to the other datatable columns.

## Use Cases

* Join outputs of a bioinformatics pipeline with the sample metadata to create a comprehensive file for use in Microreact.
* Join laboratory and epidemiological data by a common ID.&#x20;


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://cgps.gitbook.io/data-flo/reference-guide/join-datatables.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.