3

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.

2
  • 2
    What type of data are you working with? Does "a through i are images" mean, you have images in the attribute table, or are you talking about links? Can you provide example data? What have you tried? How large is your data set?
    – Erik
    Dec 1, 2022 at 12:37
  • 3
    For simple work there is a 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.
    – John
    Dec 1, 2022 at 21:45

4 Answers 4

6

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.

1
  • 1
    Nice to know my rough idea would have worked out.
    – Erik
    Dec 1, 2022 at 12:51
4

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

Here comes another PyQGIS solution.

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

input

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 run script and get the output that will look like this:

output

0
3

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.

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.