Transpose a Table Using FME

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2022.0

Introduction

Transposing a table involves switching the columns of a table into rows – in most cases, without any data manipulation or summarization. For example:

transposetable.png

In Example 1, a custom transformer called the AttributeTransposer is used to transpose data. In Example 2, a combination of the AttributeExploder and the Aggregator transformers allows you to transpose tables; this example allows for more fine-tuning of data. The AttributeExploder transformer takes the attribute values on a row and creates a separate feature for each attribute – as a name/value pair. By aggregating these features back together, you can recombine the columns of the table as rows. Example 3 is an advanced example that focuses on reordering attributes.

 

Video

This video was recorded using FME 2016. The concepts are still the same but the interface may be different.

 

Step-by-step Instructions

Source Data

The source data for Examples 1 & 2 is a Microsoft Excel spreadsheet containing information about how many vegetables were purchased and for how much. 
InputData.png

 

Example 1: Using the AttributeTransposer Custom Transformer

1. Add Microsoft Excel Data

Open FME Workbench and start a blank workspace. Add a Microsoft Excel reader and browse to the vegetables.xlsx dataset that is available to download from the Files section on this article. 

Reader.png

 

2. Add the AttributeTransposer Custom Transformer

Add an AttributeTransposer custom transformer from FME Hub. This transformer uses Python to perform the transpose.  Connect the AttributeTransposer to the Vegetables reader feature type. In the parameters, set the Column Attribute to Vegetable, and then for Row Attribute(s), click on the ellipsis. In the Select Row Attribute(s) Attributes dialog, select Quantity, Price, and Total. Reorder the attributes on the right-hand side so that Total is at the bottom. 

Transposer.png

 

3. Expose Attributes

Before we can continue, we need to expose the attributes that we want. Add an AttributeExposer to the canvas and connect it to the AttributeTransposer. In the parameters, click on the Import drop-down and select From Dataset. 
Expose1.png

In the Import Wizard, select the vegetables.xlsx Microsoft Excel dataset, then click Next. 
Expose2.png

Set the Import Mode to Attribute Values, then click Next. 
Expose3.png

Finally, select the Vegetable attribute and click Import. 
Expose4.png

All of the Vegetable values will be exposed. 
Expose5.png
 

4. Write Out to Excel

Add a Microsoft Excel writer, set the Dataset to the same Vegetables.xlsx that was read in, then change the Sheet Definition to Automatic. 
Writer.png

In the Feature Type dialog that appears, set the Sheet Name to AttributeTransposer, then click OK. 
WriterParam.png

Connect the writer feature type to the AttributeExposer, then reopen the writer parameters. 
Workflow.png

In the parameters, switch to the User Attributes tab, then change the Attribute Definition to Manual. Remove the Price, Quantity, and Total attributes, then click OK. 

WriterParam2.png

 

5. Run the Workspace

Save and then run the workspace. View the results in Excel or in Visual Preview.

VP.png
 

Example 2: Using the Aggregator Transformer

1. Read In and Inspect the Microsoft Excel File

Open FME Workbench and start a blank workspace. Add a Microsoft Excel reader and browse to the vegetables.xlsx dataset. 

Reader.png

 

2. Split Each Record into Separate Records

To split each record into separate records, we will use the AttributeExploder, which will create a name/value pair for each record. Add an AttributeExploser to the canvas and connect it to the vegetable reader feature type. We can accept the default parameters.  The transformer will create two attributes, one called _attr_name, which will contain the name of each attribute, and _attr_value, which will contain the values. 
AttributeExposer.png

 

3. Remove Format Attributes

Note that in FME 2022.1 or newer, the AttributeExploder has a new parameter called Ignore Attributes Containing, which is the same as using the tester.
When using the AttributeExploder, format attributes get exploded as well, so we need to remove these before continuing. Add a Tester to the canvas. In the parameters, set up the following tests:

Logic

Left Value

Operator

Right Value

NOT

_attr_name

Begins With

fme_

AND NOT

_attr_name

Begins With

multi_reader

AND NOT

_attr_name

Begins With

xlsx_

Tester.png

 

4. Build the Transpose

With the _attr_name values cleaned up, we can now build the transpose. Add an Aggregator transformer to the canvas and connect it to the Tester Passed output port. The Aggregator will recombine the name/value pairs back into records. In the parameters, enable Group Processing, then set Group By to _attr_name. Next, set the Aggregation Mode to Attributes Only, then set Accumulation Mode to Merge Incoming Features. Finally, enable Generate List, set the List Name to attrs then set Add to List to All Attributes. 

Aggregator.png

 

5. Create Excel Column Names

For this transpose to be successful, we need to assign the list attributes Excel column names (A, B, C…). Add an AttributeCreator to the canvas and connect it to the AttributeExploder. In the parameters, create the following attributes:

New Attribute

Attribute Value

A

_attr_name

B

_attrs{0}._attr_value

C

_attrs{1}._attr_value

D

_attrs{2}._attr_value

E

_attrs{3}._attr_value

attributelisttable.png
AttributeCreator.png
 

6. Reorder the Rows

The AttributeExploder can sometimes reorder the rows, so we will need to put them in the correct order. Add a Counter to the workspace and connect it to the AttributeCreator. This will assign each attribute a value. 

Counter.png

Next, add a Sorter transformer and connect it to the Counter. In the parameters, set the Attribute to _count, Alpha/Num to Numeric, and the Order to Descending.
Sorter2.png

 

7. Write Out to Excel

The data is now transposed and ready to be written back out to Excel. Add a Microsoft Excel writer to the canvas. Browse to the Vegetables.xlsx Excel file and set the Sheet Definition to Automatic. 
Writer.png
 
In the Feature Type dialog, set the Sheet Name to Aggregator. Next, under Sheet Settings, change the Output Field Names to No, and Use Attribute Names As Column Positions to Yes. Setting this will use the A, B, C… as the column locations, but won’t write out A, B, C (Output Field Names). Click OK to close the parameters. 

Ex2WriterParams.png


Connect the writer feature type to the Sorter, then reopen the writer parameters. 
Workspace2.png
 
In the writer parameters, switch to the User Attributes tab, then change the Attribute Definition to Manual. Remove all of the attributes except for the column names (A, B, C…). Click OK to close the writer parameters. 
Ex2Params2.png

 

8. Run the Workspace and View Results

Save and run the workspace. View the output in Visual Preview or in Microsoft Excel. 
Output2.png

exceloutput.jpg

 

Example 3: Advanced Transpose

The third transpose example – TransposeAdvanced.fmw - uses the same principles. Again, the AttributeExploder and Aggregator transformers are used to split and then recombine the features. In this example, the tricky part of the workflow is getting the features – header, data, totals – output in the correct order. The workspace is annotated to describe the function of each transformer.

Input:

advancedinput.jpg

 

Workspace:

advancedworkflow.jpg

advancedworkflow2.jpg

 

Output:

advancedoutput.jpg 

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.