4

I have a delimited text layer created from table with a few hundred coordinates (lat/long) in columns A and B and several hundred values associated with these coordinates in columns C to XXX. Now, I would like to create mean coordinates for each column from C to XXX - weighted by the values in the given column.

Columns in table (section)

Manually, I can create these mean coordinates in QGIS 3.16.14 (LTR) via "Processing Toolbox"/"Mean coordinate(s"): I choose the delimited text layer as input layer and select a column via "Weight field". This works fine. However, I would have to do this individually for every column. And this would a few days.

Is there an easy way to automatize this process? One "mean coordinate" per column C to XXX? The expected result would be a "line" of points on the map, one "mean coordinate" for every column, for example like this:

enter image description here

I tried "Run as Batch Process" in the toolbox but I still would have to select every column manually...

How can this workflow be automated? Any ideas?

SAMPLE DATA (for a semicolon CSV):

lat;lon;97;98;99;100;101;102;103;104;105;106;107;108;109;110;111
54.65455;19.90929;1000;1480;1960;2440;2920;3400;3880;4360;4840;5320;5800;6280;6760;7240;7720
54.36346;12.72491;;;;;;;;;;;;;;;
54.41823;13.43349;500;600;;;;;;;;;;;;;
56.87930;16.65634;;;;;;;;;;;;;;;
53.42894;14.55302;;;;;;;;11700;11600;11500;11400;11300;11200;11100;11000
59.43696;24.75353;;7072;7143;7215;7286;7358;7429;7500;7572;7643;7715;7786;7858;7929;8000
57.64089;18.29602;;;;;;;;1000;1000;1000;1000;1000;1000;1000;1000
55.00801;11.91057;;;;;;;;;;;;;;;
60.70763;28.75283;;;;;;;;;;;1160;1200;1240;1280;1320
54.17670;12.08402;3000;3250;3500;3750;4000;4100;4200;4300;4400;4500;4600;4700;4800;4900;5000
53.89314;11.45286;;;;;;;;1000;1000;2000;3000;4000;6000;8000;9000
63.82842;20.25972;;;;;;;;1000;1005;1013;950;1000;1000;1000;1000
53.87537;13.92394;;;;;;;;1000;1000;1000;1000;1000;1000;1000;1000
54.58048;16.86194;;;;;;;;1226;1259;;;;;;
59.66755;28.28713;;;;;;;;;;;;;;;
60.80043;21.40841;1000;1000;1000;1000;1000;1000;1000;1000;1000;1000;1000;1000;1000;1000;1000
63.09600;21.61577;;;;;;1000;1000;1000;1000;1000;1000;1000;1000;1000;1000
57.75840;16.63733;1000;1000;1000;1000;1000;1100;1200;1300;1400;1500;1600;1700;1800;1900;2000
57.39485;21.56121;;;;;;;;;;;;;;;
55.09510;10.24226;;;;;;;;;;3000;3000;3000;3000;3000;3000
54.78431;09.43961;;;;;;;;;;;;;;;
55.56568;09.75257;2000;2000;2000;2000;2000;2100;2200;2300;2400;2500;2600;2700;2800;2900;3000
54.35766;19.68029;;;;;;;;;678;;;;;;
60.67452;17.14174;;;;;;;;;8334;8250;8167;8084;8000;7917;7834
54.35227;18.64912;;;;;;;;3260;3297;3334;3371;3408;3445;3482;3519
54.51889;18.53188;;;;;;;;420;430;440;450;460;470;480;490
54.26698;18.95196;;;;;;;;;;;2000;2158;2316;2474;2632
14
  • Thanks for your reply! And sorry if I wasn't clear here! I calculate the mean coordinate for every column (C to XXX) from the coordinates given in the columns A and B.
    – Alrik
    Dec 22, 2021 at 21:50
  • Thus, every mean coordinate is calculated by dozens of coordinates, weighted by the values of the given columns (C to XXX).
    – Alrik
    Dec 22, 2021 at 21:51
  • 1
    Run Mean Coordinate(s) in Batch mode: click at the lower left of the dialog window
    – Babel
    Dec 22, 2021 at 21:52
  • 1
    OK, thanks. I appreciate your help very much! An example for such an expression would be great (see my sample data above). This would help me very much. I'll check the thread tomorrow.
    – Alrik
    Dec 22, 2021 at 22:27
  • 1
    Btw, what's the difference between "Calculate by expression" and "Add values by expression"? Both direct to the "Expression string builder".
    – Alrik
    Dec 22, 2021 at 22:30

2 Answers 2

6

You can use the following script. It adds all results to one layer.

  • Add CSV to QGIS.
  • Select the layer and run the script.
layer = iface.activeLayer()

crs = layer.crs().authid()
uri = "Point?crs=" + crs + "&field=field:string&field=MEAN_X:double&field=MEAN_Y:double&index=yes"
mean_layer = QgsVectorLayer(uri, 'TEST', 'memory')

fields = layer.fields().names()[2:]
features = list(layer.getFeatures())

for field in fields:
    weighted_xs = []
    weighted_ys = []
    weights = []
    for f in features:
        weight = f[field] if f[field] != NULL else 1.0
        p = f.geometry().asPoint()
        weighted_xs.append(p.x() * weight)
        weighted_ys.append(p.y() * weight)
        weights.append(weight)
    
    feat = QgsFeature(mean_layer.fields())
    feat["field"] = field
    mx = sum(weighted_xs) / sum(weights)
    my = sum(weighted_ys) / sum(weights)
    feat["MEAN_X"] = mx
    feat["MEAN_Y"] = my
    
    geom = QgsGeometry.fromPointXY(QgsPointXY(mx, my))
    feat.setGeometry(geom)
    
    mean_layer.dataProvider().addFeatures([feat])

QgsProject.instance().addMapLayer(mean_layer)

Result:

enter image description here

Attribute Table: (Field names are added as field value)

enter image description here

5
  • Addin all to one layer - maybe even better!
    – Babel
    Dec 23, 2021 at 22:26
  • 3
    @Babel I guess, there are thousands of columns. In Excel, C to XXX means that. A to ZZZ equals 26+26*26+26*26*26). Dec 23, 2021 at 22:36
  • 1
    As stated above: XXX was just a (poorly chosen) placeholder. Sorry for this misunderstanding. There are just a few hundred columns.
    – Alrik
    Dec 23, 2021 at 23:31
  • The script works fine! Thanks again! Just one question: The script does only work with INTEGER values, but not with FLOAT values. Is there an alternative to the operator "*" that works with floating-point numbers?
    – Alrik
    Jan 3, 2022 at 11:49
  • 1
    Not tested, but try weight = float(f[field]) if f[field] != NULL else 1.0 Jan 3, 2022 at 12:21
3

Solution: Use batch mode

Run Mean Coordinate(s) in Batch mode: click at the lower left of the dialog window.

To automatically fill the Weight field in the dialog for each attribute, insert an array of the corresponding fieldnames clicking Autofill... / Add values by Expression... like this:

array('val1','val2','val3','val4','val5')

Then for every element of the array (fieldname), a new row will be generated and the tool runs separately for each row - see screenshots below.


Automatically create an array of all fieldnames

Edit: You can create the array of field names automatically - handy if you have hundreds of fieldnames to include. Here the expression (with the help of @MrXsquared, see comments) to use. This gets you all fieldnames of the layer points (end of line 2), except the fieldnames excluded in line 3:

array_filter (
    map_akeys(attributes(get_feature_by_id('points',1))),
    @element not in ('fid','lat','lon', 'fields')
)

enter image description here

Output of the tool in batch mode for the sample dataset you provided: the colored points inside the red line are the weighted mean coordinates for each of the attribute values (97 to 111): enter image description here

6
  • 2
    Reading your answer i slowly (but still not entirely) get the question. May I suggest to use something like map_akeys(attributes(get_feature_by_id('layername',1))) to auto get an array of all fieldnames, that avoids typing all hundreds of names by hand.
    – MrXsquared
    Dec 23, 2021 at 22:02
  • 1
    @MrXsquared - that's what I was trying for some time in different variants, however, it does not work. Somehow the layer is not identified and no attributes are returned. Would be interesting to see if there is a way to achieve this. Workaround: create a list of attribute names on the layer itself, copy the result and paste it to the batch mode expression editor. Will update the answer accordingly.
    – Babel
    Dec 23, 2021 at 22:07
  • You're right - I overlooked the attributes(feature) variant - just hat attributes() in view (I knew that it should work somehow). Maybe worth adding as another answer?
    – Babel
    Dec 23, 2021 at 22:25
  • 1
    Thank you very much for your ideas and suggestions! Very instructive and much to think about. I did not know about this possibility to automatically create an array of all fieldnames. That's very helpful, not only in this context.
    – Alrik
    Dec 23, 2021 at 23:39
  • The script works fine! Thanks again! Just one question: The script does only work with INTEGER values, but not with FLOAT values. Is there an alternative to the operator "*" that works with floating-point number?
    – Alrik
    Jan 3, 2022 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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