Classify Database Tables using Unsupervised Machine Learning?

Question

I have a unique use case for ML - I need to classify Database Tables. They contain meta-data which is Alphanumeric. I know that some tables are similar to others, but I don't know how many groups exist.

Is there a way that I can feed this data to a cluster-classifier and find out how many possible groups exist in my pool of tables?

Thanks in advance for replies!

Well, that depends on the metric you use. Do you have a sense which tables are more similar to each other than the others? Can you quantify this somehow? — pythonic833, Mar 28 '18 at 20:25
Scikit-learn has [many](http://scikit-learn.org/stable/unsupervised_learning.html) methods for unsupervised learning. If you need help figuring out which one is most appropriate for your data, you will get better answers on [stats stack exchange](https://stats.stackexchange.com/). Other wise providing a small sample of the data, what you have tried, and what you would expect the result to be, will help tremendously — johnchase, Mar 28 '18 at 20:26

score 0 · Answer 1 · answered Mar 28 '18 at 20:57

This is not the answer to your problem but could give you a first idea what is possible and how I would approach this problem once I found a good metric to tackle this clustering problem.

Let's assume we figured out euclidean metric is well suited, then we can do the following (I use random numbers here, just for illustration)

X = np.random.randint(100, size=(20,5))

from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score

for i in range(2,10):
    km = KMeans(n_clusters=i)
    y_pred = km.fit_predict(X)
    print('num_clusters: ' + str(i) + "\t" + str(silhouette_score(X,y_pred)))

Output:

num_clusters: 2 0.24318056918852374
num_clusters: 3 0.21859606573283147
num_clusters: 4 0.2320853440044738
num_clusters: 5 0.21159893083770434
num_clusters: 6 0.2436021768392968
num_clusters: 7 0.2798416731321928
num_clusters: 8 0.31839456337186695
num_clusters: 9 0.27654631385700396

The silhouette scores how well clusters fit together and how strong the overlap between the clusters is concerning a given metric. Perfect score is 1 worst is 0. So in this special case 8 clusters would fit the problem.

But keep in mind, you need to choose the algorithms appropriately to your problem so you need to have an criteria for tables being similar and for tables being totally different.

Classify Database Tables using Unsupervised Machine Learning?

1 Answers1