Pandas for Bioinformatics: Reading, Filtering, and Summarizing Omics Tables

Quick answer: Pandas is Python’s table tool — a DataFrame is a spreadsheet with code attached, and it is the standard way bioinformaticians wrangle the endless tab-separated files the field produces: count matrices, annotation tables, sample metadata, BLAST hits, variant summaries. The daily workflow is small and powerful: read_csv with the right separators, boolean filtering, groupby for summaries, merge to join tables on gene IDs, and melt/pivot to reshape between the wide and long formats that plotting libraries prefer.

Companion to the Biopython tutorial and Python for bioinformatics guides on this blog — Biopython handles sequences, pandas handles the tables that describe them.

Getting data in without being misled

Most bioinformatics text files are not plain CSV, and the load call is where correctness starts:

import pandas as pd

counts  = pd.read_csv('gene_counts.tsv', sep='\t', comment='#',
                      index_col=0)
meta    = pd.read_csv('samples.tsv', sep='\t', dtype={'sample': str})
blast   = pd.read_csv('hits.txt', sep='\t', header=None,
                      names=['qseqid','sseqid','pident','length',
                             'mismatch','evalue','bitscore'])

Details that bite later if skipped now:

• Separator. Genomics lives on tabs (sep='\t'), not commas; comment lines starting with # (VCF headers, GFF) must be skipped with comment='#' or they land in the first row.
• dtypes. An ID column full of digits silently becomes integers — then leading zeros vanish and every future join fails. Force dtype=str on identifiers.
• Index vs column. index_col=0 for count matrices makes gene IDs the row labels, which is what merging and plotting expect.
• Missing values. pandas reads empty fields as NaN, not zero. df.isna().sum() immediately after loading tells you whether your table has holes before they turn into zeros in an average.

A first orientation habit: df.shape, df.head(), df.dtypes, df.describe() — four calls that catch most bad loads.

Filtering: boolean masks, not spreadsheet squinting

Selection is a boolean expression that returns rows, and masks compose:

expressed = counts[(counts > 10).sum(axis=1) >= 3]      # count >10 in ≥3 samples
top       = counts.loc[:, counts.mean().sort_values(ascending=False).index[:5]]

hits = blast[(blast['evalue'] < 1e-5) & (blast['pident'] >= 90)]
# query has at least one strong hit
good_queries = hits.groupby('qseqid').size().loc[lambda s: s >= 1]

query() reads even better for simple cases (df.query('length >= 100 and evalue < 1e-10')), and .loc[rows, cols] beats chained brackets for anything non-trivial — chained assignment (df[a][b] = x) is the classic pandas bug that warns or silently fails.

Groupby: the summarize verb

Split–apply–combine in one line — the same idea as SQL GROUP BY:

meta.merge(counts.T, left_index=True, right_index=True) \
    .groupby('condition').mean(numeric_only=True)

Useful companions: .agg({'gene_a': 'max', 'gene_b': 'mean'}) for mixed summaries, .transform('rank') to add a per-group column without collapsing rows, and .value_counts() for quick category tallies on annotation columns.

Merge: where two tables become one analysis

Joining is where IDs either match or quietly don’t:

annot = pd.read_csv('gene_annotation.tsv', sep='\t')
df = counts.join(annot.set_index('gene_id'), how='left')   # keep all genes
missing = df['biotype'].isna().sum()                       # unmatched IDs

Three disciplines worth keeping:

• Check the result shape and the unmatched count. merge(how='left') plus df.shape[0] unchanged plus a report of unmatched keys is a two-line sanity check that prevents the silent loss of half your genes.
• Normalize IDs first. Versions and prefixes differ between sources (ENSG00000141510.12 vs ENSG00000141510, gene:AT1G01010 vs AT1G01010). Strip with .str.split('.').str[0] or .str.replace('gene:', '', regex=False) before joining.
• Prefer join/merge on explicit keys over positional pairing. Row order in a count matrix is not guaranteed to match a metadata file, and positional merges are invisible errors.

Reshaping: wide for humans, long for plots

Count matrices are wide (one column per sample); seaborn, plotnine, and R’s tidyverse want long (one row per observation):

long = counts.reset_index().melt(id_vars='gene_id',
                                 var_name='sample', value_name='count')
long = long.merge(meta, on='sample')
long['logcount'] = np.log2(long['count'] + 1)

The reverse, pivot(index=..., columns=..., values=...), rebuilds a matrix for clustering or correlation. Learning the melt–pivot pair removes most of the friction between “the file I have” and “the plot the library wants.”

The habits that keep analysis honest

• Copy before you modify. Chained filters assigned back onto the original (df = df[...] repeatedly) make the data unrecoverable; keep the raw load and derive df2 = df.copy().
• Sort deterministically (df.sort_values('gene_id')) before comparing two tables row-by-row — never compare two DataFrames positionally without sorting first.
• Export what you analyzed: df.to_csv('filtered_genes.tsv', sep='\t', index=False) alongside the script, so the figure can be regenerated from the exact table.
• Mind memory at scale. Tens of millions of rows want dtype discipline, category for repeated strings, and chunksize= in read_csv; a count matrix of 60k genes is trivial, a variant matrix of 500 million rows is not.
• Use .copy() after any slice you intend to edit, or pandas’ SettingWithCopyWarning is telling you the write may not stick.

Key takeaways

• Load with intent: sep, comment, dtype, and index_col decided up front prevent most downstream corruption.
• Boolean masks and .loc are the filtering idiom; chained assignment is the bug to avoid.
• groupby gives split–apply–combine summaries; merge/join joins tables — always verify row counts and unmatched IDs.
• Normalize identifier formats (versions, prefixes) before any join; positional alignment is a silent trap.
• melt (wide to long) and pivot (long to wide) bridge files and plotting libraries.
• Keep the raw frame, write out the exact filtered table you analyzed, and reach for dtypes/chunking when the file outgrows memory.

Challenge

Take a public count matrix and its sample sheet and, in one script: load both with correct dtypes, filter to genes with meaningful counts in at least half the samples, join on gene IDs while reporting how many IDs failed to match, compute per-condition means with groupby, and export both the filtered matrix and the summary as TSV. The mismatch report is the step most people skip — and the one that saves the analysis when a gene ID silently changes format between releases.

Want guided practice wrangling real omics tables? Ampersand Academy offers one-to-one mentoring in bioinformatics — part of Mahadhi, a development and digital marketing company.

FAQ