span8
span4
span8
span4
This article is also part of the tutorialHow Do I Do That in FME|Next: Specify Distance Buffers
A pivot table,also known as a cross-tab,allows a user to change the structure of a spreadsheet to perform data summarization.An example of a Microsoft Excel pivot table is found below which demonstrates summarization of ‘observed' data,by the ‘region',and ‘potential' attributes.This article demonstrates the FME transformers that can be used to construct a pivot-table.
Initial table (orange) and the pivot table created in Microsoft Excel (blue)
Note that this video was created using FME 2016 the interface might look different but the content and 亚搏在线workflow is the same.
StatisticsCalculatorPivoterExample.fmwt
If you are only analyzing one attribute,you can use the AttributePivoter transformer to create a pivot table or crosstab in FME.This transformer allows you to create pivot tables that are similar to Excel,and this approach has distinct advantages over other approaches,including:
1) Read in CSV data
In a blank workspace,add aCSV (Comma Separated Value)Reader to the canvas and select the pivot-source.csv file as the Dataset.If you inspect the data,you will see that we have a small table containing city names,which region they are in,their potential and an observed number.We will pivot based on the observed attribute.
Initial data viewed in the FME Data Inspector
2) Pivot the table
Add and connect theAttributePivotertransformer.In the parameters,set the Group By Rows to both potential and region,switch the order of the Selected Attributes so that region is first.Next,set the Attribute to Analyze to observed,then for the Pivot Summary Statistics Types select Average,Count,and Sum.Click OK.
AttributePivoter parameters;ensure that region is first in the Group By Rows order
3) Write to Microsoft Excel
Add aMicrosoft ExcelWriter and set the Sheet Definition to 'Dynamic (Advanced)' mode.Connect both the Data and Summary AttributePivoter output ports to the Feature Type.See the documentation for more information aboutDynamic Workspace.
Final workspace,connect both the output ports on the AttributePivoter to the Dynamic Writer
4) Run the workspace and inspect the output.
Run the workspace and then inspect the output either in Microsoft Excel or in the FME Data Inspector.
Output data viewed and manually stylized in Microsoft Excel
5) Create more pivot columns
Back in FME Workbench,open the AttributePivoter parameters,change the Group Columns By potential to further pivot the table.Rerun the translation and inspect the results.
Change the Group Columns By to potential in the AttributePivoter
Additional columns added when using the Group Columns By parameter
If you have multiple attributes to analyze or you want more control over which statistics are calculated using the StatisticsCalculator transformer to create a pivot table.The StatisticsCalculator can generate statistics for groups (or categories) of features.
1) Read in CSV data
In a blank workspace,add a CSV (Comma Separated Value) Reader to the canvas and select the pivot-source.csv file as the Dataset.If you inspect the data,you will see that we have a small table containing city names,which region they are in,their potential and an observed number.We will pivot based on the observed attribute.
Initial data viewed in the FME Data Inspector
2) Calculate statistics
Add aStatisticsCalculatortransformer to the canvas,in the parameters,set Group By to region and potential.Then under Attributes to Analyze select observed.Under Calculate Attributes,delete all the values.Then enter count_of_observed,sum_of_observed and average_of_observed into Total Count Attribute,Sum Attribute,and Mean Attribute,respectively.
StatisticsCalculator parameters;set the Group By,Attributes to Analyze and Calculate Attribute parameters
3) Calculate totals
Duplicate the StatisticsCalculator by right-clicking on it and selecting Duplicate.Connect the duplicated StatisticsCalculator to the Complete output port on the first StatisticsCalculator.In the parameters,remove the Group By attributes,the remaining parameters are the same as the first StatisticsCalculator.
Connect the duplicated StatisticsCalculator to the Complete output port on the first StatisticsCalculator
4) Order features
Next,we will order the features coming from the StatisticsCalculators.Add anAttributeCreatortransformer to the canvas and connect it to the Summary output port on the first StatisticsCalculator.Create a new attribute called _FeatureOrder and set the value to 0
Add a second AttributeCreator and connect it to the Summary output port on the second StatisticsCalculator.Create three new attributes:
Attribute Name | Attribute Value |
region | Grand Total |
potential |
|
_FeatureOrder | 1 |
Note that region and potential are in lowercase to match the original attributes.
Connect the AttributeCreators to the Summary output ports on the StatisticsCalculators
5) Sort features
Add aSortertransformer to the canvas.Connect it to both AttributeCreator Output ports.Then sort by the attribute _FeatureOrder by Numeric Ascending order.Then sort both region and potential by ascending alphabetical order.
Sorter parameters;sort _FeatureOrder,region,and potential by an Alphabetic Ascending order
6) Add a CSV Writer
Add a CSV (Comma Separated Value) writer to the canvas and browse to a location to save your output file.Set the File Definition to Automatic and click OK.Connect the writer to the Sorted output port on the Sorter.In the writer parameters,click on the User Attributes tab and set the Attribute Definition to Manual and remove _FeatureOrder.
In the User Attribute tab,switch to Manual Attribute Definition and remove _FeatureOrder
7)工作空间运行,检查输出。
Run the workspace and then inspect the output either in Microsoft Excel or the FME Data Inspector.
Output data viewed and manually stylized in Microsoft Excel
Overview of Excel Reader Parameters
Overview of Excel Writer Parameters
Tutorial: Getting Started with Excel
Using a Template File when Writing Excel Data
How to Convert CSV to Microsoft Excel
Viewing and Inspecting Excel Data
Example scripts for deleting Excel files prior to writing (FME 2013 SP1 and earlier)
Excel Provider error (-2147467259) Spreadsheet is full (FME 2013 and earlier)
© 2019 亚搏在线Safe Software Inc |Legal