Data Transposing / Pivoting

Transpose or pivot data between wide and narrow representations.

Three Data Productivity Cloud pipelines that demonstrate techniques for transposing (pivoting) data between wide and narrow representations.

Open example transpose extract and load first.

Example setup

When you run this pipeline, it will copy (colocate) the sample data from our recent assessment of the UK’s Electric Vehicle infrastructure into a new table named stg_batteryelectric_wide. This is a wide table:

• Relatively few rows
• A small number of identity columns (Make and Model)
• A large number of similarly-named columns. In this example they contain values at different points in time

The data contains some values that are not numeric, either because they were estimated, or were confidential. Also there are values such as 18,974 which are formatted strings rather than real numbers. These will be problematic when it comes to summing the values later. In other words the data is accurate and of high quality, but it needs to be made more consumable so it can be manipulated and interpreted more easily.

There are more than 50 of these “value” columns. So to avoid a lot of hand coding, and therefore to improve productivity, the pipeline automatically sets several variables for the UNPOVOT and PIVOT transformations that will follow.

Data Transposing Wide to Narrow

The first of the two transformation pipelines transposes the data into its narrow format, and creates a new table named stg_batteryelectric_narrow:

In its narrow format the table retains the two identity columns, but all the values are transposed into date / count pairs like this:

It’s exactly the same data, but expressed using more rows and less columns. An important DataOps consideration is that this narrow format offers some protection against schema drift. Adding a new quarter will not require a modification to this table’s structure.

The calculator, filter and rename components remove values such as [low] and [c] that are not numbers. They will be replaced with NULL, which will act as a zero when summed. Strings that look like numbers are replaced by actual numbers.

Data Transposing Narrow to Wide

The second of the two transformation pipelines starts with the narrow format data and pivots it back into wide format at two different granularities:

• stg_batteryelectric_make_model (re-pivoted by make and model)
• stg_batteryelectric_make (re-pivoted and summed by make only)

The new stg_batteryelectric_make_model table is almost identical to the original data. The only differences are that the non-numeric values (like [low] and [c]) are gone, and all the value columns are numbers rather than strings.

In the two PIVOT components you’ll notice the expression for the new cells is SUM("Vehicles"). That means it’s possible to aggregate, or sum, the values at a lower granularity. Removing the Model column results in the new stg_batteryelectric_make table:

Downloads

Licensed under: Matillion Free Subscription License

• Download transpose-pivot-Databricks-AWS.zip
  • Platform: AWS
  • Target: Databricks
• Download transpose-pivot-Snowflake-AWS.zip
  • Platform: AWS
  • Target: Snowflake

Installation Instructions