How do I turn my cleaning into a script I can run again?
Put what you did into a script with its log, run it on the April sheet and catch the city form the map doesn't know.
Free course. Your Maestro, an AI, .
It's April. Marisol sends you the month's sales sheet and asks the same question as in March: how many kilos were sold in each city, and where does each number come from?
If you cleaned March by hand, today you start from zero: remembering what you changed, searching and replacing again, hoping nothing slips by. If you cleaned it with code, today you run a file. That's a script: the same steps from the earlier modules, written in order in a file you can run again with a new sheet.
April brings the same entry quirks as March and, almost surely, a new one. That's the test of a good script: not that it works on the sheet it was written for, but that it warns you when something arrives that it doesn't know.
ventas_abril.csv
the sheet exactly as it arrived
limpia.py
Marisol's rules and the five steps
Path 1 of 2: writes
ventas_limpias.csv
the clean table
Ends here.
Path 2 of 2: logs
bitacora.csv
each step, with its rows before and after
Ends here.
What goes inside the script, and what doesn't
A cleaning script isn't a new invention. It's what you already did, put in order:
- The decisions go at the top, as named data. The
CIUDADESmap and theKILOS_MINandKILOS_MAXlimits are at the start of the file. Whoever reads it sees the rules without hunting for them, and changing a rule means changing one line. - The steps go in the order you learned. City, amount, discount, kilos and, last, the repeated folios: text gets cleaned before you look for duplicates, or the duplicates don't show up.
- Every step calls
anota()with the rows from before and after and a note. It's the same ten-second function from the row-counting page. - The raw file is never touched. The script reads
ventas_abril.csv(a Glossary term file) with Glossary term and writesventas_limpias.csvandbitacora.csv.
And what the script does not decide: when something doesn't fit, it doesn't guess. A city form the map doesn't know comes out empty and the script says so out loud. An amount that says "pendiente" comes out empty and the blank count goes up. A kilo value outside the rule is flagged, not deleted. What the script leaves unresolved ends up on a list for Marisol.
Is there a cleaning you repeat every month at your job? Tell your Maestro what steps you take and in what order.
Try it
The project below already has the steps for city, amount, discount and kilos. Two things are missing, and the script's output tells you which. The input type of monto is nullable text: .str will also work if another month has only amounts such as 100, without turning missing values into the word nan. The ventas_abril.csv file is read-only: it's the raw one.
Your turn
Run uv run revisar.py in the terminal and read the output. There are two things to fix. First, the WARNING line: Marisol confirms that "Cd. Mx." is Mexico City; add that form to the CIUDADES map, in lowercase like the others. Second, the repeated-folios step doesn't do anything yet: write Marisol's rule there (sort by captura and keep the last one for each folio). You're done when the log says 20 -> 18 on the repeated-folios step, no WARNING line appears anymore and V-0062 ends up with 59 kilos. revisar.py is read-only: it runs your script and checks both files, their folios and the log. A success message without the files does not pass this check.
Enter runs · ↑ ↓ history · → accepts · helpCtrl+Enter runs · Tab indents · Esc then Tab leaves · Ctrl+` terminal
When the script runs all the way through, the output has three parts. The log, one line per step: there you see that no text or type step changed the number of rows and that the repeated-folios step went from 20 to 18, the two extra copies. The general checks: unique folios, preserved folios and columns, and the latest entry retained. Then revisar.py checks the April files, including V-0062: it was entered with 95 kilos and corrected to 59. And the list for Marisol: V-0071 with 700 kilos, V-0072 with its amount "pendiente" and V-0070 with no kilos.
The script doesn't fix that list, and that's right. They're three short messages for whoever knows the data, with folio and amount, like the one on the blanks page. Your part was making sure none of those three sales disappeared from the table in silence.
Next month
For another month, change ARCHIVO, confirm the rules still hold and choose two or three control cases from that file. Run uv run limpia.py: that is the reusable program. revisar.py checks only the April exercise and should not require its folios to exist in May. Reopen the May files and check the new control cases. If May brings a new city form, the WARNING line shows it to you: you ask, add an entry to the map and run it again. It's the same blank count from the text page, now inside a file you won't forget.
You can download the project and run it on your own computer. Here, the exercise is checked with uv run revisar.py; you can also run the program with uv run limpia.py. On your computer, uv run comes from uv, a separate tool that runs the script and takes care of its dependencies so you don't set up the environment by hand (its documentation is linked below). Python for Data People, the course on the last page, teaches you how to set up that environment.
If something in the output doesn't add up, ask your Maestro to walk you through it step by step.
Check yourself: You run limpia.py with the May sheet. It finishes with no error, no WARNING line and "Done" at the end. What do you conclude?
Tip: Before you send the number
A cleaning that runs on its own also makes mistakes on its own. The first time you use a script with a new sheet, pick two or three sales you know by heart and look up which copy they ended up with, as the April verifier did with V-0062. Choose folios from the new file; do not blindly reuse the April ones.