如何将数据库标称数据转为向量以计算cosine similarity？附数据集示例

Hey DaveK, great question! Let's walk through how to turn your nominal (categorical) data into vectors for cosine similarity, plus some alternative similarity metrics that might be a better fit for your use case.

First off, raw text values like "Male" or "computer science" can't be directly used for cosine similarity—we need to turn them into numerical vectors. The most common methods for this are one-hot encoding (best for unordered categories, which is exactly what you have) and label encoding (only useful for ordered categories like "Low/Medium/High", so we can skip that here).

Method 1: One-Hot Encoding (The Go-To for Small Cardinality) #

One-hot encoding creates a binary feature (0 or 1) for every possible value in each categorical column. Let's map your dataset to see how this works:

• gender has 2 values → 2 binary features
• race has 3 values → 3 features
• city has 3 values → 3 features
• county has 3 values → 3 features
• major has 3 values → 3 features

That gives us a total of 14 dimensions for each record's vector.

For example:

• Record 1 (Male, White, St. Paul, Ramsey, computer science) becomes:
  [1, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0]
  (Breakdown: First 2 = gender (Male=1), next 3 = race (White=1), next 3 = city (St. Paul=1), next 3 = county (Ramsey=1), last 3 = major (computer science=1))
• Record 2 (Female, White, St. Paul, Ramsey, math) becomes:
  [0, 1, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 1, 0]

Calculating cosine similarity between these two:
The formula is cosθ = (v1 · v2) / (||v1|| * ||v2||)

• Dot product: 10 + 01 + 11 + 00 + 00 + 11 + 00 + 00 + 11 + 00 + 00 + 10 + 01 + 00 = 3
• Magnitude of v1: √(1²+0²+1²+0²+0²+1²+0²+0²+1²+0²+0²+1²+0²+0²) = √5 ≈ 2.236
• Magnitude of v2: √(0²+1²+1²+0²+0²+1²+0²+0²+1²+0²+0²+0²+1²+0²) = √5 ≈ 2.236
• Cosine similarity: 3/(5) = 0.6 → which makes sense, since they share 3 out of 5 attributes.

Quick Python Implementation #

Here's how to do this with pandas and scikit-learn:

import pandas as pd
from sklearn.metrics.pairwise import cosine_similarity

# Build your sample dataset
data = pd.DataFrame({
    'id': [1, 2, 3, 4],
    'gender': ['Male', 'Female', 'Male', 'Female'],
    'race': ['White', 'White', 'Black', 'Asian'],
    'city': ['St. Paul', 'St. Paul', 'Bismark', 'New York'],
    'county': ['Ramsey', 'Ramsey', 'Gotham', 'Betty'],
    'major': ['computer science', 'math', 'English', 'computer science']
})

# Generate one-hot encoded features (drop the id column since it's not a similarity feature)
one_hot_features = pd.get_dummies(data.drop('id', axis=1))

# Compute pairwise cosine similarity matrix
similarity_matrix = cosine_similarity(one_hot_features)

# Example: Similarity between record 1 and record 2
print(f"Similarity between record 1 and 2: {similarity_matrix[0][1]:.2f}")

Method 2: Embedding Encoding (For High-Cardinality Categories) #

If you had categories with hundreds of values (like hundreds of cities), one-hot encoding would create a huge, sparse vector. In that case, you could use embedding encoding:

• Train a simple neural network to map each category to a low-dimensional vector (e.g., 10-50 dimensions)
• Or use pre-trained language models (like Word2Vec) if your category names have semantic meaning (e.g., "St. Paul" and "Minneapolis" would have similar embeddings)
  This reduces dimensionality and captures subtle relationships between categories, but it needs more data to work well.

Cosine similarity works, but there are metrics tailored specifically for categorical data that might be more intuitive or perform better:

1. Jaccard Similarity #

Jaccard measures the size of the intersection of two sets divided by the size of their union. Treat each record's attributes as a set:

• Record 1's set: {Male, White, St. Paul, Ramsey, computer science}
• Record 2's set: {Female, White, St. Paul, Ramsey, math}
• Intersection: {White, St. Paul, Ramsey} (size 3)
• Union: {Male, Female, White, St. Paul, Ramsey, computer science, math} (size 7)
• Jaccard similarity: 3/7 ≈ 0.43
  It's great for focusing on overlapping attributes, and you don't need to create high-dimensional vectors.

2. Normalized Hamming Distance #

Hamming distance counts how many attributes differ between two records. Normalize it by dividing by the total number of attributes to get a similarity score (1 - Hamming distance / total attributes):

• Record 1 and 2 differ in 2 attributes (gender, major)
• Total attributes: 5
• Similarity: 1 - 2/5 = 0.6 (matches the cosine result here)
  Super simple and easy to interpret—perfect for small numbers of attributes.

3. Weighted Matching Similarity #

If some attributes matter more than others (e.g., major is more important than gender), assign weights to each attribute. For example:

• major: 0.4, gender: 0.1, race: 0.175, city: 0.175, county: 0.175
• Record 1 and 2 match on race, city, county → total score: 0.175 + 0.175 + 0.175 = 0.525
  This lets you prioritize attributes based on your business needs.

Final Recommendation #

• If your categories have few values: Stick with one-hot encoding + cosine similarity (or normalized Hamming for simplicity)
• If you have high-cardinality categories: Try embedding encoding
• If you care most about overlapping attributes: Jaccard similarity is a great fit

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