Selecting, aligning and indexing tables safely

The Series and DataFrame lessons show how labels make tables readable. This lesson develops selection, alignment and multi-key indexing skills. It assumes that you can create a DataFrame and select a column. The records below are invented cultural-programme examples.

Inspect structure before selecting

A table has rows, columns, labels and types. These are related but different. An index is not automatically a unique observation identifier, and an integer-looking label is not automatically a position. Use head, shape, dtypes and info() to establish what you actually received. A small preview cannot prove that a large column is clean, but it helps you formulate checks.

import pandas as pd
programmes = pd.DataFrame({
    "record_id": [101, 102, 103],
    "institution": ["North", "South", "North"],
    "visits": [12, 7, 20],
    "seats": [15, 10, 25],
}).set_index("record_id")
print(programmes.shape)
print(programmes.dtypes)
print(programmes.loc[102, "institution"])
print(programmes.iloc[1, 0])
assert programmes.index.is_unique

(3, 3)
institution    object
visits          int64
seats           int64
dtype: object
South
South

Both selections return South, but they express different requests. .loc[102] asks for label 102; .iloc[1] asks for the second row in the current order. This distinction also avoids relying on the legacy series[0] positional behaviour shown in some older material. Use .iloc[0] for the first position and .loc[label] for a named label. The Pandas indexing guide documents these explicit selectors.

Select and assign in one clear operation

A mask identifies rows satisfying a condition. Parentheses matter when combining masks because & and | combine elementwise conditions. Assignment should make both the selected rows and the target column explicit.

chosen = programmes.loc[
    (programmes["institution"] == "North") & (programmes["visits"] >= 15),
    ["institution", "visits"],
]
print(chosen)
revised = programmes.copy()
revised.loc[revised["visits"] < 10, "visits"] = 10
assert programmes.loc[102, "visits"] == 7
assert revised.loc[102, "visits"] == 10

          institution  visits
record_id                    
103             North      20

We preserved the source table, selected a purposeful condition, and changed the copy. Avoid chained assignment such as selecting a subset and then assigning through another bracket expression. Its behaviour is unsuitable as a general teaching pattern across Pandas versions. A change in one object should never be your only evidence that the intended table changed: inspect the actual target.

Understand label alignment

When labelled Series are combined, their labels participate in the operation. Matching by position is a different request. Before converting to an array, make sure that all related Series follow the intended row order.

expected = pd.Series([15, 25, 10], index=[101, 103, 102])
spare = expected - programmes["visits"]
print(spare.sort_index())
aligned = expected.reindex(programmes.index)
assert aligned.tolist() == [15, 10, 25]
assert spare.loc[102] == 3

101    3
102    3
103    5
dtype: int64

The deliberately different order does not break subtraction because labels match. A missing label would create an unresolved value rather than justify guessing a match. reset_index() turns the index into ordinary columns, while set_index() selects columns as index labels. Neither operation establishes that the chosen labels are unique; you must check the relevant constraint.

Work with multiple keys

A cultural institution can have several years of records. Institution plus year may identify an observation even though institution alone does not. A MultiIndex represents this combined label structure; it is not a new kind of observation.

annual = pd.DataFrame({
    "institution": ["North", "North", "South", "South"],
    "year": [2022, 2023, 2022, 2023],
    "visits": [12, 15, 8, 11],
}).set_index(["institution", "year"]).sort_index()
print(annual.loc["North"])
print(annual.xs(2023, level="year"))
assert annual.loc[("South", 2023), "visits"] == 11

      visits
year        
2022      12
2023      15
             visits
institution        
North            15
South            11

The advanced-indexing guide provides the detailed MultiIndex interface. Start with named levels and a small table. For many investigations, ordinary key columns remain easier to export and validate. Choose an index because it makes a task clearer, not because it makes the table appear advanced.

Practice and independent check

Sort programmes by visits and compare the first positional row with label 101. Create a mask for North or for at least 10 visits, then explain how its meaning differs from using “and.” Add an unexpected label to expected and inspect subtraction before and after reindexing. Explain which information would be lost if you discarded labels too early.

For a transfer task, create a six-row table with a two-column key. Demonstrate a label selection, positional selection, safe assignment, and cross-section. State the key constraint and show an assertion that checks it. This evidence prepares you for acquisition and integration, where labels and keys frequently differ across sources.

Chapter exercisesWorked answers after an attempt