如何对数据库数据应用K-Means算法、使用Confusion Matrix及处理字符串编码

Hey there! Let's tackle your K-Means questions one by one—this workflow is totally doable with a few clear steps.

First, let's walk through the full pipeline:

Step 1: Pull data from your database #

Use pandas with SQLAlchemy to fetch your data into a dataframe. Adjust the connection string to match your database type (PostgreSQL, MySQL, etc.):

import pandas as pd
from sqlalchemy import create_engine

# Connect to your database (replace with your actual credentials)
engine = create_engine('postgresql://user:password@host:port/your_database')
df = pd.read_sql('SELECT * FROM your_target_table', engine)

Step 2: Preprocess your data #

Before running K-Means, all features need to be numeric (we'll cover string-to-numeric conversion in the next section). Once cleaned, split out your features and (if you have them) your true labels—these are required to use a confusion matrix.

Step 3: Run K-Means and align cluster labels #

K-Means is unsupervised, so it assigns arbitrary cluster IDs (like 0,1,2) that don't automatically match your true labels (1,2,3 for trash/car/truck). You need to map these cluster IDs to your actual labels first (usually via majority vote in each cluster):

from sklearn.cluster import KMeans
from sklearn.metrics import confusion_matrix
from sklearn.utils import linear_assignment_
import numpy as np

# Assume df_numeric is your cleaned, all-numeric dataframe
X = df_numeric.drop('true_label', axis=1)  # Your feature set
y_true = df_numeric['true_label']  # Ground truth labels (required for confusion matrix)

# Fit K-Means to your features
kmeans = KMeans(n_clusters=3, random_state=42)
y_pred = kmeans.fit_predict(X)

# Map cluster IDs to match your true labels
def align_cluster_labels(y_true, y_pred):
    cm = confusion_matrix(y_true, y_pred)
    # Use linear assignment to find the optimal label mapping
    idx = linear_assignment_.linear_assignment(cm.max() - cm)
    return np.array([idx[i][1] for i in idx[:,0]])[y_pred]

y_pred_aligned = align_cluster_labels(y_true, y_pred)

# Now compute the confusion matrix with aligned labels
print("Confusion Matrix:")
print(confusion_matrix(y_true, y_pred_aligned))

Quick note: If you don't have true labels, you can't use a confusion matrix—it's a supervised metric. In that case, use unsupervised metrics like silhouette score or calinski-harabasz index to evaluate clustering quality.

Converting string columns #

You have two reliable options, depending on your use case:

• Manual mapping: Ideal if you know all possible string values and want explicit control over numeric labels:

  # Define your custom mapping
  category_map = {"trash": 1, "car": 2, "truck": 3}
  df['object_type'] = df['object_type'].map(category_map)
  # Add a default for unexpected values if needed
  df['object_type'] = df['object_type'].fillna(0)
  

• LabelEncoder: Great for when you have many unique strings or don't want to define a manual map. It assigns a unique integer to each unique string:

  from sklearn.preprocessing import LabelEncoder
  
  le = LabelEncoder()
  df['object_type'] = le.fit_transform(df['object_type'])
  # Reverse the mapping later if you need strings back:
  # le.inverse_transform(df['object_type'])
  

  Avoid OneHotEncoder for clustering—it creates high-dimensional sparse data which can distort K-Means' distance calculations.

Using all columns for clustering #

Yes, you can use all columns—but keep these rules in mind:

1. All columns must be numeric: No leftover strings, no missing values (impute with mean/median or drop rows if appropriate).
2. Scale your features: K-Means relies on Euclidean distance, so features with larger scales (e.g., a "price" column in thousands vs a "count" column 0-5) will skew results. Use StandardScaler or MinMaxScaler:

   from sklearn.preprocessing import StandardScaler
   
   scaler = StandardScaler()
   scaled_features = scaler.fit_transform(df_numeric)
   

3. Drop irrelevant columns: Columns like IDs, random timestamps (unless time is a meaningful feature), or noise will hurt cluster quality. Only keep columns that contribute to the patterns you're trying to find.

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