Transposing Data for Business Intelligence

Introduction

In this demo, we will take a large dataset, transpose the attributes using AttributeExploder then create a new output with a selection of attributes. We will start with a large Excel document containing population statistics from the City of Vancouver, and transpose the data to restructure it. As we are only interested in the population totals, we will select only the statistics of interest. Then we will rename the attributes and write the data to CSV.

Source

An excel file containing 2011 population census statistics from the City of Vancouver.

CensusLocalAreaProfiles2011.xls viewed in Excel.

Step-by-step Instructions

1. Read in Data

Read in the source data, CensusLocalAreaProfiles2011.xls, using the Excel Reader. Under Parameters, set Field Names Row to 5 and Cell Range to 6:.

Excel Reader parameters.

2. Inspect Data

Inspect the data with the Data Inspector. We want to make the columns into rows to make the data easier to work with. In this example, we are only interested in the population totals, not everything, as that would be a lot of data.

Excel file viewed in Data Inspector, with one population total highlighted.

3. Get Total Statistics

To get only the total statistics, add a Tester transformer. Set the Test Clauses to:

Tester parameters to filter out the statistics totals.

4. Explode Attributes

To the Tester Passed port attach an AttributeExploder. Open up the parameters for AttributeExploder, change Keep Attributes to Yes. The AttributeExploder will help us turn the attribute names (columns) into attribute values (rows). It creates one feature for each attribute value from the spreadsheet with a new pair of attributes (attribute name/attribute value). Attribute names become attribute values.

5. Keep Attributes

Add an AttributeKeeper, as we only want to keep a selection of attributes. Set Attributes to Keep to A, which represents statistic type, _attr_name, which represents the Vancouver area boundaries, and _attr_value, which represents the population totals.

6. Remove Unwanted Values

Another Tester is used to filter out the unwanted attribute values such as format attributes.

Test Clauses:

7. Cleanup Attributes

Connect an AttributeManager to the Failed output port of the Tester. The attributes are given more descriptive names in the AttributeManager:

A is renamed to StatType as it represents statistic type

_attr_name is renamed to AreaName as it represents the Vancouver area boundaries

_attr_value is renamed to StatValue as it represents the population totals

AttributeManager parameters.

8. Filter Attributes

The AttributeFilter filters attributes by AreaName. For Possible Attribute Values, please add CMA of Vancouver and Vancouver CSD (City). This will filter out statistics from the whole city of Vancouver, as we are only interested in Vancouver area boundaries.

AttributeFilter parameters.

9. Write to CSV

Add a CSV Writer, specifying an output dataset location and setting CSV File Definition to Automatic. Attach the writer feature type to the AttributeFilter <Unfiltered> port. Rename the writer feature type to something more descriptive and ensure the attributes include StatType, AreaName and StatValue. Once the workspace is run, the output CSV file can be added into Tableau. Alternatively, write out to the format of your choice. Feel free to overlay the data with local_area_boundary.shp.

Output CSV file viewed in Data Inspector