Hospital Operations Analysis
An unclean extract of 5,075 hospital visit records, profiled and cleaned in MySQL to find where demand and resource pressure concentrate.
- Records received
- 5,075
- Records analysed
- 5,0755,000
- Period covered
- Jan 2023 – Dec 2024
- Distinct patients
- 5,000
- Departments
- 8
- Missing visit dates
- 0
- SQL scripts
- 5
- Data-quality issues logged
- 11
What it found
75 rows that looked like repeat patients were exact copies of other rows.
Found by comparing all 22 columns, not just the patient identifier. Removing them is why the analysed count is 5,000 and why every patient identifier in the clean table is unique.
Ventilator demand comes almost entirely from critical visits. Mild and moderate visits never need one.
Severity, not department, is what predicts a ventilator here. That makes ventilator planning a question about how many critical cases arrive, which is a different question from how many beds a department needs.
How it was done
- Setup Create the database and load the raw extract. The raw table is never altered after this point, so every later figure can be traced back to what arrived.
- Raw profiling Count rows, distinct values, blanks and format variants before changing anything. Case-insensitive collation hides case variants, so duplicate checks were re-run under a binary collation to see them.
- Cleaning Build a separate clean table rather than editing the original: de-duplicate across all 22 columns, trim and standardise labels, convert three different date formats to real dates, derive month, weekday and season from the date, and add flags for the rows that contradict themselves.
- Validation Nine read-only checks against the clean table: row and distinct counts, completeness, flag counts, date range, and one collation across every text column. All nine matched.
- Analysis Answer the six business questions on demand, length of stay, resource needs, bed and ICU capacity pressure, and 30-day readmissions.
Every cleaning decision, and why
Eleven data-quality issues were found and logged. Six were fixed, three were flagged and deliberately left alone, and two were accepted as they are. The three I chose not to fix are the ones worth reading.
-
The first import loaded the wrong number of rows.
A column was being read as the wrong type, so rows were dropped on the way in. Re-imported with that column as text, after which the count matched the source file. Everything downstream depends on this being right, so it was settled before any profiling began.
Fixed
-
75 rows were exact copies of another row across all 22 columns.
Kept one of each pair. The count went from 5,075 to 5,000.
Fixed
-
Gender, department, length of stay and oxygen units each had blank values.
Stored as NULL rather than guessed at. A missing department reports as "Unknown" instead of being assigned to one.
Fixed
-
The same label appeared with different spacing and capitalisation.
Trimmed and mapped to one label each, so a group-by counts them once.
Fixed
-
Visit dates arrived in three different formats.
Converted to real dates, which is what makes any ordering or date range reliable.
Fixed
-
13 rows had a month that disagreed with their own visit date.
Month, weekday and season are derived from the date rather than trusted from the source column, and the 13 rows keep a flag recording the disagreement.
Fixed
-
93 rows record an ICU admission but no bed occupied.
Flagged, not corrected. Either value could be the wrong one and the data does not say which, so inventing a bed would be fabricating a record.
Flagged
-
61 rows record a ventilator in use but no oxygen required.
Flagged for the same reason. The contradiction is real and is reported as a contradiction.
Flagged
-
52 outpatient visits record a length of stay, and 6 stays run to 60 days or more.
Flagged rather than removed, and excluded from every length-of-stay average. An outpatient visit should not have a stay at all, and a 60-day stay would pull an average on its own.
Flagged
-
Some ages are implausible: 0, 110 and 120.
Accepted without a flag. Age is not used in any of the six questions, so cleaning it would be work that changes no answer.
Accepted
-
Oxygen units used is not recorded per visit.
Accepted. Oxygen demand is reported as required or not required, and the units column is not used for anything.
Accepted
What this analysis cannot tell you
- It is descriptive. ICU holding nearly half the admitted bed-days says where bed-days sit, not that ICU is inefficient or badly run.
- Each patient appears exactly once, so a readmission is a yes or no recorded on a visit, not a return that was tracked over time.
- Bed, ICU and oxygen counts are hospital-level snapshots repeated on every visit row. They describe the hospital on that date, not the patient.
- Oxygen units used is not per visit, so nothing here quantifies how much oxygen a visit consumed.