cleaning data

Columns

Insert, duplicate, delete and move columns, header included, in every row at once; split one in several, join several in one, number the rows, and tidy up the column names.

The Columns menu changes the columns themselves, in every row, the header included:

Command What it does
Insert Column Left / Insert Column Right adds an empty column before or after the cursor’s, and puts the cursor in its header cell, ready to type its name
Insert Row Number Column adds a first column that numbers the rows 1, 2, 3…
Duplicate Column adds a copy of the column after it; in the header, its name followed by copy
Delete Column removes the column, its values and its delimiter
Delete Other Columns removes every column but the cursor’s, or but the ones the selection touches
Move Column Left / Move Column Right swaps the column with the one beside it (Alt+←⌃⌥← / Alt+→⌃⌥→); the cursor goes with it, so pressing the keys again takes the column one place further
Split Column… cuts each value at a separator, into columns of their own
Join Columns… puts the values of several columns together in one
Merge Columns… keeps, of several columns, the first value that isn’t empty
people.csv
id,name,city,country
1,Ada Lovelace,London,United Kingdom
2,Grace Hopper,New York,United States
3,Alan Turing,Manchester,United Kingdom
4,Katherine Johnson,Hampton,United States
5,Hedy Lamarr,Vienna,Austria
6,Margaret Hamilton,Boston,United States
7,Tim Berners-Lee,London,United Kingdom
8,Linus Torvalds,Helsinki,Finland
9,Barbara Liskov,Los Angeles,United States
10,Frances Allen,Peru,United States
11,Edsger Dijkstra,Rotterdam,Netherlands
12,Radia Perlman,Portsmouth,United States
13,Ken Thompson,New Orleans,United States
14,Annie Easley,Birmingham,United States
15,Shafi Goldwasser,New York,United States
16,Donald Knuth,Milwaukee,United States
17,Sophie Wilson,Leeds,United Kingdom
18,Ivan Sutherland,Hastings,United States
19,Karen Spärck Jones,Huddersfield,United Kingdom
20,Jean Sammet,New York,United States
city · Row 1

They act on the cursor’s column or, with a selection, on the columns it touches: select across three columns and Delete Column removes the three, Delete Other Columns keeps only those three, Move Column Right moves the three together, Duplicate Column copies them after the last one, and the insert commands add one column before the first or after the last.

A column’s own menu has the insert commands and Delete Column too: the three dots where the column ends in the column bar above the rows, or a right click on its number. From there they act on that column, wherever the cursor is: the new column goes beside it. If you have selected several columns in the bar and open the menu of one of them, it says Delete 3 Columns and removes them all.

To take a column further than a place, drag it in the column bar: click its number to select it, then drag the number to where it should go. A line shows where it will land, and it lands there in one step, with the other selected columns, if any, beside it. See Dragging columns.

Rows that are too short for the operation (a row with three values when the column is the fifth) are left as they are. Like every operation, each one is one step of Ctrl+Z⌘Z.

To empty a column’s values but keep the column, see Clear Cells. To see a column that’s far to the right, Ctrl+Shift+O⇧⌘O goes to it by name.

Splitting a column

Columns › Split Column… cuts each value of the cursor’s column at a separator and gives each part a column: Ana Gómez at the space becomes Ana and Gómez; red; green; blue at the ; becomes three columns.

  • Separator: the window proposes the one most values have. Type any text: a comma, a space, -, or \t for a tab. A dash stands for any dash: - also cuts 3–5 or 3—5, which look the same but use the longer dashes that word processors and online forms put in.
  • Columns: as many as the value with most parts needs, or a number of your choice: with 2, Ana María Gómez becomes Ana and María Gómez, the last column keeping what’s left.
  • Keep the column too: the new columns are added after it instead of taking its place.

The spaces around each part are removed, values with fewer parts get empty cells, and the new columns are named after the old one: name 1, name 2… The window shows a few values as they will be cut before you press Split.

The Split Column window on the column timezone: separator “/”, Columns as many as it takes, and the values as they will be cut, Asia/Tokyo → Asia,Tokyo, America/Mexico_City → America,Mexico_City; 3 columns in place of “timezone”, 30 values of 30 have the separator

Joining columns

Columns › Join Columns… does the opposite: it writes the values of several columns in the first of them, with the text you choose between them, and removes the others. Select the columns first, two or more: click a column’s number in the bar above the rows and Ctrl+Click⌘Click the others (see Selecting columns and rows), or put a cursor in each of them. They don’t have to be side by side: the columns in between stay as they are. The values go in the order of the columns, from left to right; to join them in another order, move the columns first (Move Column Left, Move Column Right). Empty values are left out, so that Ana, nothing and Gómez give Ana Gómez and not Ana Gómez; untick Leave out empty values to keep every separator. The names in the header row are joined the same way: change the result to the name you want.

Columns › Merge Columns… is for columns that hold the same thing: phone in some rows and mobile in others, or two columns that should have been one after putting files together. It takes the selected columns, as Join Columns does. Each row keeps the first value that isn’t empty, in the first column, which keeps its name; the other columns are removed. Before you press Merge, the window shows rows where the value comes from another column, how many there are, and in how many rows the columns have different values: there the first one stays and the others are lost. Ctrl+Z⌘Z brings the columns back if those values mattered.

Row numbers

Columns › Insert › Row Number Column numbers the rows in a new first column, named row in the header (or row_2, if there is a row already). The name is selected, so that you can type another one straight away. Empty lines aren’t rows: they stay empty and don’t take a number.

Numbering the rows before sorting them keeps a way back to the original order: sort by the row column to get it. It’s also a key for rows that have none.

Column names

Columns › Header Names renames every column of the header row at once:

Command Customer Name, E-mail Address, orderID become
Names to snake_case customer_name, e_mail_address, order_id
Names to camelCase customerName, eMailAddress, orderId
Make Names Unique the same names, unless some are empty or repeated

snake_case and camelCase are what databases and programming languages expect: words in lower case, joined by _ or by a capital letter. The words are split at spaces, punctuation and changes of case, apostrophes are dropped (Customer’s ID becomes customers_id), and accents stay: to remove them as well, select the header row and run Remove Accents.

Make Names Unique leaves the names that are fine as they are. A name that repeats an earlier one, also with other capitals, gets a number (name, name_2), and an empty name is called after its place (column4), never with a name that’s already taken. Programs that read a CSV file into a table usually need every column to have a name of its own.

A header written in two rows or more, as spreadsheets export it, becomes one with Join Header Rows…: see A header of several rows.

The first three are enabled only when the first row is the header (Table › First Row Is Header). To change the names in any other way, edit the header row as text, or select it and run an operation such as Change Case.