如何将聚类结果的cluster_number映射到消费类别并提取timestamp列表？

原始聚类数据如下：

timestamp  consumption  cluster_number
0           0    35.666667               1
1           1    29.352222               1
2           2    24.430000               1
3           3    21.756667               1
4           4    20.345556               1
5           5    19.763333               1
6           6    19.874444               1
7           7    22.078889               1
8           8    28.608889               1
9           9    33.827778               2
10         10    36.414444               2
11         11    38.340000               2
12         12    43.305556               2
13         13    43.034444               2
14         14    39.076667               2
15         15    36.378889               2
16         16    36.171111               2
17         17    40.381111               2
18         18    48.692222               0
19         19    52.330000               0
20         20    50.154444               0
21         21    46.491111               0
22         22    44.014444               0
23         23    40.628889               0

需求说明 #

无法预先知晓cluster_number与消费高低的对应关系，但明确规则：

• 消费值最高的聚类组对应高消费类别
• 消费值最低的聚类组对应低消费类别
• 剩余聚类组对应中等消费类别

最终需提取每个类别对应的timestamp列表。

实现方案（基于Python Pandas） #

核心思路是通过聚类组的平均消费值排序，自动建立聚类编号到消费类别的映射，再分组提取时间戳。

完整代码如下：

import pandas as pd

# 构造原始数据
data = {
    'timestamp': [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23],
    'consumption': [35.666667,29.352222,24.43,21.756667,20.345556,19.763333,19.874444,22.078889,28.608889,33.827778,36.414444,38.34,43.305556,43.034444,39.076667,36.378889,36.171111,40.381111,48.692222,52.33,50.154444,46.491111,44.014444,40.628889],
    'cluster_number': [1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,0,0,0,0,0,0]
}
df = pd.DataFrame(data)

# 计算每个聚类的平均消费值并排序
cluster_mean = df.groupby('cluster_number')['consumption'].mean().sort_values()

# 自动建立聚类编号到消费类别的映射
cluster_mapping = {
    cluster_mean.index[0]: 'low',   # 均值最小的聚类对应低消费
    cluster_mean.index[1]: 'middle',# 均值中间的聚类对应中等消费
    cluster_mean.index[2]: 'high'   # 均值最大的聚类对应高消费
}

# 添加消费类别列
df['consumption_level'] = df['cluster_number'].map(cluster_mapping)

# 提取每个类别的timestamp列表
high = df[df['consumption_level'] == 'high']['timestamp'].tolist()
low = df[df['consumption_level'] == 'low']['timestamp'].tolist()
middle = df[df['consumption_level'] == 'middle']['timestamp'].tolist()

# 输出结果
print("high =", high)
print("low =", low)
print("middle =", middle)

运行结果

high = [18, 19, 20, 21, 22, 23]
low = [0, 1, 2, 3, 4, 5, 6, 7, 8]
middle = [9, 10, 11, 12, 13, 14, 15, 16, 17]

注：你给出的目标middle列表包含18属于笔误，18对应的消费值属于最高聚类组，实际应归为高消费类别。

内容的提问来源于stack exchange，提问作者Kosmylo