Naming each centroid is always a challenge. Some results make a lot of sense - while others give great insight into what are the prevalent surrounding technologies to any Stack Overflow tag. I’ll even look out for some tags I’m interested in: How are google, amazon, and azure represented in each cluster? These are the 50 groups that k-means clustering found - given the 1-hot encoding of related tags we did earlier in this post. Now we wait - while BigQuery shows us the progress of our training:ĭavies-Bouldin index: 1.6910 Mean squared distance: 0.52332 Get ready for the results: The 50 clusters are…

With this line, I’m creating a one-hot encoding string that I can use later to define the 4,000+ columns I’ll use for k-means: one_hot_big = client.query(''' SELECT STRING_AGG( FORMAT('IFNULL(ANY_VALUE(IF(tag2='%s',1,null)),0)X%s', tag2, REPLACE(REPLACE(REPLACE(REPLACE(tag2,'-','_'),'.','D'),'#','H'),'+','P')) ) one_hot FROM ( SELECT tag2, SUM(questions) questions FROM `deleting.stack_overflow_tag_co_ocurrence` GROUP BY tag2 # ORDER BY questions DESC # LIMIT 10 ) ''').to_dataframe().ilocĪnd training a k-means model in BigQuery is really easy: CREATE MODEL `deleting.kmeans_tagsubtag_50_big_a_01` OPTIONS ( model_type='kmeans', distance_type='COSINE', num_clusters=50 ) AS WITH tag_and_subtags AS ( SELECT tag1, %s FROM `deleting.stack_overflow_tag_co_ocurrence` WHERE percent>0.03 GROUP BY tag1 ) SELECT * EXCEPT(tag1) FROM tag_and_subtags

Now - instead of using this small table, let’s use the whole table to compute k-means with BigQuery. You can reduce or augment the sensibility of these relations with the percent threshold: SELECT tag1 ,IFNULL(ANY_VALUE(IF(tag2='javascript',1,null)),0) Xjavascript ,IFNULL(ANY_VALUE(IF(tag2='python',1,null)),0 ) Xpython ,IFNULL(ANY_VALUE(IF(tag2='java',1,null)),0) Xjava ,IFNULL(ANY_VALUE(IF(tag2='c#',1,null)),0) XcH ,IFNULL(ANY_VALUE(IF(tag2='android',1,null)),0) Xandroid ,IFNULL(ANY_VALUE(IF(tag2='php',1,null)),0) Xphp ,IFNULL(ANY_VALUE(IF(tag2='html',1,null)),0) Xhtml ,IFNULL(ANY_VALUE(IF(tag2='css',1,null)),0) Xcss ,IFNULL(ANY_VALUE(IF(tag2='node.js',1,null)),0) XnodeDjs ,IFNULL(ANY_VALUE(IF(tag2='jquery',1,null)),0) Xjquery ,SUM(questions) questions_tag1 FROM `deleting.stack_overflow_tag_co_ocurrence` WHERE percent>0.03 GROUP BY tag1 ORDER BY questions_tag1 DESC LIMIT 100 K-means clustering time ‘unit-testing’ a relation to almost every column here, except to ‘php’, ‘html’, ‘css’, and ‘jquery’.‘multi-threading’ shows a relation to ‘python’, ‘java’, ‘c#’, and ‘android`.‘machine-learning’ shows a relation to ‘python’, but not the other way around.‘javascript’ shows a relation to ‘php’, ‘html’, ‘css’, ‘node.js’, and ‘jquery’.What you see here is a co-occurrence matrix:

Bitnami mean stack for ruby on rails