Manipulation of Tabular Data

Introduction

This article describes the different tools you have available to manipulate tabular data using FME. This can include: transposing a table (table rotation), summary statistics and totals, pivot tables, and crosstab. These operations are common in spreadsheet applications such as Excel.

If your target format is Microsoft Excel, then you might want to use some of the FME Microsoft Excel writer capabilities for writing to a template file. The template file can include pre-defined pivot tables, charts, named ranges etc. Refer to the Tutorial: Getting Started with Excel article series for more information. 

Manipulating Tabular Data

We often think of tabular data in the context of a spreadsheet such as Excel, but table manipulation can also be carried out on a Microsoft Access table, CSV, or any other tabular or database data. Common operations are:

• Transpose: switching the columns and rows – sometimes referred to as table rotation.

• Summary Statistics: calculation of totals and averages for the table data.

• Pivot: also referred to as a crosstab. Pivot tables are used to analyze categorical datasets.

Articles

Calculating a Running Total or Cumulative Sum

You can generate simple statistics using the StatisticsCalculator. The StatisticsCalculator can also be used to generate a Cumulative Sum for a series of features

The Aggregator can also be used to generate simple statistics. The Aggregator will calculate weighted averages if the features have area geometries.

Transpose a Table Using FME

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

Pivot Tables and FME

A pivot table, also known as a cross-tab, allows a user to change the structure of a spreadsheet to perform data summarization. This article shows how to create a pivot table with both the AttributePivoter and the StatisticsCalculator transformers.