Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

writing pandas data frame to existing workbook

I want to save a data frame to the second sheet of a file but I can't do that and I don't know why.

yfile = openpyxl.load_workbook(new_file, data_only=True) 
ws = yfile.worksheets[0] 
sheet2 = yfile.create_sheet() 
ws2 = yfile.get_sheet_by_name("Sheet").title = "Analysis" 
writer = pd.ExcelWriter(yfile, engine='xlsxwriter') 
df3.to_excel(writer, sheet_name='Analysis') 
writer.save()
yfile.save(new_file)                                          

I have created the sheet 'Analysis' but when I save in it I received the following response: "AttributeError: 'Workbook' object has no attribute 'write'"

What I have to modify?

like image 398
Michele Della Mea Avatar asked Apr 06 '16 10:04

Michele Della Mea


People also ask

How do you write a Pandas DataFrame to an existing Excel file?

You can write any data (lists, strings, numbers etc) to Excel, by first converting it into a Pandas DataFrame and then writing the DataFrame to Excel. To export a Pandas DataFrame as an Excel file (extension: . xlsx, . xls), use the to_excel() method.

How do you write to an existing Excel file without overwriting data using pandas?

How to write to an existing Excel file without overwriting data using Python Pandas? To write to an existing Excel file without overwriting data using Python Pandas, we can use ExcelWriter . to create the ExcelWriter instance with the Excel file path. And then we call save to save the changes.

How do I write pandas DataFrame to multiple sheets in Excel?

To write to multiple sheets it is necessary to create an ExcelWriter object with a target file name, and specify a sheet in the file to write to. Multiple sheets may be written to by specifying unique sheet_name . With all data written to the file it is necessary to save the changes.


2 Answers

You can use the append_df_to_excel() helper function, which is defined in this answer:

Demo:

In [127]: df = pd.DataFrame(np.random.rand(5, 3), columns=list('abc'))

In [128]: df
Out[128]:
          a         b         c
0  0.597353  0.770586  0.671231
1  0.628605  0.161283  0.397493
2  0.476206  0.701582  0.476809
3  0.045590  0.302834  0.857033
4  0.414820  0.478016  0.563258

In [129]: df.to_excel(filename)

In [130]: append_df_to_excel(filename, df, sheet_name="Sheet2", startrow=1, startcol=1)

In [131]: append_df_to_excel(filename, df, sheet_name="Sheet3", index=False)

In [132]: append_df_to_excel(filename, df, sheet_name="Sheet1", startcol=2, index=False)

Result:

Sheet1:

enter image description here

Sheet2:

enter image description here

Sheet3:

enter image description here

PS tested using the following versions:

  • python: 3.6.4
  • pandas: 0.22.0
  • openpyxl: 2.4.10
like image 195
MaxU - stop WAR against UA Avatar answered Oct 20 '22 00:10

MaxU - stop WAR against UA


You can use such way as below. It writes dataframe to an excel-file row by row.

from openpyxl.utils.dataframe import dataframe_to_rows
filename = r'some_file.xlsx'
df = some_dataframe

wb = load_workbook(filename)
if 'sh2' in wb.sheetnames:#to check whether sheet you need already exists
    ws = wb['sh2']
else:
    ws = wb.active
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)
wb.save(filename)
like image 34
Sergey Solod Avatar answered Oct 19 '22 22:10

Sergey Solod