CODEWITHSUNDEEP
pythonexcelpandasdictionary
mahmood
mahmood

Reputation: 24745

Duplicated sheets in excel file with if_sheet_exists='replace'

I use the following code to write dataframes from a dictionary to excel sheet. The sheet name is the key from dictionary. The code also checks for the presence of the file (in case of missing file, create one) and replaces old sheet with new data from previous runs.

df_dict = {}
data = [[0.1,3,0.4], [0.2,6,0.9], [0.3,8,1]]
df = pd.DataFrame(data, columns = ['c', 's', 'e'])
df_dict['AA'] = df
data = [[0.1,2,0.3], [0.2,6,0.4], [0.3,9,0.6]]
df = pd.DataFrame(data, columns = ['c', 's', 'e'])
df_dict['BB'] = df

print(df_dict)
excel_file = 'test.xlsx'
if not os.path.isfile(excel_file):
    wb = openpyxl.Workbook()
    wb.save(filename=excel_file)

for key in df_dict:
    df = df_dict[key]
    with pd.ExcelWriter(excel_file, mode='a', if_sheet_exists='replace') as writer:  
        df.to_excel(writer, sheet_name=key)

However, by running the code, multiple times, I see duplicated sheets. For example, I see AA,BB,AA1,BB1, and so on. How can I fix that?

enter image description here

Upvotes: 0

Views: 282

Answers (1)

Christian Eslabon
Christian Eslabon

Reputation: 185

Add engine = 'openpyxl' inside pd.ExcelWriter

 for key in df_dict:
     df = df_dict[key]
     with pd.ExcelWriter(excel_file, mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:  
         df.to_excel(writer, sheet_name=key)

Upvotes: 1

Related Questions