Pandas

🎯 Goal

After reading this chapter:

• You’ll learn to use DataFrame and Series as an “in-memory SQL table”
• You’ll be able to work with real CSV/JSON/Parquet files
• You’ll manage missing data, duplicates, type conversions
• You’ll write complex queries with groupby, pivot_table, merge
• You’ll be able to work with time series data

What to learn

• Series and DataFrame structure
• I/O: read_csv, read_json, read_parquet, read_sql, to_* variants
• Indexing: .loc[], .iloc[], boolean indexing, query()
• Missing data: isna(), fillna(), dropna()
• Aggregation: groupby, agg, transform, apply
• Joining: merge, concat, join
• Reshaping: pivot_table, melt, stack, unstack
• Time series: pd.to_datetime, resample, rolling windows
• Categorical data, ordering, ranking

Libraries

pip install pandas pyarrow openpyxl

• pandas — main
• pyarrow — faster engine for parquet files
• openpyxl — working with Excel files

Important topics

Mental model for backend dev

.loc vs .iloc

• .loc[] — label-based(by index name or column name)
• .iloc[] — integer position(by row/column number)

df.loc[5, 'name']      # row with label 5, column 'name'
df.iloc[5, 0]          # row 5, column 0 (like Python list)

inplace problem

In old Pandas the df.fillna(0, inplace=True) pattern was common. In the new version(2.0+) this is deprecated. Instead:

df = df.fillna(0)              # correct # or use copy-on-write mode
pd.set_option('mode.copy_on_write', True)

Method chaining

In ML, transformations are usually written as a chain:

result = (
    df
    .dropna(subset=['price'])
    .query('price > 0')
    .assign(price_log=lambda x: np.log(x.price))
    .groupby('category')
    .agg(avg_price=('price', 'mean'), count=('id', 'count'))
    .sort_values('avg_price', ascending=False)
)

This — using pipe, assign, transform — is a “best practice” in ML data preparation.

Code examples

DataFrame creation and basic operations

import pandas as pd
import numpy as np

# From dict
df = pd.DataFrame({
    "name": ["Ali", "Vali", "Salim", "Karim"],
    "age": [25, 30, 35, 28],
    "city": ["Tashkent", "Samarkand", "Bukhara", "Tashkent"],
    "salary": [1000, 1500, 2000, 1200],
})

# From CSV # df = pd.read_csv("users.csv")

# Basic overview
print(df.head())          # first 5 rows
print(df.info())          # shape, dtype, memory
print(df.describe())      # statistical summary
print(df.shape)           # (4, 4)

Filtering and groupby

# Filtering
adults = df[df.age >= 30]
tashkent_users = df.query("city == 'Tashkent' and salary > 1000")

# Groupby aggregation
by_city = df.groupby("city").agg(
    avg_salary=("salary", "mean"),
    max_age=("age", "max"),
    count=("name", "count"),
).reset_index()
print(by_city)

# Multiple aggregation
stats = df.groupby("city")["salary"].agg(["mean", "std", "min", "max"])

Missing data and data cleaning

# Synthetic missing data
df.loc[0, "salary"] = np.nan
df.loc[2, "city"] = None

# Detection
print(df.isna().sum())            # number of NaN per column

# Fill strategies
df["salary"] = df["salary"].fillna(df["salary"].median())
df["city"] = df["city"].fillna("Unknown")

# Or drop
df_clean = df.dropna()

Merge and join

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4],
    "user_name": ["Ali", "Vali", "Ali", "Karim"],
    "amount": [100, 200, 150, 75],
})

# INNER JOIN (default)
merged = df.merge(orders, left_on="name", right_on="user_name")

# LEFT JOIN
all_users = df.merge(orders, left_on="name", right_on="user_name", how="left")

# Users and their total orders
user_totals = (
    df.merge(orders, left_on="name", right_on="user_name", how="left")
      .groupby("name")["amount"].sum()
      .fillna(0)
      .reset_index()
)

Time series

# Random daily sales data
dates = pd.date_range("2024-01-01", periods=365, freq="D")
sales = pd.DataFrame({
    "date": dates,
    "sales": np.random.poisson(100, size=365) + np.sin(np.arange(365) / 30) * 20,
})

sales = sales.set_index("date")

# Weekly aggregation
weekly = sales.resample("W").sum()

# 30-day rolling mean
sales["rolling_30"] = sales["sales"].rolling(window=30).mean()

# Extract year, month, weekday
sales["month"] = sales.index.month
sales["weekday"] = sales.index.day_name()

Backend integration

1. Django ORM → Pandas

from django.db.models import Sum
import pandas as pd

# Django QuerySet → DataFrame
qs = Order.objects.values('user_id', 'amount', 'created_at')
df = pd.DataFrame(list(qs))

# Or directly SQL
df = pd.read_sql("SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'",
                 connection)

2. CSV export endpoint in FastAPI

from fastapi import FastAPI
from fastapi.responses import StreamingResponse
import pandas as pd
import io

app = FastAPI()

@app.get("/reports/orders.csv")
async def export_orders():
    df = pd.read_sql("SELECT * FROM orders", engine)
    # Enrichment: add new column
    df["revenue_per_item"] = df["total"] / df["quantity"]
    
    stream = io.StringIO()
    df.to_csv(stream, index=False)
    
    return StreamingResponse(
        iter([stream.getvalue()]),
        media_type="text/csv",
        headers={"Content-Disposition": "attachment; filename=orders.csv"},
    )

3. Background job — daily report

# Celery task
@app.task
def generate_daily_report():
    df = pd.read_sql("SELECT * FROM events WHERE date = CURRENT_DATE - 1", engine)
    
    report = (
        df.groupby("country")
          .agg(users=("user_id", "nunique"),
               revenue=("amount", "sum"),
               avg_session=("duration_sec", "mean"))
          .sort_values("revenue", ascending=False)
    )
    
    report.to_excel(f"/reports/daily_{date.today()}.xlsx")
    # Email send via Celery beat

Resources

• Official Pandas docs — pandas.pydata.org/docs/user_guide/
• “Python for Data Analysis” — Wes McKinney (Pandas creator, 3rd edition) — MUST READ
• “Modern Pandas” — Tom Augspurger (blog series) — best practices
• Kaggle Learn — Pandas — free mini-course
• DataCamp / pandas tutor — pandastutor.com — visual debug

🏋️ Exercises

🟢 Easy

1. Read a CSV file (e.g., Titanic dataset), look at the first 10 rows, and output info(), describe().
2. Filter by one column (age > 18).
3. Create a new column (bmi = weight / height **2).

🟡 Medium

1. On Titanic, output the comparison of Sex and Pclass by Survived (pivot table).
2. Compare strategies for missing values: fillna(mean) vs fillna(median) vs dropna() — compare statistics for each.
3. Time series: generate fake sales data over 1 year and find/plot weekly trends.

🔴 Hard

1. Django/FastAPI endpoint: /api/analytics/cohort/ — divide users into cohorts by registration month and return each cohort’s retention over the next 6 months as heatmap data. Use Pandas pivot_table and groupby.
2. Streaming CSV: read a 1 GB CSV file with chunks so it doesn’t fit in memory (chunksize), aggregate per chunk, return final result.

Capstone

notebooks/month-01/02_pandas_practice.ipynb:

• Download the e-commerce dataset (Olist Brazilian e-commerce Kaggle)
• Make merge between 5 tables
• For each product category:
• Average price
• Number of orders
• Average delivery time (in days)
• Customer satisfaction rating (mean review_score)
• Rank the top 10 revenue-generating categories

✅ Checklist

• I know the difference between DataFrame and Series
• I understand the difference between .loc and .iloc, use each in its place
• I’ve mastered the groupby + agg pattern
• I know at least 3 strategies for missing data
• I know the how parameters of merge (inner, left, right, outer)
• I use resample and rolling in time series
• I write readable code via method chaining
• I convert SQL results from Django/FastAPI into DataFrame

Let’s move on to Matplotlib and Seaborn.