Notebook 2: Pandas Fundamentals

Module: Data Analytics Level: Beginner to Intermediate Prerequisites: Notebook 1 (NumPy Fundamentals and Vector Math) Estimated Time: 3–4 hours

---

Table of Contents#

1. Why Pandas Exists
2. The Series: A Labeled 1D Array
3. The DataFrame: A Labeled 2D Table
4. The Index: Pandas's Labeling System
5. Selection and Indexing with loc and iloc
6. Filtering with Boolean Conditions
7. Adding, Modifying, and Dropping Columns
8. Reading and Writing Data
9. Inspecting a DataFrame
10. Hands-On: A Complete Exploration Workflow
11. Case Studies
12. Common Misconceptions and Pitfalls
13. Glossary
14. Exercises
15. Quiz
16. Summary
17. Further Reading

---

Learning Objectives#

By the end of this notebook, you will be able to:

• Explain the relationship between NumPy arrays, Series, and DataFrames
• Create Series and DataFrames from various data sources and explain the role of the Index
• Select data precisely using both label-based (loc) and position-based (iloc) indexing
• Filter rows using boolean conditions, including combining multiple conditions correctly
• Add, modify, and remove columns without introducing unintended side effects
• Read data from and write data to common file formats (CSV, JSON, Excel)
• Perform a first-pass inspection of any new dataset using standard diagnostic methods

---

1. Why Pandas Exists#

Notebook 1 introduced NumPy's ndarray, an extremely fast structure for numeric computation, but with one significant limitation for real analytics work: arrays are indexed only by integer position, and every element must share the same dtype. Real-world tabular data rarely fits this mold cleanly — a customer dataset naturally has a mix of numeric, categorical, and datetime columns, and analysts often want to refer to rows and columns by meaningful labels ("the revenue column," "the row for customer ID 4471") rather than raw integer positions.

Pandas is built directly on top of NumPy and addresses exactly this gap. Its two core structures, the Series (Section 2) and DataFrame (Section 3), add a labeling system (the Index, Section 4) and per-column typing on top of NumPy's underlying array machinery, while preserving as much of NumPy's vectorized performance as possible. In practice, nearly every table-shaped dataset used throughout the rest of this course, and throughout real-world analytics work, is represented as a Pandas DataFrame.

---

2. The Series: A Labeled 1D Array#

2.1 Definition#

A Series is a one-dimensional labeled array, conceptually a NumPy array (Notebook 1, Section 2) with an attached Index providing a label for every element.

Python
CopyAsk AI
import pandas as pd

s = pd.Series([25, 32, 47, 51], index=["Alice", "Bob", "Carol", "David"])
print(s)

Expected output:

code
Ask AICopy
Alice    25
Bob      32
Carol    47
David    51
dtype: int64

2.2 Series Are Still Vectorized NumPy Arrays Underneath#

Because a Series wraps a NumPy array internally, every vectorized operation from Notebook 1 (arithmetic, universal functions, boolean masking) works identically on a Series, with the added benefit that the Index labels are preserved through the operation:

Python
CopyAsk AI
ages_in_5_years = s + 5
print(ages_in_5_years)

Expected output:

code
Ask AICopy
Alice    30
Bob      37
Carol    52
David    56
dtype: int64

2.3 Accessing the Underlying Array#

Python
CopyAsk AI
print(s.values)      # the raw underlying NumPy array: [25 32 47 51]
print(type(s.values)) # <class 'numpy.ndarray'>

---

3. The DataFrame: A Labeled 2D Table#

3.1 Definition#

A DataFrame is a two-dimensional labeled table, best understood as a dictionary of Series objects that all share the same Index, one Series per column.

Python
CopyAsk AI
import pandas as pd

df = pd.DataFrame({
    "name": ["Alice", "Bob", "Carol", "David"],
    "age": [25, 32, 47, 51],
    "city": ["NY", "LA", "NY", "SF"]
})
print(df)

Expected output:

code
Ask AICopy
    name  age city
0  Alice   25   NY
1    Bob   32   LA
2  Carol   47   NY
3  David   51   SF

3.2 Columns Have Independent dtypes#

Unlike a NumPy array (Notebook 1, Section 2.2), where every element shares a single dtype, each column of a DataFrame is an independently-typed Series; the DataFrame as a whole is heterogeneous, but each individual column remains a fast, homogeneous NumPy array underneath.

Python
CopyAsk AI
print(df.dtypes)

Expected output:

code
Ask AICopy
name    object
age      int64
city    object
dtype: object

Text columns typically show as dtype object in older pandas versions (or the dedicated string dtype in more recent ones); Notebook 4 covers dtype selection and correction in depth, since getting dtypes right is essential both for correctness and for the vectorized performance discussed in Notebook 1, Section 10.

3.3 Selecting a Single Column#

Selecting one column from a DataFrame returns a Series:

Python
CopyAsk AI
ages = df["age"]
print(type(ages))  # <class 'pandas.core.series.Series'>
print(ages)

---

4. The Index: Pandas's Labeling System#

4.1 The Default Integer Index#

If no index is specified, Pandas assigns a default RangeIndex (0, 1, 2, ...), as seen implicitly in the DataFrame in Section 3.1.

4.2 Setting a Meaningful Index#

An index can be set to any column with meaningfully unique values, which then allows label-based lookups:

Python
CopyAsk AI
df_indexed = df.set_index("name")
print(df_indexed)
print(df_indexed.loc["Carol"])  # look up a row directly by name

Expected output:

code
Ask AICopy
       age city
name
Alice   25   NY
Bob     32   LA
Carol   47   NY
David   51   SF

age      47
city     NY
Name: Carol, dtype: object

4.3 Why the Index Matters Beyond Convenience#

The Index is not merely a display label; it governs how Pandas aligns data during operations. When combining two Series or DataFrames, Pandas automatically matches entries by index label, not by position, which is a critical and sometimes surprising behavior.

Python
CopyAsk AI
s1 = pd.Series([10, 20, 30], index=["a", "b", "c"])
s2 = pd.Series([1, 2, 3], index=["b", "c", "d"])

print(s1 + s2)

Expected output:

code
Ask AICopy
a     NaN
b    22.0
c    33.0
d     NaN
dtype: float64

Notice that a and d produce NaN (missing), since each exists in only one of the two Series; Pandas aligns by label first, and only combines values where the labels match, rather than combining based on raw position. This automatic alignment is extremely useful for combining datasets correctly, but can introduce silent NaN values if index labels do not match as expected — a subtle bug source, covered further in Notebook 4's discussion of missing data.

---

5. Selection and Indexing with loc and iloc#

5.1 Why Two Indexers Exist#

Pandas provides two distinct indexers to remove ambiguity between label-based and position-based selection, an ambiguity that plain bracket indexing (df[...]) can create, especially when the Index itself consists of integers.

5.2 loc Examples#

Python
CopyAsk AI
df_indexed = df.set_index("name")

print(df_indexed.loc["Carol", "age"])          # 47 - single value by label
print(df_indexed.loc["Bob":"David"])            # rows from Bob through David, INCLUSIVE
print(df_indexed.loc[["Alice", "David"], "city"])  # specific rows, specific column

Expected output:

code
Ask AICopy
47
       age city
name
Bob     32   LA
Carol   47   NY
David   51   SF

name
Alice    NY
David    SF
Name: city, dtype: object

A critical and frequently surprising detail: label-based slicing with .loc is inclusive of the end label ("Bob":"David" includes David), unlike Python's normal slicing convention and unlike .iloc's position-based slicing, which remains exclusive of the endpoint, exactly like NumPy and native Python.

5.3 iloc Examples#

Python
CopyAsk AI
print(df.iloc[2, 1])        # 47 - row at position 2, column at position 1
print(df.iloc[1:3])          # rows at positions 1 and 2 (position 3 excluded)
print(df.iloc[:, 0])          # all rows, first column only

5.4 Choosing Between loc and iloc#

Use .loc when you know the meaningful label you want (a specific customer ID, a specific date). Use .iloc when you need positional access (the first 10 rows, the last column, every other row via slicing). Mixing the two concepts using plain bracket indexing on an integer-labeled DataFrame is a common source of ambiguity and bugs, discussed further in Section 12.

---

6. Filtering with Boolean Conditions#

6.1 The Basic Pattern#

Exactly as with NumPy boolean masking (Notebook 1, Section 4.2), a condition applied to a DataFrame column produces a boolean Series, which can then be used to select matching rows:

Python
CopyAsk AI
df = pd.DataFrame({
    "name": ["Alice", "Bob", "Carol", "David", "Eve"],
    "age": [25, 32, 47, 51, 29],
    "revenue": [1200, 3400, 800, 5600, 2100]
})

high_revenue = df[df["revenue"] > 2000]
print(high_revenue)

Expected output:

code
Ask AICopy
    name  age  revenue
1    Bob   32     3400
3  David   51     5600
4    Eve   29     2100

6.2 Combining Multiple Conditions#

As with NumPy (Notebook 1, Section 5.3), combining conditions requires & (and), | (or), and ~ (not), with each individual condition wrapped in parentheses, not Python's native and/or keywords, which do not work correctly on Series:

Python
CopyAsk AI
filtered = df[(df["age"] > 28) & (df["revenue"] > 2000)]
print(filtered)

Expected output:

code
Ask AICopy
    name  age  revenue
1    Bob   32     3400
3  David   51     5600
4    Eve   29     2100

6.3 The isin Method#

For checking membership against a list of values, .isin() is clearer and less error-prone than chaining multiple == conditions with |:

Python
CopyAsk AI
target_names = ["Alice", "Carol", "Eve"]
subset = df[df["name"].isin(target_names)]
print(subset)

6.4 Filtering with query#

For more complex, string-based conditions, .query() offers a more readable alternative, particularly useful with several combined conditions:

Python
CopyAsk AI
result = df.query("age > 28 and revenue > 2000")
print(result)

This produces identical results to Section 6.2's boolean masking approach; the choice between the two is largely stylistic, though .query() can be more readable for complex, multi-condition filters.

---

7. Adding, Modifying, and Dropping Columns#

7.1 Adding a New Column#

New columns can be added via direct vectorized assignment, following the exact same principle as Notebook 1's vectorized operations:

Python
CopyAsk AI
df["revenue_per_year_of_age"] = df["revenue"] / df["age"]
print(df)

7.2 Modifying Values Conditionally#

np.where (from NumPy, Notebook 1) is commonly used with Pandas to assign different values based on a condition, vectorized across the whole column at once:

Python
CopyAsk AI
import numpy as np

df["revenue_tier"] = np.where(df["revenue"] > 2000, "high", "low")
print(df[["name", "revenue", "revenue_tier"]])

Expected output:

code
Ask AICopy
    name  revenue revenue_tier
0  Alice     1200          low
1    Bob     3400         high
2  Carol      800          low
3  David     5600         high
4    Eve     2100         high

7.3 Dropping Columns and Rows#

Python
CopyAsk AI
df_dropped_col = df.drop(columns=["revenue_per_year_of_age"])
df_dropped_row = df.drop(index=0)  # drops the row with index label 0

By default, .drop() returns a new DataFrame rather than modifying the original in place, following Pandas's general (though not universal) preference for returning new objects rather than mutating existing ones, a theme discussed further in Section 12.

7.4 The SettingWithCopyWarning#

A frequently encountered Pandas warning arises from an ambiguous pattern: modifying a DataFrame that was itself produced by a prior filtering or slicing operation, where Pandas cannot always determine whether that prior result was a view or an independent copy (directly echoing the view-versus-copy distinction from Notebook 1, Section 4.4).

Python
CopyAsk AI
subset = df[df["age"] > 30]
subset["new_column"] = 1  # may raise: SettingWithCopyWarning

The safe fix is to explicitly call .copy() immediately after any filtering operation you intend to modify further:

Python
CopyAsk AI
subset = df[df["age"] > 30].copy()
subset["new_column"] = 1  # no warning, safe and unambiguous

---

8. Reading and Writing Data#

8.1 CSV Files#

Python
CopyAsk AI
df = pd.read_csv("data/sales.csv")
df.to_csv("data/sales_cleaned.csv", index=False)

The index=False argument is important: by default, .to_csv() writes the DataFrame's Index as an extra column, which is usually not desired unless the Index itself holds meaningful data (Section 4.2).

8.2 Common read_csv Parameters#

Python
CopyAsk AI
df = pd.read_csv(
    "data/sales.csv",
    parse_dates=["order_date"],      # automatically convert to datetime
    usecols=["order_date", "revenue", "region"],  # load only needed columns
    dtype={"region": "category"},    # specify dtype to save memory
    na_values=["N/A", "missing", "-"]  # additional strings to treat as missing
)

8.3 JSON and Excel#

Python
CopyAsk AI
df_json = pd.read_json("data/customers.json")
df_excel = pd.read_excel("data/report.xlsx", sheet_name="Q1")

df.to_json("data/output.json", orient="records")
df.to_excel("data/output.xlsx", index=False)

8.4 Reading Only What You Need#

For very large files, specifying usecols and appropriate dtypes at read time (Section 8.2), rather than loading everything and filtering afterward, can substantially reduce both memory usage and load time, a theme revisited in the large-dataset optimization notebook later in this course.

---

9. Inspecting a DataFrame#

Before any real analysis, a standard set of inspection methods should be applied to any newly loaded dataset.

Python
CopyAsk AI
df = pd.read_csv("data/sales.csv")

df.head()          # first 5 rows
df.tail(3)          # last 3 rows
df.shape            # (n_rows, n_columns)
df.info()           # dtypes, non-null counts, memory usage
df.describe()       # summary statistics for numeric columns
df.columns          # list of column names
df.dtypes           # dtype of every column
df.isna().sum()     # count of missing values per column
df["region"].value_counts()  # frequency count of each category

.info() in particular is one of the most useful first commands to run on any new dataset: it immediately reveals column dtypes (flagging columns that may need type conversion, per Notebook 4) and non-null counts (immediately revealing which columns have missing data, before any further analysis begins).

---

10. Hands-On: A Complete Exploration Workflow#

This example ties together selection, filtering, column creation, and inspection into a first-pass exploration of a small dataset, directly extending the retail example from Notebook 0, Section 7.

Python
CopyAsk AI
import pandas as pd
import numpy as np

df = pd.DataFrame({
    "customer_id": [101, 102, 103, 104, 105, 106, 107, 108],
    "region": ["North", "South", "East", "West", "North", "South", "East", "West"],
    "signup_date": pd.to_datetime([
        "2023-01-15", "2023-02-20", "2023-01-05", "2023-03-10",
        "2023-02-01", "2023-01-25", "2023-03-15", "2023-02-10"
    ]),
    "total_spend": [450, 1200, 300, 780, 950, 210, 1600, 540],
    "num_orders": [3, 8, 2, 5, 6, 1, 11, 4]
})

# Step 1: inspect
print(df.info())
print(df.describe())

# Step 2: derive a new feature (vectorized, per Notebook 1, Section 5)
df["avg_order_value"] = (df["total_spend"] / df["num_orders"]).round(2)

# Step 3: filter to a specific subgroup
high_value = df[df["total_spend"] > 500]

# Step 4: aggregate by region (previewed here, covered fully in Notebook 3)
region_summary = df.groupby("region")["total_spend"].agg(["mean", "sum", "count"])

print("\nHigh-value customers:")
print(high_value[["customer_id", "region", "total_spend"]])

print("\nRegional summary:")
print(region_summary)

Expected output (abbreviated):

code
Ask AICopy
High-value customers:
   customer_id region  total_spend
1          102  South         1200
3          104   West          780
4          105  North          950
6          107   East         1600
7          108   West          540

Regional summary:
          mean   sum  count
region
East    950.0  1900      2
North   700.0  1400      2
South   705.0  1410      2
West    660.0  1320      2

This short workflow, inspect, derive, filter, aggregate, is the template underlying nearly every exploratory analysis task performed in this course from here forward.

---

11. Case Studies#

11.1 Case Study: A Silent Data Loss From Chained Indexing#

An analyst writes df[df["region"] == "West"]["total_spend"] = 0 intending to zero out spend for West-region customers, but the assignment silently has no effect on the original DataFrame, and no error is raised.

Diagnosis: This is a classic instance of chained indexing: df[df["region"] == "West"] first creates an intermediate object (potentially a copy, per the ambiguity described in Section 7.4), and the subsequent ["total_spend"] = 0 assignment happens on that intermediate object, not necessarily on df itself. Pandas often (though not always reliably) issues a SettingWithCopyWarning in this situation, but the assignment's actual effect on the original DataFrame is undefined and should never be relied upon.

Lesson: The correct, unambiguous approach is a single .loc call combining the row filter and column selection in one indexing operation: df.loc[df["region"] == "West", "total_spend"] = 0. This directly targets the original DataFrame in a single step, avoiding the intermediate object entirely, and is the pattern that should always be used for conditional assignment.

11.2 Case Study: Index Misalignment After a Manual Merge Attempt#

A team manually reorders one DataFrame (sorting customers by revenue) before adding a column from a second, unsorted DataFrame using simple positional assignment (df1["new_col"] = df2["some_col"].values), intending to attach corresponding values. The resulting column is completely misaligned with the correct customers.

Diagnosis: Using .values (Section 2.3) strips away the Series's Index entirely, reducing the assignment to pure positional matching, which silently ignores the fact that df1 had been reordered relative to df2. Had the team instead assigned df2["some_col"] directly (without .values), Pandas's automatic index alignment (Section 4.3) would have either correctly matched rows by their shared index labels or, more safely, revealed a mismatch as NaN values rather than silently misaligned data.

Lesson: Stripping away the Index via .values before combining data discards exactly the safety mechanism (label-based alignment) that Pandas provides over raw NumPy arrays; this should only be done when you are certain both objects share identical row order and length, and even then, an explicit merge (Notebook 3) on a shared key column is almost always the safer choice than relying on row order at all.

11.3 Case Study: Loading an Entire Multi-Gigabyte File Unnecessarily#

An analyst needs only three columns (date, region, revenue) from a 40-column, several-gigabyte CSV file, but loads the entire file with a plain pd.read_csv(path) call, causing the analysis notebook to run out of available memory.

Diagnosis: Every one of the 40 columns, including many unneeded text and identifier columns, was loaded into memory in full, even though only 3 were ever used, directly illustrating the inefficiency flagged in Section 8.4.

Lesson: Specifying usecols=["date", "region", "revenue"] at read time loads only the required data directly from disk, often reducing memory usage dramatically without requiring any change to the downstream analysis code, and is one of the simplest, highest-leverage optimizations available when working with wide datasets.

---

12. Common Misconceptions and Pitfalls#

---

13. Glossary#

---

14. Exercises#

Exercise 1 (Conceptual)#

Explain why df.loc["Bob":"David"] includes the row labeled "David," while df.iloc[1:3] excludes the row at position 3, using the concepts from Section 5.2 and 5.3.

<details> <summary>Solution</summary>

.loc slicing is label-based and, by Pandas convention, is inclusive of both the start and end labels, so "Bob":"David" includes every row from Bob through David, David included (Section 5.2). .iloc slicing is position-based and follows the same convention as Python's native list slicing and NumPy array slicing (Notebook 1), where the end position is exclusive, so 1:3 includes positions 1 and 2 but stops before position 3 (Section 5.3). This is one of the most important distinctions between the two indexers and a common source of off-by-one errors when switching between them.

</details>

---

Exercise 2 (Conceptual)#

A colleague writes df[df["status"] == "active"]["priority"] = "high" and is confused why the original df is unchanged. Explain what went wrong and provide the corrected code.

<details> <summary>Solution</summary>

This is the chained indexing problem described in Section 11.1: df[df["status"] == "active"] creates an intermediate object first, and the subsequent ["priority"] = "high" assignment is applied to that intermediate object, not reliably to df itself. The corrected, safe approach combines both the row filter and column selection into a single .loc call: df.loc[df["status"] == "active", "priority"] = "high", which unambiguously targets the original DataFrame in one step.

</details>

---

Exercise 3 (Coding)#

Using the customer DataFrame from Section 10, write a single .loc-based expression that sets avg_order_value to 0 for any customer with num_orders equal to 0, avoiding a division-by-zero issue that would otherwise occur when computing total_spend / num_orders.

<details> <summary>Solution</summary>

Python
CopyAsk AI
import pandas as pd
import numpy as np

df = pd.DataFrame({
    "customer_id": [101, 102, 103],
    "total_spend": [450, 0, 300],
    "num_orders": [3, 0, 2]
})

# Safely compute, avoiding division by zero for now
df["avg_order_value"] = np.where(
    df["num_orders"] > 0,
    df["total_spend"] / df["num_orders"],
    0
)
print(df)

Using np.where (Section 7.2) here is preferable to computing the division first and then patching zero-order rows afterward with .loc, since the direct division would generate a division-by-zero warning (or an infinite value) before it could be corrected; np.where evaluates the safe branch for each row without that intermediate problematic computation ever being relied upon for the zero-order rows.

</details>

---

Exercise 4 (Applied Thinking)#

You receive a 25-column, 8 GB CSV file, but your analysis only requires the transaction_date, customer_id, and amount columns, and you know customer_id should be treated as a category rather than a raw integer for memory efficiency. Write the pd.read_csv() call that loads this data as efficiently as possible, referencing the relevant section of this notebook.

<details> <summary>Solution</summary>

Python
CopyAsk AI
import pandas as pd

df = pd.read_csv(
    "transactions.csv",
    usecols=["transaction_date", "customer_id", "amount"],
    parse_dates=["transaction_date"],
    dtype={"customer_id": "category"}
)

This combines three techniques from Section 8: usecols to avoid loading the other 22 unneeded columns (Section 8.2, 8.4, and the case study in Section 11.3), parse_dates to avoid a separate later conversion step, and an explicit dtype specification to store customer_id in a memory-efficient categorical representation from the moment the data is loaded, rather than as a generic, more memory-hungry type.

</details>

---

15. Quiz#

Q1. What is the relationship between a Pandas Series and a NumPy array? a) They are entirely unrelated data structures b) A Series wraps a NumPy array and adds a labeled Index c) A Series is a Python list with extra methods d) A NumPy array is built on top of a Series

Q2. Which indexer uses label-based, inclusive-of-endpoint slicing? a) .iloc[] b) .loc[] c) Plain bracket indexing df[] d) .values

Q3. What is the safest way to conditionally assign a value to a subset of rows in a DataFrame? a) df[condition]["column"] = value b) df.loc[condition, "column"] = value c) df["column"][condition] = value d) All three are equally safe

Q4. What does specifying usecols in pd.read_csv() accomplish? a) It renames the columns after loading b) It loads only the specified columns, reducing memory usage c) It sorts the columns alphabetically d) It has no effect on memory usage, only on display order

Q5. True or False: Using .values to extract data from a Series before combining it with another DataFrame preserves Pandas's automatic index-based alignment.

<details> <summary>Answers</summary>

1. b) A Series wraps a NumPy array and adds a labeled Index
2. b) .loc[]
3. b) df.loc[condition, "column"] = value
4. b) It loads only the specified columns, reducing memory usage
5. False — .values strips away the Index entirely, per Section 11.2, removing the alignment safety net that Pandas would otherwise provide

</details>

---

16. Summary#

• Pandas builds the Series and DataFrame on top of NumPy arrays, adding a labeling system (the Index) and independent per-column dtypes to support realistic, heterogeneous tabular data.
• The Index is functionally important, not merely cosmetic, since it governs automatic alignment during arithmetic and combination operations.
• .loc (label-based, inclusive slicing) and .iloc (position-based, exclusive slicing) provide unambiguous selection, and should generally be preferred over plain bracket indexing for anything beyond simple column selection.
• Boolean filtering, using &, |, ~, .isin(), or .query(), follows the same vectorized principles introduced for NumPy in Notebook 1.
• Chained indexing is a common source of silent bugs during conditional assignment; a single combined .loc call is the safe, unambiguous alternative.
• Reading data efficiently, using usecols, dtype, and parse_dates at load time, can substantially reduce memory usage and processing time for large files.
• A standard inspection routine (.head(), .info(), .describe(), .isna().sum(), .value_counts()) should be applied to every new dataset before analysis begins.

---

17. Further Reading#

• Pandas official documentation: https://pandas.pydata.org/docs/
• Wes McKinney, Python for Data Analysis, Chapters 5 and 6 (Getting Started with Pandas; Data Loading, Storage, and File Formats)
• Pandas official documentation on indexing: https://pandas.pydata.org/docs/user_guide/indexing.html
• Pandas official documentation on the SettingWithCopyWarning: https://pandas.pydata.org/docs/user_guide/indexing.html#returning-a-view-versus-a-copy

---

Next Up#

Notebook 3: High-Performance Data Manipulation — vectorization versus apply, groupby mechanics in depth, merging and joining strategies, and method chaining, extending the aggregation preview from Section 10 and the case study in Section 11.3 of this notebook into a full, performance-focused treatment.

---

Course: Data Analytics | Notebook 2 of 13