Indexing
Indexing, in a data analysis context, refers to two related but distinct operations that frequently get conflated: selecting a subset of data by position or label (accessing a specific row, column, or cell within a data frame or array), and building a database index, a separate data structure that accelerates lookups and queries against a large table. Both share the same root idea — providing a fast, organized way to locate specific data — but they operate at completely different layers of a data system, and the term's double meaning is a common source of confusion for people moving between the two contexts.
In the data frame and array sense, indexing has two primary modes that pandas makes explicit with separate accessor syntax specifically to avoid ambiguity: label-based indexing (.loc), which selects by the actual index label (a date, a customer ID, a named row), and position-based indexing (.iloc), which selects by raw integer position regardless of what label happens to be there. The distinction matters concretely whenever a data frame's index isn't simply 0, 1, 2, 3... in order — after filtering a data frame down to a subset of rows, the remaining rows keep their original labels rather than being renumbered, so df.loc[5] and df.iloc[5] can refer to two entirely different rows: .loc[5] looks for the row literally labeled "5" (which might no longer be the sixth row, or might not exist at all after filtering), while .iloc[5] always means the sixth row by position, whatever label it happens to carry. Using the wrong one is a common, quiet source of bugs precisely because both often return valid-looking (just wrong) results rather than raising an obvious error.
Boolean indexing — selecting rows based on a condition that evaluates to true or false for each row (df[df['revenue'] > 1000]) rather than by explicit label or position — is the mechanism underlying most real-world filtering in pandas, and it works through the same vectorized evaluation pattern that makes pandas operations fast: the condition is evaluated across the entire column at once, producing a boolean mask, which is then used to select only the rows where the mask is true, all without an explicit Python loop. This pattern composes directly with multiple conditions combined via logical operators, which is the standard idiom for anything beyond a single simple filter.
In the database sense, an index is a separate, auxiliary data structure — most commonly a B-tree — that a database maintains alongside a table specifically to make lookups and searches on a particular column fast without scanning every row. Without an index, finding all orders for a given customer_id requires a full table scan, checking every row in the table one by one; with an index on customer_id, the database can jump nearly directly to the relevant rows, the same conceptual speedup a book's index provides over reading every page to find a topic. This kind of indexing isn't free — every insert or update to an indexed column has to additionally update the index structure, which is why indexes are chosen deliberately for columns that are frequently searched, filtered, or joined on, rather than applied indiscriminately to every column in a table.
MultiIndex (hierarchical indexing) extends the single-label indexing concept to multiple levels simultaneously — a data frame indexed by both "region" and "year" together, for instance, letting data be selected, grouped, and reshaped along either level independently or jointly — and it's the mechanism underlying much of pandas' pivot table and multi-level groupby functionality, since a pivoted or grouped result naturally has more than one dimension of labeling to track.
Read indexing as a term covering two genuinely different operations that happen to share a name: selecting a subset of in-memory data by label or position (where .loc versus .iloc is the single most common source of confusion) and building an auxiliary lookup structure to accelerate database queries (where the index itself is invisible to a query's syntax but decisive for its performance) — both exist to answer the same underlying question, "how do I find the specific data I want quickly," just at entirely different layers of a data system.