# Algorithm for creating cluster groups on two criteria

I would like to group a population based on two criteria. I will use an analogy to simplify my question.

Let's say I want n number of groups. I want to populate those groups based on person's age and weight, so that all groups contain about the same total age and are evenly distributed by weight (so that there are about same number of heavy and light people in each group).

What kind of algorithm could I use to automate this process? Is there a simple Excel formula or some other method?

UPDATE

Here is the motivation for this statistical analysis. I would like to set up partitioning in a database which will have the best performance. I need to store a lot of data which is grouped by county. I do not know ahead of time, what would be the best number of partitions. Partitions should be uniform, so that they contain about the same number of rows. A partition should hold data rows for one or more counties. Each county will be ranked on the frequency and possibly quantity of updates. Partitions should be built so that frequently updated county data is uniformly distributed.

It does not seem as if there is a simple way to do this. So what kind of algorithm would work for this? I probably would not use VBA for coding, instead most likely I would use perl to write the program for doing the analysis. Are there any ready-made statistical tools that do these type of analysis?

Let me clarify what I mean when I say n number of groups. I will basically pick a number of groups (partitions), plug it into the formula or analysis tool, or custom program. Then I will repeat the process for a different number of groups (partitions) until I find by trial and error, the number of partitions that yields best performance.

Maybe there is a name for this type of analysis? Something that I could try to research via a search engine?

• I got a laugh out of the prospect of using Excel for this :-). Seriously, it sounds like you need additional constraints. After all, with \$m\$ people, if I can find one group of \$m-n+1\$ people with an even age and weight distribution, then I can make \$n-1\$ more groups out of the \$n-1\$ remaining individuals, putting each in their own group. Would you be happy with such a solution? Even more to the point, to get appropriate answers you really need to share with us the statistical motivation for this question. (It's off topic here otherwise.) Ultimately, maybe grouping isn't the answer... Commented Apr 13, 2012 at 18:48
• I agree with @whuber, as it stands you could just sort on age and then sort on weight then take the top n and put them in their own groups and the bottom n and put them in the groups and repeat. Just write a simple VBA loop if it is that long of a list. It gets at what you want, though knowing why you want it would make for a better answer. Commented Apr 13, 2012 at 19:19
• It seems to me you are just looking for some kind of bootstrapping method? Definitely you are not looking for clustering algorithm. I second whuber suggestion to drop Excel for this, I would go with R, but that is just personal preference.
– nico
Commented Apr 14, 2012 at 6:29
• "so that all groups contain about the same total age and are evenly distributed by weight" -- that's not clustering, that's actually opposite of clustering. Clustering means dividing set into groups of similar characteristics. Commented May 7, 2012 at 10:41

@Anony-Mousse, usually (or rather in its simplest form) "cluster analysis" is used to build clusters of similar objects.

I would suggest @dabest1 to consider looking into biclustering - this wikipedia article seems to be a bit weak at the time of the post.

I have discussed Biclustering in another post in CV.

To further help you in your research, here are a few links that will help get you started in Biclustering from the aforementioned post:

HTH!

Usually, the term "cluster analysis" refers to building groups of similar objects.

Your intention however seems to build groups of diverse objects, such that each group is a somewhat representative sample of your data and similarly sized, right? Why would you choose the sum of age to be approximately the same?

Definitely look into sampling strategies. You will much more likely find you answer there than in the clustering domain. Because again, clustering usually tries to minimize variation inside each group.

• That was only an analogy about a person's age. I have already updated my post with more information explaining why I need to do this. Person's age is an analogy for number of data rows for a given county. You are right, my intention is not to build groups of similar objects. Commented Apr 14, 2012 at 6:35
• That is why I suggested looking into sampling. Commented Apr 15, 2012 at 18:52