Tembo Hotel’s booking file becomes usable once one duplicate is removed and the fields are standardised. David Mwandairo’s 2026 case study on DEV Community starts with 286 rows and ends with 285 unique bookings, 253 of which are checked-out stays. Only those 253 stays count as collected revenue, at KES 7,752,400. Two bookings whose totals do not reconcile and a payment-method pattern among cancellations are the caveats that matter most before drawing conclusions.
What was wrong with the raw file
The file, named tembo_hotel_dirty.csv in the case study, is a CSV with 286 rows and 20 columns, with one row per booking. The author found five categories of defect:
As an Amazon Associate I earn from qualifying purchases.
- An exact duplicate. Booking BK0006 appeared twice. One copy was removed, leaving 285 rows and 285 unique booking IDs.
- Inconsistent guest names. Capitalisation varied, and some names carried extra whitespace.
- Inconsistent city values. City names had spelling and casing differences.
- Mixed date formats. Dates were not stored in one consistent format.
- Uneven categorical vocabulary. Short fields such as room type and payment method used more than one label for the same category.
How the cleaning was staged
The author did not clean the file in place. The workflow moved data through three stages, so that a bad value could be inspected rather than silently dropped or allowed to block the import.
- Load everything as text. All raw fields go into a staging table as text. Malformed dates or numbers therefore cannot stop the load.
- Inspect the anomalies. Look for duplicate booking IDs, spelling and casing variants, unparseable dates and category labels outside the expected set before changing anything.
- Clean and standardise. Trim whitespace, normalise name and city casing, correct city spellings and map each category to one label.
- Convert to database types. Dates become date values, amounts become numeric values and so on.
- Insert into a constrained clean table. The case study reports two constraints: guest ratings must fall between 1 and 5, and checkout must be later than check-in.
- Derive a reporting month in a view. A view computes the month from the check-in date, so every monthly query uses the same definition.
Because the staging table keeps the raw text, any row that fails a later check can be traced back to what the file actually contained.
#1 Best Overall
Scope of the cleaned data
After cleaning, the data covers check-ins from June 10, 2023 to December 31, 2024. It contains 285 bookings across 10 rooms. Fifteen records have no guest rating, so any rating-based figure rests on the remaining 270 bookings.
Defining collected revenue
The author counts only bookings with the status Checked Out as collected revenue. Cancelled and no-show bookings still carry an amount, but that is booked value that was never collected. Reports that add all three statuses together would overstate income, so the distinction should be kept in any summary.
Booking status and value
The figures below are as reported in the case study, computed by David Mwandairo in 2026. They have not been recalculated against the original CSV for this article. The average column is derived here by dividing the reported total by the reported count, and it is not a figure the case study states.
| Booking status | Bookings | Total amount (KES) | Average per booking (derived) | Treatment in the analysis |
|---|---|---|---|---|
| Checked Out | 253 | 7,752,400 | about 30,642 | Counted as collected revenue |
| Cancelled | 23 | 910,500 (listed value) | about 39,587 | Booked value, not collected |
| No Show | 9 | 264,800 (listed value) | about 29,422 | Booked value, not collected |
Room types and length of stay
Standard rooms
Standard has the most checked-out stays of any room type, at 97 in the case study’s table.
Suite rooms
Suites have the longest reported average stay, at 3.19 nights per checked-out booking. The case study does not report comparable averages for every room type in the material summarised here, so the ranking applies only to the average it gives.
Where guests were checking out from the city table
In the case study’s city table, Nairobi has 111 checked-out stays. Its spelling and casing were standardised during cleaning, which is why it appears as a single category.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Two totals that do not reconcile
The author checked each booking’s recorded total against nightly rate multiplied by nights, plus the service price. 283 of the 285 totals pass this check. The two exceptions are BK9007 (Breakfast Buffet) and BK9004 (Laundry), and the service amounts on those two bookings total KES 2,000.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →The file alone cannot show whether those services were billed separately and left out of the recorded total, or were simply omitted from the record. The author left the recorded totals unchanged. Until the underlying billing records are checked, these two rows should be treated as unexplained differences rather than confirmed errors.
Bank transfers and cancellations
All 32 cancelled or no-show bookings (23 cancelled and 9 no-shows) were paid by bank transfer. None of them appears under card, cash or M-Pesa. Bank-transfer bookings in the file involve only two staff members.
The data cannot separate two explanations: reservations that lapsed unpaid, and recording differences in how bank-transfer payments were logged. This is an association in the dataset. It does not show that the payment method caused cancellations, or that either staff member is responsible for the pattern.
What the case study set out to answer
The case study frames its work around four practical questions: which rooms earn the most, which months are busiest, how staff are doing, and whether guests are happy. The findings above answer the room and booking-status parts of that agenda. The monthly, staff, payment and guest-rating breakdowns are presented in the original article, and they should be read there with the caveats above in mind.
The original write-up is on DEV Community under the author’s name, David Mwandairo, and was published in 2026.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
