Can you clean data without losing rows? Check yourself and pick what's next
Answer the three opening questions again, try three new cases and pick your next step.
Free course. Your Maestro, an AI, .
At the start of the course I asked you three questions about a sales sheet. Here they are again, word for word. You don't need to remember what you answered then: answer with what you think today.
After that come three questions with new tables, and at the end, where you can go from here.
Check yourself: In the sales sheet, 44 of 60 rows have an empty `descuento` (discount). What do you need before filling them in?
Check yourself: You have 60 rows of sales and you run `ventas.drop_duplicates()`. It returns 60. What can you conclude?
Check yourself: You convert the `monto` (amount) column from text to numbers and the code runs with no error. What do you know?
Three new cases
Now the same ideas in situations you haven't seen yet: a rule the sale doesn't fit, a SQL query and two members with the same name.
Check yourself: In the April sheet, sale V-0071 says 700 kilos and 77,000 pesos. Marisol's rule is 1 to 500 kilos. What do you do in your script?
Check yourself: You want to keep the latest entry for each folio, in SQL. Which query does it?
Check yourself: In the member list there are two members named José Luis Pérez Santiago: one from San Agustín Loxicha and another from Candelaria Loxicha. What do you do?
What you can do now
When you started, "CDMX", "Ciudad de México" and "cdmx " were three cities and a report with no error. Now you can:
- Look before you change. Take an inventory of a table with counts, blanks and ranges, and write the list of problems before fixing the first one.
- Tell what you decide from what someone else answers. That "puebla" is Puebla is yours to decide; what a blank means, or whether "Oaxaca" is the capital, is something you ask, and the answer ends up written in the code.
- Fix in code, not by hand. Text with a map, numbers with a conversion that counts what it lost, impossible values with a flag, duplicates with a key and a rule.
- Always count. Rows before and after every step, and a note that answers "where did this number come from?" three months from now.
- Do the same in SQL, with
TRIM,LOWER,CASE,TRY_CAST,COALESCEandROW_NUMBER. - Put it all in a script you can run again on next month's sheet.
What this course didn't cover: dates, files with broken accents, approximate matching between similar names, and joining tables. Those are separate topics, and the method here (look, decide, fix in code, count, write it down) stays the same in all of them.
Tell your Maestro about a table from your job and build your problem inventory with them, plus the list of questions for the person who knows the data.
Think of a table at your job that someone once cleaned by hand. What decision was made there without asking anyone, and which number in a report changes because of it?
Reflect: Think of a table at your job that someone once cleaned by hand. What decision was made there without asking anyone, and which number in a report changes because of it?
Only you and your Maestro read this. It's kept on your account and deleted with it.
Where to go next
This course is complete on its own: with what you've done, you can already clean your work tables carefully. If you want to go further, two Maestro courses start where this one ends.
Python for Data People: From Notebooks to Real Programs. Your limpia.py is a script that reads one file and writes two others. That course teaches what surrounds it: functions so you don't repeat steps, how to read an error without panicking, files and paths, your own modules and a project environment to run the script on your computer. You already meet what it asks for: you work with data and you wrote a pandas script.
SQL for Data People: Think in Tables, Not Spreadsheets. If your data lives in a database, that course builds out what you used here in pieces: filters, groupings, joins between tables, CASE, subqueries and CTEs, and how to create and change data carefully. You already meet what it asks for: you read and wrote queries with SELECT, WHERE and GROUP BY.
Pick the one that matches where your data lives: files and notebooks, the Python one; a database, the SQL one.