Patient master CSV

Stage 7b writes three long-format CSV files next to dashboard_data.json. Each row is one record (a problem, a medication, a laboratory result, a note section and so on) with the patient's demographics joined on the front, so a reviewer can:

• open the file in a spreadsheet and filter by patient_id to see one person's whole record across every table;
• filter by category to see, for example, every medication in the cohort;
• filter by source to compare what came from C-CDA documents with what came from FHIR resources;
• check any value in the record viewer against raw_record_json, the original record.

File	Contents
patient_master.csv	Every record for every patient, from both C-CDA and FHIR
patient_master_fhir.csv	Patient header rows and FHIR records only
patient_master_ccda.csv	Patient header rows and C-CDA records only

All three have the same columns. The two split files include the patient header rows, so each can be read on its own.

Identified file

Names and birth dates are the leftmost columns, so a reviewer can always see whose record a row belongs to. The files are identified and stay in the approved environment.

Columns

Column	Present	Contents
last_name, first_name	always	Patient name; blank when no source gave one
dob	always	Date of birth, YYYY-MM-DD
patient_id	always	The patient identifier used throughout the pipeline
mrn	always	Medical record number, when available
gender	always	male, female, other or unknown
marital_status	always	When recorded
category	always	patient for the header row, otherwise the bundle table: problems, medications, procedures, labs_vitals, allergies, immunizations, careplans, diagnostic_reports, goals, notes, documents, document_references, encounters
source	always	fhir, ccda, or empty for the header row
effective_date	when known	The record's main date (effective, start, authored or recorded); the birth date on the header row
end_date	when known	End or resolution date
code, code_system, display_name	when coded	The code shown in the record viewer
all_codings_json	when coded	Every coding on the record, as compact JSON
value, unit	laboratory results and vital signs	Value and unit
status	when recorded	For example active, completed or final
text	varies	The main free text for the category: section narrative, dosage instruction, conclusion or description
source_file	when known	The file the record came from
raw_record_json	always	The whole original record as compact JSON

The files are UTF-8 with a byte-order mark and every cell quoted, so spreadsheet programs open them with the right encoding. A cell longer than 32,000 characters, below the spreadsheet limit of 32,767, is cut and ends with ...[truncated]; this affects only very long narratives and raw_record_json for very large records.

Sort order

Rows are sorted by last name, first name, patient ID, category and date, so each patient's rows sit together with the header row first and each category in date order.

Reviewing in a spreadsheet

1. Open patient_master.csv and turn on filters for the header row.
2. Filter patient_id or last_name to one patient to see that person's records in date order within each category.
3. Compare with the same patient in the record viewer: each row there has a matching row here with the same code, display_name and effective_date.
4. To check a value, paste the row's raw_record_json into a JSON viewer.

Reading it in Python or R

import pandas as pd
df = pd.read_csv('patient_master.csv', encoding='utf-8-sig', dtype=str)
df.groupby(['patient_id', 'category']).size().unstack(fill_value=0)     # records per patient and table
df.query("patient_id == '<id>' and category == 'medications'") \
  .sort_values('effective_date')[['effective_date', 'code', 'display_name', 'text']]

df <- read.csv('patient_master.csv', encoding = 'UTF-8', stringsAsFactors = FALSE)
table(df$category, df$source)

Which file to use

• patient_master.csv for most purposes: one file with every source.
• patient_master_fhir.csv when the analysis needs discrete FHIR values, such as dose, numeric results or encounter class.
• patient_master_ccda.csv when the analysis needs C-CDA section text, such as assessments and plans.

Related outputs

File	Shape
dashboard_data.json	The bundle the master files are built from
dashboard_data.xlsx	One sheet per table, without demographics joined; suits review by table
csv_exports/	One CSV per table with patient_id and no demographics
code_inventory.csv	One row per code, not per record (Code inventory)