📊 Day 14 - Data Download

Hospital Patient Management - Prescription Tracking System

Download CSV files for all 4 tables with 20+ rows of messy hospital data

Healthcare Data Prescription Tracking Risk Scoring Data Cleaning

👥 Table 1: patient_records

25 rows of messy patient data with inconsistent formats, NULL values, and various date formats

patient_id	full_name	date_of_birth	admission_date	discharge_date	diagnosis
P001	john smith	15/05/1985	2024-01-10 08:30:00	2024-01-15	Hypertension
p002	SARAH JONES	1990-08-22	10/01/2024	NULL	Diabetes
P003	Mike Wilson	1978-03-10	2024/01/12 14:45	2024-01-20	flu
P004	anna garcia	22-07-1995	2024-01-15	NULL	PNEUMONIA
p005	Bob Brown	1982/11/30	15/01/2024 09:00	2024-01-18	Fracture
P006	EMMA DAVIS	1998-02-14	2024-01-16 10:15:30	2024-01-22	Asthma
p007	david lee	10/09/1975	16/01/2024	2024-01-19	Migraine
P008	Lisa Martinez	1989-07-25	2024/01/17 11:30	NULL	DEPRESSION
P009	JAMES TAYLOR	05-12-1992	2024-01-18	2024-01-25	Bronchitis
p010	maria lopez	1980/03/18	18/01/2024 14:00	2024-01-21	arthritis
P011	Robert Clark	1987-11-05	2024-01-19 08:45:00	NULL	COPD
p012	NANCY WHITE	15/04/1993	19/01/2024	2024-01-23	Gastritis
P013	kevin harris	1976-06-30	2024/01/20 09:30	2024-01-26	Insomnia
P014	Linda Martin	20-08-1991	2024-01-21	NULL	ANXIETY
p015	PAUL THOMPSON	1984/01/22	21/01/2024 10:00	2024-01-24	Allergy
P016	karen walker	1996-09-12	2024-01-22 11:15:00	2024-01-28	ECZEMA
p017	Mark Hall	08/11/1979	22/01/2024	NULL	Sinusitis
P018	SANDRA YOUNG	1988-04-17	2024/01/23 13:45	2024-01-27	gout
P019	Daniel King	12-06-1994	2024-01-24	2024-01-29	GERD
p020	betty wright	1981/07/08	24/01/2024 15:30	NULL	Vertigo
P021	GEORGE ALLEN	1977-10-25	2024-01-25 09:00:00	2024-01-30	Anemia
p022	helen scott	18/02/1990	25/01/2024	2024-01-31	THYROID
P023	Ryan Green	1986-12-03	2024/01/26 10:45	NULL	Obesity
P024	MICHELLE BAKER	30-05-1995	2024-01-27	2024-02-01	UTI
p025	steven adams	1983/09/14	27/01/2024 12:00	2024-02-02	CELLULITIS

💊 Table 2: medications

5 rows of medication reference data with risk levels (Clean data)

med_id	medication_name	category	unit_price	risk_level
MED001	Aspirin	Pain Relief	5.50	LOW
MED002	Metformin	Diabetes	12.00	LOW
MED003	Lisinopril	Hypertension	18.50	MEDIUM
MED004	Warfarin	Blood Thinner	25.00	HIGH
MED005	Morphine	Pain Relief	45.00	HIGH

📋 Table 3: prescriptions

30 rows of messy prescription records with missing data and format inconsistencies

prescription_id	patient_id	doctor_id	med_id	dosage	prescription_date	status
RX001	P001	D101	MED003	10mg	2024-01-10	ACTIVE
RX002	p002	d102	med002	500 mg	10/01/2024	active
RX003	P003	D101	MED001	NULL	2024/01/12	Completed
RX004	P004	D103	med999	250mg	15-01-2024	ACTIVE
RX005	NULL	D102	MED005	5mg	2024-01-16	cancelled
RX006	P006	d103	MED001	100 mg	16/01/2024	ACTIVE
RX007	p007	D101	med003	20mg	2024/01/16	active
RX008	P008	D104	MED002	NULL	17-01-2024	ACTIVE
RX009	P009	D102	MED001	75mg	2024-01-18	Completed
RX010	p010	d105	med004	5 mg	18/01/2024	active
RX011	P011	D103	MED003	15mg	2024/01/19	ACTIVE
RX012	P012	D101	med001	50 mg	19-01-2024	Completed
RX013	p013	D102	MED005	NULL	2024-01-20	ACTIVE
RX014	NULL	D104	MED002	1000mg	20/01/2024	cancelled
RX015	P015	d101	med003	25mg	2024/01/21	active
RX016	P016	D103	MED001	100 mg	22-01-2024	ACTIVE
RX017	p017	D105	med004	NULL	2024-01-22	Completed
RX018	P018	D101	MED002	500 mg	23/01/2024	active
RX019	P019	d102	med003	10mg	2024/01/24	ACTIVE
RX020	p020	D104	MED005	10 mg	24-01-2024	ACTIVE
RX021	P021	D103	med001	NULL	2024-01-25	Completed
RX022	P022	d101	MED003	20mg	25/01/2024	active
RX023	p023	D102	med002	750 mg	2024/01/26	ACTIVE
RX024	P024	D105	MED001	50mg	27-01-2024	Completed
RX025	NULL	D104	med004	NULL	2024-01-27	cancelled
RX026	P001	d103	MED005	15 mg	28/01/2024	ACTIVE
RX027	p005	D101	med003	30mg	2024/01/28	active
RX028	P010	D102	MED002	500 mg	29-01-2024	ACTIVE
RX029	P015	D104	med001	NULL	2024-01-30	Completed
RX030	p020	d105	MED004	7.5mg	30/01/2024	active

👨‍⚕️ Table 4: doctors

8 rows of doctor information with messy names and statuses

doctor_id	doctor_name	specialization	license_status	hire_date
D101	dr. emily chen	Cardiology	ACTIVE	2020-03-15
d102	DR. MICHAEL BROWN	Endocrinology	active	15/06/2019
D103	Sarah Davis	general medicine	ACTIVE	2021-09-01
D104	james wilson	Orthopedics	SUSPENDED	2018/12/10
d105	LISA MARTINEZ	Pediatrics	active	01-08-2022
D106	Robert Taylor	Neurology	ACTIVE	2019-11-20
d107	KAREN MOORE	Dermatology	active	10/03/2020
D108	david anderson	PSYCHIATRY	SUSPENDED	2017/05/15

📝 How to Import CSV Files

MySQL:

CREATE DATABASE hospital_db;
USE hospital_db;

-- Import patient_records
LOAD DATA LOCAL INFILE 'patient_records.csv'
INTO TABLE patient_records
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Repeat for other tables...

PostgreSQL:

CREATE DATABASE hospital_db;
\c hospital_db

-- Import patient_records
COPY patient_records FROM '/path/to/patient_records.csv'
DELIMITER ',' CSV HEADER;

-- Repeat for other tables...

Tip: Create tables with appropriate data types before importing. Use VARCHAR for IDs, DATE for dates, DECIMAL for prices.

← Back to Day 14 Challenge