Menu

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

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

DEV Community: tutorial·Python Tools Lab·about 12 hours ago
#boGJdBlU
#devtotutorial#devto#dev#excel#cleaned
Reading 0:00
15s threshold

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…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.