Add Columns from 2nd Dataset
Add columns (single or multiple) from a second dataset by linking a shared value found in both datasets.
This guide describes how to apply the Add Columns from Second Dataset 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 Add Columns from Second Dataset 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 list 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.
-
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.
-
From the Election_2016 map layer, click the ANALYSIS tab.
-
Click ADD ANALYSIS and apply the Add Columns from Second Dataset option. The base layer must be the layer from which the final geometry needs to be derived.
-
Select the education by race county map layer as the INPUT #2 dataset.
-
Select Intersect as the JOIN TYPE.
-
For the Foreign Keys options, select the
fipscolumn for both datasets. This joins the FIPS columns from both datasets. -
For the Columns options, select the columns from each dataset that you want to keep in the final, joined dataset.
-
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.
Note: 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.
CHEATSHEET: Join Types
The following join types are available when you are merging data.
- Left: Joins the two tables and fetches all matching rows for which the relation exists. In addition, it also adds the rows from the first table (or source table) that do not match any row in the second table to the final dataset.
- Inner: Selects all rows from both participating tables as long as there is a match between the columns. It does not add the rows where there is no match.
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.
The Data View and Map View appear as buttons 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.