Rework multiple attributes with field calculator in QGIS3

Question

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:

Answer 1

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')

Answer 2

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')

Answer 3

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:

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

output: