Lecture 15 — Pandas for Data Analysis#
🎯 Learning Objectives
By the end of this lecture, you will be able to:
- ✅ Understand the Pandas
SeriesandDataFramedata structures - ✅ Read and write data from CSV, Excel, and other formats
- ✅ Select, filter, and transform data with indexing and boolean masks
- ✅ Group, aggregate, and merge datasets for analysis
- ✅ Perform basic data cleaning: handle missing values, duplicates, and type conversions
📑 Table of Contents
- ● 🐼 Lecture 15 — Pandas for Data Analysis
- 1. ▸ 🐼 1. What is Pandas?
- 2. ▸ 📊 2. Series — 1D Labeled Array
- 3. ▸ 📋 3. DataFrame — The Core of Pandas
- 4. ▸ 📁 4. Reading & Writing Data
- 5. ▸ 🔍 5. Selecting & Filtering Data
- 6. ▸ 🔄 6. Transforming Data
- 7. ▸ 📊 7. Grouping & Aggregation
- 8. ▸ 🛠️ 8. Handling Missing Data
- 9. ▸ 🔗 9. Merging & Joining DataFrames
- 10. ▸ 🚀 10. Putting It All Together
- 11. ▸ 📝 11. Practice Exercises
- 12. ▸ 🎯 Key Takeaways
🐼 1. What is Pandas?
Pandas is Python's most widely used library for data manipulation and analysis. Built on top of NumPy, it provides two core data structures: the Series (1D labeled array) and the DataFrame (2D labeled table). Think of a DataFrame as a programmable spreadsheet with the speed of NumPy under the hood.
Pandas is used by data scientists, researchers, financial analysts, and anyone who works with structured data. In this lecture, we’ll work through a practical workflow: loading data, exploring it, cleaning it, transforming it, and answering questions with it.
import pandas as pd
import numpy as np
print(f"Pandas version: {pd.__version__}")
print(f"NumPy version: {np.__version__}")
📊 2. Series — 1D Labeled Array
A Series is like a NumPy array with an index — each value has a label. You can think of it as a single column of a spreadsheet.
# Creating a Series from a list with custom index
temperatures = pd.Series(
[22.5, 25.1, 19.8, 23.4, 27.0],
index=["Mon", "Tue", "Wed", "Thu", "Fri"],
name="Temperature (°C)"
)
print(temperatures)
print(f"\nType: {type(temperatures)}")
print(f"Mean temperature: {temperatures.mean():.1f}°C")
print(f"Hottest day: {temperatures.idxmax()} ({temperatures.max()}°C)")
# Creating from a dictionary — keys become the index
populations = pd.Series({
"Dakar": 1_146_000,
"Abidjan": 4_707_000,
"Lagos": 15_400_000,
"Nairobi": 4_397_000,
"Cape Town": 4_618_000,
})
print(populations)
print(f"\nTotal population: {populations.sum():,}")
print(f"\nCities with > 4M people:")
print(populations[populations > 4_000_000])
📋 3. DataFrame — The Core of Pandas
A DataFrame is a 2D table with labeled rows and columns. It is the central data structure in Pandas and the one you'll use most. Each column is a Series, and all columns share the same index.
Let’s create a DataFrame from scratch and explore it. We’ll use this dataset throughout the lecture:
# Creating a DataFrame from a dictionary of lists
data = {
"Name": ["Alice", "Bob", "Charlie", "Diana", "Eve", "Frank"],
"Age": [25, 30, 35, 28, 22, 31],
"City": ["Dakar", "Paris", "Lagos", "Nairobi", "Dakar", "Lagos"],
"Department": ["Physics", "CS", "Math", "Physics", "CS", "Math"],
"Score": [88.5, 92.0, 76.3, 95.1, 84.7, 71.2],
}
df = pd.DataFrame(data)
print(df)
# Basic inspection — always start here with a new dataset
print(f"Shape: {df.shape} ({df.shape[0]} rows, {df.shape[1]} columns)")
print(f"Columns: {df.columns.tolist()}")
print(f"\nData types:")
print(df.dtypes)
print(f"\nSummary statistics (numeric columns):")
print(df.describe())
# Quick look at the data
print("First 3 rows:")
print(df.head(3))
print("\nLast 2 rows:")
print(df.tail(2))
print("\nRandom sample of 2 rows:")
print(df.sample(2))
📁 4. Reading & Writing Data
In practice, you rarely create DataFrames by hand. Pandas can read from CSV, Excel, JSON, SQL databases, and more. This is where Pandas truly shines — turning messy real-world files into clean, analyzable tables.
The most common format in science is CSV (Comma-Separated Values):
# Reading data
df = pd.read_csv("data.csv")
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")
df = pd.read_json("data.json")
# Writing data
df.to_csv("output.csv", index=False)
df.to_excel("output.xlsx", index=False)
# Let's create a CSV file and read it back
csv_content = """Name,Age,City,Department,Score
Alice,25,Dakar,Physics,88.5
Bob,30,Paris,CS,92.0
Charlie,35,Lagos,Math,76.3
Diana,28,Nairobi,Physics,95.1
Eve,22,Dakar,CS,84.7
Frank,31,Lagos,Math,71.2"""
with open("students.csv", "w") as f:
f.write(csv_content)
# Read the CSV into a DataFrame
df = pd.read_csv("students.csv")
print(df)
print(f"\nLoaded {len(df)} rows from students.csv")
🔍 5. Selecting & Filtering Data
Selecting subsets of data is the most common Pandas operation. Pandas provides multiple ways to do this, similar to how NumPy uses indexing and slicing (Lecture 10).
# Selecting columns
print("--- Single column (returns Series) ---")
print(df["Name"])
print()
print("--- Multiple columns (returns DataFrame) ---")
print(df[["Name", "Score"]])
# Selecting rows with .loc (label-based) and .iloc (position-based)
print("--- Row by label (index 0) ---")
print(df.loc[0])
print()
print("--- Rows 1-3, specific columns ---")
print(df.loc[1:3, ["Name", "City", "Score"]])
print()
print("--- First 2 rows by position ---")
print(df.iloc[:2])
# Boolean filtering — the most powerful selection method
# This works exactly like NumPy boolean masking!
print("--- Students who scored above 85 ---")
high_scorers = df[df["Score"] > 85]
print(high_scorers)
print()
# Multiple conditions: use & (and), | (or), ~ (not)
print("--- Dakar students who scored above 85 ---")
dakar_high = df[(df["City"] == "Dakar") & (df["Score"] > 85)]
print(dakar_high)
🔄 6. Transforming Data
Once you've selected your data, you often need to transform it: add computed columns, sort, rename, or apply functions. This is where comprehensions and lambda functions become very handy.
# Adding new columns
df["Grade"] = df["Score"].apply(lambda x: "A" if x >= 90 else "B" if x >= 80 else "C")
df["Passed"] = df["Score"] >= 70
print(df)
# Sorting
print("--- Sorted by Score (best first) ---")
print(df.sort_values("Score", ascending=False))
# Renaming columns
df_renamed = df.rename(columns={"Name": "Student", "Score": "Exam_Score"})
print(f"New columns: {df_renamed.columns.tolist()}")
📊 7. Grouping & Aggregation
The groupby operation is one of Pandas' most powerful features. It splits data into groups, applies a function to each group, and combines the results. This is the "split-apply-combine" paradigm.
# Group by city and compute statistics
print("--- Average score by city ---")
city_scores = df.groupby("City")["Score"].mean()
print(city_scores)
print()
print("--- Full statistics by city ---")
city_stats = df.groupby("City")["Score"].agg(["count", "mean", "min", "max"])
print(city_stats)
# Group by department
print("--- Students per department ---")
dept_summary = df.groupby("Department").agg({
"Name": "count",
"Score": ["mean", "std"],
"Age": "mean"
})
print(dept_summary)
🛠️ 8. Handling Missing Data
Real-world data is messy. Sensors fail, surveys go incomplete, records get corrupted. Pandas represents missing values as NaN (Not a Number) and provides tools to detect, remove, or fill them.
# Create a DataFrame with missing values
df_messy = pd.DataFrame({
"Sensor_A": [22.1, 23.5, np.nan, 24.0, 22.8],
"Sensor_B": [np.nan, 19.2, 20.1, np.nan, 18.9],
"Sensor_C": [15.0, np.nan, np.nan, 16.2, 15.8],
}, index=["08:00", "09:00", "10:00", "11:00", "12:00"])
print("Raw sensor data (NaN = sensor failure):")
print(df_messy)
print(f"\nMissing values per column:\n{df_messy.isna().sum()}")
print(f"Total missing: {df_messy.isna().sum().sum()}")
# Strategy 1: Drop rows with any missing values
print("--- Drop rows with NaN ---")
print(df_messy.dropna())
print()
# Strategy 2: Fill with a constant
print("--- Fill NaN with 0 ---")
print(df_messy.fillna(0))
print()
# Strategy 3: Forward fill (use last known value)
print("--- Forward fill (propagate last valid reading) ---")
print(df_messy.ffill())
print()
# Strategy 4: Fill with column mean
print("--- Fill with column mean ---")
print(df_messy.fillna(df_messy.mean()))
🔗 9. Merging & Joining DataFrames
In real projects, data is often split across multiple tables or files. pd.merge() lets you combine them based on common columns, similar to SQL JOINs.
# Two separate tables that share a key column
students = pd.DataFrame({
"student_id": [1, 2, 3, 4],
"name": ["Alice", "Bob", "Charlie", "Diana"],
"department": ["Physics", "CS", "Math", "Physics"],
})
grades = pd.DataFrame({
"student_id": [1, 2, 3, 5], # Note: student 5 has no match in students
"exam": ["Midterm", "Midterm", "Midterm", "Midterm"],
"grade": [88, 92, 76, 95],
})
print("Students:")
print(students)
print("\nGrades:")
print(grades)
# Inner merge: only rows with matching keys in BOTH tables
print("--- Inner merge (intersection) ---")
print(pd.merge(students, grades, on="student_id", how="inner"))
print()
# Left merge: keep ALL students, even without grades
print("--- Left merge (keep all students) ---")
print(pd.merge(students, grades, on="student_id", how="left"))
print()
# Outer merge: keep everything from both tables
print("--- Outer merge (union) ---")
print(pd.merge(students, grades, on="student_id", how="outer"))
🚀 10. Putting It All Together
Let's combine everything you've learned into a mini data analysis project. This is the typical workflow a data scientist follows every day.
# Complete mini-analysis: Student performance report
df = pd.read_csv("students.csv")
# 1. Quick exploration
print("=== Dataset Overview ===")
print(f"Shape: {df.shape}")
print(f"Columns: {df.columns.tolist()}")
print(df.head())
print()
# 2. Add computed columns
df["Grade"] = pd.cut(df["Score"], bins=[0, 60, 70, 80, 90, 100],
labels=["F", "D", "C", "B", "A"])
df["Status"] = df["Score"].apply(lambda x: "Pass" if x >= 60 else "Fail")
# 3. Group analysis
print("=== Average Score by City ===")
print(df.groupby("City")["Score"].mean().sort_values(ascending=False))
print()
print("=== Grade Distribution ===")
print(df["Grade"].value_counts().sort_index())
print()
# 4. Filter for top performers
top = df[df["Score"] >= 85].sort_values("Score", ascending=False)
print(f"=== Top Performers (Score >= 85) ===")
print(top[["Name", "City", "Score", "Grade"]])
📝 11. Practice Exercises
Exercise 1: Create a DataFrame of 5 products with columns Name, Price, Quantity. Compute a Total column (Price * Quantity). Find the most expensive product and the total revenue.
Exercise 2: Load students.csv, add a Pass/Fail column (pass if Score >= 75), group by City, and compute the pass rate for each city. Save the result to city_report.csv.
Exercise 3: Create two DataFrames — orders (with columns: order_id, customer_id, amount) and customers (with columns: customer_id, name, city). Merge them to create a report showing each customer’s total spending.
Exercise 4: Create a DataFrame with some NaN values representing missing sensor readings over a week. Practice dropna(), fillna(), ffill(), and interpolate(). Which strategy makes the most sense for time-series sensor data?
# Exercise 1: Your code here
Click to show solution
import pandas as pd
products = pd.DataFrame({
"Name": ["Laptop", "Mouse", "Keyboard", "Monitor", "Headset"],
"Price": [999.99, 29.99, 59.99, 349.99, 79.99],
"Quantity": [10, 150, 75, 30, 60],
})
products["Total"] = products["Price"] * products["Quantity"]
print(products)
print(f"\nMost expensive: {products.loc[products['Price'].idxmax(), 'Name']}")
print(f"Total revenue: ${products['Total'].sum():,.2f}")
# Exercise 2: Your code here
Click to show solution
import pandas as pd
# Create a sample students.csv for demonstration
sample = pd.DataFrame({
"Name": ["Alice", "Bob", "Charlie", "Diana", "Eve", "Frank"],
"City": ["Paris", "London", "Paris", "London", "Paris", "London"],
"Score": [88, 72, 95, 60, 45, 80],
})
sample.to_csv("students.csv", index=False)
# Load and process
df = pd.read_csv("students.csv")
df["Pass/Fail"] = df["Score"].apply(lambda x: "Pass" if x >= 75 else "Fail")
city_report = df.groupby("City").apply(
lambda g: (g["Pass/Fail"] == "Pass").mean() * 100
).reset_index(name="Pass Rate (%)")
print(city_report)
city_report.to_csv("city_report.csv", index=False)
# Exercise 3: Your code here
Click to show solution
import pandas as pd
orders = pd.DataFrame({
"order_id": [1, 2, 3, 4, 5],
"customer_id": [101, 102, 101, 103, 102],
"amount": [250.0, 130.0, 85.0, 320.0, 210.0],
})
customers = pd.DataFrame({
"customer_id": [101, 102, 103],
"name": ["Alice", "Bob", "Charlie"],
"city": ["Paris", "London", "Berlin"],
})
merged = pd.merge(orders, customers, on="customer_id")
spending = merged.groupby("name")["amount"].sum().reset_index()
spending.columns = ["Customer", "Total Spending"]
print(spending)
# Exercise 4: Your code here
Click to show solution
import pandas as pd
import numpy as np
# Create sensor data with some NaN values
sensor = pd.DataFrame({
"Day": ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"],
"Temp": [22.1, np.nan, 23.5, np.nan, 24.0, 23.8, np.nan],
"Humidity": [45, 47, np.nan, 50, np.nan, np.nan, 52],
})
print("Original:")
print(sensor)
# dropna: removes rows with any NaN
print("\ndropna:")
print(sensor.dropna())
# fillna: replace NaN with a fixed value
print("\nfillna(0):")
print(sensor.fillna(0))
# ffill: forward-fill (carry last valid value forward)
print("\nffill:")
print(sensor.ffill())
# interpolate: linear interpolation (best for time-series)
print("\ninterpolate:")
print(sensor.interpolate())
# For time-series sensor data, interpolate() is usually best
# because it estimates missing values based on surrounding trends.
🎯 Key Takeaways
Series(1D) andDataFrame(2D) are the two core Pandas data structures.- Read data from CSV, Excel, JSON with
pd.read_csv(),pd.read_excel(), etc. - Select data with
.loc[](label-based) and.iloc[](position-based). .groupby()splits data into groups for aggregation: sum, mean, count, etc.- Handle missing data with
.isna(),.fillna(), and.dropna().
—
—