Tidying data

Tidying data is the practice of turning untidy data into tidy data, and is a necessary step in the preparation of data visualisation based on the Grammar of Graphics. Below are some of the more common operations for tidying data.

Separating data

This table contains 2 variables in the rate column:

To make the table tidy, the values in the rate column need to be split into 2 new columns. This operation is often called separating or spreading a column into new columns. In order to separate or spread a column, you need to provide the character (or characters) that are used as the separator between the values in the column. In this case, the separator is the forward slash “/”.

After separating the values in a column, you need to give the newly created columns new column names (which are the variable names of the created tidy data set).

Making data wider

The table below is untidy, because the count column contains the values for 2 variables (cases and population).

To make the data tidy, the values in the count column need to be organised in two columns, based on the values in the type column. This operation is called making data wider, or pivot data wider.

Making data longer

Consider the following untidy data table:

This table is untidy, because the 2020 and 2021 column names are values of the year variable, so this variable is spread over multiple columns. The tidy version of this table looks like this:

The operation of taking the names of multiple columns and “collapsing” them into a single column (in this case this is the year column) and putting the values of the original columns into another column (this is the cases column here) is called making data longer, pivotting data longer or unpivotting data).

This operation usually results in tables that are longer (with more rows, and less columns) than the original table. You can read more about wide and long data in Wide versus long data.

Transposing data

Below is another example of an untidy data table:

This table is untidy, because it has the variables as rows and the observations in the columns, instead of vice versa. The operation to make this data table tidy is called transposing a table. Transposing a table will make rows out of the columns, and columns out of the rows.