standardize-excel-categories

Read a file /home/piotr/Documents/customers/customers.xlsx

</>Load customer data

import pandas as pd

df = pd.read_excel("/home/piotr/Documents/customers/customers.xlsx")

print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()

Shape: (530, 7)
Columns (7): ['customer_id', 'full_name', 'email', 'country', 'signup_date', 'plan', 'monthly_spend']

	customer_id	full_name	email	country	signup_date	plan	monthly_spend
0	CUST-0292	Anna Weber	anna.weber91@example.com	Poland	14/09/2024	Pro	49
1	CUST-0283	Mateo Kowalski	mateo.kowalski38@example.com	Poland	April 29, 2023	Basic	19
2	CUST-0082	Emma Andersen	emma.andersen58@example.com	United States	2024-09-13	Enterprise	199
3	CUST-0016	Nina Martin	nina.martin22@example.com	Italy	2023-08-13	Free	0
4	CUST-0472	Tomasz Rossi	tomasz.rossi70@example.com	Italy	2024-11-13	Free	0

The workbook contains 530 customer records with seven fields: customer identifier, name, email, country, signup date, subscription plan, and monthly spend. The preview confirms mixed signup-date formats, while monthly spend includes zero values for Free-plan customers.

show unique values in the country column with counts

</>Count countries

country_counts = df["country"].value_counts(dropna=False).rename_axis("country").reset_index(name="count")
country_counts

	country	count
0	Poland	156
1	Germany	88
2	France	66
3	Spain	45
4	United States	44
5	Italy	31
6	POLAND	17
7	poland	13
8	PL	10
9	FR	9
10	france	8
11	spain	7
12	germany	6
13	ES	6
14	IT	5
15	GERMANY	5
16	Polska	5
17	italy	3
18	U.S.	3
19	USA	2
20	DE	1

There are 21 distinct country labels. Poland is most common (156), followed by Germany (88), France (66), Spain (45), the United States (44), and Italy (31).

Several labels represent the same country with inconsistent spelling, casing, or abbreviations—for example Poland appears as Poland (156), POLAND (17), poland (13), PL (10), and Polska (5), totaling 201 records.

standardize the country column to full country names

Thinking

Standardizing code steps

I realize we need to give the code a standard structure. It seems like we can create a straight mapping while also accounting for whitespace, which might require normalizing the keys. I'm thinking it could be helpful to show the resulting counts in a table format, which allows for some flexibility in how we present this information. Ultimately, I aim to ensure everything is presented clearly and cohesively with one unified code.

Standardize casing, abbreviations, and alternate names to six full country names.

</>Normalize country names

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"])

df["country"].value_counts(dropna=False).rename_axis("country").reset_index(name="count")

	country	count
0	Poland	201
1	Germany	100
2	France	83
3	Spain	58
4	United States	49
5	Italy	39

The country values are now standardized to full names. The 530 customer records are represented by six countries: Poland (201), Germany (100), France (83), Spain (58), United States (49), and Italy (39). No abbreviated, lowercase, or alternate country labels remain.