CSV Metadata Forensic

CSV Metadata Forensic Reconstruction

Overview

Reconstruct a user's activity history by cross-referencing metadata headers across multiple CSV export files. The core principle: headers are the schema, timestamps are the skeleton, and cross-file key relationships are the connective tissue -- before analyzing content, extract everything the metadata alone can tell you.

When to Use

• Multiple CSV files from a structured data export (platform takeout, GDPR export, API dump)
• Need to establish baseline metrics before deeper content analysis
• Need to identify data gaps, missing files, or schema inconsistencies
• Calculating account lifespan, activity frequency, content creation ratios
• Building a timeline of user activity from timestamps scattered across files

When NOT to use:

• Single CSV file with no cross-reference potential
• Unstructured data (logs, freeform text) -- use log parsing techniques instead
• When you need content-level analysis (sentiment, topic modeling) -- this skill is metadata-only

Core Pattern

1. INVENTORY  -->  2. SCHEMA MAP  -->  3. CROSS-REFERENCE  -->  4. TEMPORAL  -->  5. METRICS  -->  6. REPORT
  List files       Extract headers     Find shared keys        Build timeline    Calculate       Write findings
  + checksums      + detect types      + validate joins        + find bounds     baselines       + flag gaps

Quick Reference

Implementation

Step 1: File Inventory

List every CSV. Record filename, size, row count, and checksum (if a checkfile exists).

import os, csv
from pathlib import Path

export_dir = Path(".")
manifest = {}
for f in sorted(export_dir.glob("*.csv")):
    with open(f) as fh:
        reader = csv.reader(fh)
        headers = next(reader, [])
        row_count = sum(1 for _ in reader)  # excludes header
    manifest[f.name] = {
        "size_bytes": f.stat().st_size,
        "row_count": row_count,
        "headers": headers,
    }

Validate against checkfile: If the export includes a checksum manifest (e.g., checkfile.csv with filename,sha256), verify every file's hash matches. Flag mismatches as potential corruption or tampering.

Step 2: Schema Mapping

Build a unified schema map: which columns appear in which files, and which columns are shared across files.

from collections import defaultdict

col_to_files = defaultdict(list)
for fname, info in manifest.items():
    for col in info["headers"]:
        col_to_files[col].append(fname)

# Shared keys = columns appearing in 2+ files
shared_keys = {col: files for col, files in col_to_files.items() if len(files) > 1}

Key shared columns to look for:

• id -- content identifiers (may need disambiguation: post ID vs. comment ID)
• permalink -- URL-based content locators
• date / timestamp / created_at -- temporal anchors
• subreddit / channel / category -- grouping dimensions
• ip -- network location (flag as PII)

Step 3: Temporal Skeleton

Extract the earliest and latest timestamp from every file that has a date column. This establishes the activity window per data type.

import pandas as pd

temporal_bounds = {}
for fname, info in manifest.items():
    date_cols = [c for c in info["headers"] if c in ("date", "created_at", "sent_at", "timestamp")]
    if not date_cols:
        continue
    df = pd.read_csv(fname, usecols=date_cols, parse_dates=date_cols)
    for col in date_cols:
        series = df[col].dropna()
        if series.empty:
            continue
        temporal_bounds[f"{fname}.{col}"] = {
            "earliest": series.min(),
            "latest": series.max(),
            "count": len(series),
        }

Account lifespan = delta between the registration timestamp (often in a statistics or account file) and the most recent activity timestamp across all files.

Step 4: Baseline Metrics

Calculate these from metadata alone -- no content parsing required:

Step 5: PII Scan

Before proceeding to any downstream analysis, flag columns that contain personally identifiable information:

Rule: Always flag PII. Never silently pass it through to reports.

Step 6: Gap Analysis

Identify what is missing or inconsistent:

• Empty files: Row count = 0 but file exists (feature never used, or data was purged)
• Missing timestamps: Files without date columns cannot be placed on the timeline
• IP gaps: Some records have IPs, others do not (platform may only log recent activity)
• Schema mismatches: _headers file columns do not match the full data file columns
• Orphan references: parent IDs in comments that do not map to any known id

Step 7: Write Report

Output findings to docs/analysis/01-csv-metadata-forensic.md. The report structure:

# CSV Metadata Forensic Reconstruction Report

## File Inventory
[Table: filename, row count, size, has timestamps, has PII]

## Schema Map
[Table: column name, which files contain it, inferred type]

## Temporal Skeleton
[Table: file, earliest timestamp, latest timestamp, record count]
[Account lifespan calculation]

## Baseline Metrics
[All calculated metrics with values]

## PII Inventory
[Table: column, file, data type, recommended handling]

## Data Quality Issues
[List of gaps, mismatches, empty files, orphan references]

## Recommendations for Downstream Analysis
[What analyses are viable given the data quality]
[What analyses are NOT viable due to insufficient data]

Good Patterns

• Checksum-first: Validate file integrity before any analysis. Corrupted files produce misleading metrics.
• Headers before content: Read only row 1 of each file during schema mapping. Do not load full datasets until you know what you are working with.
• Explicit type inference: Do not trust pandas.read_csv auto-detection for dates. Pass parse_dates= explicitly for known date columns. Verify with .dtypes.
• Quarantine bad rows: When a row fails to parse, log it with filename + line number instead of silently dropping it.
• Schema reconciliation via pd.concat: When merging data from files with slightly different schemas, use pd.concat with join="outer" and track which columns came from which files.
• Least-frequency-of-occurrence: Rare values in metadata columns (unusual subreddits, one-off IPs) are often the most analytically interesting.

Anti-Patterns

Boundaries

This skill SHOULD:

• Parse metadata headers and calculate structural baselines
• Identify join keys and cross-file relationships
• Calculate temporal bounds and activity metrics
• Flag PII for downstream handling
• Identify data gaps and quality issues
• Produce a structured report of findings

This skill should NOT:

• Make behavioral inferences beyond what metadata supports (e.g., "user was depressed during gap" from an activity hiatus)
• Perform content analysis (sentiment, topic modeling, psycholinguistics)
• Handle PII without flagging it -- every PII column must be inventoried
• Skip data validation -- never assume files are complete or uncorrupted
• Combine data from different users or accounts without explicit disambiguation

Insufficient Data Handling

Minimum Viable Data

To produce any meaningful output, you need at minimum:

• 2+ CSV files with at least 1 shared column (for cross-referencing)
• At least 1 file with a timestamp column (for temporal analysis)
• At least 1 file with > 0 data rows (for baseline metrics)

What to Do When Data Is Missing

When in doubt, report what you CAN determine and explicitly state what you CANNOT. An incomplete report that flags its own gaps is more valuable than a complete-looking report built on silent assumptions.

Common Mistakes

References

• Forensic Focus: CSV File Meta Data
• Mastering Forensic Timelines: Tools and Techniques for DFIR
• Timeline-Based Event Reconstruction for Digital Forensics (SoK)
• csvkit Documentation
• CSV Formatting Tips for Data Accuracy (Integrate.io)
• SANS: Timeline Analysis with Apache Spark and Python
• Forensic Data Validation (Vaia)