3

I often have to rework a lot of columns in attribute tables (expand abbreviations, replace numerical values with string values, and so on).

Is there a way to do this more efficient than opening the field calculator and paste the CASE WHEN THEN END statements into it for every single field that I want to rework? The field names of my tables are always the same.

Even a way with Python would be acceptable but I am not very familiar with pyqgis.

EDIT:


from PyQt5.QtCore import QVariant
from qgis.core import QgsField, QgsExpression, QgsFeature


lyr_path = r'/home/ktw/Schreibtisch/_testBumbleBee_QGIS/shp/bumblebees.shp'
#vl = QgsVectorLayer(lyr_path, "Hummeln", "ogr")
vl = iface.addVectorLayer(lyr_path, "Hummeln", "ogr")

expr_1 = QgsExpression('species')

context = QgsExpressionContext()
context.appendScopes(QgsExpressionContextUtils.globalProjectLayerScopes(vl))


pr = vl.dataProvider()
pr.addAttributes([QgsField('species2',  QVariant.String)])
vl.updateFields() 

with edit(vl):
    for f in vl.getFeatures():
        context.setFeature(f)
        f['species2'] = expr_1.evaluate(context)
        vl.updateFeature(f)
vl.commitChanges

This is what I got so far. It does nothing more than copying the values from species to species2.

I am struggling with QgsExpression(). I want to pass my SQL expression:

CASE
  WHEN species IS 'B.ter' THEN 'Bombus terrestris'
  WHEN species IS 'B.lap' THEN 'Bombus lapidarius'
  WHEN species IS 'B.cry' THEN 'Bombus cryptarum'
  WHEN species IS 'B.ves' THEN 'Bombus vestalis'
  ELSE 'ERROR'
END

My attribute table before running the script:Attribute table

5
  • Sorry, but there is no such way. Jan 22 at 14:47
  • Really? Not even a pythonic one within QGIS? Otherwise I would export my table and do it with pandas, then import it again. Seems inconvenient but would save a lot of work.
    – ktw
    Jan 22 at 14:48
  • Alternatively, you can press the F3 key, set a condition and select objects, then click on the toolbar button "Modify the Attributes of All Selected Features Simultaneously" (yellow button with red pencil) and set the selected objects values for many fields at once. Perhaps this will speed up your work. Jan 22 at 15:03
  • 1
    It depends if you are working on a lot of layers that use the same conditions, then Python is much more efficient because you only need to define the conditions once and loop through the layers. There is no copy-pasting the expression each time.
    – Matt
    Jan 22 at 15:09
  • In the processing tools you find an algorithm named aggregate, this allows you to do many things in addition to aggregating; in fact, if in the group field you put a field of the attribute table with unique values, it does not aggregate anything, but executes the expressions listed below. - link: docs.qgis.org/3.22/en/docs/user_manual/processing_algs/qgis/…
    – pigreco
    Jan 22 at 17:17

3 Answers 3

3

You can use pyqgis.

I have a field called kkod and I'm adding a new field called new_kkod and calculating.

Adjust layername, field names and type of new field, and the d dictionary of old and new values. You can add all the values you like to it.

layer = QgsProject.instance().mapLayersByName('buildings')[0]

#Add field
pr = layer.dataProvider()
pr.addAttributes([QgsField('new_kkod', QVariant.String)])
layer.updateFields()
field_index = layer.fields().indexFromName('new_kkod')

#Create a dictionary of current and new values
d = {331:'house', 352:'church', 323:'telecom', 341:'other'}

#Create a dictionary of feature id: {new field index:new value}
attributemap = {}
for f in layer.getFeatures():
    attributemap[f.id()] = {field_index: d.get(f['kkod'], 'ERROR')}

#Update
pr.changeAttributeValues(attributemap)

print('Done')

enter image description here

1
  • +1 for your solution, but if there is a way to load my SQL scripts without transform them to dict, I would prefer it.
    – ktw
    Jan 22 at 20:29
1

An acceptable solution for me is to create a virtual layer and then make it permanent/save it. This solution allows me to use the SQL statements from my collection. In a next step I can load them directly from the text files and iterate over a list of queries to automate the process. Furthermore I do not have to touch my original shapefiles. Anyway, I would really appreciate a "filed calculator only" solution that allows to use SQL.


from PyQt5.QtCore import QVariant
from qgis.core import QgsField, QgsExpression, QgsFeature

lyr_dir = r'/home/ktw/Schreibtisch/_testBumbleBee_QGIS/shp/'
lyr_path = r'/home/ktw/Schreibtisch/_testBumbleBee_QGIS/shp/bumblebees.shp'
lyr_name = 'Hummeln'
vl = iface.addVectorLayer(lyr_path, lyr_name, "ogr")

query = "?query=SELECT \
CASE \
  WHEN species IS 'B.ter' THEN 'Bombus terrestris' \
  WHEN species IS 'B.lap' THEN 'Bombus lapidarius' \
  WHEN species IS 'B.cry' THEN 'Bombus cryptarum' \
  WHEN species IS 'B.ves' THEN 'Bombus vestalis' \
  ELSE 'ERROR' \
END AS Art, \
date AS Datum, \
amount AS Anzahl \
FROM Hummeln"

vl_name = lyr_name + '_2'
vl = QgsVectorLayer(query, lyr_2_name, 'virtual')
QgsProject.instance().addMapLayer(vl)

out_lyr = r'/home/ktw/Schreibtisch/_testBumbleBee_QGIS/shp/bumblebees_2.shp'
write = QgsVectorFileWriter.writeAsVectorFormat(vl, out_lyr, 'utf-8', vl.crs(), driverName='ESRI Shapefile')
1

A quick way is to use processing in conjunction with QGIS expressions:

using aggregate algorithm, with the trick of using a unique-valued attribute in Group by expression:

enter image description here

use the field calc icon to add the desired expressions, this can be done in all fields.

output:

enter image description here

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.