# Python Bio Data Wrangling

> Clean/reshape bio pandas tables — impute NaNs, dedupe replicates, coerce clinical strings to numeric/categorical, melt/pivot wide-long, regex-parse GTF attrs. Use for cleaning expr/clinical dataframes or reshaping.

- Skill: `pavel-kravchenko/python-bio-data-wrangling` (Agent Skill)
- Install (CLI): `npx skillmds@latest add pavel-kravchenko/python-bio-data-wrangling`
- Raw SKILL.md: https://api.skillmd.com/api/skills/pavel-kravchenko/python-bio-data-wrangling/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-data-wrangling

---


# Python Bio Data Wrangling

## When to Use

- Cleaning a messy gene expression matrix: missing values from failed library preps, duplicate probes/technical replicates.
- Coercing clinical/annotation tables loaded as strings (`'unknown'`, `'N/A'`) into numeric or ordered categorical dtypes.
- Reshaping expression data between wide (one row per gene) and long (one row per gene×sample observation) for seaborn/statsmodels vs. NumPy matrix ops.
- Parsing GTF/GFF-style semicolon attribute strings or cytogenetic bands (`17p13.1`) with regex into structured columns.
- Building a row-wise or group-wise transformation pipeline (z-scores, fold-change, QC flags) with `apply`/`transform`/`pipe`.

## Version Compatibility

pandas ≥2.0 (copy-on-write semantics differ pre-2.0 — chained assignment warnings became stricter), NumPy ≥1.24, Python ≥3.10. Examples assume default (non copy-on-write-forced) settings; behavior is the same either way for the `.loc` patterns shown here.

## Prerequisites

`pip install pandas numpy`. Assumes basic pandas familiarity (DataFrame/Series, boolean masks). No other skill dependency required.

## Complicated Moments

**`fillna` is not in-place by default**: `df['col'].fillna(0)` returns a new Series. Use `df['col'] = df['col'].fillna(0)` or `df.fillna({'col': 0}, inplace=True)`.

**`melt` vs `stack`**: `melt` converts specific value columns to rows (wide → long), keeping id columns. `stack` pivots the innermost column level into the row index. For expression data reshaping, `melt` is usually what you want.

**`groupby` and the index**: `groupby('gene_id').agg(...)` moves `gene_id` into the index by default. Use `as_index=False` to keep it as a regular column.

**String ops on NaN columns**: `df['col'].str.upper()` returns `NaN` for missing values silently. Always check for unexpected NaNs after parsing annotation columns.

## Missing Values

```python
# Strategy selection for expression data:
# Drop:           missingness is small and random (failed library preps)
# Fill with mean: need complete matrix for PCA/clustering, low missingness
# Fill with 0:    count data where NaN = "no reads detected"

sample_cols = ['s1', 's2', 's3']

# Column-mean imputation (sample-wise)
df[sample_cols] = df[sample_cols].fillna(df[sample_cols].mean())

# Row-mean imputation (gene-wise)
row_means = df[sample_cols].mean(axis=1)
for col in sample_cols:
    df[col] = df[col].fillna(row_means)
```

## Duplicates

```python
# Keep first occurrence
deduped = df.drop_duplicates(subset='gene_id', keep='first')

# Average technical replicates (most correct for expression data)
averaged = df.groupby('gene_id', as_index=False).agg({
    's1': 'mean', 's2': 'mean', 's3': 'mean',
    'gene_type': 'first',   # keep first annotation
})
```

## Type Conversion

```python
# Clinical/annotation tables often load everything as str
clinical['age'] = pd.to_numeric(clinical['age'], errors='coerce')      # 'unknown' → NaN
clinical['mut_count'] = pd.to_numeric(clinical['mut_count'], errors='coerce')
clinical['alive'] = clinical['alive'].map({'True': True, 'False': False})

# Ordered categorical — enables comparison operators
stage_order = ['I', 'II', 'III', 'IV']
clinical['tumor_stage'] = pd.Categorical(
    clinical['tumor_stage'], categories=stage_order, ordered=True
)
clinical[clinical['tumor_stage'] >= 'III']   # works with ordered categorical
```

## Wide ↔ Long Reshaping

```text
Wide (one row per gene):  gene | s1 | s2 | s3
Long (one row per obs):   gene | sample | expression
```

Wide → long for seaborn/ggplot/statistical models. Wide for NumPy matrix ops and heatmaps.

```python
# Wide → Long
long = df.melt(
    id_vars=['gene', 'gene_type'],
    value_vars=['ctrl_1', 'ctrl_2', 'treat_1', 'treat_2'],
    var_name='sample',
    value_name='expression',
)
long['condition'] = long['sample'].str.split('_').str[0]   # 'ctrl' / 'treat'

# Long → Wide
wide = long.pivot_table(
    index=['gene', 'gene_type'],
    columns='sample',
    values='expression',
).reset_index()
wide.columns.name = None   # flatten MultiIndex column names

# stack/unstack for MultiIndex DataFrames
stacked = df.set_index(['gene', 'gene_type'])[sample_cols].stack()
stacked.name = 'expression'
unstacked = stacked.unstack()
```

## String Parsing (GTF-style attributes)

```python
# Parse GTF attribute column: gene_id "ENSG..."; gene_name "TP53"; ...
df['ensembl_id'] = df['raw_id'].str.extract(r'gene_id "(ENSG\d+)"')
df['gene_name']  = df['raw_id'].str.extract(r'gene_name "(\w+)"')
df['biotype']    = df['raw_id'].str.extract(r'gene_biotype "([^"]+)"')

# Cytogenetic band parsing: '17p13.1' → chrom='17', arm='p'
chromosomes = bands.str.extract(r'(\d+)[pq]')[0]
arms        = bands.str.extract(r'\d+([pq])')[0]
```

## Apply / Transform / Pipe

**Goal:** classify, normalize, and chain QC steps over an expression DataFrame without writing manual loops.
**Approach:** use `apply` for custom row/column logic, `transform` when the output must stay the same shape as the input (group-wise ops), and `pipe` to chain functions into a readable, reusable pipeline.

| Method | Returns | Use case |
|--------|---------|----------|
| `apply(fn, axis=1)` | Anything | Row-wise custom logic |
| `apply(fn, axis=0)` | Anything | Column-wise (e.g. z-score) |
| `transform(fn)` | Same shape as input | Group-wise normalization |
| `pipe(fn)` | DataFrame | Chaining processing steps |

```python
# Row-wise classification
def classify_expr(row):
    """Bucket a gene into high/medium/low based on its mean expression."""
    m = row[sample_cols].mean()
    return 'high' if m > 6 else 'medium' if m > 4 else 'low'

df['category'] = df.apply(classify_expr, axis=1)

# Column-wise z-score normalization
def zscore(col):
    """Standardize a numeric column to mean 0, std 1."""
    return (col - col.mean()) / col.std()

normalized = df[sample_cols].apply(zscore)

# Group-wise normalization with transform (keeps original shape)
df['expr_zscore'] = df.groupby('condition')['expression'].transform(zscore)
```

```python
# Chained QC/fold-change pipeline with .pipe() — each step is testable in isolation
def remove_low_expression(df, threshold=3.0):
    """Drop genes where every ctrl/treat sample is below threshold."""
    cols = [c for c in df.columns if c.startswith(('ctrl', 'treat'))]
    mask = df[cols].max(axis=1) >= threshold
    return df[mask]

def add_fold_change(df):
    """Add log2 fold change (treatment mean / control mean)."""
    ctrl_mean = df[['ctrl_1', 'ctrl_2']].mean(axis=1)
    treat_mean = df[['treat_1', 'treat_2']].mean(axis=1)
    df = df.copy()
    df['log2FC'] = np.log2(treat_mean / ctrl_mean)
    return df

def flag_significant(df, fc_threshold=0.5):
    """Flag genes with absolute log2 fold change above threshold."""
    df = df.copy()
    df['significant'] = df['log2FC'].abs() > fc_threshold
    return df

result = (
    expr_data
    .pipe(remove_low_expression, threshold=2.5)
    .pipe(add_fold_change)
    .pipe(flag_significant, fc_threshold=0.5)
)
```

## Pitfalls

- **Chain indexing**: `df[mask]['col'] = value` silently sets a copy. Always use `df.loc[mask, 'col'] = value`.
- **`groupby` moves key into index**: use `as_index=False` to keep it as a column, or call `.reset_index()` after.
- **`pd.to_numeric(errors='coerce')`**: silently converts unparseable strings to NaN; always inspect `.isna().sum()` afterward.
- **`melt` column order**: `value_vars` defaults to all non-id columns if omitted; be explicit to avoid including unexpected columns.
- **Off-by-one in coordinates**: BED is 0-based half-open; VCF/GFF are 1-based. Converting between them without adjusting causes systematic errors.
- **String ops on mixed-case gene IDs**: normalize with `.str.upper()` before any merge or filter to avoid missed matches.

## See Also

- `bio-expression-matrix-counts-ingest` — loading raw count matrices before wrangling.
- `bio-expression-matrix-gene-id-mapping` — mapping/normalizing gene identifiers across annotation sources.
- `bio-expression-matrix-metadata-joins` — joining expression data with sample/clinical metadata.
- `bio-genome-intervals-gtf-gff-handling` — full GTF/GFF parsing beyond single attribute-column regex.