Cleaning and Analyzing Tembo Hotel's Bookings

One guest at Tembo Hotel & Suites checked out three days before checking in. The spreadsheet says so. It also spells Nairobi as Nairobi and as NAIROBI, files a town called Thikax under guest cities, and writes January dates as 10/01/2024, 01-12-2024 and 28-01-24, three formats in one column.

That spreadsheet is the whole booking history of a mid-range business hotel in Nairobi, open since 2023. The Hotel Director wants to know which rooms earn the most, which months are busiest, how the staff are doing and whether guests are happy. Nobody can answer those questions from a file like this. Any total you add up is wrong by an amount you can't measure.

This article follows the project from the raw CSV to the finished answers. I load the file into PostgreSQL, fix the damage one defect at a time, and then run the six analyses the Director asked for. I ran every query below, in order, against the real file on PostgreSQL 16. The output under each query is what came back, laid out the way DBeaver shows it: a result grid for SELECT, a statistics line for everything else.

The brief in plain words

The Director's message boils down to four worries: money, busy periods, staff and guest happiness. The brief turns them into six groups of questions:

1. Revenue by month, by room type and by payment method.
2. Which room types are booked most, and how many nights a guest stays on average.
3. The top 10 cities guests come from, and the average rating per room type.
4. Which staff member handled the most bookings, and which department earns the most.
5. Month-over-month revenue growth (with a window function), and the busiest and quietest months.
6. The cancellation rate per room type, and the revenue lost to cancellations and no-shows.

The file is tembo_hotel_dirty.csv: 286 rows and 20 columns, one row per booking. Each row carries the guest, the room, the dates, the staff member who handled it, how the guest paid, any extra service (spa, laundry, airport pickup) and the guest's rating.

The plan: register first, get a room second

A guest doesn't walk straight to a room. They stop at the front desk, where staff check the details, and only then get a key. The database work follows the same order:

tembo_hotel_dirty.csv | v staging_bookings every column is TEXT, so nothing is rejected on arrival | fix each defect with UPDATE / DELETE v clean_bookings real types and CHECK constraints, so bad data can't get in | v v_clean_bookings a view that adds a month column for the analysis | v six business questions 

The staging table exists because a strict table would refuse the file at the first KES 34000 in a numeric column. With everything as text, the load always succeeds, and the cleaning happens where I can watch it, one column at a time.

Step 1: Ingest the CSV without losing a row

I keep the project in its own schema so it never collides with anything else in the database.

CREATE SCHEMA IF NOT EXISTS tembo; SET search_path TO tembo; DROP TABLE IF EXISTS tembo.staging_bookings; CREATE TABLE tembo.staging_bookings ( booking_id TEXT, guest_name TEXT, guest_phone TEXT, guest_city TEXT, guest_nationality TEXT, room_no TEXT, room_type TEXT, room_rate_per_night TEXT, check_in_date TEXT, check_out_date TEXT, nights_stayed TEXT, staff_name TEXT, staff_department TEXT, staff_salary TEXT, payment_method TEXT, booking_status TEXT, total_amount TEXT, service_used TEXT, service_price TEXT, guest_rating TEXT ); 

Statistics tab: Updated Rows = 0

In DBeaver you can right-click the table and choose Import Data, then point it at the CSV. If you prefer a script, PostgreSQL's COPY does the same job in one line:

COPY tembo.staging_bookings FROM '/path/to/tembo_hotel_dirty.csv' WITH (FORMAT csv, HEADER true); 

Statistics tab: Updated Rows = 286

COPY reads the file on the database server, so the path must be visible to the server and your role needs the right privileges. From a client machine, use \copy in psql or the DBeaver wizard.

Next, count the rows and look at the first few. This is the sanity check I run after every load.

SELECT COUNT(*) AS rows_loaded FROM tembo.staging_bookings; 

Result grid: 1 row fetched

SELECT booking_id, guest_name, guest_phone, guest_city, room_type, check_in_date, total_amount FROM tembo.staging_bookings LIMIT 8; 

Result grid: 8 rows fetched

286 rows, as promised. Look at the grid, though. Row 3 has stray spaces around a name, row 4 has dashes in a phone number, row 5 has a +254 prefix instead of a leading zero, row 7 has a date in month-day-year order, and row 8 has an empty city. We are just getting started.

Step 2: Audit the damage before touching anything

I don't fix by feel. One query counts every kind of damage I can test for, so I know the size of the job and can prove afterwards that it's done. Each line is a rule the clean data must obey, tested against the raw text.

SELECT 'Duplicate booking_id rows' AS problem, COUNT(*) - COUNT(DISTINCT booking_id) AS rows_affected FROM tembo.staging_bookings UNION ALL SELECT 'Names with stray spaces or odd casing', COUNT(*) FROM tembo.staging_bookings WHERE guest_name <> INITCAP(TRIM(guest_name)) UNION ALL SELECT 'Phones with dashes, +254 or padding', COUNT(*) FROM tembo.staging_bookings WHERE guest_phone !~ '^0[0-9]{9}$' UNION ALL SELECT 'Cities blank, misspelt or oddly cased', COUNT(*) FROM tembo.staging_bookings WHERE guest_city IS NULL OR guest_city !~ '^[A-Z][a-z]+$' OR guest_city = 'Thikax' UNION ALL SELECT 'Nationality casing', COUNT(*) FROM tembo.staging_bookings WHERE guest_nationality <> 'Kenyan' UNION ALL SELECT 'Room type aliases (Std, DLX, lower case)', COUNT(*) FROM tembo.staging_bookings WHERE room_type NOT IN ('Standard','Deluxe','Suite','Penthouse') UNION ALL SELECT 'Payment method aliases', COUNT(*) FROM tembo.staging_bookings WHERE payment_method NOT IN ('Cash','Card','Bank Transfer','M-Pesa') UNION ALL SELECT 'Booking status casing', COUNT(*) FROM tembo.staging_bookings WHERE booking_status NOT IN ('Checked Out','Cancelled','No Show') UNION ALL SELECT 'Dates not in YYYY-MM-DD', COUNT(*) FROM tembo.staging_bookings WHERE check_in_date !~ '^\d{4}-\d{2}-\d{2}$' OR check_out_date !~ '^\d{4}-\d{2}-\d{2}$' UNION ALL SELECT 'Check-out before check-in', COUNT(*) FROM tembo.staging_bookings WHERE check_in_date ~ '^\d{4}-\d{2}-\d{2}$' AND check_out_date ~ '^\d{4}-\d{2}-\d{2}$' AND check_out_date < check_in_date UNION ALL SELECT 'Nights stayed below 1', COUNT(*) FROM tembo.staging_bookings WHERE nights_stayed::INT < 1 UNION ALL SELECT 'Staff salary unreadable', COUNT(*) FROM tembo.staging_bookings WHERE staff_salary !~ '^\d+$' UNION ALL SELECT 'Total amount blank or not a plain number', COUNT(*) FROM tembo.staging_bookings WHERE total_amount IS NULL OR total_amount !~ '^\d+$' UNION ALL SELECT 'Rating padded or outside 1 to 5', COUNT(*) FROM tembo.staging_bookings WHERE guest_rating !~ '^[1-5]$'; 

Result grid: 14 rows fetched

Fourteen kinds of damage. (Missing phone numbers and ratings are not on the list, because a value that was never recorded is a gap, not a defect.) Some are cosmetic (casing), some silently change an answer (a duplicate booking counts twice), and some make an answer impossible (a total that is just a comma). The !~ operator means "does not match this regular expression", which is the fastest way to find everything that breaks a rule I can describe.

Step 3: Fix the data, one defect at a time

Every fix follows the same rhythm. Look at the problem, write the UPDATE, then run a check that proves it worked. I keep the WHERE clause on each update so the "Updated Rows" count tells me how many rows I really touched. An update with no WHERE clause always reports 285, which teaches you nothing.

3.1 Duplicate bookings

SELECT booking_id, guest_name, check_in_date, total_amount, COUNT(*) AS copies FROM tembo.staging_bookings GROUP BY booking_id, guest_name, check_in_date, total_amount HAVING COUNT(*) > 1; 

Result grid: 1 row fetched

Booking BK0006 appears twice, identical in every column. Revenue counted twice is the classic quiet error. In PostgreSQL every row has a hidden physical address called ctid. I keep the lowest address for each booking_id and delete the rest.

DELETE FROM tembo.staging_bookings WHERE ctid NOT IN ( SELECT MIN(ctid) FROM tembo.staging_bookings GROUP BY booking_id ); 

Statistics tab: Updated Rows = 1

SELECT COUNT(*) AS rows_left, COUNT(DISTINCT booking_id) AS unique_ids FROM tembo.staging_bookings; 

Result grid: 1 row fetched

285 rows and 285 unique IDs. (MIN(ctid) needs PostgreSQL 14 or newer. On older versions, use ROW_NUMBER() in a CTE.)

3.2 Guest names

The LENGTH column exposes what the eye can't see: trailing spaces.

SELECT guest_name, LENGTH(guest_name) AS chars, COUNT(*) AS rows_affected FROM tembo.staging_bookings WHERE guest_name <> INITCAP(TRIM(guest_name)) GROUP BY guest_name ORDER BY rows_affected DESC; 

Result grid: 5 rows fetched

INITCAP capitalises each word and lower-cases the rest. TRIM strips the spaces.

UPDATE tembo.staging_bookings SET guest_name = INITCAP(TRIM(guest_name)) WHERE guest_name <> INITCAP(TRIM(guest_name)); 

Statistics tab: Updated Rows = 45

The check is the same query as before. It should return nothing.

SELECT guest_name, LENGTH(guest_name) AS chars, COUNT(*) AS rows_affected FROM tembo.staging_bookings WHERE guest_name <> INITCAP(TRIM(guest_name)) GROUP BY guest_name; 

Result grid: 0 rows fetched

3.3 Phone numbers

QUOTE_LITERAL wraps each value in quotes, so blanks and padding become visible.

SELECT QUOTE_LITERAL(guest_phone) AS phone_as_typed, COUNT(*) AS rows_affected FROM tembo.staging_bookings WHERE guest_phone !~ '^0[0-9]{9}$' GROUP BY guest_phone ORDER BY rows_affected DESC; 

Result grid: 3 rows fetched

Three shapes: dashes, a +254 prefix and padding. The 14 blank phones don't appear, because COPY loads an empty CSV field as NULL. Some import tools load an empty string instead, so the CASE below turns those into NULL as well. The target is the local ten-digit form, 07xxxxxxxx: strip everything that isn't a digit, and turn a leading 254 into 0.

UPDATE tembo.staging_bookings SET guest_phone = CASE WHEN NULLIF(TRIM(guest_phone), '') IS NULL THEN NULL WHEN TRIM(guest_phone) LIKE '+254%' THEN '0' || SUBSTRING(REGEXP_REPLACE(guest_phone, '[^0-9]', '', 'g') FROM 4) ELSE REGEXP_REPLACE(guest_phone, '[^0-9]', '', 'g') END WHERE guest_phone !~ '^0[0-9]{9}$'; 

Statistics tab: Updated Rows = 29

SELECT COUNT(*) FILTER (WHERE guest_phone IS NULL) AS missing_phones, COUNT(*) FILTER (WHERE guest_phone !~ '^0[0-9]{9}$') AS malformed_phones FROM tembo.staging_bookings; 

Result grid: 1 row fetched

3.4 Cities

SELECT QUOTE_LITERAL(guest_city) AS city_as_typed, COUNT(*) AS bookings FROM tembo.staging_bookings GROUP BY guest_city ORDER BY bookings DESC; 

Result grid: 12 rows fetched

Nairobi has three spellings. Thikax is a typo for Thika, which is the only reading that fits: it appears exactly as often as the other small towns and no such place exists. That is an assumption, and I say so here so a reader can overrule it. Blank cities become Unknown rather than staying NULL, so the label is visible in every report and the Director can see how much is missing.

UPDATE tembo.staging_bookings SET guest_city = CASE WHEN NULLIF(TRIM(guest_city), '') IS NULL THEN 'Unknown' WHEN LOWER(TRIM(guest_city)) = 'thikax' THEN 'Thika' ELSE INITCAP(TRIM(guest_city)) END WHERE guest_city IS NULL OR guest_city !~ '^[A-Z][a-z]+$' OR guest_city = 'Thikax'; 

Statistics tab: Updated Rows = 45

SELECT guest_city, COUNT(*) AS bookings FROM tembo.staging_bookings GROUP BY guest_city ORDER BY bookings DESC; 

Result grid: 10 rows fetched

3.5 Short vocabularies: room type, payment, status, nationality

These four columns hold a handful of allowed values each, so one pattern fits all: map every known variant to one spelling. The room types hide the most, since Std and DLX are abbreviations that no casing function can repair.

UPDATE tembo.staging_bookings SET room_type = CASE WHEN UPPER(TRIM(room_type)) IN ('STANDARD', 'STD') THEN 'Standard' WHEN UPPER(TRIM(room_type)) IN ('DELUXE', 'DLX') THEN 'Deluxe' WHEN UPPER(TRIM(room_type)) = 'SUITE' THEN 'Suite' WHEN UPPER(TRIM(room_type)) = 'PENTHOUSE' THEN 'Penthouse' ELSE room_type END WHERE room_type NOT IN ('Standard', 'Deluxe', 'Suite', 'Penthouse'); 

Statistics tab: Updated Rows = 29

UPDATE tembo.staging_bookings SET payment_method = 'M-Pesa' WHERE UPPER(TRIM(payment_method)) IN ('MPESA', 'M PESA') AND payment_method <> 'M-Pesa'; 

Statistics tab: Updated Rows = 15

UPDATE tembo.staging_bookings SET booking_status = INITCAP(TRIM(booking_status)) WHERE booking_status <> INITCAP(TRIM(booking_status)); 

Statistics tab: Updated Rows = 15

UPDATE tembo.staging_bookings SET guest_nationality = INITCAP(TRIM(guest_nationality)) WHERE guest_nationality <> INITCAP(TRIM(guest_nationality)); 

Statistics tab: Updated Rows = 1

One query verifies all four columns at once:

SELECT 'room_type' AS column_name, room_type AS value, COUNT(*) AS bookings FROM tembo.staging_bookings GROUP BY room_type UNION ALL SELECT 'payment_method', payment_method, COUNT(*) FROM tembo.staging_bookings GROUP BY payment_method UNION ALL SELECT 'booking_status', booking_status, COUNT(*) FROM tembo.staging_bookings GROUP BY booking_status UNION ALL SELECT 'guest_nationality', guest_nationality, COUNT(*) FROM tembo.staging_bookings GROUP BY guest_nationality ORDER BY column_name, bookings DESC; 

Result grid: 12 rows fetched

3.6 Dates, the hard one

Dates need the most care, because a wrong guess is invisible. 05/03/2024 is either 5 March or 3 May, and nothing in the text says which. First, sort the check-in dates by shape:

SELECT CASE WHEN check_in_date ~ '^\d{4}-\d{2}-\d{2}$' THEN 'YYYY-MM-DD' WHEN check_in_date ~ '^\d{2}/\d{2}/\d{4}$' THEN 'NN/NN/YYYY' WHEN check_in_date ~ '^\d{2}-\d{2}-\d{2}$' THEN 'NN-NN-YY' WHEN check_in_date ~ '^\d{2}-\d{2}-\d{4}$' THEN 'NN-NN-YYYY' Source：https://dev.to/david_mwandairo/cleaning-and-analyzing-tembo-hotels-bookings-41o5
My favorites

Related posts

FiveM Script Database Migrations: What to Check Before You Install

Most FiveM script problems that show up a week after the install are not script bugs. They are database problems, and th

0dev.to

The insurance edge case that breaks event-driven policy pipelines

If you're moving insurance policy data from a nightly batch to an event stream, the broker is the easy part. The hard pa

0dev.to

The fix for an empty state isn't filling it

Last week I posted on Indie Hackers about a decision in my renovation cost tool: it ships with an empty price book. The

0dev.to UX

Five Things That Make Dependabot Painful for Terraform

A Terraform block in dependabot.yml looks identical to an npm or uv one: - package-ecosystem : terraform directory : /te

0dev.to GitHub

Tracking Roblox player counts: what the public data actually gives you

Roblox does not publish a public API for per-game concurrent player counts, so anything you see is a sample. I have been

0dev.to Data

Telegram gold signals this week: results to Oct 30

From October 24 to October 30, we executed 3,629 signals from 405 Telegram gold channels at the broker. More channels ma

0dev.to Data