Approach to clustering a large data frame (~7M * 60) with different data types

Question

I posted a similar question yesterday. However, this question is distinct since I'm more seeking validation on my approach.

I have a data frame with about 7M rows and 60 features. The features are a mix of data types including booleans, currency, counts/integers. Here are some sample fields cut and paste from my other recent post:

• (Currency / Money) Customer monthly spend in $
• (Count) Quantity of service x customer has active with us
• (Count) Quantity of service y that customer has with us
• (Boolean) Is customer a premium services customer
• (Boolean) Is customer some other kind of customer boolean
• (Time) Customer tenure in months
• (Time but entirely different meaning) Average time in a month spent using our call center

I went into this project thinking of using kmeans since I've used it before and it's simple and intuitive to understand. However, after doing some research I found that kmeans is not really suited to differing data types.

Nevertheless, I'm curious to see the clusters it returns so I scaled all of my data to be between 0 and 1 (since I have some 1 and 0 preprocessed categorical features, I thought it would help to transform all my data to be between 0 and 1).

Where pdata_scaled is my data frame with regular actual numbers and then being scaled to be between 0 and 1.
pdata_scaled[scale_features] <- lapply(pdata_scaled[scale_features], 
                                       function(x) {(x-min(x))/(max(x)-min(x))})

I then run kmeans, add the cluster vector to my original data frame, group by cluster and report some summary data about each cluster.

However, this post suggests to use Gowers distance when working on data of mixed types.

Copying the example provided in the answer on that post, I created a distance matrix:

pdata_gower_distance_matrix <- cluster::daisy(select(pdata, -c(year_month, shopper_id)), metric = "gower")

Question 1: When creating a distance matrix is it best to use raw data or should I use the scaled data frame?

I was unable to create a distance matrix on my full data frame, eventually I was able to create one with a sample of 10K rows only. That's a fraction of 1% of my whole data (0.14%).

Even with this sample of 10K rows / < 1% of all my data, when I try to run pamk clustering: (see documentation here)

pam_cluster <- pamk(pdata_gower_distance_matrix, 
                    krange = 10, 
                    criterion = "multiasw",
                    ns = 2,
                    usepam = F) 

The documentation says set usepam to False for large data sets, in which case clara method is used over pam:

"logical. If TRUE, pam is used, otherwise clara (recommended for large datasets with 2,000 or more observations; dissimilarity matrices can not be used with clara)."

Given it appears to take a long time to cluster using this clara method, and that pam is not suitable for data frames with more than 2K rows, here is what I was thinking of as an approach:

1. Use KMeans to determine appropriate number of clusters using elbow chart, since doing anything similar with fpc::pamk() would take a very long time.
2. Cluster on a sample, e.g. 10 or maybe even 50K rows.
3. Use a classifier such as Random Forrest of XGB to fit a model on the the 10 or 50K clustered data, using the cluster vector as labels.
4. Predict the clusters of the remaining 6,990,000.

Is my approach reasonable? Is there a more sensible option? The difference between my sample, 10K or even 50K is so much smaller than my full data that it makes me uncomfortable.

I'm constrained by compute power. currently I'm working on a Mac, dual core i5 16GB.

Any minute now I'm going to have a virtual machine with 8 cores and 47GB but I'm not sure that will change things much?

How should I cluster my 7M records of mixed data types?

Answer 1

Much of these things (scaling, CLARA instead of PAM, etc.) are just hacks to be able to invoke some method x on your data.

You need to take a step back. What does the function x actually do? What effect do the hacks have on the outcome of function x? And how does all this effect the solution to your problem? Then make sure that you are really solving the right problem.

Much of the time, people encode categories because they find this as the only option to make the data "numeric". Then they scale everything because they have read somewhere that this can help. But what are they actually doing? The results won't be much more meaningful than a random generator...

First do the math and stats. Figure out what is the right way to answer your problem. Then do the code.