Change the name of excel worksheet with pandas

import pandas as pd
import numpy as np
df = pd.read_excel(r"C:\Users\venkagop\Subbu\promo validation testing\P 02. Promotions-UK C1.xls")
df = df[['Promotions', 'Promotions: AE', 'Promotions: Anaplan ID', 'Promotions: Is Optima Scenario?', 'Promotions: SIDs', 'Set Inactive?', 'Start Date', 'End Date', 'Promo Period', 'Promo Optima Status', 'Change Promo Status']]
df = df[(df['Promo Period'] == 'FY1819')]
df = df[(df['Set Inactive?'] == 0 ) & (df['Promotions: Is Optima Scenario?'] == 1)]
df.dropna(subset=['Promotions: SIDs'], inplace=True)
df['Optima vs Anaplan Promo Status Validation'] = ""
df['Optima vs Anaplan Promo Status Validation'] = np.where(df['Promo Optima Status'] == df['Change Promo Status'], 'True', 'False')
df.to_excel(r"C:\Users\venkagop\Subbu\mytest.xls", index = False)
#after this i want to change sheeet1 name to some other name#


  • There are 2 ways you can approach this problem.

    Approach 1

    Save the excel file to the correct worksheet name from the beginning, by using the sheet_name argument.

    import pandas as pd
    writer = pd.ExcelWriter(r'C:\Users\venkagop\Subbu\mytest.xls')
    df.to_excel(writer, sheet_name='MySheetName', index=False)

    Approach 2

    If Approach 1 is not possible, change the worksheet name at a later stage using openpyxl. The advantage of this method is you remove the cost of converting pandas dataframe to Excel format again.

    import openpyxl
    file_loc = r'C:\Users\venkagop\Subbu\mytest.xls'
    ss = openpyxl.load_workbook(file_loc)
    ss_sheet = ss.get_sheet_by_name('Sheet1')
    ss_sheet.title = 'MySheetName'