How do I clean text written many different ways in pandas? strip, lower and a map

In the previous module you made the list of problems and Marisol answered what only she knew. Now you start fixing, and this whole module has one rule: every fix is a line of code that reads the raw data and leaves the clean data. No find-and-replace by hand.

The first problem on the list is the city: 12 ways of writing 4 cities. Before you see the solution, predict what three shortcuts people often try first actually do.

Capitalizing every word

Example 1 of 3

example.py

print("oaxaca de juarez".title())
print("cd. de méxico".title())

What does title() print with these two names?

Why?

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 3, opens once you explain this one

Make your prediction

Three lines, in this order

The recipe has three steps, and the order matters because each one evens out the text for the next:

str.strip() removes the spaces at the start and the end. "cdmx " becomes "cdmx".
str.lower() lowercases everything. "CDMX" and "Cdmx" become "cdmx".
map() with a dictionary that has each form you found and the official name that goes with it.

You decide the first two steps yourself: nobody will argue that "Puebla" and "puebla" are different cities. The map is different. It holds decisions that came from asking, like that in this sheet "oaxaca" means the capital, Oaxaca de Juárez, and not the state. That's why the map is a written, visible list and not a trick buried in the code: anyone can read it and say "not that one".

Why not also strip the accents and be done? Because "oaxaca de juarez" and "oaxaca de juárez" would come out equal, sure, but the official name has an accent, and the report has to have it too. The map already handles both forms without losing anything.

Here's the complete recipe on the sales sheet, one step at a time. Open each step once you've thought about what will come out.

From 12 forms to 4 cities

Worked example · 5 steps

The ciudad column of the March sheet has 12 distinct values for 4 cities. You want a column with each city's official name, and you want to know that no sale was left without a city.

Work out the next step yourself, then open it to compare.

Notice the last step. Without it, the recipe is incomplete: the map works with the forms you saw today, and the April sheet will bring a new one. The blank count after map() is your alarm. If it comes out 0, the map covered everything; if it comes out 1, there's a form you didn't know and you have to decide (or ask) which city it is before you go on.

Does your column have forms you're not sure which official value they belong to? Ask your Maestro how to build the map.

Try it

The member list has the same problem in municipio: there's "pluma hidalgo", "Pluma Hidalgo " with a space, "San Agustin Loxicha" without the accent and "Sta. María Huatulco" abbreviated. The official municipalities are four: San Agustín Loxicha, Candelaria Loxicha, Pluma Hidalgo and Santa María Huatulco. If you can't get it, ask your Maestro for a hint without the answer.

Normalize municipio: strip spaces, lowercase, and apply a MUNICIPIOS map with each form you find. You're done when value_counts(dropna=False) shows exactly 4 municipalities and the blank count for municipio is 0.

Your code (Python)

Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

Run your code to see its output here.

Data: socios-cooperativa, 30 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "socios-cooperativa". Your edits run with your code.
Row							Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 2

Run it yourself

With the column cleaned up, a count of members by municipality finally tells the truth: San Agustín Loxicha 11, Pluma Hidalgo 8, Candelaria Loxicha 6 and Santa María Huatulco 5. Before cleaning, Pluma Hidalgo was split across three rows and San Agustín across two.

Check yourself: In April someone enters a sale with the city "Cd. Mx.". You run the same recipe with the same map. What happens to that sale?

pandas: Series.map with a dictionary