Rectangular data, sources, and the tidy layout

The shape every tool expects

A rectangular (or tidy) dataset has one row per record — the thing observed, here an order — one column per variable — a property of that thing — and exactly one header row naming the columns. Nothing else: no totals inside the data, no merged cells, no second table pasted underneath. Sheets, Excel pivots and R's data.frame all assume this shape, and a sheet that breaks it does not fail loudly; it produces wrong numbers quietly.

Worked example — the orders table, and its broken cousin

Maple & Birch's Q1 2025 export is the model. Twelve rows, six columns, one header:

order_id  prov  channel  amount  units  order_date
1001      ON    Online      420      3  2025-01-06
1002      QC    Store       180      1  2025-01-08
1003      ON    Store       760      5  2025-01-15
...
1012      BC    Online      470      3  2025-03-25

Each row is one order; each column is one fact about it. amount is a single variable holding twelve values, which is why =SUM(D2:D13) (6,140) and =AVERAGE(D2:D13) (511.67) work with no further thought.

Now the cousin a store manager built by hand for the same quarter:

prov   January  February  March
ON       1180       510    305
QC        180        90    215
BC        250       640    470
AB          0      1320    980
Total    1610      2560   1970

The numbers are right — the January column totals 1,610 like the real data — but two faults make it useless as input. First, one variable, amount, is smeared across three columns whose names carry data (the month). To average all orders you would have to average across columns and rows at once; to pivot by month there is no month column to pivot on. Second, the Total row sits inside the data. A SUM down the January column now double-counts (3,220 instead of 1,610), a sort scatters the total among the provinces, and a pivot treats "Total" as a fifth province.

The fix is to unpivot — turn each (province, month) cell into its own row of a (prov, month, amount) table, twelve rows, three columns — and to delete the total row, because a total is something you compute from tidy data, never something you store in it. Excel's Power Query and R's reshape() do this mechanically; recognising when it is needed is the examined skill.

Where the data comes from

The source of a dataset is worth one line in its dictionary. Internal data is generated by your own organisation — the orders export, the ledger, the CRM. External data comes from outside — Statistics Canada population tables, an open-data portal, a purchased list. Independently, data is primary if it was collected for your question (a survey you ran on your customers) and secondary if it was collected for someone else's (the same StatCan table, gathered for the census). Secondary data is the common case and carries a standing warning: someone else chose the definitions, so read them. A PDF of a table is neither until it has been extracted into rows and columns — until then it is unstructured, and this course stays with structured data.

Spot three faults, classify four sources

A messy sheet arrives with merged province headers, a blank row between February and March, and a "Q1 total" row at the bottom. All three break the rectangle: merged cells are not one-column-per-variable, the blank row splits the table, the total is data that should be computed. Then classify: the orders export — internal, primary; a StatCan provincial population table used for per-capita sales — external, secondary; a customer survey the crew runs — internal, primary; a competitor's price list bought from a vendor — external, secondary.

CRISP-DM: recognising and fixing the layout is data preparation → format data; recording the source is data understanding → describe data.