Spread attribute and recursively join in standalone pyqgis app

Question

TL;DR

Is there a pyqgis equivalent to R's tidyr::spread or reshape2::cast ? If not, how do we recursively join by attribute n layers to another without erasing previous join ?

The problem

I have a layer that contains qualitative data in a given field. I'm trying to programmatically separate the modalities of this field and then join them to a grid layer in distinct columns.

Basically, what I'm trying to do resembles functions such as tidyr::spread or reshape2::cast for those familiar to data wrangling with R. Only I'm trying to do this in a standalone pyqgis application.

Sample

The is meant to work with any given ogr vector that has areal qualitative data. Therefore, the program must loop to get each modalities in a given data field

Data table (data.shp) after intersection with grid id | data | area 1 | cat1 | 123 1 | cat. | 456 1 | catn | Null .. | cat1 | 012 .. | cat. | 345 .. | catn | 678 n | cat1 | 890 n | cat. | 123 n | catn | 456

Expected result id | cat1 | cat. | catn 1 | 123 | 456 | 789 .. | 012 | 345 | 678 n | Null | 123 | 456

My almost but not quite working solution

The best I can come up with is this :

1. Get feature and append to a list with data.getFeatures()

2. Split data layer with processing.runalg("qgis:splitvectorlayer") This creates n shapefiles in a temporary folder that I destroy at the end of the program.

3. In a for loop on point 1. list, load/Edit the splitted files by reconstructing the path with .format, storing each QgsVectorLayer() in another list and finally editing the field name with lyr.startEditing() lyr.renameAttribute() and lyr.commitChanges()

This all works fine.

4. Final step is to recursively join splitted layers onto the grid layer. The following doesn't work : In a for loop on point 3. list (modalLyr), I reconstruct the field name and attempt an addJoin(), like this :

```

for lyr in modalLyr:
areaField = "sup_{}".format(modalLyr.index(lyr))

joinObject = QgsVectorJoinInfo()
joinObject.joinLayerId = lyr.id()
joinObject.joinFieldName = "id"
joinObject.targetFieldName = "id"
joinObject.setJoinFieldNamesSubset([areaField])
joinObject.memoryCache = True
grid.addJoin(joinObject)

```

The problem with this is that I only get one additional field in my output, instead of n areaFields...

Answer 1

I managed to hack my way into a solution using the processing algorithm rather than addJoin, and using a memory layer (None in OUTPUT_LAYER) to store iterations that were otherwise getting erased at each loop. It goes as follows :

for lyr in modalLyr:
    joinAlg = processing.runalg("qgis:joinattributestable",
                    {"INPUT_LAYER": grid,
                    "INPUT_LAYER_2": lyr,
                    "TABLE_FIELD": "id",
                    "TABLE_FIELD_2": "id",
                    "OUTPUT_LAYER": None})
    joinMemo = joinAlg["OUTPUT_LAYER"]
joinLyr = QgsVectorLayer(joinMemo, "joinLyr", "ogr")

It works, but there could be a better solution, i.e. one that doesn't leave a mess of redundant fields to clear up and one that does not use memory layers (as the standalone in meant to be on a server that never reboots, each execution will be wasting memory space...)