4

In QGIS 3.18. In a shapefile attribute table, I have some fields with concatenated values (with commas as the delimiter). Some of the values are duplicate, and I would like to remove them. For example, if an attribute value is 37.3,37.8,44.A2,37.3,37.31, I'd like an expression that will catch the two instances of "37.3" and remove one of them (without removing the instance of "37.31" which is a different value).

I have not found a simple SQL expression that can do this in the field calculator, though I am a relative beginner at SQL (and a complete newbie at Python). On this site, there are similar questions here, here and here but they are rather old, not for QGIS, and ever-so-slightly out of my league (perhaps the formulas are adaptable, but I'm not sure how).

4

Since you mention SQL, here's a PostgreSQL approach to this issue:

WITH yourvalues AS (
SELECT '37.3,37.8,44.A2,37.3,37.31' AS attributefield
)

SELECT
DISTINCT UNNEST(STRING_TO_ARRAY(attributefield,',')) AS unique_attributes
FROM yourvalues

Result:

unique_attributes 37.31 44.A2 37.8 37.3

Result without using DISTINCT:

attributes 37.3 37.8 44.A2 37.3 37.31

The yourvalues CTE doubles as the field in your table. The SELECT query first converts that attribute from a string to an array by using , as a delimiter, and then UNNESTs that array and selects DISTINCT (i.e. unique) values from the unnested array. You can use this kind of logic in any SQL variant, or the QGIS expression calculator.

2
  • 1
    You can use this syntax in either the DB Manager or the Expression calculator. But why not use a proper backend for your data?
    – Encomium
    Sep 18 at 22:04
  • Thank you @Encomium, I will investigate this possibility.
    – pete
    Sep 18 at 22:25
4

If you want to use python, this is one (complicated) way:

#Change the following three lines:
layername = 'New scratch layer'
fieldwithdups = 'values'
fieldwithout = 'values2'

lyr = QgsProject.instance().mapLayersByName(layername)[0] #Create a layer object by name
attrmap = {} #A dictionary to store and to set new values
fieldindex = lyr.fields().indexFromName(fieldwithout) #Index of field to write values to

for row in lyr.getFeatures(): #Iterate over each feature/row in the table
    rowval = row[fieldwithdups] #Fetch value in fieldwithdups field
    if rowval is not None:
        nodups = ','.join(list(dict.fromkeys(rowval.split(',')))) # Split (by commas) the string into a list, create dictionary from the list (which cannot have duplicates), join back to a string
        if len(rowval)!=len(nodups): #If the string length changed
            attrmap[row.id()]={fieldindex:nodups} #Store the new value

#attrmap is now: {1: {1: '37.3,37.8,44.A2,37.31'}, 2: {1: '37.3,37.8,44.A2,37.31'}}
#which means     {feature 1: {field 1 (second field in attribute table : new values},
#                 feature 2: {field 1 (second field in attribute table : new values}}

lyr.dataProvider().changeAttributeValues(attrmap) #Update the values

enter image description here

(Are dictionaries ordered in Python 3.6+?)

1
  • 1
    thank you @BERA. As I continue to develop my Python abilities, I'll use this example for sure.
    – pete
    Sep 19 at 10:48
3

Use this expression with field calculator to create a new field with duplicates deleted (only distinct values remain). "text" is the fieldname where the initial string is:

array_to_string (array_distinct(string_to_array("text")))

enter image description here

1
  • Simple and works for what I need. Thanks @Babel.
    – pete
    Sep 18 at 22:26

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.