聚类交易数据并移除1.5*Interquartile range外数据点的技术请求

Hey Brian, let's walk through how to handle your 15M-row trade dataset efficiently. We'll use Python's pandas (and scikit-learn for clustering) since they're optimized for large datasets—no need to worry about performance here.

Step 1: Load and Filter the Dataset #

First, we'll load your data and filter down to rows where Size falls between 1 and 100000. We'll also optimize memory usage to handle the large volume smoothly:

import pandas as pd

# Load the data with optimized dtypes to save memory
df = pd.read_csv(
    'mydata_tsample.txt',
    sep='\s+',  # Handles whitespace-separated values
    names=['Size', 'TradingCost'],
    dtype={'Size': 'int32', 'TradingCost': 'float32'}  # Reduce memory footprint
)

# Filter rows where Size is in [1, 100000]
filtered_df = df[(df['Size'] >= 1) & (df['Size'] <= 100000)].copy()

Step 2: Sort or Cluster the Filtered Data #

You mentioned either clustering or sorting—let's cover both options:

Option A: Sort by Size

This is straightforward and great if you just want ordered data:

# Sort the filtered data by Size (ascending; set ascending=False for descending)
sorted_df = filtered_df.sort_values(by='Size', ascending=True)

Option B: Cluster the Data

If you want to group similar Size values into clusters, we can use KMeans from scikit-learn. Let's pick 5 clusters as an example (adjust the number based on your needs):

from sklearn.cluster import KMeans

# Reshape the Size column for scikit-learn (expects 2D input)
X = filtered_df[['Size']].values

# Initialize and fit KMeans
kmeans = KMeans(n_clusters=5, random_state=42)  # random_state ensures reproducibility
filtered_df['Cluster'] = kmeans.fit_predict(X)

# Optional: Sort within each cluster by Size for cleaner organization
clustered_sorted_df = filtered_df.sort_values(by=['Cluster', 'Size'])

Step 3: Remove Outliers with the 1.5*IQR Rule #

Now we'll eliminate any data points where TradingCost falls outside 1.5 times the interquartile range (IQR) of the filtered dataset:

# Calculate IQR for TradingCost
q1 = filtered_df['TradingCost'].quantile(0.25)
q3 = filtered_df['TradingCost'].quantile(0.75)
iqr = q3 - q1

# Define outlier boundaries
lower_bound = q1 - 1.5 * iqr
upper_bound = q3 + 1.5 * iqr

# Filter out outliers
cleaned_df = filtered_df[
    (filtered_df['TradingCost'] >= lower_bound) & 
    (filtered_df['TradingCost'] <= upper_bound)
]

# If you clustered first, replace filtered_df with clustered_sorted_df here

Quick Notes for Large Datasets #

• Memory Efficiency: Using dtype when loading data cuts down on memory usage, which is critical for 15M rows.
• Speed: Pandas operations are vectorized, so they're much faster than looping through rows manually.
• Reproducibility: Setting random_state in KMeans ensures you get the same clusters every time you run the code.

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