Kolanut Distribution · FMCG distribution

Customer Data Clean-up

Turn a messy customer export into a clean, reliable list: duplicates, mixed formats, stray spaces and money stored as text.

Download data (3 kB ZIP)

ExcelPythonPower BIBeginner

Overview

The business

Kolanut is moving to a new sales system. Before its customer list can be imported, it has to be cleaned. The export from the old system has been typed by different people over several years.

Some customers appear more than once, each region is written several different ways, dates come in three formats and credit limits are stored as text.

Your brief

Produce a clean customer list with one row per customer and consistent, correctly typed columns, and a short log of what you changed so the IT team can trust it.

Questions to answer

1How many rows are there, and how many real customers once duplicates are removed?
2How many different ways is each region written, and what should the standard names be?
3Which dates are in which format, and what is each one as a real date?
4Which credit limits are missing, and what are the rest as numbers?

What to deliver

A clean file with one row per customer and these columns: name, region, city, phone, date joined, credit limit, channel.
A cleaning log: each problem you found, how many rows it affected, and what you did.
Optional: compare your result with sales/customers.csv, the answer key.

Suggested approach

Never edit the raw file. Work on a copy or in Power Query, so every step can be repeated.
Trim spaces and fix capitals before looking for duplicates, or the duplicates won't match.
Map every region spelling (SW, South-West, south west…) to one of the six standard names.
Dates like 22/10/2023 are day first. Convert all three formats to real dates.
Remove ₦, commas and .00 from credit limits, then convert to numbers. Leave blanks as blanks and note them.

Data

Kolanut customer export (messy). Kolanut's customer list as exported from its old system: duplicates, stray spaces, mixed capitals, three date formats and money stored as text.

1 file · 102 rows in total · 8 kB. Download everything as a ZIP.

customer_list_raw.csv

102 rows · 7 columns · 8 kB

Download

Kolanut's customer list exported from an old system, errors and all. The clean version is sales/customers.csv.

Column	Type	Description	Values	Missing
Customer Name	Text	Business name. Watch for extra spaces, different capitals and duplicates.	99 distinct	None
Region	Text	Sales region, written many ways: LAGOS, lagos, SW, South-West and so on.	23 distinct	None
City	Text	City or town, with inconsistent capitals and spacing.	37 distinct	None
Phone	Text	Phone number in several formats, with and without +234 and spaces.	90 distinct	None
Date Joined	Text	Date they became a customer, in three formats: 2023-10-22, 22/10/2023 (day first) and 22-Oct-2023.	89 distinct	None
Credit Limit	Text	Credit limit stored as text, sometimes with ₦, commas or .00. Some are blank.	67 distinct	5
Channel	Text	Wholesale, Supermarket or Kiosk.	3 distinct	None

Get started

Starter code to get you going. The Python runs as it is in Google Colab: it loads the data straight from this site. For SQL, import each CSV as a table with the same name as the file.

PythonFirst cleaning steps

import pandas as pd

raw = pd.read_csv("https://academy.cloudtechanalytics.com/datasets/cleaning/customer_list_raw.csv", dtype=str)
df = raw.copy()

df["Customer Name"] = df["Customer Name"].str.strip().str.replace(r"\s+", " ", regex=True).str.title()
df["Credit Limit"] = pd.to_numeric(df["Credit Limit"].str.replace(r"[₦,\s]", "", regex=True), errors="coerce")
df["Date Joined"] = pd.to_datetime(df["Date Joined"], format="mixed", dayfirst=True)
df = df.drop_duplicates(subset="Customer Name")

print(len(raw), "rows before,", len(df), "after")

ExcelIn Excel with Power Query

Data → From Text/CSV → customer_list_raw.csv → Transform Data.
Select the text columns → Transform → Format → Trim, then Capitalize Each Word.
Use Replace Values on Region to map each spelling to the standard name.
Change Date Joined to Date using Locale → English (United Kingdom) for day-first dates.
Remove Duplicates on Customer Name, then Close & Load.

When you've finished

Put your work on GitHub with a short README, add it to your portfolio, and share what you found. The Git & GitHub and Student Portfolio courses show you how.

Loading…

Overview

The business

Kolanut is moving to a new sales system. Before its customer list can be imported, it has to be cleaned. The export from the old system has been typed by different people over several years.

Some customers appear more than once, each region is written several different ways, dates come in three formats and credit limits are stored as text.

Your brief

Produce a clean customer list with one row per customer and consistent, correctly typed columns, and a short log of what you changed so the IT team can trust it.

Questions to answer

1How many rows are there, and how many real customers once duplicates are removed?
2How many different ways is each region written, and what should the standard names be?
3Which dates are in which format, and what is each one as a real date?
4Which credit limits are missing, and what are the rest as numbers?

What to deliver

A clean file with one row per customer and these columns: name, region, city, phone, date joined, credit limit, channel.
A cleaning log: each problem you found, how many rows it affected, and what you did.
Optional: compare your result with sales/customers.csv, the answer key.

Suggested approach

Never edit the raw file. Work on a copy or in Power Query, so every step can be repeated.
Trim spaces and fix capitals before looking for duplicates, or the duplicates won't match.
Map every region spelling (SW, South-West, south west…) to one of the six standard names.
Dates like 22/10/2023 are day first. Convert all three formats to real dates.
Remove ₦, commas and .00 from credit limits, then convert to numbers. Leave blanks as blanks and note them.

Data

Kolanut customer export (messy). Kolanut's customer list as exported from its old system: duplicates, stray spaces, mixed capitals, three date formats and money stored as text.

1 file · 102 rows in total · 8 kB. Download everything as a ZIP.

customer_list_raw.csv

102 rows · 7 columns · 8 kB

Download

Kolanut's customer list exported from an old system, errors and all. The clean version is sales/customers.csv.

Column	Type	Description	Values	Missing
Customer Name	Text	Business name. Watch for extra spaces, different capitals and duplicates.	99 distinct	None
Region	Text	Sales region, written many ways: LAGOS, lagos, SW, South-West and so on.	23 distinct	None
City	Text	City or town, with inconsistent capitals and spacing.	37 distinct	None
Phone	Text	Phone number in several formats, with and without +234 and spaces.	90 distinct	None
Date Joined	Text	Date they became a customer, in three formats: 2023-10-22, 22/10/2023 (day first) and 22-Oct-2023.	89 distinct	None
Credit Limit	Text	Credit limit stored as text, sometimes with ₦, commas or .00. Some are blank.	67 distinct	5
Channel	Text	Wholesale, Supermarket or Kiosk.	3 distinct	None

Get started

Starter code to get you going. The Python runs as it is in Google Colab: it loads the data straight from this site. For SQL, import each CSV as a table with the same name as the file.

PythonFirst cleaning steps

import pandas as pd

raw = pd.read_csv("https://academy.cloudtechanalytics.com/datasets/cleaning/customer_list_raw.csv", dtype=str)
df = raw.copy()

df["Customer Name"] = df["Customer Name"].str.strip().str.replace(r"\s+", " ", regex=True).str.title()
df["Credit Limit"] = pd.to_numeric(df["Credit Limit"].str.replace(r"[₦,\s]", "", regex=True), errors="coerce")
df["Date Joined"] = pd.to_datetime(df["Date Joined"], format="mixed", dayfirst=True)
df = df.drop_duplicates(subset="Customer Name")

print(len(raw), "rows before,", len(df), "after")

ExcelIn Excel with Power Query

Data → From Text/CSV → customer_list_raw.csv → Transform Data.
Select the text columns → Transform → Format → Trim, then Capitalize Each Word.
Use Replace Values on Region to map each spelling to the standard name.
Change Date Joined to Date using Locale → English (United Kingdom) for day-first dates.
Remove Duplicates on Customer Name, then Close & Load.

When you've finished

Put your work on GitHub with a short README, add it to your portfolio, and share what you found. The Git & GitHub and Student Portfolio courses show you how.