Y

pandas · Beginner

Load, inspect and filter a real sales file

3 min read

Why it matters

Take-home assignments for analyst and data scientist roles almost always start with "here is a CSV". Reviewers read your first notebook cells closely: did you check types, missing values and duplicates before computing anything? Those habits are what this lesson builds.

The concept

A DataFrame is a table: named columns, each with one dtype (int64, float64, object or string, datetime64, bool), and an index labelling the rows.

The first five things to run on any new data:

python
df.shape            # rows, columns
df.dtypes           # is every column the type you expect?
df.head()           # what do values look like?
df.isna().sum()     # missing values per column
df.duplicated().sum()

Selecting data:

  • columns: df["Country"], df[["Invoice", "Quantity"]]

  • rows by condition (a boolean mask): df[df["Quantity"] > 0]

  • rows and columns by label: df.loc[mask, ["Invoice", "Price"]]

Add columns with vectorised arithmetic, never a Python loop over rows: df["Revenue"] = df["Quantity"] * df["Price"].

Worked example: Online Retail II

inspect.py
import pandas as pd

df = pd.read_excel("online_retail_II.xlsx", sheet_name="Year 2010-2011",
                   dtype={"Invoice": str, "StockCode": str})
print(df.shape)                 # (541910, 8)
print(df.dtypes)
print(df.isna().sum())          # Customer ID: 135080 missing, Description: 1454
print(df.duplicated().sum())    # 5268 exact duplicate lines

Reading Invoice as text matters: cancellations are marked by a leading C, and a numeric type would fail or drop it. Customer ID loads as float because it has missing values; pandas uses NaN, which is a float.

Now build a clean view in named steps:

clean.py
is_cancel = df["Invoice"].str.startswith("C")
clean = (
    df[~is_cancel & (df["Quantity"] > 0) & df["Customer ID"].notna()]
    .drop_duplicates()
    .assign(
        customer_id=lambda d: d["Customer ID"].astype(int),
        revenue=lambda d: d["Quantity"] * d["Price"],
    )
)
print(len(clean))
print(clean["revenue"].describe())
print(clean.nlargest(3, "revenue")[["Invoice", "Description", "Quantity", "revenue"]])

The largest line is invoice 581483: 80,995 units of "PAPER CRAFT , LITTLE BIRDIE" for 168,469.60. Its cancellation, C581484, was dropped by the filter, so this order is still in clean. Your filter created an error. Fix it by removing orders that have a matching cancellation, or at least list the largest lines in your report.

Common mistakes

  • Chained assignment. df[df.Quantity > 0]["Revenue"] = ... may modify a copy and warn. Use .loc or .assign.

  • Letting pandas guess types for ids. Leading zeros and letter prefixes disappear. Pass dtype= for id columns.

  • Dropping NaN everywhere with dropna(). You may remove rows for a column you do not even use. Drop by the columns that matter: dropna(subset=[...]).

  • Looping with iterrows(). It is hundreds of times slower than vectorised operations.

Interview angle

A live task often starts: "load this and tell me what you see". Narrate the checks: shape, types, missing values, duplicates, suspicious values. Finding the cancelled 80,995-unit order before being asked is the kind of thing interviewers remember.

Practice task

  1. How many distinct customers, products and countries are in clean?

  2. Find the invoices where every line was cancelled.

  3. Remove orders that have an exact matching cancellation (same customer, stock code and absolute quantity) and recompute total revenue.

Further reading