How to Join Tabular Data to GIS Data in QGIS

Introduction

Combining GIS data with tabular data in QGIS lets you analyze and visualize your data in more depth.

This article shows how to use the table join feature in QGIS to join tabular data to GIS data. With this feature, you can get much more out of attribute tables.

What is a table join?

The table join feature in QGIS combines information from multiple attribute tables into one table, using the values of a column they have in common.

For example, joining population data created in Excel to administrative area data lets you visualize the population distribution on a map.

Steps for joining tables

The steps below join population data from e-Stat to administrative area data from National Land Numerical Information.

Load the data

First, load the administrative area data into QGIS.

Next, load the population data. The data is in CSV format, so follow these steps. If the tabular data you want to join is in Excel format, convert it to CSV first.

From the menu bar, choose Layer → Add Layer → Add Delimited Text Layer.

The Data Source Manager opens with the Delimited Text tab selected. Here, load the CSV file as data without geometry and set the field types.

1. File name: click the ... button and select the CSV file
2. Geometry Definition: select No geometry (attribute only table)
3. Type of 地域コード (area code): select Text (string)
4. Type of A1101_総人口【人】 (total population): select Integer (64 bit)
5. Type of A1102_日本人人口【人】 (Japanese population): select Integer (64 bit)
6. Type of A1231_年齢中位数【歳】 (median age, in years): select Decimal (double)

After you finish these settings, click Add. The layer appears in the Layers panel.

Set up the table join

Next, identify the common field for the join.

Checking each dataset shows that N03_007 in the administrative area data and 地域コード in the population data hold the same information.

Use these columns to join the tables.

First, right-click the administrative area layer and choose Properties from the menu.

In the Layer Properties dialog, select Joins from the menu on the left. Then click the + button at the bottom.

The Add Vector Join dialog opens. The table below describes each option.

Options 1 to 3 are required for a table join.

To join the population data to the administrative area data, set the options as follows.

1. Join layer: select the population data layer
2. Join field: select 地域コード (the area code field of the population data)
3. Target field: select N03_007 (the area code field of the administrative area data)
4. Joined fields: select the checkboxes for A1101_総人口【人】, A1102_日本人人口【人】, and A1231_年齢中位数【歳】
5. Custom field name prefix: select the checkbox and clear the text box
6. When you finish these settings, click the OK button

Confirm that the options you set appear in the settings area, then click the OK button in the Layer Properties dialog.

Check the result

When the join is done, open the attribute table of the administrative area layer to check it. QGIS adds the columns you selected from the population data to the right of the original columns.

With the population data joined to the administrative area data, you can now visualize on the map the attribute information that originally belonged to the CSV data, such as median age.

For detailed steps on styling based on attribute information, see the following article.

Export and save the joined table

The joined data exists only temporarily in QGIS, so the administrative area layer saved on your computer does not contain attributes such as median age.

To save the joined data to a file, right-click the layer with the joined attribute table and choose Export → Save Features As. Then save it in any file format.

The following article explains the detailed steps.

Common problems with table joins

The join result is NULL

If the joined fields show NULL, the join has failed. This mainly happens when the values of the join fields do not match or their data types differ.

For example, the population data join uses the area code as the common field. The code consists only of digits, but you need to be careful when importing the CSV. Some municipalities have codes with a leading 0, such as 01101. If the column is loaded as a numeric type, QGIS drops the leading 0 and the code becomes 1101.

As a result, the code is 01101 in the administrative area data and 1101 in the population data. QGIS treats the join fields as different, and the join fails. To prevent this, define the area code column as a text type when you load it.

The joined data no longer appears

Table join settings are saved in the QGIS project. If you quit QGIS without saving the project, the joined fields are lost and you have to repeat the join steps.

Even when the join is saved in the QGIS project, it is not saved directly in the file itself, so it is normal that the joined fields do not appear when you inspect the file. In that case, export the layer that contains the join, as described above.

When using Excel data

QGIS does not support loading Excel files directly. You can force a file into QGIS by dragging and dropping it onto the window, but the column names are not recognized correctly and all values are treated as text, so this is not practical.

So to use Excel data in a table join, convert the Excel file to CSV format and then add it to QGIS.

Conclusion

The table join feature lets you join tabular data such as Excel and CSV to GIS data and visualize it on a map. Joining tables greatly widens what you can do with GIS data in QGIS, so make good use of this feature.