span8
斯潘4
Note: This article was created with FME 2013.A current article表格数据的处理是可用的.
通常需要重新安排电子表格数据的组织以适应各种用途。一种常见的重组是将行转换为列,or transposing a table.使用FME,您可以使用attributeExploder Transformer对表格数据执行此操作和其他更复杂的操作,如下所述:
Transposing a spreadsheet involves switching the columns of a table into rows for example:
The AttributeExploder transformer allows you to transpose tabular data,as well as do more advanced table manipulations in FME.此转换器获取行的属性值,并为每个名称/值对创建一个新功能。By aggregating these features you can recombine the columns of the table as rows.More information on performing pivots with the AttributeExploder can be found inhttp://fmepedia.亚搏在线safe.com/articles/How_To/Pivoting-between-columns-lists-and-records2.在Excel表上执行转置的示例工作区如下所示:
第一步是将表的行发送到属性分解器,并选择分解类型到特征。这将为表中的每个单元格创建一个新功能,这些单元格的属性与列或属性名(_attr_name)和属性值或单元格值(_attr_value)相对应。
我们希望将同一列中的所有功能重新组合为一个功能,or row,为了做到这一点,我们使用一个聚合转换器,并将其设置为按“属性名”分组,we also set it to keep input attributes in a list by specifying a name for our list in the list name field under 'Attribute Accumulation'.Before running the Aggregator it is a good idea to use a tester or some other filter to get rid of undesired attributes.
The output of the Aggregator is now a feature for each column in the initial table with a list attribute that contains the various cell values,we want to convert these list elements to values so we use an AttributeRenamer transformer.我们几乎完成了,但首先我们添加了一个计数器和排序器,以确保行的顺序是正确的。
最终结果是原始表的转置版本。
还可以使用attributeExploder Transformer执行更复杂的表格操作。在本例中,我们将使用attributeExploder重新格式化一些表格数据,如下所示:
此示例需要更复杂的重新格式化,因此实现此转换的工作区使用的变压器比前一个示例多:
这个工作区中的转换器也是attributeexploder,它分解初始表的行并为每个单元格创建新功能,this time we will set the 'Keep Attributes' parameter to yes,because we will be regrouping the new rows by their year attribute later on.接下来是一个测试仪,这样就消除了不需要的格式属性。
接下来,我们使用聚合转换器将特性重新组合成新行,这一次,我们不仅按属性名(属性名)分组,还按年份分组,because we would like to keep the rows for each year separate.我们现在可以添加一个属性创建者来创建新的属性,the first attribute will append a year to the value of each cell in the header row of the original table,thus creating attributes with year and month attributes.This will also create some values with species and total with year appended which we will need to then filter and modify later on.
We add attributes for the all of the unique individual rows of our table giving them generic names r1,r2,etc.,最后,我们将创建一个具有月份和年份的新属性,以便在以后使用我们的属性。this will also have a constant string of "011231" appended which we will use as a trick with the DateFormatter transformer to put our 'Total' rows in the right place.
测试筛选器用于将具有降水类型的标题行和常规月份行中的年度合计行分开。For the header values we will only want one of these as the first row of the output table,we will have 3 output from the Aggregator,所以我们用一个采样变压器过滤掉除第一个特征外的所有东西,followed by an AttributeValueMapper to rename the attribute so that it no longer has a year appended.对于总计行,我们希望将这些行放在每年的月度数据下面,要做到这一点,我们必须有某种方式对行进行排序。在这种情况下,我们可以使用日期格式转换器将日期字段转换为自Unix Epoch以来的秒数(用于比较日期的常用方法),这将为我们提供一种将日期与排序器进行比较的方法。
The trick we use here is to take the string appended to the date attribute and tell the the DateFormatter to interpret 12 as the month and 30 as the day to ensure that the yearly total comes after December row of that year.我们还将通过另一个日期格式化程序运行每个月的行,但这次我们将把01解释为日期,并从值的前缀中提取月份。日期格式化程序的输出现在可以用排序器变压器进行数字排序。
The final output of the sorter has all of our new rows ordered the way we want them and ready to be written with a csv writer,请记住取消选中“输出字段名”选项,这样我们就不会在头中包含带有虚拟属性名R1、R2等的名称。
?2019安全亚搏在线软件公司|合法的