# Transposing columns into rows in QGIS Attribute table

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.

• 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
• 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

## 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)` -> `a`
• 2nd element: `array_agg(trouble, group_by:=id)` -> `b`
• etc.

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

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

## Solution 3 : Full pure Python & PyQGIS

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

# 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")

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

with edit(pivot_lyr):

# load the new pivot layer in the project
``````

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)

# 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)
# most occurred value of the declarative_field
most_occurred_value = layer.aggregate(aggregate=QgsAggregateCalculator.Majority, fieldOrExpression=declarative_field)
# 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)
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)
grouped_values_of_target_field.append(aggregated_values_of_target_field)

# get indexes of features with unique geometries
ids_of_unique_geoms = [group for group in grouped_features]
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.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
Change the parameters of the function in the last line. Press `Run script` and get the output that will look like this: 