Using lookup from CSV in Field Calculator?

Question

Is it possible to access a CSV in order to feed a certain value from the CSV into an attribute's field calculator expression? Ex.:

CSV will be as such:

ITEM COST
FOOT 100
THING 200

My polyline layer will have a length measurement in FT and an arbitrary number of THINGs for each feature.

I need to multiply the $/FT value from the CSV by the length measurement for each feature.

I need to multiply the $/THING value from the CSV by the number of THINGs per feature.

How can I get the $/FT and $/THING data from the CSV into the polyline layer in order to add a new field with $/length measurement and a $/total # of THINGs for each feature?

The aim is to easily change the CSV values instead of hard-coding them in the Field Calculator.

• Sergio C.: Have you considered an attribute join, so you add the csv information as attributes to your features, but these attributes are not hard coded, so they can be changed without the field calculator.

Answer 1

You could make use of the Function editor in the Field Calculator which allows you to create a function. This can return the value from the COST column when specifying the ITEM.

---

1. In your Field Calculator, click the Function Editor tab, either create a new file or edit an existing one and enter the following code:

   from qgis.core import *
   from qgis.gui import *
   import csv
   @qgsfunction(args='auto', group='Custom')
   def func(value, feature, parent):
    with open('path/to/csv') as f:
    reader = csv.reader(f)
    for row in reader:
    if row[0] == value:
    return row[1]
   

   Function editor

   Then click Load.

---

2. Click the Expression tab and either create or update a field. If we type the expression:

   func('FOOT')
   

   It will return a value of 100 as written in your csv file. So for your first example, you can use the following expression to divide the FOOT value by the length of each line feature:

   func('FOOT') / $length
   

   Expression

---

3. Replace func('FOOT') with func('THING') to return the 200 value which you can then use for other calculations.

• Joseph: @Metalgearmaycry - Most welcome! Glad it helped :)

Answer 2

(reviving this coming from a more recent linked Q)

The custom function approach is an effective one, but can be hard to read or maintain if you're not a python wizard. Also, custom functions need to reside in a file in your user profile and so are not automatically portable with the project.

Therefore you could also proceed as follows:

1. Load the CSV lookup file into your project as a Delimited text layer with no geometry.

2. Look up in field calculator using attribute(get_feature(csvLayer,'Item','FOOT'),'COST') (note single not double quotes - these are strings passed to the function, not attributes of the current feature in the current layer)

3. Alternately, for slightly different use cases, you could join the CSV layer to the target layer by a shared field, e.g. to have access to the COST as a joined attribute if your layer had for each feature a single item type whose cost is the one you want to us.

Answer 3

A third way is to create two new variable FOOT and THING in your layer properties then you use @FOOT and @THING in your expression and the calculator will use the value you set in the layer variable.

You can edit the variable to another value and the expression will use the new value. These variable could be used anywhere in QGIS and you only have to edit in one place to update all your expression (to reuse variable anywhere you should define your variable at the project level, not at layer level).

More on variable in QGIS from the nyalldawson blog :

part 1

part 2

part 3

• Houska: Another good approach! One should also consider whether the right scope for the variables is Layer or Project properties.