[Python] How to dynamically rename headers in a bank statement CSV/Excel using Python and...

I have bank statements in both Excel and CSV formats. The headers can vary slightly depending on the bank or the file export, for example:

TRAN_DATE, CHQNO, PARTICULARS, DR, CR, BAL, SOL


I want to normalize these column names to consistent names like:

{
"TRAN_DATE": "transaction_date",
"DR": "debit_amount",
"CR": "credit_amount",
"PARTICULARS": "narration",
"CHQNO": "cheque_no",
...
}


I have written a function to detect the header row dynamically:

def detect_header(self, df_raw, min_match=2, min_cols=3, max_scan=200):
normalized_mapping = {self.normalize_header(k): v for k, v in COLUMN_MAPPING.items()}
valid_headers = set(normalized_mapping.keys())

df_raw = df_raw.fillna("")

for idx in range(min(len(df_raw), max_scan)):
row = df_raw.iloc[idx].tolist()
split_row = []
for c in row:
if isinstance(c, str) and "," in c:
split_row.extend([x.strip() for x in c.split(",") if x.strip()])
elif isinstance(c, str) and c.strip():
split_row.append(c.strip())

values = [self.normalize_header(c) for c in split_row]
matches = [v for v in values if v in valid_headers]

if len(matches) >= 4:
new_cols = [normalized_mapping.get(self.normalize_header(c), c) for c in split_row]
df = df_raw.iloc[idx+1:].reset_index(drop=True)
if len(df.columns) < len(new_cols):
for i in range(len(new_cols) - len(df.columns)):
df[i + df.shape[1]] = ""
df.columns = new_cols
df = df.replace("", pd.NA).dropna(how="all").dropna(axis=1, how="all")
print(f"✅ Detected header at row {idx}: {df.columns.tolist()}")
return df

print("⚠️ No valid header found in sheet")
return None


My questions are:

Is there a simpler or more efficient way to rename headers dynamically for CSV/Excel files where headers may appear after a few metadata rows?

How can I handle cases where the header row has merged values or extra empty columns?

Is it better to normalize the headers using str.lower().strip() or is there a more robust method for bank statements with varying column names?

Any help or best practices for handling such dynamic header renaming in Pandas would be greatly appreciated.

Continue reading...