pandas.read_csv duplicate column handling: custom suffix naming not supported

Best strategy for custom duplicate‑column handling in pandas.read_csv

The recommended approach is to let pandas mangle the duplicates with its default numeric suffixes, then rename the columns to the pattern you want.

Why this works

pandas requires a unique column index; mangle_dupe_cols=True (the default) guarantees uniqueness by appending .1, .2, … to repeated names. Setting it to False raises a ValueError, so the only way to get a DataFrame is to accept the mangled names and change them afterward.

Confirmed facts (pandas 2.2.x)

• mangle_dupe_cols accepts only a boolean; there is no argument for a custom suffix format.
• When True, the generated suffixes are exactly .1, .2, … in order of appearance.
• Setting False raises ValueError: Duplicate column names not allowed.
• No open PR or roadmap item (as of pandas 2.2) adds a custom suffix formatter.

Step‑by‑step workflow

1. Read the CSV with the default mangling:

   import pandas as pd
   df = pd.read_csv('file.csv')  # mangle_dupe_cols=True by default
   

2. Inspect the generated column list to confirm the suffix pattern:

   print(df.columns.tolist())
   # e.g. ['id', 'value', 'value.1', 'value.2']
   

3. Build a mapping that converts the pandas‑generated names to your desired format. For a simple “_dup” suffix:

   def rename(col):
       if col.endswith('.1'):
           return col[:-2] + '_dup'
       if col.endswith('.2'):
           return col[:-2] + '_dup2'
       return col
   new_cols = [rename(c) for c in df.columns]
   df.columns = new_cols
   

4. If you need a more dynamic rule (e.g., timestamp or incremental counter), use enumerate on duplicated base names:

   from collections import defaultdict
   counts = defaultdict(int)
   mapped = []
   for c in df.columns:
       base = c.rsplit('.', 1)[0] if '.' in c and c.split('.')[-1].isdigit() else c
       if base != c:
           counts[base] += 1
           mapped.append(f'{base}_dup{counts[base]}')
       else:
           mapped.append(c)
   df.columns = mapped
   

5. Continue with your analysis; the DataFrame now has unique column labels that follow your convention.

Alternative: header‑none method (only if you need to inspect the raw header first)

1. Read without header:

   df_raw = pd.read_csv('file.csv', header=None)
   

2. Extract the first row as the raw header:

   raw_header = df_raw.iloc[0].tolist()
   

3. Apply your own duplicate‑resolution logic to raw_header to produce a list of unique names.
4. Set the columns and drop the header row:

   df = df_raw.iloc[1:].copy()
   df.columns = processed_header
   df.reset_index(drop=True, inplace=True)
   

Notes and limitations

• Pandas cannot store a DataFrame with duplicate column labels; the names must be made unique at some point.
• Post‑processing must match the exact suffix pandas creates (e.g., .1, .2) – be careful if original names already contain dots.
• The header=None workflow shifts the data down one row; re‑apply any dtype, na_values, or converters you need.
• Extending pandas’ internal parser (subclassing TextFileReader) to accept a custom suffix function is possible but fragile across versions and not recommended for production.
• As of pandas 2.2.x there is no announced enhancement to add a custom suffix argument to mangle_dupe_cols. Check the pandas issue tracker for any future proposals.