Add Suffix to Dataframe with Specific Repeating Column Name

Question

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

Answer 1

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

Answer 2

Another possible solution:

df.columns = [
    f'{x}_{y}' if x == 'SITE' else x for x, y in 
    zip(df.columns, np.cumsum(df.columns == 'SITE'))]

Output:

   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

Answer 3

Another option is to create a dictionary and use rename:

d = {'SITE':list(range(1,df.columns.tolist().count('SITE')+1))}

df.rename(lambda x: x + '_' + str(d.get(x).pop(0)) if x in d else x,axis=1)