Data Cleaning Pipeline Prompt

Description

## Data Cleaning Pipeline

Clean and prepare [DATASET_NAME] for analysis:

**Dataset:** [DATASET_NAME]
**Issues Identified:** [ISSUES — missing values/duplicates/inconsistencies/outliers]
**Target Use:** [USE — ML model/reporting/analytics/visualization]

**Cleaning Pipeline:**

### Phase 1: Initial Assessment
```python
import pandas as pd
import numpy as np

df = pd.read_csv('[FILE]')

# Data quality report
def data_quality_report(df):
    report = pd.DataFrame({
        'dtype': df.dtypes,
        'missing': df.isnull().sum(),
        'missing_pct': (df.isnull().sum() / len(df) * 100).round(2),
        'unique': df.nunique(),
        'sample': df.iloc[0]
    })
    return report

print(data_quality_report(df))
```

### Phase 2: Handle Missing Values
- **Numeric columns:** [STRATEGY]
  - < 5% missing → Impute with median
  - 5-30% missing → ML imputation (KNN/MICE)
  - > 30% missing → Consider dropping column
- **Categorical columns:**
  - Low cardinality → Mode imputation
  - High cardinality → 'Unknown' category
- **Date columns:** Forward fill or interpolate

### Phase 3: Remove Duplicates
```python
# Check for duplicates
dupes = df.duplicated(subset=[KEY_COLUMNS], keep='first')
print(f"Duplicates found: {dupes.sum()}")
df = df.drop_duplicates(subset=[KEY_COLUMNS], keep='first')
```

### Phase 4: Fix Data Types
- Dates: Parse with pd.to_datetime([COL], format='[FORMAT]')
- Categories: Convert string columns with limited unique values
- Numerics: Remove currency symbols, commas, convert to float
- Booleans: Standardize Yes/No/True/False/1/0

### Phase 5: Standardize Values
- Text: lowercase, strip whitespace, remove special characters
- Categories: Map variants to canonical values
- Addresses: Standardize format
- Phone numbers: Standard format

### Phase 6: Handle Outliers
- Method: [METHOD — IQR/Z-score/domain knowledge]
- Action: [ACTION — cap/remove/flag/investigate]

### Phase 7: Validation
- Assert no nulls in required columns
- Assert correct data types
- Assert value ranges
- Cross-column consistency checks

**Variables:** Replace [DATASET_NAME], [ISSUES], [USE], [FILE].

Free to copy and use. Compatible with Claude 4 Opus, Gemini 2.5 Pro.

Usage Instructions

Tags

Compatible AI Models

Version History

Related Prompts