如何对比同结构DataFrame customer_watched与future_programs的标题列相似度？

Hey there! Let's work through this problem together. You're looking to compare title similarity between two DataFrames—customer_watched (users' watched programs) and future_programs (upcoming shows)—and you hit a snag trying to generate their Cartesian product. Here are a few practical, working solutions to make this happen:

Method 1: Generate Cartesian Product with Pandas Merge (Then Calculate Similarity) #

The most straightforward way to get all title pairs is to use a temporary key to perform a full outer join. This creates every possible combination of watched and future titles, which you can then score for similarity.

import pandas as pd
from fuzzywuzzy import fuzz  # Use fuzzywuzzy for easy string matching (install via pip first)

# Add a temporary key column to both DataFrames
customer_watched['tmp_key'] = 1
future_programs['tmp_key'] = 1

# Merge on the temp key to get the Cartesian product
title_combinations = pd.merge(customer_watched, future_programs, on='tmp_key').drop('tmp_key', axis=1)

# Calculate similarity score for each title pair (using fuzzy ratio here)
title_combinations['similarity_score'] = title_combinations.apply(
    lambda row: fuzz.ratio(row['title_x'], row['title_y']), axis=1
)

# Optional: Filter results to only high-similarity pairs (e.g., score >= 80)
high_similarity_pairs = title_combinations[title_combinations['similarity_score'] >= 80]

Note: #

If you didn't get the Cartesian product working before, it's likely because you skipped the temporary key step—this is the trick to forcing pandas to pair every row from the first DF with every row from the second.

Method 2: Iterate Through Title Pairs with itertools.product #

For smaller datasets, this approach is more intuitive and avoids modifying your original DataFrames. It directly loops through all possible title combinations:

import itertools
from fuzzywuzzy import fuzz

# Extract title lists from both DataFrames
watched_titles = customer_watched['title'].tolist()
future_titles = future_programs['title'].tolist()

# Calculate similarity for every pair
similarity_results = []
for watched_title, future_title in itertools.product(watched_titles, future_titles):
    score = fuzz.ratio(watched_title, future_title)
    similarity_results.append({
        'watched_title': watched_title,
        'future_title': future_title,
        'similarity_score': score
    })

# Convert results to a DataFrame for easy analysis
results_df = pd.DataFrame(similarity_results)

Method 3: Vectorized Similarity for Large Datasets #

If you're working with a lot of titles, vectorized operations (like TF-IDF + cosine similarity) are way faster than loops. This uses scikit-learn to compute similarity at scale:

from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity
import pandas as pd

# Combine all titles to build a shared TF-IDF vocabulary
all_titles = pd.concat([customer_watched['title'], future_programs['title']]).tolist()

# Initialize TF-IDF vectorizer (remove stopwords for better accuracy)
vectorizer = TfidfVectorizer(stop_words='english')
tfidf_matrix = vectorizer.fit_transform(all_titles)

# Split the matrix into watched and future title vectors
num_watched = len(customer_watched)
watched_vectors = tfidf_matrix[:num_watched]
future_vectors = tfidf_matrix[num_watched:]

# Compute cosine similarity matrix
similarity_matrix = cosine_similarity(watched_vectors, future_vectors)

# Convert to a readable DataFrame
similarity_df = pd.DataFrame(
    similarity_matrix,
    index=customer_watched['title'],
    columns=future_programs['title']
)

Pro Tips for Better Results #

• Clean Your Titles First: Normalize text to reduce false mismatches. For example:

  def clean_title(title):
      return title.lower().replace(r'[^a-zA-Z0-9 ]', '').strip()
  
  customer_watched['clean_title'] = customer_watched['title'].apply(clean_title)
  future_programs['clean_title'] = future_programs['title'].apply(clean_title)
  

• Pick the Right Similarity Metric: Use fuzz.partial_ratio if titles have extra details (like "Season 2"), or cosine similarity for longer, more descriptive titles.

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