Join Data

Updated: October 5, 2017

Joins two datasets

Category: Data Transformation / Manipulation

This article describes how to use the Join Data module in Azure Machine Learning Studio to merge two datasets using a database-style join operation.

To perform a join on two datasets, they must be related by a single key column. Composite keys are not supported.

1. in Azure Machine Learning Studio, add the datasets you want to combine, and then drag the Join Data module into your experiment. You can find the module in the Data Transformation category, under Manipulation.

   Connect the datasets to the Join Data module.

   The Join Data module does not support a right outer join, so if you want to ensure that rows from a particular dataset are included in the output, that dataset must be on the lefthand input.

2. Click Launch column selector to choose a single key column for the dataset on the left input.

3. Click Launch column selector to choose a single key column for the dataset on the right input.

4. Select the Match case option if you are joining on a text column and want to ensure that the join preserves case sensitivity. For example, if you deselect this option, A1000 would be considered a different key value than a1000.

   If you deselect this option, A1000 would be considered the same as a1000.

5. Use the Join type dropdown list to specify how the datasets should be combined. types:

   • Inner Join. An inner join is the typical join operation. It returns the combined rows only when the values of the key columns match.

   • Left Outer Join. A left outer join returns joined rows for all rows from the left table. When a row in the left table has no matching rows in the right table, the returned row contains missing values for all columns that come from the right table unless you specify a replacement value for missing values.

   • Full Outer Join. A full outer join returns all rows from the left table (table1) and from the right table (table2).

     For each of the rows in the left table that have no matching rows in the right table, the join results include a row containing missing values from the right table.

     For each of the rows in the right table that have no matching rows in the left table, the join results include a row containing missing values for all columns from the left table.

   • Left Semi-Join. A left semi-join returns only the values from the left table when the values of the key columns match.

6. For the option, Keep right key colums in joined table:

   • Deselect the option to get a single key column in the results.
   • Leave the option selected to view the keys from both input tables.

7. Run the experiment, or select the Join Data module and selected Run Selected, to perform the join.

8. To view the results, right-click the Join Data module, select Results dataset, and click Visualize.

Tip
If your dataset has no key column, you can still combine it with another dataset, by using the Add Columns module. This module functions more like R, and can combine two datasets on a row-by-row basis.

You can see examples of how this module is used by exploring these sample experiments in the Model Gallery:

• In the Breast cancer detection sample, Join Data is used to combine the positive training cases with the negative training cases after the proportion of cases has been adjusted.

• In the Flight delay prediction sample, Join Data is used to bring together useful features from external datasets.

• In the Movie recommendation sample, the datasets are joined so that we can present the recommended movie titles rather than a movie ID.

• In the Prediction of student performance sample, Join Data is used to bring in new features.

• The combined dataset cannot have two columns with the same name. Therefore, if the left and right datasets have any duplicate column names, a numeric suffix is appended to the column names of the right dataset to make them unique. For example, if both datasets had a column named Month, the column from the left dataset would remain as is, and the column from the right dataset would be renamed Month (1).

• The algorithm that is used for comparison of key values is hash-forced.

• Each column of the joined dataset preserves a categorical type, if the corresponding column of the input dataset is categorical.

• In left outer joins, if there are any missing values, a categorical level is created in the left dataset for missing values. This is true even if there are no missing values in the joined (right) dataset.

For a list of all exceptions, see Module Error Codes.

Manipulation
Data Transformation
A-Z Module List