Rename Excel Tabs

Question

I am trying to rename tabs in an excel spreadsheet however it for one of the tabs it produces this name every time: '# of Behavioral Health Reps (5)1' Not sure why. There is only 1 of these matching tabs in each file. It also does not do it to any other tab.

text_search =  pd.DataFrame(
{'text_to_search':
    [
'Retro'
,'Prior'
,'Concurr'
,'Rate'
,'claims pd'
,'health reps'
,'External']
, 
'Replace':
[
'UR - Retrospective (1)'
,'UR - Prior Auth Req (2)'
,'UR - Concurrent Auth Req (2)'
,'Rate of First Level Appeals (3)'
,'Pct of Claims Paid (4)'
,'# of Behavioral Health Reps (5)' 
,'External Appeals (9)']})


for mco in files:
    wb = xl.load_workbook(mco, data_only=True)
    for sheet in wb.sheetnames:
        for index, row in text_search[0:].iterrows():           
            #print(row['text_to_search'],row['Replace'])
            if re.search(row['text_to_search'], sheet, re.IGNORECASE):
                worksheet = wb[sheet]
                worksheet.title = row['Replace']
    wb.save(mco)
    wb.close()```

Why on earth are you using a datafame and a regex? This code looks unnecessarily complicated and difficult to debug. If you simplify it, it will be easier to test. — Charlie Clark, Nov 21 '19 at 09:12

Charlie Clark · Answer 1 · 2019-11-21T17:00:32.403

If it looks like a mapping, then use a mapping:

l1 = [
'Retro'
,'Prior'
,'Concurr'
,'Rate'
,'claims pd'
,'health reps'
,'External']

l2 =[
'UR - Retrospective (1)'
,'UR - Prior Auth Req (2)'
,'UR - Concurrent Auth Req (2)'
,'Rate of First Level Appeals (3)'
,'Pct of Claims Paid (4)'
,'# of Behavioral Health Reps (5)' 
,'External Appeals (9)']

mapping = {k:v for k,v in zip(l1, l2)}

for ws in wb:
     if s.title in mapping:
          s.title = mapping[s.title]

If case is likely to an issue then convert to lowercase:

l1 = [v.lower() for v in l1]
if s.title.lower() in mapping:

Rename Excel Tabs

1 Answers1