Wide-to-tall Data Reshaping Using Regular Expressions and the nc Package
Dylan Hocking Toby · The R Journal · 2021
Regular expressions are powerful tools for extracting tables from non-tabular text data.Capturing regular expressions that describe the information to extract from column names can be especially useful when reshaping a data table from wide (few rows with many regularly named columns) to tall (fewer columns with more rows).We present the R package nc (short for named capture), which provides functions for wide-to-tall data reshaping using regular expressions.We describe the main new ideas of nc, and provide detailed comparisons with related R packages (stats, utils, data.table,tidyr, tidyfast, tidyfst, reshape2, cdata).Recently, Hocking (2019a) proposes a new syntax for defining named capture groups in R code.Using this new syntax, named capture groups are specified using named arguments in R, which results in code that is easier to read and modify than capture groups defined in string literals.For example, the pattern in the previous paragraph can be written as part = ".*","[.]", dimension = ".*".Sub-patterns can be grouped for clarity and/or re-used using lists, and numeric data may be extracted with user-provided type conversion functions.The main thesis of this article is that regular expressions can greatly simplify the code required to specify wide-to-tall data reshaping operations (when the input columns adhere to a regular naming convention).For one such operation, the input is a "wide" table with many columns, and the desired output is a "tall" table with more rows, and some of the input columns are converted into a smaller number of output columns (Figure 1).To clarify the discussion, we first define three terms that we will use to refer to the different types of columns involved in this conversion:Reshape columns contain the data which is present in the same amount but in different shapes in the input and output.There are equivalent terms used in different R packages: varying in utils::reshape, measure.vars in melt (data.table,reshape2), etc.Copy columns contain data in the input which are each copied to multiple rows in the output (id.vars in melt).Capture columns are only present in the output, and contain data which come from matching a capturing regex pattern to the input reshape column names.For example, the wide iris data (W in Figure 1) have four numeric columns to reshape: Sepal.Length, Sepal.Width, Petal.Length, Petal.Width.For some purposes (e.g., displaying a histogram of each reshape input column using facets in ggplot2), the desired reshaping operation results in a table with a single reshape output column (S in Figure 1), two copied columns, and two columns captured from the names of the reshaped input columns.For other purposes (e.g., scatterplot to compare sepal and