How to Migrate Existing Data Formats to the sdata Structure: A Complete Guide

Migrate any legacy dataset to sdata by loading it via a Data.from_* constructor, enriching metadata with helper methods, and exporting it through a to_* serializer.

The sdata package defines a single, self-descifying object—sdata.Data—that bundles a pandas DataFrame, a rich metadata table (sdata.Metadata), and an optional description. When migrating existing data formats to the sdata structure, you transform disparate CSV, Excel, JSON, or database exports into a normalized, version-control-friendly format using the I/O routines defined in sdata/data.py.

The Three-Step Migration Workflow

Every migration follows the same pattern regardless of the source format. The Data class provides symmetric import and export methods that preserve the internal !sdata_* attribute namespace automatically.

1. Load the Raw File

Use the appropriate class-method constructor for your source format. The Data class supports direct ingestion from common file types and remote sources:

• Data.from_csv() – Parses CSV files with optional comment-header metadata.
• Data.from_xlsx() – Reads Excel workbooks, including multi-sheet metadata layouts.
• Data.from_json() – Ingests JSON strings or dictionaries.
• Data.from_sqlite(), Data.from_hdf5(), Data.from_parquet() – Handles database and binary columnar formats.
• Data.from_url() – Fetches and parses remote datasets.

2. Enrich the Metadata

After loading, populate the Metadata object to capture column-level attributes and provenance. The helper Data.set_column_metadata() automatically parses headers like "Force [N]" into name/unit pairs and stores them as indexed attributes (!sdata_column_i). You can also manually add attributes using data.metadata.add() or data.metadata.set_attr().

3. Persist as sdata

Export the fully populated object using any to_* method. The Data.to_folder() method creates a portable directory structure containing metadata.csv and a data file (CSV or XLSX), enabling version-safe round-trips and human-readable archiving.

Migrating from CSV Files

CSV files often contain embedded metadata in comment lines or unit annotations in column headers. The sdata loader extracts this information and the set_column_metadata() helper normalizes it.

import sdata

# Load a legacy CSV that uses "#;" lines for metadata

data = sdata.Data.from_csv("legacy_dataset.csv")

# Parse "Force [N]" style headers into structured column attributes

data.set_column_metadata()

# Export as a self-contained folder suitable for git versioning

data.to_folder("sdata_export", dtype="csv")

Relevant source: Data.from_csv and Data.set_column_metadata in sdata/data.py.

Migrating from Excel Workbooks

Excel files frequently separate data and metadata across sheets. The from_xlsx method detects sdata-compatible layouts automatically, while to_sqlite converts the result to a high-performance queryable format.

import sdata

# Import the data sheet and any companion metadata sheet

data = sdata.Data.from_xlsx("experiment.xlsx")

# Ensure column-level attributes exist for downstream analysis

data.set_column_metadata()

# Convert to SQLite for fast random access via SqliteDict

data.to_sqlite("experiment.sqlite")

Relevant source: Data.from_xlsx and Data.to_sqlite in sdata/data.py, with SQLite backend support from sdata/contrib/sqlitedict.py.

Handling JSON and API Payloads

For web-based data sources, from_json accepts Python dictionaries directly, allowing seamless integration with REST APIs.

import sdata
import requests

# Fetch payload from a REST endpoint

payload = requests.get("https://example.com/api/results").json()

# Construct Data object from JSON dict

data = sdata.Data.from_json(s=payload)

# Append provenance metadata

data.metadata.add("source_url", "https://example.com/api/results")
data.metadata.add("acquired", sdata.timestamp.now_utc_str())

# Generate an HTML report with embedded download links

data.to_html("report.html")

Relevant source: Data.from_json and timestamp utilities in sdata/timestamp.py.

Ensuring Reproducibility with Deterministic UUIDs

For reproducible data pipelines, generate a hash-based UUID instead of a random one. Setting uuid="hash" triggers Data.gen_uuid_from_state(), which computes a SHA-3-256 hash over the metadata, table contents, and description to derive a deterministic identifier.

import sdata

# Load data with a deterministic UUID derived from content hash

data = sdata.Data.from_csv("raw.csv", uuid="hash")

print(f"Deterministic UUID: {data.uuid}")

Relevant source: Data.__init__ handling of the uuid parameter and Data.gen_uuid_from_state in sdata/data.py.

Architectural Foundations of sdata Migration

Understanding the internal conventions ensures robust custom migrations.

Unified Attribute Namespace – All sdata-specific fields use the !sdata_ prefix (e.g., !sdata_uuid, !sdata_name). These are managed by the Metadata class in sdata/metadata.py and are automatically serialized by every I/O method.

Column-Level Metadata Extraction – The set_column_metadata() method uses regex utilities from sdata/tools.py to decompose column headers into name and unit components. The reverse operation, set_columnnames_from_metadata(), reconstructs descriptive headers for export.

Folder-Based Archiving – Data.to_folder() creates a plain directory structure with separate files for metadata and data. Re-importing via Data.from_folder() restores an identical object state, making this format ideal for long-term archival and version control systems.

Summary

• Load legacy formats using Data.from_csv, Data.from_xlsx, Data.from_json, or database-specific constructors defined in sdata/data.py.
• Enrich metadata automatically with set_column_metadata() or manually via metadata.add() to capture units, descriptions, and provenance.
• Persist using to_folder() for human-readable, version-control-friendly archives, or use to_sqlite, to_parquet, and to_hdf5 for performance-critical storage.
• Leverage deterministic UUIDs (uuid="hash") for reproducible data pipelines that require stable identifiers across regeneration.

Frequently Asked Questions

How does sdata handle column units embedded in header names?

The Data.set_column_metadata() method parses strings like "Force [N]" or "Temperature [°C]" using regex utilities in sdata/tools.py. It extracts the quantity name and unit, then stores them as indexed attributes (!sdata_column_0, !sdata_column_1, etc.) in the Metadata object. This allows downstream tools to access unit information programmatically while keeping the raw DataFrame clean.

Can I migrate data directly from a REST API URL?

Yes. Use Data.from_url() to fetch remote CSV or JSON files directly, or combine requests with Data.from_json() for complex authentication flows. After loading, you can attach provenance metadata such as the source URL and retrieval timestamp using data.metadata.add() before exporting to your preferred format.

What is the difference between to_folder and to_sqlite for archiving?

Data.to_folder() creates a portable directory containing a metadata.csv file and a data file (CSV or XLSX), optimized for human inspection and git versioning. Data.to_sqlite() serializes the object to a binary SQLite database using sdata/iolib/jsonsqlitestore.py, providing faster random access and better suited for large datasets or SQL-based querying.

How do I ensure the same UUID is generated every time I re-migrate a dataset?

Pass uuid="hash" when constructing the Data object (e.g., Data.from_csv("file.csv", uuid="hash")). This triggers gen_uuid_from_state(), which computes a SHA-3-256 hash of the DataFrame contents, metadata dictionary, and description string, then derives a UUID from that hash. Identical inputs will always produce identical UUIDs, enabling reproducible data pipelines.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}