Why does pandas count "CDMX" and "cdmx " as different cities?
Predict how many cities pandas counts in a column typed by hand, and learn the five steps you'll use all course long.
Free course. Your Maestro, an AI, .
A coffee cooperative on the coast of Oaxaca sells to cafes, hotels and shops in four cities. Marisol, who handles sales, sends you the March sheet with a simple question: how many kilos did we sell in each city? Three people filled in the sheet by hand during the month, and it shows.
In this course you'll clean that sheet and the cooperative's member list, first in pandas and then in SQL, until you have a script you can run again next month. The cooperative, Marisol and all the data are made up for the course (synthetic data), but every problem you'll run into is the kind that shows up in real work sheets.
Before we start, three quick questions. Answer with what you think today, without looking anything up: the same three come back at the end of the course so you can compare.
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?
Now the first sheet. Below are six sales with the city exactly as someone typed it. Before you run the code, write down what number you think it prints and why.
How many distinct cities?
Example 1 of 2
import pandas as pd
ventas = pd.DataFrame({
"ciudad": ["CDMX", "Ciudad de México", "cdmx ", "CDMX", "Puebla", "puebla"],
"kilos": [40, 25, 10, 30, 50, 20],
})
print(ventas["ciudad"].nunique())
A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.
Example 2 of 2, opens once you explain this one
What the computer sees
You read "CDMX", "Ciudad de México" and "cdmx " and think of one place. The computer compares text letter by letter, and any difference makes a different value:
- Capital and lowercase letters: "Puebla" and "puebla".
- Spaces you can't see: "cdmx " with a trailing space, " Guadalajara" with a leading one. On screen they look the same; in a comparison they don't.
- Accents: "Oaxaca de Juárez" and "oaxaca de juarez".
- Abbreviations and nicknames: "Cd. de México", "CDMX", "GDL".
- Names that can mean two things: "Oaxaca" can be the capital city or the whole state. No code decides that; whoever entered the sale knows.
In the full March sheet, the ciudad column has 12 ways of writing 4 cities. Any count, sum or chart by city comes out split into pieces, and the worst part is that it looks fine: tidy rows, round numbers, no error.
Do you have a column like this at work? Tell your Maestro what it looks like and what report comes out of it.
This course's method
The temptation is to open the sheet in Excel, search for "cdmx", replace and move on. It works once. Next month another equally messy sheet arrives, nobody remembers what was changed, and if a replace took out something it shouldn't have, there's no way to know. That's why in this course you'll always work in five steps, in this order:
Look without changing anything
Count the values in each column, the blanks and the types. Write the list of problems before you fix the first one.
Decide what each problem means
Some things you decide yourself, like that "puebla" is Puebla. Others only the person who knows the data can say: whether "Oaxaca" is the capital or whether a blank is zero. You ask that person.
Fix it in code
Every change is a line you can read and run again. The original file is never touched: the code reads the raw one and writes a clean one.
Count the rows
Before and after every step. If a step that should only change text leaves you with fewer rows, something got lost along the way.
Write down what changed
A log with every step: what it did, how many rows there were before and after, and why. That way you can answer "where did this number come from?" three months from now.
In the rest of this module you practice the first two steps: look and decide. In the second module you fix things in pandas and count rows, in the third you take on duplicates, in the fourth you do the same in SQL, and in the last one you put it all together in a script with its log.
One idea to hold on to from today: cleaning isn't making the table pretty, it's making it true and being able to explain every change. A table that looks tidy but lost the sales that mattered is worse than the messy sheet, because nobody distrusts it anymore.
Try it
One last question to close the page. If you're torn between two answers, ask your Maestro what separates them.