The cross-tabular format - also known as a pivot table - is a common data presentation technique used to summarise and analyse the relationship between two or more categorical variables.
Row aggregation
Pivot tables typically aggregate data across both rows and columns.It is important to first consider whether the row data is presented at a suitable level of granularity for analysis. Aible works best with many thousands of detailed data records and your cross tab data may be unsuitable if the rows have been aggregated up to a summary level with only tens or hundreds of records. If this is the case consider de-aggregating the rows.
Column aggregation
Pivot tables typically have multiple columns representing the same value aggregated for different categories.
For example, this table contains the land-area for each country along with population for 2020 - 2023.
The figures in the 2020 - 2023 columns are all populations with the column heading representing the Year key.
There are a few transformations that we may wish to consider with data structured like this.
Our first consideration in this case is - what is the outcome/dependent variable we wish to analyse?
Perhaps we wish to understand what drives the 2023 population, in which case we could select this as the outcome but, in all likelihood, all the analysis will simply show us is that large countries have larger populations.
Option 1 - Converting to a delta
When the column grouping is a time period we are often more interested in the change in values rather than the absolute values. We may be interested in questions such as “what is driving revenue up quarter on quarter”, “what is driving the population change” or “why are our qualified leads diminishing” etc.
We can best answer these by recalculating the values as deltas or, as in this example, % changes:
This normalises the differences between rows and draws out the change in values.
(See also Working with Data - Time Series)
Option 2 - Pivoting
Converting to a delta won’t be appropriate if the columns are not ordinal numbers or are textual categories. In this case we may want to directly compare the impact of the categorical values.
We can also do the same with ordinal categories such as the year column in our example above.
Pivoting means transposing selected columns to rows or vice versa. In this case we would pivot the year columns to rows, while retaining all of the other columns:
Now we have 4x as many records to analyse; our data has been reshaped from wide to long. Note that we now have a single “Population” column which we could use to evaluate the direct impact of year on the population as the dataset outcome field.
Aible has a built in pivot/unpivot function that lets you make transformations like this easy:
If you don’t have access to the Transformations options in Aible Sense speak to Aible Customer Success.