<!-- persona-dash wiki source
     doc_id:   python/chapter-3-loading-inspecting-and-selecting-data-with-pandas
     title:    Chapter 3 — Loading, Inspecting and Selecting Data with pandas
-->

| | |
|---|---|
| **Level** | 0+: you've worked through Chapters 1 and 2 |
| **Time** | 6–8 hours, best spread over three sessions |
| **You need** | Your `python-data-analysis` project with the libraries from Chapter 2 installed in `.venv` |
| **Project milestone** | Harbour Pharmacy's real sales file loaded and inspected, a written data-quality log, answers to five business questions, and a reusable `first_look.py` script |

---

## Where we are

In Chapter 2 you met pandas using data you typed in or simulated. Data like that is always tidy, because you made it. This chapter is where the real Harbour Pharmacy sales file arrives: a week of transactions exported from the pharmacies' point-of-sale system.

You'll learn three skills that together make up a large slice of any analyst's day:

1. **Getting data in**: understanding file paths and loading CSV, Excel and JSON files.
2. **Inspecting it properly**: a systematic first-look checklist that surfaces the problems hiding in real data *before* they end up in a report.
3. **Selecting exactly what you need**: columns, rows by position, rows by label, and rows that meet conditions.

Along the way you'll find several genuine problems in the file. Finding them is the point. You'll record them in a **data-quality log**, and Chapter 4 fixes them.

---

## 3.1 The analyst's workflow, and where this chapter fits

Almost every analysis follows the same loop:

```text
  Ask a question → Get the data → Inspect it → Clean it → Analyse it → Communicate
         ↑                                                                 │
         └──────────────── new questions from what you found ──────────────┘
```

This chapter covers **Get** and **Inspect**, plus the **selection** skills every later step depends on.

Inspection deserves special respect. Think of a pharmacy receiving a wholesale delivery: before anything goes on the shelves, someone checks the delivery against the invoice. Are all the boxes here? Are there extras? Any damaged packs, missing labels, short-dated stock? Nobody would skip that step and hope for the best. Data deserves the same treatment. A report built on unchecked data can look perfectly professional and still be wrong, and the person reading it has no way to tell.

---

## 3.2 Getting the Harbour Pharmacy file

### What a CSV file is

**CSV** stands for **comma-separated values**. It's a plain text file where:

- each line is one row of the table,
- commas separate the values within a row, and
- the first line usually holds the column names (the **header**).

CSV is the most common way data moves between systems, because almost every program can read and write it. It has one important limitation: **it's just text**. The file stores no information about types, so `4.50` is simply four characters. When pandas reads a CSV, it has to *guess* each column's type from the values it sees. Keep that in mind; it explains several things you'll discover shortly.

### Create the data file

1. In VS Code, create a new file at `data/pharmacy_sales.csv` (inside the `data` folder you made in Chapter 1).
2. Copy the block below into it exactly, and save.

```text
order_id,date,branch,category,product,unit_price,quantity,payment_method,loyalty_member
1001,2026-03-02,Petone,Cold & Flu,Vapour Rub 50g,9.99,1,EFTPOS,Yes
1002,2026-03-02,Lambton Quay,Cold & Flu,Throat Lozenges 16pk,8.99,2,Card,Yes
1003,2026-03-02,Karori,Cold & Flu,Throat Lozenges 16pk,8.99,4,Card,No
1004,2026-03-02,Karori,Cold & Flu,Throat Lozenges 16pk,8.99,1,Card,Yes
1005,2026-03-03,Petone,First Aid,Plasters 40pk,5.99,1,EFTPOS,Yes
1006,2026-03-03,Lambton Quay,Vitamins,Multivitamin 60tab,24.99,1,EFTPOS,No
1007,2026-03-03,Cuba Street,Pain Relief,Ibuprofen 200mg 24pk,6.90,1,Card,Yes
1008,2026-03-03,Cuba Street,Cold & Flu,Cold & Flu Capsules 24pk,14.50,3,EFTPOS,No
1009,2026-03-03,Lambton Quay,Vitamins,Vitamin C 1000mg 60tab,18.99,4,,No
1010,2026-03-03,Cuba Street,Cold & Flu,Throat Lozenges 16pk,8.99,1,EFTPOS,No
1011,2026-03-03,Cuba Street,First Aid,Antiseptic Cream 30g,7.49,1,Card,Yes
1012,2026-03-03,Cuba Street,Vitamins,Vitamin C 1000mg 60tab,18.99,1,Card,No
1013,2026-03-04,Lambton Quay,First Aid,Plasters 40pk,5.99,4,Cash,Yes
1014,2026-03-04,Lambton Quay,Cold & Flu,Throat Lozenges 16pk,8.99,2,Card,No
1015,2026-03-04,Cuba Street,Pain Relief,Paracetamol 500mg 20pk,4.50,1,EFTPOS,No
1016,2026-03-04,Karori,Vitamins,Vitamin C 1000mg 60tab,18.99,1,Cash,Yes
1017,2026-03-04,Petone,First Aid,Plasters 40pk,5.99,1,Card,No
1017,2026-03-04,Petone,First Aid,Plasters 40pk,5.99,1,Card,No
1018,2026-03-05,Lambton Quay,First Aid,Plasters 40pk,5.99,3,Card,No
1019,2026-03-05,Cuba Street,Pain Relief,Paracetamol 500mg 20pk,4.50,3,Card,Yes
1020,2026-03-05,Karori,Vitamins,Vitamin C 1000mg 60tab,18.99,2,Card,Yes
1021,2026-03-05,Petone,Vitamins,Vitamin C 1000mg 60tab,18.99,2,EFTPOS,Yes
1022,2026-03-06,Lambton Quay,Pain Relief,Paracetamol 500mg 20pk,4.50,1,Card,Yes
1023,2026-03-06,cuba street,Cold & Flu,Vapour Rub 50g,9.99,4,EFTPOS,Yes
1024,2026-03-06,Lambton Quay,Cold & Flu,Throat Lozenges 16pk,8.99,2,Card,No
1025,2026-03-06,Karori,Cold & Flu,Cold & Flu Capsules 24pk,14.50,1,Card,No
1026,2026-03-06,Lambton Quay,First Aid,Plasters 40pk,5.99,2,Card,No
1027,2026-03-06,Petone,Skincare,Moisturiser 100ml,16.50,4,Card,Yes
1028,2026-03-06,Cuba Street,Pain Relief,Paracetamol 500mg 20pk,4.50,1,Cash,Yes
1029,2026-03-06,Lambton Quay,Pain Relief,Paracetamol 500mg 20pk,4.50,2,EFTPOS,No
1030,2026-03-07,Cuba Street,Cold & Flu,Throat Lozenges 16pk,8.99,1,EFTPOS,No
1031,2026-03-07,Cuba Street,Pain Relief,Paracetamol 500mg 20pk,4.50,,EFTPOS,No
1032,2026-03-07,Cuba Street,Cold & Flu,Vapour Rub 50g,9.99,3,EFTPOS,No
1033,2026-03-07,Karori,Cold & Flu,Throat Lozenges 16pk,8.99,1,EFTPOS,Yes
1034,2026-03-07,Lambton Quay,Cold & Flu,Cold & Flu Capsules 24pk,14.50,1,EFTPOS,No
1035,2026-03-07,Lambton Quay,First Aid,Antiseptic Cream 30g,7.49,1,Cash,Yes
1036,2026-03-07,Lambton Quay,Skincare,Sunscreen SPF50 200ml,22.99,4,Card,No
1037,2026-03-08,Lambton Quay,Skincare,Moisturiser 100ml,16.50,1,Card,No
1038,2026-03-08,Petone,Pain Relief,Paracetamol 500mg 20pk,4.50,4,EFTPOS,Yes
1039,2026-03-08,Cuba Street,Cold & Flu,Vapour Rub 50g,9.99,1,Card,No
1040,2026-03-08,Lambton Quay,First Aid,Plasters 40pk,5.99,2,EFTPOS,Yes
```

That's 41 transactions across one week (Monday 2 to Sunday 8 March 2026) for the four branches. The columns:

| Column | Meaning |
|---|---|
| `order_id` | the transaction number from the till system |
| `date` | the date of the sale |
| `branch` | which pharmacy made the sale |
| `category` | the product category |
| `product` | the product name |
| `unit_price` | the price of one unit, in NZD |
| `quantity` | how many units were bought |
| `payment_method` | EFTPOS, Card or Cash |
| `loyalty_member` | whether the customer used a loyalty card (Yes/No) |

> **Real data is messy, and this file is no exception.** Some problems are deliberately hidden in it. Resist the urge to hunt for them by eye; the point of this chapter is to build a process that finds them *for* you, even in a file with a million rows.

### House rule: raw data is read-only

**Never edit files in `data/` by hand**, even to fix an obvious typo. Every change should happen in code, so it's recorded, repeatable and visible to anyone reviewing your work. That's your audit trail. If next week's file has the same problem, your code fixes it again automatically; a hand edit would have to be remembered and repeated.

---

## 3.3 File paths: telling Python where the file is

### The idea

A **path** is the address of a file on your computer. There are two kinds:

- An **absolute path** spells out the full address from the top of the drive, such as `C:\Users\Eric\Documents\python-data-analysis\data\pharmacy_sales.csv` on Windows, or `/Users/eric/Documents/python-data-analysis/data/pharmacy_sales.csv` on macOS.
- A **relative path** is given *relative to where Python is currently standing*, such as `data/pharmacy_sales.csv`.

"Where Python is currently standing" is called the **current working directory** (often shortened to *cwd*). A relative path is like directions that begin "from where you are now, go…": it only works if you start in the right place.

### Where is Python standing?

This is the source of most "file not found" errors, so it's worth being precise:

| How you run the code | Current working directory |
|---|---|
| A script, run as `python ch03/script.py` | the folder your **terminal** was in when you typed the command (usually the project folder) |
| A notebook (JupyterLab or VS Code) | the folder the **notebook file** lives in (here, `ch03/`) |

So from a notebook in `ch03/`, the data file is one level up and then into `data/`. In path language, `..` means "the parent folder" (one level up):

```text
python-data-analysis/          ← ".." from inside ch03 means this folder
├── ch03/
│   └── ch03_notebook.ipynb    ← the notebook stands here
└── data/
    └── pharmacy_sales.csv     ← so the file is at ../data/pharmacy_sales.csv
```

### `pathlib`: paths as objects

Python's standard library includes `pathlib`, which treats paths as objects with useful abilities of their own. Create a notebook, `ch03/ch03_loading.ipynb`, and start with:
```python
from pathlib import Path

print(Path.cwd().name)          # the name of the folder we're standing in

DATA_DIR = Path("..") / "data"
OUTPUT_DIR = Path("..") / "output"
csv_path = DATA_DIR / "pharmacy_sales.csv"

print(csv_path)
print(csv_path.exists())
print(csv_path.name, csv_path.suffix)
```

**Output:**

```text
ch03
../data/pharmacy_sales.csv
True
pharmacy_sales.csv .csv
```

- `Path.cwd()` returns the full current working directory; `.name` gives just its last part. If this prints something other than `ch03`, your notebook is running from somewhere unexpected.
- The `/` operator **joins** path pieces. pathlib inserts the right separator for your system: backslashes on Windows, forward slashes on macOS and Linux. (On Windows, the second line prints `..\data\pharmacy_sales.csv`.) Code written this way runs unchanged on all three.
- `.exists()` checks whether the file is really there: a good first test whenever a file won't load.
- `.name` is the file name and `.suffix` is its extension.

Writing `DATA_DIR` and `OUTPUT_DIR` in capitals is a Python convention for **constants**: values you set once near the top and never change.

> **Watch out for Windows paths in plain strings.** Inside ordinary Python text, a backslash starts a special code: `"\n"` means "new line" and `"\t"` means "tab". So `"C:\new\transactions.csv"` quietly turns into something broken. Three safe options: use `pathlib` as above, use forward slashes (`"C:/new/transactions.csv"`, which Windows accepts), or put an `r` before the quotes to make a **raw string** (`r"C:\new\transactions.csv"`), where backslashes are left alone.

### When the path is wrong
```python
pd.read_csv("pharmacy_sales.csv")
```

**Output:**

```text
FileNotFoundError: [Errno 2] No such file or directory: 'pharmacy_sales.csv'
```

`Errno 2` is the operating system's code for "no such file". The notebook was standing in `ch03/`, where there's no file by that name. The fix is almost always one of: check the current working directory, check the spelling, and check `.exists()`.

(In a notebook, error messages appear as a longer, coloured traceback. From here on, error outputs in this chapter show just the final line, which is the part to read first.)

---

## 3.4 Reading a CSV with pandas

### Worked example 3.1: the first load

```python
import pandas as pd

sales = pd.read_csv(csv_path)
sales.head()
```

**Output:**

```text
   order_id        date        branch    category               product  unit_price  quantity payment_method loyalty_member
0      1001  2026-03-02        Petone  Cold & Flu        Vapour Rub 50g        9.99       1.0         EFTPOS            Yes
1      1002  2026-03-02  Lambton Quay  Cold & Flu  Throat Lozenges 16pk        8.99       2.0           Card            Yes
2      1003  2026-03-02        Karori  Cold & Flu  Throat Lozenges 16pk        8.99       4.0           Card             No
3      1004  2026-03-02        Karori  Cold & Flu  Throat Lozenges 16pk        8.99       1.0           Card            Yes
4      1005  2026-03-03        Petone   First Aid         Plasters 40pk        5.99       1.0         EFTPOS            Yes
```

One line, and the file is a DataFrame. `.head()` shows the first five rows, `.head(10)` the first ten.

### Under the hood: what `read_csv` just did

1. **Opened the file** and read it as text.
2. **Split each line** at the commas, and used the first line as the column names.
3. **Treated empty values as missing**, recording them as `NaN`.
4. **Inferred a type for each column** by looking at all its values:
   - every value a whole number → `int64`
   - any decimals, **or any missing values among the numbers** → `float64`
   - anything else → `str` (text)
5. **Added a default index** of 0, 1, 2, … down the left side.

Step 4 is where surprises come from. pandas has no way to know that a column *should* be whole numbers, or that `2026-03-02` is a date. It makes its best guess from what it sees, and you'll check those guesses in section 3.5.

### Useful `read_csv` options

`read_csv` has dozens of settings. These are the ones you'll reach for most often:

| Option | What it does | Example |
|---|---|---|
| `sep` | the character between values, if it isn't a comma | `sep=";"` or `sep="\t"` (tab) |
| `usecols` | load only some columns | `usecols=["date", "branch", "quantity"]` |
| `nrows` | load only the first *n* rows (great for peeking at huge files) | `nrows=1000` |
| `parse_dates` | read these columns as dates instead of text | `parse_dates=["date"]` |
| `dtype` | tell pandas a column's type instead of letting it guess | `dtype={"order_id": "str"}` |
| `na_values` | extra values to treat as missing | `na_values=["N/A", "-", "unknown"]` |
| `thousands` | the thousands separator in numbers | `thousands=","` for `1,234` |
| `encoding` | how the text is encoded | `encoding="cp1252"` for some Windows exports |
| `index_col` | use a column as the index | `index_col="order_id"` |
| `skiprows` | skip lines at the top (such as report titles) | `skiprows=3` |

### Worked example 3.2: loading with options

A realistic load for this file reads the `date` column as proper dates:

```python
sales = pd.read_csv(csv_path, parse_dates=["date"])
sales.dtypes
```

**Output:**

```text
order_id                   int64
date              datetime64[us]
branch                       str
category                     str
product                      str
unit_price               float64
quantity                 float64
payment_method               str
loyalty_member               str
dtype: object
```

`date` is now `datetime64[us]`: a proper date-and-time type (the `[us]` means it's stored to microsecond precision). Proper dates let you sort chronologically, pick out weekdays and group by week, as you'll see in section 3.7 and properly in Chapter 8. Every other column has been guessed; look closely at `quantity`, and we'll come back to it.

And a quick peek at just a few columns of a few rows, the way you'd sample a file too big to open comfortably:

```python
peek = pd.read_csv(csv_path, usecols=["date", "branch", "quantity"], nrows=3)
peek
```

**Output:**

```text
         date        branch  quantity
0  2026-03-02        Petone         1
1  2026-03-02  Lambton Quay         2
2  2026-03-02        Karori         4
```

Notice that `quantity` shows whole numbers here. In these three rows nothing is missing, so pandas guessed `int64`; in the full file one value is missing, and the guess changes. Type inference only ever sees the rows you load.

> **Text encoding, briefly.** Computers store text as numbers, and an **encoding** is the agreed table for turning those numbers back into characters. Modern files use **UTF-8**, which pandas assumes by default. Files exported from older Windows software sometimes use a different table, and pandas then stops with a `UnicodeDecodeError`. Adding `encoding="cp1252"` (or `encoding="latin-1"`) usually fixes it. If a CSV saved from Excel shows odd characters like `ï»¿` at the start of the first column name, use `encoding="utf-8-sig"`.

---

## 3.5 The first-look checklist

Every time you receive a dataset, run through the same checklist before you calculate anything. It takes a few minutes and routinely saves hours.

| # | Question | Tools |
|---|---|---|
| 1 | Is it the size I expected? | `.shape` |
| 2 | What does it look like? | `.head()`, `.tail()`, `.sample()` |
| 3 | Did every column get a sensible type? | `.dtypes`, `.info()` |
| 4 | What's missing? | `.isna().sum()` |
| 5 | Are there duplicates? | `.duplicated()`, `.nunique()` |
| 6 | Do the categories look right? | `.value_counts()`, `.unique()` |
| 7 | Are the numbers plausible? | `.describe()` |
| 8 | Do the dates cover the expected period? | `.min()`, `.max()` |

Let's work through it with the Harbour Pharmacy file.

### Check 1: size

```python
sales.shape
```

**Output:**

```text
(41, 9)
```

41 rows and 9 columns. Is that what we expected? The order IDs run from 1001 to 1040, which is 40 orders. **One more row than orders** is the first clue that something's off. Make a note and keep going.

### Check 2: look at it

`.head()` shows the top, `.tail()` the bottom, and `.sample()` a random selection from anywhere, which matters because problems don't politely sit in the first five rows:

```python
sales.tail(3)
```

**Output:**

```text
    order_id       date        branch     category                 product  unit_price  quantity payment_method loyalty_member
38      1038 2026-03-08        Petone  Pain Relief  Paracetamol 500mg 20pk        4.50       4.0         EFTPOS            Yes
39      1039 2026-03-08   Cuba Street   Cold & Flu          Vapour Rub 50g        9.99       1.0           Card             No
40      1040 2026-03-08  Lambton Quay    First Aid           Plasters 40pk        5.99       2.0         EFTPOS            Yes
```

```python
sales.sample(5, random_state=1)
```

**Output:**

```text
    order_id       date       branch     category                 product  unit_price  quantity payment_method loyalty_member
3       1004 2026-03-02       Karori   Cold & Flu    Throat Lozenges 16pk        8.99       1.0           Card            Yes
2       1003 2026-03-02       Karori   Cold & Flu    Throat Lozenges 16pk        8.99       4.0           Card             No
23      1023 2026-03-06  cuba street   Cold & Flu          Vapour Rub 50g        9.99       4.0         EFTPOS            Yes
38      1038 2026-03-08       Petone  Pain Relief  Paracetamol 500mg 20pk        4.50       4.0         EFTPOS            Yes
17      1017 2026-03-04       Petone    First Aid           Plasters 40pk        5.99       1.0           Card             No
```

`random_state=1` is a seed, exactly like NumPy's in Chapter 2: it makes the "random" sample repeatable, so you see the same five rows shown here. Look at the `branch` column in row 23: `cuba street`, all lowercase. Note it.

### Check 3: types

```python
sales.info()
```

**Output:**

```text
<class 'pandas.DataFrame'>
RangeIndex: 41 entries, 0 to 40
Data columns (total 9 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   order_id        41 non-null     int64         
 1   date            41 non-null     datetime64[us]
 2   branch          41 non-null     str           
 3   category        41 non-null     str           
 4   product         41 non-null     str           
 5   unit_price      41 non-null     float64       
 6   quantity        40 non-null     float64       
 7   payment_method  40 non-null     str           
 8   loyalty_member  41 non-null     str           
dtypes: datetime64[us](1), float64(2), int64(1), str(5)
memory usage: 4.8 KB
```

Read this line by line:

- **41 entries**: the row count again.
- **Non-Null Count**: `quantity` and `payment_method` show 40 non-null out of 41, so **each is missing one value**.
- **Dtype**: most types look right. But why is `quantity` a `float64` when quantities are whole numbers? Because of that missing value. As you saw in Chapter 2, pandas' standard missing marker, `NaN`, is a floating-point value, so a whole-number column with even one gap becomes a float column. The dtype itself was a clue pointing at the gap.
- `loyalty_member` is `str`, holding the text "Yes" and "No". It works, but a True/False (`bool`) column would be easier to count and filter. That's a cleaning job for Chapter 4.

### Check 4: missing values

```python
sales.isna().sum()
```

**Output:**

```text
order_id          0
date              0
branch            0
category          0
product           0
unit_price        0
quantity          1
payment_method    1
loyalty_member    0
dtype: int64
```

`.isna()` builds a table of True/False (True where a value is missing), and `.sum()` counts the Trues in each column. To see the *rows* involved:

```python
sales[sales.isna().any(axis=1)]
```

**Output:**

```text
    order_id       date        branch     category                 product  unit_price  quantity payment_method loyalty_member
8       1009 2026-03-03  Lambton Quay     Vitamins  Vitamin C 1000mg 60tab       18.99       4.0            NaN             No
31      1031 2026-03-07   Cuba Street  Pain Relief  Paracetamol 500mg 20pk        4.50       NaN         EFTPOS             No
```

`.any(axis=1)` asks, for each row, "is *any* value across this row True?". That's the "collapse across each row" direction from Chapter 2. The result is a mask marking every row with at least one gap. Order 1009 has no payment method and order 1031 has no quantity.

### Check 5: duplicates

```python
print(sales.duplicated().sum())
print(sales["order_id"].nunique())
```

**Output:**

```text
1
40
```

- `.duplicated()` marks each row that is an **exact copy** of an earlier row. One row is.
- `.nunique()` counts the **distinct** values in a column. There are 40 distinct order IDs across 41 rows, which confirms the mystery from check 1: an order appears twice.

Which one? `keep=False` marks *every* copy, so you can see them side by side:

```python
sales[sales.duplicated(keep=False)]
```

**Output:**

```text
    order_id       date  branch   category        product  unit_price  quantity payment_method loyalty_member
16      1017 2026-03-04  Petone  First Aid  Plasters 40pk        5.99       1.0           Card             No
17      1017 2026-03-04  Petone  First Aid  Plasters 40pk        5.99       1.0           Card             No
```

Order 1017 was recorded twice, identical in every column. In a real job you'd ask the data's owner whether this is a known export glitch (very common) or a genuine repeat purchase. With an identical order ID, it's almost certainly a glitch.

### Check 6: categories

```python
sales["branch"].value_counts()
```

**Output:**

```text
branch
Lambton Quay    15
Cuba Street     12
Petone           7
Karori           6
cuba street      1
Name: count, dtype: int64
```

Five "branches", and Harbour Pharmacy has four. `cuba street` is the same branch as `Cuba Street`, typed differently. To Python they're different pieces of text (remember from Chapter 1: comparison is case-sensitive), so any branch total would split Cuba Street's sales in two.

Check the other text columns the same way. `dropna=False` includes missing values in the count:

```python
sales["payment_method"].value_counts(dropna=False)
```

**Output:**

```text
payment_method
Card      20
EFTPOS    16
Cash       4
NaN        1
Name: count, dtype: int64
```

```python
sales["category"].unique()
```

**Output:**

```text
<ArrowStringArray>
['Cold & Flu', 'First Aid', 'Vitamins', 'Pain Relief', 'Skincare']
Length: 5, dtype: str
```

`.unique()` lists each distinct value once, in order of first appearance. The five categories are all spelled consistently. (The `<ArrowStringArray>` label tells you this text is stored by the pyarrow engine you installed in Chapter 2.)

### Check 7: plausible numbers

```python
sales.describe()
```

**Output:**

```text
          order_id                 date  unit_price   quantity
count    41.000000                   41   41.000000  40.000000
mean   1020.414634  2026-03-05 00:00:00   10.405366   1.925000
min    1001.000000  2026-03-02 00:00:00    4.500000   1.000000
25%    1011.000000  2026-03-03 00:00:00    5.990000   1.000000
50%    1020.000000  2026-03-05 00:00:00    8.990000   1.000000
75%    1030.000000  2026-03-07 00:00:00   14.500000   3.000000
max    1040.000000  2026-03-08 00:00:00   24.990000   4.000000
std      11.556331                  NaN    5.680527   1.163273
```

Ask whether each number makes sense for the business:

- **unit_price** runs from $4.50 to $24.99. Plausible for pharmacy retail. No zero or negative prices.
- **quantity** runs from 1 to 4, with a count of 40 (the missing value again). No zero or negative quantities, and no absurd bulk orders like 400.
- **order_id** statistics are meaningless (IDs are labels), apart from confirming the range 1001 to 1040.

Because `date` is now a real date type, `describe()` includes it as well, showing the earliest, middle and latest dates (pandas leaves the standard deviation row blank, shown as `NaN`, for dates).

Nothing implausible, which is itself a useful finding.

### Check 8: dates

```python
print(sales["date"].min())
print(sales["date"].max())
print(sales["date"].dt.day_name().value_counts())
```

**Output:**

```text
2026-03-02 00:00:00
2026-03-08 00:00:00
date
Tuesday      8
Friday       8
Saturday     7
Wednesday    6
Monday       4
Thursday     4
Sunday       4
Name: count, dtype: int64
```

Monday 2 March to Sunday 8 March: exactly the week we were promised, with sales on all seven days. `.dt` gives access to date tools for a date column, and `day_name()` turns each date into its weekday. (Chapter 8 explores `.dt` properly.)

### The data-quality log

Now write down what you found. Add a Markdown cell to your notebook:

```text
## Data-quality log: pharmacy_sales.csv (week of 2 March 2026)

| # | Issue                                   | Rows affected        | Impact                                    | Planned fix (Chapter 4)          |
|---|-----------------------------------------|----------------------|-------------------------------------------|----------------------------------|
| 1 | Order 1017 duplicated exactly           | 1 extra row          | Inflates counts and revenue               | Drop exact duplicates            |
| 2 | Branch "cuba street" in lowercase       | 1 row (order 1023)   | Splits Cuba Street's totals in two        | Standardise branch capitalisation|
| 3 | quantity missing                        | 1 row (order 1031)   | Revenue unknown for that sale; qty is float | Ask the branch, or exclude       |
| 4 | payment_method missing                  | 1 row (order 1009)   | Payment mix slightly incomplete           | Label as "Unknown"               |
| 5 | loyalty_member stored as "Yes"/"No" text | all rows            | Awkward to count and filter               | Convert to True/False            |
```

Why write a log first and fix things later? Three reasons:

1. **Separation of steps.** Inspection answers "what's wrong?"; cleaning answers "what do we do about it?". Mixing them makes both harder to review.
2. **Some fixes need a human decision.** Should the sale with no quantity be dropped, or should someone ring the Cuba Street branch? The log is how you raise that question.
3. **Communication.** A log like this, handed to a manager or a data engineer, is a professional deliverable in its own right. It shows exactly how trustworthy the numbers are.

---

## 3.6 Selecting data

With a clear picture of the data, you can start pulling out the parts a question needs. pandas gives you several selection tools; this section explains each and when to use it.

### Selecting columns

You met these in Chapter 2:

```python
sales["product"].head(3)
```

**Output:**

```text
0          Vapour Rub 50g
1    Throat Lozenges 16pk
2    Throat Lozenges 16pk
Name: product, dtype: str
```

```python
sales[["date", "branch", "product", "quantity"]].head(3)
```

**Output:**

```text
        date        branch               product  quantity
0 2026-03-02        Petone        Vapour Rub 50g       1.0
1 2026-03-02  Lambton Quay  Throat Lozenges 16pk       2.0
2 2026-03-02        Karori  Throat Lozenges 16pk       4.0
```

One name in single brackets gives a **Series**; a list of names in double brackets gives a **DataFrame**, with the columns in the order you listed them.

You'll also see **attribute access** in other people's code, such as `sales.branch`. It works for simple column names, but it fails for names containing spaces, and it silently gives you the wrong thing when a column shares its name with a DataFrame method (a column called `count`, for example, would clash with the `.count()` method). Square brackets always work, so stick with those.

### The index, revisited

Every DataFrame has an **index**: the row labels down the left. `read_csv` gave `sales` the default 0, 1, 2, … . Often a column makes a more meaningful label, and `set_index()` promotes it:

```python
orders = sales.set_index("order_id")
orders.head(3)
```

**Output:**

```text
               date        branch    category               product  unit_price  quantity payment_method loyalty_member
order_id                                                                                                               
1001     2026-03-02        Petone  Cold & Flu        Vapour Rub 50g        9.99       1.0         EFTPOS            Yes
1002     2026-03-02  Lambton Quay  Cold & Flu  Throat Lozenges 16pk        8.99       2.0           Card            Yes
1003     2026-03-02        Karori  Cold & Flu  Throat Lozenges 16pk        8.99       4.0           Card             No
```

`order_id` has moved from being a column to being the row labels. `set_index()` returns a *new* DataFrame, so `sales` itself is unchanged; that's why we stored the result under a new name.

One catch: **index labels don't have to be unique**. Look what happens with the duplicated order:

```python
print(orders.index.is_unique)
orders.loc[1017]
```

**Output:**

```text
False
               date  branch   category        product  unit_price  quantity payment_method loyalty_member
order_id                                                                                                 
1017     2026-03-04  Petone  First Aid  Plasters 40pk        5.99       1.0           Card             No
1017     2026-03-04  Petone  First Aid  Plasters 40pk        5.99       1.0           Card             No
```

Asking for label 1017 returns *two* rows. Any code that expects one row per order would quietly double-count this one. That's another reason duplicates go at the top of the cleaning list.

### `.iloc`: selecting by position

`.iloc` (think "**i**nteger **loc**ation") selects rows and columns by their **position number**, counting from 0, exactly like list indexing in Chapter 1. It ignores the index labels completely.

```python
sales.iloc[0]
```

**Output:**

```text
order_id                         1001
date              2026-03-02 00:00:00
branch                         Petone
category                   Cold & Flu
product                Vapour Rub 50g
unit_price                       9.99
quantity                          1.0
payment_method                 EFTPOS
loyalty_member                    Yes
Name: 0, dtype: object
```

A single position gives you that row as a Series: the column names become its labels. The `dtype: object` at the bottom appears because one row mixes numbers, a date and text; `object` is pandas' general-purpose type for mixed contents.

```python
sales.iloc[0:3]
```

**Output:**

```text
   order_id       date        branch    category               product  unit_price  quantity payment_method loyalty_member
0      1001 2026-03-02        Petone  Cold & Flu        Vapour Rub 50g        9.99       1.0         EFTPOS            Yes
1      1002 2026-03-02  Lambton Quay  Cold & Flu  Throat Lozenges 16pk        8.99       2.0           Card            Yes
2      1003 2026-03-02        Karori  Cold & Flu  Throat Lozenges 16pk        8.99       4.0           Card             No
```

Slices follow the Chapter 1 rule: **start included, stop excluded**, so `0:3` gives positions 0, 1 and 2.

With two parts, `[rows, columns]`, you select rows and columns at once:

```python
sales.iloc[0:3, 0:4]
```

**Output:**

```text
   order_id       date        branch    category
0      1001 2026-03-02        Petone  Cold & Flu
1      1002 2026-03-02  Lambton Quay  Cold & Flu
2      1003 2026-03-02        Karori  Cold & Flu
```

```python
sales.iloc[[0, 10, 20], [2, 4, 6]]
```

**Output:**

```text
         branch                 product  quantity
0        Petone          Vapour Rub 50g       1.0
10  Cuba Street    Antiseptic Cream 30g       1.0
20       Karori  Vitamin C 1000mg 60tab       2.0
```

Lists pick out specific positions: rows 0, 10 and 20, and columns 2, 4 and 6 (`branch`, `product`, `quantity`). And `sales.iloc[-1]` gives the last row, just like `my_list[-1]`.

### `.loc`: selecting by label

`.loc` selects by **label**: index labels for rows, column names for columns. With `orders` indexed by `order_id`, the labels are meaningful:

```python
orders.loc[1005, "product"]
```

**Output:**

```text
'Plasters 40pk'
```

```python
orders.loc[1003:1006, ["branch", "product", "quantity"]]
```

**Output:**

```text
                branch               product  quantity
order_id                                              
1003            Karori  Throat Lozenges 16pk       4.0
1004            Karori  Throat Lozenges 16pk       1.0
1005            Petone         Plasters 40pk       1.0
1006      Lambton Quay    Multivitamin 60tab       1.0
```

**Look carefully: `1003:1006` included 1006.** Label slices with `.loc` include *both* ends, unlike position slices. The reasoning: with labels, "the next label after the end" may not exist or may be hard to know (what comes after "Karori"?), so pandas makes label ranges inclusive. It's the most common `.loc`/`.iloc` mix-up, so here it is side by side:

| | `.iloc` | `.loc` |
|---|---|---|
| Selects by | position (0, 1, 2, …) | label (index values and column names) |
| Slice end | **excluded**: `iloc[0:3]` gives 3 rows | **included**: `loc[1003:1006]` gives 4 rows |
| Typical use | "the first 10 rows", "the last column" | "order 1005", "these named columns", "rows matching a condition" |
| Accepts True/False masks | only as a plain array | **yes**, directly |

### `.loc` with conditions: the workhorse

The most common selection in real work combines a **row condition** with a **list of columns**, and `.loc` does both in one step:

```python
sales.loc[sales["branch"] == "Petone", ["date", "product", "quantity"]]
```

**Output:**

```text
         date                 product  quantity
0  2026-03-02          Vapour Rub 50g       1.0
4  2026-03-03           Plasters 40pk       1.0
16 2026-03-04           Plasters 40pk       1.0
17 2026-03-04           Plasters 40pk       1.0
21 2026-03-05  Vitamin C 1000mg 60tab       2.0
27 2026-03-06       Moisturiser 100ml       4.0
38 2026-03-08  Paracetamol 500mg 20pk       4.0
```

Read it as: *rows where branch is Petone; columns date, product and quantity*. Notice the duplicated Plasters sale on 4 March appears twice here too: rows 16 and 17. This is the same `.loc[condition, column]` pattern you used for safe assignment in Chapter 2, and it's worth having firmly in your fingers.

### Boolean filtering in depth

You've filtered with single conditions. Here's the full toolkit.

**Combining conditions** with `&` (and), `|` (or) and `~` (not), each condition in brackets:

```python
cold_flu_bulk = (sales["category"] == "Cold & Flu") & (sales["quantity"] >= 3)
sales.loc[cold_flu_bulk, ["order_id", "branch", "product", "quantity"]]
```

**Output:**

```text
    order_id       branch                   product  quantity
2       1003       Karori      Throat Lozenges 16pk       4.0
7       1008  Cuba Street  Cold & Flu Capsules 24pk       3.0
23      1023  cuba street            Vapour Rub 50g       4.0
32      1032  Cuba Street            Vapour Rub 50g       3.0
```

Storing a mask in a well-named variable, as here, makes complicated filters much easier to read and check.

**Matching any of several values** with `.isin()`, which saves writing a long chain of `|`:

```python
suburbs = sales["branch"].isin(["Petone", "Karori"])
print(suburbs.sum())
```

**Output:**

```text
13
```

**Ranges** with `.between()`, which includes both ends by default:

```python
mid_price = sales["unit_price"].between(8, 15)
sales.loc[mid_price, "product"].value_counts()
```

**Output:**

```text
product
Throat Lozenges 16pk        8
Vapour Rub 50g              4
Cold & Flu Capsules 24pk    3
Name: count, dtype: int64
```

**Text patterns** with the `.str` tools, which apply string methods from Chapter 1 to a whole column:

```python
sales.loc[sales["product"].str.contains("Vitamin"), ["order_id", "product"]]
```

**Output:**

```text
    order_id                 product
8       1009  Vitamin C 1000mg 60tab
11      1012  Vitamin C 1000mg 60tab
15      1016  Vitamin C 1000mg 60tab
20      1020  Vitamin C 1000mg 60tab
21      1021  Vitamin C 1000mg 60tab
```

`.str.contains()` is case-sensitive by default; add `case=False` to ignore case.

### Worked example 3.3: filtering around a data problem

How many Cuba Street transactions are there? The obvious filter misses one:

```python
exact = sales["branch"] == "Cuba Street"
relaxed = sales["branch"].str.lower() == "cuba street"

print(exact.sum())
print(relaxed.sum())
```

**Output:**

```text
12
13
```

`.str.lower()` converts every branch name to lowercase *before* comparing, so both spellings match. This is a useful stop-gap, and a good illustration of why the log exists: every analysis would need to remember this trick until the data is fixed properly in Chapter 4.

**Missing values in filters** use `.isna()` and `.notna()`:

```python
sales.loc[sales["payment_method"].isna(), ["order_id", "branch", "payment_method"]]
```

**Output:**

```text
   order_id        branch payment_method
8      1009  Lambton Quay            NaN
```

> **Why not `== None` or `== np.nan`?** `NaN` has a strange property: it isn't equal to anything, *including itself*, so `sales["payment_method"] == np.nan` is False for every row, even the missing one. Always use `.isna()`.

**`.query()`: filters as readable text.** pandas also lets you write a condition as a string, which some people find easier to read:

```python
sales.query("branch == 'Lambton Quay' and payment_method == 'Cash'")
```

**Output:**

```text
    order_id       date        branch   category               product  unit_price  quantity payment_method loyalty_member
12      1013 2026-03-04  Lambton Quay  First Aid         Plasters 40pk        5.99       4.0           Cash            Yes
35      1035 2026-03-07  Lambton Quay  First Aid  Antiseptic Cream 30g        7.49       1.0           Cash            Yes
```

Inside the query string, column names are written bare, text values go in the *other* kind of quotes, and you can use the words `and`, `or` and `not`. Both styles are common in the workplace, so it's worth being able to read either. This course mostly uses masks, because they work everywhere, including inside `.loc` for assignment.

### Sorting

```python
sales.sort_values(["branch", "unit_price"], ascending=[True, False]).head(6)
```

**Output:**

```text
    order_id       date       branch    category                   product  unit_price  quantity payment_method loyalty_member
11      1012 2026-03-03  Cuba Street    Vitamins    Vitamin C 1000mg 60tab       18.99       1.0           Card             No
7       1008 2026-03-03  Cuba Street  Cold & Flu  Cold & Flu Capsules 24pk       14.50       3.0         EFTPOS             No
32      1032 2026-03-07  Cuba Street  Cold & Flu            Vapour Rub 50g        9.99       3.0         EFTPOS             No
39      1039 2026-03-08  Cuba Street  Cold & Flu            Vapour Rub 50g        9.99       1.0           Card             No
9       1010 2026-03-03  Cuba Street  Cold & Flu      Throat Lozenges 16pk        8.99       1.0         EFTPOS             No
30      1030 2026-03-07  Cuba Street  Cold & Flu      Throat Lozenges 16pk        8.99       1.0         EFTPOS             No
```

With a list of columns, pandas sorts by the first, then breaks ties with the second. `ascending` can take a matching list: here branch A→Z, then price high→low within each branch. Notice two more things: the lowercase `cuba street` would sort *after* all the capitalised names (uppercase letters come first in computer ordering), and the original index labels travel with their rows, which is how you can tell the rows have been rearranged.

When you want a clean 0, 1, 2… index after filtering or sorting, use `.reset_index(drop=True)`. `drop=True` throws the old labels away instead of keeping them as a new column.

For "top N" questions, `nlargest()` and `nsmallest()` are shortcuts for sorting then taking the head:

```python
sales.nlargest(3, "unit_price")[["order_id", "product", "unit_price"]]
```

**Output:**

```text
    order_id                 product  unit_price
5       1006      Multivitamin 60tab       24.99
36      1036   Sunscreen SPF50 200ml       22.99
8       1009  Vitamin C 1000mg 60tab       18.99
```

When values tie, `nlargest()` keeps the ones that appear first.

---

## 3.7 Worked example 3.4: answering business questions

The operations manager has five questions about the week. Answer them with what you've learnt, noting where the known data problems affect the answers.

First, add a revenue column:

```python
sales["revenue"] = sales["unit_price"] * sales["quantity"]
sales.loc[sales["order_id"].between(1030, 1032), ["order_id", "unit_price", "quantity", "revenue"]]
```

**Output:**

```text
    order_id  unit_price  quantity  revenue
30      1030        8.99       1.0     8.99
31      1031        4.50       NaN      NaN
32      1032        9.99       3.0    29.97
```

Order 1031, with its missing quantity, has missing revenue too: `NaN` spreads through arithmetic, as you saw in Chapter 2. pandas' `.sum()` skips it, so totals will quietly leave that sale out. That's one more reason to log it.

**Question 1: What were the five biggest transactions?**

```python
top_five = sales.nlargest(5, "revenue")
top_five[["order_id", "date", "branch", "product", "quantity", "revenue"]]
```

**Output:**

```text
    order_id       date        branch                   product  quantity  revenue
36      1036 2026-03-07  Lambton Quay     Sunscreen SPF50 200ml       4.0    91.96
8       1009 2026-03-03  Lambton Quay    Vitamin C 1000mg 60tab       4.0    75.96
27      1027 2026-03-06        Petone         Moisturiser 100ml       4.0    66.00
7       1008 2026-03-03   Cuba Street  Cold & Flu Capsules 24pk       3.0    43.50
23      1023 2026-03-06   cuba street            Vapour Rub 50g       4.0    39.96
```

**Question 2: What share of transactions were Cold & Flu?**

```python
is_cold_flu = sales["category"] == "Cold & Flu"
print(is_cold_flu.sum(), "of", len(sales), "rows")
print(f"{is_cold_flu.mean():.1%}")
```

**Output:**

```text
15 of 41 rows
36.6%
```

The mean of a True/False column is the proportion of Trues, the same trick as Chapter 2. **Caveat:** `len(sales)` includes the duplicated order, so the true share is 15 of 40 orders (37.5%). Small differences like this are exactly how unchecked duplicates creep into reports.

**Question 3: Which Lambton Quay sales were paid in cash?**

```python
lq_cash = (sales["branch"] == "Lambton Quay") & (sales["payment_method"] == "Cash")
sales.loc[lq_cash, ["order_id", "date", "product", "revenue"]]
```

**Output:**

```text
    order_id       date               product  revenue
12      1013 2026-03-04         Plasters 40pk    23.96
35      1035 2026-03-07  Antiseptic Cream 30g     7.49
```

**Question 4: What share of transactions used a loyalty card?**

```python
loyalty = sales["loyalty_member"] == "Yes"
print(f"{loyalty.mean():.1%} of rows")
```

**Output:**

```text
46.3% of rows
```

19 of the 41 rows. Allowing for the duplicated order (a non-member), the true figure is 19 of 40 orders, or 47.5%. Because `loyalty_member` is stored as text, we compare it with `"Yes"` to build the mask. After Chapter 4 converts it to True/False, `sales["loyalty_member"].mean()` will do the job directly.

**Question 5: What did Petone and Karori sell over the weekend?**

```python
weekend = sales["date"].dt.dayofweek >= 5          # Monday is 0, so 5 and 6 are Sat and Sun
suburbs = sales["branch"].isin(["Petone", "Karori"])

sales.loc[weekend & suburbs, ["date", "branch", "product", "quantity", "revenue"]]
```

**Output:**

```text
         date  branch                 product  quantity  revenue
33 2026-03-07  Karori    Throat Lozenges 16pk       1.0     8.99
38 2026-03-08  Petone  Paracetamol 500mg 20pk       4.0    18.00
```

`.dt.dayofweek` numbers the days from Monday = 0 to Sunday = 6, so `>= 5` means Saturday or Sunday. This only works because the dates were parsed as real dates in section 3.4; on text dates, `.dt` would fail.

---

## 3.8 Other file formats

CSV is the most common format, and you'll meet several others at work. The good news: pandas' readers all share the same shape, `pd.read_<format>()`, and its writers are `df.to_<format>()`.

### Excel

Excel files can hold several **sheets**, formatting and formulas; pandas reads the *values*. Reading and writing `.xlsx` files uses the `openpyxl` library you installed in Chapter 2. Since you don't have an Excel version of the sales file, create one first, then read it back:

```python
OUTPUT_DIR.mkdir(exist_ok=True)
excel_path = OUTPUT_DIR / "pharmacy_sales.xlsx"

sales.to_excel(excel_path, sheet_name="sales", index=False)

from_excel = pd.read_excel(excel_path, sheet_name="sales")
print(from_excel.shape)
from_excel.dtypes
```

**Output:**

```text
(41, 10)
order_id                   int64
date              datetime64[us]
branch                       str
category                     str
product                      str
unit_price               float64
quantity                 float64
payment_method               str
loyalty_member               str
revenue                  float64
dtype: object
```

- `OUTPUT_DIR.mkdir(exist_ok=True)` creates the output folder if it's missing, and does nothing if it already exists.
- `index=False` stops pandas writing the 0, 1, 2… index as an extra first column. You'll want it almost every time you save.
- Excel stores dates as real dates, so `date` comes back as a date type without needing `parse_dates`.
- `sheet_name` picks a sheet by name (or by position: `sheet_name=0` is the first). `sheet_name=None` reads **every** sheet and returns a dictionary of DataFrames, one per sheet, keyed by sheet name. Exercise 3.7 uses this.

> Real-world Excel files often have titles, notes or blank rows above the actual table. `skiprows=` and `usecols="B:F"` (Excel-style column letters) handle most of those.

### JSON

**JSON** (JavaScript Object Notation) is the standard format for data sent between web services and APIs. It will look very familiar, because it's written almost exactly like Python's lists and dictionaries from Chapter 1. Let's save a small piece of the sales data as JSON and look at it:

```python
json_path = OUTPUT_DIR / "sample_sales.json"
sales.head(2).to_json(json_path, orient="records", indent=2, date_format="iso")

print(json_path.read_text())
```

**Output:**

```text
[
  {
    "order_id":1001,
    "date":"2026-03-02T00:00:00.000",
    "branch":"Petone",
    "category":"Cold & Flu",
    "product":"Vapour Rub 50g",
    "unit_price":9.99,
    "quantity":1.0,
    "payment_method":"EFTPOS",
    "loyalty_member":"Yes",
    "revenue":9.99
  },
  {
    "order_id":1002,
    "date":"2026-03-02T00:00:00.000",
    "branch":"Lambton Quay",
    "category":"Cold & Flu",
    "product":"Throat Lozenges 16pk",
    "unit_price":8.99,
    "quantity":2.0,
    "payment_method":"Card",
    "loyalty_member":"Yes",
    "revenue":17.98
  }
]
```

`orient="records"` writes a list of dictionaries, one per row: the exact structure you built by hand in Chapter 1. `indent=2` spreads it over several lines to make it readable, and `date_format="iso"` writes dates in the international standard (ISO 8601) form, `2026-03-02T00:00:00.000`, where the `T` separates the date from the time.

Reading it back is one line:

```python
pd.read_json(json_path)
```

**Output:**

```text
   order_id       date        branch    category               product  unit_price  quantity payment_method loyalty_member  revenue
0      1001 2026-03-02        Petone  Cold & Flu        Vapour Rub 50g        9.99         1         EFTPOS            Yes     9.99
1      1002 2026-03-02  Lambton Quay  Cold & Flu  Throat Lozenges 16pk        8.99         2           Card            Yes    17.98
```

JSON from real APIs is often **nested**, with dictionaries inside dictionaries. `pd.json_normalize()` flattens nested structures into columns; you'll meet it if a later chapter pulls data from an API.

### Straight from the internet

`read_csv` accepts a web address in place of a file path. For example, the open-source Seaborn project publishes practice datasets on GitHub, including measurements of 344 penguins:
```python
penguins_url = "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv"
penguins = pd.read_csv(penguins_url)
penguins.shape    # (344, 7)
```

This needs an internet connection, and it downloads the file fresh each time. For anything you'll rerun, save a copy into `data/` once (`penguins.to_csv(DATA_DIR / "penguins.csv", index=False)`) and load it from there. It's faster, and your analysis keeps working if the website changes.

### Parquet, briefly

You'll hear about **Parquet** in any job involving larger data. It's a compressed, column-based file format that stores each column's *type* along with the data, so there's no guessing when you load it, and it's far smaller and faster than CSV. `df.to_parquet(path)` and `pd.read_parquet(path)` work out of the box, thanks to the pyarrow library you installed. It's a good format for saving your own cleaned data, which you'll do in Chapter 4.

---

## 3.9 Saving results

Saving follows the pattern you've just seen. Save the Cold & Flu transactions for the category manager:

```python
cold_flu = sales.loc[is_cold_flu].sort_values("date")
cold_flu_path = OUTPUT_DIR / "cold_flu_sales.csv"
cold_flu.to_csv(cold_flu_path, index=False)

print(cold_flu_path.exists())
print(pd.read_csv(cold_flu_path).shape)
```

**Output:**

```text
True
(15, 10)
```

Reading a file straight back after writing it is a cheap, reassuring check that it saved correctly.

Three saving habits:

1. **Always save into `output/`**, never over the raw files in `data/`.
2. **Use `index=False`** unless the index holds real information (like `order_id` in `orders`). Otherwise the file gains a mysterious unnamed first column of 0, 1, 2…
3. **Name files for what they contain**, such as `cold_flu_sales_2026-03-02.csv`. A name like `final_v2_REAL.csv` tells future you nothing.

---

## 3.10 Project milestone: a reusable first-look script

Notebooks are ideal for exploring. Once you know which checks you want to run on every new file, a **script** is better: you can run it with one command on next week's file, and every week after that. Let's turn the checklist into `ch03/first_look.py`.

There's one new idea first. A script may be run from different folders, so relative paths can break. The fix is to build paths from **the script's own location**. Python sets a special variable, `__file__`, to the path of the running script:

```text
Path(__file__)                          → .../python-data-analysis/ch03/first_look.py
Path(__file__).resolve().parent         → .../python-data-analysis/ch03
Path(__file__).resolve().parent.parent  → .../python-data-analysis        (the project root)
```

`.resolve()` turns the path into a full absolute path, and `.parent` steps up one folder. From the project root, every other folder is easy to reach, whichever folder the terminal happens to be in.

Create `ch03/first_look.py`:
```python
# ch03/first_look.py
# Run a first-look inspection on the Harbour Pharmacy sales file.

from pathlib import Path

import pandas as pd

# ---- Paths, built from this script's location ----
PROJECT_ROOT = Path(__file__).resolve().parent.parent
DATA_FILE = PROJECT_ROOT / "data" / "pharmacy_sales.csv"
OUTPUT_DIR = PROJECT_ROOT / "output"
OUTPUT_DIR.mkdir(exist_ok=True)

EXPECTED_BRANCHES = ["Cuba Street", "Karori", "Lambton Quay", "Petone"]

def first_look(df):
    """Return a one-row-per-column summary of a DataFrame."""
    return pd.DataFrame({
        "dtype": df.dtypes.astype(str),
        "non_null": df.notna().sum(),
        "missing": df.isna().sum(),
        "unique": df.nunique(),
    })

# ---- 1. Load ----
sales = pd.read_csv(DATA_FILE, parse_dates=["date"])

# ---- 2. Column summary ----
print(f"FILE: {DATA_FILE.name}")
print(f"Rows: {len(sales)}   Columns: {sales.shape[1]}")
print(f"Dates: {sales['date'].min():%a %d %b %Y} to {sales['date'].max():%a %d %b %Y}")
print()
print(first_look(sales))
print()

# ---- 3. Data-quality checks, collected as a list of dictionaries ----
issues = []

duplicate_rows = sales.duplicated().sum()
if duplicate_rows > 0:
    dup_ids = sales.loc[sales.duplicated(), "order_id"].tolist()
    issues.append({"check": "duplicate rows", "count": duplicate_rows,
                   "detail": f"order_id {dup_ids}"})

for column in sales.columns:
    missing = sales[column].isna().sum()
    if missing > 0:
        ids = sales.loc[sales[column].isna(), "order_id"].tolist()
        issues.append({"check": f"missing {column}", "count": missing,
                       "detail": f"order_id {ids}"})

unexpected = sales.loc[~sales["branch"].isin(EXPECTED_BRANCHES), "branch"]
if len(unexpected) > 0:
    issues.append({"check": "unexpected branch names", "count": len(unexpected),
                   "detail": f"{sorted(unexpected.unique())}"})

issue_log = pd.DataFrame(issues)

# ---- 4. Report and save ----
print(f"Data-quality issues found: {len(issue_log)}")
print(issue_log.to_string(index=False))

log_path = OUTPUT_DIR / "ch03_data_quality_log.csv"
issue_log.to_csv(log_path, index=False)
print()
print(f"Saved: {log_path.relative_to(PROJECT_ROOT)}")
```

Run it from the project folder:

```bash
python ch03/first_look.py
```

**Output:**

```text
FILE: pharmacy_sales.csv
Rows: 41   Columns: 9
Dates: Mon 02 Mar 2026 to Sun 08 Mar 2026

                         dtype  non_null  missing  unique
order_id                 int64        41        0      40
date            datetime64[us]        41        0       7
branch                     str        41        0       5
category                   str        41        0       5
product                    str        41        0      11
unit_price             float64        41        0      11
quantity               float64        40        1       4
payment_method             str        40        1       3
loyalty_member             str        41        0       2

Data-quality issues found: 4
                  check  count          detail
         duplicate rows      1 order_id [1017]
       missing quantity      1 order_id [1031]
 missing payment_method      1 order_id [1009]
unexpected branch names      1 ['cuba street']

Saved: output/ch03_data_quality_log.csv
```

### Walking through the new pieces

- **`first_look()`** builds a DataFrame from a dictionary of Series, as in the Chapter 2 milestone, with one row per column of the original data. `df.dtypes.astype(str)` converts the dtype names to plain text so they display neatly. You now have a reusable inspection tool: call it on any DataFrame.
- **`f"{sales['date'].min():%a %d %b %Y}"`** uses a date **format spec** inside an f-string, the same idea as `:.2f` for numbers. `%a %d %b %Y` gives "Mon 02 Mar 2026".
- **The issue log is a list of dictionaries**, turned into a DataFrame at the end, exactly the Chapter 1 structure feeding the Chapter 2 tool. The loop over `sales.columns` means the script checks *every* column for gaps, including columns added in future versions of the file.
- **`EXPECTED_BRANCHES` with `~isin()`** is a **validation rule**: a list of what's allowed, and a check that flags anything else. It caught `cuba street` without anyone looking for it specifically. Rules like this are how analysts catch problems in files too big to read.
- **`to_string(index=False)`** prints a DataFrame without its index.
- **`log_path.relative_to(PROJECT_ROOT)`** shortens the printed path to the part inside the project.

When next week's file arrives, you'll run this one command and know within seconds whether it has the same problems, new ones, or none.

---

## 3.11 Exercises

Work in `ch03/ch03_exercises.ipynb`. Start the notebook with the imports and path constants from section 3.3, and load `sales` with `parse_dates=["date"]`.

**Available this chapter: 280 XP.**
### Warm-up (10 XP each)

**Exercise 3.1: Where am I?** Print the name of your notebook's current working directory, then build the path to `pharmacy_sales.csv` and print whether it exists and how big it is in bytes. (Hint: a Path has a `.stat()` method, and the result has a `.st_size` attribute.)

<details>
<summary>Show a solution</summary>

```python
from pathlib import Path

print(Path.cwd().name)
csv_path = Path("..") / "data" / "pharmacy_sales.csv"
print(csv_path.exists())
print(csv_path.stat().st_size, "bytes")
```

**Output:**

```text
ch03
True
3108 bytes
```

Your byte count may be slightly higher if your editor saved the file with Windows line endings, which use two characters per line break instead of one. A file size is a handy sanity check: if a daily export is usually 5 MB and today's is 2 KB, something went wrong upstream.

</details>

**Exercise 3.2: A light load.** Load only the `date`, `branch`, `product` and `quantity` columns, and only the first 10 rows. Print the shape.

<details>
<summary>Show a solution</summary>

```python
light = pd.read_csv(csv_path, usecols=["date", "branch", "product", "quantity"], nrows=10)
light.shape
```

**Output:**

```text
(10, 4)
```

</details>

**Exercise 3.3: By position.** Using `.iloc`, show rows 10 to 14 (inclusive) and the first three columns.

<details>
<summary>Show a solution</summary>

```python
sales.iloc[10:15, 0:3]
```

**Output:**

```text
    order_id       date        branch
10      1011 2026-03-03   Cuba Street
11      1012 2026-03-03   Cuba Street
12      1013 2026-03-04  Lambton Quay
13      1014 2026-03-04  Lambton Quay
14      1015 2026-03-04   Cuba Street
```

Rows 10 to 14 *inclusive* means a stop of 15, because `.iloc` excludes the stop.

</details>

### Core (20 XP each)

**Exercise 3.4: By label.** Set `order_id` as the index, then use `.loc` to show `product` and `quantity` for orders 1020 to 1025 inclusive.

<details>
<summary>Show a solution</summary>

```python
orders = sales.set_index("order_id")
orders.loc[1020:1025, ["product", "quantity"]]
```

**Output:**

```text
                           product  quantity
order_id                                    
1020        Vitamin C 1000mg 60tab       2.0
1021        Vitamin C 1000mg 60tab       2.0
1022        Paracetamol 500mg 20pk       1.0
1023                Vapour Rub 50g       4.0
1024          Throat Lozenges 16pk       2.0
1025      Cold & Flu Capsules 24pk       1.0
```

With `.loc`, the end label 1025 is included.

</details>

**Exercise 3.5: Vitamin buyers.** Find all Vitamins sales with a quantity of 2 or more, sorted by date. Show the order ID, date, branch, product and quantity. How many are there?

<details>
<summary>Show a solution</summary>

```python
vitamins_multi = (sales["category"] == "Vitamins") & (sales["quantity"] >= 2)
result = sales.loc[vitamins_multi, ["order_id", "date", "branch", "product", "quantity"]].sort_values("date")
print(len(result))
result
```

**Output:**

```text
3
    order_id       date        branch                 product  quantity
8       1009 2026-03-03  Lambton Quay  Vitamin C 1000mg 60tab       4.0
20      1020 2026-03-05        Karori  Vitamin C 1000mg 60tab       2.0
21      1021 2026-03-05        Petone  Vitamin C 1000mg 60tab       2.0
```

</details>

**Exercise 3.6: Payment mix without the typo.** Show the number of transactions by payment method for **Cuba Street**, making sure the lowercase `cuba street` row is included. Include missing values in the count.

<details>
<summary>Show a solution</summary>

```python
cuba = sales["branch"].str.lower() == "cuba street"
sales.loc[cuba, "payment_method"].value_counts(dropna=False)
```

**Output:**

```text
payment_method
EFTPOS    7
Card      5
Cash      1
Name: count, dtype: int64
```

13 transactions in total. The exact-match filter would have found only 12, and one of the EFTPOS sales would have gone missing from this breakdown.

</details>

### Stretch (40 XP each)

**Exercise 3.7: A two-sheet workbook.** Write an Excel file, `output/ch03_workbook.xlsx`, with two sheets: `sales` (the full data) and `by_branch` (the output of `value_counts()` on branch, as a table). Then read *all* sheets back in one call and print each sheet's name and shape. (Hint: `pd.ExcelWriter` lets you write several sheets to one file, and `value_counts().reset_index()` turns a counts Series into a two-column table.)

<details>
<summary>Show a solution</summary>

```python
workbook_path = OUTPUT_DIR / "ch03_workbook.xlsx"
by_branch = sales["branch"].value_counts().reset_index()

with pd.ExcelWriter(workbook_path) as writer:
    sales.to_excel(writer, sheet_name="sales", index=False)
    by_branch.to_excel(writer, sheet_name="by_branch", index=False)

all_sheets = pd.read_excel(workbook_path, sheet_name=None)
for name, sheet in all_sheets.items():
    print(name, sheet.shape)

all_sheets["by_branch"]
```

**Output:**

```text
sales (41, 9)
by_branch (5, 2)
         branch  count
0  Lambton Quay     15
1   Cuba Street     12
2        Petone      7
3        Karori      6
4   cuba street      1
```

`with pd.ExcelWriter(...) as writer:` opens the file, lets you write several sheets into it, and saves and closes it properly when the indented block ends. The `with` statement is Python's standard way of saying "use this resource, then tidy up automatically". `sheet_name=None` returns a dictionary of `{sheet name: DataFrame}`, which the loop walks through with `.items()`, just like the dictionaries in Chapter 1.

</details>

**Exercise 3.8: Upgrade `first_look()`.** Extend the `first_look()` function from the milestone with two more columns: `pct_missing` (the percentage of values missing, to one decimal place) and `example` (the value in the first row). Run it on `sales`.

<details>
<summary>Show a solution</summary>

```python
def first_look(df):
    """Return a one-row-per-column summary of a DataFrame."""
    return pd.DataFrame({
        "dtype": df.dtypes.astype(str),
        "non_null": df.notna().sum(),
        "missing": df.isna().sum(),
        "pct_missing": (df.isna().mean() * 100).round(1),
        "unique": df.nunique(),
        "example": df.iloc[0],
    })

first_look(sales)
```

**Output:**

```text
                         dtype  non_null  missing  pct_missing  unique              example
order_id                 int64        41        0          0.0      40                 1001
date            datetime64[us]        41        0          0.0       7  2026-03-02 00:00:00
branch                     str        41        0          0.0       5               Petone
category                   str        41        0          0.0       5           Cold & Flu
product                    str        41        0          0.0      11       Vapour Rub 50g
unit_price             float64        41        0          0.0      11                 9.99
quantity               float64        40        1          2.4       4                  1.0
payment_method             str        40        1          2.4       3               EFTPOS
loyalty_member             str        41        0          0.0       2                  Yes
```

`df.isna().mean()` is the proportion of missing values per column (the mean of True/False again), and `df.iloc[0]` is the first row as a Series whose labels are the column names, so it lines up with the other columns automatically.

</details>

### Quick self-check (20 XP for all five)

1. Your notebook is in `ch03/`. Why does `pd.read_csv("data/pharmacy_sales.csv")` fail there?
2. Why did `quantity` load as `float64`?
3. `sales.iloc[0:5]` and `orders.loc[1001:1005]` (with `order_id` as the index): how many rows does each return, and why?
4. Why can't you find missing values with `sales["quantity"] == np.nan`?
5. Name two reasons to log data problems before fixing them.

<details>
<summary>Show answers</summary>

1. A notebook's working directory is its own folder, `ch03/`, and there's no `data` folder inside `ch03`. From there the path is `../data/pharmacy_sales.csv`.
2. One value is missing, and pandas' missing marker `NaN` is a float, so the whole column became float.
3. Five rows each. `.iloc[0:5]` gives positions 0–4 (stop excluded). `.loc[1001:1005]` gives labels 1001 to 1005 inclusive, which is also five rows.
4. `NaN` is never equal to anything, even itself, so the comparison is False everywhere. Use `.isna()`.
5. Any two of: keeping inspection and cleaning as separate, reviewable steps; some fixes need a decision from someone else; the log communicates data trustworthiness to stakeholders; it makes sure every problem gets fixed consistently rather than patched ad hoc in each analysis.

</details>

---

## 3.12 Cheat sheet

| Task | Code |
|---|---|
| Path to a file | `Path("..") / "data" / "file.csv"` |
| Where am I? | `Path.cwd()` |
| Does it exist? | `path.exists()` |
| Script's project root | `Path(__file__).resolve().parent.parent` |
| Read a CSV | `pd.read_csv(path)` |
| … with dates / some columns / some rows | `parse_dates=["date"]`, `usecols=[...]`, `nrows=100` |
| Read Excel (one sheet / all sheets) | `pd.read_excel(path, sheet_name="sales")` / `sheet_name=None` |
| Read JSON | `pd.read_json(path)` |
| Size, look, types | `.shape`, `.head()`, `.tail()`, `.sample(5, random_state=1)`, `.info()` |
| Missing per column / rows with any missing | `.isna().sum()` / `df[df.isna().any(axis=1)]` |
| Duplicates | `.duplicated().sum()`, `df[df.duplicated(keep=False)]` |
| Distinct values | `.nunique()`, `.unique()`, `.value_counts(dropna=False)` |
| Number summary | `.describe()` |
| Column(s) | `df["col"]`, `df[["a", "b"]]` |
| Rows by position | `df.iloc[0:5]`, `df.iloc[rows, cols]` (stop excluded) |
| Rows by label | `df.loc[label]`, `df.loc["a":"c", ["x", "y"]]` (end included) |
| Rows by condition + columns | `df.loc[df["col"] > 5, ["a", "b"]]` |
| Combine conditions | `(cond1) & (cond2)`, `(cond1) \| (cond2)`, `~cond` |
| Several values / range / text | `.isin([...])`, `.between(a, b)`, `.str.contains("x")` |
| Missing in a filter | `.isna()`, `.notna()` |
| Sort / top N | `.sort_values(["a", "b"], ascending=[True, False])`, `.nlargest(5, "col")` |
| Renumber the index | `.reset_index(drop=True)` |
| Save | `df.to_csv(path, index=False)`, `df.to_excel(path, index=False)` |
| Make the output folder | `OUTPUT_DIR.mkdir(exist_ok=True)` |

---

## 3.13 XP tally and what's next

| Section | XP available | XP earned |
|---|---|---|
| Warm-up (3.1–3.3) | 30 | |
| Core (3.4–3.6) | 60 | |
| Stretch (3.7–3.8) | 80 | |
| Milestone: `first_look.py` runs and saves the log | 60 | |
| Data-quality log written in your notebook | 30 | |
| Self-check (all five correct) | 20 | |
| **Chapter 3 total** | **280** | |
| **Running total (Chapters 1–3)** | **720** | |

**Coming up in Chapter 4: Cleaning Data.** Your data-quality log becomes a to-do list. You'll remove the duplicate order, standardise the branch names, decide what to do about the missing quantity and payment method, convert `loyalty_member` to True/False, and fix column types. You'll also learn to write cleaning steps as a repeatable function, and save the result as a clean file that every later chapter builds on.

---

## References and further reading

- pandas development team. *How do I read and write tabular data?* (getting-started tutorial). <https://pandas.pydata.org/docs/getting_started/intro_tutorials/02_read_write.html>
- pandas development team. *How do I select a subset of a DataFrame?* (getting-started tutorial). <https://pandas.pydata.org/docs/getting_started/intro_tutorials/03_subset_data.html>
- pandas development team. *IO tools (text, CSV, HDF5, …)*: the full `read_csv` reference. <https://pandas.pydata.org/docs/user_guide/io.html>
- pandas development team. *Indexing and selecting data* (`.loc`, `.iloc`, boolean indexing, `.query()`). <https://pandas.pydata.org/docs/user_guide/indexing.html>
- pandas development team. *Working with missing data*. <https://pandas.pydata.org/docs/user_guide/missing_data.html>
- pandas development team. *pandas.read_excel* and *pandas.ExcelWriter*. <https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html>
- Python Software Foundation. *pathlib: object-oriented filesystem paths*. <https://docs.python.org/3/library/pathlib.html>
- Python Software Foundation. *Codecs: standard encodings* (UTF-8, cp1252 and others). <https://docs.python.org/3/library/codecs.html#standard-encodings>
- Shafranovich, Y. *RFC 4180: Common Format and MIME Type for CSV Files*. <https://www.rfc-editor.org/rfc/rfc4180>
- ECMA International. *The JSON Data Interchange Syntax* (overview at json.org). <https://www.json.org/>
- Apache Software Foundation. *Apache Parquet documentation*. <https://parquet.apache.org/docs/>
- Waskom, M. *seaborn-data* (open practice datasets). <https://github.com/mwaskom/seaborn-data>