Field Calculator: Bulk-Edit Attribute Tables

Introduction

QGIS has a feature called expressions, which work like Excel formulas. With expressions, you can calculate attribute table values, manipulate shapes on the map, and customize how labels are displayed. The Field Calculator is a handy tool that uses expressions to calculate and enter the values of a field (column) in the attribute table all at once.

This article covers the basic operation of the Field Calculator, how to write expressions, how to convert data types, and how to change the processing depending on conditions.

Basic usage of the Field Calculator

As the article below explains, you normally create a new column in the attribute table and enter values one by one.

With the Field Calculator, you can create a new column and fill in all of its values at once from an expression.

How to open the Field Calculator

To open the Field Calculator, first open the attribute table of a vector layer, then click the Open field calculator button at the top of the window.

You can also open it from the toolbar. Select a vector layer in the Layers panel, then click the Open Field Calculator button in the toolbar.

Field Calculator layout

The Field Calculator dialog is laid out as follows.

1. Only update x selected feature(s): This option turns on when features are selected in the layer. Select the checkbox to update only the selected features. To avoid unintended updates, clear the selection before you run the tool.
2. Output field settings: Choose whether to write the results to a new field or update an existing field. When you create a new field, you must specify its data type, such as text, integer, or decimal.
3. Expression editor: This is where you enter the formula or conditional expression. You can build an expression with the operators and functions at the bottom of the dialog.
4. Preview: Shows the result of the expression you entered. If the expression has an error, an "Expression is invalid" error message appears.
5. Function selector: Lists the available fields and functions. It is handy for entering function and field names.
6. Help panel: Shows the description, syntax, and examples of the selected function.

How to run the Field Calculator

This example uses the Field Calculator to convert the population field to thousands.

First, click the Open field calculator button in the attribute table.

When the Field Calculator opens, set the output field first.

This example creates a new field, so set Output field name to 人口（千人単位） (population in thousands) and Output field type to Decimal number (real).

The field type must match the type of the result. If the types differ, for example when the result is text but the output field is numeric, the result is NULL.

Next, enter the expression using the expression editor and the function selector. The data used here has a population field named POP_EST, so the calculation uses it.

To enter a field name, it helps to use the Fields and Values group in the function selector. Open the group and double-click the population field, POP_EST, to insert the field name into the expression editor.

Then enter the formula that converts the population to thousands. Click the / button below the expression editor to insert the division operator, then type 1000.

That completes the expression. Make sure no error message appears in the Preview, then click the OK button.

In the attribute table, you can see that a new field named 人口（千人単位） has been created and filled with the results.

Finally, save the results. After the calculation runs, click the Save edits button to commit your changes.

Basic expression rules and commonly used functions

Basic expression rules

Expressions follow a few basic rules.

Example: calculate population density from the population field (POP_EST) and the area field (AREA)

"POP_EST" / "AREA"

Example: convert an area field (AREA) in square kilometers to square meters

"AREA" * 1000000

Data type conversion functions

QGIS provides functions that convert values between data types. The table below lists the main data type conversion functions.

Example: convert a decimal area field (AREA) to an integer

-- Example output: '123.84' → 123
to_int("AREA")

Example: convert a text population field (POP_EST) to a numeric type

-- Example output: '100' → 100
to_int("POP_EST")

String functions

String functions are useful for processing text data in the attribute table. The table below lists the main functions.

Example: turn a numeric ID field (id) into a zero-padded, three-digit string

-- Example output: 1 → '001'
lpad(to_string("id"),3,'0')

Example: convert the population field (POP_EST) to a string and add the unit 人 (people)

-- Example output: 100 → '100人'
concat(to_string( "POP_EST" ) ,'人')
to_string( "POP_EST" ) || '人'

Conditionals

Conditionals let you output different results depending on attribute values. In QGIS, use the if function or a case statement.

Example: classify the price field (Price) with an IF statement

-- Example output: 5000 → 'low'
if( "Price" > 100000 ,'high','low')

Example: classify the population field (POP_EST) with a CASE statement

-- Example output: 1000000 → 'high'
case
  when "POP_EST" <10000 then 'low'
  when "POP_EST" <100000 then 'mid'
  else 'high'
end

With multiple conditions, you can nest if statements (narrowing down the conditions step by step), but a case statement is more concise.

Geometry functions

The functions above also exist in spreadsheet software such as Excel, but QGIS has a wide range of functions for getting and calculating the geometry information of features. The table below lists the main geometry functions.

Example: get the coordinates of a geometry's centroid

x(centroid(@geometry))
y(centroid(@geometry))

Example: get the coordinates of the start and end points of a line or polygon

x(start_point(@geometry))
y(end_point(@geometry))

Tips for getting the most out of the Field Calculator

Here are a few tips for using the Field Calculator more efficiently.

Saving and reusing expressions

In the Field Calculator's function selector, the Recent (fieldcalc) group lets you recall expressions you ran earlier. The number of expressions shown is limited.

Saving the expressions you use often is handy. To save an expression, enter it in the expression editor, then click the Save button above the editor.

The Store Expression dialog opens. Edit the label and other fields as needed, then click the Save button.

To load a saved expression, use the User expressions group in the function selector.

About functions that start with $

The Field Calculator includes several functions that start with a dollar sign ($), such as $area and $x.

Functions that start with $ refer to values related to the feature itself or its geometry. These functions work on their own, so you can run them by entering just the function name, with no arguments (specific values), for example $area.

Differences between $area and area, and between $length and length

QGIS expressions include similarly named functions: $area and area, and $length and length. They differ in which coordinate system or settings they use for the calculation.

For this reason, $area and $length change how they calculate depending on the “ellipsoid” and “measurement units,” which you can set for each project.

Here is how $area behaves in practice.

• When the ellipsoid is set to “WGS84”: calculates the area (distance) on the ellipsoid (the WGS84 ellipsoid).
• When the ellipsoid is set to “None / Planimetric” (no ellipsoid / planar calculation): calculates the area (distance) on a plane, based on the coordinate system of the geometry.

You can also change the project's ellipsoid and measurement units in the project properties. To do so, choose Project → Properties from the menu bar to open the project properties, then change the settings under Measurements on the General tab.

These differences are somewhat complex, but when you calculate the area of a layer that covers a local area, such as a prefecture or municipality, it is a good idea to reproject the layer to the Japan Plane Rectangular Coordinate System first and then calculate the area with area(@geometry).

Conclusion

This article explained how to bulk-edit attribute data with the QGIS Field Calculator. With the Field Calculator, you can update and calculate attributes efficiently, tasks that take a long time to do by hand. Try it in your daily work and analysis.