Open the project folder
Choose the folder that holds your own export and a copy of it. The file tools work inside that chat folder.
Workflows
Automated Data Cleaning, One Reviewed Step at a Time
03
A messy export, a script and a new file
Automated data cleaning here means a Python script that applies your written rules to a file and saves the result as a new file. You have one export with stray spaces, repeated rows and text numbers. You keep the script. You can read and rerun it, and the original is never the output.
Describe the rules
Say which columns to trim, which key finds duplicates and what happens to blanks. Plan only lists the steps first.
Run on a copy
Shell runs the script with your Python command and shows the real exit code and the printed row counts.
Review the result
Read the diff, compare the new file with the original and spot-check rows by hand before you accept.
A script small enough to read in a minute
Write clean_customers.py that cleans a few made-up rows and prints how many rows went in and out.
- list_dir
./1 file - write_file
./clean_customers.pysaved - Shell
your Python command clean_customers.pyexit 0 - read_file
./clean_customers.py17 lines
Waiting for your OK before the next stepRejectAccept
clean_customers.py
rows = [
{"email": " Ann@Mail.com ", "age": "34"},
{"email": "ann@mail.com", "age": "34"},
{"email": "Bo@Mail.com", "age": "n/a"},
]
seen, clean = set(), []
for row in rows:
email = row["email"].strip().lower()
if email in seen:
continue
seen.add(email)
try:
age = int(row["age"].strip())
except ValueError:
age = None
clean.append({"email": email, "age": age})
print("rows in:", len(rows), "rows out:", len(clean))It strips spaces, lower-cases the e-mail, skips repeats with a set and turns age text into a number or None. It holds 3 made-up rows. Running it prints 3 in, 2 out.
Two data cleaning projects, step by step
Remove duplicates from a large CSV
To remove duplicates from a large CSV you need a clear key first, for example a normalised e-mail address, because exact-match duplicates miss rows that differ only by spacing or case. Fredit Flow uses grep to search the key column for stray spaces and capital letters, then write_file saves dedupe.py and Shell runs it on a copy of the export with your own Python command. The script keeps the first row for each key and writes the others to a separate file. The original stays. Check the 2 row counts it prints, open the review file and confirm that each pair really is the same customer before you accept the output.
Rerun the script after a second look
Cleaning CSV data is rarely finished in one pass, and the iterations between data cleaning and analysis are normal: a chart shows an odd value, you add a rule and run again. Ask for the change. edit_file updates the script and shows the diff, Shell reruns it on the same copy, and the new run should write to a differently named output file so the first one is kept, which you can check in the diff. Then run it once on a second sample file from the same source. If the exit code or a row count looks wrong, you stop, read the diff again and decide yourself.
