[Python] How to output a pandas table to an excel file

[Python] How to output a pandas table to an excel file

Use the to_excel method to output a DataFrame type table as an excel file.

table of contents

1. [Install Library](#Install Library)
2. [Output by specifying the file name](# Output by specifying the file name)
3. [Output by specifying the sheet name](# Output by specifying the sheet name)
4. [Hide header](#Hide header)
5. [Hide index (row name)](#Hide index line name)
6. Don't show headers and indexes (#Hide headers and indexes)
7. [Put a blank line at the top](#Put a blank line at the top)
8. [Put a blank column on the left](#Put a blank column on the left)
9. [Specify the maximum number of decimal places to display](#Specify the maximum number of decimal places to display)
10. [Fill NaN with arbitrary value](Fill #nan with arbitrary value)
11. [What you can do with ExcelWriter and notes](What you can do with #excelwriter and notes)
12. [Create two or more sheets](#Create two or more sheets)
13. Add a new sheet to an existing file (#Add a new sheet to an existing file)

Library installation

pip install -U openpyxl
pip install -U xlwt
pip install -U pandas

① openpyxl: Python library for reading and writing xlsx / xlsm / xltx / xltm files

(2) xlwt: Library for writing data and format information to old Excel files (.xls, etc.) (Excel2003 or earlier)

③ A library for data analysis with python. Used for handling table data.

python

import pandas as pd
import numpy as np

row0 = [0, 1, 2, 3, 4]
row1 = [1, 10, 20, 30, 40]
row2 = [10, 100, 200, 300, 400]
row3 = [np.nan, np.nan, 'aaa', np.nan, np.nan]
row4 = [0.1, 0.12, 0.123, np.nan, 0.12345]

df = pd.DataFrame([row0,row1,row2,row3, row4])
df.columns = ['col0', 'col1', 'col2' ,'col3', 'col4']
df.index = ['row0', 'row1', 'row2', 'row3', 'row4']

df

• File name is required.
• If the same file already exists, overwrite it.

output

import pandas as pd
df.to_excel('~/desktop/output.xlsx')

Output to the desktop with the name "output.xlsx".

If the sheet name is not specified, it will be "sheet1".

** ▼ Output result **

df.to_excel ('file path', sheet_name ='A') └ "A": Sheet name

Specify the sheet name

import pandas as pd
df.to_excel('~/desktop/output.xlsx', sheet_name='AAA')

Output with the sheet name "AAA".

** ▼ Output result **

Or you can use "header = None".

No header

import pandas as pd
df.to_excel('~/desktop/output.xlsx', header=False)

Or you can use "index = None".

No index

import pandas as pd
df.to_excel('~/desktop/output.xlsx', index=None)

Describe ʻindex = False and header = False` in the options.

No header / index

import pandas as pd
df.to_excel('~/desktop/output.xlsx', index=False, header=False)

Describe startrow = n in the option. └ "n": Number of free lines

** ▼ When n = 3 **

Put a blank line at the top

import pandas as pd
df.to_excel('~/desktop/output.xlsx', startrow=3)

Describe startcol = n in the option. └ "n": Number of empty columns

** ▼ When n = 2 **

Put a blank line at the top

import pandas as pd
df.to_excel('~/desktop/output.xlsx', startcol=2)

Describe float_format ='% .nf' in the option. └ "n": Number of digits to display

Specify the maximum number of displayed digits for the decimal point

import pandas as pd
df.to_excel('~/desktop/output.xlsx', float_format='%.2f')

If n = 2, the 3rd digit is rounded off and displayed up to the 2nd decimal place.

Describe na_rep ='A' in the option. └ "A": Value to fill NaN cell

Fill NaN

import pandas as pd
df.to_excel('~/desktop/output.xlsx', na_rep='XXXX')

What you can do with Excel Writer and notes

If you use the ExcelWriter function and to_excel together, you can output an Excel file with two or more sheets or add a sheet to an existing file.

Official page

Excel Writer error supplement

How to write

with pd.ExcelWriter('Output destination path.xlsx') as writer:
Table object.to_excel(writer, sheet_name='Sheet name')
Table object 2.to_excel(writer, sheet_name='Sheet name 2')
・
・
・

▼ When outputting two tables, df1 and df2, to one Excel file

Illustration

with pd.ExcelWriter('test.xlsx') as writer:
    df1.to_excel(writer, sheet_name='sheet1')
    df2.to_excel(writer, sheet_name='sheet2')

Create a text.xlsx file in the same directory as the running environment.

How to write

with pd.ExcelWriter(''Output destination path.xlsx', mode='a') as writer:
Table object to add.to_excel(writer, sheet_name='Sheet name')

・ Mode ='a': Add file

▼ Add sheet "sheet3" to the existing file (test.xlsx).

Illustration

with pd.ExcelWriter('test.xlsx', mode='a') as writer:
    df3.to_excel(writer, sheet_name='Sheet3')

Official page list -Pandas.DataFrame.to_excel ・ Xlwt ・ Openpyxl ・ ExcelWriter