Cleaning Experiment Data with Pandas

After Completing This Topic

You'll be able to read CSVs into Pandas DataFrames, apply conditional filtering, compute group statistics, and merge two tables.

What Is Pandas?

In the previous topic, you learned to read files with the csv module and process them with loops. That's fine for 10 samples, but with 5,000 samples and 50 columns, loops become unwieldy.

Pandas is a Python library for handling tabular data. You load data into a DataFrame structure (similar to an Excel sheet) and perform filtering, sorting, and aggregation in a single line.

Lab notebook analogy — if the csv module reads your notebook line by line, Pandas spreads the entire table out so you can instantly pick any column or row.

Creating a DataFrame

import pandas as pd

df = pd.read_csv("expression_data.csv")

print(df)
print(f"\nRows: {len(df)}, Columns: {len(df.columns)}")
print(f"Columns: {list(df.columns)}")

One line of pd.read_csv() turns a CSV file into a DataFrame. No csv.DictReader or loops needed — the entire dataset is loaded.

You can also create data directly:

import pandas as pd

data = {
    "sample_id": ["S001", "S002", "S003", "S004", "S005"],
    "gene": ["EGFR", "TP53", "EGFR", "BRCA1", "TP53"],
    "expression": [12.5, 3.2, 18.7, 7.1, 2.8],
    "status": ["high", "low", "high", "medium", "low"],
}

df = pd.DataFrame(data)
print(df)

Output:

sample_id   gene  expression  status
0      S001   EGFR        12.5    high
1      S002   TP53         3.2     low
2      S003   EGFR        18.7    high
3      S004  BRCA1         7.1  medium
4      S005   TP53         2.8     low

Exploring the Data

When you first receive a DataFrame, quickly get the big picture:

df.head()          # First 5 rows
df.tail(3)         # Last 3 rows
df.shape           # (row count, column count)
df.dtypes          # Data type of each column
df.describe()      # Basic stats for numeric columns (mean, std, min, max)

df.describe() is useful for quickly answering "roughly what range is this data in?" when you first receive results.

Selecting Columns and Rows

# Select one column — returns a Series
genes = df["gene"]

# Select multiple columns — returns a DataFrame
subset = df[["sample_id", "expression"]]

# Select rows — by index
first_row = df.iloc[0]        # First row
first_three = df.iloc[0:3]    # Rows 0-2

Conditional Filtering

This is Pandas' core feature. What took for + if with the csv module becomes one line:

# EGFR gene only
egfr = df[df["gene"] == "EGFR"]
print(egfr)

# Samples with expression >= 5
high_expr = df[df["expression"] >= 5.0]
print(high_expr)

# Compound condition: EGFR AND expression >= 15
egfr_high = df[(df["gene"] == "EGFR") & (df["expression"] >= 15.0)]
print(egfr_high)

assert len(egfr) == 2
assert len(egfr_high) == 1

& is AND, | is OR. Each condition must be wrapped in parentheses.

Sorting

# Sort by expression, descending
sorted_df = df.sort_values("expression", ascending=False)
print(sorted_df)

Group Aggregation: groupby

"What's the mean expression per gene?" — what you'd do with a pivot table in Excel, you do with groupby:

gene_stats = df.groupby("gene")["expression"].agg(["mean", "std", "count"])
print(gene_stats)

Output:

mean       std  count
gene
BRCA1   7.10       NaN      1
EGFR   15.60  4.384062      2
TP53    3.00  0.282843      2

groupby("gene") — group by gene, ["expression"] — for the expression column, agg(["mean", "std", "count"]) — calculate mean, standard deviation, and count all at once.

Adding New Columns

# Add a log2-transformed expression column
import numpy as np

df["log2_expression"] = np.log2(df["expression"])
print(df[["sample_id", "expression", "log2_expression"]])

Merging Two Tables: merge

When experiment data and sample info are in separate files:

import pandas as pd

# Sample metadata
metadata = pd.DataFrame({
    "sample_id": ["S001", "S002", "S003", "S004", "S005"],
    "tissue": ["lung", "breast", "lung", "breast", "colon"],
    "age": [45, 62, 38, 55, 71],
})

# Merge with expression data
merged = pd.merge(df, metadata, on="sample_id")
print(merged)

on="sample_id" — joins rows with matching values in both tables. Same concept as SQL JOIN. What you learned in the database-basics topic applies here too.

Saving Results

# Save as CSV
egfr.to_csv("egfr_samples.csv", index=False)

# Save as TSV
egfr.to_csv("egfr_samples.tsv", sep="\t", index=False)

index=False — don't include row numbers (0, 1, 2...) in the file.

Try It Yourself (Faded Example)

Fill in the blanks to perform conditional filtering and group aggregation on a DataFrame.

import pandas as pd
df = pd.read_csv("samples.csv")
# Filter samples with OD >= 1.0
passed = df[df["od"]  1.0]
# Mean OD by status
stats = df.("status")["od"].mean()
print(stats)

Common Errors & Solutions

Q: KeyError: 'gene'

Check if the column name is exact. Print actual names with df.columns. There might be hidden spaces (" gene" vs "gene").

Q: Filtering returns an empty DataFrame

The condition might be too strict, or data types don't match. Check the type with df["expression"].dtype. Comparing strings to numbers may yield no results.

Q: SettingWithCopyWarning appears

This warning occurs when assigning values to a filtered result. Create an explicit copy with df_filtered = df[condition].copy(), then modify — the warning goes away.

Q: merge increased the number of rows

If the on column has duplicate values, all combinations are created, expanding rows. Before merging, check duplicates in both tables with df["sample_id"].duplicated().sum().