Lesson 8 of 8 · Lab

More data tools: when you'd use each one

The courses taught tools as the job needed them. These twelve didn't come up. Each example below runs on a small table, so you can see what the tool does and recognize the job when it arrives.

Each lab below is a live flow in Strata, Oplema's browser app for data prep. Select a block to see its settings and its output.

The first lab branches September's Harbour hires, 428 rows in a Manual Input, a table typed into the flow, into seven tools at once.

Shape a table

  1. Record ID adds line, numbered 1 to 428: a key for a file that has none.
  2. Sample keeps 50 random rows, the same 50 each time because Seed is fixed. Use it to pick rows to check by hand.
  3. Split sends 343 rows to Train and 85 to Test, with Train % at 80: a holdout set for testing a model. Its Validation output stays empty, because Validation % is 0.
  4. Bin puts amount_eur into four bands of about 106 hires each. The 5 staff hires with no amount stay unbanded.
  5. Encode makes one column per bike code, 1 or 0, for tools that need numbers instead of labels.
  6. Scale rescales bikes to run from 0 to 1, so columns on different scales can be compared.
  7. Split to columns cuts booking_id at the dash, and Auto Field turns the number part from text into BIGINT. Use it when numbers arrive as text, so they sort and compare as numbers.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

Fill gaps and spread rows

Two small tables: a touring shop's five multi-day hires with two missing distances, and a week of wind readings with two gaps.

  1. Impute fills the two blank ride_km with the median, 42, and flags them. Use it when a value is missing by accident. Leave a null that's correct, like a staff hire's empty price.
  2. Expand Rows turns each hire's from_on and to_on into one row per day: 12 rows from 5 hires.
  3. Interpolation fills each wind gap from its neighbors in order: 26 and 20. Sort first, because it reads rows as they arrive.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

Find and combine

  1. Join Multiple joins September's hires, the fleet and the hires budget on shop in one step: 3 rows, one per shop. Use it for three or more tables that share a key name.
  2. Fuzzy Search ranks the eight suppliers by how closely they match brake, and only Ostend Brake Co gets a score. The score ranks matches and isn't a percentage, so it can pass 100. Use it to find a row by typing part of a name.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

Connectors

Connectors read straight from services such as Google Sheets, QuickBooks or a database, instead of an exported file. No lesson uses one, because each needs your own account with that service. The connector guides cover each one, starting from Input's Connectors source.

Course complete Now use your own file.

A published dashboard for fictional Kramerica Bikes that someone else can rerun, then a blank-canvas project on a business you haven't seen.