nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Clustering and Big Data - Customer Power Usage Profiles for a Power Utility

HydeMiller

I’m preparing for a visit to a power utility customer (JMPPRO) who is interested in clustering their customers by power usage. They meter each account and sample power usage every 15mins. When you do the math on this, it quickly turns into big data (96 readings a day on 10K customers is about 1M readings per day). I'm trying to use clustering to group customer accounts based on similar power usage patterns. I'm new to clustering, so not sure I'm using the best method. I've tried stacking and splitting the data, splitting appear to work the best although a week of readings is already 672 columns with 10K rows for the customer accounts. Hierarchical clustering gives a very nice result but can't handle 10K rows, it works great on 2K rows but not 3K rows. K Means clustering can handle all 10K rows but the results aren't as good as Hierarchical, many dissimilar accounts are clustered together. So right now, I'm boxing in the data with only 7 days of metering and 2K accounts. I'm getting a very nice result and it demonstrates the technique but it's very sampled down from the real use case. If I'm missing something, I'd appreciate some pointers. I have an example file but it is large, 190MB.

Accepted answers

All comments

Mark_Bailey

You might additionally consider deriving new meaningful responses. For example, peak usage, peak usage in past six months, average usage, and so on. The raw data contains information but as such what does it say every fifteen minutes?

DonMcCormack

I agree with Mark. The trick will be to aggregate the data into meaningful metrics to reduce the number of rows (not sure what you can do with the columns, since I assume they represent customers). How about calculating median use by different time periods (e.g., hour of day, day of week, week of year, month of year, etc.), then derive a deviation score for each of these (summed squared or absolute difference, maybe). I'd also considering looking at some scaled version of these, since I suspect they'll be very skewed. Another idea would be to cluster a subsample, like your doing, then use a neural network (or tree) to predict cluster.

I wouldn't mind seeing the example file, if you could share it.

Don

jiancao

Based on your description I think that is the way to cluster accounts on the data consisting of columns of power usage and rows of accounts. As you have discovered hierarchical clustering cannot handle large # of rows but K-means can. This is known in clustering and is one of key considerations when choosing between the two methods. You said that Kmean clustering group many dissimilar accounts while hierarchical clustering doesn't. I was wondering how many clusters you had set when running K-means. And, as a comparison, how many clusters did you have using hierarchical clustering? Maybe you need to try a larger number of clusters with K-means.

jiancao

Wanted to add that for hierarchical clustering with Fast Ward option, you still can analyze data up to 200,000 rows. http://www.jmp.com/support/help/13/Overview_of_Platforms_for_Clustering_Observation.shtml

martindemel

Just a Short comment to Dons response about nn:
In energy companies they used exactly that approach, taking a sample and find appropriate clusters and then using neural nets to predict the rest of the data into these clusters. I've done this quite often while working for MathWorks and it worked quite well. Of course you would have to check the nn model from time to time as things can change over time and the initial groups may change too.
Those companies use these classifications often to specify which contract and price offer they provide to new customers where they request a consumption profile. Also sometimes they are using this to predict the usages for their customers to be able to trade the free resources without getting into trouble in peak times.

Best
Martin

HydeMiller

Thank you everyone for your inputs. Some of you were interested in the file. I'm putting two files on the file exchange, one of them acts as a control file and they other has an embedded script to build a large data table. It ends up with about 6M rows and took 2mins on my end to finish. The two files should be linked by Account, but not sure if this works after shuffling the files around, so you might have to establish the virtual join. The control file is called "Customer Info" and the self building file is called "Power Comany Example - Self building (Large) Table". Just make sure both files are open in the JMP session before running the embedded script.

HydeMiller

I got some requests to see the example file and I posted this on the file exchange. The download itself is a small file and there is an embedded script that will build the table. Be careful how you select the parameters because you can end up with a giant table.

I'm using a cosine function to simulate the power usage curve during the day. Duke told me what this curve really looks like, which is like a cosine with a hump for afternoon and evening power usage.

https://community.jmp.com/t5/JMP-Sample-Data/Power-Company-Metering-Example-Data/ta-p/42016