NSG/541 Week 4: Cleaning, Sorting and Organizing the Data, sample paper

From 21,864 Rows to 18,019 Call Events: Cleaning, Linking and Organizing Nurse Call and Record Data for Analysis, Step by Step

[Student Name]

University of Phoenix

NSG/541: Data Analysis and Management

Week 4 Assignment

[Instructor Name]

[Date]

The hospital, the unit and all data are a composite written for a model paper.

The five measures defined in Week 3 need a clean, linked data set before any of them can be calculated. This paper prepares the data to calculate them. The preparation follows the decisions made in the Week 2 data quality assessment and records every step, because a result is only as trustworthy as the steps that produced it.

Tools and Protection

Data were prepared in the hospital's secure analytics environment using a spreadsheet and a simple query tool, with access limited to me and the data analyst. Patient names and medical record numbers were replaced with study codes after linking, and the linking key was stored separately. Nurse call data contain no patient identifiers on their own, but once linked to bed assignments they become protected health information, so the combined file was handled under the hospital's data security policy.

Step 1: Load and Standardize

The nurse call extract contained 21,864 rows. Timestamps arrived in two formats, one with seconds and one without, depending on the call type; all were converted to one date-time format with seconds. Room identifiers in the nurse call data used a hyphen, such as "4-12A," while the record used "412A"; both were converted to the record's format. Call types were mapped to five categories: standard, bathroom, staff assist, emergency and other.

Step 2: Correct the Clock

Following Week 2, 4 minutes and 10 seconds were subtracted from every nurse call timestamp so that nurse call and record times align. A check using ten code events not included in the original concordance sample confirmed alignment within 30 seconds.

Step 3: Remove Records That Do Not Belong

Staff-assist calls, 1,312 rows, and emergency calls, 66 rows, were separated from the patient-call data set and kept in a separate file. Calls from beds with no assigned patient at the time, 612 rows, were removed. The remaining patient-call data set had 19,874 rows.

Step 4: Merge Duplicates

Calls from the same bed or bathroom within 60 seconds of each other, and canceled together, were merged into single call events, keeping the first placement time and the final cancellation time. This combined 1,855 rows into their preceding events, leaving 18,019 call events. Merging duplicates changes the count of calls far more than it changes response times, which is why the rule was chosen before the results were known.

Step 5: Flag Cancellation Location and Cap Long Calls

Each call event was flagged as bedside-canceled or desk-canceled; 2,522 events, 14.0%, were desk-canceled. Of bedside-canceled events, 131 lasted longer than 60 minutes and were capped at 60 and flagged.

Step 6: Link Calls to Patients

Each call event from a bed was matched to the patient assigned to that bed at the call time, using bed assignment history from the record. For bathroom calls in private rooms, the room's patient was used. For shared-bathroom calls in semi-private rooms with two patients, 1,641 events, no patient was assigned; these events are included in unit-level measures but not in patient-level links. The linking was checked by drawing 50 random call events and confirming the assignment by hand in the record; all 50 were correct.

Step 7: Prepare the Fall Data

The eleven fall reports were coded with study codes, fall date and time, bed, activity at the time, whether elimination-related and injury level. For the three reports with approximate times, the stated time was used, following the Week 3 definition. Each fall was then linked to all call events from the patient's bed and bathroom in the 20 minutes before the fall time.

Step 8: Add Context

Each call event was tagged with its shift, hour of day and day of week. Staffing data for each shift, registered nurses and assistants on duty and census, were added so that response times can be compared with workload in Week 5.

Step 9: Verify the Final Data Set

The final patient-call data set contains 18,019 call events: 15,497 bedside-canceled and 2,522 desk-canceled. Checks confirmed no negative response times, no duplicate event identifiers and that every call event falls within the 90-day period. Totals were reconciled: 21,864 original rows minus 1,378 staff-assist and emergency calls, minus 612 calls from empty beds, minus 1,855 merged duplicates equals 18,019. Weiskopf and Weng (2013) describe validity checks and comparison with a gold standard among the main methods for assessing data quality, and both were used here, the hand check of 50 links and the reconciliation of totals.

The Documented Audit Trail

Every step is recorded in a data preparation log with its date, rule, records affected and the person who performed it, and the query code is saved with the log. The analyst repeated steps 1 through 9 independently from the raw extract and obtained the same final counts, which is the test that the process can be reproduced.

Decisions That Affect Results

Three preparation decisions could change results and were therefore made before any measure was calculated. Merging duplicates within 60 seconds reduces the call count but should barely change response times. Excluding desk cancellations from response time will make bedside response look slower than a simple average of all calls would, which is the honest view. Using reported fall times with a window rather than exact minutes avoids false precision. These choices follow the measure definitions, and Week 5 will test whether changing them alters conclusions.

Why the Data Set Is Organized This Way

The structure anticipates the questions others will ask. The falls committee will want each fall with its surrounding calls, which the link table provides. The manager will want response times by hour and shift, which the call events table provides. And the patient safety office will want fall rates, calculated from the falls table and census in the same way as the conventional rate per 1,000 patient days (Oliver et al., 2010). Workload data are kept at the shift level because published work suggests that call volume and patient turnover, not staffing alone, affect response times (Tzeng & Larson, 2011).

Organizing for Analysis

The data are organized into three linked tables: call events, one row per event with times, type, cancellation location and context; falls, one row per fall with its attributes; and a link table connecting each fall to call events in the preceding 20 minutes. A fourth table holds shift-level staffing. This structure lets each Week 3 measure be calculated from one or two tables.

Limits That Remain

The data set still cannot identify the patient for shared-bathroom calls in semi-private rooms, carries approximate fall times for three falls and does not record why patients called. These limits are documented and will be reported with the analysis.

Conclusion

A 90-day extract of 21,864 nurse call rows became 18,019 call events linked to patients, falls and staffing through nine documented steps, each with a rule, a count and a check. The audit trail and the independent repetition by the analyst make the data set reproducible. Week 5 will analyze it.

References

Oliver, D., Healey, F., & Haines, T. P. (2010). Preventing falls and fall-related injuries in hospitals. Clinics in Geriatric Medicine, 26(4), 645-692. https://doi.org/10.1016/j.cger.2010.06.005

Tzeng, H.-M., & Larson, J. L. (2011). Exploring the relationship between patient call-light use rate and nurse call-light response time in acute care settings. CIN: Computers, Informatics, Nursing, 29(3), 138-143. https://doi.org/10.1097/NCN.0b013e3181fc41d9

Weiskopf, N. G., & Weng, C. (2013). Methods and dimensions of electronic health record data quality assessment: Enabling reuse for clinical research. Journal of the American Medical Informatics Association, 20(1), 144-151. https://doi.org/10.1136/amiajnl-2011-000681