4

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.

1
  • 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. – Sergio C. Jul 5 '17 at 18:25
5

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.


  1. 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


  1. Replace func('FOOT') with func('THING') to return the 200 value which you can then use for other calculations.
1
  • @Metalgearmaycry - Most welcome! Glad it helped :) – Joseph Jul 10 '17 at 12:30
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.

1

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

1
  • Another good approach! One should also consider whether the right scope for the variables is Layer or Project properties. – Houska Feb 14 '20 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.