Lesson 4 of 10 · Lab
Clean up inconsistent text in a CSV
Cleanse fixes how text is written without changing what it means. It trims stray spaces and makes case consistent, either across every column or for one column at a time.
To a computer, completed, Completed and completed with a trailing space are three values. A count by status splits one group three ways, and a filter on one spelling misses the others.
At fictional Kramerica Bikes, Harbour's staff can edit a booking at the till, and some retype the status or bike code as they go. This live flow in Strata, Oplema's browser app for data prep, starts with the six columns you kept in the last lesson.
Cleanse can make every text column lower case at once. Why not do that here?
booking_id would turn from KB-10001 into kb-10001 and stop matching other files, and bike codes are written in capitals. Each column needs its own rule.
One spelling per status and bike code
- Summarize opens first.
statusescounts 12 spellings of what should be three statuses, andcodescounts 15 for four bike codes. - Select Cleanse and select Trim Whitespace.
- Select Summarize. Spellings that differed only by a space have merged:
statusesandcodesboth read 8. - Select Cleanse. Open Per-Column Overrides, select the arrow at the right of
status, scroll down and set its Modify Case to Lower. - Do the same for
bike_code, with Modify Case set to Upper. - Select Summarize.
statusesreads 3,codesreads 4 androwsis still 321.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
What you should see
Three statuses: 286 completed, 29 cancelled and 6 no-show. Four codes: CTY, EBK, KID and TDM. Still 321 rows, because cleaning changes values, never how many rows there are.
Cleaning keeps every row
Trim applies to every text column, because a stray space never means anything. Case applies only to the columns you name, because in an ID it can matter. Both rules sit in one block, where anyone reading the flow can see them.
Cleanse can't tell you what a value should be. It merged Completed into completed only because you said case doesn't matter in status. Next, the amounts and dates, which are still text.
See the Cleanse reference. For later: Null vs blank vs zero covers missing values, the other half of cleaning.
Common text-cleaning mistakes
- Lowercasing every column. IDs and codes can depend on case, so set case per column.
- Fixing spellings by hand in a spreadsheet. Next week's file arrives with the same mess, and a hand fix doesn't come with it. A block does.
- Checking by eye. A trailing space is invisible in a preview. Count the distinct values, as Summarize does here.
TRIM and LOWER in SQL and Excel
SELECT count(DISTINCT LOWER(TRIM(status))) AS statuses,
count(DISTINCT UPPER(TRIM(bike_code))) AS codes
FROM harbour;SQL cleans inside the query, so every query has to repeat it. In Excel, =LOWER(TRIM(A2)) in a helper column does the same, and =UNIQUE() on the result lists the spellings that are left.