Spreadsheets as places where data tables are recorded can be pretty messy. The ‘tidy’ paradigm in R proposes that data are organised so that variables are recorded in columns, observations in rows and that there is only one value per cell (Wickham 2014). This, however, is only one interpretation of how data should be organised and especially when scraping data from multiple heterogeneous sources off the internet, one frequently encounters messy data that don’t follow this paradigm.
The tidyr package is one of the most popular tools to bring data into a tidy format. However, up until today it is limited to tables that are already organised into topologically coherent (rectangular) chunks. In tabshiftr we try to describe and work with a further dimension of messiness, where data are available as so-called disorganised (messy) data, data that are not yet arranged into rectangular form.
Data can be disorganised according to multiple dimensions. To understand those dimensions, we first need to understand the nature of data. Data "of the same kind" are collected in a variable, which is always a combination of a 'name' and the 'values'. In a table, names are typically in the topmost row and values are in the column below that name (Tab. 1). Conceptually, there are two types of variables in any dataset:
The following lists the dimensions of how data can be disorganised, several of which can co-occur in one table.
The approach of tabshiftr is based on capturing the arrangement of a table in a so-called schema description. Typically there is an input and an output schema, describing the arrangement of the input and output tables, respectively. The advantage of this procedure is that input and output tables exist explicitly and the schema maps the transformation of the data. As we want to end up with tidy tables the output schema is pre-determined by a tidy table of the included variables, but the input schema description needs to be provided by the user.
To set up a schema description, the following steps should be taken:
Clarify which are the identifying variables and which are the observed variables. Make sure not to mistake a listed observed variable as identifying variable.
Determine whether there are clusters, find the origin (top left cell) of each cluster and provide the required information in setCluster() (Tab. 9, Tab. 10 & Tab. 11). It might make sense to treat a table that does not start at the topmost left cell as cluster (Tab. 6). Follow the next steps for each cluster…
Determine which variable identifies clusters and specify that as identifying variable. It can also be the case that the data are organised into separate spreadsheets or files according to one of the variables and also those cases should be treated as if they were clusters, even if each spreadsheet/file contains a topologically coherent table. It may be that either an identifying variable, or a observed variable identifies clusters:
setCluster(id = ...) and specify it as an identifying variable (setIDVar())"observed" as cluster ID. Determine for each identifying variable the following:
setIDVar(value = ...) (Tab. 6).setIDVar(split = ...) (Tab. 5).setIDVar(distinct = TRUE) (Tab. 12). Determine for each observed variable the following:
key, together with the name of the respective observed variable (as it appears in the table) in values.key = "cluster" and in values the cluster number the variable refers to (Tab. 11). Provide potentially information about the table header (setHeader()) and format (setFormat()).
In this section we look at some examples of disorganised data, discuss the dimension along which they are disorganised and show which schema description can be used to reorganise them. This is certainly not an exhaustive list of possible table arrangements, but it covers certain basic building blocks of the most common operations and should be extensible enough to capture many mutations of the presented tables.
Ideally, we read in tables while treating any header rows as data, i.e., by not setting the first row as header. This makes sense, because in disorganised tables it’s often not only the first row that is part of the header.
In case the observed variables are arranged into individual columns (Tab. 2), we have tidy data (Wickham 2014), which are already in the target arrangement. The variables in a tidy table may however, still need different names, units and transformation factors.
schema <- setHeader(rows = 1) %>% setIDVar(name = "territories", columns = 1) %>% setIDVar(name = "year", columns = 2) %>% setIDVar(name = "commodities", columns = 3) %>% setObsVar(name = "harvested", unit = "ha", columns = 4) %>% setObsVar(name = "production", unit = "t", columns = 5)
In case identifying variables are factors with a small number of levels, those levels may falsely be used as names of (some) other variables, where they would be next to each other and thus "wide". The other variables (both identifying and observed variables) would then be "nested" in those wide identifying variables. In those cases we have to record the columns and the row in which the values of the identifying variable (which appear to be names) are found. Beware that in those cases you also need to adapt the header specification, so that this row is excluded from the respective nested variable.
schema <- setHeader(rows = c(1, 2)) %>% setIDVar(name = "territories", columns = 1) %>% setIDVar(name = "year", columns = 2) %>% setIDVar(name = "commodities", columns = c(3, 5), row = 1) %>% setObsVar(name = "harvested", unit = "ha", columns = c(3, 5), row = 2) %>% setObsVar(name = "production", unit = "t", columns = c(4, 6), row = 2)
In case several variables are nested within other variables, we have to specify for all nested or nesting variables in which rows their values sit and adapt the header.
schema <- setHeader(rows = c(1:3)) %>% setIDVar(name = "territories", columns = 1) %>% setIDVar(name = "year", columns = c(2, 6), row = 1) %>% setIDVar(name = "commodities", columns = c(2, 4, 6, 8), row = 2) %>% setObsVar(name = "harvested", unit = "ha", columns = c(2, 4, 6, 8), row = 3) %>% setObsVar(name = "production", unit = "t", columns = c(3, 5, 7, 9), row = 3)
Sometimes it may be the case that several variables are stored in the same column, for example when a territorial unit is given together with the year of observation (e.g. unit1, year1). In those cases, the resulting variables in the output schema need to be specified so that they point to that same column but extract information via a regular expression (find more information here). For example, .+?(?=_) gives everything up until the first _ and (?<=\\_).* everything after the _.
schema <- setHeader(rows = 1) %>% setIDVar(name = "territories", columns = 1) %>% setIDVar(name = "year", columns = 2, split = ".+?(?=_)") %>% setIDVar(name = "commodities", columns = 2, split = "(?<=\\_).*") %>% setObsVar(name = "harvested", unit = "ha", columns = 3) %>% setObsVar(name = "production", unit = "t", columns = 4)
When data are split up into clusters, the identifying variable is often recorded not in the table as an explicit variable, but is only provided in the file or spreadsheet name. In those cases, we have to register the variable nevertheless, and provide the value of that variable in value = of the identifying variable.
schema <- setCluster(id = "territories", top = 2, left = 1) %>% setHeader(rows = 1, relative = TRUE) %>% setIDVar(name = "territories", value = "unit 1") %>% setIDVar(name = "year", columns = 4, relative = TRUE) %>% setIDVar(name = "commodities", columns = 1, relative = TRUE) %>% setObsVar(name = "harvested", unit = "ha", columns = 2, relative = TRUE) %>% setObsVar(name = "production", unit = "t", columns = 3, relative = TRUE)
Some tables contain a column where the names of observed variables (harvested and production) are treated as if they were the values of an identifying variable (dimension), while the values are presented in only one column (values) (Tab. 7). To end up with tidy data in those cases, we need to extract the values associated with the observed variables. Thus, we define the observed variables and specify the key = in which the variable names sit, and the value = the variable name has, to extract that variable.
schema <- setHeader(rows = 1) %>% setIDVar(name = "territories", columns = 1) %>% setIDVar(name = "year", columns = 2) %>% setIDVar(name = "commodities", columns = 3) %>% setObsVar(name = "harvested", unit = "ha", columns = 5, key = "dimension", value = "harvested") %>% setObsVar(name = "production", unit = "t", columns = 5, key = "dimension", value = "production")
Moreover, (several) identifying variables may be wide additionally and we have to proceed as mentioned above, by providing the row and columns of the wide identifying variables.
schema <- setHeader(rows = 1) %>% setIDVar(name = "territories", columns = 1) %>% setIDVar(name = "year", columns = 2) %>% setIDVar(name = "commodities", columns = c(4, 5), row = 1) %>% setObsVar(name = "harvested", unit = "ha", columns = c(4, 5), key = "dimension", value = "harvested") %>% setObsVar(name = "production", unit = "t", columns = c(4, 5), key = "dimension", value = "production")
Clusters are often of the same arrangement within one spreadsheet, they can be repeated along rows (horizontally) or along columns (vertically). A table should be treated like a cluster also when the spreadsheet contains not only the table, but perhaps also text that may be scattered across the document and that does not allow the table to start at the spreadsheet origin in the topmost left cell.
To reorganise those data into tidy form, each cluster is "cut out", rearranged individually and appended to the end of an output table by reorganise().
In case clusters are sitting right next to each other in the same origin row (Tab. 9), it is sufficient to provide the topmost row and all leftmost columns at which a new cluster starts. In case there is some arbitrary horizontal space between clusters, also the width (of each cluster) needs to be provided.
schema <- setCluster(id = "territories", left = c(2, 5), top = 2) %>% setHeader(rows = 1) %>% setIDVar(name = "territories", columns = c(2, 5), row = 2) %>% setIDVar(name = "year", columns = 1) %>% setIDVar(name = "commodities", columns = c(2, 5)) %>% setObsVar(name = "harvested", columns = c(3, 6), unit = "ha") %>% setObsVar(name = "production", columns = c(4, 7), unit = "t")
For vertically arranged clusters (Tab. 10), just like for the horizontal case, the respective rows, columns (and heights) need to be provided.
schema <- setCluster(id = "territories", top = c(3, 8), left = 2) %>% setHeader(rows = 1) %>% setIDVar(name = "territories", columns = 1, row = c(2, 7)) %>% setIDVar(name = "year", columns = 2) %>% setIDVar(name = "commodities", columns = 3) %>% setObsVar(name = "harvested", columns = 4, unit = "ha") %>% setObsVar(name = "production", columns = 5, unit = "t")
In case several clusters are neither aligned along a row nor a column, and are all of differing size, the respective information need to be provided at the same index of the respective property. For example, three clusters, where the first cluster starts at (1,1) and is 3 by 4 cells in size, where the second clusters starts at (5,2) and is 5 by 5 cells in size, and so on, needs to be specified as below.
schema <- setCluster(top = c(1, 5, 1), left = c(1, 2, 5), width = c(3, 5, 2), height = c(4, 5, 3), id = ...) %>% setHeader(rows = 1) %>% setIDVar(name = "territories", columns = 1, relative = TRUE) %>% ...
Additionally, given that at least the tables within each cluster are all arranged in the same way, the contained variables can be specified so that their row and column indices are given relative to the cluster position (relative = TRUE). If also that is not the case, the row and column values for each cluster need to be provided for the respective variables in the same way as for cluster positions.
The previous two types of clusters are clusters of identifying variables, but it may also be the case that the observed variables are split up into distinct clusters. Here, we need to specify first of all in clusters "id = "observed" to indicate that the observed variable is the cluster ID. Next, we need to set up the observed variables so that they contain "key = "cluster" and in value the number of the cluster this variable can be found in. Moreover, we provide the column(s) and all rows that contain the values of each observed variable. This is in contrast to nested observed variables, where the rows of the variable names need to be provided.
schema <- setCluster(id = "observed", left = 2, top = c(3, 12), height = 8) %>% setHeader(rows = 1) %>% setIDVar(name = "territories", columns = 2) %>% setIDVar(name = "year", columns = 3) %>% setIDVar(name = "commodities", columns = 4) %>% setObsVar(name = "harvested", columns = 5, unit = "ha", key = "cluster", value = 1) %>% setObsVar(name = "production", columns = 5, unit = "t", key = "cluster", value = 2)
When not all identifying variables can be provided relative to the cluster origin, for example because they are missing for some clusters, it makes more sense to define such a variable as a distinct variable. This is done by providing row and col as absolute values and setting distinct = TRUE. Other variables that are all arranged in the same way in each cluster can be specified so that their row and column indices are given relative to the cluster position (relative = TRUE).
schema <- setCluster(id = "territories", top = c(1, 8, 8), left = c(1, 1, 4), width = 3, height = 6) %>% setHeader(rows = 1, relative = TRUE) %>% setIDVar(name = "territories", columns = 1, row = 2, relative = TRUE) %>% setIDVar(name = "year", columns = 4, row = c(3:6), distinct = TRUE) %>% setIDVar(name = "commodities", columns = 1, relative = TRUE) %>% setObsVar(name = "harvested", unit = "ha", columns = 2, relative = TRUE) %>% setObsVar(name = "production", unit = "t", columns = 3, relative = TRUE)
Wickham, Hadley. 2014. “Tidy Data.” Journal of Statistical Software 59 (10): 1–23. https://doi.org/10.18637/jss.v059.i10.