Working with data

Sooner or later most Python programmers meet a table of numbers: a CSV export, a log file, the results of an experiment. You can process it with loops and dictionaries, but two third party libraries make the job shorter and much faster. NumPy provides fast arrays of numbers, and pandas, built on top of it, provides labelled tables with tools for cleaning, filtering, grouping and joining.

This unit covers the essentials of both, then puts them to work on a small crime report dataset. The examples are written for pandas 3 and NumPy 2.

You will be able to:

• Create NumPy arrays, explain their dtype, and replace loops with vectorised operations, broadcasting and boolean masks
• Load a CSV into a pandas DataFrame and inspect it with head, info and describe
• Select rows and columns with loc, iloc and boolean conditions
• Handle missing values and change column types without falling into the chained assignment trap
• Summarise data with groupby, agg, value_counts and sort_values, combine tables with merge, and work with dates
• Know when to reach for plotting and Jupyter notebooks

Installing NumPy and pandas

Both libraries come from PyPI. Install them into your virtual environment (see Setting up Python); the command is the same in PowerShell:

By convention you import them as np and pd. Check which versions you have, because the details of pandas output change between major versions:

Pitfall. Never name your own script pandas.py or numpy.py. Python would import your file instead of the library, and pd.DataFrame would fail with an AttributeError. See Modules and packages.

NumPy essentials

Arrays and dtypes

A NumPy array (ndarray) holds values of one type, packed together in memory. That type is its dtype, such as int64 (64 bit integers) or float64. An array also has a shape: a tuple giving its size along each dimension, or axis.

np.arange works like range but returns an array, reshape gives the same values a new shape, and np.linspace(start, stop, num) spaces num values evenly, including both ends.

Vectorised operations

Arithmetic on arrays works element by element, without a Python loop. This is called vectorisation: the loop still happens, but in compiled C code. Reductions such as sum, mean and max take an optional axis: axis=0 works down the columns, axis=1 across the rows.

Vectorised code is shorter and usually many times faster than a loop over the same numbers, and the gap grows with the size of the data. Your timings will differ, but the ratio is typically large:

Broadcasting

When an operation combines arrays of different shapes, NumPy broadcasts the smaller one, repeating it to fit without copying it. The rule compares shapes from the right: two sizes are compatible when they are equal or when one of them is 1.

A common use is turning counts into shares of each row's total. keepdims=True keeps the summed axis with size 1, so the totals have shape (2, 1) and broadcast across the columns:

Boolean masks

Comparing an array with a value gives an array of True/False, called a mask. Indexing with a mask keeps the elements where it is True. Combine conditions with & (and), | (or) and ~ (not), with parentheses around each comparison; the keywords and/or do not work element by element.

warm.sum() counts the True values, because True counts as 1. np.where(condition, a, b) picks from a where the condition holds and from b elsewhere.

Matrix multiplication with @

* multiplies two arrays element by element. The @ operator, first mentioned in Operators and expressions, does matrix multiplication instead. For two one dimensional arrays that is the dot product: multiply the pairs, then add up the results.

prices @ quantities is the total cost of the order. turn @ point applies a 2 by 2 matrix to a point, here a quarter turn anticlockwise.

Two NumPy traps

Unlike Python's int, NumPy integers have a fixed size and wrap around silently when an array operation overflows. And slicing an array returns a view that shares memory with the original, unlike slicing a list, which copies.

The default int64 is large enough for most counting, but keep the wraparound in mind when you choose small dtypes to save memory. Call .copy() when you want to change part of an array without touching the original.

pandas: Series and DataFrame

pandas has two core types:

• A Series is a one dimensional column of values with a label for each one. The labels form the index.
• A DataFrame is a table: several Series that share one index, each with a column name.

Without an explicit index, pandas numbers the rows from 0. Selecting one column of a DataFrame, as in menu["price"], gives a Series.

Loading and inspecting data

pd.read_csv reads CSV from a file path or from any file like object. The examples here keep their data in a string and wrap it in io.StringIO, so they need no files. parse_dates converts the named columns to dates, and index_col uses a column as the row labels.

Notice:

• shape is (rows, columns). The index column, order_id, is not counted as a column.
• head(n) shows the first n rows (5 by default) and tail(n) the last.
• Empty cells become NaN ("not a number"), pandas's usual marker for a missing value. Because NaN is a float, the quantity column became float64 even though every value present is a whole number.
• describe() summarises numeric columns: the count of non missing values, mean, standard deviation, minimum, quartiles and maximum.
• info() lists every column with its count of non missing values and its dtype, so the gaps in quantity and unit_price stand out. Text columns have the dtype str, which is new in pandas 3; earlier versions showed object.
• info() normally ends with the memory used. The example passes memory_usage=False because that figure depends on your versions and on whether the optional pyarrow package is installed.

Selecting rows and columns

pandas has two accessors for picking rows and columns, one by label and one by position. Mixing them up is a common source of bugs:

A boolean condition on a column gives a Series of True/False that selects rows, like a NumPy mask. The same &, |, ~ and parentheses rules apply. isin tests membership in a list.

The examples in the rest of the unit share the orders data. To keep each block complete, they repeat the loading lines.

The label slice 1002:1004 includes 1004, while the position slice -2: behaves like a list slice. df.query("quantity >= 3 and category == 'books'") is an alternative way to write the filter as a string.

Missing data, types and the Copy-on-Write rule

Missing values need a decision: drop the rows, fill in a value, or leave them and let calculations skip them. isna() finds them, dropna() returns the data without the incomplete rows, and fillna() returns the data with replacements. astype() converts a column to another dtype.

In pandas 3, Copy-on-Write is always on: every object you get from a DataFrame, whether a column, a filtered set of rows or a slice, behaves as an independent copy. The consequence is a simple rule: to change a DataFrame, assign to it directly, with df["col"] = ... or df.loc[rows, "col"] = ....

Notice:

• Each fillna result is assigned back to its column. Filling with the median, not the mean, keeps one very large price from distorting the replacement.
• Once the gap is filled, astype("int64") turns quantity back into whole numbers.
• orders.loc[condition, "category"] = "audio" changes the matching rows in place, in one step.
• Arithmetic between columns is vectorised, as in NumPy.

Pitfall. Older tutorials write df["quantity"].fillna(0, inplace=True) or df[df["x"] > 0]["y"] = 0. Both are chained assignment: the first step returns an object that behaves as a copy, and the second step changes only that copy. In pandas 3 these lines never update df; pandas emits a ChainedAssignmentError warning instead. Use the assignment forms above.

The chained call also prints a warning on standard error, which explains the problem and suggests the fix.

Grouping, sorting and counting

Summaries follow a pattern called split, apply, combine: split the rows into groups by a key, apply a calculation to each group, and combine the results into a new table. groupby does the splitting, and agg applies one or more calculations. With named aggregation, each keyword becomes an output column, given as (column, function). The data here is the orders table with its gaps already filled:

• sort_values sorts by one or more columns; pass a list such as ["category", "total"] with ascending=[True, False] to mix directions.
• value_counts() counts how often each value occurs, most frequent first.
• nlargest(n) is a shortcut for "sort descending and take the first n".

Combining tables with merge

merge joins two tables on matching key values, like a SQL join. The how argument decides what happens to rows without a match:

validate="many_to_one" makes pandas check that each order matches at most one customer row, which catches duplicated keys early. indicator=True adds a _merge column saying where each row came from.

Zoe has an order but no customer record, so her city is missing. The second merge finds the opposite case: Chloe is a customer with no orders.

Working with dates

A column parsed as dates offers the .dt accessor, with attributes such as year, month and hour and methods such as day_name(). to_period("M") turns a date into its calendar month, which is handy for grouping:

Subtracting two dates gives a Timedelta, pandas's version of datetime.timedelta. pd.to_datetime also converts text columns that were not parsed when the file was read.

Plotting and notebooks

pandas can draw charts through the third party matplotlib library: every Series and DataFrame has a .plot accessor. Install matplotlib with python -m pip install matplotlib, then:

savefig writes the chart to an image file, and show() opens a window when you run the script from a terminal.

Data work is exploratory: you load, look, adjust and look again. Jupyter notebooks suit that style. A notebook is a document of cells; you run one cell at a time, and the result of the last expression in a cell, such as a DataFrame, is displayed below it as a formatted table. Install and start JupyterLab from your virtual environment:

The command opens JupyterLab in your browser. VS Code and PyCharm can open notebook (.ipynb) files too.

Pitfall. A notebook keeps its variables between cells, in whatever order you ran them. After a few edits, what you see may depend on a cell you have since deleted. Before sharing results, restart the kernel and run all cells from the top, and move code you want to reuse into ordinary .py modules.

Mini lab: crime reports

The lab data below is invented, but its columns imitate the City of Chicago's public dataset of reported crimes from 2001 to the present, published on the City of Chicago Data Portal. The full dataset has millions of rows; if you download it to practise on, credit the City of Chicago as the source. Each row is one report: its date and time, the primary offence type, the police district and whether an arrest was made.

The questions:

1. How many reports are there of each type?
2. What share of reports of each type led to an arrest?
3. How many reports were made in each month?
4. What share of reports happened at night (from 20:00 to 05:59)?
5. Which district had the most thefts?

Notice how short each answer is once the data is in a DataFrame: read_csv recognised the true/false text as booleans, the mean of a boolean column is a proportion, and the .dt accessor turns timestamps into hours or months. With the full dataset, the same code answers the same questions for millions of rows.

Check yourself

1. What is a dtype, and why does an array with one missing value often become float64?
2. Why is prices * counts on NumPy arrays faster than a Python loop that multiplies the same numbers?
3. Can an array of shape (4, 3) be added to one of shape (3,)? To one of shape (4,)? Why?
4. What is the difference between df.loc[2:4] and df.iloc[2:4] on a DataFrame whose index is 0, 1, 2, ...?
5. Why must you write (df["a"] > 1) & (df["b"] < 5), with parentheses, instead of df["a"] > 1 and df["b"] < 5?
6. In pandas 3, why does df["price"].fillna(0, inplace=True) leave df unchanged, and what should you write instead?
7. What do how="left" and indicator=True do in merge?
8. Why should you restart a Jupyter notebook and run all cells before sharing its results?

Suggested answers

1. A dtype is the single type that every element of an array or column has, such as int64 or float64. Missing values are stored as NaN, which is a float, so a column of integers with a gap is converted to floats to hold it.
2. The multiplication loop runs in compiled C code over a compact block of memory, instead of the interpreter handling one Python object at a time.
3. (4, 3) and (3,) broadcast: compared from the right, 3 matches 3, and the row is reused for all 4 rows. (4, 3) and (4,) do not: 3 and 4 differ and neither is 1. Reshaping the second array to (4, 1) makes it work.
4. loc uses labels and includes the end, so it returns the rows labelled 2, 3 and 4. iloc uses positions and excludes the end, so it returns the rows at positions 2 and 3.
5. and asks for the truth value of a whole Series, which is ambiguous and raises an error. & works element by element, and it binds more tightly than >, so each comparison needs parentheses.
6. df["price"] returns an object that behaves as a copy under Copy-on-Write, and inplace=True changes only that copy; pandas warns with ChainedAssignmentError. Write df["price"] = df["price"].fillna(0).
7. how="left" keeps every row of the left table, filling in NaN where the right table has no match. indicator=True adds a _merge column with "both", "left_only" or "right_only" for each row.
8. Cells keep their state between runs, so results can depend on cells run out of order or since deleted. Running everything from a fresh kernel proves that the notebook works from top to bottom.

Exercises

These exercises are optional practice. Pick the ones that help you.

1. Normalise columns. Write min_max_scale(data), which takes a 2D NumPy array and rescales each column to the range 0 to 1 using (x - column_min) / (column_max - column_min). A column whose values are all equal becomes all zeros instead of dividing by zero. Use broadcasting, not loops. For [[1, 10, 5], [3, 20, 5], [5, 30, 5]] the result is [[0, 0, 0], [0.5, 0.5, 0], [1, 1, 0]].

2. Clean a messy price list. This CSV has stray spaces, the text n/a and an empty cell in the price column:

   Load it so that price is a float64 column, fill the missing prices with the median of the others, and print the products with their prices. Expected: pen 1.2, notebook 1.2, ruler 0.8, stapler 1.2, folder 2.5.

3. Category report. Use the orders table with its gaps already filled, the one loaded at the start of Grouping, sorting and counting (with total = quantity * unit_price). Build a table with one row per category and the columns revenue (sum of totals) and share (that revenue as a percentage of all revenue, rounded to 1 decimal place), sorted by revenue, largest first. Expected shares: books 45.0, games 39.7, music 15.3.

4. Customers without orders. Given a customers table (customer_id, name) and an orders table (order_id, customer_id), return the sorted list of names of customers who have never ordered. Use merge with indicator=True. Test it with customers 1 Ana, 2 Ben, 3 Chloe, 4 Dev and orders from customers 1, 1 and 3: the answer is ["Ben", "Dev"].

Solutions

1. Normalise columns.

data.min(axis=0) has shape (3,), so it broadcasts across every row. Where a column's span is 0, every data - low value in that column is also 0, and dividing by 1 keeps it at 0.

2. Clean a messy price list.

skipinitialspace=True ignores the spaces after commas, and na_values adds n/a to the strings that mean "missing". If the messy values were already in a DataFrame, pd.to_numeric(column, errors="coerce") would turn anything unparseable into NaN instead.

3. Category report.

4. Customers without orders.

The second test passes an empty orders table (iloc[0:0] keeps the columns but no rows), so every customer is left_only. A shorter alternative is customers.loc[~customers["customer_id"].isin(orders["customer_id"]), "name"].