xlsxwriter生成Excel失败及商品信息比对致Spyder崩溃求助

Let’s dive straight into fixing your slow execution and crash issues while addressing both of your core requirements. The root problem here is almost certainly inefficient pairwise comparisons (the dreaded O(n²) loop that chokes large datasets) or not leveraging optimized libraries for heavy lifting. Here’s how to streamline everything:

1. Efficiently Group Items with Similar Descriptions #

Instead of comparing every single pair of descriptions (which will tank performance for even moderately sized datasets), use text vectorization and approximate nearest neighbors to find matches in O(n log n) time.

Step-by-Step Implementation: #

• Clean your text: Strip out noise like punctuation, stopwords, and normalize case/word forms to make similarity checks more accurate.
• Vectorize descriptions: Convert text into numerical vectors using TF-IDF, which captures the importance of words in each description.
• Find similar items: Use a ball tree algorithm to quickly locate near matches without checking every pair.

Example code snippet:

import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.neighbors import NearestNeighbors
import re
from nltk.corpus import stopwords
from nltk.stem import WordNetLemmatizer

# Load your dataset
df = pd.read_excel("your_input_file.xlsx")

# Text cleaning function
def clean_description(text):
    if pd.isna(text):
        return ""
    # Lowercase and remove non-alphanumeric characters
    text = re.sub(r'[^a-zA-Z0-9\s]', '', text.lower())
    # Remove common stopwords (e.g., "the", "and")
    stop_words = set(stopwords.words('english'))
    words = text.split()
    words = [word for word in words if word not in stop_words]
    # Reduce words to their base form (lemmatization)
    lemmatizer = WordNetLemmatizer()
    words = [lemmatizer.lemmatize(word) for word in words]
    return ' '.join(words)

# Apply cleaning to descriptions
df['cleaned_desc'] = df['descriptions'].apply(clean_description)

# Vectorize cleaned text (limit features to 5000 for speed)
vectorizer = TfidfVectorizer(max_features=5000)
tfidf_matrix = vectorizer.fit_transform(df['cleaned_desc'])

# Set up nearest neighbors search
nn_model = NearestNeighbors(n_neighbors=5, algorithm='ball_tree', metric='cosine')
nn_model.fit(tfidf_matrix)

# Find similar items (adjust threshold based on your needs: lower = stricter similarity)
distance_threshold = 0.3
similar_groups = []
visited_rows = set()

for idx in range(len(df)):
    if idx not in visited_rows:
        # Get all items with similarity above the threshold
        distances, neighbors = nn_model.kneighbors(tfidf_matrix[idx])
        valid_neighbors = [n for d, n in zip(distances[0], neighbors[0]) if d <= distance_threshold]
        # Create a group for these similar items
        group = df.iloc[valid_neighbors].copy()
        group['similarity_group_id'] = f"desc_group_{idx}"
        similar_groups.append(group)
        visited_rows.update(valid_neighbors)

# Combine all similar groups into one dataframe
similar_descriptions_df = pd.concat(similar_groups, ignore_index=True)

2. Flagging Catalog Number Anomalies per Supplier #

First, define what "差异极大" means for your catalog numbers:

• Numeric catalogs: Flag values outside the IQR (Interquartile Range) for each supplier.
• Alphanumeric catalogs: Flag items with length mismatches or prefix/suffix deviations from the supplier’s dominant pattern.

Example for Numeric Catalogs: #

def find_numeric_anomalies(group):
    # Calculate IQR to detect outliers
    q1 = group['Catalog #'].quantile(0.25)
    q3 = group['Catalog #'].quantile(0.75)
    iqr = q3 - q1
    lower_bound = q1 - 1.5 * iqr
    upper_bound = q3 + 1.5 * iqr
    # Return rows outside the valid range
    return group[(group['Catalog #'] < lower_bound) | (group['Catalog #'] > upper_bound)]

# Apply per supplier (buy_line)
anomalous_catalogs_df = df.groupby('buy_line').apply(find_numeric_anomalies).reset_index(drop=True)

Example for Alphanumeric Catalogs: #

def find_alphanumeric_anomalies(group):
    # Get the most common catalog length for the supplier
    length_counts = group['Catalog #'].str.len().value_counts()
    dominant_length = length_counts.idxmax()
    # Flag items that don't match the dominant length
    return group[group['Catalog #'].str.len() != dominant_length]

anomalous_catalogs_df = df.groupby('buy_line').apply(find_alphanumeric_anomalies).reset_index(drop=True)

3. Fixing Performance & Crash Issues #

• Ditch manual loops: Never write code that compares every row to every other row—use vectorized operations or optimized libraries like scikit-learn.
• Chunk large datasets: If your Excel file has 100k+ rows, read it in chunks with pd.read_excel(chunksize=10000) and process each chunk separately.
• Adjust Spyder memory limits: If Spyder crashes mid-run, increase its memory allocation via Tools > Preferences > IPython Console > Graphics or run the script in a standalone Python environment instead.
• Test with a small subset: Validate your logic on a 100-row sample first before running on the full dataset.

4. Save Results to Excel #

Combine both results into a single Excel file with separate sheets:

with pd.ExcelWriter("output_results.xlsx") as writer:
    similar_descriptions_df.to_excel(writer, sheet_name="Similar_Descriptions", index=False)
    anomalous_catalogs_df.to_excel(writer, sheet_name="Anomalous_Catalog_Numbers", index=False)

Quick Tips: #

• Adjust the distance_threshold for descriptions based on your data (test with a small subset to find the right balance).
• For alphanumeric catalogs, you can add regex checks to validate prefixes/suffixes if length alone isn’t enough.
• Don’t forget to install required packages: pip install pandas scikit-learn nltk (and run nltk.download('stopwords') and nltk.download('wordnet') once to get NLTK resources).

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