Canine Respiratory PCR
Positivity & Co-infection Analysis
A SQL-to-R analytics pipeline that extracts multi-target PCR panel results from a Microsoft SQL Server laboratory database, calculates per-antigen positivity rates, and quantifies co-infection frequency across a 6-month clinical window.
The Problem
Multi-target PCR data is hard to analyze correctly
Canine respiratory PCR panels test for several pathogens in a single submission. Raw data from the laboratory system contains repeated results per requisition, inconclusive readings that need separate handling, and text-based results that must be classified before any positivity calculation makes sense. A naive aggregation would double-count results and misrepresent co-infection rates.
Duplicate result versions
Each panel test can have multiple result records over time — reruns, corrections, later interpretations. Without version selection, positivity counts would be inflated.
Repeated panel runs
The same requisition can have the same panel run more than once. Only the most recent panel run per requisition should count toward the analysis.
Non-diagnostic test codes
Some codes returned by the panel are Ct values or helper tests, not diagnostic calls. These must be filtered out so only true positive / negative determinations enter the rate calculation.
Denominator ambiguity
Should “Inconclusive” count in the denominator? The pipeline explicitly excludes it, reporting positivity as Detected / (Detected + Not Detected) and inconclusive rate separately.
Solution Architecture
Two focused scripts, one clean output
The pipeline is deliberately small: one SQL script pulls a deduplicated result set from the laboratory database, and one R script transforms that CSV into positivity tables, a co-infection summary, and publication-ready visualizations. Each stage does one thing well.
Extract Layer
The SQL: two window functions do the heavy lifting
A single Common Table Expression joins six tables to build a wide result set. Two window functions run in parallel — one ranks panel runs per requisition, the other ranks result versions per panel test. The outer query keeps only rank 1 from each, guaranteeing exactly one row per requisition × panel × test.
-- Rank 1: most recent panel run per requisition DENSE_RANK() OVER ( PARTITION BY req.requisition_id, p.panel_id ORDER BY rp.run_date DESC, rp.req_panel_id DESC ) AS panel_run_rn, -- Rank 2: most recent result version per test ROW_NUMBER() OVER ( PARTITION BY rp.req_panel_id, rst.panel_test_id ORDER BY rst.created_date DESC, rst.result_id DESC ) AS result_version_rn -- Outer filter keeps only the newest row from each WHERE panel_run_rn = 1 AND result_version_rn = 1
DENSE_RANK for panel runs
Partitioned by requisition_id and panel_id, ordered by run_date descending. Ensures multiple runs of the same panel on the same requisition collapse to the most recent one.
ROW_NUMBER for versions
Partitioned by req_panel_id and panel_test_id, ordered by created_date descending. Keeps only the latest recorded value for each individual test within a panel run.
Soft-delete filters
Both rp.del_flag = 0 and rst.del_flag = 0 exclude records the source system marked as deleted — a common pattern in clinical databases where records are hidden rather than physically removed.
Test code filters
test_code LIKE '2500%' scopes to the target panel family. NOT LIKE '% ct' removes cycle threshold rows. An explicit code exclusion removes one known non-diagnostic entry.
Transform Layer
R pipeline: classify, aggregate, visualize
The R script reads the extracted CSV and executes a linear tidyverse pipeline. Every stage below corresponds to a discrete block in the source code, producing intermediate tibbles that are printed to the console and, in the case of the co-infection table, also saved as a standalone HTML file.
Read CSV
readr::read_csv loads the SQL export from data/raw with column types silenced for cleaner console output.
Antigen extraction
str_remove strips the trailing “ PCR” suffix from test_name to yield clean antigen labels.
Result categorization
case_when maps raw result text into detected / not_detected / inconclusive / other classes and derives boolean flags is_positive and is_dn.
Month grouping
lubridate::floor_date normalizes run_date to the first of each month for monthly aggregation.
Antigen positivity
group_by(antigen) with n_distinct(req_panel_id) counts unique panels per class, then computes Detected / (Detected + Not Detected).
Co-infection detection
Per req_panel_id, sums positive targets. Panels with 2+ detected targets are flagged as co-infections.
Top-5 antigen filter
Ranks antigens by positivity then panel count and takes the top 5 for the monthly deep-dive analysis.
Monthly antigen table
Repeats the positivity calculation grouped by month × antigen to produce a time-varying view of infection prevalence.
Overall bar chart
ggplot geom_col with reorder(antigen, positivity) produces a horizontal bar chart sorted by positivity rate.
Monthly co-infection bucketing
Bucketizes panels into <2, 2, and 3+ detected targets. Formats cells as “count (percent)” strings using scales::percent.
gt table rendering
gt::gt formats the monthly co-infection table with a header and centered columns for consistent presentation.
HTML fallback save
gtsave writes the table to outputs/figures/coinf_monthly_table.html so it remains viewable outside the IDE viewer pane.
Deliverables
What the pipeline produces
Four distinct outputs, each with a matching data table printed to the console. Rate calculations use the panel as the unit of analysis rather than the individual test row, matching how positivity is clinically interpreted.
| Month | Panels | 2 targets | 3+ targets | Total co-inf |
|---|---|---|---|---|
| 2026-02 | 142 | 12 (8.5%) | 4 (2.8%) | 16 (11.3%) |
| 2026-03 | 168 | 18 (10.7%) | 6 (3.6%) | 24 (14.3%) |
| 2026-04 | 195 | 22 (11.3%) | 7 (3.6%) | 29 (14.9%) |
| 2026-05 | 203 | 19 (9.4%) | 5 (2.5%) | 24 (11.8%) |
| 2026-06 | 187 | 15 (8.0%) | 4 (2.1%) | 19 (10.2%) |
| 2026-07 | 176 | 14 (8.0%) | 3 (1.7%) | 17 (9.7%) |
Analytical Decisions
Deliberate choices that shape the output
Several small decisions in the code have meaningful downstream effects. Each was made explicitly rather than by default, and documented directly in the R script.
Panel as the unit of analysis
All rate calculations use n_distinct(req_panel_id) rather than raw row counts. This matches how a clinician interprets a panel result — a single answer per submission, not per test row.
Inconclusive excluded from denominator
Positivity = Detected / (Detected + Not Detected). Inconclusive is reported as its own rate. This prevents borderline results from artificially lowering the positivity signal.
Co-infection threshold at 2 targets
A panel with 2+ Detected targets counts as a co-infection. Monthly output further breaks out 3+ targets to reveal how often multiple pathogens appear together.
Top-5 antigen ranking
Monthly trend analysis is restricted to the top 5 antigens by positivity, then by panel volume as a tiebreaker — keeping the deep-dive focused on the highest-signal targets.
What This Demonstrates
Skills exercised by this project
-
SQL Server extraction at production quality Six-table join, two parallel window functions, soft-delete handling, and precise text filters — all in a single readable CTE.
-
Tidyverse fluency Pipe-based transformations using dplyr, lubridate, stringr, and scales — each step doing one thing, chained for clarity.
-
Every plot paired with a table The bar chart has an antigen_summary table beside it; the co-infection table has a summary. Nothing is shown without its underlying numbers.
-
Reproducible outputs gt tables are also saved to disk as HTML, so the analysis is viewable outside the IDE session — useful for handoff or async review.
Technology