Clean M&E Data with AI

You are a senior M&E data specialist. I need to clean a dataset from a household survey. Start by generating validation rules. Based on the survey variables I describe, create a validation rules table with columns: - Variable name - Valid range or values - Rule type (range check, consistency check, skip logic check, duplicate check, completeness check) - Error severity (critical, warning, minor) - Suggested action when violated (flag for review, auto-correct, delete record) Include rules for: - Numeric ranges (age, household size, income) - Categorical values (only valid response options) - Skip logic consistency (if Q5 = "No", Q6 should be blank) - Cross-variable consistency (e.g., age of household head > age of children) - Completeness (required fields that should not be blank) - Duplicates (same respondent ID, same GPS coordinates within X meters) Here are my survey variables: [Describe your variables, or paste the variable list from your questionnaire]

Based on the validation rules, generate error detection queries. For each rule, produce: 1. **Query logic**: A plain-language description of what to check (e.g., "Find all records where age < 0 or age > 120") 2. **Implementation**: The query written for a common tool. Provide versions for: - Excel formula (for small datasets) - Python/pandas (for larger datasets) 3. **Expected error rate**: For each rule type, what error rate is typical? (e.g., "Range violations are typically 1-3% in well-supervised data collection; above 5% suggests systematic problems") Then suggest an error detection summary template: - Total records - Records with at least one error - Error rate by rule type - Variables with highest error rates - Records flagged for deletion vs. correction vs. review

Create a data correction protocol. For each error type found, define: 1. **Decision tree**: A flowchart or decision table for how to handle the error: - Can it be corrected from other data in the record? (e.g., GPS coordinates from village name) - Can it be corrected from the original paper form or device log? - Should it be set to missing? - Should the entire record be deleted? 2. **Correction rules**: Specific rules for common corrections: - How to handle outliers (Winsorize, cap, delete, or keep with flag?) - How to handle missing data (listwise deletion, imputation, or keep with flag?) - How to handle duplicate records (keep first, keep most complete, merge, or delete both?) 3. **Never auto-correct list**: Variables or error types where correction must always involve human review (e.g., income data, sensitive questions, key outcome indicators). 4. **Correction documentation**: For every correction made, what must be logged? (record ID, variable, original value, corrected value, reason, who made the correction)

Create a data cleaning documentation package: 1. **Cleaning audit log template**: A table with columns: - Record ID - Variable - Original value - Corrected value (or "set to missing" or "record deleted") - Reason for correction - Rule applied (reference to validation rule number) - Date corrected - Corrected by 2. **Cleaning summary report template** (to include in evaluation reports): - Total records received - Total records after cleaning - Records deleted and why - Variables with highest correction rates - Overall data quality assessment (good, acceptable, or concerning, with criteria) - Impact on analysis (does the cleaning materially change any indicator values?) 3. **Data quality flag variables**: Suggest 2-3 new variables to add to the clean dataset that flag data quality at the record level (e.g., "quality_flag: 0 = no issues, 1 = minor corrections, 2 = major corrections, 3 = flagged for cautious interpretation"). 4. **Version control**: Naming convention for raw vs. cleaned datasets (e.g., "dataset_raw_20260401.csv" vs. "dataset_clean_v1_20260410.csv").