You want to know how many customers you have in each country. It sounds like one of the simplest questions you can ask about a spreadsheet.
You count the values in country, and Poland comes out on top with 156 customers. The result looks reasonable. Poland is still your largest market, the chart looks fine, and there is nothing obvious that would make you question the number.
But the real number is 201.
The other 45 Polish customers are already in the file. They are recorded as POLAND, poland, PL, and Polska, so a simple count treats them as separate categories.
We could ask AI to clean the entire column in one prompt, but in this tutorial we will work step by step. First, we will inspect every value that appears in the data. Then we will standardize the names. Finally, we will count the countries again and check that every record is still accounted for.
This keeps a person in the analysis loop. We can review which labels actually mean the same thing instead of trusting an automatic text transformation blindly.
You can open the full MLJAR Studio conversation to inspect every prompt, generated Python cell, and output used in this tutorial.
Get the data
Download the demo file from datasets-for-start and save it in:
~/Documents/customers/
The file is called customers.xlsx.
It is a sample customer list with several deliberately messy parts. In this tutorial, we will focus only on country.
The column contains six real countries, but some names are written in different ways. There are differences in capitalization, abbreviations, punctuation, and language. These differences are small when you read the spreadsheet, but they matter when Python groups, filters, joins, or counts the values.
Open the Excel file
Start MLJAR Studio and choose Start a New AI Conversation.
Click Read a file at the bottom, choose Read file from computer, and select customers.xlsx.
Reading is not uploading. MLJAR Studio opens
customers.xlsxdirectly from your disk. The generated Python loads the workbook and performs the category analysis locally on your computer.
Studio reads the workbook and shows a preview.
The result tells us:
Shape: (530, 7) Columns (7): ['customer_id', 'full_name', 'email', 'country', 'signup_date', 'plan', 'monthly_spend']
We have 530 rows and seven columns. Before changing country, let us see exactly what is inside it.
Look at the existing country values
Ask Studio:
show unique values in the country column with counts
Studio generates:
country_counts = ( df["country"] .value_counts(dropna=False) .rename_axis("country") .reset_index(name="count") ) country_counts
The result contains 21 labels:
| country | count | country | count |
|---|---|---|---|
| Poland | 156 | germany | 6 |
| Germany | 88 | ES | 6 |
| France | 66 | IT | 5 |
| Spain | 45 | GERMANY | 5 |
| United States | 44 | Polska | 5 |
| Italy | 31 | italy | 3 |
| POLAND | 17 | U.S. | 3 |
| poland | 13 | USA | 2 |
| PL | 10 | DE | 1 |
| FR | 9 | ||
| france | 8 | ||
| spain | 7 |
There are 21 labels but only six actual countries.
Poland alone appears in five forms:
Poland POLAND poland PL Polska
Adding their counts gives us the real total:
156 + 17 + 13 + 10 + 5 = 201
Poland has 201 customers, not 156.
Inspecting the values first tells us what the mapping must cover. If we wrote it from memory, we might remember PL and poland but overlook Polska until it appeared in a report as a separate category.
Standardize the country names
Now that we know which labels are present, ask Studio:
standardize the country column to full country names
Studio generates:
country_map = { "poland": "Poland", "pl": "Poland", "polska": "Poland", "germany": "Germany", "de": "Germany", "france": "France", "fr": "France", "spain": "Spain", "es": "Spain", "italy": "Italy", "it": "Italy", "united states": "United States", "u.s.": "United States", "usa": "United States", } normalized_country = ( df["country"] .astype("string") .str.strip() .str.casefold() ) df["country"] = normalized_country.map(country_map).fillna( df["country"].astype("string").str.strip() ) df["country"].value_counts( dropna=False ).rename_axis("country").reset_index(name="count")
Now the result is much simpler:
| country | count |
|---|---|
| Poland | 201 |
| Germany | 100 |
| France | 83 |
| Spain | 58 |
| United States | 49 |
| Italy | 39 |
We now have six country names, and all 530 rows are still present. We did not remove customers or change which country they belong to. We made different representations of the same country consistent.
How the standardization works
There are three useful ideas in the generated code.
First:
.str.strip()
This removes spaces from the beginning and end of a value. A cell containing Poland with an invisible trailing space may look identical to Poland in Excel, but Python sees different strings.
Next:
.str.casefold()
This normalizes capitalization, so POLAND, Poland, and poland all become poland before the mapping is applied. You may also see .lower() used for this task. casefold() is designed for stronger text normalization and handles some international characters better.
Finally:
.map(country_map)
This looks up each normalized value in our reviewed dictionary. pl, polska, and poland become Poland; de and germany become Germany.
The dictionary is explicit and reviewable. Another person can open the notebook and see exactly which source labels become which standard names.
Keep unknown values instead of deleting them
One part of the code is especially important:
.fillna(df["country"].astype("string").str.strip())
When .map() encounters a value that is not in country_map, it returns a missing value. Without .fillna(), any country we forgot would quietly become empty.
With this fallback, an unknown value keeps its original text. If next month's export contains Portugal and Portugal is not yet in the mapping, Portugal remains visible. We can review it, decide whether it is valid, and update the dictionary if needed.
This is a safer failure mode than deleting information because the code did not recognize it. It also keeps the human decision visible: AI can propose a mapping, but a person should confirm that abbreviations and translated names truly represent the same business category.
Check the result after cleaning
The final counts provide an important verification:
Poland 201 Germany 100 France 83 Spain 58 United States 49 Italy 39
Together:
201 + 100 + 83 + 58 + 49 + 39 = 530
We started with 530 records and still have 530 after standardization. The 21 labels became six reviewed categories without losing a customer.
This is why we work in steps. We inspect the original labels, apply an explicit mapping, and then check both the number of categories and the total number of records.
Why inconsistent categories matter
A messy category column usually does not produce an error. A chart, groupby(), or filter still runs; it simply treats slightly different strings as separate groups.
In our example, Poland initially appears to have 156 customers instead of 201. That is an undercount of 45, yet Poland remains the largest market. The result looks plausible enough that nobody may question it.
The same issue affects later operations. Before cleaning:
df[df["country"] == "Poland"]
does not include PL, POLAND, poland, or Polska. Joins on country names may fail to match those rows, and revenue by country may split one market across several labels.
What looks like text formatting can quietly become a reporting problem.
Use the same approach for other categories
The same process works for products, departments, subscription plans, payment statuses, and other text categories.
You might see product labels such as:
Enterprise enterprise ENT Enterprise Plan
or departments such as:
Human Resources HR hr People
Start by listing the unique values and their counts. Look for differences in capitalization, abbreviations, punctuation, local-language names, and extra spaces. Then create an explicit, reviewed mapping from the observed variants to the standard values you want.
Keep the mapping in the notebook and rerun the count when a new export arrives. If a new label appears, review it before adding another rule.
If you control the system that creates the spreadsheet, fix the problem at the source as well. A dropdown with six allowed country names is easier to maintain than a free-text field where everyone can write a different variation.
Reuse the notebook
The prompts, generated Python, and outputs are stored in a normal Jupyter notebook. The standardization process remains available after you save the cleaned spreadsheet.
Next month, load the new export and rerun the notebook. The first count immediately reveals new labels. You can update the mapping if necessary, rerun the standardization, and retain a clear history of the cleaning rule.
A note on privacy
MLJAR Studio works with files directly on your computer. The generated Python reads customers.xlsx from your disk and performs the standardization locally, so you do not need to upload the workbook before Python can work with it.
There is a difference between where Python runs and where the AI model runs. With a cloud AI provider, the model may receive context such as your prompt, column names, dataframe shape, and preview values. In this example, labels such as Polska or U.S. may be part of that context.
If you want the AI part to stay local too, MLJAR Studio can connect to local LLMs through Ollama, vLLM, LM Studio, Jan, and llama.cpp. In that setup, prompts, data context, generated code, and results do not need to be sent to a cloud AI provider.
What we did
We started with a country column containing 21 text labels even though the dataset represented only six countries.
Before changing anything, we inspected every label and its count. That showed us that Poland appeared as Poland, POLAND, poland, PL, and Polska.
We normalized the text and applied an explicit mapping to consistent full country names. After cleaning, the 21 labels became six countries while all 530 customer records remained accounted for.
The important part was doing the work in reviewable steps: inspect the source values, confirm the mapping, preserve unknown labels, and verify the final counts.
Next steps
Continue with Remove Duplicate Rows from Excel with Python or Fix Mixed Date Formats in Excel with Python for other focused checks. Follow Clean Messy Excel Data with Python and AI to combine category standardization with duplicate removal, missing-value handling, and date parsing.