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
  1. ventas_limpias.csv
    the clean table
  Ends here.
- Path 2 of 2: logs
  1. bitacora.csv
    each step, with its rows before and after
  Ends here.

The raw file is never touched: the script reads it and writes two new files.

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 CIUDADES map and the KILOS_MIN and KILOS_MAX limits 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 file) with and writes ventas_limpias.csv and bitacora.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 ventas_abril.csv file is read-only: it's the raw one.

Your turn

Run uv run limpia.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.

Files

"""Clean the cooperative's sales sheet and log every step.

Marisol confirmed the rules below. If a rule changes,

change it here and run the script again.

"""

import pandas as pd

ARCHIVO = "ventas_abril.csv"

# City map: each form, already lowercase, and its official name.

CIUDADES = {

"ciudad de méxico": "Ciudad de México",

"cdmx": "Ciudad de México",

"cd. de méxico": "Ciudad de México",

"oaxaca de juárez": "Oaxaca de Juárez",

"oaxaca de juarez": "Oaxaca de Juárez",

"oaxaca": "Oaxaca de Juárez",

"puebla": "Puebla",

"guadalajara": "Guadalajara",

"gdl": "Guadalajara",

}

# A sale is 1 to 500 kilos.

KILOS_MIN = 1

KILOS_MAX = 500

bitacora = []

def anota(paso, antes, despues, nota):

bitacora.append({"paso": paso, "antes": antes, "despues": despues, "nota": nota})

print(f"{paso:<17} {antes:>3} -> {despues:>3} {nota}")

ventas = pd.read_csv(ARCHIVO)

anota("read", len(ventas), len(ventas), ARCHIVO)

# 1. City: strip, lower and the map. Whatever the map doesn't know comes out empty.

crudas = ventas["ciudad"].str.strip().str.lower()

ventas["ciudad"] = crudas.map(CIUDADES)

sin_mapa = ventas["ciudad"].isna()

anota("city", len(ventas), len(ventas), f"{sin_mapa.sum()} not in map")

if sin_mapa.any():

print("WARNING: the map doesn't know", sorted(crudas[sin_mapa].unique()))

# 2. Amount: remove "$" and ",", convert and count the blanks before and after.

huecos_antes = ventas["monto"].isna().sum()

texto = (ventas["monto"]

.str.replace("$", "", regex=False)

.str.replace(",", "", regex=False)

.str.strip())

ventas["monto"] = pd.to_numeric(texto, errors="coerce")

huecos_despues = ventas["monto"].isna().sum()

anota("amount", len(ventas), len(ventas), f"blanks {huecos_antes} -> {huecos_despues}")

# 3. Discount: Marisol confirmed that empty means 0.

ventas["descuento"] = ventas["descuento"].fillna(0)

anota("discount", len(ventas), len(ventas), "empty is 0, confirmed by Marisol")

# 4. Kilos: the ones that break the rule are flagged, not deleted.

kilos = ventas["kilos"]

ventas["revisar_kilos"] = kilos.notna() & ~kilos.between(KILOS_MIN, KILOS_MAX)

anota("kilos", len(ventas), len(ventas), f"{ventas['revisar_kilos'].sum()} flagged for review")

# 5. Repeated folios: Marisol says the last entry counts.

antes = len(ventas)

# Your turn: sort by captura and drop them with the folio as the key.

anota("repeated folios", antes, len(ventas), "keeps the last entry of each folio")

# Check with a case you know: V-0062 was entered with 95 kilos and then corrected.

v62 = ventas.loc[ventas["folio"] == "V-0062", "kilos"]

print(f"V-0062: rows {len(v62)}, kilos {v62.iloc[0]:.0f}")

print("To review with Marisol:")

print(" kilos outside the rule:", ventas.loc[ventas["revisar_kilos"], "folio"].tolist())

print(" no amount:", ventas.loc[ventas["monto"].isna(), "folio"].tolist())

print(" no kilos:", ventas.loc[ventas["kilos"].isna(), "folio"].tolist())

ventas.to_csv("ventas_limpias.csv", index=False)

pd.DataFrame(bitacora).to_csv("bitacora.csv", index=False)

print(f"Done: {len(ventas)} sales in ventas_limpias.csv and {len(bitacora)} steps in bitacora.csv")

"""Clean the cooperative's sales sheet and log every step.

Marisol confirmed the rules below. If a rule changes,
change it here and run the script again.
"""
import pandas as pd

ARCHIVO = "ventas_abril.csv"

# City map: each form, already lowercase, and its official name.
CIUDADES = {
    "ciudad de méxico": "Ciudad de México",
    "cdmx": "Ciudad de México",
    "cd. de méxico": "Ciudad de México",
    "oaxaca de juárez": "Oaxaca de Juárez",
    "oaxaca de juarez": "Oaxaca de Juárez",
    "oaxaca": "Oaxaca de Juárez",
    "puebla": "Puebla",
    "guadalajara": "Guadalajara",
    "gdl": "Guadalajara",
}

# A sale is 1 to 500 kilos.
KILOS_MIN = 1
KILOS_MAX = 500

bitacora = []

def anota(paso, antes, despues, nota):
    bitacora.append({"paso": paso, "antes": antes, "despues": despues, "nota": nota})
    print(f"{paso:<17} {antes:>3} -> {despues:>3}   {nota}")

ventas = pd.read_csv(ARCHIVO)
anota("read", len(ventas), len(ventas), ARCHIVO)

# 1. City: strip, lower and the map. Whatever the map doesn't know comes out empty.
crudas = ventas["ciudad"].str.strip().str.lower()
ventas["ciudad"] = crudas.map(CIUDADES)
sin_mapa = ventas["ciudad"].isna()
anota("city", len(ventas), len(ventas), f"{sin_mapa.sum()} not in map")
if sin_mapa.any():
    print("WARNING: the map doesn't know", sorted(crudas[sin_mapa].unique()))

# 2. Amount: remove "$" and ",", convert and count the blanks before and after.
huecos_antes = ventas["monto"].isna().sum()
texto = (ventas["monto"]
         .str.replace("$", "", regex=False)
         .str.replace(",", "", regex=False)
         .str.strip())
ventas["monto"] = pd.to_numeric(texto, errors="coerce")
huecos_despues = ventas["monto"].isna().sum()
anota("amount", len(ventas), len(ventas), f"blanks {huecos_antes} -> {huecos_despues}")

# 3. Discount: Marisol confirmed that empty means 0.
ventas["descuento"] = ventas["descuento"].fillna(0)
anota("discount", len(ventas), len(ventas), "empty is 0, confirmed by Marisol")

# 4. Kilos: the ones that break the rule are flagged, not deleted.
kilos = ventas["kilos"]
ventas["revisar_kilos"] = kilos.notna() & ~kilos.between(KILOS_MIN, KILOS_MAX)
anota("kilos", len(ventas), len(ventas), f"{ventas['revisar_kilos'].sum()} flagged for review")

# 5. Repeated folios: Marisol says the last entry counts.
antes = len(ventas)
# Your turn: sort by captura and drop them with the folio as the key.
anota("repeated folios", antes, len(ventas), "keeps the last entry of each folio")

# Check with a case you know: V-0062 was entered with 95 kilos and then corrected.
v62 = ventas.loc[ventas["folio"] == "V-0062", "kilos"]
print(f"V-0062: rows {len(v62)}, kilos {v62.iloc[0]:.0f}")

print("To review with Marisol:")
print("  kilos outside the rule:", ventas.loc[ventas["revisar_kilos"], "folio"].tolist())
print("  no amount:", ventas.loc[ventas["monto"].isna(), "folio"].tolist())
print("  no kilos:", ventas.loc[ventas["kilos"].isna(), "folio"].tolist())

ventas.to_csv("ventas_limpias.csv", index=False)
pd.DataFrame(bitacora).to_csv("bitacora.csv", index=False)
print(f"Done: {len(ventas)} sales in ventas_limpias.csv and {len(bitacora)} steps in bitacora.csv")

Python 3.12· runs with your account

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 check with a case you know: V-0062 was entered with 95 kilos and corrected to 59, and the script kept the correction. 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

To clean May you change one line, ARCHIVO, and run the script again. 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 page's terminal accepts uv run limpia.py and runs the project. 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 script did with V-0062.

pandas: read_csv

uv: running Python scripts