Skip to content

Data Cleaner

Clean a table one step at a time: missing values, duplicate rows, stray spaces, text in number columns, filters, sorting and rescaling. Every step goes into a recipe you can edit, save and use again, and into a pandas script that gives the same table.

Drop a CSV file here

Columns split by commas, tabs, semicolons, pipes or spaces. The delimiter, decimal commas and the header row are found for you.

.csv.tsv.txt.dat
How-to guide

How to clean a table and keep the recipe

Load a file, add the steps it needs, and take away the cleaned CSV, the recipe and a pandas script that repeats it, without opening Python, R or a spreadsheet.

1

Load your file

Drag a .csv, .tsv, .txt or .dat file onto the drop zone, or press Choose file. The delimiter, a decimal comma and the header row are found for you; change them above the table if the guess is wrong. Load a sample gives you a small file with gaps, a repeated row, stray spaces and a note in a number column to practise on.

2

Add steps

Pick an action, choose the columns and press Add step; the line under the button tells you what it will do before you add it. Suggested for this table offers one-click fixes for what the page spots, such as repeated rows or text with spaces around it. Click a column name in the table to use it in the step.

3

Work with the recipe

Each step is listed with the rows it leaves. Click a step to see the table at that point; switch it off, edit it, move it or remove it, and the table is worked out again from the file. Undo and Redo (Ctrl+Z, Ctrl+Shift+Z) step through your changes.

4

Take it with you

Download the cleaned CSV, Save the recipe to open it here again on another file, or copy the Python script: it reads the file, applies the same steps with pandas, prints the rows after each one and writes the same CSV.

What each step does

Rows

Drop rows with missing values removes rows with an empty cell in the columns you choose (or in any). Remove duplicate rows keeps the first of each set of rows that repeat, comparing the columns you choose; the number 1 and the text "1" are different values. Keep rows where tests one column: more than and the other comparisons only let numbers through, contains ignores case. Sort rows is stable: numbers come first, then text ignoring case, and missing values stay last in either direction.

Values

Fill missing values with a value, the column mean or median (of its numbers), or the value above. Replace values swaps whole cells, such as n.d. for a missing value, or text inside text cells, such as kg for nothing. Trim spaces removes spaces, tabs and line breaks at the ends of text (and can shrink runs inside); text left empty becomes missing. Convert text to numbers turns text such as " 12 " into 12.

Numbers

Round numbers to decimal places or significant figures, as the number is written: 2.675 becomes 2.68, where most software gives 2.67 because the stored value is 2.67499…. Ties go away from zero, or to the even digit if you choose. Transform numbers: log10 and ln (zero and negative values become missing), absolute value, multiply, add, min–max to 0 to 1, and z-scores.

Columns and the script

Rename a column and Remove columns change the table's shape; later steps use the new names. The Python script reads the file with the same settings, runs each step as a commented pandas block and writes the CSV the page downloads, byte for byte: every rule here is written into it.

How a cell is read. A cell is a number when it is ASCII digits with an optional sign, one decimal separator and an exponent, such as 12, -0.5 or 1e-7; an empty cell is missing; anything else, including NA, true and dates, stays text, so a column can hold numbers and text side by side. Whole numbers of 253 and above stay text, so long IDs keep their digits. Mark text such as NA as missing with Replace values.
Method and equations

How the numbers are worked out

Rescaling follows the standard feature-scaling definitions, over the numbers in the column; text and missing cells are left out and left alone.

Min–max rescaling maps a column onto the range [0, 1]:

{x}^{'}=\frac{x-{x}_{\min }}{{x}_{\max }-{x}_{\min }}

A z-score centres a column on 0 with unit standard deviation:

z=\frac{x-μ}{σ},\qquad μ=\frac{1}{N}\sum_{i=1}^{N}{x}_{i},\qquad σ=\sqrt{\frac{1}{N}\sum_{i=1}^{N}{({x}_{i}-μ)}^{2}}

where x is a cell value, {x}_{\min }/{x}_{\max } the column extremes, μ the column mean, σ the population standard deviation (dividing by N, as scikit-learn’s StandardScaler does; pandas’ std() divides by N-1), and N the count of numbers. A constant column maps to 0.

The same result in pandas. Sums are exact (the algorithm of Python’s math.fsum), so a mean does not depend on the order numbers are added in. Logarithms are correctly rounded, since Math.log and C’s log differ in the last bit for about one value in a hundred. Numbers are written as JavaScript writes them (298, 1e-7), and text is compared by code point rather than by the browser’s language. The script does each of these the same way, and the page’s tests run it with pandas to check the two agree cell for cell.

References & documentation

  1. Pedregosa, F., et al. (2011). Scikit-learn: Machine learning in Python. JMLR, 12, 2825–2830. Docs: StandardScaler · Preprocessing guide.
  2. McKinney, W. (2010). Data structures for statistical computing in Python. Proceedings of the 9th Python in Science Conference, 56–61. pandas documentation.
  3. Shewchuk, J. R. (1997). Adaptive precision floating-point arithmetic and fast robust geometric predicates. Discrete & Computational Geometry, 18, 305–363 (the exact sums).
  4. Heckert, N. A., Filliben, J. J., Croarkin, C., et al. (2002). NIST/SEMATECH e-Handbook of Statistical Methods. itl.nist.gov/div898/handbook.

Frequently asked questions

Is my file uploaded to a server?
No. The file is read in your browser and every step runs in memory on your machine. Nothing is sent anywhere.
Does the Python script give exactly the same result?
Yes. It reads the file with the same rules, runs the same steps and writes the same CSV, byte for byte; the page’s tests run it with pandas 2 and 3 on awkward files to check. It needs Python 3.8 or later and pandas, and prints the rows after each step so you can compare them with the recipe.
How do I apply the same cleaning to another file?
Load the other file with Load another file: the recipe stays and runs on it. You can also Save the recipe and open it later, or change INPUT in the Python script. A step that names a column the new file does not have is marked and skipped.
Why is "NA" not treated as missing?
Only empty cells are missing, so a code such as NA, n.d. or -999 is never removed by accident. Use Replace values with an empty replacement to mark it as missing.
How is the z-score calculated?
(value − column mean) / population standard deviation, over the numbers in the column. Text and missing cells are left out of the statistics and left as they are.
Why does the preview show only 200 rows?
Only the first 200 rows are drawn, to keep the page quick; every row is cleaned and goes into the download.
Feature scaling (min–max, standardization) Standard score (Z-score) pandas documentation

Runs entirely in your browser, no data leaves your device.