Chapter 3 — Loading, Inspecting and Selecting Data with pandas

Part of Python · page 3 of 4

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:

  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.

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:

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:

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):

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:

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:

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

pd.read_csv("pharmacy_sales.csv")

Output:

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

import pandas as pd

sales = pd.read_csv(csv_path)
sales.head()

Output:

   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:

Worked example 3.2: loading with options

A realistic load for this file reads the date column as proper dates:

sales = pd.read_csv(csv_path, parse_dates=["date"])
sales.dtypes

Output:

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:

peek = pd.read_csv(csv_path, usecols=["date", "branch", "quantity"], nrows=3)
peek

Output:

         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.

Let's work through it with the Harbour Pharmacy file.

Check 1: size

sales.shape

Output:

(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:

sales.tail(3)

Output:

    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

sales.sample(5, random_state=1)

Output:

    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

sales.info()

Output:

<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

sales.isna().sum()

Output:

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:

sales[sales.isna().any(axis=1)]

Output:

    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

print(sales.duplicated().sum())
print(sales["order_id"].nunique())

Output:

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:

sales[sales.duplicated(keep=False)]

Output:

    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

sales["branch"].value_counts()

Output:

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:

sales["payment_method"].value_counts(dropna=False)

Output:

payment_method
Card      20
EFTPOS    16
Cash       4
NaN        1
Name: count, dtype: int64

sales["category"].unique()

Output:

<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

sales.describe()

Output:

          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

print(sales["date"].min())
print(sales["date"].max())
print(sales["date"].dt.day_name().value_counts())

Output:

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:

## 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:

sales["product"].head(3)

Output:

0          Vapour Rub 50g
1    Throat Lozenges 16pk
2    Throat Lozenges 16pk
Name: product, dtype: str

sales[["date", "branch", "product", "quantity"]].head(3)

Output:

        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:

orders = sales.set_index("order_id")
orders.head(3)

Output:

               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:

print(orders.index.is_unique)
orders.loc[1017]

Output:

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 "integer location") selects rows and columns by their position number, counting from 0, exactly like list indexing in Chapter 1. It ignores the index labels completely.

sales.iloc[0]

Output:

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.

sales.iloc[0:3]

Output:

   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:

sales.iloc[0:3, 0:4]

Output:

   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

sales.iloc[[0, 10, 20], [2, 4, 6]]

Output:

         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:

orders.loc[1005, "product"]

Output:

'Plasters 40pk'

orders.loc[1003:1006, ["branch", "product", "quantity"]]

Output:

                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:

.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:

sales.loc[sales["branch"] == "Petone", ["date", "product", "quantity"]]

Output:

         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:

cold_flu_bulk = (sales["category"] == "Cold & Flu") & (sales["quantity"] >= 3)
sales.loc[cold_flu_bulk, ["order_id", "branch", "product", "quantity"]]

Output:

    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 |:

suburbs = sales["branch"].isin(["Petone", "Karori"])
print(suburbs.sum())

Output:

13

Ranges with .between(), which includes both ends by default:

mid_price = sales["unit_price"].between(8, 15)
sales.loc[mid_price, "product"].value_counts()

Output:

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:

sales.loc[sales["product"].str.contains("Vitamin"), ["order_id", "product"]]

Output:

    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:

exact = sales["branch"] == "Cuba Street"
relaxed = sales["branch"].str.lower() == "cuba street"

print(exact.sum())
print(relaxed.sum())

Output:

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():

sales.loc[sales["payment_method"].isna(), ["order_id", "branch", "payment_method"]]

Output:

   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:

sales.query("branch == 'Lambton Quay' and payment_method == 'Cash'")

Output:

    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

sales.sort_values(["branch", "unit_price"], ascending=[True, False]).head(6)

Output:

    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:

sales.nlargest(3, "unit_price")[["order_id", "product", "unit_price"]]

Output:

    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:

sales["revenue"] = sales["unit_price"] * sales["quantity"]
sales.loc[sales["order_id"].between(1030, 1032), ["order_id", "unit_price", "quantity", "revenue"]]

Output:

    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?

top_five = sales.nlargest(5, "revenue")
top_five[["order_id", "date", "branch", "product", "quantity", "revenue"]]

Output:

    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?

is_cold_flu = sales["category"] == "Cold & Flu"
print(is_cold_flu.sum(), "of", len(sales), "rows")
print(f"{is_cold_flu.mean():.1%}")

Output:

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?

lq_cash = (sales["branch"] == "Lambton Quay") & (sales["payment_method"] == "Cash")
sales.loc[lq_cash, ["order_id", "date", "product", "revenue"]]

Output:

    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?

loyalty = sales["loyalty_member"] == "Yes"
print(f"{loyalty.mean():.1%} of rows")

Output:

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?

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:

         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:

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:

(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:

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:

[
  {
    "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:

pd.read_json(json_path)

Output:

   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:

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:

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:

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:

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:

# 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:

python ch03/first_look.py

Output:

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.)

Exercise 3.2: A light load. Load only the date, branch, product and quantity columns, and only the first 10 rows. Print the shape.

Exercise 3.3: By position. Using .iloc, show rows 10 to 14 (inclusive) and the first three columns.

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.

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?

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.

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.)

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.

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.

3.12 Cheat sheet

3.13 XP tally and what's next

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