I have data that look like this:

amount    creator    accounts
100       john       cash, accounts payable
325       jane       accounts receivable, cash
200       john       tax account, accounts payable, cash

How should these data points be clustered?

Thoughts so far:

  • Popular, consensus answer seems to be to one-hot encode the categorical and multivalue_categorical fields, and then scale the numeric field to [0,1]. This causes two primary problems: extremely sparse/high-dimensional data (4,000 dimensions in my case), and a numeric column that is perhaps not weighted appropriately.

  • Attempt to apply differing algorithms to each data type and mash them together somehow. This could involve market-basket type analysis for the multivalue_categorical, k-modes for the categorical, and k-means for numeric (or k-prototypes for the categorical and numeric).

Is there any method/implementation that would allow for these three types of data to be clustered without one-hot encoding the categorical and multivalue categorical? I have looked into SOM as an unsupervised NN that performs clustering, but I haven't seen evidence that it can handle multivalue categorical.

  • $\begingroup$ If you can determine a distance measure that is appropriate for your data, you can use any clustering method that can operate over a distance matrix instead of the raw data. You need to think about the issue of sparsity (what would it mean to have clusters in sparse data, what would you want to detect?), & the ontological status of your multivalue variable (is that really $1$ variable, or a bunch? How do they go together more than they go w/ the other variables?). $\endgroup$ Commented Mar 2, 2018 at 15:17
  • $\begingroup$ thank you for the nudges in the right direction! i updated my question to reflect a more realistic example of the data (clustering journal entries, essentially). given that data, do you have any further thoughts? i guess the root of my question is whether it's even possible to have a distance metric that fits all three types of data. $\endgroup$ Commented Mar 2, 2018 at 15:31
  • $\begingroup$ You introduced a new tag [mixed-data]. Can you please provide a tag wiki? $\endgroup$ Commented Mar 3, 2018 at 10:52
  • $\begingroup$ @kjetilbhalvorsen, see the discussion here: A new tag - for mixture of data types. $\endgroup$ Commented Mar 4, 2018 at 1:53
  • $\begingroup$ how many variables of each type are there in your dataset? $\endgroup$
    – g3o2
    Commented Mar 4, 2018 at 15:47

3 Answers 3


With mixed data types the basic answer is to use Gower's distance (see @ttnphns' thorough explainer here: Hierarchical clustering with mixed type data - what distance/similarity to use?). The gist of it is that you get the distance measure of your preference for each variable individually, then average them. You can also do a weighted average of the constituent distances, if you think some should be given more credibility than others.

For your continuous variable, the absolute difference should be fine. Simple matching is presumably fine for your categorical variable, creator. That is, $1$, if two rows have the same creator, and $0$ otherwise. Then you just need to find a metric for your multivalue categorical variable. I think it is fine for you to think of this as a single variable, but I suspect it is ultimately better to think of it as a set of binary variables, where all possible options constitutes the set. From there, if the option is listed, that amounts to having a $1$ in that column, and $0$ otherwise. Thus, you have a high-dimensional binary space. There have been lots of measures defined for binary data (see: Choi, Cha, & Tappert, A Survey of Binary Similarity and Distance Measures, pdf, for a list of 76!). You need to decide which makes sense. The constituent distance measures each gets normalized, and then you use whichever clustering algorithm you like that can work with a distance matrix instead of the raw data (see, e.g., my answer here: How to use both binary and continuous variables together in clustering?).


Do not choose an approach, just because it is easy to implement.

One hot encoding, normalization, etc. are all just hacks that get you at least something, but not necessarily something good!

Instead, do the math: what is a better clustering in your problem? Until you can evaluate how well it solves your problem, you might as well just use random partitions...

  • $\begingroup$ that's exactly why i was looking for something that was not just a popular consensus thrown out every time. do you have any thoughts on what a sound approach might be, based on the data types? $\endgroup$ Commented Mar 5, 2018 at 20:14
  • $\begingroup$ Clustering is more of an art than of a science. Once the business problem is clearly defined, clustering is naturally easier. The tricky part is to get to that clear problem definition. Evolving from any starting point through an iterative and interactive process towards a better point of problem understanding is a typical and familiar clustering process. So why not ... Whatever you choose, without any domain knowledge, even the best approach won't get you very far anyway. $\endgroup$
    – g3o2
    Commented Mar 5, 2018 at 23:33
  • $\begingroup$ i think i have the clear problem definition, the semantics of how to deal with mixed data types (particularly multivalue categorical) is what's tripping me up. $\endgroup$ Commented Mar 6, 2018 at 13:58
  • $\begingroup$ Your problem definition must include how to handle (weight etc.) variables, and how to measure differences and similarities. $\endgroup$ Commented Mar 6, 2018 at 17:08
  • $\begingroup$ that's what i'm here for - learning about the different ways to measure differences and weight values. i have realistic sample data and data types included. $\endgroup$ Commented Mar 6, 2018 at 19:34

Given that there are only two numeric and three categorical variables in your dataset, one of which has many categories, you could investigate the opportunity of transforming the two numeric ones into ordered categorical ones.

That way, the clustering problem becomes all categorical, with the dedicated distance functions at hand.

Fo binning there are at least three approaches in descending order of relevance:

  • define the bins based on domain knowledge;
  • inspect the distribution of each numeric variable to set the cutoff points for each bin;
  • set the bins so that each bin has roughly the same number of observations.

As an algorithm, hierarchical clustering should get the job done.

  • $\begingroup$ thank you - I hadn't really thought seriously about this approach. So this would still involve two distance functions, right? One for the multivalue, and one for the normal categorical? $\endgroup$ Commented Mar 5, 2018 at 11:47
  • $\begingroup$ It depends on how you want to weight the variables and what you want to do with the multivalue one. The latter can be used in several ways: as a categorical variable, as a text variable or even split into binary tokens. The most naïve way of weighting is to apply a separate distance calculation on each of the 5 variables, then to aggregate, that is if each one presents a separate analysis theme. If there are less than 5 themes, then you should apply a distance function on each group of variables that belong together, then aggregate thereafter. $\endgroup$
    – g3o2
    Commented Mar 5, 2018 at 21:43
  • $\begingroup$ i was thinking along the lines of a market basket comparison - not sure if there is a traditional distance function for market baskets that would fit in well $\endgroup$ Commented Mar 6, 2018 at 13:51
  • $\begingroup$ you mean along the lines of association rule learning ? That would mean that you want to analyze the co-occurrence of transactions? I guess that you should edit your original post then and specify what you really expect to retrieve from your data. This does change your original question a lot btw. $\endgroup$
    – g3o2
    Commented Mar 6, 2018 at 19:30
  • $\begingroup$ is there a distance function for association rule learning? $\endgroup$ Commented Mar 6, 2018 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.