pandas: working with data tables in Python

Definition

Handling a data file with nothing but the language's own tools quickly turns into makeshift work: a loop to read the rows, a dictionary to accumulate totals. pandas avoids that makeshift work: it adds to Python an object it does not have as standard, the DataFrame, a table of named columns, each carrying its own type. Where a list of dictionaries forces a walk through the rows one by one, a DataFrame is handled column by column, in a single expression.

In practice, on a sales file, that looks like this:

import pandas as pd

sales = pd.read_csv("sales.csv")
print(sales["amount"].sum())
print(sales[sales["country"] == "France"].head())

It is not part of the standard library: it is installed with pip, preferably inside a virtual environment, failing which the very first import raises a ModuleNotFoundError.

pip install pandas

Data rarely comes from a single CSV file. pandas also reads Excel sheets, JSON and the result of an SQL query, and whatever comes out of it can then be plotted with matplotlib without any intermediate conversion.

The concrete problem it solves

The real competitor of pandas is not another library, it is the loop written by hand. Working out revenue per country without it takes an accumulator dictionary, an iteration, separate handling for incomplete rows and a final sort, and none of that stays readable six months later. With pandas, the same thing fits on one line, at a glance.

The comparison shows better side by side:

# Without pandas
totals = {}
for row in rows:
    totals[row["country"]] = totals.get(row["country"], 0) + row["amount"]

# With pandas
totals = sales.groupby("country")["amount"].sum()

The gain has a price. The file is loaded into memory in full, importing costs about a second at startup, and the syntax has to be learned like a second language. On fifty rows processed once, a list comprehension and the csv module do the job faster, with no dependency to install.

The choice almost always comes down to the size and use of the table:

The classic beginner mistake

The most common mistake, for someone just starting out, is treating the DataFrame as a plain list and looping over it with iterrows. The code returns the right result, which makes it hard to suspect, but it runs dozens of times slower than the expected form, because every turn rebuilds a whole Python object where pandas knows how to work on the entire column in one go.

The difference shows up by writing the same calculation both ways:

# Slow, and the machine is not to blame
for i, row in sales.iterrows():
    sales.at[i, "gross"] = row["amount"] * 1.2

# Expected
sales["gross"] = sales["amount"] * 1.2

The second mistake is editing an extract while believing the original is being edited. sales[sales["amount"] > 100]["discount"] = 5 writes into a temporary copy and changes nothing in the starting table. The correct form names the rows and the column in one single operation: sales.loc[sales["amount"] > 100, "discount"] = 5. A misspelled column name, for its part, raises a KeyError, exactly as on a dictionary.

Missing values are not None

A real data file almost always has holes: an age left blank, a reading a sensor failed to take. Python normally answers a missing value with None, but pandas makes a different choice: an empty cell becomes NaN, which is a float of a peculiar kind. The consequence catches people out: a column of whole numbers holding a single hole tips entirely into decimal numbers, and NaN == NaN answers false, which makes any equality test useless for spotting them.

Three methods replace that failing test:

sales["discount"].isna().sum()     # How many holes
sales["discount"].fillna(0)        # Fill them
sales.dropna(subset=["amount"])    # Or drop those rows

Choosing between filling and dropping is not a technical question. Replacing a missing discount with zero is fair; replacing a missing temperature with zero skews the average of the whole series. This is exactly the kind of judgement call a spreadsheet leaves invisible and a line of code makes explicit and reviewable.

Frequently asked questions

No. The library rests on it but does not demand it from its user: a file can be loaded, filtered and aggregated without ever writing a line of numpy. The detour becomes worthwhile the day matrix maths is needed, or a level of performance that column-wise work no longer delivers.

Neither of the two. It replaces the manual gesture inside Excel, not Excel itself: a written treatment replays identically the following month, without redoing the same clicks. Against a database it replaces nothing: it consumes the result of a query and leaves storage to the server.

A table held in memory often takes several times the size of the original file. Three reflexes settle most cases: read only the useful columns with usecols, force the types with dtype, and process the file in pieces with chunksize. Beyond that, the problem is no longer one of library but of storage.