Data wrangling

In tap SQL is how you can manipulate the data. In this tutorial, we'll explore some common data wrangling tasks you may want to perform: cleaning a messy dataset, joining it with another dataset, and then enriching it with H3 geospatial indexing to enable performant geospatial filtering.

Get set up

open tap and sign in or create your (free) account. This could take a minute to set up your storage bucket and domain if it's your first time signing in.

Next, download the required datasets:

• Hospitals in Scotland
• ONS Postcode Lookup

Add data

After signing in you'll be on the Data page. Click Create data + to open the data creation form.

Data are the "sources" for all data processing in tap. You can upload any CSV, JSON(L), Parquet, XLSX, or ODS document. JSON documents will be converted into a single-line JSONL file, and XLSX and ODS documents will be extracted into separate CSV sheets.

Note: already have a bunch of data in object storage? Tap works with any S3-compatible object store so talk to us about bringing your own data lake.

Let's call the data:

• scotland_hospitals
• ons_postcode_lookup

For each, click Upload file and select the downloaded CSV. Once the upload completes, tap will automatically try to preview the file. For now, click Save to import the selected data.

Once saved, you'll see tap "materialising" the data. This creates an optimised copy of the data in Parquet format to accelerate queries. Materialisation should only take a few seconds.

Once you've created both data, navigate to the Models page. This is where we will do all our data wrangling.

Clean it

Notice the incosistent Postcode spacing and extra whitespace around AddressLine1 in the scotland_hospitals data:

We can clean it with the following query:

SELECT
  regexp_replace("Postcode", '\s*(.3$)', ' \1', 'g') AS "Postcode",
  trim("AddressLine1") AS "AddressLine1",
  trim("AddressLine2") AS "AddressLine2"
FROM data.scotland_hospitalsCopy

Let's save this model with the name scotland_hospitals_cleaned. Our data should no look like this:

Join it

We can join it with the ons_postcode_lookupdata to map postcodes to latitude and longitude with the following query in a new model:

SELECT model.example_cleaned.*,
  data.ons_postcode_lookup.latitude AS "Latitude",
  data.ons_postcode_lookup.longitude AS "Longitude"
FROM model.scotland_hospitals_cleaned
JOIN data.ons_postcode_lookup ON "Postcode" = postcodeCopy

Let's save this model with the name scotland_hospitals_with_lat_long. Our data should no look like this:

Enrich it

To support performant geospatial querying through the H3 filter we will add H3 indexing with the use of our Latitude and Longitudecolumns.

SELECT
  model.scotland_hospitals_with_lat_long.*,
  h3_latlng_to_cell("Latitude", "Longitude", 15::INT) AS "H3Res15"
FROM model.scotland_hospitals_with_lat_longCopy

Let's save this model with the name scotland_hospitals_enriched. Our data should no look like this:

That's it!

You can then serve it via a Data API and start building data-centric applications right away! Or, just download the data in a CSV, Parquet or JSONL format for further analysis.