Skip to content

Working with CSV files — Python

This page explains how to load and explore your delivered CSV files using Python. Code examples use pandas, which is pre-installed on the OUH Data Science VM.

See Understanding your data first if you haven't already — it explains the folder structure and how datasets relate to each other.

Downloads

You can download ready-to-use scripts and open them directly on your VM:

To open Jupyter Notebook on your VM, open a terminal and run: 

jupyter notebook

Setting your data path

All examples use a DATA_DIR variable pointing to the folder containing your dataset subfolders. Update this to match where your data is stored.

import os
import pandas as pd

# Windows
DATA_DIR = r"C:\Users\yourname\data"
# or shared storage
DATA_DIR = r"Z:\your-project\data"

# Linux
DATA_DIR = "/home/yourname/data"

Loading a dataset

Each dataset lives in its own subfolder containing one CSV file. This helper function finds and loads it:

def load_dataset(dataset_name):
    folder = os.path.join(DATA_DIR, dataset_name)
    csv_files = [f for f in os.listdir(folder) if f.endswith(".csv")]
    if not csv_files:
        raise FileNotFoundError(f"No CSV file found in {folder}")
    filepath = os.path.join(folder, csv_files[0])
    print(f"Loading: {filepath}")
    return pd.read_csv(filepath)

Ignore the other files

Each folder also contains _SUCCESS, _committed_... and _started_... files. These are Spark metadata files — ignore them. The helper function above handles this automatically by only loading .csv files.

Loading demographics

demographics = load_dataset("demographics")

print(f"Rows: {len(demographics):,}")
print(demographics.head())
print(demographics.dtypes)

Exploring a dataset

# Load a clinical dataset
diagnosis = load_dataset("diagnosis")

# Basic info
print(f"Rows: {len(diagnosis):,}")
print(f"Columns: {list(diagnosis.columns)}")

# Preview
print(diagnosis.head())

# Check for missing values
print(diagnosis.isnull().sum())

# Summary statistics for numeric columns
print(diagnosis.describe())

Joining datasets

All datasets link to demographics via salted_warehouse_identifier. Use this as your join key:

# Inner join — only patients present in both tables
merged = demographics.merge(
    diagnosis,
    on="salted_warehouse_identifier",
    how="inner"
)
print(f"Merged rows: {len(merged):,}")
print(f"Unique patients: {merged['salted_warehouse_identifier'].nunique():,}")

# Left join — all demographics rows, with diagnosis where available
merged_left = demographics.merge(
    diagnosis,
    on="salted_warehouse_identifier",
    how="left"
)

Working with dates

Date columns are loaded as strings by default. Convert them to datetime for date-based analysis:

demographics["BIRTH_DT_TM"] = pd.to_datetime(demographics["BIRTH_DT_TM"], errors="coerce")
demographics["DECEASED_DT_TM"] = pd.to_datetime(demographics["DECEASED_DT_TM"], errors="coerce")

# Calculate age at a reference date
import datetime
reference_date = pd.Timestamp("2024-01-01")
demographics["age"] = (reference_date - demographics["BIRTH_DT_TM"]).dt.days / 365.25

print(demographics["age"].describe())

Listing all available datasets

datasets = [
    d for d in os.listdir(DATA_DIR)
    if os.path.isdir(os.path.join(DATA_DIR, d))
]
for d in sorted(datasets):
    folder = os.path.join(DATA_DIR, d)
    csv_files = [f for f in os.listdir(folder) if f.endswith(".csv")]
    if csv_files:
        size_mb = os.path.getsize(os.path.join(folder, csv_files[0])) / (1024 * 1024)
        print(f"{d:<45} {size_mb:.1f} MB")

Saving results

Save outputs to your VM's local storage or shared storage. Do not save identifiable data.

# Save a summary table
summary = demographics["SEX"].value_counts().reset_index()
summary.columns = ["sex", "count"]
summary.to_csv(os.path.join(DATA_DIR, "sex_summary.csv"), index=False)

When you are ready to export results out of the TRE, use the airlock. See Airlock requests and Egress and disclosure control for guidance.