# Clustering the data if some values in each vector is not applicable

I have four different categories which the number of variables in each category is 7, 3, 8, and 9 (in total 27). The value of each variable changes from 1 to 5 but some of the variables were not applicable for some observations. In total, there are 20 observations which their data is available for 27 variables in the mentioned context (from 1 to 5 plus not applicable). How can I cluster these four categories for 20 observations to get three clusters for each category?

edit: There are 4 categories (A, B, C, and D). Each category has a different vector including 7, 3, 8, and 9 values (from 1 to 5 plus NA). Now, it's 18 observations for each category of A, B, C, and D. I need to make three groups for each category. How can I find the centroid of each cluster for each category? It means I need three centroid in each category, totally 12(=4 category* 3 groups). Any feedback would be appreciated. Thank you.

• Please add some additional information and formatting so that your question is more understandable. If you are having a difficult time describing your question, you could post your data within the question or post a portion of it. – AN6U5 Sep 18 '15 at 20:24

First, are your categories independent of each other? If this is really four parallel problems, solve one at a time and it'll simplify things a bit.

Second, the heart of clustering is the distance function between any two observations. Because your data has missing values, you need to explicitly decide how to handle them. A common approach is to ignore the problem by throwing away any rows that have any null values, which won't work at all for your data.

A simple approach would be to ignore any NA columns, but keep the rest of the columns--for category C, the difference between rows 1 and 2 would be 4 (columns 11, 14, 15, and 16 are all off by one). There's an issue with this approach that I'll get to later, but let's implement it for now.

For each category, we need three rows of centroid positions (we might as well start off with randomly selected rows that have no missing values in them). We then calculate distance to each centroid using a bunch of summed ifs: if(matrix cell != NA, matrix cell - centroid cell, 0) across all the columns. (If you're doing this computation in a spreadsheet, I'd use intermediate cells to calculate the pieces instead of dealing with one huge formula.)

We then optimize the centroids with a standard iterative procedure: each row gets a group membership corresponding to the closest centroid, then each centroid becomes the mean of its group members (and it looks like your mean function automatically excludes NAs), then we repeat until satisfied.

Why did I specify that we had to seed with a row without NAs? If you allow NA measurements in the centroids, this will fail because a row of all NAs is as central as it gets! If our update step never lets us move to NAs, the NA column will be transient, but what happens when we take the mean of a missing column? (In your sample data, consider what happens if rows 4-12 are a group in the B category.)

So if we want to allow NAs in the centroids, we need to modify our distance function to something like the following:

• If both cells are numbers, the distance is the difference
• If both cells are NA, the distance is 2
• If only one cell is NA, the distance is 3.

Those numbers are chosen such that for any full column of numbers, a numerical value in the centroid position will be closer, but if the column has sufficiently many NA rows, a NA value in the centroid will be closer. You might want to adjust the values so that the NA threshold looks like what you want it to.

In order to find the centroid of a set of rows using our distance function, we should use an optimization procedure, but we can cheat: if the number of NAs in the group is above the threshold, we set it to NA, and otherwise we set it to the mean.

• Thank you for your detailed information. But I have two questions: 1- How did you get the difference between rows 1 and 2 as 3 for category C, (columns 11, 15, and 16 are all off by one). Don't you consider column 16 which is -1, therefore, it must be 2 instead of 3, am I right? 2- What is the problem for the first approach (which was simpler) rather than being cautious about choosing the seeds? Thank you again. – Amir Sep 20 '15 at 2:25
• @Amir, I missed that column 14 was also different, so the total distance should be 4. When calculating distance, we need to use absolute value so that distances are non-negative and symmetric. (If we want '0 distance' to mean that two rows are the same, we can't let +1 in one column cancel out a -1 in another column. If we want to use one number for distance, we need 2 to be 2 away from 4 and 4 to be 2 away from 2, instead of -2 away from 2.) The problem with the first approach is a centroid with all NA values will be as close as possible to all rows--which makes us unable to cluster! – Matthew Graves Sep 21 '15 at 14:25