Transposing columns into rows in QGIS Attribute table

Question

I have a data set as follows:

id	trouble
1	a
1	b
1	c
2	d
2	e
2	f
3	g
3	h
3	i

But I need it as follows:

id	trouble_1	trouble_2	trouble_3
1	a	b	c
2	d	e	f
3	g	h	i

Is there any tool that can do this? It is probably worth noting that a through i in the example are images, which may cause issues.

Answer 1

Solution 1: Excel

Export your data as CSV and do this operation in Excel: Transpose data from rows to columns (or vice versa) in Excel, the re-import to QGIS.

Solution 2: QGIS

Use this expression to create an array of all trouble values for each group with the same id: array_agg(trouble, group_by:=id) - this returns like [ 'a', 'b', 'c' ]. Add the index of the element (0 for the first element in the array) in brackets [] to get the element.

• 1st element: array_agg(trouble, group_by:=id)[0] -> a
• 2nd element: array_agg(trouble, group_by:=id)[1] -> b
• etc.

Create a separate field for each of the elements or create a model to add all at once. Then delete duplicate features.

Answer 2

Solution 3 : Full pure Python & PyQGIS

See the code comments

from string import ascii_letters

ID, TROUBLE = range(2)

# create base layer
uri = "NoGeometry?crs=epsg:4326&field=id:integer&field=trouble:string"
lyr = QgsVectorLayer(uri, "base_layer", "memory")

feats = []
for i, trouble in enumerate(ascii_letters):
    if trouble == "j":
        break

    feat = QgsFeature()
    feat.setAttributes([i // 3 + 1, trouble])
    feats.append(feat)

with edit(lyr):
    lyr.addFeatures(feats)

# retrieve data
data = {}
for feat in lyr.getFeatures():
    attr = feat.attributes()
    if attr[ID] in data:
        data[attr[ID]].append(attr[TROUBLE])
    else:
        data[attr[ID]] = [attr[TROUBLE]]

# create new pivot layer
max_trouble_per_id = max([len(d) for d in data.values()])
trouble_fields = ""
for i in range(max_trouble_per_id):
    trouble_fields += f"&field=trouble_{i + 1}:string"

uri = f"NoGeometry?crs=epsg:4326&field=id:integer{trouble_fields}"
pivot_lyr = QgsVectorLayer(uri, "pivot_layer", "memory")

# add pivot features
pivot_feats = []
for id, troubles in data.items():
    feat = QgsFeature()
    feat.setAttributes([id, *troubles])
    pivot_feats.append(feat)

with edit(pivot_lyr):
    pivot_lyr.addFeatures(pivot_feats)

# load the new pivot layer in the project
QgsProject.instance().addMapLayer(pivot_lyr)

Answer 3

Here comes another PyQGIS solution.

Let's assume there is a polygon layer called 'polygon' with its attribute table, see the image below.

Note: Features with the same "id" possess the same geometry.

Proceed with Plugins > Python Console > Show Editor and paste the script below:

# imports
from PyQt5.QtCore import QVariant
from qgis.core import QgsProject

def transpose_attributes_from_one_column(layer_name: str, declarative_field: str, target_field: str) -> None:
    """
    Transposes fields and values in the attribute table of a layer
    :param layer_name: name of the layer
    :param declarative_field: name of the supportive field e.g. id, fid etc.
    :param target_field: name of the target field
    """

    # get a layer by its name
    layer = QgsProject.instance().mapLayersByName(layer_name)[0]

    # get all fields of the layer
    all_fields = layer.fields()

    # get index of the target field
    target_field_index = all_fields.lookupField(target_field)

    # list of all values of the declarative field
    all_values = layer.aggregate(aggregate=QgsAggregateCalculator.ArrayAggregate, fieldOrExpression=declarative_field)[0]
    # most occurred value of the declarative_field
    most_occurred_value = layer.aggregate(aggregate=QgsAggregateCalculator.Majority, fieldOrExpression=declarative_field)[0]
    # count how many times this value occured
    times_occurred_value = all_values.count(most_occurred_value)
    # get a set of unique values
    unique_values = set(all_values)

    # create names for new fields
    new_fields = [f'{target_field}_{n+1}' for n in range(times_occurred_value)]

    grouped_features = []
    grouped_values_of_target_field = []

    # loop over each group of features united by the unique value in the declarative field
    for unique_value in unique_values:
        
        params = QgsAggregateCalculator.AggregateParameters()
        params.filter = f"\"{declarative_field}\"={unique_value}"
        
        # aggregate features ids of the original layer by $id attribute
        aggregated_features_ids = layer.aggregate(aggregate=QgsAggregateCalculator.ArrayAggregate, fieldOrExpression="$id", parameters=params)[0]
        grouped_features.append(aggregated_features_ids)
        
        # aggregate features ids of the original layer by target field
        aggregated_values_of_target_field = layer.aggregate(aggregate=QgsAggregateCalculator.ArrayAggregate, fieldOrExpression=target_field, parameters=params)[0]
        grouped_values_of_target_field.append(aggregated_values_of_target_field)

    # get indexes of features with unique geometries
    ids_of_unique_geoms = [group[0] for group in grouped_features]
    # get a list with the required fields for the output
    required_fields = [field for field in all_fields.allAttributesList() if field != target_field_index]
    # create a temporary layer for output
    temp_layer = layer.materialize(QgsFeatureRequest().setFilterFids(ids_of_unique_geoms).setSubsetOfAttributes(required_fields))

    # get data provider of the output layer
    temp_layer_provider = temp_layer.dataProvider()

    # create new fields in the output layer
    for new_field in new_fields:
        temp_layer_provider.addAttributes([QgsField(new_field, QVariant.String)])
    temp_layer.updateFields()

    # get indexes of new fields in the output layer
    new_indxs = [temp_layer.fields().lookupField(new_field) for new_field in new_fields]

    # enrich each list with empty values
    for group in grouped_values_of_target_field:
        group.extend([None for i in range(times_occurred_value - len(group))])

    # get features ids of the output layer
    feature_ids = [feature.id() for feature in temp_layer.getFeatures()]

    # connect new indexes with a group of values of the target field
    new_values = []
    for group in grouped_values_of_target_field:
        new_values.append(dict(zip(new_indxs, group)))

    # edit output layer
    with edit(temp_layer):
        for i, feature in enumerate(temp_layer.getFeatures()):
            temp_layer_provider.changeAttributeValues({ feature.id(): new_values[i] })
            
    # add the output layer to map canvas
    QgsProject.instance().addMapLayer(temp_layer)

transpose_attributes_from_one_column('polygon', "id", "trouble")

Change the parameters of the function in the last line. Press Run script and get the output that will look like this:

Answer 4

For simple work there is the "Group Stats" plugin which can function like a pivot table if you don't want/need to do a full Excel type pivot table workup.