Datasets: rows, columns, records and variables

In depth

Levels of measurement

In 1946 the psychologist S. S. Stevens proposed classifying measurements by the operations that make sense on them. His four levels of measurement are still taught in introductory statistics. Each level allows everything the levels below it allow, plus one more kind of comparison.

Each row allows everything in the rows above it. Summaries follow Stevens’s table of “permissible statistics” (1946).

Interval and ratio variables are both numerical; the difference is the zero. On a ratio variable zero means none of the quantity, so ratios are meaningful: an order of 4 packs is twice an order of 2. On an interval variable differences are meaningful but zero is an arbitrary point on the scale, so ratios are not. Calendar dates are interval: 15 May is 14 days after 1 May, but no date is “twice” another, because where a calendar starts counting is a convention.

Temperature in degrees Celsius is the classic case: 20 °C is not twice as warm as 10 °C. Convert both to Fahrenheit and they become 68 °F and 50 °F, a ratio of $68 \div 50 = 1.36$; in kelvin, which starts at absolute zero, they are 293.15 K and 283.15 K, a ratio of about 1.035. A ratio that changes when you move the scale’s zero was never meaningful.

Durations are different from dates. The number of days between two dates is a ratio variable, because zero days means no time at all: a 6-night stay is twice as long as a 3-night stay. That is why Harborview Hotel’s nights column is ratio although its check_in column is interval.

Do the levels forbid calculations?

Stevens argued that, in the strictest sense, means and standard deviations should not be used with ordinal data, because the steps between categories need not be equal. He also acknowledged that such statistics often prove useful in practice. Online shops average star ratings all the time, and an average of 4.2 is a reasonable quick summary. What an average hides is the shape: an average of 3 can come from every customer giving 3, or from half giving 1 and half giving 5, and those describe very different businesses. When you average an ordinal variable, report its distribution or its median too.

Velleman and Wilkinson (1993) went further, arguing that the level of measurement is not a fixed property of the data but depends on the question being asked. Lantern’s order numbers are nominal when you use them to look up an order; if the shop issues them in sequence, comparing two of them tells you which order was created first, which is an ordinal use. Treat the levels as a check on whether a calculation means anything, not as a rule that software should enforce.

Identifiers and keys

To find, count, correct or link rows you need a way to say which row is which. A primary key is a column, or a set of columns, whose values identify each row uniquely. Two conditions define it: no two rows share a value, and no row is missing one. Relational databases enforce both; PostgreSQL, for example, requires a primary key’s values to be unique and not null. Exports and spreadsheets enforce nothing, so you have to check.

Sometimes no single column is enough. In Lantern’s line-item report order_id repeats (one order has several lines) and line repeats (every order has a line 1), but each pair of order_id and line appears only once. A key made of two or more columns is a composite key. Choosing a key is the same decision as naming the unit of observation: if one row is one order line, the key must identify order lines.

To test a candidate key, count the rows, $n$; count the distinct values of the candidate (for a composite key, the distinct combinations); and count the rows where any part of it is blank. The candidate is a key for this table only if the distinct count equals $n$ and the blank count is zero. In SQL, SELECT COUNT(*), COUNT(DISTINCT order_id) FROM orders; returns both counts. In pandas, df.order_id.is_unique tests uniqueness and df.order_id.isna().sum() counts blanks. In a spreadsheet, compare the count of the column with the count of its unique values.

A key that passes proves only that this file has no repeats. Whether a repeat could arrive is a question about how the data is created, and the lesson on duplicates returns to it. Good keys are stable and meaningless. A customer’s email address seems to identify them, but people change addresses, couples share one, typing errors create a second version and some orders have no email at all. That is why systems issue identifiers such as customer_id, which never change and carry no meaning that could go out of date. When customer_id also appears in an orders table, it links each order to one row of the customer table; a column used this way is called a foreign key, and the next lesson shows how to check that every link points somewhere.

The data dictionary

A data dictionary records what each column of a dataset is supposed to contain. The US Geological Survey describes it as a way to catalogue and communicate a dataset’s structure and content. Typical entries give each element’s name and definition, its data type and size, whether it may be blank, its allowed values, the codes used for missing data and the business rules it must obey. Add the unit of measurement and, for calculated columns, how they are calculated.

Writing the dictionary is where vague columns get pinned down. “discount” could be a percentage or a dollar amount, per pack or per order; “order_date” could be when the order was placed, paid for or shipped. Below is a draft for Lantern’s full order export, the dataset called lantern-orders that you will clean in this course. It records what each column should contain. The export doesn’t always comply, and finding where it doesn’t is the subject of the next lesson.

Notice what the dictionary settles. Email is required but rating is optional, so a blank rating is normal and a blank email is a defect. Discount is defined in dollars for the whole order, which turns order_total into something you can check from the other columns. And unit_price is declared a number, which, as the next lesson shows, the raw export doesn’t honour. A dictionary is the reference that profiling and validation measure the data against.

Wide and long layouts

The same data can be laid out in more than one way. Suppose Lantern records the packs it sells of each coffee in each month. A wide layout gives each product a row and each month a column; a long layout gives each product–month combination its own row, with the month as a column of values.

Both tables hold the same six numbers. A table is in wide format when the values of a variable, here the month, are spread across several columns, and in long format when that variable has a column of its own and each combination of values has its own row. Wickham (2014) avoids the terms as imprecise: they are relative, so a layout is only wider or longer than another layout of the same data.

Here the long layout is the tidy one, because month is a variable and so belongs in a column. Adding April adds two rows rather than a new column, and a chart or a model can use month directly. Wide layouts suit people reading a report, and some calculations that compare columns side by side. Converting between the two, called reshaping, is part of lesson 11.