Join Columns from Second Layer

Analysis Guides

Join Columns from Second Layer

This guide describes how to apply the Join columns from 2nd Layer analysis to merge your current dataset with another existing dataset, by linking a shared row value.

For example, admin regions typically contain a unique ID, such as FIPS for US Counties. Suppose you have two map layers containing FIPS data. You can join a single value, or multiple values, from the admin region data into a selected map layer. The Join column from 2nd layer analysis option merges the shared value into the second dataset.

Adding a second map layer is similar to importing the first dataset into a map, as described in the Quick Data Import Guide. Click ADD from the LAYERS pane of Builder to connect another dataset as a map layer.

Joining Data

Let's explore demographic data using a US Presidential Election Map of 2016. This example joins a county-level census map layer to a county-level election result map layer.

  1. Import the template .carto file packaged from "Download resources" of this guide and create the map. Builder opens with election data as the first map layer, and education data by race as the second map layer.

    Click on "Download resources" from this guide to download the zip file to your local machine. Extract the zip file to view the .carto file(s) used for this guide.
  2. From the Election_2016 map layer, click the ANALYSIS tab.

  3. Click ADD ANALYSIS and apply the Join column from 2nd layer option. The source layer must be the layer from which the final geometry needs to be derived.

  4. Select the education by race county map layer as the INPUT #2 dataset.

  5. Select Inner as the JOIN TYPE.

  6. For the Foreign Keys options, select the fips column for both datasets. This joins the FIPS columns from both datasets.

  7. For the Columns options, select the columns from each dataset that you want to keep in the final, joined dataset. For this example:

    • Select all columns for the Election_2016 dataset.
    • Select the last four columns for the Education dataset.

    Note that since raw numbers are not an accurate representation for demographic data, columns that contain percentage data for education are selected to be added to the Election dataset.

    Join columns from 2nd layer analysis options

  8. Click APPLY to join the data.

    The final map layer includes all the data from the Election_2016 dataset, and the selected columns from the Education dataset.

Merged Results

View the Election_2016 map layer in the Data View and note the four new columns that were added to your table, all prefixed with "right" in the column name. The prefix is significant of the SQL join clause in a relational database. The first table, or the source table, is referred to as the left table. The second table on which the join is performed is referred to as the right table, hence the prefix right for the column name.

Merged results from Join columns from 2nd layer analysis

The Data View and Map View appear as icons on your map visualization when a map layer is selected. Click to switch between viewing your connected dataset as a table, or show the map view of your data.

To gain more insights from your data, style the counties with the election data by styling by value, and add widgets to explore the education data.

Download the final .carto file from the "Download resources" of this guide, and explore the cartography applied. Additional layers were added to show labels and state boundaries for the data.

External Resources

If you are interested in using the underlying functions in the SQL view of Builder, you can join common columns from your datasets with the SQL Inner Join and Left Join queries. View the SQL Join syntax for details.