dataset-profiler 2.1.0

Data profiling tool for CSV and Excel files with cross-table relationship detection

Data Profiler

A modular data profiling tool for CSV and Excel files that enables quick decisions about dataset compatibility and context priming for synthetic data generation.

Features

• Multi-format support: Profile CSV files and Excel workbooks (each sheet as separate table)
• Cross-table analysis: Automatically detects foreign key relationships and primary key candidates
• Intra-table relationships: Numeric correlations, categorical associations (Cramér's V), and conditional statistics
• Token-efficient output: TXT output by default (compact, ideal for LLM context priming)
• Comprehensive profiling: Type inference, distributions, anomalies, high-cardinality analysis
• Batch processing: Profile all tables in a folder with a single command

Installation

Requires Python 3.9+ and the following dependencies:

pip install pandas openpyxl typing_extensions

Quick Start

# Profile all CSV/Excel files in a folder (TXT output)
python -m data_profiler /path/to/data/folder

# Also generate JSON output
python -m data_profiler /path/to/data/folder --json

# Specify output directory
python -m data_profiler /path/to/data/folder --out-dir ./reports

CLI Options

positional arguments:
  folder                Folder containing CSV and/or Excel files to profile

options:
  --json                Also generate JSON output (default: TXT only)
  --out-dir PATH        Output directory (default: same as input folder)
  --sep SEP             CSV delimiter (default: ,)
  --encoding ENC        CSV encoding (optional)
  --nrows N             Profile only first N rows per file
  --low-memory          Pass low_memory=True to pandas.read_csv
  --date-threshold F    Fraction for date-like detection (default: 0.80)
  --topk N              Top-K examples for distributions (default: 10)
  --max-examples-scan N Max rows to scan for expensive ops (default: 200000)
  --seed N              Random seed for sampling (default: 7)
  --numeric-parse-threshold F
                        Parse-rate threshold for numeric detection (default: 0.90)
  --hc-prefix-len N     Prefix length for high-cardinality analysis (default: 3)
  -v, --verbose         Enable verbose logging

Output Formats

TXT Report (Default)

Compact, token-efficient format ideal for LLM context priming:

PROFILE REPORT
============================================================
folder: /data/retail/store_data
tables: 3
generated: 2026-01-14T10:30:00
runtime: 0.142s

== customers.csv (rows=1000, cols=5) [0.045s]
col | dtype | sem | card | null% | distinct%
------------------------------------------------------------------------
customer_id | object | id-like | low | 0.00% | 100.00%
  hc len(p50/p90/p95/p99)=8/8/8/8
    pref CUS:1000
name | object | text | high | 0.00% | 95.00%
  hc len(p50/p90/p95/p99)=12/18/22/25
email | object | text | high | 0.00% | 100.00%
signup_date | object | date-like | med | 0.50% | 45.00%
  date parse=99.50% range=2022-01-01..2024-12-31
    years 2022:250 2023:400 2024:350
is_active | bool | boolean | low | 0.00% | 40.00%
  num 0..1 mean=0.75 p50=1 p95=1

== orders.csv (rows=5000, cols=6) [0.082s]
...

============================================================
CROSS-TABLE RELATIONSHIPS
============================================================

Primary Key Candidates:
  customers.csv: customer_id
  orders.csv: order_id

Foreign Key Hints:
  orders.csv.customer_id -> customers.csv.customer_id (confidence=0.95, evidence=both)

JSON Report (Optional)

Full detailed output including anomaly analysis. Generated with --json flag.

Semantic Types

The profiler infers one of 9 semantic types for each column:

Type	Description
empty	No rows or all-null column
boolean	Bool dtype or string vocab {true, false, yes, no, 0, 1}
year-like	Integer column with values in year range (1900-2100)
numeric	Numeric dtype or string that parses as numeric
date-like	Datetime dtype or string that parses as datetime
categorical	Low cardinality with low distinct fraction
id-like	Near-unique with low nulls and ID-like pattern (primary key candidates)
fk-like	Numeric column with ID-like name pattern but not near-unique (foreign key candidates)
text	Fallback for unclassified strings

Intra-Table Column Relationships

The profiler analyzes relationships between columns within each table:

Numeric Correlations

• Pearson correlation between numeric columns
• Reports pairs with |r| ≥ 0.5
• Example: OT Hours ~ Total OT Paid (r=0.92)

Categorical Associations (Cramér's V)

• Cramér's V measures association between categorical columns
• Includes high-cardinality columns (up to 500 categories, aggregated to top-20)
• Reports associations with V ≥ 0.3
• Co-occurrence patterns show which values tend to appear together
• Example:

  Agency Name ~ Pay Basis (V=0.71 strong)
    "DEPT OF ED PEDAGOGICAL" -> "per Annum" (100%)
    "POLICE DEPARTMENT" -> "per Annum" (94%)
  

Conditional Statistics

• Numeric column ranges grouped by categorical values
• Reveals constraints like "Base Salary for hourly workers is typically $20-$50"
• Only reports when there's meaningful variation between groups (CV > 10%)

Cross-Table Relationship Detection

The profiler automatically detects:

1. Primary Key Candidates: Columns with ≥99% distinct values and ≤1% nulls
2. Foreign Key Hints: Based on:
   • Column name pattern matching (e.g., customer_id → customers.id)
   • Value overlap analysis (sampled)
   • Type compatibility

Confidence scores (0.0-1.0) indicate relationship strength.

Programmatic Usage

The package provides a clean Python API for integration into your workflows:

from data_profiler import (
    profile_folder,
    profile_file,
    profile_dataframe,
    render_txt,
    render_json,
    ProfilerConfig,
)

# Profile all files in a folder
report = profile_folder("/data/retail")

# Profile with custom configuration
config = ProfilerConfig(
    date_threshold=0.85,
    topk=20,
    max_examples_scan=100_000,
)
report = profile_folder("/data/retail", config=config)

# Profile a single file (CSV or Excel)
report = profile_file("customers.csv")
report = profile_file("data.xlsx")  # All sheets profiled

# Profile a pandas DataFrame directly
import pandas as pd
df = pd.read_csv("data.csv")
profile = profile_dataframe(df, name="my_table")

# Render outputs
txt_output = render_txt(report)   # Token-efficient TXT for LLM context
json_output = render_json(report) # Full JSON with all details

# Access results
for file_profile in report["files"]:
    print(f"{file_profile['table_name']}: {file_profile['profile']['rows']} rows")

# Access FK hints
if report["relationships"]:
    for hint in report["relationships"]["fk_hints"]:
        print(f"{hint['from_table']}.{hint['from_column']} -> "
              f"{hint['to_table']}.{hint['to_column']}")

API Reference

Function	Description
profile_folder(path, config=None)	Profile all CSV/Excel files in a folder
profile_file(path, config=None)	Profile a single CSV or Excel file
profile_dataframe(df, name, config=None)	Profile a pandas DataFrame directly
render_txt(report)	Render report as compact TXT string
render_json(report)	Render report as JSON string

Architecture

flowchart TD
    subgraph input [Input Layer]
        CLI[cli.py]
        CLI --> Reader[readers.py]
    end
    
    subgraph readers [File Discovery]
        Reader --> CSV[CSV Files]
        Reader --> Excel[Excel Sheets]
        CSV --> Tables[List of DataFrames]
        Excel --> Tables
    end
    
    subgraph profiling [Profiling Layer]
        Tables --> Core[profiler.py]
        Core --> Distributions[distributions.py]
        Core --> Anomalies[anomalies.py]
        Core --> Correlations[correlations.py]
        Core --> Shape[shape.py]
        Core --> TableProfiles[Per-Table Profiles]
    end
    
    subgraph intra [Intra-Table Analysis]
        Correlations --> NumCorr[Numeric Correlations]
        Correlations --> CramersV[Categorical Associations]
        Correlations --> CondStats[Conditional Statistics]
    end
    
    subgraph cross [Cross-Table Analysis]
        TableProfiles --> Relations[relationships.py]
        Relations --> FKHints[FK Hints]
        Relations --> PKCandidates[PK Candidates]
    end
    
    subgraph output [Output Layer]
        TableProfiles --> Render[output.py]
        FKHints --> Render
        PKCandidates --> Render
        NumCorr --> Render
        CramersV --> Render
        CondStats --> Render
        Render --> TXT[profile_report.txt]
        Render -.->|--json flag| JSON[profile_report.json]
    end

Data Flow

1. Input Layer: CLI parses arguments and invokes the reader
2. File Discovery: Discovers CSV files and Excel sheets, loads as DataFrames
3. Profiling Layer: Profiles each table using distribution and anomaly modules
4. Cross-Table Analysis: Detects FK relationships and PK candidates across tables
5. Output Layer: Renders TXT (always) and JSON (optional) reports

Repository Structure

dataset-profiler/
├── README.md                # This file
├── requirements.txt         # Python dependencies
├── pyproject.toml           # Package configuration
├── .gitignore
├── data_profiler/           # Main package
│   ├── __init__.py          # Public API exports
│   ├── __main__.py          # Entry point for python -m data_profiler
│   ├── api.py               # Public API functions (profile_folder, profile_file, etc.)
│   ├── cli.py               # Argparse CLI + orchestration
│   ├── readers.py           # CSV/Excel file discovery and loading
│   ├── profiler.py          # Core DataFrame profiling logic
│   ├── distributions.py     # Categorical, datelike, high-cardinality summaries
│   ├── anomalies.py         # Duplicates, outliers, invalid dates, rare categories
│   ├── correlations.py      # Intra-table: numeric correlations, Cramér's V, conditional stats
│   ├── shape.py             # Distribution shape detection (normal, skewed, bimodal, etc.)
│   ├── relationships.py     # Cross-table FK hints and PK detection
│   ├── output.py            # TXT and JSON rendering
│   ├── helpers.py           # Shared utility functions
│   ├── types.py             # TypedDicts and dataclasses
│   └── ANALYSIS.md          # Detailed analysis reference
├── example_outputs/         # Sample profile reports
│   ├── retail/
│   ├── hr/
│   ├── ecomm-sales/
│   ├── company/
│   └── payroll/
└── tests/                   # API and integration tests
    ├── test_api.py          # Public API tests
    └── data/                # Test data files

Detailed Analysis Reference

For comprehensive documentation on what analysis is performed for each column type, see data_profiler/ANALYSIS.md, which covers:

• Core stats and cardinality bucketing
• Semantic type inference rules (including year-like detection)
• Numeric coercion for string columns
• Distribution summaries (categorical, datelike, high-cardinality)
• Distribution shape detection (normal, skewed, bimodal, sparse, discrete)
• Intra-table column relationships (correlations, associations, conditional stats)
• Anomaly detection (duplicates, outliers, invalid dates, rare categories)
• Cross-table relationship detection algorithms