Data sources¶

Exercise data-sources-annex.pdf · PDF

Anscombe's four datasets arrived on your screen ready to use: clean, tiny, already in a table. Somebody handed them to you.

Nobody will do that at work. Your boss will say "the sales are in the database", or forward you a spreadsheet, or point at a website. The data exist. They are just somewhere else, in some shape, behind some door. Before you can look at data, you have to go and get them.

That is stage 1 of the data lifecycle: obtain. In the crafting game, it is farming and looting. And every player knows the first question of looting: where is the loot, and what do I need to get in?

Three shapes of data¶

A data source is any place data come from: a database, a file, a web service.

Before asking where the data are, ask what shape they have. Here are the same two points of Anscombe's set I, stored three ways:

dataset,x,y
I,10,8.04
I,8,6.95

[
  {"dataset": "I", "x": 10, "y": 8.04},
  {"dataset": "I", "x": 8, "y": 6.95}
]

SELECT * FROM anscombe
WHERE dataset = 'I'
LIMIT 2;

Same data, three wrappers. The wrappers differ in how strict they are about shape:

Why is JSON only semi-structured? Because a record may have a field that the next one lacks, and nobody complains:

[
  {"pokemon": "bulbasaur", "type_1": "grass", "type_2": "poison"},
  {"pokemon": "charmander", "type_1": "fire"}
]

In a table, the second row would still need something in the type_2 column. In JSON, the label is simply not there.

Charts need rows and columns. So this course works with structured data, and when a source hands you something else, part of the job is turning it into a table first.

Six places where data live¶

Now the looting map. These are the six kinds of source you will meet in this module and in almost any job. The last column is the one that matters most.

Six new terms in one table. One at a time:

• A relational database stores data in tables that can be linked to each other. It runs as a program on a server, such as PostgreSQL or MySQL, and you talk to it in a language called SQL. You will write your first SQL on the next page.
• The host, the port and the driver are the address, the door and the translator you need to reach a database. They get a full page: Connecting Superset to a database.
• An API (application programming interface) is a web address that answers with data instead of a web page. Your weather app does not store the forecast: it asks an API.
• An API key is a password for programs: a long code the service gives you so it knows who is asking.
• A rate limit is the maximum number of questions an API accepts in a given time. Ask too often and it stops answering for a while.
• A licence is the text that says what you are allowed to do with data somebody else published.

Who asks for credentials?¶

Credentials are what you show to prove who you are: usually a user name and a password, or an API key.

flowchart LR
    Q{"Does the source<br>ask who you are?"}
    Q -- "always" --> DB["<b>Relational database</b><br>user + password"]
    Q -- "usually" --> API["<b>API</b><br>API key"]
    Q -- "never" --> F["<b>Files</b><br>CSV, Excel, JSON:<br>if you have the file, you are in"]
    Q -- "never" --> OD["<b>Open-data portal</b><br>public on purpose"]

A database always asks. An API usually asks. A file never asks: whoever holds the file holds the data. That is convenient, and it is also why you do not email a file with customers' personal details as casually as a meme.

CSV is not Excel¶

Both open in a spreadsheet program with a double click. That is where the confusion starts.

A CSV file (comma-separated values) is a table written as plain text: one line per row, with a character between one value and the next.

That character is the separator (also called the delimiter). The first line usually holds the column names: it is the header. This is the real beginning of pokemon_df.csv, the dataset we use in this unit, shortened to its first eight columns:

id,pokemon,species_id,height,weight,base_experience,type_1,type_2
1,bulbasaur,1,0.7,6.9,64,grass,poison
2,ivysaur,2,1,13,142,grass,poison

Nothing hidden. What you see is the whole file.

An Excel file is a different animal:

The separator trap¶

The "C" in CSV promises commas. It does not always keep the promise.

In Spain, as in much of Europe, the decimal mark is a comma: we write 6,9 kg. If the separator were also a comma, 6,9 would look like two values. So files made in these countries often use a semicolon as separator instead:

pokemon,height,weight
bulbasaur,0.7,6.9

pokemon;height;weight
bulbasaur;0,7;6,9

Both are valid CSV files. A tool that expects one and receives the other will read every line as one single column, or split your decimals in two. The file is fine. The tool was told the wrong thing.

There is a second hidden setting: the encoding, the rule that turns letters into the bytes stored in the file. When a tool guesses it wrong, Calvià shows up as CalviÃ. If you see that, the data are not broken: the encoding is wrong.

So, one habit for life: before you load a file anywhere, open it in a text editor and look at the first lines. You are checking the separator, the decimal mark and the header. Mismatched formats are a UT2 problem, but you spot them today.

Open data: free, not lawless¶

Open data are data that an organisation publishes so that anyone can download, reuse and share them.

Governments publish open data: budgets, bus lines, beaches, school enrolment. The data community does too. Two sources you will use this year:

Our Pokémon dataset comes from TidyTuesday. More sources, with direct links to every class file, are on the Resources page.

Meet Apache Superset¶

Six kinds of source, three shapes, separators, credentials. You need a tool that can open those doors and show you what is behind them.

Apache Superset is a web application for exploring data: it connects to databases, lets you ask them questions in SQL, and turns the answers into charts and dashboards.

In the game, Superset is the client: the window you look through. Remember that for the next page, because it has a consequence: Superset does not store your data. It connects to the database where they live, asks, and draws.

flowchart LR
    C["<b>Connect</b><br>to a database"] --> Q["<b>Query</b><br>ask in SQL"]
    Q --> V["<b>Chart</b><br>draw the answer"]
    V --> D["<b>Dashboard</b><br>several charts<br>on one screen"]

Its story is short. In 2015, at a hackathon (an event where programmers build a prototype in a few days) inside Airbnb, an engineer called Maxime Beauchemin built a tool to slice and chart the company's data quickly. He had already created another well-known data tool, Apache Airflow. Airbnb released the new tool as open source: its code is public, and anyone may use it, study it and improve it for free.

It is the main tool of this module. Near the end of this unit you will also meet its best-known commercial rival, Microsoft's Power BI, and compare how the two get their data.

Try it: an open-data hunt¶

You need a browser and a text editor. No account, no password.

Now do the same with a dataset you choose, from the GOIB portal or from TidyTuesday, and fill in this card:

Keep that last line. A good question is the start of every chart you will draw this year.

Key takeaways¶

• Data come in three shapes: structured (fixed rows and columns), semi-structured (labelled, flexible, like JSON) and unstructured (no fields, like a photo).
• Six kinds of data source: relational database, CSV, Excel, JSON, API and open-data portal. For each one, ask: what do I need to get in?
• Credentials: a database always asks for them, an API usually does, a file never does.
• A CSV is plain text with a separator; an Excel file is a ZIP package of XML. Open files in a text editor before loading them, and check separator, decimal mark and header.
• Open data are free to reuse, under a licence. With CC BY, you must credit the source.
• Apache Superset connects to databases, queries them in SQL and draws charts and dashboards. It began at Airbnb in 2015 and has been a top-level Apache project since 2021.

Quick check¶

You know where data live and what each place asks for. Time to open the hardest door of the six, the database: Connecting Superset to a database.