Skip to content

About

No description, website, or topics provided.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

14 Commits

Folders and files

Repository files navigation

# Lab 4: Database-Driven Feature Table

PUBH 6854, Fall 2026. Abdulmateen Oyediran.

This repo turns cohort.db (566 lung adenocarcinoma patients from the TCGA PanCancer Atlas,

via cBioPortal) into a feature table with one row per patient. The columns are sex, age at

diagnosis, stage, tissue source site, tumor mutation burden, and a count of protein-altering

mutations in each of TP53, KRAS, EGFR, STK11, and KEAP1.

SQL does the joining and counting. Python reshapes the counts to wide format. An R script

builds the same table separately and checks that it matches.

# How to run

Set up the environment once:


conda env create -f environment.yml

conda activate pubh6854-lab4

Then, from this folder, this command rebuilds the feature table from cohort.db:


python scripts/build\_feature\_table.py

For the R version


Rscript scripts/build\_feature\_table.R

Both scripts should print the expected and actual row count at every step and stop with an error if any count doesn't match.

## What it produces

| File | Made by | Contents |

|---|---|---|

| output/feature\_table.tsv | Python script | **The feature table**: 566 rows, 12 columns, tab-separated, missing values written as NA |

| output/feature\_table\_r.tsv | R script | The same table built in R. It matches the Python file value for value |

## Queries: expected vs. actual row counts

| Query | File | Expected | Got |

|---|---|---|---|

| 1. Protein-altering mutations per patient per gene | sql/01\_gene\_counts\_per\_patient.sql | 2,830 (566 × 5) | 2,830 |

| 2. Patients per tissue source site per stage | sql/02\_patients\_per\_site\_and\_stage.sql | 142 | 142 (counts add up to 566) |

| 3. Patient base table for the feature table | sql/03\_patient\_base.sql | 566 | 566 |

To run Query 2 on its own, open cohort.db in DB Browser for SQLite and run the file in the

Execute SQL tab. Each SQL file's header comment explains why its row count is what it is.

## Where each task is

| Task | File |

|---|---|

| 1. Schema: tables, keys, grain, relationships | docs/schema.md |

| 2. SQL queries with expected vs. actual counts | sql/ and the table above |

| 3. Feature table | scripts/ and output/feature\_table.tsv |

| 4. Missing data | docs/missing\_data.md |

| 5. Data dictionary | docs/data\_dictionary.md |

| AI use | AI\_USAGE.md |

## Folder layout


lab4-assignment/

├── README.md

├── AI\_USAGE.md

├── environment.yml

├── data/raw/cohort.db

├── sql/        three queries

├── scripts/    build\_feature\_table.py and build\_feature\_table.R

├── output/     the feature table TSVs

└── docs/       schema, missing data, data dictionary

About

No description, website, or topics provided.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages