·

10 min read

·

Aug 22, 2024

--

This is one project I couldn’t wait to work on. Why you may ask? It is because I am an optometrist, meaning that I have a background in medicine so understanding my data was like taking chocolate from a baby.

Steps for this project :

· Get to know our data.

· Clean and transform our data using Power Query and Excel.

· Visualize it and then build a dashboard using Excel and PowerBI.

There is no need to feel intimidated if you do not understand medical language as the problem to be solved is not focused much on the diagnosis or procedure.

· After studying the data, I noticed 12 columns and 433 rows. After carefully going through each column, I realized that two out of the 12 columns served no purpose whatsoever because they had no value. I deleted the two columns but before I did, I created a new sheet and copied all the data into there into there to be on the save side. Below is the overview of the data.

For the modified data

Columns went from 12 to 10.

Let’s get some info about the columns:

The date-of-report column had one anomaly and using power query in Excel, I cleaned it from 26MAR 2021 to 26 MAR 2021.

Another thing to notice here is that the surgeon column had some names like general, local, spinal, and spinal/epidural. It also contained NIL. Using power query, I changed all to Nil.

The columns scrub nurse, diagnosis, procedure, scheduled time, and reason for delay were also cleaned appropriately. The diagnosis and procedure had some misspelled words, and the scheduled time format was changed to the correct one.

This analysis aims to improve surgical efficiency by Optimizing surgical delay time and improving patient care, I had to create three more columns, delay time, month, and procedure time. This analysis won’t focus much on the procedure time. To get the delay time column, I subtracted the time-in column which is the time the surgeon started the procedure from the scheduled time which is the time the procedure was scheduled for.

The scheduled time, time-in, and time-out are in 12-hour formats, so they were first converted to 24hrs formats before converting to minutes. Below are the functions used:

Below are the 3 columns(scheduled time, time-in and time-out)

Using the new columns, I calculated delay time and procedure time. To determine the delay time column, an If statement was used. It says when the scheduled time is null, return null, when the time-in is less than the scheduled time, the delay time is zero and if the time-in is greater than scheduled time, subtract the scheduled time from the time-in.

For the procedure column, subtract time-in from time-out.

I extracted the month and weekday from the date-of-report column to form new columns using the function below;

Also, I added a column where scheduled time is grouped into morning, afternoon, or night.

Ok, to the more fun stuff: some little EDA.

The statistical analysis for the delay time(mins) shows that the average, median, and standard deviation are 91.20, 32, and 154.80 respectively. The maximum delay time is 930 mins and the minimum delay time is 0.

Dr. Ekwe has the highest delay time, totaling 61. Dr. Ogbu and Dr. Ogedegbe also have significant delays, with 39 and 37, respectively.

Surgeons like Dr. Ajakaiye and Dr. Akeju have relatively lower delay times, both with 16.

Below are the top 10 surgeons with the most delay time:

NIL (possibly indicating unassigned or unspecified scrub nurses) has the highest delay time count of 94. This suggests potential issues in scheduling or assigning specific scrub nurses to surgeries.

Nurse Grace has a significant delay time count of 68, followed by Nurse Gonya with 47 and Nurse Hannah with 45.

Nurse Nkechi has the lowest delay time count of 9, indicating strong time management and efficiency.

The top 10 scrub nurses with the highest delay time are seen in the table below.

Anesthetist GEORGE has the highest individual delay count with 44 delays, followed by UGHEOKE with 60 delays. The combined delays involving multiple anesthetists (e.g., GEORGE/UGHEOKE and UGHEOKE/AJIBOLA) indicate potential coordination issues. A significant number of delays (111) are marked as “NIL,” suggesting that in many cases, the anesthetists names were not recorded.

Delays involving multiple anesthetists (e.g., GEORGE/UGHEOKE, UGHEOKE/AJIBOLA) suggest possible issues with communication or coordination between team members.

The top 10 anesthetists with the most delay time are seen below:

Circumcision procedures have the highest total delay time, with 18 counts of recorded delays.

ORIF with Plate and Screws also has a significant delay time count of 16.

Total Knee Replacement and Total Hip Replacement procedures have delay counts of 13 and 9, respectively.

The procedures with the highest delay times may indicate potential bottlenecks or inefficiencies in the surgical workflow, possibly due to the complexity of the procedures, preparation time, or resource availability.

The top 6 procedures with the most delay time are seen below;

March has the highest delay time, totaling 162 minutes, which is significantly higher than the delay times in April (135 minutes) and May (135 minutes).

The consistent delay times in April and May suggest that while some improvements might have been made, delays remain an ongoing issue.

The daily log recorded was from March to May and March showed the highest delay time.

The count of delays for Sunday is significantly low (26) compared to the other days. This could indicate reduced operational activities on Sundays, possibly due to fewer scheduled procedures or reduced staff availability.

Weekdays Monday to Thursday have consistently high counts of delay times (ranging from 70 to 76). This suggests that these are the busiest days, which could result in higher chances of delays due to workload, scheduling issues, or other factors.

Friday was slightly lower (58) compared to earlier in the week but still higher than weekends. This could reflect a typical reduction in scheduled procedures as the week winds down.

Saturday: The count of delays (51) is closer to the Friday count but significantly higher than Sunday. Saturdays may still have a substantial number of procedures, but perhaps with reduced staff or resources, leading to some delays.

The Morning period has the highest average delay time of 0.07, indicating that delays are more significant during this time. The Evening period has the lowest average delay time 0.02, suggesting that delays are less frequent or less severe during this time. The data labeled as NULL doesn’t contribute to delay times, so it might be because the procedure is an emergency or there is no appropriate recording.

Previous surgery delayed is the highest reason for delay amounting to 35 counts.

The reason for the delay of procedure with the highest average delay time is the patient didn’t come on time. The average delay time is 864 minutes.

1. SURGEONS:

Dr. Ekwe, Dr. Ogbu, and Dr. Ogedegbe should be the primary focus. Conduct a detailed review of the cases they were involved in to understand why the delays occurred. This could include examining the nature of the surgeries, staffing levels, availability of necessary resources, or any other factors contributing to these delays.

For surgeons with higher delays, time management training or scheduling adjustments might be necessary. This could include ensuring that all necessary preparations are made in advance and that any potential issues (like equipment readiness or staff availability) are identified and resolved before the surgery starts.

Consider assigning more experienced or additional support staff to surgeons with higher delay times to help streamline the process. Develop or reinforce standardized protocols that all surgeons must follow before a procedure. This could include a checklist to ensure that all elements are in place before the scheduled start time, helping to minimize unexpected delays.

If the delays are due to systemic issues (e.g., equipment availability, and scheduling conflicts), these need to be addressed at the organizational level. This could involve investing in additional resources, improving scheduling systems, or enhancing communication between departments.

2. MONTH

Since March had the highest delay time, it is crucial to investigate what specifically led to this peak. Review the surgeries conducted in March, the personnel involved (surgeons, scrub nurses, anesthetists, etc.), and any external factors (e.g., equipment issues, staffing shortages) that may have contributed to the delays.

3. PROCEDURE:

By addressing the root causes of delays in specific procedures, such as Circumcision, ORIF with Plate and Screws, and joint replacements, the surgical team can enhance overall efficiency and reduce wait times. This will improve patient outcomes, increase the throughput of surgical procedures, and contribute to a more effective and responsive healthcare environment. Regular monitoring and continuous improvement efforts will help sustain these gains over time.

4. REASON FOR DELAY:

By addressing the most common causes of delays — such as previous surgeries running late, surgeon-related issues, and patient punctuality — through improved coordination, communication, and strict adherence to schedules, the overall efficiency of surgical procedures can be significantly enhanced. These recommendations will help reduce delays, improve patient satisfaction, and contribute to a more effective surgical department. Regular monitoring and adjustments will ensure that these improvements are sustained over time.

Resource Allocation: Since the delays are higher from Monday to Thursday, consider increasing staff availability or extending operational hours during these days to manage the workload more effectively.

Process Optimization: Investigate the causes of delays specifically on Wednesdays and Thursdays, as these days have the highest counts (76). Streamlining the scheduling or preparation processes might reduce delays.

Weekend Efficiency: With lower counts on the weekends, particularly on Sunday, consider if resources could be reallocated or if certain non-critical procedures could be rescheduled to these days to balance the workload better across the week.

Scheduling Adjustments: Evaluate if some procedures could be shifted from the busiest days (Monday to Thursday) to less busy days (Friday to Sunday) to alleviate pressure on the system during peak times.

Data-Driven Strategy: Continuously monitor the delay trends by weekday, and adjust strategies as needed based on updated data. Implementing a feedback loop where the causes of delays are regularly analyzed could help in making more informed decisions over time.

Hope you enjoyed my walkthrough of this project. Drop in the comments any suggestions you have to improve the analysis, dashboard or anything in general.