span8
span4
span8
span4
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 MS Excel,then you might want to use some of the FME 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 Excel reader/writer tutorial.
Workspaces used in examples:
transpose2015.fmwt
transposeadvanced2015.fmwt
pivot-attributepivoter2015.fmwt
pivot-statisticscalculator2015.fmwt
Transposing a table involves switching the columns of a table into rows – in most cases without any data manipulation or summarization.For example:
In FME,a combination of the AttributeExploder and the Aggregator transformers allows you to transpose tables.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.
For example if you have two records in a table:
A sample workspace which performs a transpose on an Excel table is shown below:
Here's the key points of the workspace:
The name/value pair represents the column or attribute name (_attr_name) and the attribute or cell value (_attr_value).
The output of the aggregator is a new feature for each column in the initial table with a list attribute that contains the various cell values.
How to map this list to the output?For MS Excel output the easiest approach is to use an AttributeCreator or AttributeRenamer to rename to column letters;A,B,C,etc.See the example workspace.
The tricky part is writing the rows in the correct order.The AttributeExploder can scramble the order of the attributes so a Sorter transformer can be used to sequence the rotated table's rows.
The end result is a transpose of the original table.
The second transpose example –Example:TransposeAdvanced.fmw- uses the same principals.Again,theAttributeExploderandAggregatortransformers are used to split 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.
You can generate simple statistics using theStatisticsCalculator.The StatisticsCalculator can also be used to generate a Cumulative Sum for a series of features – see the article:Calculating a Running Total or Cumulative Sum.For more details on using theStatisticsCalculatorsee the FME Transformer help.
TheAggregatorcan also be used to generate simple statistics.The Aggregator will calculate weighted averages if the features have area geometries.
TheAttributePivotershould probably be your first choice when trying to create a pivot table or crosstab.This effectively allows you to create pivot tables in FME similar to the pivot tables in Excel.
The StatisticsCalculator transformer has the ability to generate statistics for groups (categories) of features rather than generating statistics for all the features – effectively creating a pivot.
Below is an example of an Excel spreadsheet with a pivot result – the Excel file is also attached to this article in the Pivot_AttributePivoter.fmwt FME template.The ‘observed' data has been summarized by the ‘region' and ‘potential' attributes.
Above:source table and example pivot table in Excel.
如果你的输出格式是Excel,then you may want to consider using the FME Excel writer along with a template file.
The following sections describe how pivots can be achieved using FME.
TheAttributePivotertransformer is a good choice for creating a pivot table or crosstab.There are a couple of advantages to using the AttributePivoter over the StatisticsCalculater approach.
AttributePivoter automatically generates the summary statistics.Also,when creating pivot tables,it is sometime hard to predict the output data schema.When you create pivot tables,the results have new attribute columns generated from the original attributes – for example "Average of Observed" in the Excel example above.The AttributePivoter addsdynamic attributesto the results which makes it easier to write the pivot table.
The one drawback of AttributePivoter is that in current versions of FME you can only analyse one attribute.If you need an analysis on more than one attribute then you might consider the StatisticsCalculator approach – see the next section.
An example workspace using the AttributePivoter is show below.
Above:AttriutePivoter transformer generates a pivot table -Example: Pivot_AttributePivoter.fmwt.
For configuration of the AttributePivoter start with thetransformer documentation.In the example above,the pivot attribute isobserved.Data are summarized byregionandpotential(Group Rows By).The order of the row groups is important,since this affects how the summaries are created.The Group Rows By attribute order is defined in the Select Group Rows by Items dialog:
The results are shown below (highlighting has been added manually):
You can also use the Group Columns By parameter to create further column pivots,in this case using thepotentialattribute results in:
The workspace shown below uses theStatisticsCalculatortransformer to create statistics for the observed attribute by first grouping features by region and potential.Then the new statistics features are sorted by region and potential and output to a CSV file.The resulting CSV file has all of the same attributes/fields as the Excel pivot table.
Above:StatisticsCalculator transformer generates a pivot table
In the workspace there are two StatisticsCalculators used.The first StatisticsCalculator generates the statistics for each region and potential,the second generates the summary statistics for the entire dataset – the Grand Total.Subtotals for the potential attribute would require a third StatisticsCalculator.The results are shown below:
Above:the table written by FME (viewed in Excel) resembles the Excel pivot table.
The tricky parts are the ordering of the output features (Sorter transformer) as well as defining the output schema.
Which of the two approaches – AttributePivoter or StatisticsCalculator - should you use?
Start with the AttributePivoter.The advantages are:
The main drawback of AttributePivoter is that,in current versions of FME (this article is written for 2015),the AttributePivoter will only generate the statistics for one attribute.So if you need statistics for more than one attribute you may have to revert to the more complex,but versatile,StatisticsCalculator approach.
However,if your output format is MS Excel,then you should probably consider using the Excel writer with a template file and building the pivot tables in Excel.This approach brings out the best in both FME and Excel!
Advanced manipulation of Excel tables using AttributeExploder
Using a Template File when Writing Excel Data
Tutorial: GIS and Business Intelligence Data Wrangling
Converting multi-line records into single features
Pivoting Between Columns,Lists and Records
Excel Provider error (-2147467259) Spreadsheet is full (FME 2013 and earlier)
RCaller: Is Tree Height and Tree Width Correlated?
Overview of Excel Writer Parameters
MS Excel Error: Failed to open writer (FME 2013 SP1 and earlier)
© 2019 亚搏在线Safe Software Inc |Legal