Data Automation & Cleaning Beginner

Clean Messy Accounting & Financial CSV Data in Python (Currency, Dates & Cr/Dr)

Standardize messy accounting exports in Python by stripping ₹/$ symbols, converting parenthesized negatives '(500.00)' to '-500.0', and parsing mixed dates.

#pandas#numpy

Problem Statement

ERP and accounting exports format negative numbers as '(1,250.00)' or append 'Dr/Cr' strings, causing Pandas to treat numeric columns as text objects. This sanitizer fixes them automatically.

Formula / Core Logic

Replaces '(x)' with '-x', strips non-numeric characters via regex r'[^0-9.-]', and casts to float64.

Installation

pip install pandas numpy

clean-messy-financial-statements-python-pandas.py

import pandas as pd
import re

def sanitize_accounting_column(series: pd.Series) -> pd.Series:
    def clean_val(val):
        s = str(val).strip()
        is_neg = ('(' in s and ')' in s) or s.upper().endswith('DR')
        num_str = re.sub(r'[^0-9.]', '', s)
        if not num_str:
            return 0.0
        val_float = float(num_str)
        return -val_float if is_neg else val_float
    return series.apply(clean_val)

Get the Complete 100+ Python Quant & Automation Bundle

Instant lifetime access to all production-ready scripts, valuation models, and trading bots ($19).

Buy Full Bundle on Gumroad ($19) →

Frequently Asked Questions

Q: Does this handle Indian numbering commas like 1,25,000.00?

Yes, stripping all commas before float conversion handles both Indian (Lakhs/Crores) and International (Millions) formatting seamlessly.