Transposing Data for Business Intelligence

Introduction

In this demo, we will work with a large dataset, transpose the attributes using AttributeExploder, and then create a new output that includes 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 a CSV file.

Source

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

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:.

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 all the data, as that would be overwhelming.

3. Get Total Statistics

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

4. Explode Attributes

To the Tester Passed port attach an AttributeExploder. Open the parameters for AttributeExploder and 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, paired with a new attribute (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 the 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 the statistical 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

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 the Vancouver area boundaries.

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 to Tableau. Alternatively, write out in the format of your choice. Feel free to overlay the data with local_area_boundary.shp.