3

I have large sized spatial joined data of 75000 rows and I want to add a new column in my attributes table of Qgis, named 'ITID_1', In this picture of attribute table; 'class1' is value, 'ITID' is subset and 'ITID_1' is a column to be calculated. I want to copy only one minimum value from 'class1' which are within subset 'ITID' to new column 'ITID_1' and update this new column 'ITID_1'. So what should I do?

If the question isn't clear to understand, May this picture describe clearly what I want to do.

enter image description here

Full view of Attribute Table: enter image description here

  • Please add reason(s) why record #12 was selected, not #11 or #14. – Kazuhito Mar 8 at 12:19
  • @Kazuhito, I want to copy only one minimum class1, it's not important which one minimum value was selected/copied i.e. either record 12 or 11 or 14. Thanks for response. – Kapil Dev Adhikari Mar 8 at 12:30
  • Got that thanks Kapil. Does your data have an unique ID field? It would be useful. – Kazuhito Mar 8 at 12:49
  • @Kazuhito. I've mistakenly attached some portion photo of attribute table in my question, Please view Full view of attribute table of my data on my added answer. – Kapil Dev Adhikari Mar 8 at 13:30
  • Is it possible to have a mid-size sample of your data to make some testing ? (or the full set if not confidential ...) – snaileater Mar 8 at 17:45
1

It seems your "FID" field is the unique ID field (equivalent to row_number or $id+1).

Then, please use this expression to update ITID_1 field:

CASE WHEN 
  "TID" = array_first(
             array_agg("TID", 
                        group_by:= "ITID",  
                        filter:= "class1"= minimum("class1" , group_by:= "ITID")))
THEN "ITID"
ELSE 0
END

This will select the first record among the minimum "class1" value for each "ITID" group.

Image below is a result of small test.

enter image description here

  • May this will solve my problem, but when I use this expression in field calculator, qgis got stuck or hang for 20 minutes for only 2000 data records. I think processing python script will do better. – Kapil Dev Adhikari Mar 8 at 15:01
  • @KapilDevAdhikari Until you get answers by python script, will you try the syntax not on the attribute table, but in the following way; (1) Open Layer Properties and select Source Fields tab. (2) Click on Pencil mark to start Edit mode, then start Field Calculator. (3) Use the above expression. --- before doing this, please close the attribute table. – Kazuhito Mar 8 at 15:07
  • Problem remains the same. – Kapil Dev Adhikari Mar 8 at 15:17
  • 2
    @KapilDevAdhikari OK. I give up. – Kazuhito Mar 8 at 15:19
  • 1
    @KapilDevAdhikari I honestly don't know. But we are always encouraged to post new question for usage of other software. – Kazuhito Mar 9 at 10:56
1

Kapil, if i clearly understood what you expect this could be done using an sql query.

You go to Database / Database Manager / Database Manager then Virtual Layers / Qgis Layers you can then try the following query :

select TID,ITID, min (class1)
group by ITID 

you then save the result of this query and join it (using TID) to your original table to update (or create) ITID_1

Tell me if it works !

I assume TID (or is it TARGET_FID ?) is a unique ID field.

  • Did u try it ? Did that meet your expectations ? Thanks for follow-up ... – snaileater Mar 20 at 12:06
0

You may try running the code below in the python console in QGIS 3. There may be a more elegant way to code this task, but I tried it on a small test data set and it did work (quickly), however I am not convinced it would be faster than @Kazuhito's solution using a native field calculator expression. I will be interested to hear how it goes if you try it on your full data set. Note: I have included a progress dialog with a cancel button which should allow you to break out of the main for loop if it is taking too long. Hint: This could also be implemented in a background thread using QgsTask if necessary; or better still, put into a script for the processing toolbox since they are automatically run in a background thread where possible. There are some useful templates and tutorials for QGIS 3 processing scripts, so you would need to make an attempt at some code by yourself then post a new question if you get stuck.

Also: It seems the name of your layer is 'MT'. If that is not the case, please change the layer name string in the first line below to match yours (double check the field names in my script match yours too).

layer = QgsProject().instance().mapLayersByName('MT')[0]
fld_idx2 = layer.fields().lookupField('ITID')
fld_idx3 = layer.fields().lookupField('class1')
feats = [feat for feat in layer.getFeatures()]
itids = []
for feat in feats:
    itids.append(feat.attributes()[fld_idx2])
itid_vals = set(itids)
min_group_lists = []
v_count = len(itid_vals)
prog = QProgressDialog('Working...', 'Cancel', 0, 100)
prog.setWindowModality(Qt.WindowModal)
prog.setMinimumDuration(1)
check_value = 1
for current, val in enumerate(itid_vals):
    f = int(current + 1)
    pcnt = int(f/v_count * 100/1)
    prog.setValue(pcnt)
    if prog.wasCanceled():
        iface.messageBar().pushMessage('Info', 'Action Cancelled', level=Qgis.Info)
        break
        check_value = 0
    group = {}
    for feat in feats:
        cls1 = feat.attributes()[fld_idx3]
        if feat.attributes()[fld_idx2] == val:
            group[feat.id()] = cls1
    n_min = min(group.values())
    min_group_lists.append([k for k, v in group.items() if v == n_min])
all_min_ids = []
for l in min_group_lists:
    all_min_ids.append(l[0])
if check_value == 1:
    feats_to_update = layer.getFeatures(all_min_ids)
    fld_ID = layer.fields().lookupField('ITID_1')
    for f in feats_to_update:
        fld_val = f.attributes()[fld_idx2]
        atts = {fld_ID: fld_val}
        layer.dataProvider().changeAttributeValues({f.id(): atts})

Here is the attribute table of my test subset of data after running the code:

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.