Data analysis with pandas: first steps through a concrete case

When a spreadsheet becomes too slow or the same operations have to be repeated every month, pandas is often the natural next step. It is a Python library for working with tabular data: it lets you load a file, clean it, transform it and summarise it in a few lines of code, which can be rerun unchanged on next month's file. Let us go through the fundamental steps on a concrete example: a sales export from an online shop.

Loading the data

The file sales.csv contains the columns date, order_code, product, category, quantity, price, region.

import pandas as pd

df = pd.read_csv(
    "sales.csv",
    sep=";",
    decimal=",",
    parse_dates=["date"],
    dayfirst=True,
    dtype={"order_code": str},
)

The result is a DataFrame, the central pandas object: a table with named rows and columns, similar to a spreadsheet. The parameters are not a detail: they state the Italian separator, the decimal comma, which columns are dates and in what order day and month are written, and that the order code is text even though it contains only digits.

Looking at the data before using it

The first step of any analysis is understanding what you have in front of you:

df.shape        # number of rows and columns
df.head()       # the first five rows
df.dtypes       # the type of each column
df.describe()   # basic statistics on numeric columns
df.isna().sum() # missing values per column

These five commands reveal most problems. If price shows up as object instead of numeric, some value was not recognised as a number. If describe() shows a negative minimum quantity, there are returns or errors to understand. If a column has many missing values, you need to decide how to handle them before doing any calculation.

Cleaning

Some frequent operations:

# inconsistent spaces and capitals
df["region"] = df["region"].str.strip().str.title()

# fully duplicated rows
df = df.drop_duplicates()

# invalid values
df = df[df["quantity"] > 0]

# new calculated column
df["amount"] = df["quantity"] * df["price"]

Each operation returns a new result, which must be reassigned. A common mistake is writing df.drop_duplicates() without reassigning and believing the duplicates have been removed.

Operations work on whole columns at a time: df["quantity"] * df["price"] multiplies all the rows together. Writing a for loop that walks through the rows one by one works, but it is much slower and almost always unnecessary.

Filtering

Rows are selected with conditions inside square brackets:

march = df[(df["date"] >= "2025-03-01") & (df["date"] < "2025-04-01")]
north = df[df["region"].isin(["Lombardia", "Piemonte", "Veneto"])]

Multiple conditions are combined with & (and) and | (or), and each must be wrapped in brackets. Using Python's and and or keywords raises an error.

Grouping and summarising

This is the equivalent of GROUP BY in SQL and of pivot tables in spreadsheets:

by_category = (
    df.groupby("category")
      .agg(orders=("order_code", "nunique"),
           units=("quantity", "sum"),
           revenue=("amount", "sum"))
      .sort_values("revenue", ascending=False)
)

The result is a table with one row per category: the number of distinct orders, the units sold and the revenue, sorted from the most important category. Counting orders with nunique rather than count avoids counting the same order several times when it contains more than one product.

For a monthly trend:

monthly = df.groupby(df["date"].dt.to_period("M"))["amount"].sum()

And for a real pivot table, with regions as rows and months as columns:

pivot = df.pivot_table(index="region",
                       columns=df["date"].dt.to_period("M"),
                       values="amount", aggfunc="sum", fill_value=0)

Joining two tables

If product costs are in another file, you join them as with a SQL join:

costs = pd.read_csv("costs.csv", sep=";", decimal=",")
df = df.merge(costs, on="product", how="left", validate="many_to_one")
df["margin"] = df["amount"] - df["quantity"] * df["cost"]

The validate parameter is a valuable safeguard: if the same product appears twice in the costs file, pandas raises an error instead of silently duplicating the sales rows and inflating the totals. After the merge it is worth checking how many products did not find a cost, with df["cost"].isna().sum().

Saving the results

by_category.to_csv("category_summary.csv", sep=";", decimal=",")
by_category.to_excel("summary.xlsx")  # requires the openpyxl library

The advantage over manual work is that the script remains: next month you just replace the input file and run it again, getting the same steps applied in the same way.

In short

• read_csv should be configured with separator, decimals, dates and the types of code columns.
• Use shape, head, dtypes, describe and isna before any analysis.
• Operations work on whole columns, and the result must be reassigned.
• groupby, agg and pivot_table summarise data; nunique avoids double counting.
• In merge, the validate parameter prevents silent duplication of rows.