Mean coordinates from several columns

Question

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.

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:

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

Answer 1

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:

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

Answer 2

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

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