Changing data structure in Omniscope: pivot, de-pivot, transpose, melt

Data orientation and structure can affect how the data can be analyzed and visualized, so it is worth understanding the difference between different methods and the effects these will have on the data structure.

Some scenarios: If you are analysing the correlation between different metrics, it will be useful for each metric to have a separate field. If you wish to create a time series as a layered line view, where each measurement is represented as a line (e.g., temperature, pressure, precipitation over a period of time), then this will be easier to achieve if all the values are in a single field, with the measurement name in another.

Omniscope offers three operations from the menu in the data prep workflow that will change data orientation. Each of these operations allows the user to achieve different results and organise the data in a different structure.

Transpose is a mechanical operation that will switch table rows and columns. The resulting data table is simply flipped compared to the original.

The Pivot operation will change the orientation of one chosen field, selected as a 'Header field' in the block options - each value in this field will become a new field.

Below, we have a [Measure] field with values 'sale,' 'profit,' and 'cost.' After the pivot operation, these values will become column headers.

What happens to the other fields?

The user can pick one or more metrics to appear as value fields in the new cross-tab format.

The values will be redistributed accordingly: while the row and column count will be affected, the structural change will simply reorganise the table's appearance and will not result in a loss of information.

The De-pivot operation should be used to achieve the opposite effect: to convert multiple column headers into values in a single field.

In the example below, five items (days of the week) are selected and will be transformed to become the contents of a [day] field. Again, only the selected fields are affected; the rest of the fields will not change their value or orientation.

Melt de-pivot is a custom Python-based block which performs a specific de-pivot operation.

It allows the user to choose fields that will remain fixed, while the rest of the category fields will be 'melted' into a single column and the values will be melted into the other.

Note - this block will require Python installation on the machine that is running Omniscope, also a commercial plan that supports use of Custom blocks.