Lesson 22 of 40 Data Science Advanced ⏱ 35 min

pandas — DataFrames & Data Wrangling

Load, clean, transform, and aggregate data with pandas — DataFrame, Series, groupby, merge, pivot_table, and handling missing values.

Part 1: What You Will Learn

Create and inspect DataFrames.
Clean missing values and transform columns.
Aggregate data with groupby().
Combine tables with merge() and summarize with pivot_table().

Part 2: Key Concepts

pandas builds labelled tabular data structures on top of NumPy. A Series is one labelled column and a DataFrame is a table of rows and columns. Data wrangling means cleaning, reshaping, combining, and summarizing data so it is ready for analysis.

Part 3: Topic-Specific Code Example

# Install once: pip install pandas
import pandas as pd

sales = pd.DataFrame({
    "sale_id": [1, 2, 3, 4, 5],
    "product_id": [101, 102, 101, 103, 102],
    "region": ["North", "North", "South", "South", "North"],
    "quantity": [2, 1, 3, None, 4],
    "unit_price": [50.0, 80.0, 50.0, 120.0, 80.0],
})
products = pd.DataFrame({
    "product_id": [101, 102, 103],
    "product": ["Mouse", "Keyboard", "Monitor"],
})

sales["quantity"] = sales["quantity"].fillna(0).astype(int)
sales["revenue"] = sales["quantity"] * sales["unit_price"]

print("Clean data:\n", sales)
print("\nRevenue by region:")
print(sales.groupby("region")["revenue"].sum())

combined = sales.merge(products, on="product_id", how="left")
print("\nMerged data:\n", combined)

summary = combined.pivot_table(
    index="product",
    columns="region",
    values="revenue",
    aggfunc="sum",
    fill_value=0,
)
print("\nPivot table:\n", summary)

Part 4: How the Example Works

fillna() cleans the missing quantity before converting it to integers. A calculated revenue column is created using vectorised column arithmetic. groupby() aggregates by region, merge() joins product names, and pivot_table() creates a cross-tab summary.

Part 5: Hands-On Practice

Mini project — Student Results DataFrame. Create columns for student, course, test, and exam. Fill a missing test mark, calculate a weighted final score, group by course, and build a pivot table showing average score by course and pass/fail status.

Part 6: Next Steps

Run and modify the examples in Visual Studio 2026, then continue to Lesson 23. Return to Python Tutorial Home to review the complete curriculum.

📘 Want the complete guide with projects? Get the book →