Structuring Data

Giving data a consistent shape, type and scale. Tidy data (each variable a column, each observation a row, each kind of observational unit a table) and the common ways real files break it; reshaping between wide and long layouts with melt, wide_to_long and pivot; headers that hold values or two variables at once; types, ordered codes and one-hot encoding; dates and epidemiological weeks; and rescaling by z-scores, minimum and maximum, or median and interquartile range, with the effect of units on nearest neighbors measured. Worked on NHANES blood pressure readings, Census population files and NCHS weekly death counts.

NHANES records up to three blood pressure readings for each person examined. Its file stores them side by side, in six columns named BPXSY1 to BPXSY3 and BPXDI1 to BPXDI3, one row per person. That layout is convenient for some questions, such as each person's mean systolic pressure. It is awkward for others. Does the first reading tend to run higher than the later ones? Answering that needs the reading number as a variable, and in this file the reading number is not stored in any column. It is part of the column names.

Structuring is the step of wrangling that gives data a consistent shape, consistent types and, where a method needs it, consistent scales. This article starts with shape, using Wickham's (2014) definition of tidy data and the reshaping operations that move between layouts, then turns to types and codes, dates, and finally the rescaling of numerical features. The examples come from three public sources, chosen because each has a structural feature worth seeing in real form: the NHANES examination files, the Census Bureau's population estimates and the National Center for Health Statistics (NCHS) weekly death counts.

Tidy data

Wickham (2014) starts from the observation that the same values can be laid out in many tables, and that a vocabulary of rows and columns cannot say which layout is right. He adds semantics: "Every value belongs to a variable and an observation. A variable contains all values that measure the same underlying attribute (like height, temperature, duration) across units. An observation contains all values measured on the same unit (like a person, or a day, or a race) across attributes." A dataset is tidy when its layout matches its meaning:

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

Wickham notes that this is Codd's third normal form from relational database design, "but with the constraints framed in statistical language", and that "Messy data is any other arrangement of the data." The relational side is covered in Relational Databases.

Tidiness matters because most operations act on whole variables. A mean, a filter, a grouping or a model formula names columns. If a variable is spread over several columns, or several variables are packed into one, each operation needs a special case, and in Wickham's words this "slows analysis and invites errors". In tidy data every variable can be reached the same way.

Wickham lists the five most common ways real datasets are messy:

  • column headers are values, not variable names;
  • multiple variables are stored in one column;
  • variables are stored in both rows and columns;
  • multiple types of observational unit are stored in the same table;
  • a single observational unit is stored in multiple tables.

He also observes that most can be fixed with three operations: melting (turning columns into rows), string splitting and casting (turning rows back into columns). The sections below meet four of the five problems in public files.

Wide and long

The NHANES readings are a clear case of the first problem. The columns sys_1, sys_2, sys_3 (renamed here for readability) are three values of a variable, the reading number, stored as column headers. Each adult's row is an observation of the adult, but the observation the question is about is a reading.

Melting fixes this. Each of the six reading columns becomes a row, keyed by the person and the reading number:

long = pd.wide_to_long(wide, stubnames=["sys", "dia"], i="SEQN", j="reading", sep="_")

wide_to_long handles the case where several variables (systolic and diastolic) share the same suffixes; pd.melt handles one variable at a time and is the more general tool. For the 5,247 adults, the wide table has 5,247 rows and 7 columns; the long one has 15,741 rows (three per adult) and 4 columns: SEQN, reading, sys and dia.

The same readings of three adults in wide form (one row per person, the reading number hidden in the column names) and long form (one row per reading, the reading number a column of its own). The color bar and the repeated SEQN both mark which person a row belongs to. Missing readings become explicit rows of NaN in long form.
The same readings of three adults in wide form (one row per person, the reading number hidden in the column names) and long form (one row per reading, the reading number a column of its own). The color bar and the repeated SEQN both mark which person a row belongs to. Missing readings become explicit rows of NaN in long form.

The figure shows something the wide form hides. Adult 93705 has only a third reading and adult 93708 only a second and third; in long form each missing reading is a row with NaN, and dropping those rows leaves 15,146 readings. Among the adults, 4,730 have all three systolic readings, 439 have two and 78 have one.

The question about the first reading is now a one-line grouped mean. For the 4,730 adults with all three readings, so that every reading number covers the same people, the mean systolic pressure is 125.74 mm Hg at the first reading, 125.40 at the second and 124.96 at the third. For the 4,811 adults with both a first and a second reading, the first is higher in 44.9% of cases, equal in 16.1%, and higher on average by 0.34 mm Hg. The decline is real but small here. This is one reason clinical protocols average several readings rather than relying on the first.

The inverse operation, pivoting or casting, turns rows back into columns: long.pivot(index="SEQN", columns="reading", values=["sys", "dia"]) rebuilds a table of 5,247 rows. Neither layout is wrong in itself. The wide layout suits a per-person analysis and is how a person's record is naturally entered; the long layout suits per-reading analysis, plotting and modeling. Wickham avoids the words "wide" and "long" in the paper as imprecise, preferring to ask which columns are variables, but they are the common names for the two layouts and the pandas documentation uses them (pandas development team, 2024b).

Headers that hold values, and rows that hold totals

The Census Bureau's file of 2021 state population estimates by age, sex, race and Hispanic origin (sc-est2021-alldata6.csv; U.S. Census Bureau, 2022) has 236,844 rows and 12 columns, and shows two of the problems at once.

Header values. The estimates are in three columns, ESTIMATESBASE2020, POPESTIMATE2020 and POPESTIMATE2021. The year is a variable stored in the headers. Melting the two population columns gives a year column with values 2020 and 2021 and a population column, 473,688 rows in all:

melted = census.melt(id_vars=["NAME", "SEX", "ORIGIN", "RACE", "AGE"],
                     value_vars=["POPESTIMATE2020", "POPESTIMATE2021"],
                     var_name="year", value_name="population")
melted["year"] = melted.year.str.removeprefix("POPESTIMATE").astype(int)

Totals stored as rows. Each state has 4,644 rows: three values of SEX, three of ORIGIN, six race groups and 86 single years of age (0 to 84, and 85 for 85 and over), $3 \times 3 \times 6 \times 86 = 4{,}644$. But SEX 0 means "both sexes" and ORIGIN 0 means "both Hispanic and non-Hispanic". These rows are totals of other rows in the same table, a version of the fourth problem: two kinds of unit, people in a detailed cell and a sum over cells, in one table. Summing the 2021 column over every row gives 1,327,574,980, exactly four times the United States population of 331,893,745, because every person is counted once in a detailed row, once in a sex total, once in an origin total and once in a row that totals both. Keeping only SEX > 0 and ORIGIN > 0 leaves 105,264 detailed rows for each year (210,528 in the melted table), and their 2021 values sum to the correct total. The file's layout document defines these codes ("0 = Total" for both; U.S. Census Bureau, 2022), and reading it before summing is the only reliable protection. The 2019 national and state file has the same feature in another form, with rows for the United States and four Census regions alongside the states; Enriching Data meets them during a join.

Two variables in one header. The NCHS weekly death counts by state, 2014 to 2019, have one column for each of eleven causes of death, with names such as Septicemia (A40-A41) and Malignant neoplasms (C00-C97). Each header holds a value of two variables: a cause name and its range of codes in the International Classification of Diseases, 10th revision (ICD-10). Melting the eleven columns gives 185,922 rows, and a regular expression splits the old header into two columns:

parts = long.cause_col.str.extract(r"^(?P<cause>.*) \((?P<icd10>[^)]*)\)$")

The same file has an All Cause column with two spaces in its name, a detail that breaks any code that types the name with one. Renaming columns to a consistent form (lower case, single underscores) immediately after reading is a cheap habit that prevents this class of error; Data Engineering does so when loading the file into a database.

Types

A column's type decides what operations mean on it. Three kinds of mismatch are common.

Numbers that are identifiers or codes. Every column of the NHANES files arrives as a 64-bit floating-point number, because the SAS transport format stores all numerical values that way (SAS Institute, 2021). The person number SEQN reads as 93703.0 and sex as 1.0 or 2.0. Nothing about the values is wrong, but an identifier should never be averaged, and a code should not be treated as a quantity. Converting the person number to an integer and sex and race to pandas categories reduces the memory of those three columns from 222,096 to 55,868 bytes for the 9,254 rows of the demographics file and, more usefully, makes their roles explicit:

demo = demo.astype({"SEQN": "int32", "RIAGENDR": "category", "RIDRETH3": "category"})

Identifiers that look like numbers. The opposite problem appears in text files. The Census files identify each state by a two-digit code, 01 for Alabama. pd.read_csv infers that the column is numeric and reads it as the integer 1, losing the leading zero; any later join with a table that stores "01" as text then fails to match. Passing dtype={"STATE": str} keeps the code as text (pandas development team, 2024a). In the 2019 Census file the REGION column holds 0 to 4 for the nation and the four regions, and X for Puerto Rico, which is not in a region; that one letter makes pandas read the entire column as text. Inference from a file's contents is a guess, and checking df.dtypes after every read catches both kinds of surprise.

Codes whose numbers do not follow their meaning. The NHANES household income variable INDHHIN2 records annual household income in dollars, in categories coded 1 to 10 for ranges from "0 to 4,999" up to "65,000 to 74,999", then 12 for "20,000 and over", 13 for "under 20,000", 14 for "75,000 to 99,999" and 15 for "100,000 and over", with 77 for refused and 99 for "don't know" (National Center for Health Statistics, 2020). The documentation explains codes 12 and 13: respondents who would not give an exact figure were asked whether income was below or above 20,000 dollars and then to choose a range from a card, and the two coarse codes record those who could answer only the first question. The codes are labels in the order they were added, not in order of income. Their mean, 12.50, is meaningless, and sorting by code puts "20,000 and over" between the ranges ending at 74,999 and starting at 75,000.

The structured form is an ordered categorical variable with the twelve detailed ranges in income order. It covers 7,925 people; 448 gave only the coarse answer, 390 refused or did not know, and 491 have no value. The coarse answers overlap the detailed ranges, so they cannot be placed in the order, and they need a decision of their own (for instance, a separate analysis or an imputation), which is a cleaning question. With the order fixed, statistics that need only order become possible, such as the median category of the detailed answers, 45,000 to 54,999 dollars. Types of Numerical Data explains which statistics an ordinal variable supports and proves that a mean of ordered codes can change its ranking under recoding.

One-hot encoding

Most models need numerical inputs, and a nominal variable such as race and Hispanic origin (six groups, coded 1, 2, 3, 4, 6 and 7 in NHANES) must not enter a model as its code, which would impose an order and spacing the groups do not have. One-hot encoding replaces it with one indicator column per category, 1 for that category and 0 otherwise:

pd.get_dummies(adults.race, prefix="race", dtype=int)                    # 6 columns, each row sums to 1
pd.get_dummies(adults.race, prefix="race", drop_first=True, dtype=int)   # 5 columns

Because the six indicators always sum to 1, a regression with an intercept can use only five; drop_first=True drops the first category in sorted order (here Mexican American), which becomes the baseline. Multiple Linear Regression derives why one indicator must go and how the coefficients are read.

Dates

A date stored as text, such as 10/25/2014 in the weekly deaths file, sorts as text and cannot be subtracted. pd.to_datetime(s, format="%m/%d/%Y") converts it; giving the format explicitly is faster and, more importantly, refuses to guess whether 01/02/2016 is the 2nd of January or the 1st of February. Converted, the file's week-ending dates run from 4 January 2014 to 28 December 2019, and every one is a Saturday.

A converted date is easy to decompose into year, month and weekday, but the decomposition must match the meaning of the data. The weekly deaths are tallied by MMWR week, the epidemiological week used by the Centers for Disease Control and Prevention. Its rules are that each week begins on a Sunday and that week 1 of a year "is the first week of the year that has at least four days in the calendar year"; as a consequence, "December 29, 30, and 31 could potentially fall into MMWR week #1 of the following MMWR year", and early January days can fall in week 52 or 53 of the previous year (Centers for Disease Control and Prevention, n.d.). In the file, MMWR year 2014 has 53 weeks and the others 52; week 53 of 2014 ends on 3 January 2015 and week 52 of 2015 on 2 January 2016. Taking the calendar year of the week-ending date as the year would misassign those 108 rows (two weeks for each of 54 jurisdictions). The variable "year" has two definitions here, and the file's own MMWR Year column is the one consistent with its counts.

Scaling numerical features

Shape and type make data usable; scale matters for methods that combine features. Distance-based methods such as k-nearest neighbors and k-means, and variance-based ones such as principal component analysis, treat a difference of one unit in any feature as equally important, so the feature with the largest numbers dominates. K-means in Practice and Introduction to Unsupervised Learning show the effect on clusters; this section shows it directly.

The examples use the 5,176 adults with a height, a weight and a systolic pressure:

Feature Mean Standard deviation Median IQR Min Max
Age (years) 49.75 18.52 51.00 30.25 18 80
Height (cm) 166.47 10.04 166.00 14.72 139.0 197.7
Weight (kg) 82.54 22.89 79.00 28.12 36.2 219.6
Systolic (mm Hg) 126.19 19.90 123.33 24.67 72.67 238.0

Units decide distances

Consider the nearest neighbor of each adult by height and weight, measured by Euclidean distance. The squared distance between two people adds the squared difference in height to the squared difference in weight. With height in centimeters and weight in kilograms, the two variances are $10.04^2$ and $22.89^2$, so on average weight contributes 83.9% of a squared distance and height 16.1%. Express height in meters instead, a choice with no bearing on the people, and height's share falls to 0.002%; distance becomes weight alone. For about 84% of the adults, the nearest neighbor changes when height is converted from centimeters to meters. (Measurements recorded to 0.1 cm and 0.1 kg produce ties: 7% of the adults have two or more equally near neighbors, so shares like this one move by a point or two with the rule for breaking ties.) For the first adult in the table (158.3 cm, 79.5 kg), the nearest neighbor in centimeters is a person of 158.2 cm and 79.9 kg; in meters it is a person of exactly the same weight but 159.8 cm tall.

A distance computed on raw features therefore reflects the units in which they happened to be recorded. Rescaling puts the features on a common footing.

Three rescalings

Standardization replaces each value by its z-score, its distance from the mean in standard deviations (Measures of Center and Spread introduces it on the same blood pressures):

$$z_i = \frac{x_i - \bar{x}}{s},$$

where $\bar{x}$ is the feature's mean and $s$ its standard deviation. The result has mean 0 and standard deviation 1, and units cancel, so the choice of centimeters or meters no longer matters. For the first adult, aged 66 with a systolic pressure of 202, the z-scores are 0.878 for age, $-0.814$ for height, $-0.133$ for weight and 3.809 for systolic pressure: an unusually high pressure stands out at once.

Min-max scaling, often called normalization, maps each feature onto $[0, 1]$:

$$x_i' = \frac{x_i - x_{\min}}{x_{\max} - x_{\min}}.$$

It gives the first adult 0.774, 0.329, 0.236 and 0.782. Every feature then has the same range, which some methods (and image pixel values) need. Its weakness is that the range is set by the two most extreme values. The heaviest adult weighs 219.6 kg, so min-max scaling puts 95.7% of the adults below 0.5 on weight, with a median of 0.233. One extreme person compresses everyone else into the lower quarter of the scale.

Robust scaling uses the median and the interquartile range (IQR, the distance between the 25th and 75th percentiles), which extreme values barely move:

$$x_i' = \frac{x_i - \operatorname{median}(x)}{\operatorname{IQR}(x)}.$$

The first adult's values become 0.496, $-0.523$, 0.018 and 3.189. Measures of Center and Spread explains why the median and IQR resist outliers.

Standardization is the usual default for distance and variance-based methods. Min-max scaling suits features with natural bounds. Robust scaling suits features with long tails or outliers that should not set the scale. None of them changes the shape of a distribution: a skewed feature stays skewed. Changing shape needs a transformation such as the logarithm or the Box–Cox family, which The Normal Distribution covers for blood pressure and body mass index.

Standardizing height and weight changes the nearest neighbor of about 30% of the adults compared with the raw centimeter and kilogram values, and standardizing all four features, compared with the same four in raw units, changes it for 34.8%. There is no neutral choice. Raw units weight features by their spread in whatever units were used; standardization weights them equally. If one feature is known to matter more, weighting it more is legitimate, but it should be a decision.

Two details

Which standard deviation. A sample standard deviation divides by $n - 1$ and a population one by $n$. pandas' std() uses $n - 1$ by default; scikit-learn's StandardScaler and preprocessing.scale use $n$ (scikit-learn developers, n.d.). For 5,176 adults the two differ by a factor of $\sqrt{n/(n-1)} = 1.000097$, which is negligible here but not for small datasets, and it explains why z-scores computed in the two libraries do not match exactly.

Fit on the training data. When data are split for model evaluation, the means, standard deviations, minima and maxima used for scaling must be computed from the training set alone and then applied unchanged to the test set. Otherwise information from the test set leaks into the model. In a 70/30 split of these adults (seed 0), the training set's weight has mean 82.86 kg and standard deviation 23.08, against 82.54 and 22.89 for all the data; the difference is small, but the principle does not depend on its size. Training, Validation, and Test Sets covers the split, and scikit-learn's fit and transform methods enforce the order.

In Python

import pandas as pd
from sklearn.preprocessing import MinMaxScaler, RobustScaler, StandardScaler

# wide to long and back
long = pd.wide_to_long(wide, stubnames=["sys", "dia"], i="SEQN", j="reading", sep="_").reset_index()
long.dropna(subset=["sys"]).groupby("reading").sys.mean()
back = long.pivot(index="SEQN", columns="reading", values=["sys", "dia"])

# reading with explicit types; dates with an explicit format
census = pd.read_csv("sc-est2021-alldata6.csv", dtype={"STATE": str})
weekly["week_end"] = pd.to_datetime(weekly["Week Ending Date"], format="%m/%d/%Y")
weekly.columns = weekly.columns.str.lower().str.replace(r"\W+", "_", regex=True)

# an ordered category with its real order
income = pd.Categorical(labels, categories=ordered_labels, ordered=True)

# scaling, fitted on training rows only
scaler = StandardScaler().fit(X_train)          # also MinMaxScaler(), RobustScaler()
Z_train, Z_test = scaler.transform(X_train), scaler.transform(X_test)

The numbers in this article are computed by tools/experiments/data-science/structuring-data.py in the site's repository from the public files named there.

References