I have a large-ish data set (400K records) composed of two fields (both strings). I am looking for a tool that will enable me to cluster the data e.g. around the first column, either using exact matches or some kind of string proximity function like Levenshtein distance. I would also like to be able to find all duplicate records and merge them into one.

OpenRefine looks ideal for my purposes but it is so slow when clustering my data or creating a text facet that it is unusable. Apparently this is a known issue.

I looked around but couldn't find another tool that would enable me to explore a data set of this size, cluster, eliminate dupes, look for anomalies, etc. Can anyone recommend something that might fit the bill?

  • What do you mean to cluster data around a column? You can use standard clustering models, such as k-Means clustering or Gaussian Mixture Models for an exploratory analysis of your dataset. However, I'm not sure this is what you're looking for, please let me know.

  • I often study a new dataset by employing some dimensionality reduction technique. The most common is PCA, but I don't recommend it since it can extract only latent factors that are linearly associated with your variables. You can use t-SNE models (available in sklearn), or, if you are familiar with Deep Learning, with Autoencoders for dimensionality reduction. Once your dataset is compressed/reduced, you can observe how different values-factors are distributed on it. Dimensionality reduction is also very important in case your dataset suffers from high multicollinearity.

  • You can remove duplicates using pandas' drop_duplicates() function, explained here.

Hope this helps, otherwise let me know.

| improve this answer | |
  • $\begingroup$ His data are strings. You can't use kmeans nor GMM nor PCA nor tSNE on this data. $\endgroup$ – Has QUIT--Anony-Mousse Jun 5 '19 at 23:40
  • $\begingroup$ I can definitely analyze the data in code. I was expecting that there would be some good visual tools for doing this, which would greatly accelerate the process. I'm surprised that I haven't been able to find anything (besides OpenRefine which is, as I said, way too slow to be usable with my data). Maybe I'll write my own... $\endgroup$ – Matthew Gertner Jun 6 '19 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.