Series, DataFrames, Indexes, and Explicit Schemas

Module 1: Foundations and Reproducibility · Lecture 4 of 32

A DataFrame is more than a spreadsheet-shaped object. Its columns have types, its rows have labels, and operations can align those labels automatically. Learn those rules before they silently introduce missing values.

What you will learn

Construct typed Series and DataFrames.
Distinguish row labels from row positions.
Explain label alignment during arithmetic.

Understand the problem

Two axes and a schema

A DataFrame has a row index and named columns. Each column is a Series with its own dtype. A schema states which columns exist, what each value means, which types are accepted, and whether missing values are allowed. The schema is a contract your analysis can verify, not just whatever types pandas happened to infer from today's file.

Labels are not positions

An index of order IDs can be useful for lookup, but the label O0003 is not “the third row.” Sorting changes positions while preserving labels. A default integer index can disguise this distinction because label 2 and position 2 initially refer to the same row. Use loc for labels and iloc for positions so the intent remains explicit after filtering or reordering.

Alignment can help or surprise

When two Series are added, pandas pairs matching labels. Missing partners introduce missing results unless you deliberately supply a fill policy. This is useful when revenue and refunds arrive in different order, but dangerous if you expected raw positional addition. Converting to a NumPy array discards label alignment; only do so after proving that order and grain match.

Choose nullable types deliberately

The nullable Int64 dtype can represent an unknown integer without converting the entire column to float. The explicit string dtype gives text operations a consistent missing-value model. Dtype spelling can vary between inferred strings and explicitly requested nullable strings, particularly across pandas major versions. Test the behavior you need rather than asserting that all text must use the older object dtype.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 4 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

revenue = pd.Series([10000, 20000], index=['O1', 'O2'], dtype='Int64')
refunds = pd.Series([3000, 1000], index=['O2', 'O1'], dtype='Int64')
net = revenue - refunds
table = pd.DataFrame({'revenue_paise': revenue, 'refund_paise': refunds,
                      'net_paise': net})
print(table.to_string())
print('O1 net:', int(table.loc['O1', 'net_paise']))
assert table.loc['O1', 'net_paise'] == 9000
assert table.loc['O2', 'net_paise'] == 17000
assert table.index.is_unique

Verified output

    revenue_paise  refund_paise  net_paise
O1          10000          1000       9000
O2          20000          3000      17000
O1 net: 9000

Walk through the result

Refunds were constructed in the opposite order from revenue. The labels still match O1 to its 1000-paise refund, demonstrating why label alignment is valuable. The DataFrame constructor also aligns the Series by index. The explicit uniqueness assertion matters because duplicate row labels can make a label lookup return several rows. In the retail project, order_id remains a normal column until a particular analysis benefits from setting it as the index.

Common mistakes and diagnosis

A DataFrame does not enforce a relational primary key by default. Assigning an index name does not make its values unique. Constructing a DataFrame from a mix of differently indexed Series can create more rows than expected. If an arithmetic result unexpectedly becomes missing, compare the indexes before filling the result with zero; a missing label may indicate a broken join rather than zero activity.

Practice assignment

Add O3 revenue with no refund record. Compute one net Series that preserves the unknown refund and another that explicitly interprets the absence of a refund record as zero. Explain the assumption behind the second result.

Worked solution and explanation

Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.

expanded = pd.Series([10000, 20000, 5000], index=['O1', 'O2', 'O3'], dtype='Int64')
unknown_net = expanded - refunds
zero_assumed = expanded.sub(refunds, fill_value=0)
assert pd.isna(unknown_net.loc['O3'])
assert zero_assumed.loc['O3'] == 5000

Check your understanding

Why is fill_value=0 a business decision rather than a harmless formatting choice?

Answer guide

It converts the absence of a matched value into a numeric fact. That is defensible only if the source contract says that no refund record means no refund. If records are delayed, missing, or filtered incorrectly, replacing the absence with zero overstates net revenue. Preserve unknown values until the source semantics justify an imputation.

Reference and next step

pandas Series and DataFrame model

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next