Unsupervised clustering of demand into groups of hours

user_2987488 0
Viewed 179

I have the following DataFrame that contains for each hour the corresponding consumption of a product. I want to somehow group those hours based on similar demand but the grouping of the hours must be consecutive in order to make sense. For instance, a meaningful grouping of hours could be 10-12 but not (10-12, 2, 4-5).

1970-01-01 08:00:00     9
1970-01-01 09:00:00    11
1970-01-01 10:00:00    28
1970-01-01 11:00:00    26
1970-01-01 12:00:00    26
1970-01-01 13:00:00    32
1970-01-01 14:00:00    24
1970-01-01 15:00:00    30
1970-01-01 16:00:00    23
1970-01-01 17:00:00    32
1970-01-01 18:00:00    27
1970-01-01 19:00:00    21
1970-01-01 20:00:00    16
1970-01-01 21:00:00    13
1970-01-01 22:00:00     1
1970-01-01 23:00:00     0

import scipy.cluster.hierarchy as hcluster
temp_data = df.values

ndata = [[td, td] for td in temp_data]
data = np.array(ndata)

# clustering
thresh = (15.0 / 100.0) * (
            max(temp_data) - min(temp_data))  # Threshold 15% of the total range of data

clusters = hcluster.fclusterdata(data, thresh, criterion="distance")

total_clusters = max(clusters)

clustered_index = []
for i in range(total_clusters):
    clustered_index.append([])

for i in range(len(clusters)):
    clustered_index[clusters[i] - 1].append(i)

clustered_range = []
for x in clustered_index:
    clustered_index_x = [temp_data[y] for y in x]
    clustered_range.append((min(clustered_index_x), max(clustered_index_x)))
print(clustered_range)

The code above (as well as all unsupervised clustering algos) produces some ranges of cluster values BUT it is not aware that the hours must be consecutive; it simply clusters the values. Any idea on how to tackle this constraint and enforce consecutive groups of hours at the same time?

cluster-analysis python
2 Answers

This is a very similar heuristic that tries to achieve what you want.

Essentially you just list down your demand in an array and find out the largest continuous subarray where the absolute value of difference of consecutive elements is within a threshold. You can vary your threshold to get desired output. Setting things up:

import numpy as np, pandas as pd, datetime as dt
date = lambda i: dt.datetime.now()+dt.timedelta(i)
df = pd.DataFrame({"date":[date(i) for i in range(25)], "demand": np.random.randint(0,20,25)})

Original array:

arr = df.demand.tolist()

[7, 11, 11, 4, 6, 6, 8, 10, 18, 11, 2, 12, 16, 0, 12, 8, 11, 15, 16, 14, 18, 14, 19, 3, 15]

Array of (absolute) differences:

diff = [abs(arr[i]-arr[i-1]) for i in range(1,len(arr))]

[4, 0, 7, 2, 0, 2, 2, 8, 7, 9, 10, 4, 16, 12, 4, 3, 4, 1, 2, 4, 4, 5, 16, 12]

Set T to 5. T is the threshold used for window. This is the maximum value of difference in demand you are willing to accept within consecutive dates/hours. Tweak it if you want to increase or decrease the accepted value of difference.

T = 5

Length of interval with current subarray less than T at each timestamp:

counter = 0
intervals = []
for i in range(len(diff)):
    if diff[i]<T:
        counter += 1
    else:
        counter = 0
    intervals.append(counter)

[1, 2, 0, 1, 2, 3, 4, 0, 0, 0, 0, 1, 0, 0, 1, 2, 3, 4, 5, 6, 7, 0, 0, 0]

Largest continuous interval with the condition being met:

max_interval_idx = max(range(len(intervals)), key=lambda i: intervals[i])
max_interval = intervals[max_interval_idx]

Verify answer:

print(arr[max_interval_idx-max_interval +1: max_interval_idx +2])

[12, 8, 11, 15, 16, 14, 18, 14]

Notice that all consecutive differences are less than 5.

This is your answer:

df["date"][max_interval_idx-max_interval +1: max_interval_idx +2]

Now you can vary your T to get different groupings.

user_95630060

I'm just expanding the answer by @Sebastian Hoffmann. I assume that your data has no "dead hours". If it is not the case, you will need to fill missing rows with (say) -100, get the cluster ids and remove the rows you've added afterwards. as cluster ids don't have to be consecutive.

df = pd.DataFrame([('1970-01-01', '08:00:00',  9), ('1970-01-01', '09:00:00', 11), ('1970-01-01', '10:00:00', 28), ('1970-01-01', '11:00:00', 26), ('1970-01-01', '12:00:00', 26), ('1970-01-01', '13:00:00', 32), ('1970-01-01', '14:00:00', 24), ('1970-01-01', '15:00:00', 30), ('1970-01-01', '16:00:00', 23), ('1970-01-01', '17:00:00', 32), ('1970-01-01', '18:00:00', 27), ('1970-01-01', '19:00:00', 21), ('1970-01-01', '20:00:00', 16), ('1970-01-01', '21:00:00', 13), ('1970-01-01', '22:00:00',  1), ('1970-01-01', '23:00:00',  0)], columns=['Date','Time','data'])    

thresh = 5.4
df['cluster_id'] = (df.data.diff().abs() > thresh).cumsum()

The result is

           Date      Time  data  cluster_id
0   1970-01-01  08:00:00     9           0
1   1970-01-01  09:00:00    11           0
2   1970-01-01  10:00:00    28           1
3   1970-01-01  11:00:00    26           1
4   1970-01-01  12:00:00    26           1
5   1970-01-01  13:00:00    32           2
6   1970-01-01  14:00:00    24           3
7   1970-01-01  15:00:00    30           4
8   1970-01-01  16:00:00    23           5
9   1970-01-01  17:00:00    32           6
10  1970-01-01  18:00:00    27           6
11  1970-01-01  19:00:00    21           7
12  1970-01-01  20:00:00    16           7
13  1970-01-01  21:00:00    13           7
14  1970-01-01  22:00:00     1           8
15  1970-01-01  23:00:00     0           8

To get your clusters ids, filter those with more than single entry:

clusters = (df.cluster_id.value_counts() > 1)
clusters[clusters].index.values


array([7, 1, 8, 6, 0], dtype=int64)
user_85058170
Related