How can I save a dataframe into an excel sheet based on number of the worksheet (not a name)?

Question

Here is my DF.

data3 = {'DCF Years':  ['1st', '2nd', '3rd','4th','5th'],
    'DCF Amt': ['8.5', '6.5', '10.5', '4.5', '12.5']}
df = pd.DataFrame (data3, columns = ['DCF Years', 'DCF Amt'])
df = np.round(df, decimals=3)

And I want to save it to this excel sheet on the second tab (or worksheet). I don't want to use a name, but rather a number of the term sheet.

df.to_excel('/Users/AB/Drive/Earnings/Earnings.xlsx', 
           sheet_name= 1)

Answer 1

Assuming your excel sheet already exists, I would use ExcelWriter in append mode - that would be:

with pd.ExcelWriter(r'/Users/AB/Drive/Earnings/Earnings.xlsx', engine='openpyxl', mode='a') as writer:  
    df.to_excel(writer, sheet_name='mydf')

This would 'append' a new sheet at the end of your excel file with 'mydf' as a name. Then everytime you want to append a new sheet to the end, you just rerun the code with a different sheet_name