The National Health and Nutrition Examination Survey (NHANES) publishes its 2017 to 2018 results as dozens of separate files. One of them, DEMO_J, has a row for each of the 9,254 people interviewed, with their age, sex, household and income. Another, BPX_J, has a row for each of the 8,704 people who then attended the physical examination, with up to three systolic and three diastolic blood pressure readings. Suppose the question is simple: what is the average blood pressure of adults, by age group? Neither file answers it. The ages are in one file and the pressures in the other. Every column, including the person identifier and the codes for sex, arrives as a floating-point number. Some diastolic readings are 0. Children are mixed in with adults, and some people who came to the examination have no reading at all.
Getting from those files to a table that answers the question is data wrangling: the work between receiving data and analyzing it. This article defines it, lays out its usual steps, contrasts it with the automated pipelines of data engineering, introduces the pandas operations used to read and inspect data, and then carries the NHANES question through every step, counting the rows that survive each one. The later articles of this topic take the steps one at a time: Exploring Data with Groups and Pivot Tables, Structuring Data, Cleaning Data, Enriching Data and Data Engineering.
What wrangling is
The term comes from database and visualization research. Kandel, Paepcke, Hellerstein and Heer (2011) describe data wrangling as work that "regularly involves reformatting data values or layout, correcting erroneous or missing values, and integrating multiple data sources", and note that analysts often do it by "writing idiosyncratic scripts in programming languages such as Python and Perl, or extensive manual editing using interactive tools such as Microsoft Excel". The same activity goes by other names, among them data munging, data cleaning and data preparation; Wickham (2014) uses "data cleaning" for the whole of it and "tidying" for one part, the arrangement of values into rows and columns.
How much of an analysis it takes is often quoted and rarely measured. Wickham (2014) opens with "It is often said that 80% of data analysis is spent on the process of cleaning and preparing the data", citing Dasu and Johnson (2003), and Kandel et al. (2011) cite an estimate that data cleaning accounts for up to 80% of the development time and cost in data warehousing projects. These are estimates, not measurements, and the share varies with the project. The interview study of Kandel, Paepcke, Hellerstein and Heer (2012) gives a more direct impression. Having interviewed 35 analysts from 25 organizations, the authors describe discovery and wrangling as "often the most tedious and time-consuming aspects of an analysis", and one analyst told them, "I spend more than half of my time integrating, cleansing and transforming data without doing any actual analysis." The safe statement is that wrangling regularly takes a large part of an analysis, often more than the modeling.
It matters for a reason beyond time. Every number an analysis reports depends on decisions made during wrangling: which rows were kept, what a code was taken to mean, how two files were matched. Those decisions are rarely visible in the final result, which is why they deserve to be written down and checked.
The steps
A common way to organize the work, used for this topic, has six steps. The order is logical rather than strict; in practice each step can send the analyst back to an earlier one.
- Explore. Find out what the data contain before changing anything: the number of rows and columns, their types, the range of each variable, how much is missing, what the codes mean. Summaries by group are part of this step (Exploring Data with Groups and Pivot Tables).
- Structure. Arrange the data so that each variable is a column and each observation a row, give each column the right type, put dates, units and category labels into one consistent form, and rescale numerical features where a method needs it (Structuring Data).
- Clean. Find and handle missing values, special codes, impossible values, duplicates and inconsistencies, with a stated reason for each decision (Cleaning Data).
- Enrich. Add information: join other tables, stack further records, derive new variables from existing ones (Enriching Data).
- Validate. Check that the result obeys rules it must obey: keys are unique, values lie in their documented ranges, totals agree with published totals, related values are consistent.
- Publish. Save the result, with a record of how it was made, where others (including the analyst's future self) can use it.
Kandel et al. (2012) offer a related division of an analysis into five tasks, discovery, wrangling, profiling, modeling and reporting, in which "profiling" is the checking of data quality that the list above calls exploring and validating. The names differ; the work is the same.
Wrangling and ETL
Organizations that analyze data routinely build pipelines that do much of this work automatically. The classic form is extract, transform, load (ETL): extract data from the operational systems that record day-to-day transactions, transform it into consistent, cleaned tables, and load it into a database designed for analysis, the data warehouse. Chaudhuri and Dayal (1997), in an early survey of data warehousing, describe its back end as tools "for extracting, cleaning and loading data into a data warehouse". A pipeline runs on a schedule, on the same sources each time, and is maintained as software. A variant, ELT, loads the raw data first and transforms it inside the analytical database.
Wrangling, in the sense of this topic, is usually the ad hoc counterpart: one analyst, one question, a dataset that may never be processed again. The two are layers rather than rivals. An analyst often wrangles data that a pipeline has already loaded into a warehouse, because the warehouse was built for questions other than the one at hand. Data Engineering covers pipelines, warehouses and the file formats they use.
Reading and inspecting data in pandas
pandas, the standard Python library for tables, represents a table as a DataFrame: labeled columns, each with one data type, and a row index. The pandas article of the Python topic introduces it; the operations below are the ones a first look at data needs.
Reading. Each format has its own reader, and each returns a DataFrame:
import pandas as pd
demo = pd.read_csv("demographics.csv") # comma-separated text
ws = pd.read_excel("workbook.xlsx", sheet_name=0) # a spreadsheet (needs openpyxl)
bpx = pd.read_sas("BPX_J.xpt", format="xport") # a SAS transport file, as NHANES publishes
tbl = pd.read_sql("select * from visits", con) # the result of a SQL query
The NHANES files are SAS transport (XPORT) files. pandas reads them, but its reader (in releases up to 3.1) returns a stored zero as a tiny positive number, about $5.4 \times 10^{-79}$, rather than 0; Cleaning Data explains why and how this site's scripts restore the zeros. The code in this article reads the files through that corrected reader. Reading from a database through SQL is covered in Queries in Python.
Inspecting. df.shape gives the numbers of rows and columns, df.head() the first rows, df.dtypes the type of each column and df.info() all of these with the count of non-missing values in each column. For four columns of BPX_J:
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 SEQN 8704 non-null float64
1 BPXSY1 6302 non-null float64
2 BPXDI1 6302 non-null float64
3 BPXSY2 6563 non-null float64
This is already informative. The first systolic reading is present for 6,302 of the 8,704 people examined, the second for 6,563: the second reading is more often present than the first. And every column, including the person number SEQN (93703, 93704, ...), is stored as float64. That is a property of the file format, which stores every number as a floating-point value; the DEMO_J file has all 46 of its columns in the same type. A person number is an identifier and a sex code is a category, and Structuring Data converts both.
Summarizing and sorting. df.describe() gives the count, mean, standard deviation, quartiles and extremes of each numerical column, and s.value_counts() the frequency of each value of a column s. Sorting by a column with df.sort_values("BPXDI1") and looking at both ends is a quick way to find impossible or suspicious values. In BPX_J, the lowest first diastolic readings are 0 and the highest is 136, while first systolic readings run from 72 to 228 mm Hg. A diastolic pressure of 0 is not a physiological value, so the documentation must be consulted, and it says that "Diastolic BP can be zero" (National Center for Health Statistics, 2020a). In a reading by ear, such a value arises when the sounds that mark the diastolic pressure can still be heard as the cuff pressure falls to zero. For an average, 0 is best treated as missing.
Selecting. df.loc[rows, columns] selects by label (including Boolean conditions such as df.loc[df.RIDAGEYR >= 18]), df.iloc by position and df.at[row, column] a single value. Selecting the rows a question is about is often the first real decision of a wrangle, as the next section shows.
One wrangle from start to finish
The question: the mean systolic and diastolic blood pressure of adults aged 18 and over, by age group, among the people examined in NHANES 2017 to 2018. Each step below is a few lines of pandas; the decisions are the substance.
Explore. DEMO_J has 9,254 rows and 46 columns, BPX_J 8,704 rows and 21 columns. The person number SEQN is unique in both. The interview status variable RIDSTATR is 2 ("both interviewed and MEC examined") for 8,704 people and 1 ("interviewed only") for 550, which matches the size of BPX_J (National Center for Health Statistics, 2020b).
Enrich: join the files. A join on SEQN combines the two:
m = demo.merge(bpx, on="SEQN", how="left", validate="one_to_one", indicator=True)
m["_merge"].value_counts() # both 8704, left_only 550, right_only 0
validate="one_to_one" makes pandas raise an error if SEQN were repeated in either file, and indicator=True adds a column saying where each row came from. All 8,704 examination records found a match, and the 550 unmatched interview records are exactly the people with status 1. A join whose counts match what the documentation predicts is a good sign; Enriching Data shows joins whose counts do not.
Clean: select the population. The question is about adults. Of the 9,254 people, 5,856 are 18 or over; 5,533 of them were examined; and 286 of those have no systolic reading at all, leaving 5,247. Each systolic value used is the mean of a person's available readings (one, two or three). Of the diastolic readings of these adults, 83 are zeros, spread over 53 people, and 15 people have no diastolic reading other than zeros; treating zeros as missing leaves 5,232 with a usable diastolic pressure.
Two details deserve a sentence each. First, the order of these filters does not change the result, but recording the count after each one is what makes the result checkable, here and in the figure above. Second, leaving out the age filter changes the answer: the 6,714 people with any systolic reading include children from age 8, and their mean systolic pressure is 121.7 mm Hg instead of 126.4. A wrangle is shaped by its question.
Validate. The blood pressure documentation lists editing rules: the systolic pressure must exceed the diastolic, and there can be no diastolic reading without a systolic one (National Center for Health Statistics, 2020a). Checking them costs a line each, and in these data neither is broken by any of the three pairs of readings. The documentation also says the examiners recorded even numbers only, and all systolic readings are indeed even, a fact Types of Numerical Data uses.
Result. Grouping by age and averaging gives:
| Age group | Adults | Mean systolic (mm Hg) | Mean diastolic (mm Hg) |
|---|---|---|---|
| 18 to 39 | 1,745 | 114.8 | 69.8 |
| 40 to 59 | 1,579 | 126.3 | 77.4 |
| 60 and over | 1,923 | 136.8 | 70.9 |
| All adults | 5,247 | 126.4 | 72.5 |
Systolic pressure rises steadily with age, while diastolic pressure rises into middle age and then falls. The decision about zeros matters for the diastolic means: averaging the zeros in as if they were measurements gives an overall mean of 72.12 mm Hg instead of 72.49. That is a small shift, but it is a bias, not noise, and it would grow in any subgroup where zeros are common. These means describe the examined adults as a group of people. They are not estimates for the United States population, which would need the survey's sampling weights, as the Applied Statistics pages note when they use the same adults.
Publish. The script that produced every number above, tools/experiments/data-science/introduction-to-data-wrangling.py in the site's repository, downloads the two public files, checks that they are byte-for-byte the files it was written for, and reruns every step. That is the publishing step in its simplest form.
Wrangling as code
The steps above could have been done by hand in a spreadsheet: copy the ages next to the pressures, delete the children, blank out the zeros. Kandel et al. (2011) observed analysts doing exactly that and designed their Wrangler tool to produce "robust, auditable transforms instead of manual editing". The argument for writing the wrangle as code, or as a recorded series of transforms, is that it can be read, checked and rerun. When the agency releases a corrected file, the script runs again; when a reviewer asks why 286 adults are missing, the line that removed them answers.
Wilson et al. (2017), in a set of practices for scientific computing, put the data rules first: "Save the raw data", record all the steps used to process data, and give every record a unique identifier. Their reason for keeping raw files untouched is that "faithful retention is essential for rerunning analyses from start to finish, for recovery from analytical mishaps, and for experimenting without fear". A wrangle that overwrites its input cannot be repeated.
Judgment in every step
Wrangling is not a sequence of mechanical fixes. The zero diastolic readings were not errors to delete; they were a documented recording convention, and the right treatment depended on the question (an average) rather than on the data alone. The 286 adults without a systolic reading could have been ignored, but Cleaning Data shows they are not a random subset: they are more often in the youngest and oldest age groups, which matters for some questions more than others. The same raw files, wrangled for a different question, such as how often the examination failed to produce a reading, would keep exactly the rows this wrangle removed.
The later articles of this topic take these decisions one kind at a time.
References
- Chaudhuri, S., and Dayal, U. (1997). An overview of data warehousing and OLAP technology. ACM SIGMOD Record, 26(1), 65–74. https://doi.org/10.1145/248603.248616 record · 2 pages
- Dasu, T., and Johnson, T. (2003). Exploratory Data Mining and Data Cleaning. Wiley. (Cited through Wickham, 2014.) record
- Kandel, S., Paepcke, A., Hellerstein, J., and Heer, J. (2011). Wrangler: Interactive visual specification of data transformation scripts. In Proceedings of the SIGCHI Conference on Human Factors in Computing Systems (CHI '11). https://doi.org/10.1145/1978942.1979444 record
- Kandel, S., Paepcke, A., Hellerstein, J. M., and Heer, J. (2012). Enterprise data analysis and visualization: An interview study. IEEE Transactions on Visualization and Computer Graphics, 18(12), 2917–2926. https://doi.org/10.1109/TVCG.2012.219 record
- National Center for Health Statistics (2020a). National Health and Nutrition Examination Survey 2017–2018 Data Documentation, Codebook, and Frequencies: Blood Pressure (BPX_J). Centers for Disease Control and Prevention. https://wwwn.cdc.gov/Nchs/Data/Nhanes/Public/2017/DataFiles/BPX_J.htm record · 8 pages
- National Center for Health Statistics (2020b). National Health and Nutrition Examination Survey 2017–2018 Data Documentation, Codebook, and Frequencies: Demographic Variables and Sample Weights (DEMO_J). Centers for Disease Control and Prevention. https://wwwn.cdc.gov/Nchs/Data/Nhanes/Public/2017/DataFiles/DEMO_J.htm record · 8 pages
- Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1–23. https://doi.org/10.18637/jss.v059.i10 record · 5 pages
- Wilson, G., Bryan, J., Cranston, K., Kitzes, J., Nederbragt, L., and Teal, T. K. (2017). Good enough practices in scientific computing. PLOS Computational Biology, 13(6), e1005510. https://doi.org/10.1371/journal.pcbi.1005510 record · 2 pages