A data frame can be added as a new sheet to an existing excel sheet. For this operation, the library required is openpyxl.
You can install this library using below command in Jupyter notebook. The same command can be executed in command prompt without the exclamation character “!”.
|
1 2 |
# Installing library for excel interaction using pandas !pip install openpyxl |
You can add the data from multiple DataFrames, each becoming one sheet.
Below snippet loads a pre-existing excel sheet and adds two more sheets to it using two different data frames.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
import pandas as pd import numpy as np from openpyxl import load_workbook # Defining the path which excel needs to be created # There must be a pre-existing excel sheet which can be updated FilePath = "/Users/farukh/Python ML IVY-May-2020/CarPricesData.xlsx" # Generating workbook ExcelWorkbook = load_workbook(FilePath) # Generating the writer engine writer = pd.ExcelWriter(FilePath, engine = 'openpyxl') # Assigning the workbook to the writer engine writer.book = ExcelWorkbook # Creating first dataframe DataSample1= [[10,'value1'], [20,'value2'], [30,'value3']] SimpleDataFrame1=pd.DataFrame(data=DataSample1, columns=['Col1','Col2']) print(SimpleDataFrame1) # Creating second dataframe DataSample2= [[100,'A'], [200,'B'], [300,'C']] SimpleDataFrame2=pd.DataFrame(data=DataSample2, columns=['colA','colB']) print(SimpleDataFrame2) # Adding the DataFrames to the excel as a new sheet SimpleDataFrame1.to_excel(writer, sheet_name = 'Data1') SimpleDataFrame2.to_excel(writer, sheet_name = 'Data2') writer.save() writer.close() |
Author Details
Lead Data Scientist
Farukh is an innovator in solving industry problems using Artificial intelligence. His expertise is backed with 10 years of industry experience. Being a senior data scientist he is responsible for designing the AI/ML solution to provide maximum gains for the clients. As a thought leader, his focus is on solving the key business problems of the CPG Industry. He has worked across different domains like Telecom, Insurance, and Logistics. He has worked with global tech leaders including Infosys, IBM, and Persistent systems. His passion to teach inspired him to create this website!
this doesn’t appear to work. It throws and error “BadZipFile”..
Hi Max,
Please make sure the excel file is already present on your system. And you provide the correct path.
If you can share a screenshot of your screen with error I may be able to help.
its throwing same error for me as well. BadZipFile
When I open the excel workbook, I get a warning. We found a problem with some content in “filename” So you want us to try to recover as much as we can? if you trust the source of this workbook, click Yes. I click yes and the new worksheet appear. How can I fix this error?
Did you managed to fix this error, I’m facing the same at the moment. Thx
Try using writer.close() instead of writer.save().
Why do I loose the contents of my original workbooks when I try this
def Copy_Sheet_rename(path,pathn,Sheet,Sheetn):
# Copy a sheet to another sheetn
data = pd.read_excel(path, sheet_name=Sheet)
data.to_excel(pathn, sheet_name=Sheetn)
# Copy_Sheet_rename(path,pathNew,”Zadar_Az1″,”Zadar_Az1New”) # copy to a new workbook
Copy_Sheet_rename(path,path,”Zadar_Az”,”Zadar_Az1New”) # copy within the same workbook
Contents of xlsx file before
Zadar_Az
Zadar_Az2
Zadar_El
Zadar_Az3
Zadar_El1
Zadar_Az4
Zadar_El2
Zadar_Az5
Zadar_El3
Zadar_Az6
Zadar_El4
Zadar_Az7
Zadar_El5
Sheet1
Zadar_Az8
Zadar_El6
Zadar_Az9
Zadar_El7
Zadar_Az10
Zadar_El8
Zadar_Az11
Zadar_El9
Contents After
(1,)
Zadar_Az1New