From Pivot Tables to Tidy Data

BUS220 - Business Intelligence and Analytics | Week 3

Oleh Omelchenko

2026-03-25

Data has shape

After the quiz, transition: “Last week you built pivot tables and charts. This week we ask: what was that pivot table actually doing to your data? And what happens when a tool can do it for you automatically?”

What did the pivot table do?

You had 10,000 order rows. To make a multi-series line chart, you built a pivot table.

Before (long):

Quarter	Region	Sales
2014-Q1	Central	$8,601
2014-Q1	East	$12,340
2014-Q1	South	$6,209
2014-Q1	West	$9,734
2014-Q2	Central	$11,082
…	…	…

⟶

After (wide):

Quarter	Central	East	South	West
2014-Q1	$8,601	$12,340	$6,209	$9,734
2014-Q2	$11,082	…	…	…
…	…	…	…	…

Quick nod to last week, not an extended callback. Just show the two shapes side by side.

“The pivot table took values from the Region column and spread them across column headers. Google Sheets needed this because its charting engine reads each column as a separate series — one column, one line.”

Don’t linger. Spend 1-2 minutes here, then move to the reframe.

The pivot table is a data reshaping operation

The pivot table changed your data’s shape.

Values from the Region column became column headers.

This shape is called wide format: compact, human-readable, one row per time period.

The original shape is called long format: one observation per row, each variable in its own column.

“There’s nothing wrong with wide format. It’s how humans like to read data — reports, crosstabs, summary tables are all wide. You’ve been using it successfully for two weeks.”

“But there’s a reason your slicer couldn’t filter the line chart last week: once you go wide, tools that work row-by-row can’t reach inside those columns. The pivot table was a frozen summary.”

Where does this idea come from?

In 2014, Hadley Wickham — a New Zealand statistician at Rice University — published a paper formalizing something practitioners had been doing informally.

He noticed every analysis project started with the same struggle: reshaping data before the actual analysis could begin. The target shape was always the same.

He called it tidy data. Three rules:

1. Each variable is a column
2. Each observation is a row
3. Each type of observational unit is a table

Wickham’s “Tidy Data” paper (2014)

“That paper has been cited over 10,000 times. It’s one of the most influential papers in applied statistics. And the dataset you’ll clean in practice today — the Billboard Hot 100 — is literally Table 5 from Wickham’s paper. You’re doing the transformation he described.”

Wickham translated a 1970 idea

In 1970, Edgar Codd published the rules of relational database design — including third normal form: store each fact once, in the right place.

Wickham references Codd directly in his paper. Tidy data is third normal form, repackaged for people who work in R and spreadsheets instead of databases.

Community	They call it
Database engineers	Third normal form
Statisticians	Tidy data
BI practitioners	Star schema

Wickham’s contribution was making the idea accessible outside the database world.

“This is a 1-minute aside, not a history lecture. Wickham says so himself in the paper — tidy data is Codd’s third normal form, restated for statisticians. The BI world calls the same pattern a star schema. The point: this isn’t a quirky Tableau requirement. It’s a 55-year-old principle for organizing data. Wickham just gave it a name that stuck outside the database community.”

You’ve already seen a star schema

The Superstore spreadsheet had three sheets, connected by lookups.

erDiagram
    Orders ||--o{ People : "Region"
    Orders ||--o{ Returns : "Order ID"

    Orders {
        string Order_ID
        date Order_Date
        string Region
        string Product
        float Sales
        float Profit
        float Discount
        int Quantity
    }
    People {
        string Region
        string Manager
    }
    Returns {
        string Order_ID
        string Returned
    }

Star schema = one central fact table (the transactions, the measurements) surrounded by smaller dimension tables (the context: who, where, what category).

The XLOOKUP you wrote in week 1 was a manual join between a fact table and a dimension table.

“You’ve already built this structure without knowing the name. The Orders sheet is the fact table — it has the measurements (Sales, Profit, Quantity) and the events (each order line). The People sheet and Returns sheet are dimension tables — they add context to the facts.”

“The XLOOKUP you used to bring Regional Manager into the Orders sheet? That’s a join between a fact table and a dimension table. In Tableau, you’ll do this with a visual drag-and-drop join next week.”

“Star schema is a formal data modeling pattern used in data warehouses and BI platforms. You don’t need to design star schemas in this course, but knowing the vocabulary helps when you work with data engineers or read documentation. The core idea: keep the big table of events in the center, put the lookup context around it, and connect them by shared keys.”

Keep this to 2 minutes. The point is vocabulary + recognition, not a deep dive into data modeling theory.

Exercise: which shape is this?

Quick interactive exercise. Show each table, students vote: wide or long?

Spend about 30-45 seconds per example. Use hand-raising or a quick verbal poll. The goal is to make the abstract concept mechanical before they encounter it in practice.

A grade book

Student	HW1	HW2	Midterm	Final
Anna	85	92	78	88
Boris	91	87	94	90
Clara	73	80	85	82

Wide or long?

Wide. “Assignment” is a variable stuffed into column headers. To use this in Tableau, you’d unpivot HW1/HW2/Midterm/Final into an “Assignment” column and a “Score” column.

Students should recognize this immediately — it’s exactly like a spreadsheet they’d use.

“What if you wanted to color by assignment type in Tableau? You’d need ‘Assignment’ as a column with values, not spread across headers.”

A bank transaction log

Date	Account	Amount	Type
Mar 1	Checking	-45.00	Purchase
Mar 1	Checking	2,500.00	Salary
Mar 2	Savings	500.00	Transfer

Wide or long?

Long (tidy). Each transaction is one row. Each variable is one column.

“If you can look at a dataset and immediately say ‘this is wide, I’d need to unpivot columns X through Y’ — you have the most important skill for this week.”

Transition: “So we know what shape the data needs to be in. The next question: how do we get it there? Real data doesn’t arrive tidy.”

Why tools matter

What happens when spreadsheets aren’t enough.

“You’ve been cleaning data in spreadsheets — TRIM, LOWER, Find and Replace. That works for small datasets. But spreadsheets have problems that become dangerous as your data gets more important.”

Excel renames your genes

Genomics researchers work with gene names like SEPT1 (Septin 1), MARCH1 (Membrane Associated Ring-CH-Type Finger 1), DEC1 (Deleted in Esophageal Cancer 1).

Open a CSV with these names in Excel:

• SEPT1 → September 1
• MARCH1 → March 1
• DEC1 → December 1

No warning. No error message. Silent data corruption.

Excel silently converts gene names to dates

“Excel sees text that looks like a date and silently converts it. No dialog box, no error — it just rewrites your data.”

“This isn’t a hypothetical. Let it sink in for a moment.”

20% of genomics papers had corrupted gene names

A 2016 study by Ziemann, Eren, and El-Osta screened 3,597 published papers with supplementary Excel files.

704 papers — roughly one in five — contained gene names that had been silently converted to dates or floating-point numbers by Excel.

In 2020, the Human Gene Nomenclature Committee gave up on fixing the software. They renamed 27 human genes instead.

SEPT1 → SEPTIN1. MARCH1 → MARCHF1.

They renamed parts of the human genome because they couldn’t get scientists to stop opening CSVs in Excel.

“This is a data type problem. Excel guessed that SEPT1 was a date because it didn’t have enforced column types. A tool where you explicitly declare ‘this column is text, not a date’ wouldn’t allow that conversion.”

“When we use Syto in a few minutes, you’ll see explicit type assignment. That’s the thing that prevents this.”

Invisible formulas, real consequences

In 2010, Harvard economists Reinhart and Rogoff published a paper arguing that countries with debt above 90% of GDP experience negative growth.

The paper was cited in Congressional hearings, European Parliament debates, and IMF policy documents to justify austerity measures.

Three years later, a graduate student found a SUM formula that excluded five rows — Belgium, Austria, Australia, Canada, and Denmark were missing from the average.

With all countries included, the result flipped: slow but positive growth.

Reinhart-Rogoff spreadsheet error

“One wrong cell range. Five rows excluded from a simple average. Policy affecting millions of people. And nobody caught it for three years because spreadsheet formulas are invisible — you see the result, not the logic.”

The corrected results

Reinhart-Rogoff: original vs. corrected GDP growth by debt level

The “above 90%” group changed the most: from −0.1% to +2.2% average growth. The formula error didn’t just nudge the number — it flipped the sign.

“Look at the last group — above 90% debt-to-GDP. The original paper said negative growth. The corrected version says positive growth. That sign flip was the entire basis for the austerity argument.”

“Both problems — the gene names and this — share a root cause: spreadsheets mix data, logic, and presentation in the same artifact. The formula is hidden behind the cell. The type conversion is silent. There’s no record of what transformations happened in what order.”

Transition: “But tool problems are only half the story. Even if your tool is perfect, the data itself arrives broken.”

But tools aren’t the only problem

Even if your tool is perfect, the data itself arrives broken.

“Those were tool problems — Excel converting types, a formula with the wrong range. But most data quality issues live inside the data itself, before you open any tool.”

“I’m going to show you a small dataset. Take 30 seconds and tell me: what’s wrong with it?”

What problems can you spot?

id	date	city	category	amount	notes
1	2025-03-01	Kyiv	Electronics	1200
2	03/04/2025	Kiev	electronics	$800	returned
3	2025-03-01	Kyiv	Electronics	1200
4	2025-03-12		Clothing	0
5	01/04/2025	kyiv	Other	350
6	2025-03-20	Київ	Electronics	2,100

Put this on screen. Give students 30 seconds, then start collecting answers. Use hand-raising or just call on people. Don’t reveal anything yet — just collect observations.

Typical student responses: “rows 1 and 3 are the same,” “the dates look different,” “Kyiv is spelled differently,” “there’s a blank city,” “the amounts look inconsistent.” Some will catch the subtle ones, some won’t. That’s fine — the next slides walk through each problem.

“Let’s go through them one by one.”

Duplicated rows

id	date	city	category	amount	notes
1	2025-03-01	Kyiv	Electronics	1200
2	03/04/2025	Kiev	electronics	$800	returned
3	2025-03-01	Kyiv	Electronics	1200
4	2025-03-12		Clothing	0
5	01/04/2025	kyiv	Other	350
6	2025-03-20	Київ	Electronics	2,100

Rows 1 and 3 are identical. Is this a real duplicate, or two separate transactions that happen to match?

If you sum the amount column, you get an extra 1,200 — or you lose a real sale.

“Duplicates are tricky because the right action depends on context. In campaign finance data, the Quartz guide notes that ‘amendments’ reuse the same unique identifiers as the original transaction. If you don’t know that, every calculation is wrong.”

“The question is always: should I deduplicate, or are these genuinely separate records? You need domain knowledge to decide.”

Inconsistent dates

id	date	city	category	amount	notes
1	2025-03-01	Kyiv	Electronics	1200
2	03/04/2025	Kiev	electronics	$800	returned
3	2025-03-01	Kyiv	Electronics	1200
4	2025-03-12		Clothing	0
5	01/04/2025	kyiv	Other	350
6	2025-03-20	Київ	Electronics	2,100

Two formats in one column. Rows 1, 3, 4, 6 use YYYY-MM-DD. Rows 2 and 5 use MM/DD/YYYY — or is it DD/MM/YYYY?

Is row 5 January 4th or April 1st? Without knowing who entered it, you can’t tell.

“The Quartz guide puts it simply: ‘Without knowing the history of the data you can’t know for sure.’ This is why ISO 8601 — YYYY-MM-DD — exists. It’s unambiguous everywhere. When you export clean data, always use it.”

“A tool like Syto will try to parse dates, and it will flag rows where it’s uncertain. That’s already better than a spreadsheet, which will silently guess.”

Inconsistent spelling

id	date	city	category	amount	notes
1	2025-03-01	Kyiv	Electronics	1200
2	03/04/2025	Kiev	electronics	$800	returned
3	2025-03-01	Kyiv	Electronics	1200
4	2025-03-12		Clothing	0
5	01/04/2025	kyiv	Other	350
6	2025-03-20	Київ	Electronics	2,100

Four spellings of the same city: Kyiv, Kiev, kyiv, Київ.

A GROUP BY city gives you four groups instead of one. Every aggregate — total, average, count — is silently wrong.

“This is one of the most reliable indicators that data were entered by hand. And it’s not just typos — it’s transliterations, encoding differences, case differences, and legitimate alternative names.”

“You need to decide: which spelling is canonical? That’s a judgment call, and you need to document it. In the Syto demo, you’ll see exactly this kind of fix — standardizing values in a column through a Replace step.”

Missing and disguised values

id	date	city	category	amount	notes
1	2025-03-01	Kyiv	Electronics	1200
2	03/04/2025	Kiev	electronics	$800	returned
3	2025-03-01	Kyiv	Electronics	1200
4	2025-03-12	(blank)	Clothing	0
5	01/04/2025	kyiv	Other	350
6	2025-03-20	Київ	Electronics	2,100

Row 4: city is blank — was it not recorded, or is this an online order with no city?

Row 4: amount is 0 — is this a free item, or does 0 mean “not yet invoiced”?

“Missing values are the most common data quality issue. A blank cell could mean ‘we don’t know,’ ‘not applicable,’ or ‘truly zero’ — and the dataset doesn’t tell you which.”

“The Quartz guide describes a real case: geographic coordinates of 0°N 0°E — a point in the Atlantic Ocean south of Ghana called ‘Null Island’ — showing up in thousands of records because a system wrote zero when it had no location data.”

“The rule: if you see a blank or a zero, ask yourself — do I know what the absence of this value means?”

Mixed types and formatting

id	date	city	category	amount	notes
1	2025-03-01	Kyiv	Electronics	1200
2	03/04/2025	Kiev	electronics	$800	returned
3	2025-03-01	Kyiv	Electronics	1200
4	2025-03-12		Clothing	0
5	01/04/2025	kyiv	Other	350
6	2025-03-20	Київ	Electronics	2,100

$800 and 2,100 are text strings, not numbers. A SUM formula skips them silently.

Electronics vs. electronics — same category or different? Other — what does that even mean?

“The amount column has three different formats: plain numbers (1200, 350, 0), a currency symbol ($800), and a thousands separator (2,100). A spreadsheet might treat some as numbers and others as text. A SUM could silently exclude the text values.”

“The category column has ‘Electronics’ with a capital E and ‘electronics’ with a lowercase e. To a computer, those are different values. And ‘Other’ is a category that hides information — the Quartz guide warns that ‘Other’ could mean anything from ‘unknown’ to ‘doesn’t fit our schema’ to ‘the person entering data gave up.’”

Six rows, six problems

Problem	Rows	Risk
Duplicated rows	1, 3	Inflated totals
Inconsistent dates	2, 5	Wrong time series
Inconsistent spelling	1, 2, 5, 6	Broken grouping
Missing values	4	Wrong aggregates
Numbers as text	2, 6	Silent calculation errors
Meaningless categories	5	Lost information

This was 6 rows. Real datasets have thousands.

Fixing these problems one cell at a time doesn’t scale. You need a process that’s systematic and reproducible.

“Every one of these problems is from the Quartz Guide to Bad Data — a reference by data journalists who’ve cataloged dozens of ways data goes wrong. It’s one of your readings this week.”

“At 6 rows, you can eyeball every issue. At 6,000 rows — which is roughly the size of the Billboard dataset you’ll clean today — you need tools that let you apply fixes across entire columns, document what you did, and re-run the process when new data arrives.”

“A dog licensing database in Cook County, Illinois had a free-text field for breed. It ended up with 250 different spellings of ‘Chihuahua.’ At that scale, manual cleanup is hopeless. You need a pipeline.”

Transition: “That’s what a data preparation tool gives you.”

Pipelines instead of spreadsheets

A data preparation tool separates data from logic. Every transformation is an explicit, named step.

import

→

filter

→

rename

→

convert types

→

derive

→

export

If step 3 is wrong, you fix step 3 — steps 4 through 8 rerun automatically.

If a column is typed as text, it stays text. No silent date conversions.

“In Sheets, if you do Find and Replace, it’s done — you can Ctrl+Z, but you can’t selectively undo it later while keeping everything after. In a pipeline tool, every transformation is a recorded step you can inspect, reorder, or remove.”

Transition to the live demo: “Let me show you what this looks like in practice.”

Every BI platform has a data prep tool

Platform	Data prep tool	Cost
Power BI	Power Query	Included with Power BI
Tableau	Tableau Prep	~$70/month (on top of Tableau Creator)
Python	pandas	Free, but you write code
Browser	Syto	Free

The concept is the same in all of them: a visual pipeline where each step is an explicit transformation you can inspect and reorder.

“Every serious BI platform ships with a data preparation tool. Power BI has Power Query built in — it’s one of Power BI’s strongest features. Tableau has Tableau Prep, which is excellent but costs extra on top of an already expensive Tableau license.”

“We’re using Tableau Public, which is free — but it doesn’t include Tableau Prep. So we need a free alternative that does the same job: import, reshape, clean, export.”

“The tool we’ll use is called Syto. It’s browser-based, free, no installation needed. It does the same pipeline operations — filter, replace, unpivot, aggregate, type conversion — through a visual interface.”

Syto

• Browser-based — no installation, works on any OS
• Visual pipeline — each transformation is a named step you can reorder, edit, or remove
• Exports clean CSV — which you then connect to Tableau

It’s free, it’s visual, and it teaches the same concepts you’d use in Power Query or Tableau Prep.

The skill transfers: once you understand pipeline thinking, switching tools is learning new syntax for the same operations.

Syto interface with pipeline steps

“I want to be honest about the trade-off here. If you had a Tableau Creator license, you’d use Tableau Prep — it’s more polished and integrates directly with Tableau. If you’re comfortable writing code, pandas in Python is more powerful and flexible. Syto is the tool that lets us do real data preparation work without a paid license and without writing code.”

“The mental model matters more than the specific tool. A pipeline of explicit, reversible, inspectable steps. That works the same in Syto, Power Query, Tableau Prep, or a Python notebook. The tool you use at your first job will probably be different, but the thinking will be identical.”

Transition: “Let me show you what this looks like.”

Live demo: cleaning data in Syto

Switch to Syto in the browser. Use the ihtm_2025.xlsx dataset (Oxford IHTM survey, 18 rows). The dataset is intentionally small so every issue is visible without scrolling.

Walk through the cleaning pipeline step by step. At each step, ask: “What do you notice?”

1. Import the Excel file. Inspect: spot empty rows, mixed types, inconsistent values.
2. Filter: remove rows where all fields (except ID) are null.
3. Replace: standardize capitalization in favourite_colour (“Navy blue” → “navy blue”).
4. Replace: fix nationality inconsistencies (“Vietnam” → “Vietnamese”).
5. Types: convert number_siblings to integer — this catches “one” as an error. “Notice: Syto flagged this instead of silently converting it. Compare that to the gene name story.”
6. Fix “one” → “1”, re-apply type.
7. Export as clean CSV.

Key emphasis: every step is visible in Syto’s pipeline panel. You can reorder steps, remove one, see how it changes the output.

Keep it conversational. The dataset is tiny, so spend time on the why behind each decision rather than tool mechanics. This is most students’ first time seeing Syto — let them absorb the interface.

Target: ~10 minutes for the live demo portion.

Tableau’s data model

How Tableau understands your data — the layer underneath the charts.

Transition: “Now you’ve seen how to get from messy to clean. The next question: how does Tableau understand the clean data you give it?”

“Some of you have used Tableau before in data viz. What we’re covering now is the data model — the layer that determines whether your chart shows something meaningful or garbage. This is the part that STAT230 didn’t cover.”

Live demo: connecting data in Tableau

Open Tableau Public. Connect to the pre-built billboard-long.csv.

Once connected, look at the left panel — the Data pane. Tableau has already sorted fields into Dimensions (above the line) and Measures (below the line).

“Tableau splits every field into one of two types. Let’s look at what it guessed and whether it got them right.”

Dimensions vs. measures

[Screenshot: Tableau Data pane for billboard-long.csv — Dimensions section (artist, genre, track, date_entered) above the line, Measures section (rank, week) below the line]

Dimensions answer what

• What artist?
• What genre?
• What date?

Drag to Rows → one row per unique value

Measures answer how much

• What rank?
• How many weeks?
• What sales amount?

Drag to Columns → Tableau aggregates (sums, averages, counts)

“When you connect a CSV, Tableau guesses: text and dates become dimensions, numbers become measures. It’s usually right — but not always.”

Back in the live demo: point at the fields.

“Look: week is under Measures. Is week a quantity you’d sum? No — it identifies which week of the chart run we’re looking at. Let’s drag it to Dimensions.”

“Rank is also under Measures. For most charts, that’s actually correct — we want to plot rank values. But keep in mind: summing ranks is meaningless. We’ll see why in a moment.”

“A zip code is a number but it’s a dimension. A year is a number but it’s a dimension. The question is always: does this identify something (dimension) or quantify something (measure)?”

Discrete vs. continuous

Blue pill
Discrete — separate buckets
Tableau draws a header for each value
Good for: categories, individual years, distinct groups

Green pill
Continuous — an unbroken range
Tableau draws an axis
Good for: time series, numeric ranges, quantities

[Screenshot: Same date field shown two ways — top: discrete YEAR(date_entered) with separate column headers “1999” “2000”; bottom: continuous date_entered as a time axis flowing left to right]

The shortcut: blue = headers, green = axis. The same field can be either — you choose.

Back in the live demo: right-click chart_date.

Show it as discrete YEAR: separate column headers appear for 1999, 2000.

Show it as continuous: a time axis flows left to right.

“For a timeline, you usually want continuous — a flowing axis. For comparing categories, you usually want discrete — separate headers.”

“Most beginners struggle with this distinction. The shortcut is simple: blue = headers, green = axis.”

The Marks card: pre-attentive attributes in Tableau

[Screenshot: Tableau Marks card showing Color, Size, Shape, Detail, Tooltip]

Pre-attentive attributes — color, size, shape — are the channels the brain processes fastest.

In Tableau, these live on the Marks card. Drag a field to Color, Size, or Shape to encode data through that visual channel.

“The Marks card is where you choose pre-attentive attributes in Tableau. Drag a field to Color and you’re using hue. Drag to Size and you’re using area.”

“The important one for this week is Color. When you drag a field like Track to Color, Tableau draws one line per unique value — each in a different hue. This is the automatic pivot in action: Tableau reads values from the column and creates separate visual series on the fly.”

Live demo: the automatic pivot in action

Back in Tableau. Build step by step:

1. Drag week (continuous) to Columns.
2. Drag rank to Rows.
3. Right now: a single aggregated line. “Tableau summed all ranks for each week. One mark per week. Not useful.”
4. Drag track to Detail on the Marks card. Suddenly: hundreds of individual song lines appear.
5. “What just happened? By adding track as a dimension, we changed the granularity — from one mark per week to one mark per song per week. Tableau went from aggregating everything together to splitting by song.”
6. Filter to 5-6 recognizable songs (Destiny’s Child, Santana, 3 Doors Down, Madonna, Creed).
7. Move track from Detail to Color. Each song gets a distinct color.
8. Reverse the Y-axis (right-click → Edit Axis → Reversed) so #1 is at top.

“In Google Sheets, to get this chart you needed: a pivot table with each song as a column, then build the chart from that wide table. Here, the data stayed in its tidy form — one row per song-week — and Tableau split it visually through the Color shelf.”

This is the demo’s payoff moment. Let it land.

What’s ahead in practice

Quick preview — 2-3 minutes max.

Practice this week

P1 — Data wrangling in Syto

Take the Billboard Hot 100 dataset: 317 songs, 76 weeks of rankings, crammed into 79 columns. Unpivot the 76 week columns into rows, clean the results, export two files at two granularity levels.

This is literally Table 5 from Wickham’s tidy data paper.

P2 — First steps in Tableau

Connect both cleaned files. Build worksheets from each — line charts from the song-week data, bar charts and histograms from the song-level data.

[Screenshot: The raw billboard-raw.csv opened in a spreadsheet — showing the 79-column structure with x1st.week through x76th.week columns stretching off-screen to the right. The “this is untidy” moment.]

“By the end of practice, you’ll have a Tableau workbook with 4+ worksheets — all from a dataset that started as an unreadable 79-column mess.”

Logistics: - Remind about Assignment 2 deadline. - Starting this week, quiz questions include material from the assigned readings, not just lectures.

Next week preview: “Business metrics and KPIs. Calculated fields, joins, and your first interactive dashboard. The granularity concept from today becomes critical — when you join two tables, you need to know what level each is at.”

Three ideas to take with you

Data has shape. Wide and long are two layouts for the same information. Pivot goes long → wide. Unpivot goes wide → long.

Tableau pivots for you. Give it tidy data and it handles grouping, aggregation, and visual separation automatically.

Granularity determines meaning. The same drag-and-drop produces insight or garbage depending on whether the data level matches the question.

“A principle that database engineers formalized in 1970, Wickham translated for statisticians in 2014, and you’ll use in every week from here on.”