letitbk/cursor-setup/datacheck

Data Check

Inspect and validate a data file before analysis. Report findings with suggested fixes. Do not auto-fix.

When to Use

• Before any analysis or visualization on a new data file
• When parsing errors or unexpected values appear
• When switching to a different dataset mid-session
• User says "check", "inspect", "what's in this", "profile" a data file

When NOT to Use

• File has already been checked in this session and nothing changed
• User just wants to read a specific value (use Read tool directly)

Workflow

Step 1: Raw File Inspection

Before loading into Python, check the raw file:

file <filename>                          # encoding detection
head -c 500 <filename> | cat -v          # hidden chars: ^M (CR), BOM, non-UTF8
wc -l <filename>                         # row count sanity check
head -3 <filename>                       # peek at delimiter and header

Flag these issues:

Step 2: Load and Summarize

For CSV/TSV files:

import pandas as pd
df = pd.read_csv("file.csv")  # or sep='\t' for TSV

For Excel files:

import pandas as pd
xls = pd.ExcelFile("file.xlsx")
print(f"Sheets: {xls.sheet_names}")
df = pd.read_excel("file.xlsx", sheet_name=0)  # or specific sheet name

For JSON files:

df = pd.read_json("file.json")

Report this table:

Then per column:

Column          Type        Missing   Unique   Example Values
─────────────────────────────────────────────────────────────
revenue         float64     12 (2%)   845      19.99, 49.50, 129.00
category        object      0 (0%)    8        Electronics, Clothing, Home
order_date      datetime    3 (0.5%)  365      2024-01-15, 2024-02-20

Step 3: Excel-Specific Checks

When the file is .xlsx or .xls, also report:

1. Sheet names: List all sheets and row counts per sheet
2. Merged cells: Flag if any exist (they break pandas parsing)
3. Header row position: Check if header is row 0 or if there are title rows above
4. Formula columns: Columns that had formulas (now resolved to values)
5. Named ranges: If any relevant data is in named ranges

Step 4: Data Quality Checks

Scan ALL columns for common issues:

Report as:

DATA QUALITY ISSUES:
  revenue: 15 values are "$X,XXX.XX" strings. Needs: df['revenue'] = df['revenue'].str.replace('[$,]', '', regex=True).astype(float)
  status: contains {'N/A', '-', ''} (n=23). Likely missing values.
  order_date: mixed formats detected (2024-01-15 and 01/15/2024). Needs standardization.

Step 5: Light Profiling

After the structural check:

1. Numeric columns: Basic stats (mean, median, min, max, std) + correlation matrix (top 10 strongest pairs)
2. Categorical columns: Value counts (top 10 levels per column, flag if >50 unique levels)
3. Flag: Numeric columns stored as object (likely parsing issue), zero-variance columns, constant columns

# Quick profiling
print(df.describe(include='all'))
print(df.nunique())
# Correlation matrix for numeric columns
numeric_cols = df.select_dtypes(include='number')
if len(numeric_cols.columns) > 1:
    corr = numeric_cols.corr()
    # Show top correlated pairs

Step 6: Report Summary

Present a concise summary with three sections:

Clean: Columns ready for analysis (no issues)

Needs attention: Columns with data quality issues. Include suggested fix code but do NOT execute.

# SUGGESTED FIX for revenue currency strings:
df['revenue'] = df['revenue'].str.replace('[$,]', '', regex=True).astype(float)
# SUGGESTED FIX for placeholder missings:
df['status'] = df['status'].replace({'N/A': pd.NA, '-': pd.NA, '': pd.NA})

Notes: Observations about the data structure (time series detected, panel structure, likely join keys, etc.)

Quick Reference

Common Mistakes