Python
Topic 19 · Python for data · ⏱ about 8 h

pandas: Working with Tables

DataFrames: loading, inspecting, selecting, filtering, creating columns, grouping, merging, reshaping and dates.

Show:
In this topic
Beginner

Series and DataFrames

pandas gives Python spreadsheets that you control with code. A DataFrame is a table: named columns, each holding one type, and an index labelling the rows. A single column is a Series. Under the hood each column is a NumPy array, so vectorised operations are fast.

import pandas as pd df = pd.read_csv("appointments.csv", parse_dates=["appt_date"]) df.head() # first 5 rows df.shape # (rows, columns) df.info() # column types and missing values df.describe() # count, mean, std, min, quartiles, max for numeric columns df["clinic"].value_counts() # how often each value occurs

Always look before you model: head, info, describe and value_counts catch most data problems in the first five minutes (wrong types, impossible values, missing data, surprising categories).

Beginner

Selecting, filtering and new columns

df["age"] # one column (Series) df[["age", "clinic"]] # several columns (DataFrame) df[df["age"] > 75] # rows where the condition is True df[(df["clinic"] == "North") & (df["lead_days"] > 14)] # & and |, with brackets df.loc[df["age"] > 75, ["patient_id", "age"]] # rows by condition, columns by name df.iloc[0:10, 0:3] # rows and columns by position df.sort_values("lead_days", ascending=False).head(10) df["long_lead"] = (df["lead_days"] > 30).astype(int) # new column, vectorised df["age_group"] = pd.cut(df["age"], [0, 25, 65, 120], labels=["young", "adult", "senior"])

Avoid looping over rows (for i, row in df.iterrows()): it is slow and rarely needed. Express the rule as a column operation instead. When you filter and then want to change the result, add .copy() to avoid the SettingWithCopyWarning.

Try it

Try it: pandas operations, step by step

Pick an operation to see the pandas code and the exact table it produces from a small appointments table.

Interactive: this part runs in your browser.

Try it

Code example: pandas essentials

A worked example: read it step by step (every part is explained), then run it and experiment.

Interactive: this part runs in your browser.

Beginner

Grouping, merging, reshaping and dates

groupby splits the table into groups, applies a function to each, and combines the results: the same idea as SQL's GROUP BY.

df.groupby("clinic")["no_show"].mean() df.groupby(["clinic", "weekday"]).agg(n=("appt_id", "count"), rate=("no_show", "mean")) df["patient_prev"] = df.groupby("patient_id")["no_show"].cumsum() - df["no_show"] # per-group running count

merge joins tables like SQL joins. Always check the row count: if the key is not unique on the right, rows multiply.

m = appts.merge(clinics, on="clinic", how="left", validate="many_to_one") # raises if clinics has duplicate keys

Reshaping: pivot_table turns long data into a grid (rows × columns); melt does the reverse. Dates: convert with pd.to_datetime, then use the .dt accessor (.dt.month, .dt.dayofweek), subtract dates to get time differences, and resample("W") to aggregate by week. Missing values: find them with isna().sum(), then decide per column whether to drop, fill (fillna) or flag them, as the Data Cleaning topic explains.

Try it

Code example: merge, pivot and dates

A worked example: read it step by step (every part is explained), then run it and experiment.

Interactive: this part runs in your browser.

Try it

Cheat sheet: pandas

The commands you will use most, on one screen. Come back to it while you work.

Interactive: this part runs in your browser.

Check yourself

Quiz

1. Which command shows column types and missing-value counts?

2. Which selects rows where clinic is North AND age > 60?

3. df.groupby("clinic")["no_show"].mean() returns…

4. A left merge increases the number of rows. Why?

5. Why avoid iterrows() loops?

Go deeper

📚 Further reading

Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.

Ready