DEV Community

Python Tools Lab
Python Tools Lab

Posted on

How to Clean Messy Currency Strings & Duplicate Rows in Python (Pandas)

Real-world CSV and Excel exports rarely come clean. You almost always end up dealing with trailing spaces, exact duplicate rows, and text-formatted prices like "$ 1,499.00" that break math operations.

Here is a clean, reusable Python function using Pandas and regex to normalize an entire dataset in seconds:

import re
import pandas as pd

def clean_currency(val):
if pd.isna(val):
return 0.0
cleaned = re.sub(r"[^0-9.]", "", str(val))
return float(cleaned) if cleaned else 0.0

df = pd.read_csv("messy_data.csv")
df.columns = [c.strip().title().replace(" ", "_") for c in df.columns]
df = df.drop_duplicates()

if "Price" in df.columns:
df["Price"] = df["Price"].apply(clean_currency)

df.to_excel("cleaned_data.xlsx", index=False)

Enter fullscreen mode Exit fullscreen mode




Want 4 Complete Excel & CSV Automation Scripts?

If you need ready-to-run templates for merging 100s of spreadsheets, cleaning messy datasets, generating styled executive Excel reports, and catching fuzzy duplicate names, grab The No-BS Python Excel & CSV Automation Kit (50% launch discount with code EXCEL50):

Download the Playbook & 4 Templates (50% OFF): https://tugrulian8.gumroad.com/l/yhitqp/EXCEL50

Top comments (0)