I have a very large datasets (billions of observations) made of multiple nominal categorical variables (nominal, not ordinal), and I want to outline the set of variables that accounts for the most variability in the overall dataset. Multiple Correspondence Analysis could be used for that purpose, but it relies on the use of an indicator matrix (aka complete disjunctive table), which can become extremely large when dealing with large sets of records and large numbers of categorical dimensions that have large cardinalities, because its size is the product of the number of records by the product of the cardinalities.
Furthermore, MCA will outline which variables account for the most variability, but it won’t help identify which combinations of variables account for it the most. This is due to the fact that when using MCA, data is represented in a low-dimensional Euclidian space, while interesting patterns might emerge when representing data in spaces of higher dimensionality, and the optimal dimensionality is usually not known in advance.
In such a context, I am trying to figure out if Pivot Tables could be used as an alternative to Indicator Matrices or Burt Tables. According to this method, a set of n
discrete variables are selected. For simplification purposes, all variables are considered as nominal, even though some of them might actually be ordinal.
From there, we compute multiple sets of pivot tables, taking all combinations of k
dimensions as pivot axes, with k
ranging from 1
to n
, and using COUNT
as aggregation function. Consequently, for a given value of k
, the number of computed pivots will be equal to the binomial coefficient, or n! / (k! × (n - k)!)
. In total, 2ⁿ
pivots will be computed.
For every pivot, we then compute sets of average counts (averages of counts). For a pivot defined with k
pivot axes, we will compute averages across k-1
dimensions, and we will do that for every combinations of k-1
values of the k-1
dimensions. For a given combination of k-1
dimensions, which is defined by the dimension being excluded from the set, the number of averages will be equal to the product of the cardinalities of the dimensions that are included in the combination.
For example, if k=2
and the first dimension (displayed in columns) is made of c
individual values and the second dimensions (displayed in rows) is made of r
individual values, there would be c
averages on a column-by-column basis, and r
averages on a row-by-row basis.
These averages will then be distributed across k
buckets, one for every pivot axis, with each average going to the bucket related to the dimension not included in the k-1
dimensions used to compute the average. Then, we will compute for every bucket the relative standard deviation of the averages contained in the bucket. Finally, we will compute the average of all relative standard deviations across all buckets. This will give us a normalized variability coefficient for every pivot, which (we hope) quantifies the degree to which the pivot’s dimensions account for the variability of the overall dataset.
My questions are the following:
- Are there better ways of achieving similar results?
- Has such a method been used before (links please)?
- Is such a method mathematically correct?
Note: This article provides a more complete description of the method.