# What is the best to identify the proper hierarchy of this data?

So I worked on a hierarchical clustering algorithm to be able to determine which items are most similar, and what attributes are most important. I have two tables:

Table 1: contains a bunch of item codes, and it's attribute (brand, flavor, sales, and so on). It looks something like:

Item_code | Brand | Flavor   | Caloric_content | ... | sales
006891313 | Coke  | Original | 0               | ....
002349823 | Fanta | Orange   | 200             | ...


The other table that I have is what i run my clustering algorithm on. It's an NxN matrix, (where N is the number of distinct item_codes in the previous table) An entry [i,j] in the matrix, corresponds to the number of times j was bought after i was purchased on a previous trip. So more clearly, in the matrix below, what the number 1223 means is that, 1223 times after item 003428734 was purchase on an initial trip to the store, item 003428734 was purchased on the next trip.

          |003428734 |009849328 | 09840202 |....
003428734 |  1223    |    13    |    0     |
009849328 |    12    |   945    |    34    | ....
.
.
.


I apply a hierchical clustering on that matrix, using Ward 2, squared euclidean distance and standard Z scores. The final output is a dendrogram, with all the item_codes on the branches of the dendrogram.

This is where the tedious process is. The ultimate goal, is to have a hierarchy for our products, and see which attribute (brand, flavor, size...) falls where on the dendrogram. The only way i can think of doing it, is organizing the item codes in the first in the same order that they are in the dendrogram, put a picture of the dendrogram side by side, and eyeball which attribute clusters the most. You'll see in the link below, it looks like brand A and B are clustering together, so I would say that products cluster by brand first. I would then take a closer look at the smaller cluster within the dendrogram and try to identify where the other attributes fit in the hierarchy (the picture is just a snippet of the dendrogram, it is in reality much, much bigger)

This eye balling process is very tedious and annoying. Is there a way to run a similar cluster, which shows a hierarchy within the products, as well as indicate which attribute fit where in that hierarchy? Or is this even the best way to approach this?

I should mention that I'm very new to these clustering algorithm, so apologies if this is a dumb question

• What do you actually want to do? (A) determine which items drive other sales, (B) which items can be grouped together meaningfully according to some rules? (A) and (B) can also be calculated as a function of time (e.g. shopping behavior differs in summer vs winter). For (B) you can try "Association Rules", e.g. the Apriori algorithm, it gives you output such as  1. biscuits=t frozen foods=t fruit=t total=high 788 ==> bread and cake=t 723, .i.e When you buy biscuits, fruits you are also likely to buy something from the "bread and cake" subcategory. Isn't that insightful?
– knb
Commented Jul 29, 2019 at 9:24

I don't think your clustering makes sense in the first place. That you actually get some output is more of a coincidence than a result. Just invoking some functions in a way they don't fail is not enough to get a trustworthy result...

Your matrix is vaguely resembling a similarity matrix, except that it is not symmetric, and the diagonald are not necessarily the maximum values.

Now Ward usually needs a squared Euclidean distance matrix, something very different. The reason why your program doesn't fail supposedly is because it now computes such a distance matrix based on the rows from your similarity matrix. That will usually give somewhat okay looking results (as similar items should have similar rows) but causes subtly problematic bias based on the number of product types.

Later on you can evaluate each hierarchy level based on the agreement with some known label (e.g., manufacturer or product class) and you then can argue which agreement comes earlier. But on a result that isn't valid this doesn't make much sense.

• My bad, I should have made it clearer. Using the matrix, we calculate the squared eucliedean distance between each of the item code. The resulting matrix is what we apply the cluster to. Could you elaborate on your last point about evaluating each hierarchy level? Commented Feb 27, 2019 at 18:02
• Standardizing that matrix makes the results even less meaningful and does not remove the bias problem. Try to write down what quality you optimize there... Don't just chain functions after functions without considering what you are doing on the mathrmatical and statistical level. Commented Feb 27, 2019 at 18:57
• Not sure why this is less meaningful. The original matrix is essentially a measure of the interaction between each item. We then calculate the squared euclidean distance between each of those items to determine how similar they all are, then applying Ward's method. Why wouldn't this work? Commented Feb 28, 2019 at 4:13
• First of all, you can't compute the distance of single interactions, but of interaction vectors. That is where the bias comes in. Secondly, why squared? Third, you are additionally doing standardization. What is the statistical meaning of doing standardization in there? You glue together lots of function, each does some damage, and these problems tend to accumulate to make the result largely random. Try to write down a proper theory of what you assume, then act within that theory to prove it. Don't just add random functions that sound cool to use. Commented Feb 28, 2019 at 7:01
• That makes sense. I'm pretty new at this stuff so it sounds like i have some more research to do. thank you! Commented Mar 1, 2019 at 3:46