Exploring with pandas

Introduction

SQL is excellent for pulling and summarising data. pandas is excellent for exploring it, cleaning it and making charts. A common workflow is to use SQL to fetch what you need, then pandas to explore. The outputs below are from real runs with pandas 3.1.

One tidy table to explore

Join the three tables so each row is a loan with the book's genre and the member's city, and ask pandas to read the dates as dates:

df = pd.read_sql_query(
    """SELECT l.*, b.genre, m.city
       FROM loans l
       JOIN books b ON b.id = l.book_id
       JOIN members m ON m.id = l.member_id""",
    con,
    parse_dates=["loaned_on", "returned_on"],
)
print(df.shape)

(30, 7)

Thirty rows and seven columns. Always check the shape: it should match what you expect.

Types and missing values

print(df.dtypes.astype(str).to_dict())
print(df["returned_on"].isna().sum())

{'id': 'int64', 'book_id': 'int64', 'member_id': 'int64', 'loaned_on': 'datetime64[us]', 'returned_on': 'datetime64[us]', 'genre': 'str', 'city': 'str'}
3

The dates are real dates, which allows arithmetic. Three returned_on values are missing: the books still out. Missing data is normal, but you must decide how to treat it in each calculation. (The exact type names can differ between pandas versions.)

Counting categories

print(df["genre"].value_counts().to_string())

genre
Crime      11
Fiction     8
Food        6
Home        5

Crime accounts for 11 of the 30 loans, the largest share, although it has only two of the eight books.

Loan length

Subtract the dates to get days. Rows with a missing return date give a missing result:

df["days"] = (df["returned_on"] - df["loaned_on"]).dt.days
print(df["days"].describe().round(1).to_string())

count    27.0
mean     16.9
std       7.1
min       7.0
25%       9.5
50%      18.0
75%      22.5
max      29.0

• count is 27, not 30, because the three missing values are skipped.
• The mean is 16.9 days, and the median (50%) is 18.
• Loans range from 7 to 29 days.

Summaries by group

print(df.groupby("city")["days"].mean().round(1).to_string())

city
Hull     22.0
Leeds    15.8
York     18.1

Hull's 22.0 days comes from a single loan, so it tells us almost nothing. Always check how many rows are behind an average.

print(df.groupby("city")["days"].agg(["count", "mean"]).round(1))

Adding count next to the mean shows the sample size. It is a habit worth keeping.

Keep a record

Write down what you did in order: load, check, summarise. In a notebook or a script, your steps can be repeated by anyone, including you next month.

Try it yourself

Find the loan with the longest length, and the average length for each genre, together with the number of loans in each genre. Which genre average rests on the fewest loans?

Summary

• Load joined SQL results into pandas, and check shape, types and missing values straight away.
• value_counts counts categories, and subtracting dates gives loan lengths in days.
• Always show the count behind a group average, because small groups mislead.