Explore Data Skill

Profile and explore datasets to understand their shape, quality, and patterns. Produce a structured overview that informs downstream analysis decisions.

Workflow

Step 1: Load and Inspect

import pandas as pd
import numpy as np

# Load the data
df = pd.read_csv('data.csv')  # or pd.read_parquet, pd.read_sql, etc.

# First look
print(f"Shape: {df.shape[0]:,} rows x {df.shape[1]} columns")
print(f"Memory usage: {df.memory_usage(deep=True).sum() / 1e6:.1f} MB")
print(f"\nColumn types:\n{df.dtypes.value_counts()}")
print(f"\nFirst 5 rows:")
df.head()

Step 2: Column-Level Profiling

For each column, compute:

def profile_column(series):
    stats = {
        'dtype': str(series.dtype),
        'count': len(series),
        'null_count': series.isnull().sum(),
        'null_pct': f"{series.isnull().mean():.1%}",
        'unique': series.nunique(),
        'unique_pct': f"{series.nunique() / len(series):.1%}",
    }

    if pd.api.types.is_numeric_dtype(series):
        stats.update({
            'min': series.min(),
            'max': series.max(),
            'mean': series.mean(),
            'median': series.median(),
            'std': series.std(),
            'zeros': (series == 0).sum(),
            'negatives': (series < 0).sum(),
        })
    elif pd.api.types.is_datetime64_any_dtype(series):
        stats.update({
            'min': series.min(),
            'max': series.max(),
            'range_days': (series.max() - series.min()).days,
        })
    else:  # categorical / string
        stats.update({
            'top_5': series.value_counts().head(5).to_dict(),
            'min_length': series.astype(str).str.len().min(),
            'max_length': series.astype(str).str.len().max(),
        })

    return stats

profile = {col: profile_column(df[col]) for col in df.columns}

Step 3: Data Quality Checks

Run these checks automatically:

Completeness

# Null analysis
null_summary = pd.DataFrame({
    'null_count': df.isnull().sum(),
    'null_pct': df.isnull().mean()
}).sort_values('null_pct', ascending=False)
print("Columns with nulls:")
print(null_summary[null_summary['null_count'] > 0])

Uniqueness

# Duplicate detection
dup_count = df.duplicated().sum()
print(f"\nExact duplicate rows: {dup_count} ({dup_count/len(df):.2%})")

# Check candidate keys
for col in df.columns:
    if df[col].nunique() == len(df):
        print(f"  Potential primary key: {col}")

Validity

# Suspicious values
for col in df.select_dtypes(include='number').columns:
    outliers = df[col].quantile([0.01, 0.99])
    extreme = df[(df[col] < outliers[0.01]) | (df[col] > outliers[0.99])]
    if len(extreme) > 0:
        print(f"  {col}: {len(extreme)} extreme values outside 1st/99th percentile")

# Date range gaps
for col in df.select_dtypes(include='datetime64').columns:
    date_range = pd.date_range(df[col].min(), df[col].max())
    missing_dates = date_range.difference(df[col].dt.normalize().unique())
    if len(missing_dates) > 0:
        print(f"  {col}: {len(missing_dates)} missing dates in range")

Consistency

# Cross-column checks
# Example: end_date should be after start_date
if 'start_date' in df.columns and 'end_date' in df.columns:
    invalid = df[df['end_date'] < df['start_date']]
    print(f"  end_date before start_date: {len(invalid)} rows")

# Referential integrity (if multiple tables)
# Example: all order.customer_id values exist in customers table

Step 4: Distribution Analysis

# Numeric distributions
for col in df.select_dtypes(include='number').columns:
    print(f"\n{col}:")
    print(df[col].describe(percentiles=[.01, .05, .25, .50, .75, .95, .99]))

# Categorical distributions
for col in df.select_dtypes(include=['object', 'category']).columns:
    print(f"\n{col} (top 10 of {df[col].nunique()} unique):")
    print(df[col].value_counts().head(10))

Step 5: Relationship Detection

# Correlation matrix for numeric columns
corr = df.select_dtypes(include='number').corr()
high_corr = []
for i in range(len(corr.columns)):
    for j in range(i+1, len(corr.columns)):
        if abs(corr.iloc[i, j]) > 0.8:
            high_corr.append((corr.columns[i], corr.columns[j], corr.iloc[i, j]))

if high_corr:
    print("Highly correlated pairs (|r| > 0.8):")
    for col1, col2, r in high_corr:
        print(f"  {col1} <-> {col2}: r={r:.3f}")

Step 6: Temporal Patterns (if date columns exist)

if date_cols:
    date_col = date_cols[0]
    daily = df.groupby(df[date_col].dt.date).size()
    print(f"\nTemporal coverage:")
    print(f"  Date range: {daily.index.min()} to {daily.index.max()}")
    print(f"  Days covered: {len(daily)}")
    print(f"  Avg rows/day: {daily.mean():.0f}")
    print(f"  Min rows/day: {daily.min()} on {daily.idxmin()}")
    print(f"  Max rows/day: {daily.max()} on {daily.idxmax()}")

Output Format

Produce a structured exploration report:

## Dataset Overview
- **Source**: [file name, table name, or query]
- **Shape**: [rows] x [columns]
- **Date range**: [min date] to [max date]
- **Grain**: One row appears to represent [entity]

## Column Summary
| Column | Type | Nulls | Unique | Notes |
|--------|------|-------|--------|-------|
| col_a  | int  | 0%    | 1,234  | Likely primary key |
| col_b  | str  | 2.3%  | 15     | Categorical |

## Data Quality Issues
1. [Issue description with counts and severity]
2. [Issue description with counts and severity]

## Key Distributions
[Top values for categoricals, summary stats for numerics]

## Relationships
[Notable correlations or patterns between columns]

## Recommendations
- [Which columns are useful for analysis]
- [Which columns need cleaning]
- [What questions this data can/cannot answer]

Examples

Example 1: New CSV File

User drops a CSV. Profile all columns, identify the grain, flag quality issues, and suggest what analyses are possible.

Example 2: Database Table

User asks about a warehouse table. Run SQL-based profiling queries, check freshness, compare to documentation.

Example 3: Pre-Analysis QA

User has data loaded in a notebook. Run quality checks before starting analysis to ensure the data is fit for purpose.

Best Practices

1. Profile before analyzing. Never start an analysis without understanding the data shape and quality first.
2. Check the grain first. Understanding what one row represents prevents aggregation errors downstream.
3. Look at the tails. Min/max values often reveal data quality issues faster than summary statistics.
4. Compare expected vs. actual. If you expect 365 days of data, check that you have 365 distinct dates.
5. Sample large datasets. For tables with billions of rows, profile a representative sample first.
6. Document what you find. Quality issues discovered during exploration should be recorded for future analysts.
7. Check for PII. Flag columns that may contain personally identifiable information (emails, names, phone numbers).
8. Verify types. Numeric IDs stored as strings, dates stored as strings, and booleans stored as integers are common issues.
9. Look at null patterns. Are nulls random or systematic? A column that is null for all rows after a certain date suggests a schema change.
10. Count before you filter. Know how many rows you start with and how many each filter removes.