Evangelos

Evangelos

Pandas Mental Models, Indexing, and the Reality of Data Transformations

PandasPythonETLData EngineeringSEC Filings

I have been working through a real dataset from the SEC financial statement data sets (2026 Q1 release), using pandas to understand how structured financial reporting data behaves in real investment and data engineering workflows.

This type of dataset is representative of what shows up in production financial systems: high-volume, multi-entity reporting data used in equity research, portfolio analytics, and downstream feature engineering pipelines.

The files (sub.txt and pre.txt) are part of the SEC's public dataset:
SEC Financial Statement Data Sets

In practice, data like this is rarely analysis-ready. It requires structured transformation, normalization, and deduplication before it becomes usable for research or portfolio systems.

The dataset is loaded like this:

sub = pandas.read_csv("sub.txt", sep="\t", index_col="adsh")

Each row represents a filing submission. The adsh field (Accession Number) is used as the DataFrame index, turning the dataset into a keyed structure rather than a flat table.

This matters because in real systems, identifiers like accession numbers, tickers, or timestamps define access patterns and join behavior, not just row identity.

What initially looks like exploratory analysis quickly becomes a question of data modeling and transformation semantics.

A DataFrame is not a table, it is a transformation system

It is common to think of a pandas DataFrame as a SQL table.

That model breaks down quickly in real pipelines.

A DataFrame behaves more like an in-memory transformation layer with multiple access semantics:

  • labeled indexing (semantic access via .loc)
  • positional indexing (.iloc)
  • vectorized column operations
  • implicit alignment on indexes during operations

In production systems, the index often encodes business meaning, not just row position.

loc vs iloc: semantic vs positional access

Label-based access:

sub.loc["0000014693-26-000010"]

Position-based access:

sub.iloc[0, -1]

These represent two different models of data access: key-based retrieval versus array indexing.

Mixing them incorrectly is a common source of subtle bugs in data pipelines.

Vectorization is the core abstraction

sub["name"].str.lower()

This is not iteration it is a column-wise transformation.

Pandas expresses computation as operations over entire arrays rather than row-by-row execution.

In production data systems, this is the difference between scalable transformations and inefficient logic.

You do not iterate over data. You define transformations over datasets.

Mutation vs transformation design

Mutation:

sub["url"] = "http://www.sec.gov/" + sub["cik"].astype(str) + "/"

Pipeline style:

sub.assign(
    url="http://www.sec.gov/" + sub["cik"].astype(str) + "/"
)

The second approach is closer to production ETL design: explicit transformations and clearer structure.

Chaining turns pandas into a transformation pipeline

The value of transformation-style APIs becomes clear when operations are composed rather than executed as isolated mutations.

Instead of updating a shared DataFrame step by step:

sub["url"] = "http://www.sec.gov/" + sub["cik"].astype(str) + "/"
sub = sub[sub.form.isin(["10-K", "10-Q"])]
sub = sub.drop_duplicates(subset=["cik"], keep="last")
sub["name"] = sub["name"].str.upper()

You can express the same logic as a single transformation pipeline:

clean = (
    sub
    .assign(
        url=lambda df: "http://www.sec.gov/" + df["cik"].astype(str) + "/"
    )
    .query("form in ['10-K', '10-Q']")
    .drop_duplicates(subset=["cik"], keep="last")
    .assign(name=lambda df: df["name"].str.upper())
)

Each step is now:

  • locally defined
  • free of external state
  • composable with other transformations

This shifts pandas code from imperative mutation of a dataset to declarative definition of a transformation.

Copy-on-Write (pandas 3): eliminating hidden mutation risk

Pandas 3 introduces Copy-on-Write semantics by default.

This removes a class of hidden mutation bugs in analytical and production ETL pipelines.

It eliminates ambiguity around whether derived objects share memory with their source.

In earlier versions of pandas, slicing a DataFrame could lead to unintentionally modifying the original data depending on internal view versus copy behavior.

In pandas 3, behavior is deterministic.

df = pd.DataFrame({"foo": [1, 2, 3], "bar": [4, 5, 6]})

subset = df["foo"]
subset.iloc[0] = 100

Under Copy-on-Write semantics:

  • subset is modified independently
  • df remains unchanged

This makes transformations safer and easier to reason about in pipelines where data is reused across multiple downstream computations.

Reference: pandas Copy-on-Write documentation

Filtering as compositional logic

sub[sub.form.isin(["10-K", "10-Q"])]

sub[
    sub.name.str.upper().str.startswith("BANK") &
    sub.name.str.upper().str.contains("HAWAII")
]

Filtering is boolean composition over arrays, not query execution.

This pattern maps directly to feature engineering logic and SQL-style filtering in ETL pipelines.

Real-world data is inconsistent by default

sub.name.value_counts().head(10)
sub.name.drop_duplicates().value_counts()

Duplication is expected in financial datasets due to repeated filings, amendments, and entity-level inconsistencies.

Deduplication becomes a standard ETL step rather than an exception handling case.

sub.drop_duplicates(subset=["cik"], keep="last")

Schema definition through column selection

sub = sub[["cik", "name", "countryba", "stprba", "cityba", "url"]]

This defines the dataset contract for downstream consumers.

In production systems, this step effectively enforces schema boundaries between raw ingestion and analytical or modeling layers.

Closing thought

pandas is not just an analysis library it is a transformation system for structured data.

When applied to financial datasets like SEC filings, it functions as a lightweight ETL layer for constructing datasets used in research and investment workflows.

With pandas 3, the mental model becomes more explicit:

  • mutation is controlled and predictable
  • copy semantics are deterministic
  • transformations are composable and explicit

This aligns pandas more closely with production data engineering systems than traditional notebook-based analysis tools.




Extra Note

This mini blog is built with Next.js, TypeScript, and React.

← Back to home