Add Suffix to Dataframe with Specific Repeating Column Name

May 31, 2023

i have data in a dataframe such as the following columns: week, SITE, LAL, SITE, LAL. I need to assign a suffix to the col name == ‘SITE’ such that the final df will look like: week, SITE_1, LAL, SITE_2, LAL.

Thank you,

dataframe example:

    week    SITE        LAL SITE        LAL
0   1   BARTON CHAPEL   1.1 PENASCAL I  1.0
1   2   BARTON CHAPEL   1.1 PENASCAL I  1.0
2   3   BARTON CHAPEL   1.1 PENASCAL I  1.0
3   4   BARTON CHAPEL   1.1 PENASCAL I  1.0
4   5   BARTON CHAPEL   1.1 PENASCAL I  1.0
5   6   BARTON CHAPEL   1.4 PENASCAL I  1.0

>Solution :

You can try to use itertools.count:

from itertools import count

cnt = count(1)
df.columns = [f'{c}_{next(cnt)}' if c == 'SITE' else c for c in df.columns]

print(df)

Prints:

   week         SITE_1  LAL      SITE_2  LAL
0     1  BARTON CHAPEL  1.1  PENASCAL I  1.0
1     2  BARTON CHAPEL  1.1  PENASCAL I  1.0
2     3  BARTON CHAPEL  1.1  PENASCAL I  1.0
3     4  BARTON CHAPEL  1.1  PENASCAL I  1.0
4     5  BARTON CHAPEL  1.1  PENASCAL I  1.0
5     6  BARTON CHAPEL  1.4  PENASCAL I  1.0