Data Cleaning and Preprocessing

import pandas as pd
import numpy as np
 
# Load data
DATA_PATH = '/data/'
 
orders = pd.read_csv(DATA_PATH + 'src_orders.csv', parse_dates=['created_at'])
order_items = pd.read_csv(DATA_PATH + 'src_order_items.csv')
products = pd.read_csv(DATA_PATH + 'src_products.csv')
users = pd.read_csv(DATA_PATH + 'src_users.csv')
 
print(f"Data loaded!")
print(f"   - orders: {len(orders):,} rows")
print(f"   - order_items: {len(order_items):,} rows")

import pandas as pd
import numpy as np
 
# Sample data
df_sample = pd.DataFrame({
    'user_id': [1, 2, 3, 4, 5],
    'name': ['John', None, 'Mike', 'Sarah', 'Emily'],
    'age': [25, 30, np.nan, 28, 35],
    'city': ['New York', 'Boston', 'Chicago', None, 'New York']
})
 
# 1. Check missing value count
print("Missing value count by column:")
print(df_sample.isnull().sum())
 
# 2. Check missing value ratio
print("\nMissing value ratio (%):")
print((df_sample.isnull().sum() / len(df_sample) * 100).round(2))
 
# 3. Check overall info
df_sample.info()

Missing value count by column:
user_id    0
name       1
age        1
city       1
dtype: int64

Missing value ratio (%):
user_id     0.0
name       20.0
age        20.0
city       20.0
dtype: float64

# Show only columns with missing values
missing = df_sample.isnull().sum()
missing_pct = (missing / len(df_sample) * 100).round(2)
 
missing_df = pd.DataFrame({
    'missing_count': missing,
    'missing_ratio(%)': missing_pct
})
 
# Filter only columns with missing values
missing_df = missing_df[missing_df['missing_count'] > 0]
missing_df = missing_df.sort_values('missing_count', ascending=False)
 
if len(missing_df) > 0:
    print(missing_df)
else:
    print("No missing values!")

# Recreate sample data
df_test = pd.DataFrame({
    'user_id': [1, 2, 3, 4, 5],
    'name': ['John', None, 'Mike', 'Sarah', 'Emily'],
    'age': [25, 30, np.nan, 28, 35],
    'city': ['New York', 'Boston', 'Chicago', None, 'New York']
})
 
# Delete rows with missing values
df_dropped = df_test.dropna()
print(f"Before: {len(df_test)} rows -> After: {len(df_dropped)} rows")
 
# Delete rows with missing values only in specific columns
df_dropped_name = df_test.dropna(subset=['name'])
print(f"After deleting based on name: {len(df_dropped_name)} rows")
 
# Delete only rows where all values are missing
df_dropped_all = df_test.dropna(how='all')
print(f"After how='all': {len(df_dropped_all)} rows")

# Recreate sample data
df_fill = pd.DataFrame({
    'user_id': [1, 2, 3, 4, 5],
    'name': ['John', None, 'Mike', 'Sarah', 'Emily'],
    'age': [25, 30, np.nan, 28, 35],
    'city': ['New York', 'Boston', 'Chicago', None, 'New York'],
    'value': [100, np.nan, 150, np.nan, 200]
})
 
# 1. Replace with specific value
df_fill['city'] = df_fill['city'].fillna('Unknown')
print("After city replacement:")
print(df_fill['city'].tolist())
 
# 2. Replace with mean (numeric)
df_fill['age'] = df_fill['age'].fillna(df_fill['age'].mean())
print(f"\nAfter age mean replacement: {df_fill['age'].tolist()}")
 
# 3. Forward fill (fill with previous value)
df_fill['value'] = df_fill['value'].ffill()
print(f"After value ffill: {df_fill['value'].tolist()}")
 
# 4. Backward fill (fill with next value)
df_test2 = pd.DataFrame({'v': [np.nan, 10, np.nan, 30, np.nan]})
df_test2['v'] = df_test2['v'].bfill()
print(f"bfill result: {df_test2['v'].tolist()}")

def handle_missing_values(df):
    """Auto-handle missing values based on data type"""
    df_clean = df.copy()
 
    # Numeric columns: replace with median
    numeric_cols = df_clean.select_dtypes(include=[np.number]).columns
    for col in numeric_cols:
        if df_clean[col].isnull().sum() > 0:
            median_val = df_clean[col].median()
            df_clean[col] = df_clean[col].fillna(median_val)
            print(f"[OK] {col}: replaced with median ({median_val:.2f})")
 
    # Text columns: replace with 'Unknown'
    object_cols = df_clean.select_dtypes(include=['object']).columns
    for col in object_cols:
        if df_clean[col].isnull().sum() > 0:
            df_clean[col] = df_clean[col].fillna('Unknown')
            print(f"[OK] {col}: replaced with 'Unknown'")
 
    return df_clean
 
# Apply (using orders data)
print("Missing value status:")
print(orders.isnull().sum()[orders.isnull().sum() > 0])
print()
 
orders_clean = handle_missing_values(orders)
print(f"\nMissing values after processing: {orders_clean.isnull().sum().sum()}")

# Check duplicates in order_items
print("=== order_items Duplicate Check ===")
 
# 1. Check completely identical rows
n_duplicates = order_items.duplicated().sum()
print(f"Duplicate row count: {n_duplicates:,}")
 
# 2. View duplicated rows (first 5)
print("\nDuplicated rows (sample):")
duplicates = order_items[order_items.duplicated(keep=False)]
print(duplicates.head())
 
# 3. Remove duplicates (keep first row)
order_items_clean = order_items.drop_duplicates(keep='first')
print(f"\nBefore removal: {len(order_items):,} rows")
print(f"After removal: {len(order_items_clean):,} rows")

# Detailed duplicate data analysis
def analyze_duplicates(df, subset=None):
    """Detailed duplicate data analysis"""
    print("Duplicate Data Analysis")
    print("=" * 60)
 
    # Total duplicates
    n_dup = df.duplicated(subset=subset).sum()
    pct_dup = (n_dup / len(df) * 100)
 
    print(f"Total rows: {len(df):,}")
    print(f"Duplicated rows: {n_dup:,} ({pct_dup:.2f}%)")
 
    # Removal simulation
    df_dedup = df.drop_duplicates(subset=subset, keep='first')
    n_after = len(df_dedup)
 
    print(f"After removal: {n_after:,}")
    print(f"Rows to be removed: {len(df) - n_after:,}")
 
    return df_dedup
 
# Execute
order_items_clean = analyze_duplicates(order_items)

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
 
def detect_outliers_iqr(df, column):
    """Detect outliers using IQR method"""
    print(f"Outlier Detection for {column} (IQR Method)")
    print("=" * 60)
 
    # Calculate IQR
    Q1 = df[column].quantile(0.25)
    Q3 = df[column].quantile(0.75)
    IQR = Q3 - Q1
 
    # Outlier bounds
    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR
 
    print(f"Q1 (25th percentile): {Q1:,.2f}")
    print(f"Q3 (75th percentile): {Q3:,.2f}")
    print(f"IQR: {IQR:,.2f}")
    print(f"Lower bound: {lower_bound:,.2f}")
    print(f"Upper bound: {upper_bound:,.2f}")
 
    # Find outliers
    outliers = df[(df[column] < lower_bound) | (df[column] > upper_bound)]
    n_outliers = len(outliers)
 
    print(f"\nOutlier count: {n_outliers:,} ({n_outliers/len(df)*100:.2f}%)")
 
    return outliers, lower_bound, upper_bound
 
# Execute (using sale_price column from order_items)
outliers, lower_bound, upper_bound = detect_outliers_iqr(order_items, 'sale_price')

# Create test data with outliers
np.random.seed(42)
test_prices = pd.DataFrame({
    'sale_price': np.concatenate([
        np.random.normal(100, 30, 9500),  # Normal data
        np.random.uniform(300, 500, 300),  # High outliers
        np.random.uniform(-50, 0, 200)     # Low outliers (negative prices)
    ])
})
 
outliers, lower_bound, upper_bound = detect_outliers_iqr(test_prices, 'sale_price')
print(f"\nOutlier sample (top 5):")
print(outliers.nlargest(5, 'sale_price'))

def visualize_outliers(df, column):
    """Visualize outliers with boxplot and histogram"""
    fig, axes = plt.subplots(1, 2, figsize=(14, 5))
 
    # Calculate IQR
    Q1 = df[column].quantile(0.25)
    Q3 = df[column].quantile(0.75)
    IQR = Q3 - Q1
    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR
 
    # Boxplot
    axes[0].boxplot(df[column].dropna(), vert=False)
    axes[0].set_xlabel(column)
    axes[0].set_title(f'{column} Boxplot')
 
    # Histogram
    axes[1].hist(df[column].dropna(), bins=50, alpha=0.7, edgecolor='black')
    axes[1].axvline(lower_bound, color='red', linestyle='--', label=f'Lower: {lower_bound:.0f}')
    axes[1].axvline(upper_bound, color='red', linestyle='--', label=f'Upper: {upper_bound:.0f}')
    axes[1].set_xlabel(column)
    axes[1].set_ylabel('Frequency')
    axes[1].set_title(f'{column} Histogram')
    axes[1].legend()
 
    plt.tight_layout()
    plt.show()
 
visualize_outliers(test_prices, 'sale_price')

# Outlier handling example (using test_prices data)
df_outlier = test_prices.copy()
 
# Calculate bounds
Q1 = df_outlier['sale_price'].quantile(0.25)
Q3 = df_outlier['sale_price'].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
 
print(f"Outlier bounds: {lower_bound:.2f} ~ {upper_bound:.2f}")
 
# 1. Remove outliers
df_removed = df_outlier[(df_outlier['sale_price'] >= lower_bound) &
                        (df_outlier['sale_price'] <= upper_bound)]
print(f"\n1. Remove outliers: {len(df_outlier):,} rows -> {len(df_removed):,} rows")
 
# 2. Replace outliers with bounds (Winsorizing)
df_capped = df_outlier.copy()
df_capped['sale_price_capped'] = df_capped['sale_price'].clip(lower=lower_bound, upper=upper_bound)
print(f"2. Winsorizing applied")
print(f"   Original max: {df_outlier['sale_price'].max():.2f}")
print(f"   After clipping max: {df_capped['sale_price_capped'].max():.2f}")
 
# 3. Replace outliers with NaN then median
df_replaced = df_outlier.copy()
mask = (df_replaced['sale_price'] < lower_bound) | (df_replaced['sale_price'] > upper_bound)
median_val = df_replaced.loc[~mask, 'sale_price'].median()
df_replaced.loc[mask, 'sale_price'] = median_val
print(f"3. Median replacement: {mask.sum()} outliers replaced with {median_val:.2f}")

import pandas as pd
import numpy as np
 
def clean_data_pipeline(df, verbose=True):
    """Data cleaning pipeline"""
 
    df_clean = df.copy()
 
    if verbose:
        print("Data Cleaning Pipeline Started")
        print("=" * 60)
        print(f"Original data: {df_clean.shape[0]:,} rows x {df_clean.shape[1]} columns")
 
    # 1. Remove duplicates
    before = len(df_clean)
    df_clean = df_clean.drop_duplicates()
    after = len(df_clean)
    if verbose:
        print(f"\n[1] Duplicate removal: {before - after:,} rows removed")
 
    # 2. Handle missing values
    missing_before = df_clean.isnull().sum().sum()
 
    # Numeric -> median
    numeric_cols = df_clean.select_dtypes(include=[np.number]).columns
    for col in numeric_cols:
        df_clean[col] = df_clean[col].fillna(df_clean[col].median())
 
    # Text -> 'Unknown'
    object_cols = df_clean.select_dtypes(include=['object']).columns
    for col in object_cols:
        df_clean[col] = df_clean[col].fillna('Unknown')
 
    missing_after = df_clean.isnull().sum().sum()
    if verbose:
        print(f"[2] Missing value handling: {missing_before - missing_after:,} handled")
 
    # 3. Data type optimization
    for col in object_cols:
        if df_clean[col].nunique() / len(df_clean) < 0.5:
            df_clean[col] = df_clean[col].astype('category')
 
    if verbose:
        print("[3] Data type optimization completed")
        print(f"\nCleaning complete: {df_clean.shape[0]:,} rows x {df_clean.shape[1]} columns")
 
    return df_clean
 
# Execute (using orders data)
orders_clean = clean_data_pipeline(orders)