调试经验——使用Spotfire Unpivot进行数据的列转行（数据伸展）操作(Unpivoting data in Spotfire)

昨天，第一次使用Spotfire中Data Transformation（数据转换）中的Unpivot功能，感觉很实用。

源数据是Excel文件，格式如下：

这种数据格式虽然比较紧凑，但并不便于在Spotfire中进行数据分析及可视化展现（参考另一篇文章：二维表和一维表在Spotfire中的可视化差异 ）

其实，在将Excel数据导入到Spotfire中时，直接进行Unpivot操作。

1. Add Data Tables -> Transformations -> Add

2. 将需要合并到一列的所有列字段拖放到Columns to Transform中。在本例中，所有年份需要统一到年份字段中，所以首先将所有年份列拖放到Columns to Transform中，

然后，重命名两个新列（标签列(label field)和值列(value field)），即在Category Column Name和Value Column 中分别填入Year和Indicator Value.

然后，尽情享受Spotfire数据分析爽快感吧。

最后，科普一下Unpivot Data（摘自TIBCO Sptofire User's Guide）：

Unpivoting Data 
--------------------------------------------------------------------------------


An unpivot transformation is one way to transform data from a short/wide to a tall/skinny format. When the data types of source columns differ, the varying data is converted to a common data type so the source data can be part of one single column in the new data set.


Tip: A shortcut for unpivoting data is available by selecting and right-clicking the columns to combine in the Data panel, and then clicking Unpivot. You get some more options to control the result if you unpivot using Insert > Transformation instead.


Example:


The example below shows an unpivot transformation on a very simple data set. In the original data table, there are three columns and four rows. Each row contains a city, a morning temperature and an evening temperature for each city.


While this is certainly useful, we want to determine the average temperature of all the cities for all times of day.


After unpivoting the data, we have one row for each measurement and can easily get an average value for the Temperature column in the analysis after the data has been imported.


Note: Observe that the morning temperatures were given as integers and the evening temperatures as real numbers. In the unpivoted data table, these values must have the same data type to be used in the same column. Integers are therefore changed to real numbers (changing the real number temperatures to integers, while still somewhat compatible in this case, would have resulted in a loss of information).