Transpose a Table Using FME

Introduction

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

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. 


 

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. 

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. 

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. 


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


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


Finally, select the Vegetable attribute and click Import. 


All of the Vegetable values will be exposed. 

 

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. 


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


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


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. 

5. Run the Workspace

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

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. 

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. 

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:

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. 

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:

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. 

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.

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. 

 
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. 

Connect the writer feature type to the Sorter, then reopen the writer parameters. 

 
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. 

8. Run the Workspace and View Results

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

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:

Workspace:

Output:

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.