pandas Basics: DataFrames, Filtering and groupby in Python
Spreadsheets in Code
Results sheets, attendance registers, survey responses, sales records: most real data comes in tables of rows and columns. Spreadsheets handle small tables well, but they become slow and error-prone with large files and repeated analysis. pandas is Python's library for tabular data. It reads CSV and Excel files, cleans messy values, filters, sorts, groups and summarises, and it combines tables, all in a few lines of code that can be rerun whenever the data changes. It is the first tool data analysts reach for.
This tutorial covers pandas basics with a running example of student results: Series and DataFrames, reading CSV data, inspecting a table, selecting columns and rows with loc and iloc, filtering, adding calculated columns, sorting, handling missing values, grouping and aggregating, merging tables, pivot tables and saving results. Install it with pip install pandas. It builds on the NumPy basics tutorial.
Series and DataFrames
pandas has two main structures:
- A Series is a one-dimensional column of values with an index (labels for the rows).
- A DataFrame is a table: a collection of Series sharing the same index, one per column.
import pandas as pd ages = pd.Series([19, 21, 20], index=["Ada", "Bayo", "Chidi"]) print(ages) print(ages["Bayo"], ages.mean())
Output:
Ada 19 Bayo 21 Chidi 20 dtype: int64 21 20.0
Reading Data from a CSV File
Normally you would write pd.read_csv("results.csv"). To keep this tutorial self-contained, the CSV text is created inside the program and read through io.StringIO, which makes a string behave like a file.
import io csv_text = """name,dept,level,mth101,phy101,csc101 Ada,Physics,100,72,81,65 Bayo,Physics,200,58,64, Chidi,Maths,100,90,77,88 Dami,Maths,200,45,52,60 Efe,Physics,100,81,,70 Femi,Computer,100,66,59,92 """ df = pd.read_csv(io.StringIO(csv_text)) print(df)
Output:
name dept level mth101 phy101 csc101 0 Ada Physics 100 72 81.0 65.0 1 Bayo Physics 200 58 64.0 NaN 2 Chidi Maths 100 90 77.0 88.0 3 Dami Maths 200 45 52.0 60.0 4 Efe Physics 100 81 NaN 70.0 5 Femi Computer 100 66 59.0 92.0
Two marks are missing in the file, and pandas shows them as NaN ("not a number"). Columns containing NaN are stored as floating-point numbers, which is why they print with a decimal point.
Inspecting a DataFrame
print(df.shape) print(list(df.columns)) print(df.head(2)) print(df["mth101"].describe().round(1))
Output:
(6, 6) ['name', 'dept', 'level', 'mth101', 'phy101', 'csc101'] name dept level mth101 phy101 csc101 0 Ada Physics 100 72 81.0 65.0 1 Bayo Physics 200 58 64.0 NaN count 6.0 mean 68.7 std 16.1 min 45.0 25% 60.0 50% 69.0 75% 78.8 max 90.0 Name: mth101, dtype: float64
describe() gives a quick statistical summary, and df.info() lists each column's type and the number of non-missing values. Always inspect new data before analysing it.
Selecting Columns and Rows
print(df["name"].tolist()) # one column, as a Series print(df[["name", "mth101"]].head(3)) # several columns, as a DataFrame print(df.loc[2, "name"]) # loc: by label (row index 2, column "name") print(df.iloc[0, 3]) # iloc: by position (row 0, column 3) print(df.loc[df["dept"] == "Maths", ["name", "level"]])
Output:
['Ada', 'Bayo', 'Chidi', 'Dami', 'Efe', 'Femi']
name mth101
0 Ada 72
1 Bayo 58
2 Chidi 90
Chidi
72
name level
2 Chidi 100
3 Dami 200
locselects by labels: index labels and column names. Its slices include the end label.ilocselects by integer positions, like list indexing. Its slices exclude the end.
Filtering Rows
Filtering works like NumPy's Boolean masks. Combine conditions with & and |, with brackets around each one.
high_maths = df[df["mth101"] >= 70]
print(high_maths["name"].tolist())
physics_100 = df[(df["dept"] == "Physics") & (df["level"] == 100)]
print(physics_100["name"].tolist())
print(df[df["dept"].isin(["Maths", "Computer"])]["name"].tolist())
print(df[df["name"].str.startswith("D")]["name"].tolist())
Output:
['Ada', 'Chidi', 'Efe'] ['Ada', 'Efe'] ['Chidi', 'Dami', 'Femi'] ['Dami']
Adding Calculated Columns
courses = ["mth101", "phy101", "csc101"]
df["average"] = df[courses].mean(axis=1).round(1) # ignores NaN by default
df["passed_all"] = (df[courses] >= 45).all(axis=1)
def grade(mark):
if mark >= 70: return "A"
if mark >= 60: return "B"
if mark >= 50: return "C"
return "D"
df["mth_grade"] = df["mth101"].apply(grade)
print(df[["name", "average", "passed_all", "mth_grade"]])
Output:
name average passed_all mth_grade 0 Ada 72.7 True A 1 Bayo 61.0 False C 2 Chidi 85.0 True A 3 Dami 52.3 True D 4 Efe 75.5 False A 5 Femi 72.3 True B
Bayo and Efe show passed_all as False because a missing mark (NaN) is not greater than or equal to 45. Whether that is right depends on the situation, which is why missing data needs a deliberate decision.
Sorting
ranked = df.sort_values("average", ascending=False)
print(ranked[["name", "average"]].head(3).to_string(index=False))
print(df.sort_values(["dept", "mth101"], ascending=[True, False])[["dept", "name"]].to_string(index=False))
Output:
name average
Chidi 85.0
Efe 75.5
Ada 72.7
dept name
Computer Femi
Maths Chidi
Maths Dami
Physics Efe
Physics Ada
Physics Bayo
to_string(index=False) prints a table without the index column.
Handling Missing Values
print(df[courses].isna().sum().to_dict()) # missing values per column filled = df[courses].fillna(0) # option 1: treat as zero print(filled.loc[1].tolist()) complete = df.dropna(subset=courses) # option 2: drop incomplete rows print(len(complete), "complete records")
Output:
{'mth101': 0, 'phy101': 1, 'csc101': 1}
[58.0, 64.0, 0.0]
4 complete records
Filling with zero, filling with the column mean, or dropping the row each lead to different results. Choose based on what a missing value means: an absent student, a lost script, or a course not taken.
Grouping and Aggregating
groupby splits the rows into groups, applies a calculation to each group, and combines the results. This is the "split-apply-combine" pattern, like a pivot table in a spreadsheet, or GROUP BY in SQL.
print(df.groupby("dept")["mth101"].mean().round(1))
summary = df.groupby("dept").agg(
students=("name", "count"),
best_maths=("mth101", "max"),
avg_overall=("average", "mean"),
).round(1)
print(summary)
Output:
dept
Computer 66.0
Maths 67.5
Physics 70.3
Name: mth101, dtype: float64
students best_maths avg_overall
dept
Computer 1 66 72.3
Maths 2 90 68.6
Physics 3 81 69.7
Merging Tables
pd.merge joins two DataFrames on a shared column, just like a JOIN in SQL.
advisers = pd.DataFrame({
"dept": ["Physics", "Maths", "Chemistry"],
"adviser": ["Dr Okafor", "Prof Bello", "Dr Musa"],
})
merged = pd.merge(df[["name", "dept"]], advisers, on="dept", how="left")
print(merged.to_string(index=False))
Output:
name dept adviser Ada Physics Dr Okafor Bayo Physics Dr Okafor Chidi Maths Prof Bello Dami Maths Prof Bello Efe Physics Dr Okafor Femi Computer NaN
With how="left", every student is kept, even Femi, whose department has no adviser listed. Use how="inner" to keep only the matches.
Pivot Tables
pivot = df.pivot_table(values="mth101", index="dept", columns="level", aggfunc="mean") print(pivot)
Output:
level 100 200 dept Computer 66.0 NaN Maths 90.0 45.0 Physics 76.5 58.0
The pivot table shows the average MTH 101 mark for each department and level. The NaN shows that there are no 200-level Computer students in the data.
Saving Results
ranked[["name", "dept", "average"]].to_csv("ranked.csv", index=False)
print(open("ranked.csv").read().splitlines()[:3])
Output:
['name,dept,average', 'Chidi,Maths,85.0', 'Efe,Physics,75.5']
to_excel writes Excel files (it needs the openpyxl package), and read_excel reads them.
Common Mistakes with pandas
- Using
andororin filters. Use&and|, with brackets around each condition. - Chained assignment, such as
df[df["dept"] == "Maths"]["level"] = 300, which may change a temporary copy instead ofdf. Usedf.loc[df["dept"] == "Maths", "level"] = 300. - Confusing
locandiloc.locuses labels, andilocuses positions. - Ignoring missing values, which silently affect counts, means and comparisons.
- Looping over rows with
fororiterrowswhen a vectorised column operation would be shorter and much faster.
pandas Basics: Practice Exercises
- List the names of the students whose CSC 101 mark is greater than their MTH 101 mark.
- Find the average PHY 101 mark for each level.
- Add a column
totalwith the sum of the three marks, treating missing marks as 0, and show the student with the highest total. - Count the number of students in each department, sorted from most to fewest.
Sample solutions:
print(df[df["csc101"] > df["mth101"]]["name"].tolist())
print(df.groupby("level")["phy101"].mean().round(1).to_dict())
df["total"] = df[courses].fillna(0).sum(axis=1)
best = df.loc[df["total"].idxmax()]
print(best["name"], best["total"])
print(df["dept"].value_counts().to_dict())
Output:
['Dami', 'Femi']
{100: 72.3, 200: 58.0}
Chidi 255.0
{'Physics': 3, 'Maths': 2, 'Computer': 1}
Conclusion
pandas brings spreadsheet-style analysis to Python with code that can be rerun. A DataFrame is a table of labelled columns (Series). Read data with read_csv, inspect it with shape, head and describe, select with column names, loc (labels) and iloc (positions), and filter with Boolean conditions. Add calculated columns with vectorised operations or apply, sort with sort_values, and decide deliberately how to handle NaN. Summarise with groupby and agg, join tables with merge, reshape with pivot_table, and save with to_csv.