You have a customer export with 530 rows, and you suspect that some records appear more than once. Maybe an export was run twice and appended to the same file. Maybe two people imported the same batch. The spreadsheet still opens normally, so nothing immediately looks broken.
The problem usually appears later. Customer counts are a little too high, revenue totals are slightly inflated, or reports disagree with another system. Duplicate rows are easy to miss because the file can still look perfectly reasonable.
In this tutorial, we will use Python in MLJAR Studio to find exact duplicate rows, check whether repeated customer IDs hide conflicting records, remove only the safe duplicates, and save the reviewed result as a new Excel file.
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 put 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 duplicate rows. The same file is used in the larger data-cleaning tutorial, where we also fix missing values, dates, and inconsistent country names.
Open the Excel file
Start MLJAR Studio and choose Start a New AI Conversation.
Use the Read a file button 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 duplicate analysis locally on your computer.
Studio creates the instruction, writes the Python, and runs it.
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()
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. That is our starting point.
Count exact duplicate rows
First, ask:
how many rows are exact duplicates
Studio generates:
exact_duplicate_rows = df.duplicated().sum() print(f"Exact duplicate rows: {exact_duplicate_rows}")
The result is:
Exact duplicate rows: 30
There are 30 extra rows that exactly match another row in the file.
The word exact matters. duplicated() compares every column. The customer ID, name, email, country, signup date, plan, and monthly spend must all match for a row to count as an exact duplicate.
That makes these records safer to remove than rows that only look similar. If two rows share a customer ID but contain different email addresses, that is not an exact duplicate. It may be an update, a data-entry error, or two versions that require human review.
By default, duplicated() keeps the first copy and marks later copies. The number 30 therefore means 30 extra rows would be removed; more than 30 rows are involved in the duplicate groups.
Check duplicate customer IDs too
Before deleting anything, let us check whether customers also appear more than once with slightly different data.
Ask:
how many rows have a duplicate customer_id
Studio generates:
duplicate_customer_id_rows = df.duplicated( subset="customer_id", keep=False, ).sum() print(f"Rows with a duplicate customer_id: {duplicate_customer_id_rows}")
The result is:
Rows with a duplicate customer_id: 60
The two numbers mean different things:
- 30 is the number of extra exact duplicate rows.
- 60 counts every row whose
customer_idrepeats, including the copy that will be kept.
Here, 60 is exactly twice 30. Each repeated ID appears twice, and each pair consists of exact copies. There are no additional repeated IDs with conflicting details.
If the second number were larger—for example, 70—that would be a warning. Some customer IDs would occur in rows that are not exact copies. drop_duplicates() would not resolve those conflicts, and we would inspect them separately before deciding what to keep.
This extra check keeps a human in the analysis loop. Python finds the duplicate patterns; we confirm what they mean before removing data.
Remove duplicates and save the result
Now that we know the repeated records are exact copies, ask:
remove duplicate rows and save as customers-deduped.xlsx
Studio generates:
df_deduped = df.drop_duplicates().copy() output_path = "customers-deduped.xlsx" df_deduped.to_excel(output_path, index=False) print(f"Rows before: {len(df)}") print(f"Rows after: {len(df_deduped)}") print(f"Removed exact duplicate rows: {len(df) - len(df_deduped)}") print(f"Saved: {output_path}")
The output is:
Rows before: 530 Rows after: 500 Removed exact duplicate rows: 30 Saved: customers-deduped.xlsx
This is exactly what we expected. Printing all three counts is a useful safeguard. If we expected to remove 30 rows but ended with only 470 records, we would immediately know that something else happened.
index=False prevents pandas from writing its internal row numbers into an extra Excel column.
Because the output filename contains no folder, it is saved relative to the notebook's working directory. To place it beside the source spreadsheet, use an explicit path:
remove duplicate rows and save as ~/Documents/customers/customers-deduped.xlsx
The original customers.xlsx remains unchanged. Duplicate removal happens in memory, and the reviewed data is written to a new workbook.
Which duplicate does pandas keep?
By default, drop_duplicates() keeps the first occurrence and removes later copies:
df.drop_duplicates(keep="first")
You can keep the last copy instead:
df.drop_duplicates(keep="last")
Or mark every row involved in a duplicate group:
df.duplicated(keep=False)
For exact duplicates, choosing the first or last copy usually does not change the data because both records contain identical values. Once two records differ, however, choosing which one to keep requires understanding the data. Python can surface the records, but it should not silently decide which version is correct.
What exact duplicate detection will not catch
drop_duplicates() compares values exactly. These names may refer to the same person but are not exact matches:
Anna Weber Ana Weber
The same applies to capitalization differences, trailing spaces, changed email addresses, or customers who received two IDs.
Exact duplicates are a good first cleaning step because they are unambiguous. Fuzzy duplicates are a separate entity-resolution problem and need stronger rules, supporting evidence, and usually human review.
Reuse the notebook
The work is saved in a normal Jupyter notebook. Prompts become markdown cells, generated Python becomes code cells, and outputs are stored beside them.
When another customer export arrives, change the input file, rerun the notebook, and compare the duplicate counts. You can extend the checks to repeated email addresses, customer IDs, or similar names.
Keeping the cleaning logic and checks together means you do not need to remember or manually repeat the process in Excel.
A note on privacy
MLJAR Studio reads customers.xlsx from your disk and performs the duplicate checks locally. The workbook does not need to be uploaded for Python to work with it.
If you use a cloud AI provider, the model may need context such as your prompt, column names, dataframe shape, and preview values. For a real customer file containing names or email addresses, consider what context may be sent.
To keep the AI part local too, connect MLJAR Studio to a local LLM through Ollama, vLLM, LM Studio, Jan, or llama.cpp. Then prompts, data context, generated code, and results can remain on your computer or infrastructure.
Summary
We started with 530 customer rows and found 30 exact duplicate records. Before deleting anything, we checked repeated customer_id values and confirmed that all repeated IDs belonged to the same exact duplicate pairs.
We removed the 30 extra rows and saved a new workbook containing 500 customer records, while leaving the original file untouched.
The important step was not simply calling drop_duplicates(). We checked what kind of duplicates were present before removing them, separating safe exact copies from conflicting records that would require more careful review.
Next steps
- Clean messy Excel data with Python and AI to continue with missing values, mixed dates, and inconsistent country names
- Explore spreadsheet workflows for more ways to clean, analyze, and automate Excel files