Join tabular data to a spatial layer

In this tutorial, you have a .csv table with the average number of people affected by river flooding each year in each country. You'd like to visualize this data on a map, but it is nonspatial: it does not include any geographic coordinates.

Fortunately, the table does include locational information in the form of country names. You'll use the country names to join the river flood data to a feature layer of country boundaries so it can be visualized on a map.

This tutorial was last tested on August 6, 2023, using ArcGIS Pro 3.1. If you're using a different version of ArcGIS Pro, you may encounter different functionality and results.

Requirements

• ArcGIS Pro (see options for software access)

Join tabular data to a spatial layer

Video

This tutorial is also available as a video.

Join tabular data to a spatial layer video

Find matching fields

To join two layers, a matching field must be found between them. You'll search for a common field between a spatial countries layer and a nonspatial flood layer.

1. Download the RiverFloods .zip file and unzip it to a location on your computer, for example, drive C.
2. Open the unzipped RiverFloods folder and double-click RiverFloods.aprx to open the project in ArcGIS Pro.

   

3. If prompted, sign in to your ArcGIS account.

   Note:

   If you don't have access to ArcGIS Pro or an ArcGIS organizational account, see options for software access.

   A map of the world appears.

   

   The map has one basemap layer (Charted Territory), one feature layer (Countries), and one stand-alone table layer (global_flood_risk.csv).

   

   The .csv file is considered a stand-alone table because it only contains tabular data. This contrasts with feature layers such as Countries, which have both spatial information (the shapes and locations of the countries visible on the map) and tabular information (an attribute table).

   Note:

   The Countries layer comes from the Natural Earth public domain dataset. Global_flood_risk.csv comes from the World Resources Institute. The dataset is named Aqueduct Global Flood Risk Country Rankings and it was created by Hessel Winsemius, Senior Researcher at Deltares; and Phillip Ward, Senior Researcher at the Institute for Environmental Studies of the VU University of Amsterdam.

4. In the Contents pane, right-click Countries and click Attribute Table.

   

   The attribute table appears below the map.

5. Scroll through the table to view all of the fields.

   The table has many fields to store country names, including SUBUNIT, NAME, and NAME_LONG.

   

   Multiple name fields are included because there are multiple ways to write the names of some countries. The lack of standardization means that country names are not the easiest choice for join fields. Fortunately, the table also includes many fields with different versions of country codes, for example, FIPS_10_, ISO_A2, and ISO_A3. A code field is a good choice for a join field, because its values are unique and standardized.

   

   You'll view the global_flood_risk.csv table to see if it contains any of the same code fields as the Countries layer.

6. In the Contents pane, right-click global_flood_risk.csv and click Open.

   

   There are only three fields in this table: Rank, Country, and Current annual average population affected by river floods. Unfortunately, there are no country code fields, so you'll have to use Country as a join field. It is likely that you won't get a perfect match with this field, but you might be able to get close.

7. Scroll through the global_flood_risk.csv table and take note of how the country names are formatted.

   Names like Egypt, Arab Rep., Congo, Dem. Rep., and Russian Federation stand out as examples of formatting.

   

8. On the map, click Egypt.

   A pop-up appears, listing attributes from the Countries layer.

9. Scroll through the pop-up to compare the various name fields.

   NAME_SORT is the only field that formats the name of Egypt the same way as the global_flood_risk.csv layer.

   

10. On the map, click some other countries to compare the NAME_SORT field to the name in the Country column in the .csv file.

    Most of the names match. You'll attempt to join the two tables using the matching values in the Country and NAME_SORT fields.

11. Close the pop-up.

Join the stand-alone table to the feature layer

You'll use the Add Join tool to join fields from the global_flood_risk.csv table to the attribute table of the Countries layer.

1. In the Contents pane, right-click Countries. Point to Joins and Relates and click Add Join.

   

   The Add Join window appears.

2. In the Add Join window, confirm that Input Table is set to Countries.

   This is the layer that will receive the joined fields.

3. For Input Join Field, type NAME_SORT.

   The field name autocompletes as you type.

4. For Join Table, choose global_flood_risk.csv.

   This is the layer that will provide the joined fields.

5. For Join Table Field, choose Country.

   The join operation will only be able to join records where the NAME_SORT and Country fields match. For this first attempt, you will only keep countries that are successfully matched. This will make it easier to find out which countries did not match.

6. Uncheck Keep All Target Features.

   

7. Click OK.

Review the results

The map has changed its appearance. Some areas, including the United States and Greenland, are now missing from the gold-colored Countries layer. Greenland is missing because it is not included in the global_flood_risk.csv layer. The United States is missing because its name was formatted differently in the Country and NAME_SORT fields.

You'll review the joined fields to find other countries that did not match.

1. Below the global_flood_risk.csv attribute table, find the record count.

   

   The record count says there are 163 rows—or countries—in the table.

2. Above the table, click Countries to switch to the Countries attribute table.

   There are 159 rows in this table.

   

   This means that 159 out of a possible 163 countries were matched during the join. There are four countries that were not matched.

   Note:

   If you left Keep All Target Features checked, there would be 255 records—the full size of the Countries layer.

   You will find out which countries are missing.

3. Scroll to the end of the Countries table.

   The three fields joined from the .csv file are found at the end of the table. Conveniently, one of the fields is Rank, which can help you find the missing features.

4. Right-click the Rank column header and click Sort Ascending.

   

5. Scroll through the Rank field to find the gaps in the numbers.

   

   The following numbers are missing from the rank field: 18, 94, 141, and 158. In the .csv table, these numbers correspond to the following countries:

   Rank	Country
   18	United States
   94	Czech Republic
   141	Swaziland
   158	Brunei Darussalam

   These are the four countries missing from the join.

Remove the join and edit the table

It is common that the first attempt at a table join is not fully successful. You'll remove the join, edit the names of the four missing countries, and attempt the join again.

1. In the Contents pane, right-click Countries. Point to Joins and Relates and click Remove All Joins.

   

2. In the Remove Joins window, click Yes.

   The three extra fields disappear from the Countries attribute table. The map once again shows all countries.

3. On the map, click the United States. In the pop-up, find the NAME_SORT field, about one-third of the way down the list.

   

   The NAME_SORT attribute is United States of America. In global_flood_risk.csv, the Country attribute is United States. The two attributes must be exactly the same in order for them to join. You'll edit one of the tables to make them match.

4. Close the pop-up.

   It is easiest to edit the spatial layer, Countries, since you can edit it directly in ArcGIS Pro. However, in this case, the spatial layer is the more authoritative source of country names. You'll edit the .csv file instead, outside of ArcGIS Pro.

5. In the Catalog pane, click the Project tab and click the arrow next to Folders to expand it.

   

6. Also expand the RiverFloods folder.
7. Right-click global_flood_risk.csv and click Show In File Explorer.

   

   The RiverFloods folder that you downloaded and unzipped at the start of the tutorial appears in your computer's file explorer.

8. Right-click global_flood_risk.csv and choose to open the file with Notepad or another text editor.

   

9. In the .csv file, find row 18.
10. Edit the text of this row to replace United States with United States of America.

    

11. On row 94, replace Czech Republic with Czechia.

    The Czech government approved Czechia as the official English short name in 2016.

12. On row 141, replace Swaziland with eSwatini.

    Swaziland changed its name to Eswatini in 2018. An alternative spelling—eSwatini—is used in the Countries layer.

13. On row 158, replace Brunei Darussalam with Brunei.

    Brunei Darussalam is the formal name of Brunei.

14. Save and close the .csv file.

Redo the join

You'll refresh the .csv table in ArcGIS Pro to display the edited country names, and try the join again.

1. In ArcGIS Pro, in the table view, switch to the global_flood_risk.csv table.
2. In the lower corner below the table, click the Refresh button.

   

   The table refreshes to include the edited values.

3. In the Contents pane, right-click Countries. Point to Joins and Relates and click Add Join.
4. In the Add Join window, set the following parameters:
   • For Input Table, choose Countries.
   • For Input Join Field, choose NAME_SORT.
   • For Join Table, choose global_flood_risk.csv.
   • For Join Table Field, choose Country.

   This time, you are more confident of your join and in your final result; you want to keep all 255 countries from the spatial layer, even though only 163 of them will contain flood information.

5. Leave Keep All Target Features checked.
6. Click Validate Join.

   

   A Message window appears.

7. Scroll to the bottom of the Message window.

   The second-to-last line says A one - to - one join has matched 163 records.

   

   This number tells you that your join will be fully successful.

8. Click Close.
9. In the Add Join window, click OK.
10. Close the global_flood_risk.csv table.
11. In the Countries attribute table, scroll to the end and confirm that the three new fields are present.

    

    Some countries—for example Bermuda, Greenland, and Singapore—have null values for the joined fields, since they were not present in global_flood_risk.csv.

12. Close the Countries attribute table.

    Note:

    Joins performed with the Add Join tool are not permanent, which is why you were able to remove your first join. To make the join permanent, you can export the Countries layer with the Export Features or Copy Features tools, or you can perform the join with the permanent Join Field tool instead of Add Join.

Visualize the results

Finally, you'll visualize one of the joined fields on the map.

1. In the Contents pane, right-click Countries and click Symbology.

   The Symbology pane appears.

2. In the Symbology pane, for Primary symbology, choose Unclassed Colors.
3. For Field, choose Current annual average population affected by river floods.

   Because this field represents a count instead of a rate or ratio, the symbology should be normalized. You'll divide the affected population by the total population to normalize the values.

4. For Normalization, choose POP_EST.

   

   The map shows the highest per capita river flood risk in Bangladesh.

   

5. On the Quick Access Toolbar, click Save.

   

In this tutorial, you learned the following skills:

• How to find matching fields across two tables
• How to use the Add Join field to add fields from one table to another
• How to assess the success of a join
• How to remove a join
• How to edit a .csv file and refresh its view in ArcGIS Pro
• How to symbolize a feature layer with a joined field

You can find more tutorials in the tutorial gallery.

Acknowledgements

• Admin 0 – Countries, Natural Earth
• Aqueduct Global Flood Risk Country Rankings, World Resources Institute, created by Hessel Winsemius, Senior Researcher at Deltares, and Phillip Ward, Senior Researcher at the Institute for Environmental Studies of the VU University of Amsterdam
• Charted Territory sources: Esri, HERE, Garmin, FAO, NOAA, USGS, OpenStreetMap contributors, and the GIS User Community