1. The Pandas Mental Model: Bridging Raw Files to AI Pipelines
In real-world AI and data engineering, raw data does not arrive as clean, numerical tensors. It arrives as messy, heterogeneous, labeled tables: customer CRM exports, LLM evaluation benchmark logs, database transaction dumps, or JSON API payloads.
The Two Foundational Structures: Series vs. DataFrame
Pandas introduces two primary data structures designed specifically for labeled data:
| Data Structure | Dimensions | Mental Model | Analogy |
|---|---|---|---|
| Series | 1D labeled array | A single column of data with an explicit, labeled Index. All elements within one Series share the same dtype. | Single spreadsheet column or single feature list. |
| DataFrame | 2D tabular container | A collection of aligned Series sharing the same row Index. Different columns can have completely different data types (int, float, string, date). | Complete SQL table, Excel worksheet, or tabular ML dataset. |
- Python Dictionaries: Lack vectorized math, cannot filter 100,000 rows in milliseconds, and consume massive RAM through pointer boxing.
- NumPy ndarrays: Blazingly fast, but require homogeneous types. You cannot store customer strings, timestamps, and float revenue in one NumPy matrix without casting everything to slow Python objects.
- Pandas: Gives you the best of both worlds: column-specific NumPy speed underneath, with labeled headers and flexible data types on top.
2. Creating, Loading & Inspecting Data
Data in AI Engineering is loaded from external storage before processing. Pandas supports native readers for CSV, JSON, Parquet, and SQL connections:
# Construct from dictionary of lists
df = pd.DataFrame({
"model": ["GPT-4o", "Claude-3.5", "Gemini-1.5"],
"latency_ms": [180, 215, 310],
"accuracy": [0.94, 0.91, 0.88]
})
# Industrial loading idioms
df_csv = pd.read_csv("evals.csv")
df_parquet = pd.read_parquet("features.parquet") # 5x faster, compact binary format
The Mandatory Inspection Protocol
Never modify or train on a dataset before inspecting it. Running inspection functions detects missing values, wrong column types, and duplicate rows before they poison your neural network:
| index | id | model | task | latency_ms | tokens | score | status |
|---|---|---|---|---|---|---|---|
| 0 | 101 | GPT-4o | Coding | 184 | 420 | 0.94 | ● SUCCESS |
| 1 | 102 | Claude-3.5 | Summarization | 215 | 680 | 0.91 | ● SUCCESS |
| 2 | 103 | Gemini-1.5 | Reasoning | 310 | 1200 | 0.88 | ● SUCCESS |
| 3 | 104 | Llama-3.1 | Coding | 95 | 350 | 0.82 | ● SUCCESS |
| 4 | 105 | Mistral-Large | Translation | 142 | 510 | 0.89 | ● SUCCESS |
3. Selecting, Filtering & Transforming Data
Extracting subsets of records and features is the core mechanic of exploratory data analysis. Pandas separates column selection from row filtering using dedicated indexing accessors:
models = df["model"] # 1D Series
features = df[["model", "score"]] # 2D DataFrame
# .loc (Label-based) vs .iloc (Position-based)
row_0 = df.iloc[0] # First physical row
cell = df.loc[0, "score"] # Row with index label 0, column 'score'
# Multiple boolean conditions: MUST use parentheses + & (and), | (or), ~ (not)
elite = df[(df["score"] >= 0.90) & (df["latency_ms"] < 200)]
df[(df['score'] >= 0.85)]
| model | task | latency_ms | score | status |
|---|---|---|---|---|
| GPT-4o | Coding | 184 ms | 0.94 | ● SUCCESS |
| Claude-3.5 | Summarization | 215 ms | 0.91 | ● SUCCESS |
| Gemini-1.5 | Reasoning | 310 ms | 0.88 | ● SUCCESS |
| Mistral-Large | Translation | 142 ms | 0.89 | ● SUCCESS |
4. Context-Aware Data Cleaning (Missing Values & Types)
Data cleaning is not a mechanical checklist—it requires domain context. Never blindly fill missing values with 0. In an AI dataset, filling missing latency with 0 falsifies performance, while dropping missing rows indiscriminately causes training data loss.
| Data Cleaning Method | Function Call | Strategic Best Practice in AI Engineering |
|---|---|---|
| Detect Missing | df.isna().sum() | Count nulls per column to assess feature completeness before training. |
| Drop Nulls | df.dropna(subset=['target']) | Drop records only when the primary target label or primary key is missing. |
| Impute Median | df['col'].fillna(df['col'].median()) | Impute numerical values with median to protect against outliers. |
| Deduplicate | df.drop_duplicates(subset=['id']) | Prevent training data leakage where duplicate queries bias validation scores. |
| String Strip | df['col'].str.strip().str.lower() | Eliminate trailing whitespaces and capitalization mismatches. |
| Parse Dates | pd.to_datetime(df['ts']) | Convert string dates into temporal microsecond datetimes for time-windowing. |
| id | model (raw string) | score | latency_ms | created_at |
|---|---|---|---|---|
| 201 | " GPT-4O " | 0.95 | 180 ms | 2026-03-01 |
| 202 | "claude-3.5" | NaN (Missing) | 220 ms | 2026-03-02 |
| 203 | "gemini-1.5" | 0.88 | 290 ms | 2026-03-03 |
| 201 | " GPT-4O " | 0.95 | 180 ms | 2026-03-01 |
| 204 | "llama-3.1 " | 0.81 | 95 ms | 2026-03-04 |
5. GroupBy, Aggregation & Split-Apply-Combine
When analyzing model behavior across categories (e.g. testing model accuracy across Coding, Reasoning, and Translation tasks), Pandas implements the Split-Apply-Combine paradigm:
summary = df.groupby("model").agg(
mean_score=("score", "mean"),
median_latency=("latency_ms", "median"),
total_runs=("task", "count")
).reset_index() # Restores 'model' as standard column rather than Index
| task (Cohort) | avg_score (Mean) | mean_latency | sample_count |
|---|---|---|---|
| Coding | 0.88 | 139.5 ms | 2 runs |
| Summarization | 0.91 | 215.0 ms | 1 runs |
| Reasoning | 0.88 | 310.0 ms | 1 runs |
| Translation | 0.89 | 142.0 ms | 1 runs |
6. Combining Data & Reshaping: Merging & Pivoting
AI datasets rarely come from a single file. You will frequently join customer metadata with transaction logs, or model evaluation scores with prompt token logs using pd.merge():
merged_df = pd.merge(customers, orders, on="cust_id", how="left")
# Pivot: reshape long records into wide comparison matrix
wide_matrix = df.pivot_table(
index="model", columns="task", values="score", aggfunc="mean"
)
| cust_id (Join Key) | Customer Name | order_id | Amount |
|---|---|---|---|
| C10 | Acme Corp | O-901 | $4,200 |
| C10 | Acme Corp | O-902 | $1,850 |
| C20 | Nexus AI | O-903 | $6,500 |
7. Text, Datetimes & Practical Feature Preparation
Before numbers enter neural networks or tree-based models, strings and dates must be converted into numerical representations:
df["timestamp"] = pd.to_datetime(df["timestamp"])
df["hour"] = df["timestamp"].dt.hour
# Binning continuous variables into categorical tiers
df["latency_tier"] = pd.cut(
df["latency_ms"], bins=[0, 100, 250, 1000], labels=["fast", "medium", "slow"]
)
# Export final clean features to NumPy float32 tensor
X_tensor = df[["tokens", "hour"]].to_numpy(dtype="float32")
8. Practical Pandas Data Analysis Workspace
Write and execute real Pandas code against simulated data in this interactive sandbox:
9. AI Mini Project: End-to-End AI Dataset Preparation Pipeline
In this mini project, you will clean raw LLM evaluation benchmark logs (model_evaluations.csv) containing dirty text, duplicate runs, and missing scores into an aggregated production report:
10. Production Debugging & Common Anti-Patterns
Mastering these four frequent bugs will save dozens of hours debugging data pipelines:
Symptom: Filtering rows raises: TypeError: cannot compare a dtyped [float64] array with a scalar of type [bool] or wrong rows are returned.
subset = df[df["score"] > 0.8 & df["latency"] < 200] # Evaluates (0.8 & df['latency']) first!
Symptom: Sorting a date column puts "02/15/2025" after "01/30/2026", breaking time-series windowing.
df = df.sort_values(by="date") # Sorts alphabetically by character, not time!
11. What You Should Know Now: Competency Checklist
- ✓Series vs DataFrame: Series is a 1D labeled column; DataFrame is a 2D table of multiple aligned Series sharing an Index.
- ✓Inspection Protocol: Always run
df.shape,df.dtypes,df.head(), anddf.info()before modifying data. - ✓Indexing Accuracy: Use
.loc[]for label-based access and.iloc[]for 0-based integer position offsets. - ✓Boolean Filtering: Combine conditions with
&,|, and~, wrapping each condition in parentheses. - ✓Context-Aware Cleaning: Impute numericals with median, handle categorical missingness deliberately, and avoid global
fillna(0). - ✓Split-Apply-Combine: Aggregate cohort statistics using
df.groupby('cohort').agg()andreset_index(). - ✓Relational Joins: Combine tables with
pd.merge()while monitoring one-to-many row count changes. - ✓Tensor Extraction: Extract model-ready contiguous arrays using
df[['col1', 'col2']].to_numpy(dtype=np.float32).