您可以尝试使用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)

图纸：

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

另一种可能的解决方案：

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

输出：

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

另一种选择是创建一个字典并使用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)