user guide

Cleaning data

The column profile, the problems of a file and their fixes, sorting and filtering, and the Cells, Columns, Rows and Table menus: spaces, case, characters, similar values, numbers and dates, empty and repeated values, duplicates, columns, reshaping the table and putting files together.

  • How data operations workThe Cells, Columns, Rows and Table menus change what you select, a whole column with just a cursor, in one step you can undo, and tell you how much they changed.
  • Column profileA panel at the side with what a column holds: its type, empty and missing values, distinct values, figures and a histogram for numbers and dates, and every value with how many cells have it, to select or correct them all at once.
  • Quality Check and quick fixesA panel that reads the whole file and lists what is wrong with it (rows cut, repeated or too short, empty columns, zeros lost in a spreadsheet, text with a broken encoding, columns that write numbers, yes/no values or countries in several ways), with a key to go through them and a fix for most.
  • Filtering rowsBring together the rows you want to work on, by the values of one column or of several. The other rows aren’t removed: they wait in grey after the last row, and cleaning or sorting leave them alone.
  • Finding the cells to fixSelect the empty cells of a column, its repeated values, the values of another type, the longest ones or those with unusual characters, and then fix just those.
  • Whitespace and caseTrim the spaces around values, squeeze the ones between words, remove them all, and put text in upper, lower or title case.
  • Letters, digits and other charactersRemove letters, digits, punctuation or accents, straighten curly quotes, decode HTML entities and get rid of invisible characters.
  • Grouping similar valuesFind the values of a column that are the same one written in different ways (capitals, accents, spaces, typos) and merge each group into one value.
  • Numbers and datesWrite the numbers of a column plainly, with one decimal separator and no thousands separators, and its dates in a single format, whatever way each one was written.
  • Empty and repeated valuesFill the gaps in a column with the value above, empty the values that repeat the one above, fill empty cells with your own text, turn NA, null and the like into empty cells, and remove empty rows.
  • Sorting rowsSort the rows by one column or by several, as numbers when a column holds numbers, as dates when it holds dates and in natural order when it holds text, or by the length of its values.
  • Duplicate rowsFind out how many rows repeat another, whole or by one column, exactly or ignoring capitals and spaces, and remove them keeping the first.
  • ColumnsInsert, 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.
  • Reshaping the tableTurn 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.
  • Putting files togetherAdd the rows of other files after the ones of this one, matching their columns by name, or bring columns from another file beside its rows, matched by an id.
  • RecipesA recipe is the operations you did on one file, saved to do them again on another, such as the export that arrives every week, or on a whole folder of files. Recipes come with the desktop app, which is on its way.
  • Count, sum, average, minimum and maximumSelect some cells and the status bar adds them up, as a spreadsheet does.