DataFrames with pandas

A spreadsheet you can program

A DataFrame is pandas' table: rows and named columns, like a sheet in a spreadsheet. Each column is a Series, a list of values with a name, which behaves much like a NumPy array. You can build a DataFrame from a dictionary of columns:

import pandas as pd

df = pd.DataFrame({
    "name": ["Ridgeline tent", "Trail mug", "Camp stove", "Rain jacket"],
    "category": ["tents", "camp kitchen", "camp kitchen", "apparel"],
    "price": [289, 14, 65, 120],
    "rating": [4.6, 4.8, 4.2, 4.5],
})
print(df)
print(df.shape)
print(list(df.columns))

import pandas as pd is the universal habit, so code you read elsewhere looks the same. The printout shows the columns across the top and an index down the left: the row labels, 0 to 3 here. df.shape is (rows, columns), and df.columns lists the column names.

Unlike a spreadsheet, nothing here is a formula in a cell. You ask the table questions in code, and you can run the same questions on next month's file without clicking anything.

Loading a CSV

Real tables arrive as files. pd.read_csv reads a CSV file into a DataFrame, using the first row as the column names:

import pandas as pd

with open("stock.csv", "w") as f:
    f.write("sku,item,stock,price\nTE-101,1-person tent,14,195\nPA-108,daypack 18L,0,55\nAP-122,rain jacket,31,120\n")

df = pd.read_csv("stock.csv")
print(df.head())
print(df.describe())

The first lines write a small file so the example has something to read. head() shows the first five rows, the quickest way to see what you're dealing with. describe() summarizes every number column: the count, the mean, the spread (std), and the smallest, middle and largest values.

CSV files often live on the web rather than on your computer. read_csv reads from a web address just as it reads a file. The practice shop publishes its full 40-product catalog at one:

import pandas as pd

catalog = pd.read_csv("https://api.gradient.test/v1/datasets/catalog.csv")
print(catalog.shape)
print(catalog[["sku", "name", "price", "rating"]].head(3))

import pandas as pd
df = pd.DataFrame({"a": [1, 2, 3], "b": [4, 5, 6]})
print(df.shape, len(df))

Columns and rows

Square brackets with one name give one column, a Series. With a list of names, you get a smaller DataFrame:

catalog["price"]                  # one column: a Series
catalog[["name", "price"]]        # two columns: a DataFrame

To pick rows, write a condition on a column. It gives a Series of True and False, one per row, called a boolean mask. Putting the mask in square brackets keeps the rows where it's True:

import pandas as pd

catalog = pd.read_csv("https://api.gradient.test/v1/datasets/catalog.csv")

cheap = catalog["price"] < 60
print(cheap.head(4).tolist())
print(catalog[cheap][["name", "price"]])

popular_tents = catalog[(catalog["category"] == "tents") & (catalog["rating"] >= 4.4)]
print(len(popular_tents), "well-rated tents")

Combine conditions with & (and) and | (or), not the words and and or, and put each condition in brackets. pandas works on whole columns at once, like NumPy, and the words only work on single values.

import pandas as pd
df = pd.DataFrame({"price": [10, 60, 25, 90], "stock": [0, 5, 3, 0]})
print(len(df[(df["price"] > 20) & (df["stock"] > 0)]))

import pandas as pd

df = pd.DataFrame({
    "item": ["tent", "mug", "stove", "jacket", "pack"],
    "price": [289, 14, 65, 120, 165],
    "rating": [4.6, 4.8, 3.9, 4.5, 4.4],
    "stock": [3, 40, 12, 0, 8],
})
restock = df
pricey = df

New columns, sorting and the top few

A new column is a calculation on old ones, written once for every row:

import pandas as pd

catalog = pd.read_csv("https://api.gradient.test/v1/datasets/catalog.csv")

catalog["margin"] = catalog["price"] - catalog["cost"]
catalog["revenue_q2"] = catalog["price"] * catalog["units_q2"]

print(catalog.sort_values("margin", ascending=False)[["name", "margin"]].head(3))
print(catalog.nlargest(3, "revenue_q2")[["sku", "revenue_q2"]])

best = catalog["margin"].idxmax()
print(catalog.loc[best, "name"])

• sort_values sorts by a column; ascending=False puts the largest first.
• nlargest(3, "revenue_q2") is a shortcut for "sort, then take the top 3".
• idxmax() gives the index label of the largest value, and .loc[row, column] reads one cell by its labels.

None of this changes the original order of catalog: sort_values gives back a new, sorted DataFrame. That's the pattern throughout pandas.

Counting and grouping

Two questions come up in almost every analysis: "how many of each?" and "what's the average per group?"

import pandas as pd

catalog = pd.read_csv("https://api.gradient.test/v1/datasets/catalog.csv")

print(catalog["category"].value_counts())
print(catalog.groupby("category")["price"].mean().round(1))

value_counts counts each distinct value, most common first. groupby("category") splits the table into one group per category; ["price"].mean() then works out each group's average price. The result is a Series with the categories as its index.

Notice that rating has some empty cells, for products with no reviews yet. pandas shows them as NaN, "not a number", and mean() skips them. That's usually what you want, but it's worth knowing: a mean over 37 ratings is not a mean over 40 products.

Missing values, and reading one cell

Real tables have gaps. A product with no reviews has no rating, so that cell is empty in the CSV. pandas reads an empty cell as NaN. A few tools find and handle the gaps:

import pandas as pd

catalog = pd.read_csv("https://api.gradient.test/v1/datasets/catalog.csv")

print(catalog["rating"].isna().sum(), "products have no rating")
print(catalog[catalog["rating"].isna()][["sku", "name", "reviews"]])

rated = catalog.dropna(subset=["rating"])
print(len(rated), "rated products")

print(catalog.loc[0, "name"])          # row label 0, column "name"
print(catalog.iloc[-1]["sku"])         # the last row, by position

• isna() gives True for each missing value, and .sum() counts the True values.
• dropna(subset=["rating"]) keeps only the rows that have a rating.
• fillna(0) would fill the gaps with 0. Think before you do that: a missing rating isn't a rating of zero, and filling it in would drag every average down.

.loc picks by label: the index value and the column name. .iloc picks by position, counting from 0, so iloc[-1] is the last row. They agree until you sort or filter a table. Then the labels keep their old values, but the positions start again from 0.

import pandas as pd

temps = pd.DataFrame({
    "day": ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"],
    "ridge": [48, 52, 57, 55, 44, 61, 59],
    "valley": [55, 54, 60, 58, 51, 59, 62],
}).set_index("day")

warmest_day = None
ridge_mean = None
colder_days = None

import pandas as pd

catalog = pd.read_csv("https://api.gradient.test/v1/datasets/catalog.csv")
counts = {}
avg_rating = {}

import pandas as pd

catalog = pd.read_csv("https://api.gradient.test/v1/datasets/catalog.csv")

by_category = catalog.groupby("category")[["units_q1", "units_q2"]].sum()
by_category["growth_pct"] = ((by_category["units_q2"] / by_category["units_q1"] - 1) * 100).round(1)
print(by_category.sort_values("growth_pct", ascending=False))

Saving results, and a warning about loops

to_csv("summary.csv", index=False) writes a DataFrame back to a file; index=False leaves out the row labels, which you rarely want in an export. to_dict(orient="records") turns it into a list of dictionaries, the shape JSON and APIs use.

You can loop over the rows of a DataFrame, but it's slow and long-winded, and almost never needed. When you feel the urge to write for row in ..., look for the column operation instead: a mask, a calculated column, a groupby. That habit is most of what it means to be fluent in pandas.