# Python Bio Pandas

> Manipulate bio DataFrames with pandas — loc/iloc selection, boolean/query filtering, groupby agg vs transform, left-join annotation merges, CSV/TSV expression-matrix I/O, wide/long melt. Use when indexing/filtering a gene or sample table, merging expression data with an annotation or clinical table, computing group-wise statistics (per-condition mean/z-score), or reading a counts.csv/counts.tsv into a DataFrame.

- Skill: `pavel-kravchenko/python-bio-pandas` (Agent Skill)
- Install (CLI): `npx skillmds@latest add pavel-kravchenko/python-bio-pandas`
- Raw SKILL.md: https://api.skillmd.com/api/skills/pavel-kravchenko/python-bio-pandas/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: pavel-kravchenko (https://skillmd.com/u/pavel-kravchenko)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/pavel-kravchenko/python-bio-pandas

---


# Pandas for Bioinformatics

## When to Use

- Selecting or filtering rows/columns of a gene table, count matrix, or clinical sheet by label (`.loc`) or position (`.iloc`).
- Merging expression data with a gene annotation or sample metadata table and needing to catch genes that don't match.
- Computing per-group statistics — mean expression per condition, per-gene z-score within a group — with `groupby`.
- Loading a count matrix or sample sheet from CSV/TSV (`counts.csv`, GEO-style series matrix, BED/GTF attribute exports).
- Reshaping a genes × samples wide table into long format for plotting or statistical modeling.

## Version Compatibility

pandas ≥2.0, NumPy ≥1.24, Python ≥3.10. pandas ≥2.0 defaults to stricter chained-assignment warnings (copy-on-write is opt-in via `pd.options.mode.copy_on_write = True` pre-3.0, default from pandas 3.0) — the `.loc[mask, col] = val` pattern below is safe under both.

## Prerequisites

`pip install pandas numpy`. Assumes basic Python (dict/list comprehension) and familiarity with what a gene expression count matrix looks like (genes × samples). For array-level math (RPKM/CPM, PWMs, broadcasting) see `python-bio-numpy`; for cleaning/reshaping beyond what's here see `python-bio-data-wrangling`.

## Complicated Moments

**`loc` vs `iloc` vs `[]`**: `df['col']` selects a column. `df.loc[row_label, col_label]` selects by label. `df.iloc[row_int, col_int]` selects by integer position. After filtering, integer positions no longer match original labels — always know which accessor you need.

**Chain indexing creates copies unpredictably**: `df[df['gc'] > 0.5]['length'] = 100` may silently fail to modify `df`. Always use `df.loc[mask, 'length'] = 100`.

**`groupby` + `transform` vs `agg`**: `agg` reduces each group to one row. `transform` returns a same-shape Series aligned to the original index, each row filled with its group's statistic — this is what you want for group-wise normalization added back as a new column.

**Left join for annotation merges**: `merge(..., how='inner')` silently drops genes missing from the annotation table. Default to `how='left'` and inspect the resulting NaNs.

## Selecting and Filtering

**Goal:** pull rows/columns out of a gene table by label, position, or condition, without falling into the chained-assignment trap.
**Approach:** use `.loc` for label-based access and safe assignment, `.iloc` for positional access, boolean masks or `.query()` for filtering.

```python
import pandas as pd

genes_df = pd.DataFrame({
    'gene': ['BRCA1', 'TP53', 'EGFR', 'MYC', 'KRAS'],
    'chromosome': ['17', '17', '7', '8', '12'],
    'length_bp': [7088, 2512, 5616, 2357, 5764],
    'gc_content': [0.423, 0.512, 0.487, 0.551, 0.448],
})


def flag_long_gc_rich(df, length_thresh=5000, gc_thresh=0.45):
    """Return a copy of df with a boolean 'long_gc_rich' column.

    Uses .loc for the assignment so it never triggers a
    SettingWithCopyWarning / silent no-op on a filtered copy.
    """
    df = df.copy()
    mask = (df['length_bp'] > length_thresh) & (df['gc_content'] > gc_thresh)
    df.loc[mask, 'long_gc_rich'] = True
    df['long_gc_rich'] = df['long_gc_rich'].fillna(False)
    return df


flagged = flag_long_gc_rich(genes_df)

# label vs position access
genes_df.loc[0, 'gene']            # by label -> 'BRCA1'
genes_df.iloc[0, 0]                # by integer position -> 'BRCA1'
genes_df.loc[1:3, ['gene', 'length_bp']]   # label slice is INCLUSIVE of end

# readable filtering with .query() (equivalent to the boolean-mask version)
chr17_gc_rich = genes_df.query('chromosome == "17" and gc_content > 0.45')
```

## Annotation Merges

**Goal:** attach gene/sample metadata to an expression table without silently dropping unmatched rows.
**Approach:** always start with `how='left'`, then count NaNs introduced by the merge before deciding whether to drop or investigate them.

```python
def merge_with_annotation(expr_df, annotation_df, on='gene_id'):
    """Left-join expression data with an annotation table and report misses.

    Left join keeps every row of expr_df; genes absent from annotation_df
    get NaN annotation columns instead of being silently dropped (as an
    inner join would do).
    """
    merged = expr_df.merge(annotation_df, on=on, how='left')
    n_missing = merged['gene_name'].isna().sum()
    if n_missing:
        print(f"Warning: {n_missing} genes missing annotation")
    return merged
```

## GroupBy: agg vs transform

**Goal:** compute per-condition summary statistics, and separately, per-condition normalized values that stay aligned to the original rows.
**Approach:** `agg` for one-row-per-group summaries, `transform` for same-shape group-wise normalization.

```python
import numpy as np

df = pd.DataFrame({
    'gene': [f'Gene_{i}' for i in range(6)],
    'condition': ['ctrl', 'ctrl', 'ctrl', 'treat', 'treat', 'treat'],
    'expression': [120.0, 45.0, 300.0, 340.0, 44.0, 310.0],
})

# agg: one row per group
summary = df.groupby('condition', as_index=False).agg(
    mean_expr=('expression', 'mean'),
    n=('expression', 'count'),
)

# transform: same shape as input, each row filled with its group's z-score
df['expr_zscore'] = df.groupby('condition')['expression'].transform(
    lambda x: (x - x.mean()) / x.std()
)
```

## Reading Expression Data

```python
# Count matrix: genes as row index, one column per sample
counts = pd.read_csv('counts.csv', index_col=0)

# TSV (common for BED/GTF attribute exports)
metadata = pd.read_csv('samples.tsv', sep='\t')

# Wide (genes x samples) -> long (one row per gene x sample observation)
long = counts.reset_index().melt(
    id_vars='gene_id', var_name='sample', value_name='count'
)
```

## Pitfalls

- **Chain indexing**: `df[mask]['col'] = val` sets a copy silently. Use `df.loc[mask, 'col'] = val`.
- **`groupby` index**: by default the grouping key becomes the index; use `as_index=False` to keep it as a column.
- **Integer index after filtering**: after `df = df[df['qc_pass']]`, `df.iloc[0]` is the first remaining row but `df.loc[0]` still refers to original label 0 (may raise `KeyError`). Call `df.reset_index(drop=True)` if you need position-based access afterward.
- **`.loc` slices are inclusive**: `df.loc[1:3]` includes row label 3, unlike `df.iloc[1:3]` or a Python list slice.
- **Inner-join data loss**: `merge(..., how='inner')` on an incomplete annotation table drops genes with no match instead of flagging them — default to `how='left'`.
- **`np.log2` on zero counts**: add a pseudocount (`+ 1`) before log-transforming raw read counts; pandas won't warn you about `-inf`.

## See Also

- `python-bio-numpy` — vectorized array math (RPKM/CPM, PWMs, broadcasting) underlying pandas columns.
- `python-bio-data-wrangling` — cleaning (NaN imputation, dedup, type coercion) and wide/long reshaping beyond basic melt.
- `bio-expression-matrix-counts-ingest` — loading raw count matrices from GEO/featureCounts/salmon output.
- `bio-expression-matrix-gene-id-mapping` — normalizing gene identifiers before merging annotation tables.