How to sample large database and implement K-means and K-nn in R?
k-means, knn, large-data, machine-learning, r
Solution
The way I would proceed is:
1) Extract a list of ids of your table to R, you can do this with a simple SQL query using the RMySQL library.
2) Split the ids in any way you like in R, and then do subsequent SQL queries again using RMySQL (I found this two step approach much quicker than sampling directly in MySQL).
3) Depending on how large is your sample you could get away by using the base R kmeans implementation, this however might fail for bigger samples, in that case you should look into using bigkmeans from the library biganalytics.
Problem
I'm a new user to R, trying to move away from SAS. I'm asking this question here as I'm feeling a bit frustrated with all the packages and sources available for R, and I cant seem to get this working mainly due to data size. I have the following: A table called SOURCE in a local MySQL database with 200 predictor features and one class variable. The table has 3 million records and is 3GB large. The number of instances per class are not equal. I want to: - A randomly sample the SOURCE database to create a smaller dataset of with equal number of instances per class. - Divide sample into training and testing set. - Preform k-means clustering on training set to determine k centroids per class. - Preform k-NN classification of test data with centroids.