Clustering and confusion matrix

Here is the problem The initial four cluster partition {c1, c2, c3, c4} for the text collection is provided by this link text vs cluster. Assuming that the ground-truth partition is given by

cacm texts belong to cluster1
cisi texts belong to cluster2
cran texts belong to cluster3
med texts belong to cluster4

build the confusion matrix (CM) for the partition provided. That is build a 4X4 matrix whose row i shows distribution of ci elements among cluster1, cluster2, cluster3, and cluster4. For example the first row is computed as follows: CM11=|c1∩cluster1|, CM12=|c1∩cluster2|, CM13=|c1∩cluster3|, and CM14=|c1∩cluster4| I understand the problem just not sure how to code it up. I was thinking along the lines of this

//open text file
fileID = fopen('list.txt');
C = textscan(fileID,'%s %s');
fclose(fileID);

What this does is it orders the names as $C\{1\}\{j\}$is the file name and $C\{2\}\{j\}$ is the cluster name. By the way I am using Matlab

• Where is your data stored? If it is a SQL database then you should just group by the cluster and concept to get the confusion matrix. Pandas his a similar functionality and you can read quite easily most data sources into it.
– DaL
Dec 3 '15 at 7:17

Assume you clustering results are store in R. You have the partition id part_id and the real clust_id, here in a small example

df <- read.table(text = "obj,  part_id, clust_id
X, 1, 1
Y, 2, 2
Z, 3, 3
U, 1, 3
V, 2, 3
W, 2, 3"

Than the table will do the work for you

> table(df[,c("part_id","clust_id")])

clust_id
part_id 1 2 3
1 1 0 1
2 0 1 2
3 0 0 1

UPDATE

For completeness you may also use SQL (as proposed in a comment), a sample query is shown below on a table identical to the data-frame above.

You will see imediately, that there is a limitation because you must define the exact column list in the query - so the query must be adjusted based on the cluster / partition list.

with clust_agg as (
select part_id, clust_id, count(*) cnt from clust
group by part_id, clust_id
)
select * from clust_agg
pivot(sum(cnt) clust  for (clust_id) in
(1 as "1",
2 as "2",
3 as "3"))
order by 1;