Load Data with Pandas and NumPy

You've built the model architecture, designed your prompt chains, and maybe even wired up a vector store. But when you try to actually train a model, run an evaluation harness, or feed a batch of documents through an LLM pipeline, you hit the brick wall that stops every AI engineer cold: your data is trapped in files, APIs, and messy formats that Python can't understand directly. Nothing happens until you load that data into memory as the right data structures. In this lesson, you'll master the two tools that make this almost trivial — pandas for tabular, labeled data, and NumPy for high-performance numerical arrays. You'll stop fighting file formats and start building the data-loading foundation every applied AI system depends on.

The Problem This Lesson Solves

Applied AI projects are data hungry. Whether you're fine-tuning a small language model, computing cosine similarity for a retrieval system, or running a batch evaluation of LLM outputs, the very first line of production code — often before any model API call — is about getting raw data into Python objects you can compute with. The pain is unmistakable:

• You have a dozen CSV files exported from a database, but each has a different delimiter, quotation style, or missing-value marker.
• Your JSON API responses contain nested structures you need to flatten into a tabular form for analysis.
• You need to perform vectorized math, like scaling embeddings, but Python lists are too slow.
• Excel files, SQL tables, Parquet files, JSON blobs — each format demands its own loading ceremony.

Without a reliable, fast, and well-tested way to load data, you either write brittle, hand-rolled parsers (which break the moment a date format changes) or you spend hours copy-pasting data into list literals. Neither approach scales to real AI workloads. This lesson gives you the pandas + NumPy loading pattern that data scientists and ML engineers rely on every day: load a table, inspect it, clean it, and immediately convert it into arrays for computation.

Core Concept / Mental Model

Think of pandas and NumPy as a two-layer warehouse for your data:

• NumPy is the physical shelf — a contiguous block of memory holding numbers in a homogeneous array. It's fast because operations broadcast across the whole block without Python-level loops. Every AI framework (PyTorch, TensorFlow, scikit-learn) can ingest NumPy arrays natively.
• Pandas is the catalog layer on top. A DataFrame is a labeled, two-dimensional table where each column is a Series (which is itself built on a NumPy array). The labels (column names, row indices) let you filter, aggregate, and transform data with expressive, human-readable syntax.

When you load data with pandas, you're essentially saying: "Take this raw file and turn it into a labeled table I can query with natural operations like df[df['score'] > 0.5]." When you then call .values or .to_numpy(), you're pulling that table off the catalog and handing a raw array to a numerical algorithm. This layered mental model explains why you'll often write import pandas as pd and import numpy as np together — pandas for the I/O and cleaning, NumPy for the heavy math.

Key definitions to internalize:

• DataFrame: 2D labeled data structure (rows = records, columns = features).
• Series: 1D labeled array — a single column.
• ndarray: NumPy's N-dimensional array, the low-level container that pandas uses under the hood.
• Loading: the process of reading external data (CSV, JSON, SQL, etc.) into these in-memory structures.

How It Works Step by Step

Loading data with pandas and NumPy is a repeatable pipeline you'll follow in almost every project. Here’s the high-level flow:

1. Install the libraries (if not already in your environment): pip install pandas numpy.
2. Import them with the standard aliases: import pandas as pd and import numpy as np.
3. Read raw data using a pandas read function that matches your format: pd.read_csv(), pd.read_json(), pd.read_excel(), pd.read_sql(), and so on.
4. Inspect the DataFrame — print the shape, columns, data types, and first few rows using .shape, .columns, .dtypes, and .head().
5. Clean and prepare — handle missing values, rename columns, convert data types, and possibly flatten nested JSON.
6. Convert to NumPy arrays for numerical computation using .to_numpy() or .values.

Why this order?

Loading is not just reading bytes — it's about creating a correct, usable data structure. If you skip the inspection and cleaning steps, you'll feed garbage into your AI models. The step-by-step process is therefore: read → inspect → fix → convert.

Hands-On Walkthrough

Let's put theory into practice with a real-world scenario: you're building an evaluation harness to score LLM responses. You have a CSV file (scores.csv) with model outputs and human ratings. You also have a JSON file with metadata. We'll load both, combine them, and convert the final table into a NumPy array for a regression model.

Example 1: Loading a CSV and basic inspection

import pandas as pd
import numpy as np

# Load a CSV file (assume it exists in the current directory)
df = pd.read_csv('scores.csv')

# Inspect the DataFrame
print(df.shape)        # (5, 3)
print(df.columns)      # Index(['id', 'response', 'score'], dtype='object')
print(df.dtypes)       # id: int64, response: object, score: float64
print(df.head(2))      # first two rows

Expected output (shape may vary):

(5, 3)
Index(['id', 'response', 'score'], dtype='object')
id         int64
response   object
score      float64
dtype: object
   id  response  score
0   1   hello    0.85
1   2   hi       0.72

Example 2: Flattening nested JSON and merging

Many AI APIs return nested JSON. Let's load a JSON file with per-response metadata and join it to our DataFrame.

import json
import pandas as pd

# Simulated JSON file content (you'd actually read it from disk)
with open('metadata.json', 'r') as f:
    records = json.load(f)

# Normalize semi-structured data into a flat table
meta_df = pd.json_normalize(records)
print(meta_df)

# Merge two DataFrames on a common key
combined = df.merge(meta_df, on='id', how='left')
print(combined)

Expected output (first few rows):

   id  latency_ms  model
0   1         120  gpt-4
1   2          95  llama-3
2   3         210  gpt-4
3   4          88  llama-3
4   5         150  gpt-4

   id  response  score  latency_ms  model
0   1     hello  0.85         120  gpt-4
1   2        hi  0.72          95  llama-3
...

Example 3: Handling missing values and converting to NumPy

Real data always has gaps. Let's clean and then convert to arrays.

# Fill missing numeric values with the column mean
combined['score'] = combined['score'].fillna(combined['score'].mean())

# Convert object columns to categorical (good for ML)
combined['model'] = combined['model'].astype('category')

# Select only numeric columns and convert to a NumPy array
numeric_cols = ['score', 'latency_ms']
X = combined[numeric_cols].to_numpy()

print(X)
print(X.shape)  # (5, 2)
print(X.dtype)  # float64

Expected output:

[[  0.85 120.  ]
 [  0.72  95.  ]
 [  0.9  210.  ]
 [  0.65  88.  ]
 [  0.8  150.  ]]
(5, 2)
float64

Example 4: Loading data with NumPy directly (when pandas is overkill)

Sometimes you just need raw numbers, like loading an embedding matrix saved as .npy.

import numpy as np

# Save and load a binary array
embedding = np.random.rand(8, 768)
np.save('embedding.npy', embedding)
loaded = np.load('embedding.npy')

print(loaded.shape)  # (8, 768)
print(type(loaded))  # <class 'numpy.ndarray'>

Expected output:

(8, 768)
<class 'numpy.ndarray'>

Compare Options / When to Choose What

Choosing the right loading approach depends on the task. Here's a quick reference:

When to choose what:

• Use pandas when you need labels, mixed types, or complex cleaning — which is most AI engineering preprocessing.
• Use NumPy directly when you have pure numeric, already-prepared arrays (like embeddings or standardized features).
• For very large datasets (millions of rows), consider dtype specification in pandas to save memory, or switch to chunked loading with pd.read_csv(..., chunksize=).

Pro tip: Always specify dtype for columns with known types (e.g., dtype={'id': 'int32'}) to reduce memory usage, especially in big datasets.

Troubleshooting & Edge Cases

1. FileNotFoundError — The file path is wrong or relative to a different directory. Fix: use an absolute path or check the working directory with os.getcwd().

2. UnicodeDecodeError when reading CSV — The file has a special encoding (e.g., UTF-16 or Latin-1). Fix: pd.read_csv('file.csv', encoding='utf-8') or try encoding='latin1'.

3. ParserError due to inconsistent rows — A line has more columns than the header. Fix: pass on_bad_lines='skip' to skip, or investigate the source.

4. Missing values appearing as NaN — This is normal, but if you need to distinguish missing from 'NA' string, use keep_default_na=False.

5. .to_numpy() gives an object array — This happens when columns have mixed types. Fix: convert each column to numeric with pd.to_numeric() before converting.

6. Numeric data loaded as strings — Often due to pipes or extra characters. Fix: use pd.to_numeric(df['col'], errors='coerce').

What You Learned & What's Next

You've now built the critical first step of any robust AI workflow: loading data with pandas and NumPy. You can read CSV and JSON files, flatten nested structures, merge tables, clean missing values, and convert your labeled DataFrame into a fast NumPy array suitable for mathematical operations. This skill is foundational because every model API call, every retrieval query, and every evaluation metric depends on data that’s correctly loaded and shaped upfront.

Next in the Applied AI engineering path, you'll learn how to transform and feature-engineer these loaded datasets — things like scaling, encoding categorical variables, and splitting into train/test sets. With a solid loading foundation, you're now ready to turn raw files into the exact feature matrix your model needs.