pandas basics: read a CSV, filter rows and summarize with groupby

pandas is the standard Python library for working with tables. A DataFrame is a table with named columns. Each column is a Series with one data type. If you know SQL, most operations map directly to a clause you already use.

python3 -m pip install pandas

Read a CSV file

Suppose orders.csv has the columns id,customer_id,amount,status,created_at.

import pandas as pd

df = pd.read_csv(
    "orders.csv",
    parse_dates=["created_at"],        # turn text into real dates
    dtype={"customer_id": "string"},   # keep ids as text, e.g. to keep leading zeros
)

Useful options for messy files: sep=";" for semicolon-separated data, encoding="utf-8-sig" or "cp1252" for other encodings, and na_values=["-", "n/a"] for custom missing markers.

Look before you analyze

df.shape          # (rows, columns)
df.head()         # first five rows
df.dtypes         # type of each column
df.info()         # types plus non-null counts
df.describe()     # count, mean, min, max... of numeric columns
df["status"].value_counts()

Check dtypes first. If amount shows as object, at least one value is not a clean number, such as "1,200" or "N/A", and sums will fail or give wrong results.

Filter rows (SQL: WHERE)

paid = df[df["status"] == "paid"]

big_paid = df[(df["status"] == "paid") & (df["amount"] >= 50)]   # & means AND, | means OR
some = df[df["status"].isin(["paid", "pending"])]
same = df.query("status == 'paid' and amount >= 50")

cols = df.loc[df["amount"] >= 50, ["id", "amount"]]              # rows and columns at once

Each condition needs its own parentheses, because & binds more tightly than ==. Use & and |, not Python’s and and or.

If you plan to modify a filtered result, take a copy first, for example paid = df[df["status"] == "paid"].copy(). This avoids SettingWithCopyWarning and accidental changes.

Sort and take the top rows (SQL: ORDER BY, LIMIT)

df.sort_values("amount", ascending=False).head(3)
df.nlargest(3, "amount")

Group and aggregate (SQL: GROUP BY)

df.groupby("status")["amount"].sum()

summary = (
    df.groupby("customer_id")
      .agg(orders=("id", "count"), total=("amount", "sum"), average=("amount", "mean"))
      .reset_index()
      .sort_values("total", ascending=False)
)

The named form new_name=("column", "function") gives clean column names. reset_index() turns the group keys back into a normal column. Note that by default groupby leaves out rows whose key is missing; pass dropna=False to keep them as their own group.

Missing values

df.isna().sum()                          # missing values per column
df["status"] = df["status"].fillna("unknown")
clean = df.dropna(subset=["amount"])

Save the result

summary.to_csv("summary.csv", index=False)
summary.to_json("summary.json", orient="records", indent=2)

index=False stops pandas from writing the row numbers as an extra unnamed column. That extra column is a common surprise when the file is read back later.

For files much larger than memory, consider reading in chunks with chunksize=, or querying the file directly with DuckDB.