Data Cleaner

# Data Cleaning Skill

This skill helps analyze and clean datasets by detecting data types, identifying quality issues, and suggesting or applying corrections.

## Core Capabilities

1. **Sample Rows**: View random sample of rows from a dataset using pandas.sample()
2. **Column Analysis**: List all columns with basic statistics and sample values
3. **Type Detection**: Automatically detect if columns are:
   - Numeric (integer, float)
   - Categorical (limited unique values)
   - Ordinal (ordered categories)
   - Text (free-form text)
   - DateTime
   - Boolean
4. **Data Quality Reports**: Comprehensive quality analysis with severity levels and completeness scores
5. **Value Mapping Generation**: Auto-generate standardization functions for categorical data
6. **Value Cleaning**: Fix common issues like extra whitespace, inconsistent casing, invalid values
7. **Validation Reports**: Compare before/after cleaning to verify transformations

## Instructions

When the user requests data cleaning assistance:

1. **Identify the dataset**: Ask for the file path if not provided
2. **Generate quality report**: Use `scripts/data_quality_report.py` for comprehensive quality analysis
3. **Analyze columns**: Use `scripts/analyze_columns.py` to get an overview of all columns
4. **Detect types**: Use `scripts/detect_types.py` to determine the data type of each column
5. **Generate value mappings**: Use `scripts/value_mapping_generator.py` for categorical columns needing standardization
6. **Present findings**: Show the user:
   - Data quality grade and issues
   - Column names and detected types
   - Suggested value mappings
   - Sample problematic values
7. **Suggest fixes**: Recommend cleaning strategies based on issues found
8. **Apply cleaning**: If user approves, use `scripts/clean_values.py` to fix issues
9. **Validate results**: Use `scripts/validation_report.py` to compare before/after and confirm changes

## Supported File Formats

All scripts support multiple file formats with automatic detection:
- **CSV** (.csv) - Comma-separated values
- **Excel** (.xlsx, .xls) - Microsoft Excel files
- **JSON** (.json) - JSON arrays or objects
- **JSONL** (.jsonl) - JSON Lines format (one JSON object per line)

File format is auto-detected from the extension. You can also explicitly specify the format using the `--format` parameter.

## Using the Python Scripts

All scripts should be run using `uv` for fast, dependency-managed execution:

```bash
uv run python scripts/<script_name>.py [arguments]
```

Or use `uvx` to run scripts with automatic dependency installation:

```bash
uvx --from . python scripts/<script_name>.py [arguments]
```

**Note**: The first time you run a script, `uv` will automatically install the required dependencies (pandas, numpy, openpyxl) in an isolated environment. Subsequent runs will be much faster.

### sample_rows.py
View a random sample of rows from a dataset.

**Usage**:
```bash
uv run python scripts/sample_rows.py <file_path> [--n 10] [--format csv|excel|json|jsonl] [--output table|json|csv] [--seed SEED]
```

**Options**:
- `--n`: Number of rows to sample (default: 10)
- `--format`: Input file format - csv, excel, json, or jsonl (auto-detected if not specified)
- `--output`: Output format - table (human-readable), json, or csv (default: table)
- `--seed`: Random seed for reproducibility (optional)

**Output**: Random sample of rows in the specified format

**Examples**:
```bash
# Sample 5 random rows from CSV
python scripts/sample_rows.py data.csv --n 5

# Sample from JSON Lines file
python scripts/sample_rows.py data.jsonl --n 10

# Sample with reproducible results
python scripts/sample_rows.py data.csv --n 10 --seed 42

# Output as JSON
python scripts/sample_rows.py data.xlsx --n 20 --output json
```

### analyze_columns.py
Analyzes all columns in a dataset and provides summary statistics.

**Usage**:
```bash
uv run python scripts/analyze_columns.py <file_path> [--format csv|excel|json|jsonl]
```

**Output**: JSON with column names, types, null counts, unique counts, and sample values

**Examples**:
```bash
# Analyze CSV file
python scripts/analyze_columns.py customers.csv

# Analyze JSONL file
python scripts/analyze_columns.py events.jsonl
```

### detect_types.py
Detects the semantic type of each column (numeric, categorical, ordinal, text, datetime).

**Usage**:
```bash
uv run python scripts/detect_types.py <file_path> [--format csv|excel|json|jsonl]
```

**Output**: JSON mapping columns to detected types with confidence scores

**Examples**:
```bash
# Detect types in CSV
python scripts/detect_types.py data.csv

# Detect types in JSON file
python scripts/detect_types.py data.json
```

### clean_values.py
Cleans specific columns based on detected issues.

**Usage**:
```bash
uv run python scripts/clean_values.py <input_file> <output_file> [--operations json_string] [--input-format csv|excel|json|jsonl] [--output-format csv|excel|json|jsonl]
```

**Options**:
- `--operations`: JSON string defining cleaning operations
- `--input-format`: Input file format (auto-detected if not specified)
- `--output-format`: Output file format (auto-detected if not specified)

**Operations JSON format**:
```json
{
  "column_name": {
    "operation": "trim|lowercase|uppercase|remove_special|fill_missing|convert_type",
    "params": {}
  }
}
```

**Examples**:
```bash
# Clean CSV file and output as CSV
python scripts/clean_values.py data.csv cleaned.csv --operations '{"name":{"operation":"trim"}}'

# Clean JSONL file and convert to JSON
python scripts/clean_values.py logs.jsonl cleaned.json --input-format jsonl --output-format json --operations '{"status":{"operation":"lowercase"}}'

# Clean JSON and output as JSONL
python scripts/clean_values.py data.json output.jsonl --output-format jsonl
```

### data_quality_report.py
Generates a comprehensive data quality report with severity levels and completeness scores.

**Usage**:
```bash
uv run python scripts/data_quality_report.py <file_path> [--format csv|excel|json|jsonl] [--output report.json]
```

**Output**: JSON report with:
- Overall quality grade (A-F)
- Per-column completeness scores
- Missing values analysis
- Formatting issues
- Outliers detection
- Data type consistency checks

**Examples**:
```bash
# Generate quality report for CSV
python scripts/data_quality_report.py data.csv --output report.json

# Generate report for JSONL file
python scripts/data_quality_report.py logs.jsonl
```

### value_mapping_generator.py
Auto-generates standardization mappings and Python functions for categorical columns.

**Usage**:
```bash
uv run python scripts/value_mapping_generator.py <file_path> [--column COLUMN] [--threshold 20] [--format csv|excel|json|jsonl] [--output-functions functions.py]
```

**Output**: JSON with:
- Suggested value mappings
- Groups of similar values
- Auto-generated Python standardization functions
- Before/after value counts

**Options**:
- `--column`: Analyze specific column only
- `--threshold`: Max unique values to consider categorical (default: 20)
- `--format`: File format - csv, excel, json, or jsonl (auto-detected if not specified)
- `--output-functions`: Write Python functions to file

**Examples**:
```bash
# Generate mappings for all categorical columns
python scripts/value_mapping_generator.py survey.csv

# Generate mappings for specific column in JSONL file
python scripts/value_mapping_generator.py events.jsonl --column user_type
```

### validation_report.py
Compares original and cleaned datasets to validate transformations.

**Usage**:
```bash
uv run python scripts/validation_report.py <original_file> <cleaned_file> [--format csv|excel|json|jsonl] [--output validation.json]
```

**Output**: JSON report with:
- Transformation examples for each column
- Data loss analysis
- Before/after distribution comparisons
- Validation status (pass/review_needed)
- Recommendations

**Examples**:
```bash
# Validate CSV cleaning
python scripts/validation_report.py original.csv cleaned.csv

# Validate JSONL cleaning
python scripts/validation_report.py original.jsonl cleaned.jsonl --output validation.json
```

## Workflow Examples

### Basic Workflow
1. User: "I need to clean my customer data"
2. Get file path from user
3. Run `sample_rows.py` to show user a preview of their data
4. Run `data_quality_report.py` to assess overall quality
5. Run `analyze_columns.py` to see all columns
6. Run `detect_types.py` to determine types
7. Present findings and ask user which columns to clean
8. Run `clean_values.py` with appropriate operations
9. Run `validation_report.py` to verify changes
10. Confirm cleaning completed and show summary

### Advanced Workflow (with auto-generated functions)
1. User: "Generate cleaning functions for my survey data"
2. Run `data_quality_report.py` for quality overview
3. Run `value_mapping_generator.py` for categorical columns
4. Show user the generated standardization functions
5. User can copy functions into their own cleaning script
6. Apply cleaning using the generated functions
7. Validate with `validation_report.py`

## Best Practices

- Always show sample values before suggesting changes
- Explain why certain types were detected
- Ask for confirmation before modifying data
- Create backups or save to new files when cleaning
- Support all file formats: CSV, Excel, JSON, and JSONL
- For JSON/JSONL files, pandas expects records-oriented format (list of objects)
- JSONL format is ideal for streaming or large datasets (one JSON object per line)
- You can convert between formats using clean_values.py with `--input-format` and `--output-format`
- Provide clear summaries of changes made