remove-duplicates-excel-python
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 | 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 Excel file contains 530 customer records with 7 fields: customer ID, name, email, country, signup date, subscription plan, and monthly spend. The preview shows multiple date formats and plan tiers including Free, Basic, Pro, and Enterprise.
how many rows are exact duplicates
Count rows that are identical across all seven columns:
</>Count duplicate rows
exact_duplicate_rows = df.duplicated().sum()
print(f"Exact duplicate rows: {exact_duplicate_rows}")Exact duplicate rows: 30
There are 30 exact duplicate rows—rows whose values match another row across every column. This is about 5.7% of the 530 records.
how many rows have a duplicate customer_id
Thinking
Looking into code for duplicates
I need to write some code that helps distinguish rows with duplicate IDs. It seems like I should use a function to identify these duplicates and then sum them up. I want to ensure I'm keeping the right data and doing it accurately. This sounds like a straightforward task, but I want to make sure I get it right, especially since working with duplicates can be tricky. Let’s do this!
Count every row whose customer_id occurs more than once:
</>Count duplicate customer ids
duplicate_customer_id_rows = df.duplicated(subset="customer_id", keep=False).sum()
print(f"Rows with a duplicate customer_id: {duplicate_customer_id_rows}")Rows with a duplicate customer_id: 60
60 rows have a customer_id that appears more than once in the dataset.
remove duplicate rows and save as customers-deduped.xlsx
Thinking
Proposing deduplication code
I need to propose a code to handle potential duplicate rows in a dataset. The original has a count of exact duplicates, which raises some ambiguity. Typically, removing duplicate rows involves all exact duplicates, so I consider a solution that saves the cleaned data. I want to ensure the code can specify a current working directory and require a filename. I’ll save the deduplicated file as "customers-deduped.xlsx" and include counts for clarity.
This removes exact duplicate records (30 rows) and saves the cleaned dataset.
</>Remove duplicates and save data
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}")Rows before: 530 Rows after: 500 Removed exact duplicate rows: 30 Saved: customers-deduped.xlsx
The file customers-deduped.xlsx was saved after removing 30 exact duplicate rows. The cleaned dataset contains 500 rows, compared with 530 originally.