cleaning data

Reshaping the table

Turn columns into rows and rows into columns, turn the whole table on its side, summarize a table with one row for each group, give each value of a cell a row of its own, and put those rows back together.

Six commands leave the table with another shape: other columns, or other rows. The first four are in the Table menu, and the last two in the Rows menu.

Command What it does
Columns to Rows… turns several columns into two, one with their names and one with their values: a column for each month becomes a row for each month
Rows to Columns… the way back: each name of a column becomes a column, with its values under it
Swap Rows and Columns turns the whole table on its side: each row becomes a column, and each column a row
Summarize… makes one row for each group, with the other columns counted, added up or averaged
Split Values into Rows… gives each value of a cell that holds several a row of its own
Combine Rows… the way back from that one: makes one row of the rows that only differ in one column

Each one but Swap Rows and Columns, which has nothing to choose, opens a window with its choices and the first rows of the result, and says how many rows there will be. Nothing changes until you press its button, and one Ctrl+Z⌘Z puts the table back as it was.

They take every row of the file. With a filter on, that includes the rows it leaves out, and the window says so: a table with some rows in one shape and the rest in another would be broken. Columns to Rows…, Rows to Columns…, Swap Rows and Columns and Summarize… clear the filter too, since it was a filter of columns that are no longer there.

Columns to rows

A table with a column for each month, each year or each product (a wide table) is easy to read and hard to work with: to filter by month, sort by it or add the months up, the month has to be a value, not a column. Table › Columns to Rows… does that, and leaves a long table: other programs call it unpivoting, or melting. It takes these rows

city,jan,feb,mar
Madrid,10,12,9
Lugo,3,,5

and makes these of them:

city,name,value
Madrid,jan,10
Madrid,feb,12
Madrid,mar,9
Lugo,jan,3
Lugo,feb,
Lugo,mar,5

Select the columns first, two or more: click a column’s number in the bar above the rows and Shift+Click⇧Click the last one, or Ctrl+Click⌘Click each of the others (see Selecting columns and rows). Then:

  • Names go in and Values go in: the names of the two new columns, name and value unless you type others (month, sales). They take the place of the first of the columns you selected.
  • Leave out empty values: an empty cell gives no row, so Lugo,feb, above wouldn’t be there. A row whose values are all empty goes altogether.

The other columns are repeated in each new row, exactly as they were written. In a file without a header row the names are Column 2, Column 3…

The Columns to Rows window for the columns jan, feb, mar and apr: Names go in month, Values go in sales, Leave out empty values unticked, and the first rows of the result, Madrid jan 10, Madrid feb 12, Madrid mar 9, Madrid apr 14, Lugo jan 3; 3 rows become 12

Rows to columns

Table › Rows to Columns… goes the other way, from the long table to the wide one (pivoting, in other programs): it takes the second table above and gives back the first. Each different value of one column becomes a column, with the values of another column under it, and rows next to each other that are the same in every other column become one row.

  • Names from: the column whose values will be the names of the new columns (name above, with jan, feb and mar).
  • Values from: the column whose values go under them (value).

The window proposes name and value if the table has columns called that; if not, the column of the cursor and the one after it. Every other column says which row a value belongs to: Madrid’s three rows become one because they are the same in city. So a column that differs in every row (a row number, a date of entry) keeps the rows apart, and each one ends up with a single value: delete that column first, or see Summarizing.

The new columns go where the column of names was, in the order their names first appear, and a combination the file doesn’t have (Lugo in February, if that row were missing) is an empty cell.

Rows that are the same in the other columns but not next to each other stay rows of their own: that is usually a coincidence. In a table of election results with the columns group, category, candidate and votes, turning group into columns, one candidate may have 28 % among voters aged 18 to 29 and, further down, 28 % among Hispanic voters; those are two rows, each with its own category, not one. When the other columns do say which row a value belongs to but the file is written one name after another (every city for January, then every city for February), tick Rows that are the same in the other columns become one row even when they aren’t next to each other. The window shows that box only when the file has rows like that, and says how many there are.

More than one value says what a cell holds when several rows give it a value: two rows for Madrid in January.

  • Join them with “; ”: the cell holds them all, in order, so nothing is lost; the window says how many cells are like that. It is the one to begin with.
  • Keep the first one or Keep the last one: only that value.

To add those values up or count them instead, use Summarize first, grouping by the other columns and the column of names. Empty values never count as one of several.

A column whose values are nearly all different (an id) would make a column for each: past 200 the window says so and does nothing, since that is nearly always the wrong column. In a file without a header row the names have nowhere to be written: the columns are in the order their names first appear.

This isn’t turning the whole table on its side, with every row a column: that is Swap Rows and Columns.

Swapping rows and columns

Table › Swap Rows and Columns turns the whole table on its side (transposing, in other programs): the first row becomes the first column, the first column becomes the first row, and every value goes from row 3, column 5 to row 5, column 3. It takes these rows

city,jan,feb,mar
Madrid,10,12,9
Lugo,3,,5

and makes these of them:

city,Madrid,Lugo
jan,10,3
feb,12,
mar,9,5

It is for a table written the other way round: one row for each measure and one column for each thing measured, or a form exported as a single row that reads better as a list. To make the months values of a column instead, so that you can sort, filter or add them up, use Columns to Rows: it keeps city as a column and gives each month a row of its own.

It has nothing to choose, so it doesn’t open a window: it swaps the table at once and says how big it was and how big it is (the header row counts as a row, since it becomes a column). The cursor goes to the start of the file, and one Ctrl+Z⌘Z swaps it back.

  • Every value is written as it was, quotes and all, so a value of several lines moves whole.
  • A row shorter than the others gives empty cells; blank lines aren’t rows, and go.
  • The header row stays a header row: with Table › First Row Is Header on, the names of the new columns are the values of the old first column (Madrid, Lugo above).
  • Each row becomes a column, so it works on tables of up to 1,000 rows. With more, it says so and does nothing.

Summarizing

Table › Summarize… answers the questions that start with “how many” or “how much”: orders per country, sales per month, the last visit of each customer. It makes one row for each group, a group being the rows that have the same value in the columns you group by, and the result takes the place of the table.

The window lists every column, with what to do with it:

Choice The column holds, for each group
Group by its value: there is a row for each value (or for each combination of values, with several columns)
Leave out nothing: the column isn’t in the result
Count values how many of its cells have a value
Count different values how many different values it has
Sum, Average the sum or the average of its numbers
Minimum, Maximum its lowest or highest value: as numbers in a column of numbers, as dates in one of dates (their times too, and however each is written), and in alphabetical order otherwise
First value, Last value the first or the last value that isn’t empty
All values its different values, in order, separated by commas

The column of the cursor starts as Group by (or the columns you selected in the bar above the rows) and the others as Leave out; Add a column with the number of rows, ticked to begin with, adds a count column. So, as it opens, the window is already the count of each value of a column: put the cursor in country, open it, and press Summarize.

The Summarize window for a table of cities: country set to Group by, population to Sum and the other columns to Leave out, Add a column with the number of rows ticked, and the first rows of the result, Japan 56082000 2, India 70813000 3, China 51634000 2; 30 rows become 21

A few things to know:

  • Values group when they are written exactly the same: Madrid and madrid are two groups. Tidy them first, with Quality Check or Group Similar Values.
  • The groups come in the order they first appear in the file. To order them by a result, sort afterwards (see Sorting rows).
  • The columns keep their names and their order; the count goes last.
  • Numbers are read with the decimal separator of their column, and the results are written with it: the sum, with as many decimals as the values have; the average, with two more. Currency symbols and units are read past and not written ($1,200 and $300 add up to 1500).
  • Empty cells and missing values (NA, -…) never count, and aren’t part of a sum or an average.
  • Without any column to group by, the result is one row for the whole table: its totals.

The result replaces every row of the file. It is one step of Ctrl+Z⌘Z; to keep both, the table and its summary, save the summary with another name with File › Save As… and undo.

A cell’s values to rows

Some files keep a list in a cell: red; green; blue in a colours column, three e-mail addresses in one. Rows › Split Values into Rows… gives each value its own row, with the other columns repeated:

id,colours
1,red; green
2,blue

becomes

id,colours
1,red
1,green
2,blue

It works on the cursor’s column. Separator is the text between the values; the window proposes the one most cells have. Type any other: a comma, |, \t for a tab, or \n for the line breaks of a cell with one value per line. As in Split Column, a dash stands for any dash. The spaces around each value are removed, and an empty part (red;;green) isn’t a value.

Rows whose cell has one value, or none, stay as they are.

Repeat the other columns in each new row is ticked to begin with: every new row is a whole row, which is what sorting, filtering and counting need. Untick it and only the first row of each keeps the other columns; the ones after it have them empty, the way a list is written in a report:

id,colours
1,red
,green
2,blue

To give each value a column instead of a row, see Split Column.

Combining rows

Rows › Combine Rows… goes the other way. The rows that are the same in every column but the cursor’s become one row, the first of them, with their values of that column together, in order, with the text you choose between them (Between them, ; to begin with):

id,name,colour
1,Ana,red
2,Luis,blue
1,Ana,green

with the cursor in colour becomes

id,name,colour
1,Ana,red; green
2,Luis,blue

The rows don’t have to be one after another. A value that repeats is written once, and empty ones are left out. Rows that differ in any other column, also by a space or a capital, are different rows and stay apart: the window says how many rows will become how many, and shows the first ones.

Rows that only have the value

Some files don’t repeat the other columns: the first row of each list has them, and the rows under it have only the value.

id,name,colour
1,Ana,red
,,green
2,Luis,blue
,,black

Rows that are empty in the other columns go with the row above, ticked to begin with, reads them that way: green is Ana’s and black is Luis’s, and the result is 1,Ana,red; green and 2,Luis,blue; black. The row above is the last one before it that has something in another column. The window says how many rows went with the one above them.

Unticked, those rows are rows like any other, and since all of them are the same in the other columns (empty), every one in the file ends up in a single row. That is rarely what you want; untick it only when a row without the other columns really is a thing of its own.