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.

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 3 uses weather data from the City of Vancouver.

Video

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

Step-by-step Instructions

Example 1: Using the AttributeTransposer Custom Transformer

1. Add Microsoft Excel Data

Open FME Workbench and create a new workspace. Add a Microsoft Excel. Click on the Add Source Data box on the canvas or start typing Excel to open the quick add window.

• Format: Microsoft Excel
• Dataset: vegetable.xlsx
  • Click on the ellipses to navigate to the file location on your computer

2. Add the AttributeTransposer Custom Transformer

Add an AttributeTransposer custom transformer from FME Hub. This can be added the same way as regular transformers. This transformer uses Python to perform the transpose.  Connect the AttributeTransposer to the Vegetables reader feature type and double click to open the parameters

In the window, set the following parameters:

• Columns Attribute: Vegetable
• Row Attributes: Price Quantity Total
  • Click on the ellipsis to open the Select Row Attributes window and manually select the three attributes

You can type in the row attributes manually, or if the exact name of the attributes is unknown, you can use the Select 'Row Attribute(s)' Attributes window.

Click OK twice to finish setting up the AttributeTransposer.

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. 

If the workspace has been run, attributes can be imported from the data cache. Click on the green run arrow in the menu bar, ensure "Enable data Caching" is checked, and run the workspace. 

 In the parameters, click on the Import drop-down and select From Data Cache. 


In the next window, deselect the "Vegetable" attribute and click Import.

All of the Vegetable values will be exposed. 

4. Write Out to Excel

Add a writer to your canvas and set the following parameters:

• Format: Microsoft Excel
• Dataset: PivotedVegetables.xlsx
  • Click on the ellipsis to nagivate to a location on your computer to save the file
• Sheet Definition: Automatic

Click OK to add the writer to your canvas and for the Feature Type window to open. 

In the Feature Type dialog, set the following:

• Sheet Name: AttributeTransposer

Click OK to accept the Feature Type parameters.

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

In the parameters, switch to the User Attributes tab and make the following changes:

• Change the Attribute Definition to Manual
• Ensure all the types are strings
• Remove the Price, Quantity, and Total attributes
  • Use the minus sign button at the bottom of the window to remove attributes

Click OK to accept the user attributes.

5. Run the Workspace

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

Example 2: Using the Aggregator Transformer

1. Read In and Inspect the Microsoft Excel File

Open FME Workbench and create a new workspace. 

• Format: Microsoft Excel
• Dataset: vegetable.xlsx
  • Click on the ellipses to navigate to the file location on your computer

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

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: Not
  • Left Value: _attr_name
  • Operator: Begins With
  • Right Value: fme_
• Logic: AND NOT
  • Left Value: _attr_name
  • Operator: Begins With
  • Right Value: multi_reader
• Logic: AND NOT
  • Left Value: _attr_name
  • Operator: Begins With
  • Right Value: xlsx_
• Comparison Mode: Case Insensitive

Click OK to accept the parameters.

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, set the following:

• Group Processing: enabled
  • Group By: _attr_name.
• Aggregation Mode: Attributes Only
• Attribute Accumulation:
  • Accumulation Mode: Merge Incoming Attributes
• Generate List: enabled
  • List Name: attrs
  • Add to List: All attributes

Click OK to accept the parameters.

5. Create Excel Column Names

For this transpose to be successful, we need to assign the list attributes to 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:

• Output Attribute: A
  • Value: _attr_name
• Output Attribute: B
  • Value: _attrs{0}._attr_value
• Output Attribute: C
  • Value: _attrs{1}._attr_value
• Output Attribute: D
  • Value: _attrs{2}._attr_value
• Output Attribute: E
  • Value: _attrs{3}._attr_value

Click OK to accept the parameters.

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. Keep the default values and click OK. 

Next, add a Sorter transformer and connect it to the Counter. In the parameters, set the following:

• Attribute: _count
• Method: Numeric
• Order: Descending

Click OK to accept the parameters.

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 and set the following parameters:

• Format: Microsoft Excel
• Dataset: vegetables.xlsx
  • Click on the elipsis to navigate to a location on your computer

Click OK to accept the parameters and open the Feature Type window.

In the Feature Type dialog, set the following:

• Sheet Name: Aggregator
• Use Attribute Names as Column Positions: Yes
• Output Field Names: No

Use Attribute Names as Column Positions will use A, B, C… as the column locations, but will not write out A, B, C (Output Field Names). Click OK to close the parameters. 

Click OK to accept the parameters and add the writer to the canvas.

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

 
Double-click on the writer and in the parameters, switch to the User Attributes tab. Change the Attribute Definition to Manual and 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.